How to Connect to SQL Server 2012 via TCP/IP

Solve common connection issues and configure SQL Server for remote access

Posted by Hüseyin Sekmenoğlu on February 06, 2013 Backend Development

⚠️ Common Error Message

When attempting to connect to SQL Server, you may encounter the following 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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)"

If you receive this error, follow these steps to resolve it.

🔍 Step 1: Ensure SQL Server Accepts Remote Connections

  1. Open SQL Server Management Studio

  2. Right-click on the server name and select Facets

  3. From the dropdown list, choose Server Configuration

  4. Set the RemoteAccessEnabled property to True

🔄 Step 2: Check SQL Server Services

All SQL-related services should be running and set to start automatically. It is best to ensure all related services remain in a started state.

🔐 Step 3: Verify Connection String Format

Use a connection string similar to the following:

Public conn As SqlConnection = New SqlConnection("Data Source=192.168.0.2\SQLExpress;Network Library=DBMSSOCN;Initial Catalog=DatabaseName;User Id=sa;Password=MyPassword;")

Explanation:

  • Data Source=192.168.0.2\SQLExpress specifies the server address and instance

  • Network Library=DBMSSOCN instructs it to use TCP/IP

  • Initial Catalog=DatabaseName sets the target database

  • User Id and Password provide authentication credentials

🔥 Step 4: Configure Windows Firewall

  1. Go to Start > Run > Firewall.cpl

  2. Open the Exceptions tab

  3. Add the following items:

    • Port 1433

    • sqlbrowser.exe

    • sqlwriter.exe

    • sqlservr.exe

🌐 Step 5: Enable TCP/IP Protocol for SQL Server

  1. Open SQL Server Configuration Manager

  2. Navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER

  3. Double-click TCP/IP

  4. Set the Enabled option to Yes under the Protocol tab

  5. Go to the IP Addresses tab

  6. For each IP you want to allow, set its Enabled value to Yes

✅ Final Notes

This guide was written based on two frustrating experiences with the same error. Recording the steps here helps avoid repeating lengthy research next time. There is no special reason beyond that.