The automated setup for SQL Server will attempt to perform all necessary steps to allow remote access (which is needed to both manage the Enterprise Server from another computer than the one running the SQL Server, and to connect to the server from within SCP).


Testing remote access


The best way to be sure whether or not you can connect is to use SQL Server Management Studio, a free download from Microsoft. If this can connect to your SQL Server instance from a different computer, you should be fine.


Settings to check


Allowing remote access


(If you don't have SSMS installed, feel free to leave this step until last, as it's rarely the issue unless someone has deliberately disabled it.)


Connect via SSMS on the computer running the SQL Server. Right-click on the instance and select Properties. Click Connections on the left menu and check that the "Allow remote connections to this server" tickbox is checked.


 



Checking TCP/IP is enabled


Run SQL Server Configuration Manager on the computer running the SQL Server. Expand the node "SQL Server Network Configuration" and select the entry titled "Protocols for <<Your Instance>>".



If TCP/IP is not enabled, do so and then restart the service:



Ensuring the Browser service is running


In the Services section of SQL Server Configuration Manager, check that the SQL Server Browser service is running, as shown below. If it has any other status, go into its properties and change the startup mode to Automatic, then start it.


You can also do the same tasks using the standard Windows Services tool.



Allowing connections through the firewall


You need to make two firewall rules. The first is to allow connection to the SQL Server Browser, which uses UDP port 1434. Then you must allow connections to the SQL Server itself. If your SQL Server is using dynamic ports (the default behaviour), then grant access to the program itself. Alternatively, set to a specific port and allow access to that port.


This example covers the rules needed for dynamic ports:



You can add these rules from a command prompt, using commands like these:


  netsh advfirewall firewall add rule name="SQL Browser" dir=in protocol=udp action=allow localport=1434 remoteip=localsubnet profile=DOMAIN enable=yes


  netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow program="C:\Program Files\Microsoft SQL Server\MSSQL16.SCPENTERPRISE\MSSQL\Binn\sqlservr.exe" enable=yes remoteip=localsubnet profile=DOMAIN


Note that the sqlserver.exe program may be in a different location depending on the exact version of SQL Server you are running.