[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.  🙂

Advertisements

12 Responses

  1. “netsh advfirewall firewall add rule name=”multicastbroadcastresponse” dir=in action=enable” does not work!!

    Does anyone know what the corect command is to enable multicast broadcast response using netsh advfirewall? The line “netsh firewall set multicastbroadcastresponse ENABLE” works but is depreciated on Windows 2008.

  2. Everything is very open with a precise description of the issues.
    It was really informative. Your website is very useful.
    Thank you for sharing!

  3. I was recommended this web site by my cousin. I am not sure whether this post is written by
    him as nobody else know such detailed about my trouble.

    You’re incredible! Thanks!

  4. Hi there, the whole thing is going nicely here and ofcourse every one is sharing facts,
    that’s genuinely fine, keep up writing.

  5. These are actually enormous ideas in about blogging.
    You have touched some nice factors here. Any way
    keep up wrinting.

  6. Thanks for finally writing about >[Part 1] Cannot
    connect to Server A network-related or instance-specific error occurred
    while establishing a connection to SQL Server | In Depth
    SQL : The Real DBA Activities <Liked it!

  7. Hey I know this is off topic but I was wondering if you
    knew of any widgets I could add to my blog that automatically tweet my newest
    twitter updates. I’ve been looking for a plug-in like this for
    quite some time and was hoping maybe you would have some experience with something like this.
    Please let me know if you run into anything. I truly enjoy reading your blog
    and I look forward to your new updates.

  8. I enjoy what you guys are usually up too. This kind of clever
    work and reporting! Keep up the very good works guys
    I’ve added you guys to my personal blogroll.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: