Can't connect to SQL Server 2014 Express

Question

I'm having trouble connecting to a freshly installed instance of SQL Server 2014 Express. I'm not even totally sure what information you would need to help, so please bear with me as I'm very much an amateur. If I can provide anymore information to help, I'd be glad to.

The error I get is always the same,

"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)"

The server currently running SQL Server 2014 Express is Windows Server 2008 R2, with all patches sent out by Microsoft. This is a personal server at my home, so I can do whatever needs to be done with it to make this work.

To connect, I've tried using Visual Studio Community 2013, as well as the System.Data.SqlClient namespace in C# and in PowerShell.

I've made sure it isn't the firewall causing the problem. I set the rules properly, and when it still didn't work, I turned the firewall off completely. Same issue, no connection.

Here are a few examples of connection strings I've tried in .NET. I've removed the server, instance, userid, and password from these strings, and I'm sure I have those correct

Data Source=SERVERNAME\INSTANCENAME;Initial Catalog=Requests;Integrated Security=False;User ID=USERID;Password=PASSWORD

Data Source=SERVERNAME;Initial Catalog=Requests;Integrated Security=False;User ID=USERID;Password=PASSWORD

Server=SERVERNAME\INSTANCENAME; Database=DATABASE; User ID=USERID; Password=PASSWORD

Server=SERVERNAME; Database=DATABASE; User ID=USERID; Password=PASSWORD

If anyone has the time and patience to help a newbie sort this out, I'd really appreciate it. I'm using this setup to teach myself some SQL and ASP.NET MVC 5 development, and having a minilab at home would be awesome.

Thank you for your time.

Answer 1

SQL Server Express does not have TCP/IP connections enabled by default, instead it uses Shared Memory which means that external/remote connections from another computer won't work.

Open SQL Server Configuration Manager (it's on your Start Menu) and choose Server configuration and enable TCP/IP. Also open the properties for TCP/IP and ensure you have both "Active" and "Enabled" set to True on the bindings you want to use.

Answer 2

Make sure that your username and password are correct in case of Server Authentication mode. Go to Sqlserver Management Configuration and start SQL server services if any are in stop mode.

Answer 3

First off, open services.msc and scroll down to SQL Server, and ensure its started.

If its not running modify to start automatically, and then start it.

Once it's running, close services panel, and run ssms.exe (management studio) and try connecting to Server Name: <machinename> or Server Name: <machinename\sql2014> [no <>]. You can also click the Server Name drop down list, and select BROWSE to see if your instance is listed - although I find this hit and miss.

Once you confirm its active and you are connected, follow this msdn page and follow the steps to enable tcp/ip connections.

From there you should have the details necessary to alter your connection string.

Answer 4

I had a similar issue with a C# connection string that was no longer working after we migrated from SQL Server 2008 R2 on a Windows 2008 R2 Server to SQL Server 2014 on a Windows 2012 R2 Server.

I opened SQL Server Configuration Manager and Enabled Named Pipes, restarted the SQL Server and all was right with the world (at least for a few minutes!)