How can I make MS SQL Server available for connections?

Question

I'm trying to connect to MS SQL Server (running on my machine) from a Java program. I'm getting the following long winded exception:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

When I check "Properties" and click "View Connection Properties" in the Object Explorer of MS SQL, I find that the "Server is Unavailable." This seems possibly related to the exception message.

How can I make the server available?

Edit:

I am using SQL Server 2008, and I have now enabled TCP/IP, and restarted my instance. I am still told that "Server is unavailable."

Any other ideas?

Answer 1

Open up Sql Server Configuration Manager. ( Start | Programs | Whatever version of sql server | Configuration Tools)

Browse down to 'SQL Server Services' and restart your instance. However you could do this in Management Studio by right clicking on the instance and selecting restart.

If your restart fails then, check out Computer Management | Event Viewer | Application and look for sql server events. it will record successful and error messages here.

Answer 2

Depending on the version of SQL Server you're using, it may well be set up only to use named pipes by default.

You need to enable TCP/IP connections to the server. If you can tell us which version of SQL server you're using, we can give more precise instructions for how to enable TCP/IP.

Answer 3

Before messing about with connections, first check that the SQL Server Service is actually running.

You can do this by either using the SQL Server Configuration Manager (located in the configuration tools folder) or in the standard services console in the Windows control panel.

Once you have checked the service is up and running, you need to ensure that SQL Server has been configured to allow remote connections.

See below for an explanation on how to do this:

http://support.microsoft.com/kb/914277

Answer 4

First off, check that the sql server service is running. If you're using SQL 2005 or 2008, check Configuration manager (2008) or Surface are configuration tool (2005) to make sure the TCP/IP protocol is enabled and TCP/IP connections are allowed. With SSE(express) these are off by default, which would cause your problem. Also just in case you're running multiple instances, you may need SQL browser service running. If this is the case, you should be able to connect object explorer by using (local) as the server address, since this will use a local/shared memory connection.

Answer 5

As usual, check the obvious: 1. See if your router has ports 1433 and 1434 enabled (these are the defaults for IP/UDP communication) 2. Ensure that if you have a firewall in place on either the client or the server, that said ports are not blocked (this is the one that was my "gotcha"). 3. Go into Sql Configuration Manager and under Sql Server Network Configuration, ensure that TCP/IP is enabled. Also, in the same program under Sql Native Client 1.0 (if installed) that it, too has TCP/IP enabled.

A cheap and sleazy way to ensure that all of your connection information is working is to create a .udl file (anywhere on your system), fill in the appropriate information and hit "Test Connection". If there is something wrong there, you'll get a (fairly) informative message as to the problem. If you don't know about .udl files, right click in Windows explorer (right side pane) to create a "New Text File". Type any acceptable name but change the extension from .txt to .udl. You will be asked to confirm the extension change, answer yes. Once created, click the file (or double click if you don't use single click) to open it. There are 4 tabs. The first allows you to select the provider. For Visual studio use the Sql Native client, for any other program try the standard Sql Server Provider. On the second tab you can fill in the server name, sql server user name and password (if used), etc. Then test the connection. One other item to note - if you are using Windows Authentication, ensure that those users have been added to the Server Logins.

Answer 6

Depending on the version of SQL Server you're using, it may well be set up only to use named pipes by default.

You need to enable TCP/IP connections to the server. If you can tell us which version of SQL server you're using, we can give more precise instructions for how to enable TCP/IP. link|flag answered Jul 6 at 12:49 Jon Skeet 101k●18●358●779

yeah, along with this, you can even check what Chad has suggested to check if the instance is running. – Kunal S Jul 6 at 12:51
I'm using version 2008, and I think I've enabled TCP/IP now, but no improvement. – FarmBoy Jul 6 at 13:14

@Farmboy - I've had the same error troubling me this morning. Figured that not only must you check if the TCP/IP is enabled but that you also check the properties and make sure all the ports have been correctly configured else you would have to use the dynamic port for access. * Remember if the ports aren't configured in the 1st place, how would you be able to connect any in any case?!

Answer 7

From 2005 and up the SQL server browser service has te be running.
That one fooled me many times.

Answer 8

check this link

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Answer 9

Did you manage to resolve this as I have the exact same problem.

Answer 10

I ran into this problem as well. The MSKB article applies to SQL server 2005.

As the "SQL Server Surface Area Configuration" tool has been dropped in lieu of "Facets" - this wasn't obvious to me.

I resolved this by setting the TCPAll port and enabling the relevant IP.

---

Steps

1. Open the Sql Server Configuration Manager (Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools)

2. Expand SQL Server Network Configuration -> [Your Server Instance]

3. Double click TCP/IP

   • Under Protocol:

     Ensure Enabled is Yes

   • Under IP Addresses:

     Scroll to the bottom and set the TCP Port under IPAll, (1433 by default)

     Find the IP address you want to connect to and set Enabled and Active to Yes