|
Firewall systems help prevent unauthorized access to computer resources. If a firewall is turned on but not correctly configured, attempts to connect to SQL Server might be blocked. To access an instance of the SQL Server through a firewall, you must configure the firewall on the computer that is running SQL Server to allow access.
The firewall is a component of Microsoft Windows. You can also install a firewall from another company. This topic discusses how to configure the Windows firewall, but the basic principles apply to other firewall programs.
Temporarily turning off the firewall on the computer that is running SQL Server, is the quickest and easiest method to determine if it's causing a connection problem between POSitive software and it's database on remote workstations. If you're using a Windows Firewall and you prefer to not permanently disable it (permanently disabling firewalls is not recommended), you can create Windows Firewall exceptions for SQL. External connections to SQL Server will be blocked unless SQL Server and the SQL Server Browser service can communicate through the firewall. You must create an exception for each instance (e.g. Path to SQL Server: [ServerName]\[InstanceName]) of SQL Server that you want to accept remote connections and an exception for the SQL Server Browser service.
NOTE:
The standard ports for SQL Server are 1433 TCP and 1434 UDP. However, the TCP port can be changed or set to dynamic in the SQL Configuration Manager.
--- PROJECT SUMMARY---
--- STEP-BY-STEP INSTRUCTIONS ---
------------------------------
CREATE EXCEPTIONS FOR THE SQL SERVER AND BROWSER SERVICES
------------------------------
These steps apply to Windows Firewall in most builds of Windows 10, but many of the steps may apply to Windows 7, Windows 8 and Windows 8.1. If you are using a different firewall system, see your firewall documentation or consult your IT professional for more information.
1. Click the Windows Start button, type "services", choose 'Services' or 'View local services' from the search results list
2. Double-click on the SQL Server ([InstanceName]) service to open its properties screen
--- e.g: SQL Server (POSITIVE)
3. On the General tab highlight all the text that is between the quotation marks below the "Path to executable"
--- e.g: C:\Program Files\Microsoft SQL Server\MSSQL13.POSITIVE\MSSQL\Binn\sqlservr.exe
4. Press Ctrl+C on your keyboard, or right-click on the highlighted text and select Copy to copy
5. Click OK to close the service Properties page
6. Click the Windows Start button, type "firewall", choose 'Windows Firewall' from the search results list
7. In Windows Firewall, click 'Allow an app or feature through Windows Firewall'
8. In the Allowed Apps window click 'Allow another app...' (If the button is grayed out click the 'Change settings' button)
9. In the 'Add an app' window, click Browse.
10. Right-click in the 'File name' field of the Browse window and select Paste to paste the path you obtained in step 3 above
11. Click Open, and then click Add.
12. Switch back to the Services window and double-click on the 'SQL Server Browser' service to open its properties screen
13. On the General tab highlight all of the text that is in between the quotation marks below the "Path to executable"
--- e.g: C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe
14. Press Ctrl+C on your keyboard, or right-click on the highlighted text and select Copy to copy
15. Click OK to close the service Properties page
16. Switch back over to the Windows Firewall Allowed Apps window and click 'Allow another app...'
17. In the 'Add an app' window, click Browse.
10. Right-click in the 'File name' field of the Browse window and select Paste to paste the path you obtained in step 14 above
11. Click Open, and then click Add.
12. Repeat steps 1 through 11 above for each instance of SQL Server that needs an exception. There will only be one SQL Server Browser service running. All other SQL Server services do not need an exception.
Back to top
------------------------------
ADDITIONAL INFORMATION ABOUT CONFIGURING WINDOWS FIREWALL TO WORK WITH SQL SERVER
------------------------------
Download: ConfigureWindowsFirewallToWorkWithSQLServer.pdf
Back to top
Updated 3/9/2017
| 