Remote Access from Management Studio to SQL Server

You experience the following error message:

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)

error

Instrucions to enable MSSQL remote connecton.

Run SQL Server Configuration Manager.
start

Protocols for MSSQLServer

The next good thing to check is the SQL Server Network Configuration. Open the SQL Server Configuration Manager, unfold the node SQL Server Network Configuration and select Protocols for MSSQLServer (or whatever the name of your SQL Server instance is).

Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.

tcp

Make sure TCP/IP is enabled. Right-click on TCP/IP and select Properties.

enable

Now:

Select the IP Addresses Tab. Verify that, under IP2, the IP Address is set to the computer’s IP address on the local subnet.

ip
Scroll down to IPAll. Make sure that TCP Dynamic Ports is blank. Make sure that TCP Port is set to 1433.

ipall
Apply and Click OK.

Make Sure the SQL Server Browser is running. Go to Services and set the start to automatic. Then start the service.

Add a firewall rule to allow port 1433

fw

Make user Remote Connections are enabled on your SQL Server database.

Open SQL Server 2008 Management Studio, connect to the server in question, right click the server.

prop

Navigate to Connections and ensure that Allow remote connections to this server is checked.

remote

Other resources:

http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx
https://msdn.microsoft.com/en-us/library/ms191464.aspx

Leave a Comment