Troubleshooting

Initial connection to a new database server can stumble at various points. Network connectivity might be a problem, or a firewall might be preventing access, or the database server might not be configured to listen for network connections, or authentication might not be configured correctly.

Firewall 1

Windows firewall, by default, blocks everything. If it is safe for you to turn off the firewall on the database server machine while troubleshooting connectivity issues, you should do so. (If there are several problems, you must have all the problems fixed simultaneously to succeed.) Then, once you have succeeded, configure the firewall to allow SQL Server, and turn it back on.

On the database server machine, to turn the firewall off, go to Control Panel. (On Windows 10, be sure to go to Control Panel, not Settings.) Look for Windows Firewall. (There is a search box at the top right — you can type “firewall” into it.) Click it. At the left, you will see Turn Windows Firewall on or off. Click this. For each of several locations you see here, select “Turn off Windows Firewall (not recommended)”. (Note that you should not do this if you believe that you are connected to a network that might contain anything malicious.) Click OK. Remember that later you will need to turn these back on (or at least back into the state you found them in).

Network Connectivity

On the machine running NEXUS IC, open a cmd prompt. (Click the Windows Start button, type cmd, press Enter.) Ping the database server machine:

ping machinename

or:

ping ipaddress

If the machine responds, this is good: it means that you have basic connectivity to the database server machine. If you see “request timed out”, you do not have connectivity. See your local TCP/IP guru for assistance.

Anti-virus

If you are using a third-party firewall, anti-virus, anti-malware, “optimiser”, “cleaner”, etc. product, we recommend disabling it while troubleshooting. (Windows Defender is fine.)

SQL Server Configuration Manager

On the database server machine, open Computer Management: click the Windows Start button, type manag and click on Computer Management. In the tree at left, expand Services and Applications. You should see one or more entries for SQL Server Configuration Manager. (There will be more than one if you have more than one SQL Server installed on this machine.) Fully expand each of these (for example by clicking on each one and then pressing the numeric * key on the keyboard). (Size the window larger as required.)

For each entry that mentions “Protocols”, click on it. Ensure that “TCP/IP” is set to Enabled in each case. (Right-click and choose Enable as necessary.)

If you enabled any, you will see a message telling you that you’ll need to restart the service. Once you’ve gone through the list, if you enabled any, click on Services in the tree at the left. Scroll down to SQL Server in the list. (You may need to set the Name column wider.) (You may have several SQL Servers in the list.) Right-click on each and choose Restart. (Note that this may interrupt any users who were using those database servers.)

Allow remote connections

In SQL Studio, right-click on the database server name in the Object Explorer at the left. Choose Properties. Go to the Connections page. Ensure “Allow remote connections to this server” is ticked. (It is ticked by default, so you’re just checking that no-one’s changed it.)

No default instance

If your SQL Server is set up with an instance name like “SQLExpress”, “SQL2012”, etc., and does not have a “default” instance (i.e. one without an instance name) then when requests come in on the default port (1433), none of your instances may be listening. You can fix this by re-installing the database server as the default instance, by installing an additional database server as the default instance, or by reconfiguring the ports your existing server instance is listening on:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-2017

Firewall 3

Now that you have fixed your connectivity issues (hopefully) or have temporarily admitted defeat and are returning to a secure state, it’s time to turn the firewall back on. Once again, go to Control Panel, then to Windows Firewall. At the left, you will see Allow a program or feature through Windows Firewall. Click it. If there is a button marked Change Settings, and it is enabled, click it.

Click Allow another program…. Click Browse. Browse to the folder that contains your sqlservr.exe. This might be a path like C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn. This path may vary with your SQL Server version. In particular, x86 versions will be in “Program Files (x86)”. Select sqlservr.exe and click Open. Click Add.

The list of allowed programs and features should now contain an entry like “SQL Server Windows NT - 64 Bit” (or similar). There may be two or three columns to the right of it, with checkboxes for Domain, Home/Work (Private), and Public (or just Private and Public). The process we have just stepped through should have ticked the required boxes.

Now click Turn Windows Firewall on or off and turn the firewall back on for each of the several locations. Check that you still have database connectivity: in NEXUS IC click Database ‣ Close, then Database ‣ Reopen and ensure the database can still be connected to.