Connection String Keywords and Data Source Names (DSNs)

 

DownloadDownload ODBC Driver

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

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

AddrAddressApplicationIntentAutoTranslateDatabase
DriverDSNEncryptFileDSNMARS_Connection
MultiSubnetFailoverPWDServerTrusted_ConnectionTrustServerCertificate
UIDWSID
System_CAPS_ICON_important.jpg 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 usedatabase_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 -U -P

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 TrustServerCertificate settings.

TrustServerCertificate=falseTrustServerCertificate=true
Encrypt=noServer 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=yesServer 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:

PlatformMinimum OpenSSL VersionDefault Certificate Trust Store Location
Red Hat Enterprise Linux 50.9.8e/etc/pki/tls/cert.pem
Red Hat Enterprise Linux 61.0.0-10/etc/pki/tls/cert.pem
SuSE Linux Enterprise 11 Service Pack 20.9.8j/etc/ssl/certs

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

Microsoft ODBC Driver for SQL Server on Linux
Installing the Microsoft ODBC Driver for SQL Server on Linux

Community Additions

ADD
Show: