Glen Smith

It’s a little-known fact that I’m a part-owner of one of the most uncool Grails startups in the known world. We don’t have Webscale issues, we don’t have staff beanbags, in fact we don’t even offer a cloud-based solution. We install Grails-based software on hosts inside corporate environments. We do compliance software. Like Health and Safety and IT Security stuff. Nerdy in the extreme. But also very fun to work on, and, shock-horror, a sustainable business from year one!

Lots of our customers are “Microsoft Shops” from soup to nuts - SQL Server, Active Directory, Internet Explorer - you know the drill. So we’ve been working hard to make our Grails-based offering run just lovely alongside existing Enterprise apps.

Grails and SQL Server

First things first, we’ll need a SQL Server JDBC driver so we can play nice with common SQL Server versions (viz. 2005/2008R2). We evaluated a couple of Microsoft SQL Drivers including: the Microsoft One, and the jTDS one. Both worked fine, but we ended up going with the jTDS one since it seemed faster in our smoke testing. And it was in a Maven repo.

jTDS DataSource Configuration

We experimented for this for for quite some time to get everything just so, so I’d thought I’d take some notes for you to to lean on. First of all, if you’re going dataSource config, you’ll want something like this:

dataSource {
    pooled = true
    dialect = org.hibernate.dialect.SQLServer2008Dialect
    driverClassName = "net.sourceforge.jtds.jdbcx.JtdsDataSource"
    url = "jdbc:jtds:sqlserver://yourhost:1433/GMARC;useNTLMv2=true;domain=yourDomain"
    dbCreate = "none"
}

There are some excellent docs on the URL Formats for jTDS but I found the useNTLMv2 switch was essential in the client’s environment. Apparently v1 (the default) is full of security holes, and there are standard NT group policies that disable NTLMv1 entirely. I also found that the domain switch needed to be provided otherwise we were hosed.

Going Managed

If you’re walking the Tomcat-managed DataSource, you’ll need to use the matching context.xml version of the above:

<Resource name="jdbc/gmarc" auth="Container" type="javax.sql.DataSource"
        driverClassName="net.sourceforge.jtds.jdbc.Driver"
        url="jdbc:jtds:sqlserver://yourhost:1433/GMARC;useNTLMv2=true;domain=yourDomain"
        maxActive="20"
        maxIdle="10"
        validationQuery="select 1" />

Then, of course, you’ll make your configuration datasource of the JNDI variety… Perhaps something like

dataSource {
    jndiName = "java:comp/env/jdbc/gmarc"
}

One of the advantages of using the Container-managed variety of DataSources is that you can run somethink like PSI-Probe (a fork of the old Lambda probe) to validate your data source connections without going anywhere near your Grails config. Useful stuff. We’ll do more of that.

A Note on SQL Server Port Weirdness

If you’ve had a few SQL Server installs concurrent on your Dev box, the SQL port will move off the standard 1433 and onto some random port. We’ve had issues on our dev machines with this one, so head in the SQL Server Config Manager, have a look at the Network Configuration/Protocols/TCPIP then scroll to the bottom of the IP Address and find the TCP Dynamic Ports setting you need. What a snack :-)

Integrated Security & Sticking Files Where They Belong

You’ll notice neither of the above has username or passwords. Most SQL Server Admins despise Mixed Mode security (where you setup special usernames and passwords inside SQL Server itself). Microsoft has gone to the trouble of writing security white papers telling you to turn off Mixed Mode, so Integrated Security is your best friend in most MS enterprise shops.

With Integrated Security, you run your Tomcat service as a Active Directory Domain Account, then your credentials are remoted to SQL Server automatically. To get jTDS to work that way, you’ll want to make sure that you have place the DLL that ships with the driver in the right spot.

You’ll want to dump your jtds-1.2.5.jar file into <TOMCAT_HOME>/lib, but if you want to use Integrated Security, you’ll need to put that ntlmauth.dll file somewhere too. It has to be somewhere in your Windows path (we normally use Windows\system32), but you can happily put it into <TOMCAT_HOME>/bin and it seems to find it.

Word to the Wise: You need to match the right version of the DLL to your target operating system. I foolishly copied the 32bit version of the DLL onto a 64bit Windows install and wondered why I didn’t get a connection (nor did I get an error message that helped me out, so be warned).

Further Word to the Wise: We had to run the Tomcat service using the “@” format of domain account. So instead of “yourDomain\yourAccount”, we ran the service as “yourAccount@yourDomain”. Didn’t even know that worked! I’m such a MS noob these days..

Enough SQL Config. It’s time to have a look at AD integration.

Grails & Active Directory

Given that we’re using the amazing Spring Security plugin for Auth, it was a no-brainer to head down the Spring Security LDAP plugin path and tune things for Active Directory. Our config is pretty vanilla per the sample docs, but we externalise it so we can tune it after deployment:

// Turn this switch on to enable Active Directory/LDAP Integration
grails.plugins.springsecurity.ldap.active = false

// LDAP config
// In particular, the Admin Account is only required to located the full DN of the user within the search base.
// Once that's found you can just bind as the real user.
grails.plugins.springsecurity.ldap.context.managerDn = 'CN=LdapSearchAccount,OU=My Users,DC=bytecode,DC=com,DC=au'
grails.plugins.springsecurity.ldap.context.managerPassword = 'yourClearTextPasswords'

// User-specific LDAP Configuration
grails.plugins.springsecurity.ldap.context.server = 'ldap://dir1.bytecode.com.au:389/'
grails.plugins.springsecurity.ldap.authorities.ignorePartialResultException = true // typically needed for Active Directory
grails.plugins.springsecurity.ldap.search.base = 'OU=My Users,DC=bytecode,DC=com,DC=au'
grails.plugins.springsecurity.ldap.search.filter="sAMAccountName={0}" // for Active Directory you need this
grails.plugins.springsecurity.ldap.search.searchSubtree = true
grails.plugins.springsecurity.ldap.auth.hideUserNotFoundExceptions = false

// Role-specific LDAP config
grails.plugins.springsecurity.ldap.useRememberMe = false
grails.plugins.springsecurity.ldap.authorities.retrieveGroupRoles = true
grails.plugins.springsecurity.ldap.authorities.groupSearchBase ='OU=My Groups,DC=bytecode,DC=com,DC=au'
grails.plugins.springsecurity.ldap.authorities.groupSearchFilter = 'member={0}'

Determining DNs

We found that getting the right DNs for Groups and Users was a bit of a pain. Lots of the samples use CNs for the different containers, but in the wild we have found that OUs rule the roost. We found that Microsoft’s AD Explorer tool was a good way to work our the correct DN for the containers for users and groups (spaces in names seem to work fine too - yah!).

Our only snag was that cleartext manager password. I should get motivated and write a patch for that (following the same patterns as the encrypted datastore passwords).

We use AD-based ROLES for all our app-based security stuff. Creating groups in Active Directory called “GMARC_Admin” will result in the mapping becoming ROLE_GMARC_ADMIN once the user authenticates, which makes sense, so cater for that in your mapping. We ended up using DB-based security mapping rules so we can reconfigure the app’s security after deployment. Turns out that was a good decision!

What about Browser-based SSO?

We’ve been toying with the idea of a full SPNEGO/Kerberos SSO deal, but have yet to make that leap. For starters, our clients were happy enough to be able to login to our app using their username/passwords (this is going to be a problem down the track for agencies that use some kind of token-based logon, so we’ll probably go full-on Kerberos in our next major rev and deal with the browser-fallout when it happens!).

How’s it working out for you?

Once we’d shaken down the basic config problems, and come up with some diagnostic tools, we’ve been pretty happy with the result. SQL Server seems to perform pretty snappily. And Tomcat runs just great on Windows. Only only big hurdle left is a nice Windows-based installer. Sounds like a 1.1.x feature :-)