0

I have been trying to install SQL Server Express for a few days now. I tried 2008, 2012, and now 2014 all with the same result. I cannot connect to the database remotely, even if I turn the firewall on the Windows 2012 Server off. I get 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

The server is setup to listen on port 1433 and TCP/IP and Named Pipes are enabled and show "ready to accept connections." The log does have an issue about the SQL Server Network Interface library not being able to register the Service Principle Name, but from my research that should not stop me from logging in remotely. SQL Server is configured to allow remote connections.

Windows Firewall is setup to allow connections on port 1433 and has SQL Server Browser unblocked. I followed all the instructions in this MSSQLTips.com article and everything checks out except for there being an error about SPN creation. I have tried to manually create the SPN, but the proper parameters elude me. There is no domain so I tried to use WORKGROUP\Administrator to run setspn -L WORKGROUP\Administrator, but that throws an error about that username not being found.

It is possible my remote connection settings are just wrong. I am trying to connect to the IP of the server SQL Server Express is installed on. Not sure what could be wrong about that.

freginold
  • 239
  • 1
  • 7
deflator
  • 11
  • 4

3 Answers3

1

I got an answer back from the hosting company that hosts our VPS and, lo' and behold, they block port 1433. They gave me a different port to use and all is well! Thanks to everyone who commented.

Not sure why dynamic ports did not work when I tried it, but it may be that SQL Browser could not get through the firewall either on UDP port 1434 to do its job and tell the connection what port SQL Server was listening on.

deflator
  • 11
  • 4
0

I had the same problem connecting a PC to a SQL database located on server. After trying all the same thing you did, I found a solution which was so simple I couldn't believe it worked.

What you need to do on your machine is: 1. run "cliconfg" and enable Named Pipes and TCP/IP protocols 2. run "obdcad32.exe" and add a default named pipe connection to your server (sometimes you have to do it twice in odbcad 64bit)

There is no need to change server setting any further.

Regards, vlku

Pawel Wilk
  • 21
  • 3
  • Names Pipes and TCP/IP were disabled when I ran cliconfg. I enabled them. I tried adding connections in obdcad32 but it did not change the result. I added several named pipe connections and a tcp/ip one. – deflator May 05 '16 at 21:37
  • It is possible you changed something important on SQLserver. Try to rollback the changes you made before trying my solution. Did you create a backup before tweaking server settings? – Pawel Wilk May 06 '16 at 11:16
0

It could be occure due to some dll file missing, you can also connect to your server without using instance name you have to reconfigure your server to use static TCP port.

  • open SQL Server Configuration Manager;

  • switch to the SQL Server Network Configuration | Protocols for
    SQLEXPRESS;

  • double-click the TCP/IP protocol;

  • select the Yes value in the Enabled field;

  • switch to the IP Addresses tab;

  • find the IPAll section;

  • clear the TCP Dynamic Ports field in that section;

  • specify the 1433 value in the TCP Port field:

  • restart your server.

Try to connect to your server using just its IP address.

Jason Clark
  • 103
  • 5