Connect to SQL Server Linux on Docker via ODBC Driver

Now that SQLServer 2016 is available on Linux as a public preview, one of the easiest ways to officially deploy it is through Docker engine. With the Docker image of MS SQLServer on Linux, you will have a running instance within few seconds.  Being on a Docker image doesn’t change how you connect to SQLServer via SSMS or standards-based connectivity like Progress DataDirect SQLServer ODBC and JDBC drivers which are based on wire protocol communication.

In this tutorial, we will go through on how you can run SQLServer Linux using Docker engine, load some sample data to it and connect to the instance using the Progress DataDirect SQLServer ODBC driver.

1. You have Docker engine 1.8+ installed on your Windows
2. Change the memory for Docker to at least 4 GB, the default is 2 GB 

1. Pull the Docker image for SQL Server on Linux from Docker hub by running the following command on your power shell for Windows.

    

   docker pull microsoft/mssql-server-linux

    

2. When you run the docker image for the first time you need to accept the EULA and provide a strong password for the SA account. Use the following command to run the container.

    

   docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=your_Strong_Password' -p 1433:1433 -d microsoft/mssql-server-linux

    

   Note: A strong password means, At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.

    

3. That’s it! You now have a running instance of SQL Server on Linux.

1. To load the sample data to instance, let's bash in to container using the following command

2. You should now be seeing the bash console of the docker container.

3. On your host machine, Download Adventure Works back up file from here .

4. Copy the backup file from the host machine to docker container using the following command

5.Back on the docker container bash console, create a directory for the backup file and move it in to that directory using the following commands

6. Next step is to install sqlcmd to restore the database, but to do that we would have to install couple of packages and make some minor configuration changes. Run the following commands to install mssql-tools and unixodbc-dev

7.You need to reconfigure locales before you use sqlcmd, to do that run the command “dpkg-reconfigure locales”. Choose “en_US UTF-8 UTF-8” and set “en_US UTF-8 UTF-8” as default locale.

8.Connect to SQL Server using sqlcmd by running the following command.

9.Restore the database Adventureworks by running the following command to have sample data in SQL Server Linux.

1. Download the SQL Server ODBC driver for Windows from the Progress website.
2. Extract the zip file PROGRESS_DATADIRECT_ODBC_SQLSERVER_WIN_xx.zip and run the setup.exe to install the SQL Server ODBC driver. It’s a simple setup and you should be done in few seconds.
3. Open the ODBC Administrator (XX bit) depending on whether you installed 32 or 64-bit driver. Note that they have separate ODBC administrator tools.
4. On ODBC administrator, choose Add -> Select DataDirect 7.X SQL Server Wire Protocol as your driver and click on Finish. You should now see a setup window as below.

5. Fill in host as ‘localhost’ and database as ‘AdventureWorks’ and Click on the button ‘Test Connect’. Provide the username as ‘SA’ and password that you set when you ran the Docker image for the first time. If you have created another login for your instance, you can also use that. You should be successfully connected to the SQL Server database on Linux running on a Docker engine.

1. Install the package pyodbc using pip by running the following command.

 

pip install pyodbc

 

2. Copy the following Python script which creates a connection to SQL Server using the ODBC driver that you have just configured in to a file or python console and run it.

 

##Importing pyodbc module
import pyodbc

##connecting to your database through ODBC
cnxn = pyodbc.connect('DSN=SQLDocker;UID=sa;PWD=pass, autocommit=True)
cursor = cnxn.cursor()


 

 

3. Once you have successful connected to SQL Server, you can append the following script to the above file to print the tables in your database.

 

##Print TableNames
for row in cursor.tables():
if row.table_type == "TABLE":
print (row.table_name)
print (row)


 

4. You can also try executing scripts by appending following script. 

 

##Executing a simple query and printing the records

cursor.execute("SELECT BusinessEntityID , NationalIDNumber, LoginID, OrganizationNode, OrganizationLevel, JobTitle, BirthDate, MaritalStatus FROM AdventureWorks.HumanResources.Employee")

for row in cursor.fetchall():
print (row)







We hope this tutorial helped you to connect to your SQL Server database running on Docker engine. Try the SQLServer ODBC driver for 15 days and contact us if you have any questions or need an extension for the trial.