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.

  • From where are you trying to connect to it? From the same network? – joeqwerty May 5 '16 at 20:28
  • @joeqwerty From a remote network. It is on a VPS we rent – deflator May 5 '16 at 20:32
  • Is there a firewall between you and the SQL server (not including the Windows Firewall)? – joeqwerty May 5 '16 at 20:35
  • @joeqwerty I do not believe so. We do not have another one here at the office at least. – deflator May 5 '16 at 20:37
  • 1
    @KatherineVillyard Yes I changed it from dynamic to 1433. I also opened UDP port 1434 on the windows firewall – deflator May 5 '16 at 20:44
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.

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

  • 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 5 '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 6 '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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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