SQL Server ODBC Driver for Linux | Tutorial

Introduction

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

Installing Progress DataDirect SQL Server ODBC driver on Linux

  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’

    Confirm Your OS - Linux 

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

    license 

  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.

    Evaluation Information 

  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.
    single driver
  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.

    Select SQL Server 

  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.

     

    Install Path 

     

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

Install Another Driver

 

 

Configuring SQL Server ODBC Driver on Linux

  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.

Testing the SQL Server ODBC Connection

  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.

##Executing a simple query and printing the records

cursor.execute("SELECT EmployeeID, LastName, FirstName, Address, City, Region from Northwind.dbo.Employees")

for row in cursor.fetchall():

    print (row)

 

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. Feel free to try the SQL Server ODBC driver for 30 days and contact us if you need an extension for the trial.

ODBC TUTORIAL

SQL Server ODBC Driver for Linux | Tutorial

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers