0

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

Improve this question
10
  • 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.
    – unhappyCrackers1
    Sep 7, 2011 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, 2011 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, 2011 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?
    – unhappyCrackers1
    Sep 7, 2011 at 14:08
  • I can see: TCP 0.0.0.0:1433 [sqlservr.exe] in the netstat command.
    – Ropstah
    Sep 7, 2011 at 14:30

3 Answers 3

Reset to default
1

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.

Improve this answer
9
  • 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, 2011 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, 2011 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, 2011 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, 2011 at 14:03
  • hmm, strange, can you telnet that port and get a connection or is it rejected immediately ?
    – Shadok
    Sep 7, 2011 at 15:00
0

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.

Improve this answer
1
  • 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, 2011 at 13:29
0

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .