Connecting to SQL Server from java with TCP disabled

Question

I'm trying to connect to a local database (SQL Server 2008) from Java. I have disabled the tcp connections per customer requirements and I can't connect. I have to disable too the service SQL Server Browser.

I write the next statement in Java:

conexion = DriverManager.getConnection("jdbc:sqlserver://localhost\\SQLEXPRESS;user=user;password=password");

and I have the following error:

"java.net.SocketTimeoutException: Receive timed out". (then it tells me that probably there is a firewall and that I should run the SQL Server Browser).

If I try to connect from the Microsoft SQL Server Managment Studio and I can connect whith the same parameters:

Server type: Database Engine
Server name: localhost\SQLEXPRESS
Authentication: SQL Server Authentication
User: user
Password: password

I don't know if I'm doing something wrong i Java but SQL Server Managment Studio is actually a client, so if it can connect any client should can.

Please answer. If you need more information just ask for it.

Answer 1

Unfortunately, Microsoft's JDBC driver does not support named pipes connections to SQLServer. You can try finding and alternative JDBC driver to use.

Take a look at jTDS. It's free, open source, and it connects to SQLServer using named pipes.

Answer 2

I assume you are using the SQL Server Express version came with Visual Studio 2010. For other version there should be similar solutions but I have not tested. Here is the solution:

1. Enable TCP/IP protocol. Find "SQL Server Configuration Manager" from start menu, expand "SQL Server Network Configuration" and click on "Protocols for SQLEXPRESS", double click "TCP/IP" and change the "Enabled" property to "Yes". Check the "IP Addresses" tab and enable the IP addresses you wan to use (typically "127.0.0.1").

2. Enable user sa in SQL Server. Press Win+R, type in "sqlcmd -S .\SQLEXPRESS" and run the following commands:

   ALTER LOGIN sa ENABLE;
   GO
   ALTER LOGIN sa WITH PASSWORD='StrongPassword1!'
   GO
   

3. Change the login mode to enable explicit login. Press Win+R again and type in "regedit", find the following key

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
   

   and then change the value "LoginMode" to 2.

4. Test the configuration. Create a test connection in Visual Studio 2010, uses user name "sa" and password "StrongPassword1!". if you can connect you should be able to connect through JDBC as well.