CODEWITHSUNDEEP
sql-server-2008
Richard Jefferies
Richard Jefferies

Reputation: 31

Connection String for Remote Connections to SQL Server 2008

I am trying to connect remotely to SQL Server 2008 R2 Express on a server running Windows Server 2008 R2. I receive the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

I think the problem might be related to my connection string, because I am not sure exactly what it should be. I understand the connection string should take the form:

Data Source=123.123.123.123\InstanceName;Initial Catalog=MyDBName;user id=MyUserName;password=MyPassword;

The instance name of my SQL Server installation takes the form ComputerName\SQLEXPRESS. This is what I see when I log in to Management Studio on the server. So should the connection string begin with 

Data Source=123.123.123.123\ComputerName\SQLEXPRESS

This looks wrong because of all the slashes. I have tried to omit the computer name thus:

Data Source=123.123.123.123\SQLEXPRESS

I also read somewhere that you can specify a port so I have also tried

Data Source=123.123.123.123,1433\SQLEXPRESS

because I understand that 1433 is the TCP port used by SQL Server and

Data Source=123.123.123.123,1434\SQLEXPRESS

because I understand that 1434 is the UDP port used by SQL Server browser.

I have also tried every combination of these and they all give the same error.

These are the steps I have taken to enable remote browsing on the server:

  1. In Management Studio, right click the instance, go to Properties, Security and check SQL Server and Windows Authentication Mode. On the connections tab I have checked "Allow remote connections to this computer".

  2. In SQL Server Configuration Manager I have enabled all four connection options (Shared Memory, Named Pipes, TCPIP and Via) under every node they occur. Under protocols for SQL Express I have tried specifying port 1433 and also leaving it blank with TCP Dynamic Ports set to 0 (which I think is meant to enable dynamic ports).

  3. I have created firewall exceptions for TCP Port 1433, UDP Port 1434, and program exceptions for sqlservr.exe and sqlbrowser.exe.

If someone could tell me which of the four versions of the DataSource part of the connection string I should be using it would be a great help, even if it did not solve the problem. It would mean that everything I try subsequently I would only have to test once instead of four times.

Thanks in advance for any help!

Upvotes: 1

Views: 15438

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

Where did you enter the firewall exceptions, on your machine, on the server, or both?

The second connection string is the only really valid one. Can you telnet to the server on port 1433? Can you connect to the instance from Management Studio remotely and the problem is only from your code where the connection string is? Did you try running the same code on the server? Did you try forcing TCP/IP (vs. named pipes/shared memory etc) adding the following parameter to your connection string:

Network=DBMSSOCN;

This question comes up quite a lot and I'm sure you're hitting something that others have hit before. Have you gone through this article, "How to troubleshoot connecting to the SQL Server Database Engine"? Also there are 20+ questions on here that mention this error message and Express, according to this search result. I suggest double-checking your settings against some of the items in those answers that solved the issue for other users.

Upvotes: 1

Related Questions