For the past week I’ve been working on a change to an application at work.  Some ‘enterprisey’ person decided that we needed a central session state management scheme and SQL Server Mode was the silver bullet.  Not only did it give the IT department another way to throw the word Enterprise into their functionality reports, but it was an easy, low impact change.

The project (apparently this change did demand project status) kicked off and a few weeks of emails went around before anyone would even tell us if the database had been created.  At one point a DBA noted that the database creation was on his list and “…should take too long (3 to 4 hours)”.  Amazing considering that the script to do all this work usually only takes about 30 seconds to run in query analyzer, but I’m not a DBA so what do I know.

After being apprised of the database creation, we started changing our web.config files using the information that we were provided.  Immediately things didn’t work.  Any page that used session state threw the following exception details.

System.Data.SqlClient.SqlException: Invalid connection.

We fought with this for a while before a long string of emails started to get fired around.  After a few days of playing Pass-The-Email we were told that the SQL Server had been configured to use a port other than the default and that this was most likely our problem.  It took another week of relentless research (which turned nothing up) and a meeting of epic attendance to determine the cause of the problems.

If you need to hook up your web servers to a SQL Server that is not using the default port for communication you will need to use the following sqlConnectionString format.

data source=MySqlServerName,99999;user id=<myusername>;password=<mypassword>;

The key to note here is that you separate the SQL Server Name from the port number with a comma.  We thought it would be with a colon, but alas we were wrong.