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 database engine will be blocked. In order to access an instance of the SQL Server database engine which is behind a firewall, a database administrator needs to configure the firewall on the computer that is running the SQL Server database engine to allow users access.
This article, will review how to quickly configure Windows Firewall in Window Server 2008 or in Window Server 2008 R2 to allow SQL Server database engine access to users.
Click Start → All Programs → Administrative Tools → Server Manager to open up Server Manager.
In Server Manager, expand Configurations tab and then expand Windows Firewall with Advanced Security.
Right click Inbound Rules and click on New Rule... to open up New Inbound Rule Wizard.
In New Inbound Rule Wizard's Rule Type Page, select Port option to control connections for a TCP or UDP Port. Click Next.
In Protocol and Ports, specify the protocols and ports to which this rule applies. Choose the TCP option and then specify the port number as 1433. Click Next to continue with the wizard.
If you have installed the database engine as a named instance on the server then the database engine will be assigned a dynamic port.
In such a scenario, make sure that SQL Server Browser Service is running and the UDP Port 1434 (which is used by SQL Server Browser Service) is open in the Windows Firewall.
Secondly, make sure that you have opened TCP Port 1433 in the Windows Firewall (so that the clients can connect to the SQL Server Browser and be redirected to the named instance of SQL Server Analysis Service) or else the dynamic port which is used by the database engine Named Instance should be open in Windows Firewall. You can then connect to server in the format ServerName\InstanceName.
Alternatively, you can assign a fixed port, and then unblock access to that port. This approach offers better auditing capability than if you allowed access to the program executable. For this reason, using a fixed port is the recommended approach for accessing any database engine instance. A thorough description of setting up a fixed port for the database engine instance can be found here.
In Action page, specify the action to be taken when a connection matches the conditions specified in this rule. Choose, Allow the connection and click Next.
In Profile page, specify the profiles for which this rule should apply.
Choose Domain, as you want everyone connected to its corporate domain to get connected to the SQL Server database engine instance provided they have permission to connect to the SQL database engine instance. Click Next.
In Name Page, provide a name and description. Click Finish to complete the wizard.
You will now be able to connect to the data warehouse from excel. For detailed information about configuring the Windows Firewall for the database engine access, please visit the Microsoft MSDN article on the topic here .