Setting up SQL Server 2008 Express to work with Grails

If you'll find yourself in need to configure a SQL Server database connection for your next grails application and you'd like to use the Express edition for development here's how you can do it:

1. Change login mode:
a) open regedit
b) navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
c) change the value of LoginMode from 1 (windows authorization) to 2 (mixed mode)

2. Enable TCP/IP connectivity:
a) open the SQL Server Configuration Manager (this one comes with the SQL Server itself)
b) click on "SQL Server Network Configuration / Protocols for SQLEXPRESS
c) right-click on TCP/IP and select "Enable"
d) double-click on TCP/IP, go to second tab (IP Addresses"), scroll down to the bottom of the property list and in the "IP All" section fill in the blank "TCP Port" with 1433

3. Restart SQL Server:
a) select SQL Server Services
b) in the list to the right select SQL Server (SQLEXPRESS)
c) right-click it and select "Restart"

The next step requires any form of ability to execute SQL statements against the server. What I used was the Visual Web Developer 2010 and its "Database connections" view.

4. Enable "sa" user:
a) ALTER LOGIN sa ENABLE
b) ALTER LOGIN sa WITH PASSWORD = ''

5. Create a database:
a) Create database your_database_name_here

6. Download and install the JDBC driver for Microsoft SQL Server (you just need the sqljdbc4.jar in your lib folder)

7. Configure your DataSource.groovy:

driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
dialect = "org.hibernate.dialect.SQLServerDialect"
url = "jdbc:sqlserver://localhost:1433;databaseName=your_database_name_here"
username = "sa"
password = "your_password_as_defined_in_4b"

With all that in place everything should work just fine :)

You might wonder why I've decided to mess around in windows registry instead of using proper tools (like the SQL Server Management Tools or whatever..). The answer is quite simple: It felt like a total overkill to install this humongous tool just to set one property :)