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