Allow remote computer access to SQL Server via SQL Server Management Studio

Question

I have both SQL Server and Windows on version 2016. I am trying to set the firewall to allow SQL Server Management Studio to connect from my PC to it. I have created an entry for port 1433 TCP local and another entry for the actual sqlsrv.exe application under Inbound Rules.

Am I missing something?

Answer 1

You haven't mentioned whether you installed a default or a named instance. See Microsoft for further details on the difference.

What you have done should work for a default instance SERVERNAME, if instead you have installed a named instance SERVERNAME\INSTANCENAME then you would also need to allow the SQL Browser Service service. Lookup the path in Windows services for this and while in there ensure that it is set to Startup Type: Automatic and it is started. The default installation path is "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe"

If this doesn't work, ensure that TCP/IP is enabled for the SQL Server in the SQL Configuration Manager.

To summarise:

Default instance SERVERNAME:

• Allow the sqlservr.exe or port 1433 if using the default port through the firewall
• Ensure TCP/IP is enabled in SQL Configuration Manager
• Ensure Allow Remote Connections is enabled in SQL Server

Named instance SERVERNAME\INSTANCENAME:

• Allow the sqlservr.exe through the firewall
• Allow the sqlbrowser.exe through the firewall
• Ensure the SQL Browser Service (sqlbrowser.exe) is started in services
• Ensure TCP/IP is enabled in SQL Configuration Manager
• Ensure Allow Remote Connections is enabled in SQL Server