McNelis.biz

So I was working on a project last night where I needed to set up a MS SQL Server 2005 database on my web hosting companies servers.

Setting up the database intitially was no problem. Helm (the companies web configuration software) is pretty straightforward when it comes to creating a DB instances and a user.

I started to run into problems when I started trying to connect SQL Express to the remote database. I had everything in there that I thought I needed. I had changed my server address to the IP address my provider gave me, and I had the port set up on there as they asked as well. An example of what I had in my window for the server is this:

127.0.0.1:1501

Put in my username and password. No dice. I tried making changes, one by one, to every possible connection variable. I was banging my head against a wall, when I found a bit of documentation over at MSDN that showed a connection string formatted like this:

127.0.0.1, 1501

A comma? A friggin comma to specify the port? That is stupid. Especially when every other DB I’ve worked with worked based on a colon “:”. Clint mentioned that it might have something to do with the connection string from an application. It seemed viable, after all, you will sometimes see u:username p:password in a connection string. So when I set my project up in Visual Studio I took a look at my connection string. Everything was set with equal signs…I’ll try to post a screen shot later if I remember…so the comma is completely pointless.

MS, I appreciate your tools at this point, but it took me 2 hours to find this solution. Hopefully this page will pop up on a search result for MS SQL Server 2005 Connection String Problems, so others don’t go through the same waste of time that I did.