Cannot connect to SQL Server

Question

I'm having a problem connecting to my SQL Server Express instance. I can connect to the sa account from the local machine but as soon as I try to connect from a separate workstation, the connection fails with the following error.

TITLE: Connect to Server

Cannot connect to SERVER1\SQLEXPRESS.

---

ADDITIONAL INFORMATION:

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: 1311)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=1311&LinkId=20476

---

BUTTONS:

OK

I had previously been able to connect without issues, but there were some changes made to the server (it was turned into a domain controller), and it is now refusing to work as I had assumed it ought.

I've tried restarting the SQL Server Browser instance and I've already checked the firewall settings. However I won't rule out having missed something.

I'm stymied and the database needs to be up 20 minutes ago.

How do fix this problem?

Answer 1

1. You need to enable the listener on port 1433 in the SQL Server Configuration Manager control panel.

2. Enable the "sa" user. It's disabled by default after the install.

3. Set a password on the "sa" user.

4. Enable SQL Authentication + NT Authentication mode.

5. Make sure the Windows firewall isn't blocking port 1433.

6. Try the default instance name instead of SQLEXPRESS.

7. Make sure the remote connection is using TCP/1433.

Answer 2

When SQL Server was installed, it was most likely set to run as a network service or a local service account, which will not work properly on a domain controller. Whilst SQL Server can run on a domain controller (not recommended for security reasons), it should only be installed after it has been promoted. Likewise, demoting a machine will result in similar issues with SQL Server installed beforehand. I would suggest making a backup of your databases, then uninstalling SQL Server entirely, installing a fresh copy of SQL Server, and finally restoring your databases. That way, when you install SQL Server this time, it will detect the different user / security environment and offer only those security options that will actually work on a domain controller.

Here is a link to the official guidelines for installing SQL Server 2008 on a domain controller.

Answer 3

I just wanted to help someone out that might be ready to pull their hair out over this error at top of this page and others like it. If you have went through and completed all of the above steps and sifted through other websites but still getting this error, and you have checked, re checked, and checked your installation and settings again and again, get ready, here is the answer for probably 90% of you out there:

Pull up SQL Server Management Studio once more, (I know, I know, this time it will work, I promise), in the Server name: field, it probably says SQLEXPRESS, and that's it, but this time type the name of your computer then a backslash in front of SQLEXPRESS, so that it reads something like JOHNDOEPC\SQLEXPRESS, then with everything else filled in appropriately click Connect, and, voila!

I do not know why Microsoft did not make this more apparent for its end users, but they should have. This took me a long time to figure this out for myself after having gone through more than a dozen websites offering remedies, none of which worked…

Oh well, enjoy!

Answer 4

If none of the above works for you and you are sure that you have the correct credentials, the SQL Server service may have been stopped. So you may have to start it manually by:

Click Start -> in the search box, type services -> in the resulted services window, double click the appropriate SQL Server Item, such as SQL Server (MSSQLSERVER) -> Set Startup type to 'Automatic' then click Apply, -> Click 'Start' to start the process. You should be OK, then.

Jiayun Han

Answer 5

If you go into the Surface Area Configuration for Services and Connections, check to make sure remote connections are allowed, and that it didn't revert back to only allow local connections.

Answer 6

The Windows Firewall may not be configured to allow connections to the SQL Server. Be sure to check it for appropriate ports (1433 for TCP/IP).

Also the "provider: Named >Pipes Provider" component indicates that it is attempting to use named pipes to connect, I find it best to connect via TCP/IP from a separate workstation.

Answer 7

Do the SQL Server error logs indicate it's properly listening on the TCP port? When you run a netstat -ano, do you see the TCP port open and with a status of listening? Does the PID match up with sqlservr.exe?

Since it's a named instance, the SQL Browser service will need to talk on the network. That means udp/1434 should be opened up on the firewall, if it is blocked. If you see that the SQL Server Express instance is listening on the port, try connecting via servername,TCP port. For instance, if the instance is listening on 2601, try Server1,2601. If that works, then it's likely the SQL Browser service is being blocked. On that note, make sure that the port SQL Server is listening on is permitted in the firewall. And you'll probably want to make that a static port if it's currently set to dynamic.