Tuesday, August 11, 2009

Can’t connect to SQL Server 2005 via JDBC, but can via SQL Management Studio

I found out the hard way that SQL Server 2005 Developer Edition has TCP/IP connections disabled. This could also be the case for any version of SQL Server 2005 as well. The behavior that you may see is that via SQL Server Management Studio you can connect to your database server just fine, but when you use some thing like JDBC that requires TCP/IP you can’t connect and get errors. For example, you might get a Network Error on Connect.

To enable TCP/IP connections on SQL Server 2005 Developer Edition (should also work for Enterprise, etc in case it is not configured with the default settings) do the following.

  1. Use remote desktop to access the server where the SQL Server service is running.
  2. Go to the Start Menu | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager.
  3. Once the window opens, expand the SQL Server 2005 Network Configuration. You should then see something like Protocols for MSSQLSERVER where MSSQLSERVER is the name of your SQL Server Instance. You should see something like this, except TCP/IP will have a red icon if it is disabled.:

  4. Right-click on TCP/IP on the right panel, and choose Enable.

Even if you enable or already had TCP/IP enabled, you may not have remote connections enabled. To enable remote connections to SQL Server, do the following:

  1. Open Microsoft SQL Server Management Studio and connect to your desired server in the Object Browser.
  2. Right-Click the server and choose Properties.
  3. Click the Connections page. You should see something like the window shown below. The Allow remote connections to this server may NOT be checked as shown below. If it is not checked then you can only connect to the SQL Server from the machine where the SQL Server is running. Be sure to check the checkbox to enable remote connections.



No comments: