Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Configure Windows Firewall for SSIS in Windows Server 2008 R2


By:   |   Last Updated: 2010-06-08   |   Comments (2)   |   Related Tips: More > SQL Server Configurations

Problem

I have installed SQL Server 2008 on Windows Server 2008 R2 and I am unable to connect to SQL Server 2008 Integration Services Instance from SQL Server 2008 Management Studio which is installed on another remote server. As I am new to Windows Server 2008 R2 it would be great if you can let me know the step by step approach to enable the default port of SQL Server 2008 Integration Services in Windows Firewall for user connectivity.

Solution

Windows Firewall helps prevent unauthorized access to computers in the network. By default, Windows Firewall will be turned ON once the operating is installed. If a firewall is turned ON, and if it is not configured correctly then attempts made by the users to connect to SQL Server Integration Services will be blocked.

In order to access an SQL Server Integration Services which is behind a firewall, database administrator needs to configure the firewall on the computer that is running SQL Server Integration Services to allow users access. In this tip, we will go through the steps that you need to follow to quickly configure Windows Firewall in Window Server 2008 or in Window Server 2008 R2 to allow SQL Server Integration Services access to users.

1. Click Start > All Programs > Administrative Tools > Server Manager. This will open up Server Manager as shown in the below snippet.

step approach to enable the default port of SQL Server 2008 Integration Services in Windows Firewall for user connectivity.

2. In Server Manager, expand Configurations tab and then expand Windows Firewall with Advanced Security. Right click Inbound Rules and click on New Rule... as shown in the below snippet to open up the New Inbound Rule Wizard.

 configure Windows Firewall in Window Server 2008 or in Window Server 2008 R2

3. In New Inbound Rule Wizard's Rule Type Page, you need to select the Port option as shown in the below snippet to control connections for a TCP or UDP Port. Click Next to continue with the wizard.

New Inbound Rule Wizard's Rule Type Page

4. In Protocol and Ports, specify the protocols and ports to which this rule applies. As you may know, SQL Server Integration Service when installed uses TCP Port 135 as the default port, hence you need to choose TCP option and then specify the port number as 135 as shown in the snippet below. Click Next to continue with the wizard.

SQL Server Integration Service when installed uses TCP Port 135 as the default port, hence you need to choose TCP option and then specify the port

5. In Action page, specify the action to be taken when a connection matches the conditions specified in this rule. There are basically three options available to choose from which are self explanatory.

a) Allow the connection
b) Allow the connection if it is secure
c) Block the connection

Here, you need to choose the first option which is Allow the connection and click Next, to continue with the wizard.

 choose the first option which is Allow the connection

6. In Profile page, specify the profiles for which this rule should apply. There are three options available to choose from which are self explanatory.

a) Domain - Applies when a computer is connected to its corporate domain
b) Private - Applies when a computer is connected to a private network location
c) Public - Applies when a computer is connected to a public network location

Here, you need to choose the first option which is Domain as you want everyone who is connected to its corporate domain to get connected to the SQL Server Integration Service Instance as long as they have permissions to connect to the SQL Server 2008 Integration Service Instance. Click Next, to continue with the wizard.

you want everyone who is connected to its corporate domain to get connected to the SQL Server Integration Service Instance as long as they have permissions to connect to the SQL Server

7. In Name Page, you need to provide a meaningful name and also provide a meaningful description as shown in the snippet below and click Finish to complete the wizard.

you need to provide a meaningful name and also provide a meaningful description

8. Next step will be to add one New Inbound Rule within Windows Firewall with Advanced Security to configure MsDtsSrvr.exe program within Windows Firewall. In New Inbound Rule Wizard's Rule Type Page, this time you need to select the Program option as shown in the snippet below to control connections for the program. Click Next to continue with the wizard.

add one New Inbound Rule within Windows Firewall with Advanced Security to configure MsDtsSrvr.exe program within Windows Firewall

9. In Program, specify the program path of MsDtsSrvr.exe file which is generally in %Program Files%\Microsoft SQL Server\100\DTS\Binn location. Click Next to continue with the wizard.

 specify the program path of MsDtsSrvr.exe file

10. In Action page, choose condition as Allow the connection as shown in the snippet below and click Next, to continue with the wizard.

choose condition as Allow the connection

11. In Profile page, choose Domain as you want everyone who is connected to its corporate domain to get connected to the SQL Server Integration Service Instance as long as they have permissions to connect. Click Next, to continue with the wizard.

you want everyone who is connected to its corporate domain to get connected to the SQL Server Integration Service Instance as long as they have permissions to connect

12. In Name Page, you need to provide a meaningful name and description as shown in the below snippet and click Finish to complete the wizard and to enable Windows Firewall for SQL Server 2008 Integration Service.

complete the wizard and to enable Windows Firewall for SQL Server 2008 Integration Service

13. Once the wizard configuration is complete, you will be able to see the new rules under Inbound Rules as shown in the snippet below.

you will be able to see the new rules under Inbound Rules

14. Now you will be able to connect to your SQL Server 2008 Integration Service from any remote SQL Server 2008 Management Studio.

you will be able to connect to your SQL Server 2008 Integration Service from any remote SQL Server 2008 Management Studio
Next Steps


Last Updated: 2010-06-08


get scripts

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, January 10, 2011 - 8:26:17 AM - Régis Marchal Back To Top

I am not shure that you need to create a rule for the MSDTSsrvr.EXE if you have already open a specific port for this. But, maybe I am wrong because the port 135 is a special one.

 


Wednesday, July 14, 2010 - 3:28:42 PM - SAFEKOM Back To Top

Your tip is very helpful and easy to fallow.

I hav problem,

i hav 64bit OS (window server2008) but i havn't DTS folder in  %Program Files%\Microsoft SQL Server\100\????\???? OR  Program Files(x86)\Microsoft SQL Server\100\????\????   that i can open port for SQL Server.

I found it   %Program Files%\100\DTS\BINN but there is not any MSDTSsrvr.exe file in it?

when ever i try to connect to SQL Server 2008 from client computer i get Error 40 and 53????

Kindly help me out.

 


Learn more about SQL Server tools