Using Google/Gmail Apps as a Lightweight Postini Replacement

Filed under: Exchange, Security, Tech Stuff — Written by Chrissy on Monday, April 30th, 2007 @ 3:43 pm

I work for a large company that uses Postini for Enterprise spam filtering and it does a fantastic job. It's actually famous for being one of the very few spam filter capable of blocking UCEs from the "Cajun Spam King" (No, Scelson doesn't sound very Cajun to me...). And in researching for this article, I even found out that Postini will provide spam and anti-virus filtering for Gmail.

To use Postini, you pay them a good amount of money, change your company's MX record to point to their servers and then they filter your email, removing nearly all the spam. From there, the Postini servers forward the scrubbed emails to your own mail gateway, presumably a sendmail or Exchange machine. They may also keep archives of it if you pay them extra. The whole process looks something like the visual seen below:

Last week, I realized that Google Apps can actually do something similar, free of charge. The service, formerly called Google Apps For Your Domain, offers an unlimited amount of email accounts for your domain, each with 2GB of space each, mobile access (including Blackberry access) all for free with the Standard account. The Premier Edition ($50/year per mailbox) offers 10GB of disk space, API stuff, guaranteed uptime, phone support, and e-mail migration tools coming soon.

So I saw that Google was offering e-mail hosting, but didn't really know how it could apply to me. I like having control over my mail - I've been hosting my own for about a decade and it never really crossed my mind to point my MX records to anywhere but my own machines. Exchange's NS-IMF (Not-so-Intelligent Mail Filtering) spam filtering is really weak and inaccurate, however, and overwhelming false positives were becoming a pain. After thinking it out, I realized that I could outsource my spam filtering to Google/Gmail Apps by taking an approach similar to the way that Postini sets up their own customers.

I signed up netnerds.net for an Application account on Google Apps and started the process. I deleted my MX record and in its place, added the 7 or so MX records that Google gave me. Now my records look something like this:

netnerds.net MX preference = 5, mail exchanger = alt2.aspmx.l.google.com
netnerds.net MX preference = 10, mail exchanger = aspmx2.googlemail.com
netnerds.net MX preference = 10, mail exchanger = aspmx3.googlemail.com
netnerds.net MX preference = 10, mail exchanger = aspmx4.googlemail.com
netnerds.net MX preference = 10, mail exchanger = aspmx5.googlemail.com
netnerds.net MX preference = 1, mail exchanger = aspmx.l.google.com
netnerds.net MX preference = 5, mail exchanger = alt1.aspmx.l.google.com

Then I logged in to Google Apps e-Mail (which I've addressed as "Gmail For Your Domain" in the illustration below) and created the two whole user accounts/mailboxes that are valid on netnerds.net. Next, I went and added a new A record for a supersecret subdomain and (one by one), told Gmail to forward all the email to user@supersecrethost.netnerds.net. I then setup Exchange's recipient policy to accept e-mails for supersecrethost.netnerds.net and then ensured each of the two user accounts and their aliases were set as valid recipients. I also disabled IMF at the host level (SMTP -> Default SMTP -> Properties -> General -> Advanced -> Edit -> Uncheck Apply Intellingent Mail Filter) and instructed my other user to disable it at the Outlook level (Actions -> Junk E-mail -> Junk E-mail Options -> Poke around). So here's sorta what it looks like:

Using Google's Admin interface, I also added a subdomain http://gmail.netnerds.net that automatically directs to the Gmail Apps e-mail login page. Now we can use one of three interfaces to check our mail: Outlook Web Access, Exchange/Outlook, or Google. I decided to use Office 2007 as my primary e-mail client but I login to gmail.netnerds.net every couple days to check my spam box for false positives.

    

Because of the manual creation of the email accounts and the subsequent forwarding, this doesn't scale without a huge time investment. Using the Google API's that come with the Premier Edition, however, it would probably be easy to setup something similar on a mass scale. I also considered wildcards forwarding at Google's end (which is supported) then filtering again at my end using Exchange Sinks but my setup is too small to justiy the kind of time I'd spend doing that.

Two final notes: first, by selecting the forwarding option "Forward then Delete" and thus preventing Google Apps from being a Store and Forward, the 2GB storage limit wouldn't pose any sort of restriction for those needing super large mailboxes. The drawback, of course, is if you choose that option, you can no longer use the Gmail interface to check your mail. Second, Google Apps does provide support for additional domains -- I'm currently using it for RealCajunRecipes.com.

OWA: Expired Password Causes Execute Access Forbidden

Filed under: Exchange, IIS, Security, Tech Stuff — Written by Chrissy on Tuesday, January 16th, 2007 @ 2:06 pm

Recently, a user trying to login to OWA encountered the following error:

HTTP 403.1 Forbidden: Execute Access Forbidden
You have attempted to execute a CGI, ISAPI, or other executable program from a directory that does not allow programs to be executed.

Another network administrator noticed that the URL was strange too. The user had been directed to:

https://owa.mydomain.com /iisadmpwd/aexp.htr?https:// owa.mydomain.com/exchange/USA/

A quick Googling showed mentions of an expired password but the user was able to login to the domain so we were a bit baffled. As it turns out, we're in the middle of a migration and the user's account on the old domain which still hosts OWA/Exchange was expired but his password on the new domain account was still valid. The user was also not prompted to change his password in OWA because we did not enable that feature. So if you run into something similar, ensure that the user's account does not have "User Must Change Password At Next Logon" checked.

VBScript: Find All Exchange Servers in Active Directory

Filed under: Active Directory, Exchange, Quick Code, VBScript — Written by Chrissy on Tuesday, January 9th, 2007 @ 7:44 pm

My friend Sharfa and I were exchanging some of our favorite code snippets and he showed me one for enumerating Exchange Servers in Active Directory. I dug the code but wanted to try to see if I could use my Recordset/ADsDSOObject skrills to shorten the code. The outcome isn't any shorter but it does get the version, so that's cool. Thanks, Sharfa, for pointing me towards the WMI Exchange_Server thing, too. :-D
Hit It

'****************************************************************************
' This script created by Chrissy LeMaire (clemaire@gmail.com)
' Website: http://netnerds.net/
'
' This script finds all Exchange Servers in AD. Includes Exchange Version.
'
' Run this script with admin privs on any computer within a domain.
'
' This script has only been tested on Windows Server 2003
'
' NO WARRANTIES, USE THIS AT YOUR OWN RISK, etc.
'*****************************************************************************
 
Set objAdRootDSE = GetObject("LDAP://RootDSE")
Set objRS = CreateObject("adodb.recordset")
 
  varConfigNC = objAdRootDSE.Get("configurationNamingContext")
 
  strConnstring = "Provider=ADsDSOObject"
  strSQL = "SELECT * FROM 'LDAP://" & varConfigNC & "' WHERE objectCategory='msExchExchangeServer'"
  objRS.Open strSQL, strConnstring
    Do until objRS.eof
      Set objServer = GetObject(objRS.Fields.Item(0))
        Call getExchangeInfo(objServer.CN)
      Set objServer = Nothing
      objRS.movenext
    Loop
  objRS.close
 
Set objRS = Nothing
Set objAdRootDSE = Nothing
 
Sub getExchangeInfo(strServerName)
  Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!" & strServerName & "\ROOT\MicrosoftExchangeV2")
  Set colItems = objWMIService.ExecQuery("Select * from Exchange_Server")
 
    For Each objItem in colItems
      MsgBox UCase(objItem.Name) & " (" & objItem.FQDN & ") is running Exchange " & objItem.ExchangeVersion
    Next
 
  Set colItems = Nothing
  Set objWMIService = Nothing
End Sub

VBScript: Track Outbound E-mail Addresses in Exchange

Filed under: Exchange, Quick Code, VBScript — Written by Chrissy on Thursday, April 27th, 2006 @ 3:42 pm

I use the code below to extract e-mail addresses from outbound e-mails and insert them into a SQL table. This is useful when using custom Exchange/SQL Server solutions to put a lid on spam ;D

Exchange Sink

<script LANGUAGE="VBScript">
  Sub ISMTPOnArrival_OnArrival(ByVal Msg, EventStatus )
  On Error Resume Next
  EventStatus = 0 'Run the next sink by default.
 
  Connstring = "Driver={SQL Server}; Server=servername; Database=dbname; UID=username; PWD=mypass"
 
  Set envfields = Msg.EnvelopeFields
  Set fields = Msg.Fields
  RecipList = LCase(envfields("http://schemas.microsoft.com/cdo/smtpenvelope/recipientlist"))
  IpAddr =   LCase(envfields("http://schemas.microsoft.com/cdo/smtpenvelope/clientipaddress"))
  Sender =   LCase(fields.Item("urn:schemas:mailheader:from"))
 
  recipList = replace(LCase(RecipList),"smtp:","")
  recipArray = split(RecipList,";")
 
    For aa = LBound(recipArray) To UBound(recipArray)
      If Len(recipArray(aa)) > 0  and Len(ipaddr) = 0 and Sender = """chrissy lemaire"" <chrissy@netnerds.net>" Then
        Set rs = CreateObject("adodb.recordset")
        strSQL = "select whiteline from whitelist where whiteline = '" & recipArray(aa) & "'"
          rs.Open strSQL,Connstring,1,2
            If rs.eof and rs.bof Then
              rs.Addnew
              rs("whiteline") = recipArray(aa)
              rs.Update
            End If
          rs.close
        Set rs = nothing
      End If
    Next
 
  Msg.DataSource.Save
  EventStatus = 0 'cdoRunNextSink
  End Sub
</script>

Change Exchange Server's Administrative Group using adsiedit

Filed under: Exchange, Tech Stuff — Written by Chrissy on Wednesday, April 26th, 2006 @ 11:58 pm

The MSPress Exchange Training Kit supposedly says that you must uninstall/reinstall an Exchange Server in order to move it from one Administrative Group to another. I managed to do change the administrative group for one of my servers today using adsiedit. Here’s how I did it.

(more...)

VBScript: Delete ALL E-mails from the Exchange 2003 Queue

Filed under: Exchange, Quick Code, VBScript — Written by Chrissy on Sunday, April 16th, 2006 @ 5:06 pm

Recently my Exchange server got pounded by spammers that were attacking my NDR (non delivery report) capabilities. Turning off NDRs helped 75% and I explored Exchange quite a bit along the way to figure out that last 25%.

It seems that I had 180 emails stuck in my queue. I was looking at my logs and ethereal and it seemed that my mail server would go to several other servers, say "Hello" and then "Goodbye". No message in the middle. It didn't even ask if a recipient was valid. Twenty-four hours later, my queue was still churning at 180 emails constant and Exchange was still being silly.

I took a closer look at the queue. Why was it 180 constant? Then I noticed the date they started their retry -- December 5th 2005. That was the day that installed Exchange (and presumably SP1) on my new server. I restored some PSTs and that's about it. Those emails had been stuck in the queue for well over 2 months! Why didn't they expire? I have no idea.

So I began to try to empty my queue. It was such a manual process. Click on the envelope, click "Find Messages", Delete with No NDR, confirm Yes. I'd have to do this 180 times? No way. So I wrote a script to do it for me: (NOTE! This script empties your ENTIRE queue! There are very few circumstances that you will need to use this script.)

Quick Code

' Author: Chrissy LeMaire
' Copyright 2003 NetNerds Consulting Group
' Script is provided AS IS with no warranties or guarantees and assumes no liabilities.
' Website: http://www.netnerds.net
' Description: This scripts empties out the entire Exchange queue. USE WITH CAUTION.
 
Set objWMIExch = GetObject("winmgmts://./root/MicrosoftExchangeV2")
Set objLinksList = objWMIExch.ExecQuery ("Select * from Exchange_SMTPLink")
For each objLinkInst in objLinksList
 
strSQL = "Select * from Exchange_SMTPQueue where "
strSQL = strSQL & "LinkID='" & objLinkInst.LinkID
strSQL = strSQL & "' and LinkName='" & objLinkInst.LinkName
strSQL = strSQL & "' and ProtocolName='" & objLinkInst.ProtocolName
strSQL = strSQL & "' and VirtualMachine='" & objLinkInst.VirtualMachine
strSQL = strSQL & "' and VirtualServerName='" & objLinkInst.VirtualServerName & "'"
 
Set objQueuesList = objWMIExch.ExecQuery (strsql)
For each objQueueInst in objQueuesList
        i = i +1
                If i > 7 And InStr(objQueueInst.QueueName,".") > 0 Then 'make sure its not the built in stuff
 
                                        strSQL = "Select * from Exchange_QueuedSMTPMessage where " '<-- This class requires that you pass ALL the variables below in the where clause
                                        strSQL = strSQL & "LinkID='" & objLinkInst.LinkID
                                        strSQL = strSQL & "' and LinkName='" & objLinkInst.LinkName
                                        strSQL = strSQL & "' and ProtocolName='" & objLinkInst.ProtocolName
                                        strSQL = strSQL & "' and QueueID='" & objQueueInst.QueueID
                                        strSQL = strSQL & "' and QueueName='" & objQueueInst.QueueName
                                        strSQL = strSQL & "' and VirtualMachine='" & objLinkInst.VirtualMachine
                                        strSQL = strSQL & "' and VirtualServerName='" & objLinkInst.VirtualServerName & "'"
 
                                          Set objQueuesList1 = objWMIExch.ExecQuery (strsql)
                                                 For each objQueueInst1 in objQueuesList1
                                                        If i > 7 And InStr(objQueueInst1.QueueName,".") > 0 Then
                                                                objQueueInst1.DeleteNoNDR
                                                        End If
                                                 Next
                End If
        Next
Next
 
MsgBox i

This script emptied my queue in about 25 seconds and Exchange is no longer going out and saying Hello & Goodbye. Traffic is back to normal. I'm assuming that was an Exchange bug.

VBScript: Gather E-mail Addresses from an Exchange Mailbox

Filed under: Exchange, Quick Code — Written by Chrissy on Sunday, April 9th, 2006 @ 8:09 am

I recently sent out a newsletter to everyone who had any communicatoin with RealCajunRecipes.com. I had an Exchange Mailbox that collected all emails sent using the Tell-A-Friend feature of the website as well as the guestbook, ask maw-maw, and general website comments. I'd forgotten to automatically subscribe these users to the newsletter application and so I back tracked using this handy script.

Note: the name of the mailbox containing all of the emails is named RealCajunRecipes which is associated with the Active Directory account Chrissy theMayor. The SQL Server table which contained the newsletter subs is appropriately named newslettersubs.

Quick Code

'****************************************************************************
' This script created by Chrissy LeMaire (clemaire@gmail.com)
' Website: http://netnerds.net/
'
' NO WARRANTIES, etc.
'
' This script processes an Exchange mailbox for email addresses
' and adds them to a SQL Server table
'
' Requirements -- access to the Exchange mailbox and an SQL
' table
'
' This script has only been tested on Exchange Server 2003.
'
' "What it does"
' 1. Opens the Exchange profile "MS Exchange Profile"
' 2. Opens the mail store "Mailbox - Chrissy theMayor"
' 3. Opens the mailbox "RealCajunRecipes"
' 4. Iterates through the box collecting email addresses
' 5. Adds email to SQL table if it doesnt already exist
'
'*****************************************************************************
 
Const ConnString = "Provider=SQLOLEDB; Data Source=sql; Initial Catalog=realcajun;Trusted_Connection=yes;"
 
set oSession=CreateObject("MAPI.Session")
oSession.Logon "MS Exchange Profile"
 
Set objInfoStores = oSession.InfoStores 'hi my Name is bear
For i = 1 To objInfoStores.Count
    If objInfoStores.Item(i)= "Mailbox - Chrissy theMayor" Then
        Set objInfoStore = objInfoStores.Item(i)
         Set objRootFolder = objInfoStore.RootFolder
 
        ' Open the RealCajunRecipes folder
        Set objFolder = objRootFolder.Folders("RealCajunRecipes")
        set objMessages = objFolder.Messages
 
                   For j = 1 To objMessages.Count
                       Set omsg = objMessages.Item(j)
                       theEmail = omsg.Sender
 
                       If AdditBool(theEmail,Connstring) = True Then
                               Call Addit(theEmail,Connstring)
                   End if
 
                       For each recipient in omsg.recipients
                       If AdditBool(recipient,Connstring) = True Then
                          Call Addit(recipient,Connstring)
                   End if
                       Next
                    Next
        Exit For
    End If
Next
 
Function AdditBool(theEmail,theConnString)
 
                       If InStr(theEmail,"@") = 0 or InStr(theEmail,"netnerds") > 0 or InStr(theEmail,"mymomma") > 0 or InStr(theEmail,"'") > 0 Then
                           AdditBool = False
                           Exit Function
                End If
 
                 Set rsEmailCheck = CreateObject("adodb.recordset")
                       strSQL = "select id from newsletterSubs where email = '" & theEmail & "'"
                       rsEmailCheck.Open strSQL, theConnString, 1, 1
                           If rsEmailCheck.eof and rsEmailCheck.bof Then
                               AdditBool = True
                           Else
                               AdditBool = False
                               Exit Function
                           End If
                       rsEmailCheck.Close
 
                       strSQL = "select id from newsletterNoSubs where email = '" & theEmail & "'"
                       rsEmailCheck.Open strSQL, Connstring, 1, 1
 
                       If rsEmailCheck.eof and rsEmailCheck.bof Then
                               AdditBool = True
                           Else
                               AdditBool = False
                               Exit Function
                           End If
                       rsEmailCheck.Close
                       Set rsEmailCheck = Nothing
End Function
 
Sub Addit(theEmail,theConnString)
                   Set Conn = CreateObject("adodb.connection")
                      strSQL = "insert into newsletterSubs (email,dateAdded,ipaddr) values ('" & theEmail & "','5/24/2004','10.0.0.102')"
                       Conn.Open theConnString
                       Conn.execute StrSQL
                       Conn.close
                   Set Conn = Nothing
                   'MsgBox theEmail
End Sub