[Part 1] Cannot connect to Server A network-related or instance-specific error occurred while establishing a connection to SQL Server

Hi SQL Server Folks,

On Windows Server 2008 R2 if you install SQL Server 2008 R2 then if you try to connect that SQL Server from outside of that server then you won’t able to connect it . What it does mean let me explian this. Suppose on Node1 (With OS Windows Server 2008 R2) you installed default instance of SQL Server 2008 R2. After this you did not opened the ports for this SQL instance. Now from Node2 If you try to connect this SQL Server instance then you will get a below error:-

This error is coming because of Port issue. By default for all the applications there is a firewall enable on windows 2008 R2 servers. So in this environment to access SQL Server from remote server we have to open the ports for SQL Server. Below are the commands to open the SQL Server ports for different features of it. Before opening the ports of SQL Servers you have to know the port numbers. In below I am specifying only default ports.

Below are the two methods to open the Default Ports of SQL Server apps.

Method 1 :- Open the command prompt as administartor and run the following commands.

+++++++++++++++         SQL Server Ports      +++++++++++++

netsh advfirewall firewall add rule name=”SQLServer” dir=in action=allow protocol=TCP localport=1433

netsh advfirewall firewall add rule name=”SQL Admin Connection” dir=in action=allow protocol=TCP localport=1434

netsh advfirewall firewall add rule name=”SQL Service Broker” dir=in action=allow protocol=TCP localport=4022

netsh advfirewall firewall add rule name=”SQL Debugger/RPC” dir=in action=allow protocol=TCP localport=135 

 

+++++++++++++++   Analysis Services Ports     +++++++++++++   

netsh advfirewall firewall add rule name=”Analysis Services” dir=in action=allow protocol=TCP localport=2383

netsh advfirewall firewall add rule name=”SQL Browser” dir=in action=allow protocol=TCP localport=2382

 

+++++++++++++++     Misc. Applications   +++++++++++++++ 

netsh advfirewall firewall add rule name=”HTTP” dir=in action=allow protocol=TCP localport=80

netsh advfirewall firewall add rule name=”SSL” dir=in action=allow protocol=TCP localport=443

netsh advfirewall firewall add rule name=”SQLServerBrowser” dir=in  action=allow protocol=udp localport=1434

netsh advfirewall firewall add rule name=”multicastbroadcastresponse” dir=in  action=enable

 

+++++++++++++++       Integration services     +++++++++++++++ 

netsh advfirewall firewall add rule name=”RPC (TCP/135) for SQL SSIS” dir=in action=allow protocol=TCP localport=135

 

+++++++++++++++       Programs         +++++++++++++++ 

netsh advfirewall firewall add rule name=”RPC (TCP/135) for SQL SSIS” dir=in program=”C:\Program Files\100\DTS\Binn\MsDtsSrvr.exe” action=allow

netsh advfirewall firewall add rule name=”SQLServer” dir=in Program=”F:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe” action =Allow

For all Outbound ports use the same cmds with “dir=out ” in place of dir=in

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

Method 2: If above solution is not working for you then you can use below method also

To create the script, follow these steps:

1. Start Notepad.

2. Copy and paste the following code into Notepad:

 @echo =========  SQL Server Ports  ===================

@echo Enabling SQLServer default instance port 1433

netsh firewall set portopening TCP 1433 “SQLServer”

@echo Enabling Dedicated Admin Connection port 1434

netsh firewall set portopening TCP 1434 “SQL Admin Connection”

@echo Enabling conventional SQL Server Service Broker port 4022 

netsh firewall set portopening TCP 4022 “SQL Service Broker”

@echo Enabling Transact-SQL Debugger/RPC port 135

netsh firewall set portopening TCP 135 “SQL Debugger/RPC”

@echo =========  Analysis Services Ports  ==============

@echo Enabling SSAS Default Instance port 2383

netsh firewall set portopening TCP 2383 “Analysis Services”

@echo Enabling SQL Server Browser Service port 2382

netsh firewall set portopening TCP 2382 “SQL Browser”

@echo =========  Misc Applications  ==============

@echo Enabling HTTP port 80

netsh firewall set portopening TCP 80 “HTTP”

@echo Enabling SSL port 443

netsh firewall set portopening TCP 443 “SSL”

@echo Enabling port for SQL Server Browser Service’s ‘Browse’ Button

netsh firewall set portopening UDP 1434 “SQL Browser”

@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)

netsh firewall set multicastbroadcastresponse ENABLE

3. Save the file as a .txt file by using the following name: OpenSqlServerPort.txt

4. Rename the OpenSqlServerPort.txt file to the following: OpenSqlServerPort.bat

Before you run the OpenSqlServerPort.bat script, you must copy the script to the computer which has the firewall and then run the script on that computer. To run the script, follow these steps:

1. Click Start, click Run, type cmd, and then click OK.

2. At the command prompt, use the cd command to navigate to the folder in which you saved the OpenSqlServerPort.bat file.

3. To run the OpenSqlServerPort.bat script, type OpenSqlServerPort.bat at the command prompt, and then press ENTER.

Hope this post will help someone. Please stay glue for more SQL Stuff’s.  🙂