I’ve been helping set up a new web site, we have two servers, all very off the shelf, both running Windows Server 2008, one with SQL Server 2008 as the database sever, the other acting as the web server.

The two boxes sit behind a firewall with only the necessary ports open (eg 80, 21, 443 etc). Windows FIrewall is turned on for both boxes, and on the SQL box we have port 1433 open but restricted to only the internal IP of the web server, so the web application can connect to the database.

The application is talking to the database using a standard connection string along the lines of:

Provider=sqloledb; Network Library=DBMSSOCN; Data Source=[sql server ip]; Initial Catalog=[db name]; User ID=[db user]; Password=[password];

We loaded up the site without major problems, but then started to notice this error occuring at seemingly random moments:

Microsoft OLE DB Provider for SQL Server error ‘80004005’
Login timeout expired

There seemed no rhyme nor reason, one minute you are browsing pages, the next up pops this error, then you refresh the page and all was well.

Clearly it’s a connection error between the web application and the SQL server. I dug into the Windows and SQL logs everywhere trying to correlate entries with the time the error occured, but to no avail.

There’s a fair amount online about this error, although it’s often tied to simple connectivity issues, such as incorrect IP numbers, server address, firewall settings and the like.

Finally I stumbled over this page ‘Troubleshooting: Timeout Expired‘ which gave the clue, and particularly:

‘Either start the SQL Server Browser service, or connect specifying the TCP/IP port number.’

We don’t have the SQL Server Browser service running – it’s not started by default. So I simply added the port number to the connection string and, fingers crossed, everything’s been fine since, eg:

Provider=sqloledb; Network Library=DBMSSOCN; Data Source=[sql server ip],[port]; Initial Catalog=[db name]; User ID=[db user]; Password=[password];

Actually I normally DO have the port number in the connection string – because I fell into the habit for a long time of running SQL on a non-standard port to avoid nasties with 1433 worms and the like in situations where I wasn’t so confident about the firewalling, or where we needed to leave the SQL Server exposed with IP or tunnel protection.