I wasted too much time today trying to figure out why I couldn’t connect to my local Microsoft SQL Server 2005 Express using JDBC. There were a few issues:
1) Microsoft does not enable listening by TCP/IP by default. Go to SQL Server Configuration Manager. Expand SQL Server 2005 Network Configuration. Click on Protocols for SQLEXPRESS. Right click on TCP/IP and select Enable.
I thought I was done when I did that, but it still didn’t work! So I spent another frustrating couple of hours trying all sorts of different things. It drove me nuts because I was able to connect using the Management Studio Express through TCP/IP, but I couldn’t connect by JDBC. Why, why, why???
2) For greater than 10 years, the default port on Microsoft SQL Server has been 1433. It’s like how Oracle’s default port has always been 1521. But for SQL Server 2005 Express, they made the listening port dynamically defined by default!!! ARGH!!! So everytime SQL Server starts up, it listens on a different port! This works okay for Windows applications because they use the SQL Server Browser to look up the port number, but OS-agnostic components like a JDBC connection doesn’t utilize stuff like the SQL Server Browser for lookup. I configured SQL Server to use a statically defined port (1433, as it should be), restarted the service, and I was able to connect without a problem.
3) Another thing you should do is to either 1) turn off Windows Firewall or 2) if you really want to leave Windows Firewall on (as if it will really make your Microcrap OS more secure), you have to add an exception to allow sqlserver.exe to listen on ports.
I am usually not a religiously-fanatic Microsoft basher but today, I am. Microsoft sucks! I hate you!
Post a Comment