Installing the Microsoft ODBC Driver for SQL Server on Linux

System Requirements

SQL Server 2012

This topic lists the requirements to use the Microsoft ODBC Driver 11 for SQL Server on Linux.

You can download the ODBC driver for Red Hat Linux at Microsoft ODBC Driver for SQL Server

You can download the CTP for 64-bit SUSE Linux Enterprise 11 Service Pack 2 at Microsoft ODBC Driver 11 for SQL Server - SUSE Linux Community Technology Preview.

ODBC driver for 64-bit Red Hat Enterprise Linux 5 requires the following packages:
- glibc
- libgcc
- libstdc++
- e2fsprogs-libs
- krb5-libs
- openssl
ODBC driver for 64-bit Red Hat Enterprise Linux 6 requires the following packages:
- glibc
- libgcc
- libstdc++
- libuuid
- krb5-libs
- openssl
ODBC driver for 64-bit SUSE Linux Enterprise 11 Service Pack 2 (driver still in CTP) requires the following packages:
- glibc
- libstdc++46
- libgcc46
- libuuid1
- krb5
- libopenssl0_9_8
64-bit UnixODBC 2.3.0 Driver Manager, built for 64-bit SQLLEN/SQLULEN. Later versions of the 64-bit UnixODBC Driver Manager are not supported with the ODBC driver on Linux.

Installing the Driver Manager

This topic contains instructions to install the unixODBC Driver Manager.

Important
Delete any driver manager packages installed on your computer before you install the unixODBC Driver Manager. Installing the unixODBC Driver Manager could cause a failure of an existing Driver Manager.

Using the Installation Script

Important
These instructions refer to msodbcsql-11.0.2270.0.tar.gz, which is the installation file for Red Hat Linux. If you are installing the CTP for SUSE Linux, the file name is msodbcsql-11.0.2260.0.tar.gz.

To install the driver manager:

Make sure that you have root permission.
Go to the directory where the Microsoft SQL Server ODBC Driver download placed the file called msodbcsql-11.0.2270.0.tar.gz. Make sure that you have the *.tar.gz file that matches your version of Linux. To extract the files, execute the following command: tar xvzf msodbcsql-11.0.2270.0.tar.gz.
Change to the msodbcsql-11.0.2270.0 directory and there you should see a file called build_dm.sh. You can run build_dm.sh to install the unixODBC Driver Manager.
To see a list of the available options, execute the following command: ./build_dm.sh --help.
When you are ready to install, and if your computer can access an external site via FTP, execute the following command: ./build_dm.sh.

If your computer cannot access an external site via FTP, get unixODBC-2.3.0.tar.gz. You can get unixODBC-2.3.0.tar.gz from http://www.unixodbc.org. Click the Download link on the left side of the page to go to the download page. Then click the appropriate link to download unixODBC-2.3.0 (not unixODBC-2.3.1). UnixODBC-2.3.1 is not supported with this release of the Microsoft ODBC Driver 11 for SQL Server. Execute the following command to begin the unixODBC Driver Manager installation: ./build_dm.sh --download-url=file://unixODBC-2.3.0.tar.gz.
Type YES to proceed with unpacking the files. This part of the process can take up to 5 minutes to complete.
After the script stops running, follow the instructions on the screen to install the unixODBC Driver Manager.

You are now ready to install the driver.

Manual Installation

If the installation script is unable to complete, configure and build the proper driver manager yourself.

Remove any older installed version of unixODBC (for example, unixODBC 2.2.11). On Red Hat Enterprise Linux 5 or 6, execute the following command: yum remove unixODBC. On SUSE Linux Enterprise, zypper remove unixODBC.
Go to http://www.unixodbc.org. Click the Download link on the left side of the page to go to the download page. Then click the appropriate link to save the file unixODBC-2.3.0.tar.gz to your computer. UnixODBC-2.3.1 is not supported with this release of the Microsoft ODBC Driver 11 for SQL Server.
On your Linux computer, execute the command: tar xvzf unixODBC-2.3.0.tar.gz.
Change to the unixODBC-2.3.0 directory.
At a command prompt, execute the command: CPPFLAGS="-DSIZEOF_LONG_INT=8".
At a command prompt, execute the command: export CPPFLAGS.
At a command prompt, execute the command: "./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE".
At a command prompt (logged in as root), execute the command: make.
At a command prompt (logged in as root), execute the command: make install.

You are now ready to install the driver.

Installing the Microsoft ODBC Driver for SQL Server on Linux

This topic explains how to install the Microsoft ODBC Driver 11 for SQL Server on Linux. Before you can begin to use the driver, install the unixODBC driver manager. See Installing the Driver Manager for more information.

Installation Steps

Important
These instructions refer to msodbcsql-11.0.2270.0.tar.gz, which is installation file for Red Hat Linux. If you are installing the CTP for SUSE Linux, the file name is msodbcsql-11.0.2260.0.tar.gz.

To install the driver:

Make sure that you have root permission.
Change to the directory where the ODBC driver on Linux placed the file called msodbcsql-11.0.2270.0.tar.gz. Make sure that you have the *.tar.gz file that matches your version of Linux. To extract the files, execute the following command, tar xvzf msodbcsql-11.0.2270.0.tar.gz.
Change to the msodbcsql-11.0.2270.0 directory and there you should see a file called install.sh.
To see a list of the available installation options, execute the following command: ./install.sh.
Make a backup of odbcinst.ini. The driver installation updates odbcinst.ini. odbcinst.ini contains the list of drivers that are registered with the unixODBC Driver Manager. To discover the location of odbcinst.ini on your computer, execute the following command: odbc_config --odbcinstini.
Before you install the driver, execute the following command: ./install.sh verify. The output of ./install.sh verify reports if your computer has the required software to support the ODBC driver on Linux.
When you are ready to install the ODBC driver on Linux, execute the command: ./install.sh install. If you need to specify an install command (bin-dir or lib-dir), specify the command after the install option.
After reviewing the license agreement, type YES to continue with the installation.

Installation puts the driver in /opt/microsoft/msodbcsql/11.0.2270.0. The driver and its support files must be in /opt/microsoft/msodbcsql/11.0.2270.0.

To verify that the ODBC driver on Linux was registered successfully, execute the following command: odbcinst -q -d -n "ODBC Driver 11 for SQL Server".

Use Existing MSDN C++ ODBC Samples for the ODBC Driver on Linux shows a code sample that connects to SQL Server using the ODBC driver on Linux.

Troubleshooting Connection Problems

If you are unable to make a connection to SQL Server using the ODBC driver on Linux, use the following information to identify the problem.

The most common connection problem is to have two copies of the UnixODBC Driver Manager installed. Search /usr for libodbc*.so*. If you see more than one version of the file, you (possibly) have more than one driver manager installed. Your application might use the wrong version. If you see the UnixODBC Driver Manager installed package when you execute (on Red Hat) the command yum list unixODBC, delete the package.

Enable the connection log by verifying that your odbcinst.ini file contains the section and these items:

[ODBC]
Trace = Yes
TraceFile = (your log file)

If you get another connection failure and do not see a log file, there (possibly) are two copies of the driver manager on your computer. Otherwise, the log output should be similar to the following:

[ODBC][28783][1321576347.077780][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x17c858e0
            Window Hdl = (nil)
            Str In = [DRIVER={ODBC Driver 11 for SQL Server};SERVER={contoso.com};Trusted_Connection={YES};WSID={mydb.contoso.com};AP...][length = 139 (SQL_NTS)]
            Str Out = (nil)
            Str Out Max = 0
            Str Out Ptr = (nil)
            Completion = 0
        UNICODE Using encoding ASCII 'UTF8' and UNICODE 'UTF16LE'

If the character encoding is not UTF-8, for example:

UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

There is more than one Driver Manager installed and your application is using the wrong one. Or, the Driver Manager was not built correctly.

Uninstalling the ODBC Driver on Linux

You can uninstall the ODBC driver on Linux, by executing the following commands:

rm -f /usr/bin/sqlcmd
rm -f /usr/bin/bcp
rm -rf /opt/microsoft/msodbcsql
odbcinst -u -d -n "ODBC Driver 11 for SQL Server"

Connection String Keywords and Data Source Names (DSNs)

This topic discusses how you can create a connection to a SQL Server database.

Connection Properties

For this release of the Microsoft ODBC Driver for SQL Server on Linux, you can use the following connection keywords:

Addr	Address	ApplicationIntent	AutoTranslate	Database
Driver	DSN	Encrypt	FileDSN	MARS_Connection
MultiSubnetFailover	PWD	Server	Trusted_Connection	TrustServerCertificate
UID	WSID

Important
When connecting to a database that uses database mirroring (has a failover partner), do not specify the database name in the connection string. Instead, send a use database_name command to connect to the database before executing your queries.

For more information about these keywords, see the ODBC section of Using Connection String Keywords with SQL Server Native Client.

The value passed to the Driver keyword can either be:

The name you used when you installed the driver. Or,
The path to the driver, which was specified in the template .ini file used to install the driver.

To create a DSN, create (if necessary) and edit the file ~/.odbc.ini (odbc.ini in your home directory). The following is a sample file that shows the required entries for a DSN:

[MSSQLTest]
Driver = ODBC Driver 11 for SQL Server
Server = [protocol:]server[,port]
# 
# Note:
# Port is not a valid keyword in the ~/.odbc.ini file
# for the Microsoft ODBC driver on Linux
#

You can optionally specify the protocol and port to connect to the server. For example, Server = tcp:servername,12345.

To connect to a named instance on a static port, use Server = servername,port_number. Connecting to dynamic port is not supported.

Optionally, you can add the DSN information to a template file and execute the following command: odbcinst -i -s -f template_file

You can verify that your driver is working by using isql to test the connection. Or, you can use this command: bcp master.INFORMATION_SCHEMA.TABLES out OutFile.dat -S <server> -U <name> -P <password>

Using Secure Sockets Layer (SSL)

You can use Secure Sockets Layer (SSL) to encrypt connections to SQL Server. SSL protects SQL Server user names and passwords over the network. SSL also verifies the identity of the server to protect against man-in-the-middle (MITM) attacks.

Enabling encryption increases security at the expense of performance.

For more information, see Encrypting Connections to SQL Server.

Regardless of the settings for Encrypt and TrustServerCertificate, the server login credentials (user name and password) are always encrypted. The following table shows the effect of the Encrypt and TrustServerCertificatesettings.

	TrustServerCertificate=false	TrustServerCertificate=true
Encrypt=no	Server certificate is not checked. Data sent between client and server is not encrypted.	Server certificate is not checked. Data sent between client and server is not encrypted.
Encrypt=yes	Server certificate is checked. Data sent between client and server is encrypted. The name (or IP address) in a Subject Common Name (CN) or Subject Alternative Name (SAN) in a SQL Server SSL certificate should exactly match the server name (or IP address) specified in the connection string.	Server certificate is not checked. Data sent between client and server is encrypted.

By default, encrypted connections always verify the server’s certificate. However, if you connect to a server that has a self-signed certificate, also add the TrustServerCertificateOption:

Driver='ODBC Driver 11 for SQL Server';Server=ServerNameHere;Encrypt=YES;TrustServerCertificate=YES

SSL uses the OpenSSL library. The following table shows the minimum supported versions of OpenSSL and the default Certificate Trust Store locations for each platform:

Platform	Minimum OpenSSL Version	Default Certificate Trust Store Location
Red Hat Enterprise Linux 5	0.9.8e	/etc/pki/tls/cert.pem
Red Hat Enterprise Linux 6	1.0.0-10	/etc/pki/tls/cert.pem
SuSE Linux Enterprise 11 Service Pack 2	0.9.8j	/etc/ssl/certs

You can use SQLDriverConnect to specify encryption in the connection string.

Data Access Tracing with the ODBC Driver on Linux

The ODBC Driver for SQL Server on Linux supports tracing of ODBC API call entry and exit.

To trace your application behavior, first add the following line to the odbcinst.ini file:

Trace=Yes

Then start your application with strace. For example:

strace -t -f -o trace_out.txt executable

After you finish tracing your application, remove Trace=Yes from the odbcinst.ini file to avoid the performance penalty of tracing.

Tracing applies to all applications that use the driver in odbcinst.ini. To not trace all applications (for example, to avoid disclosing sensitive per-user information), you can trace an individual application instance by providing it the location of a private odbcinst.ini, by using the ODBCSYSINI environment variable. For example:

$ ODBCSYSINI=/home/myappuser myapp

In this case, you can add Trace=Yes to the [ODBC Driver 11 for SQL Server] section of /home/myappuser/odbcinst.ini.

Determining Which ODBC.ini File the Driver is Using

The Linux ODBC Driver does not know which ODBC INI is in use, or the path to the ODBC INI file. So, the driver cannot trace the ODBC INI file.

Information about which ODBC.ini file is in use is available, however, from the unixODBC tools odbc_config and odbcinst, and from the unixODBC Driver Manager documentation.

For example, the following command prints (among other information) the location of system and user ODBC INI files that may contain, respectively, system and user DSNs:

$ odbcinst -j
unixODBC 2.3.0
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/odbcuser/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

The unixODBC documentation explains how the user vs. system DSN decision is made. Specifically:

User DSN. These are your personal Data Sources. You are able to add new ones, remove existing ones, and configure existing ones. User DSN information is stored in a secret location where only you can access them. Keeping your User DSN's separate from other User DSN's allows you a great deal of flexibility and control over creating and working with data sources which are only important to you.

System DSN. These are created by the System Administrator. They act very much like the User DSN's but with three important differences:

Only the System Administrator can add, remove, and configure System DSN's.
System DSN's will be used only if the DSN does not exist as a User DSN. In other words, your User DSN has precedence over the System DSN.
Everyone shares the same list of System DSN's.

Thank You.

LINUX Help By Nitin Tambe

Wednesday, September 9, 2015