Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

7 Answers

up vote 4 down vote accepted
  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.

share|improve this answer
    
This is mainly right. However the firewall configuration didn't like just enabling ports 1433 and 1434. Rather I had to add an exception for the sqlbrowser and sqlserver executables. –  Rister Aug 27 '09 at 12:12
    
usually I dont use windows firewall on a server anyway... i prefer a hardware firewall. –  djangofan Nov 24 '10 at 5:39
add comment

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.

share|improve this answer
    
+1 I got a warning from SQL Server when I installed it on my home DC telling me that after installing SQL I would be unable to demote the DC. –  pipTheGeek Aug 25 '09 at 19:20
add comment

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!

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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