Show TOC

Establishing Connectivity: SQL Server ODBC (Linux)Locate this document in the navigation structure

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.
    Note
    • SuSE ‒ obtain TDS and its ODBC driver from http://download.opensuse.org/repositories/devel:/libraries:/c_c++/SLE_11_SP3/x86_64/; search for:
      • libfreetds-0.91-31.2.x86_64.rpm

      • libtdsodbc0-0.91-31.2.x86_64.rpm

      You can use Yast, which is included with Linux, to install and configure unixODBC.

    • Red Hat ‒ obtain TDS from http://pkgs.repoforge.org/rpmforge-release/; search for: rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm. You can use the unixODBC package that is installed in Red Hat by default, or use the Yum installer.

    • You must install unixODBC first, and then Freetds.

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:
    Note

    Add an entry for each database, making sure that the entries in the freetds and odbc .INI files match.

    • 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.