Search code examples
sql-serverbashsqlcmd

sqlcmd works in CMD and not in bash

I am having quite a few problems making MSSQL drivers work in Ubuntu. I have followed the following tutorial to make sqlcmd work in Ubuntu 16.04.

# In CMD:
sqlcmd -S my_server_name -U my_username -P my_password -d my_database
1> select name from sys.databases
2> go

After installing the same tool in Ubuntu, it seems to work, but it timeouts when attempting to connect to the same database:

# In Ubuntu bash
sqlcmd -S my_server_name -U my_username -P my_password -d my_database
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2AFA.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I have tried to change configurations of the database, such that the port is static at 1433, but still no luck.

Do you have any suggestions?

Solution

  • I have fixed the problem!

    1. Install MSSQL drivers on Ubuntu

    Follow this tutorial.

    2. Ensure your port on the database is static

    Follow this tutorial to set up a static port.

    3. Identify the IP adress of your database

    I had to call the following code on the database to get the ip-adress: local_net_address.

    SELECT  
+    CONNECTIONPROPERTY('net_transport') AS net_transport,
+             CONNECTIONPROPERTY('protocol_type') AS protocol_type,
+             CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
+             CONNECTIONPROPERTY('local_net_address') AS local_net_address,
+             CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
+             CONNECTIONPROPERTY('client_net_address') AS client_net_address

    4. Connect to database (in Ubuntu bash)

    Here are two examples with netcat and sqlcmd.

    # Using MSSQL tool
sqlcmd -S my_server_ip_adress//my_server_name,my_port -U my_username -P my_password -d my_database

# Using netcat
nc -z -v -w5 my_server_ip_adress my_port