SQL Server ODBC Driver for Linux | Tutorial

This tutorial walks you through on how you can connect to SQL Server database from Linux machine using an ODBC driver. We drafted a step-by-step guide to:
1. install SQL Server ODBC Driver on a Linux machine
2. Configure the ODBC driver
3. Test the ODBC connection

1. Download the trial version of SQL Server ODBC driver for Linux from Progress website.
2. Once the file has downloaded, run the following Linux commands to move the package to a new folder.

   mkdir datadirect

   mv PROGRESS_DATADIRECT_ODBC_SQLSERVER_LINUX_XX.tar.Z datadirect/

   cd datadirect/

3. Extract the contents of the package by running the following command
   tar -xvf PROGRESS_DATADIRECT_ODBC_SQLSERVER_LINUX_64.tar.Z
4. Install the KSH interpreter by running the following command on your terminal.

   Debian:

   sudo apt-get install ksh

   CentOS/RHEL:

   sudo yum install ksh
5.  Run the installation, by running the following script on your terminal
   ./unixmi.ksh
6. Confirm your OS when you see a prompt like the below, by pressing Enter or by typing ‘Y’

    

7. Next, go through the License agreement and enter YES, if you agree to the terms as shown below.

    

8. Enter your details like Name and your company when prompted. If you are trying out the Progress DataDirect SQL Server ODBC driver for Linux, enter EVAL for Serial Number and Key as shown below. If you have a license, then enter the appropriate Serial Number and Key to activate the product.

    

9. Next, you will be prompted to choose if you would like to install all the drivers that comes with DataDirect Connect suite or just choose one driver to be installed. For this tutorial, lets go with option 2 as shown below.
   
10. Next you will be shown a list of drivers to choose from that you want to install. Select the ‘SQL Server Wire Protocol’, the option 11 as shown below.

     

11. Confirm the default path if you have access to it or else enter a path where you have access, when you are asked for the destination directory.

     

     

     

12. Wait until the installation finishes and if you want to install another Linux driver press ‘Y’ else press ‘N’ to exit the installer.

1. Go to /installpath/Progress/DataDirect/Connect_64_for_ODBC_71 and run the following commands in your terminal to add environment variables ODBCINI, ODBCINST and LD_LIBRARY_PATH.

   sudo chmod +x odbc.sh

   ./odbc.sh

2. Verify if the variables ODBCINI, ODBCINST and LD_LIBRARY_PATH are installed successfully by running the following command. You should see the path for the file odbc.ini
   echo $ODBCINI
3. If the environment variables aren’t added properly then add these values to ~/.bashrc

   export LD_LIBRARY_PATH=/install_path/Progress/DataDirect/Connect64_for_ODBC_71/lib

   export ODBCINI=/ install_path/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini

   export ODBCINST=/ install_path/Progress/DataDirect/Connect64_for_ODBC_71/odbcinst.ini

4. Refresh the terminal configuration by running the following command.
   source ~/.bashrc
5. Now open the odbc.ini file in the path /install_path/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini and configure your SQL Server connection details as shown in following sample odbc.ini.

   [ODBC Data Sources]

   SQL Server Legacy Wire Protocol=DataDirect 7.1 SQL Server Legacy Wire Protocol

   [ODBC]
   

   IANAAppCodePage=4

   InstallDir=/home/progress/Progress/DataDirect/Connect64_for_ODBC_71

   Trace=0

   TraceFile=odbctrace.out

   TraceDll=/home/progress/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddtrc27.so

   [SQL Server Wire Protocol]
   

   Driver=/home/progress/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddsqls27.so

   Description=DataDirect 7.1 SQL Server Wire Protocol

   AlternateServers=

   AlwaysReportTriggerResults=0

   AnsiNPW=1

   ApplicationName=

   ApplicationUsingThreads=1

   AuthenticationMethod=1

   BulkBinaryThreshold=32

   BulkCharacterThreshold=-1

   BulkLoadBatchSize=1024

   BulkLoadFieldDelimiter=

   BulkLoadOptions=2

   BulkLoadRecordDelimiter=

   ConnectionReset=0

   ConnectionRetryCount=0

   ConnectionRetryDelay=3

   Database=<database_name>

   EnableBulkLoad=0

   EnableQuotedIdentifiers=0

   EncryptionMethod=0

   FailoverGranularity=0

   FailoverMode=0

   FailoverPreconnect=0

   FetchTSWTZasTimestamp=0

   FetchTWFSasTime=1

   GSSClient=native

   HostName=<server_name>

   HostNameInCertificate=

   InitializationString=

   Language=

   LoadBalanceTimeout=0

   LoadBalancing=0

   LoginTimeout=15

   LogonID=<logon id> (optional)

   MaxPoolSize=100

   MinPoolSize=0

   PacketSize=-1

   Password=<password> (optional)

   Pooling=0

   PortNumber=1433

   QueryTimeout=0

   ReportCodePageConversionErrors=0

   SnapshotSerializable=0

   TrustStore=

   TrustStorePassword=

   ValidateServerCertificate=1

   WorkStationID=

   XMLDescribeType=-10

6. Save the odbc.ini file and exit.

1. Let’s test the ODBC driver using a simple python script. To do that install pyodbc package by running the following command.

   pip install pyodbc

   Tip: If you get an exception saying that it cannot find <sql.h> library, you might have to install unixODBC packages. You can install them by running the following command.

   Debain:

   sudo apt-get install unixodbc unixodbc-dev

   CentOS/Redhat:

   sudo apt-get install unixODBC unixODBC-devel

2. Save the following script to python file and run it to check if you can connect successfully to SQL Server.

   import pyodbc

    

   ##connecting to your database through ODBC

   cnxn = pyodbc.connect('DSN=SQL Server Wire Protocol;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. Note that I have a Northwind database on which I am trying to run the query.

We hope this tutorial helped you to connect to your SQL Server database. Note that we have used the SQL Server Authentication mode for this tutorial, but DataDirect SQL Server ODBC and JDBC drivers also support Windows authentication and Kerberos too.

Also please note that we recommend and require use of the Progress ODBC driver manager, which is shipped with the Progress ODBC drivers. Progress DataDirect ODBC drivers are not certified with the unixODBC Driver Manager and reported issues must be reproducible with a supported version of the DataDirect ODBC Driver Manager for Unix/Linux.

Feel free to try the SQL Server ODBC driver for 30 days and contact us if you need an extension for the trial.