Administrator

Establishing Connectivity: SQL Server ODBC (Linux)

Configure the ODBC connection to a SQL Server database from Linux.

Prerequisites

  • The SQL Server database must be installed on a supported version of Windows.

  • The ODBC connection must be configured to Linux.

  • Download the unixODBC and Freetds open source packages to Linux server, unzip, and install them. Use your favorite operating system or download source, and tools; or use the following examples. Keep in mind that service pack versions change continually, so you may need to adjust your search.

Context

You must edit configuration files to point to the correct SQL Server from Linux.

Procedure

  1. Edit the /freetds.conf file:
    • SuSE ‒ add the following to the end of the file (/etc/freetds/freetds.conf on SuSE Linux):
      [<SQL_server_name>]
      host = <SQL_server_name>.<domain>
      port = 1433
      tds version = 8.0
      client charset = UTF8
    • Red Hat ‒ add the following to the end of the file (/etc/freetds.conf on Red Hat Linux):
      [<SQL_server_name>]
      host = <SQL_server_name>.<domain>.
      port = 1433
      client charset = UTF8
      tds version = 8.0
  2. Edit the /odbc.ini file:
    • SuSE ‒ add the following to the end of the file (/etc/unixODBC/odbc.ini on SuSE Linux):
       [<SQL_server_description>]
      Description = <SQL_server_description>
      Driver = SQLServer
      Servername = <SQL_server_name>
      Database = <SQL_server_DB_name>
      Language = us_english
    • Red Hat ‒ add the following to the end of the file (/etc/odbc.ini on Red Hat Linux):
      [ODBC Data Sources]
      <SQL_server_name> = SQLServer
      
      [<SQL_server_description>]
      Description = <SQL_server_description>
      Driver = SQLServer
      Servername = <SQL_server_name>
      Database = <SQL_server_DB_name>
      Language = us_english
  3. Edit /etc/odbcinst.ini (or /etc/unixODBC/odbcinst.ini on SuSE Linux):
    • SuSE ‒ add the following to the end:
      [SQLServer]
      Description = ODBC For SQLServer
      Driver = /usr/lib64/libtdsodbc.so.0
      Setup = /usr/lib64/libtdsodbc.so.0
      UsageCount=1
      FileUsage=1
    • Red Hat ‒ check the following:
      • In the [ODBCINST] section, make sure there is a line that reads: SQLServer=Installed.
      • Make sure the following section exists:
        [SQLServer]
        Description=ODBC For SQLServer
        Driver=/usr/lib64/libtdsodbc.so.0
        Setup=/usr/lib64/libtdsodbc.so.0
        UsageCount=1
        FileUsage=1
  4. Verify that /freetds.conf configuration works.
    [root@CNPVGLLSSC1052 freetds-0.95rc3]# cd /usr/local/freetds
    [root@CNPVGLLSSC1052 freetds]# cd bin
    [root@CNPVGLLSSC1052 bin]# ./tsql -S <DB_name> -U <<DB Username>> -P <<DB Password>>
    locale is "en_US.UTF-8"
    Locale charset is "UTF-8"
    Using default charset "UTF-8"
    1> select top 5 [toolnum] FROM qatool
    2> go
    toolnum
    111111111
    111111AAA
    111111BBB
    111111CCC
    111111DDD
    (5 rows affected)
    1> exit

    If the rows appear, it means the SQL Server database can connect via /freetds.

  5. Verify that ODBC configuration works.
    [root@CNPVGLLSSC1052 freetds]# cd /usr/bin
    [root@CNPVGLLSSC1052 bin]# ./isql -v <DB_name> <<DB Username>> <<DB Password>>
    +----------------------------------------+
    |  Connected!                            |
    |  sql-statement                         |
    |  help [tablename]                      |
    |  quit                                  |
    |                                        |
    +----------------------------------------+
    SQL>
    

    The message indicates connection to the SQL Server database.