3

I have both java and .net applications running on an app server using Microsoft Windows Server 2003, Enterprise Edition. These are being migrated to another app server Windows 2008 64-bit machine.

All applications connect to the same SQL Server 2005 database, on a named instance.

So far I have tried to move the applications exactly as they are, with no changes in the configuration files, from the old box to the new box.

On the new app server, neither the java nor the .net applications connect to the database (named instance).

JDBC error message: "The connection to the named instance has failed. Error: java.net.SocketTimeoutException: Receive timed out."

The .net error message: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible."

If I change the DB config to one that is not on a named instance it works on the new app server.

The database is setup correctly, because we were able to connect on the old app server. I can ping the database server from the new app server.

Is there any reason this won't work on the new app server?

java connection string: "jdbc:microsoft:sqlserver://[dbservername];SelectMethod=Cursor;instanceName=[dbinstance]"

.net connection string: "Server=[dbservername]\[dbinstance];Database=Risk_DB;Uid=[user];Pwd=[pwd];"

UPDATE Per suggestions in the answers, I got the instance port number. I also installed SQL Server Management Studio so I can eliminate my apps as the problem points. From Management Studio, if I use [servername][instancename], I get the message "a network-related or instance-specific error while establishing a connection to SQL Server..." But it works when i use [servername],[port]. Not sure if there's anyway to work around this?

UPDATE #2 Escalated the issue to the infrastructure/server/network team. They disabled windows firewall on the new app server. Presto, now I can connect to [dbservername]\[dbinstance] in Management Studio, and all apps are working using existing configuration files.

Community
  • 1
  • 1
Lost In Code
  • 494
  • 1
  • 5
  • 18

2 Answers2

6

Your named instance is going to be running on a different port. Port 1433 (the default for a default instance) is probably open, and the port that the named instance is running on is probably blocked. You can check the port in the error log for the named instance (assuming you can connect locally, in Object Explorer, expand the server, expand Management, expand SQL Server Logs, right-click current, and choose "View SQL Server Log" IIRC), it will say something like this on startup:

Server is listening on [ 127.0.0.1 <ipv4> 3587 ].

That last number is the port number that needs to be accessible from your remote machine and whatever network devices and services it has to go through to get there. If you don't find a line like that, it's possible that TCP/IP is not enabled for the named instance. On that server, go to SQL Server Configuration Manager, expand SQL Server Network Configuration, click on "Protocols for " and make sure TCP/IP is enabled in the right pane. If you have to enable this you'll need to restart SQL Server for it to take effect.

If it is already enabled (or once you enable it and restart the service), you should be able to refresh this view and validate the port that is being used if you right-click TCP/IP, hit properties, and move to the IP Addresses tab. You can see the ports currently being for each IP. Here there will be multiple IPn sections and an IPAll section. For each IP, you can change the "TCP Port" box to a port you want to use (and delete any values in all the "Dynamic TCP Ports" box to 0). Hit Apply and restart the service. This will again require a restart of the service but will allow you to specify a specific port so you can add an exclusion to your firewall (or make use of one that already exists, assuming this server isn't already using that port).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Just for further confirmation on something to check - it could be the case that the old box has an explicit exception in the firewall that allows it to connect to the database server (and this might be my IP address or by name). If this is the case then please ignore my advice about changing / fixing the SQL Server port and talk to your network admin about getting the same firewall exception put in place for the new box. – Aaron Bertrand May 01 '12 at 00:13
  • It could also be the case that the SQL Server Browser service is running on the old box (and thus dynamically routing named instance requests to the correct port) but not running on the new server. – Clark May 01 '12 at 01:34
  • @Clark you shouldn't need a SQL Browser service on the application server to do any of that. I don't think it's 100% obvious from the question, but it sounds to me like the app has moved, not the database. The "old server" that can still connect to the database is the old application server. – Aaron Bertrand May 01 '12 at 01:37
  • My mistake, looks like I misread the question. Reading it again a little more closely, I agree with your interpretation and the SQL Browser service doesn't come into play. – Clark May 01 '12 at 02:06
  • Edited the question to clarify that Aaron Bertrand's interpretation is correct - only the app is moving, not the db. Thank you all for the suggestion on the port. I don't have access to SQL Server Logs. I have to ask the DBAs for the named instance port, and will report back. – Lost In Code May 01 '12 at 13:23
  • As described in Updates in the question, the problem seems to be some other port, not just the one the instance is running on. Perhaps it's the port that resolves instance name to port. We just don't know which one. I'm accepting the answer because it led me down the right track. Thank you @AaronBertrand. – Lost In Code May 02 '12 at 16:56
0

Possible issues:

  • Firewall maybe blocking connections.
  • The instance name is not the same as specified in the connection string.
  • The connection string specifies a different port or SQL Server is running on a different port rather than the default 1433
Darren
  • 68,902
  • 24
  • 138
  • 144