Cannot connect to remote SQL Server Express instance

Question

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...)

Solution 1

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

Solution 2

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.

Comments

[Ropstah]

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...

[Exzlanttt]

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)

[unhappyCrackers1]

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?

[Ropstah]

@Alex: No the computers are on different networks...

[unhappyCrackers1]

Given that you have confirmed an instance listening on 1433, can you connect to that instance locally? From the SQL Server itself?

[Ropstah]

@Somantra: Yes, both [computername]\SQLEXPRESS as [computername]\SQLEXPRESS,1433 work.

[unhappyCrackers1]

If you can access it locally, have verified your ports and the ping response is good from the same machine which fails to connect to the service you know is there and operational, then this is usually the firewall. Can you temporarily remove the firewall from the picture? In my experience, this usually causes what you are seeing.

[Ropstah]

I've doublechecked the port availability through canyouseeme.org. The site confirms that port 1433 is open, when I remove the firewall exception it says that it is closed. So the firewall is really not the issue...

[Ropstah]

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]

The firewall is has sqlservr.exe added as exception as well as the portnumber that is in use (1114)...

[Ropstah]

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)

[Shadok]

Can you try connecting to the server using this: [ip_address],1433 ? Note the comma to specify the port instead of the usual colon.

[Ropstah]

For that I need to run the sql server browser service... I tried that by the way but nothing...

[Shadok]

hmm, strange, can you telnet that port and get a connection or is it rejected immediately ?

[Ropstah]

I don't know as my putty client closes immediately. However canyouseeme.org (port checker) validates that the port is open.

[Ropstah]

Telnet connection does NOT work from my PC! What's going on..?

[Shadok]

Did you mean to allow your port 1433 to be public ? If not maybe you just created FW rules opposite to those you wanted ? Until you can telnet your server on 1433 (and get a response) you should'nt bother with SQL Server settings.

[Ropstah]

If I RDP on the server and goto: www.canyouseeme.org and fill in portnumber 1433 I get a positive response (the port is open and available). However my local telnet instance says "Cannot connect to [server] [port] Connection failed."