Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to connect to a SQL Server Express database instance on a remote server. I've taken the following steps to make this possible:

  • Enabled Windows AND Sql authentication
  • Allowed for remote connections
  • Enabled TCP/IP
  • Enabled Named pipes (\.\pipe\MSSQL$SQLEXPRESS\sql\query)

For Windows Firewall I tried the following:

  • Added [path]\Binn\sqlservr.exe to exceptions
  • Added port 1433 to exceptions
  • Checked port in SQL Server log to which it is listening (1114) and added that to exceptions

Then I tried to connect through Sql Server Management Studio using the following server names:

  1. \[ipaddress]\pipe\MSSQL$SQLEXPRESS\sql\query
  2. [ipaddress]\SQLEXPRESS

Both fail with the error shown below. Am I missing something here?

Connect to Server: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

(I can ping the server by the way...)

share|improve this question
    
Did you specify the SQL Server listening port? The default port for the default instance (SQLEXPRESS) is 1433. So if this is the case, you should be able to identify the listening port on the server using a command such as: netstat -noab >"%userprofile%\Desktop\netstat.txt" A non-default instance will not have a listening port number assigned by default and this could explain your connection failure. –  Somantra Sep 7 '11 at 13:54
    
I have tried explicitly setting the port (1433) as well as checking the dynamically assigned port (1114). I also tried to connect through Telnet, but that fails also... –  Ropstah Sep 7 '11 at 14:02
    
Are the client and servers on the same subnet? Did you try connecting with the ODBC Administrator? Start => Control Panel => Open Administrative Tools => Data Sources (ODBC) –  Alex Sep 7 '11 at 14:06
    
So in the SQL Server Configuration Manager, you went to the instance properties > IP Addresses tab and set the IP All | TCP Port to 1433 and then restarted the service? Did you happen to verify your config with the netstat command? –  Somantra Sep 7 '11 at 14:08
    
I can see: TCP 0.0.0.0:1433 [sqlservr.exe] in the netstat command. –  Ropstah Sep 7 '11 at 14:30

3 Answers 3

I think you should specify the port used if you're not using the SQL Service Browser (which I think is not running on your machine).
As explained here you have two choices, use the SQL Service Browser or specify the port explicitely.

share|improve this answer
    
Somehow I'm unable to start the SQL Server browser... I tried connecting using: [ipaddress]\SQLEXPRESS, 1114 but then I receive a different error (shortened): 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 - ....) (Microsoft SQL Server, Error: 10060) –  Ropstah Sep 7 '11 at 13:09
    
The firewall is has sqlservr.exe added as exception as well as the portnumber that is in use (1114)... –  Ropstah Sep 7 '11 at 13:10
    
Can you try connecting to the server using this: [ip_address],1433 ? Note the comma to specify the port instead of the usual colon. –  Shadok Sep 7 '11 at 13:32
    
For that I need to run the sql server browser service... I tried that by the way but nothing... –  Ropstah Sep 7 '11 at 14:03
    
hmm, strange, can you telnet that port and get a connection or is it rejected immediately ? –  Shadok Sep 7 '11 at 15:00

Try to connect using TCP/IP and not named pipes.

Is the instance name that you are using correct? If you installed SQL Server as a default instance, you won't need to add \SQLEXPRESS in the server name, just use the IP.

share|improve this answer
    
I tried following these: blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx exact instructions, but the same error occurs: 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 - ....) (Microsoft SQL Server, Error: 10060) –  Ropstah Sep 7 '11 at 13:29
up vote 0 down vote accepted

I had to allow SQL Server Management Studio Express in my firewall. I think outbound connections were being blocked. It works now!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.