Tuesday, January 31, 2017

Troubleshooting connectivity to SQL Server on Linux

In general, the connectivity to SQL Server on Linux is similar to connectivity to SQL Server on Windows, but because I do not have blog about Windows I'll do it for Linux.

Prerequisites:

Virtual Machine or bare metal server with installed Linux;

Successfully installed SQL Server on Linux;

Successfully installed SQL Server Tools on Linux (optional);

Step 1: Verification that SQL Server up and running:

Very simple code to run in Linux:

#> systemctl status mssql-server

As a result you have to get something like this:

If you did not get that output there might be three most probable problems:
I) Your SQL Server is not installed on that machine. Just try to install it again.

II) SQL Server is not configured. To configure run following command:

#> sudo /opt/mssql/bin/sqlservr-setup

III) Your SQL Server is not started. To fix that just start it:

#> sudo systemctl start mssql-server

Step 2: Check that SQL Server is responding.

After you verified SQL Server is up and running, you configured it and set SA password you can test SQL Server Tools connectivity:

#> sqlcmd -S localhost -U SA -Q "SELECT @@VERSION;"

After you enter SA password you have to get "vNext" version of your SQL Server.
If you did not get the correct response you might have following problems:
I) Wrong SA password. To fix it, just re-configure SQL Server.
II) SQL Server Tools are not installed.
III) Typo. Check your syntax.

Step 3: Check network connection to Linux machine.

At first, verify IP addresses of all network cards of your Linux machine:

#> ifconfig | grep 'inet addr:'

From the machine, you want to establish connection to the SQL Server on Linux, ping Linux machine by an IP address you've got by the previous command:

> ping 192.168.58.11

If you can't get a response from pinging Linux machine that tells about network connectivity issues.
You have to use your networking skills to troubleshoot that problem.

Step 4: Check port on the Linux machine.

At first, make sure which port is used by your SQL Server on Linux. By default it is 1433, but to verify it you can run following command:

#> cat /etc/services | grep 'Microsof SQL Server'

As the result you will see the real port SQL Server is assigned too.

Next, you can check if SQL Server port is accessible from windows machine using "PortQry" Command (you can download the executable from Microsoft site), IP address of your Linux machine and assigned SQL Server port :

> PortQry.exe -n 192.168.1.10 -e 1433

That command will determine if port 1433 is accessible on that machine and that SQL Server is listening to it.

If the port marked as "FILTERED" that means Linux firewall is blocking any incoming calls to that port.

In order to fix that you have to open SQL Server port on Linux machine and restart firewall using following two commands: