Cannot connect to SQL Server
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?
Solution 1:
You need to enable the listener on port 1433 in the SQL Server Configuration Manager control panel.
Enable the "sa" user. It's disabled by default after the install.
Set a password on the "sa" user.
Enable SQL Authentication + NT Authentication mode.
Make sure the Windows firewall isn't blocking port 1433.
Try the default instance name instead of SQLEXPRESS.
Make sure the remote connection is using TCP/1433.
Solution 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.