SQL Server – Connection String for Network Server

connectionsinstancesql serversql-server-express

I have a database on my computer running on SQL Server Express 2014, and I'd like other computers connected to my network to be able to connect to it using a C# program I wrote.

I've done the following:

1. turned off windows firewall. I know I don't need to do this completely, but I'm just debugging.

2. enabled TCP/IP in SQL Server Configuration Manager on the machine running the instance.

I can't change this for all machines. On the computer I'm trying to connect from, but not the computer hosting the database, I can't enable this. I'm not sure if that's a problem. However, on my machine I've enabled pretty much everything and restarted everything.

3. Remote connections are allowed

Using a .UDL file to test the connection, I found that on my computer, if I select MyComputerName as the server, I don't see my database listed. But if I enter MyComputerName\SQLEXPRESS, I do see the database and can connect to it with integrated windows security.

But if I log on another computer and use a .UDL to test the connection. I can connect if I enter MyComputerName as the server, but if I try to enter MyComputerName\SQLEXPRESS as the server, it tries really hard but then tells me

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Again, on my computer using the following connection strings work

Server=MyMachine\SQLEXPRESS;Database=MyDatabase;Integrated Security=True;
Server=MyMachine\SQLEXPRESS;Database=MyDatabase;Trusted_Connection=True;

But on another computer on the network, I get the 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)

But I know that the the server is allowed remote connections and that the instance name is running, so I'm stuck. Any ideas of where to go from here?

Best Answer

I found a solution. Maybe someone could expand on this, because I don't fully understand it.

When I opened Management Studio as MyMachineName\SQLEXPRESS which I normally do, attached the database there, I couldn't access it through MyMachineName\SQLEXPRESS in the connection string.

However, when I reopened Managment Studio as MyMachineName, attached the database there, and removed the \SQLEXPRESS from my connection string, I can now access it.

Problem solved.

Related Question