Windows Firewall basically allows users to choose from the below mentioned three options when adding a excption for a port.
Any computer (including those on the internet): It is recommended not to use this option as it will allow any computer that can connect to your computer to access the port and the SQL Server Integration Service.
My network (subnet) only: This is a better option tha “Any computer” as in this case only those computers within the local subnet of your network can get connected to the port or the SQL Server Integration Service.
Custom list: This is one among the best option available. When you are using “Custom list” option you will be providing the list of IP addresses which can access the port or the SQL Server Integration Services. However, if you are using DHCP then there are chances that the IP address can change and in that scenrios the users won’t be able to get connected to Integration Services. So this should be used where Fixed IP is alloted to users on the network.
As per your businees requirement you can choose any one among the above mentioned options. By default “Any computer (including those on the internet)” option will be selected. Once the necessary changes are done you can click OK to close the Change Scope dialog box and the click OK once again to close the Add a Port dialog box.
5. Next, in the Windows Firewall dialog box, click Exceptions Tab, and then click Add Program….
6. In the Add a Program dialog box, click Browse, and nagivate to Drive:Program FilesMicrosoft SQL Server100DTSBinn folder and select MsDtsSrvr.exe file and click open. Click OK to close Add a Program dialog box.
If you are using SQL Server 2005 then you will find the MsDTsSrvr.exe file located in Drive:Program FilesMicrosoft SQL Server90DTSBinn folder location.
7. To finally close the Windows Firewall dialog box, click OK
Configuring Windows Firewall Exception for SQL Server Integration Services Using Command Prompt
Another method of configuring windows firewall for SQL Server Integration is using the command prompt. Follow the below mentioned steps to configure firewall using command prompt.
1. Click Start | Run and type CMD this will open up command prompt window.
2. In the command prompt window you need to type the mentioned below commands which will create an exception for the port and an exeception for the Integration Service.
netsh firewall add portopening protocol=TCP port=135 name=”SSIS Port” mode=ENABLE scope=ALL
netsh firewall add allowedprogram program=”D:Program FilesMicrosoft SQL Server100DTSBinnMsDtsSrvr.exe” name=”SQL Server Integration Services” scope=ALL
Connecting to SQL Server Integration Services on a Remote Server
Once you have added successfully windows firewall exceptions for SQL Server Integration Services (MsDtsSrvr.exe) and for port 135 which is used by Integration Services. You can access Integration Services on a remote service using SQL Server Management Studio (SSMS).
1. Open SQL Server Management Studio by typing “SSMS” (SQL 2008) or “SQLWB” (SQL 2005) from Start | Run
2. In the Connect to Server dialog box, select “Integration Services” from the drop down for Server type
3. Provide the name of the SQL Server Integration Services server in the Server Name text box
4. Click Connect
However, there can be scenerios when a user can get “Access is denied” error message when trying to get connected to SQL Server Integration Services. This generally happens if the users does not have the necessary permission to DCOM. This generally happen to those users who are basically not members of local administrators group on the server where SQL Server Integration Service is installed. The solution to avoid such issues will be to add such users to Distributed COM users group.
Conclusion
Configuring Windows Firewall Exception for SQL Server Integration Services and for port 135 will allow users to connect to SQL Server Integration Services when the Windows Firewall is enabled to secure the SQL Server from unauthorized access. Once the exception is created then you can avoid connectivity issues on Integration Services for the SSIS developers or Database Administrators who wants to get connect to SQL Integration Services remotely. However you should also make sure that the exceptions are added in Windows Firewall for SQL Server Database Engine which runs on default port 1433 and SQL Server is configured to accept remote connections.
]]>