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

Connection Properties

The ODBC section in Using Connection String Keywords with SQL Server Native Client lists all the connection string keywords. However, for this release of the Microsoft SQL Server ODBC Driver for Linux, only the following connection string keywords are valid:

Addr

Address

AutoTranslate

Database

Driver

DSN

Encrypt

FileDSN

MARS_Connection

PWD

Server

Trusted_Connection

TrustServerCertificate

UID

WSID

The value passed to the Driver keyword can either be the name that you used when installing the driver (see Installing the Microsoft SQL Server ODBC Driver for Linux) or it can be the path to the driver, which was also specified in the template .ini file used to install the Microsoft SQL Server ODBC Driver for Linux.

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:

 Copy imageCopy Code
[MSSQLTest]
Driver = SQL Server Native Client 11.0
Server = [protocol:]server[,port]

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

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)

The SQL Server ODBC Driver for Linux lets you use Secure Sockets Layer (SSL) to encrypt connections to SQL Server, which 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 TrustServerCertificate settings.

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 the server you are connecting to has a self-signed certificate, you must also add the TrustServerCertificateOption:

 Copy imageCopy Code
Driver='SQL Server Native Client 11.0';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/certs.pem

Red Hat Enterprise Linux 6

1.0.0-10

/etc/pki/tls/certs.pem

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

See Also