ODBC Driver for SQL Server

Build 24.0.8970

Linux DSN Configuration

This section describes how to set up ODBC connectivity and configure DSNs on several Linux distributions: Debian-based systems, like Ubuntu, and Red Hat Linux platforms, like Red Hat Enterprise Linux (RHEL) and Fedora.

Minimum Linux Versions

Here are the minimum supported versions for Red Hat-based and Debian-based systems:

OSMin. Version
Ubuntu18.04
Debian10
RHEL8
Fedora28
SUSE15

Installing the Driver Dependencies

Run the following commands as root or with sudo to install the necessary dependencies:

  • Debian/Ubuntu: 
    apt-get install libc6 libstdc++6 zlib1g libgcc1
  • RHEL/Fedora: 
    yum install glibc libstdc++ zlib libgcc

Installing the Driver

You can use standard package management systems to install the driver.

On Debian-based systems, like Ubuntu, run the following command with root or sudo: 

dpkg -i /path/to/driver/setup/SQLODBCDriverforUnix.deb

On systems that support the RPM package format, run the following command with root or sudo: 

rpm -ivh /path/to/driver/SQLODBCDriverforUnix.rpm

Licensing the Driver

Run the following commands to license the driver. To activate a trial, omit the <key> input. 

cd /opt/cdata/cdata-odbc-driver-for-sql/bin/
sudo ./install-license.sh <key>

Connecting through the Driver Manager

The driver manager loads the driver and passes function calls from the application to the driver. You need to register the driver with the driver manager and you define DSNs in the driver manager's configuration files.

The driver installation registers the driver with the unixODBC driver manager and creates a system DSN. The unixODBC driver manager can be used from Python and from many other applications. Your application may embed another driver manager.

Creating the DSN

See Using unixODBC to install unixODBC and configure DSNs. See Using the DataDirect Driver Manager to create a DSN to connect to OBIEE, Informatica, and SAS.

Connecting to SQL Server

You can use the CData ODBC Driver for SQL Server to connect to any instance of Microsoft SQL Server, Azure SQL Server, or Azure Data Warehouse.

Microsoft SQL Server

Set the following connection properties to connect to SQL Server:

  • Server: The name of the server running SQL Server.
  • Database: The name of the SQL Server database.

Azure SQL Server or AZure Data Warehouse

Set the following connection properties to connect to Azure SQL Server or Azure Data Warehouse:

  • Server: The server running Azure. You can find this by logging into the Azure portal and navigating to SQL databases (or SQL data warehouses) > Select your database > Overview > Server name.
  • Database: The name of the database, as seen in the Azure portal on the SQL databases (or SQL warehouses) page.

Authenticating to SQL Server

The driver supports authenticating to SQL Server using SQL Server authentication, Windows Authentication, or Kerberos authentication.

Microsoft SQL Server

To authenticate to Microsoft SQL Server using your SQL Server user login credentials, set the following:

  • AuthScheme: Set this to Password.
  • User: The username provided for authentication with SQL Server.
  • Password: The password associated with the authenticating user.

Windows

To enable the driver to obtain login credentials automatically from the identity of the windows user running the process, set the following:

  • AuthScheme: Set this to NTLM.
  • IntegratedSecurity: Set this to true.

Kerberos

To authenticate to SQL Server with Kerberos, set AuthScheme to KERBEROS.

Authenticating to SQL Server via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

Retrieve Kerberos Tickets

Kerberos tickets are used to authenticate the requester's identity. The use of tickets instead of formal logins/passwords eliminates the need to store passwords locally or send them over a network. Users are reauthenticated (tickets are refreshed) whenever they log in at their local computer or enter kinit USER at the command prompt.

The driver provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Cerberos Credential Cache Files:

  1. Ensure that the KRB5CCNAME variable is present in your environment.
  2. Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.
  3. To obtain a ticket:
    1. Open the MIT Kerberos Ticket Manager application.
    2. Click Get Ticket.
    3. Enter your principal name and password.
    4. Click OK.

    If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The driver uses the cache file to obtain the Kerberos ticket to connect to SQL Server.

Note: If you would prefer not to edit KRB5CCNAME, you can use the KerberosTicketCache property to set the file path manually. After this is set, the driver uses the specified cache file to obtain the Kerberos ticket to connect to SQL Server.

Keytab File

If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.

To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.

To use this method, set the User and Password properties to the user/password combination that you use to authenticate with SQL Server.

Enabling Cross-Realm Authentication

More complex Kerberos environments can require cross-realm authentication where multiple realms and KDC servers are used. For example, they might use one realm/KDC for user authentication, and another realm/KDC for obtaining the service ticket.

To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

NTLM

You can use Standard credentials to authenticate to an Azure-hosted SQL Server. To authenticate, set the following:
  • AuthScheme: NTLM (Windows Credentials).
  • User: The name of the user authenticating to Azure.
  • Password: The password associated with the authenticating user.

Alternatively, you can use OAuth by setting AuthScheme to AzureAd, AzurePassword, or AzureMSI. All OAuth connections require setting the AzureTenant connection property to the Id of the tenant the SQL Server database is hosted on.

Azure AD

Azure AD is Microsoft’s multi-tenant, cloud-based directory and identity management service. It is user-based authentication that requires that you set AuthScheme to AzureAD.

Authentication to Azure AD over a Web application always requires the creation of a custom OAuth application. For details, see Creating a Custom Azure AD Application.

Desktop Applications

CData provides an embedded OAuth application that simplifies connection to Azure AD from a Desktop application.

You can also authenticate from a desktop application using a custom OAuth application. (For further information, see Creating a Custom Azure AD Application.) To authenticate via Azure AD, set these parameters:

  • AuthScheme: AzureAD.

  • Custom applications only:

    • OAuthClientId: The client Id assigned when you registered your custom OAuth application.
    • OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.
    • CallbackURL: The redirect URI you defined when you registered your custom OAuth application.

When you connect, the driver opens SQL Server's OAuth endpoint in your default browser. Log in and grant permissions to the application.

The driver completes the OAuth process, obtaining an access token from SQL Server and using it to request data. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the driver refreshes it automatically.

Headless Machines

To configure the driver with a user account on a headless machine, you must authenticate on another device that has an internet browser.

You can do this in either of the following ways:

  • Obtain the OAuthVerifier value as described below in Option 1: Obtain and Exchange a Verifier Code.
  • Install the driver on another machine as described below in Option 2: Transfer OAuth Settings. After you authenticate via the usual browser-based flow, transfer the OAuth authentication values.

Option 1: Obtain and Exchange a Verifier Code

  1. Find the authorization endpoint.

    Custom applications only: Set these properties to create the Authorization URL:

    • OAuthClientId: The client Id assigned when you registered your application.
    • OAuthClientSecret: The client secret assigned when you registered your application.

    Custom and embedded applications: Call the GetOAuthAuthorizationUrl stored procedure.

    1. Open the URL returned by the stored procedure in a browser.
    2. Log in and grant permissions to the driver. You are redirected to the callback URL, which contains the verifier code.
    3. Save the value of the verifier code. You will use this later to set the OAuthVerifier connection property.

  2. Exchange the OAuth verifier code for OAuth refresh and access tokens.

    At the headless machine, set these properties:

    • AuthScheme: AzureAD.
    • OAuthVerifier: The verifier code.
    • OAuthSettingsLocation: The location of the file that holds the OAuth token values that persist across connections.

    • Custom applications only:

      • OAuthClientId: The client Id in your custom OAuth application settings.
      • OAuthClientSecret: The client secret in the custom OAuth application settings.

  3. After the OAuth settings file is generated, reset the following properties to connect:

    • OAuthSettingsLocation: The location containing the encrypted OAuth authentication values. Make sure this location grants read and write permissions to the driver to enable the automatic refreshing of the access token.

    • Custom applications only:

      • OAuthClientId: The client Id assigned when you registered your application.
      • OAuthClientSecret: The client secret assigned when you registered your application.

Option 2: Transfer OAuth Settings

Before you can connect via a headless machine, you must create and install a connection with the driver on a device that supports an internet browser. Set the connection properties as described above, in Desktop Applications.

After you complete the instructions in Desktop Applications, the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.

At the headless machine, set these properties:

  • AuthScheme: AzureAD.
  • OAuthSettingsLocation: The location of your OAuth settings file. Make sure this location gives read and write permissions to the driver to enable the automatic refreshing of the access token.

  • Custom applications only:

    • OAuthClientId: The client Id assigned when you registered your application.
    • OAuthClientSecret: The client secret assigned when you registered your application.

Azure Service Principal

Service principals are security objects within an Azure AD application that define what that application can do within a particular Azure AD tenant. Service Principals are created in the Azure service portal. As part of the creation process we also specify whether the service principal will access Azure AD resources via a client secret or a certificate.

Instead of being tied to a particular user, service principal permissions are based on the roles assigned to them. The application access to the resources is controlled through the assigned roles' permissions.

When authenticating using an Azure Service Principal, you must register an application with an Azure AD tenant, as described in Creating an Azure AD Application with Service Principal.

You are ready to connect after setting the properties described in this subsection. These vary, depending on whether you will authenticate via a client secret or a certificate.

Authentication with Client Secret

  • AuthScheme: AzureServicePrincipal.
  • AzureTenant: The Azure AD tenant to which you wish to connect.
  • OAuthGrantType: CLIENT.
  • OAuthClientId: The client Id in your application settings.
  • OAuthClientSecret: The client secret in your application settings.

Authentication with Certificate

  • AuthScheme: AzureServicePrincipalCert.
  • AzureTenant: The Azure AD tenant to which you wish to connect.
  • OAuthGrantType: CLIENT.
  • OAuthClientId: The client Id in your application settings.
  • OAuthJWTCert: The JWT Certificate store.
  • OAuthJWTCertType: The JWT Certificate store type.

Azure Password

Set AuthScheme to AzurePassword.

To connect using your Azure credentials directly, specify the following connection properties:

  • User: The user account for connecting to Azure.
  • Password: The user account password for connecting to Azure.
  • AzureTenant: The Directory (tenant) ID of the custom Azure AD application used to authenticate to SQL Server on Azure. Found on the custom application's Overview page.

Managed Service Identity (MSI)

If you are running SQL Server on an Azure VM and want to leverage MSI to connect, set AuthScheme to AzureMSI.

User-Managed Identities

To obtain a token for a managed identity, use the OAuthClientId property to specify the managed identity's "client_id".

When your VM has multiple user-assigned managed identities, you must also specify OAuthClientId.

Set the Driver Encoding

The ODBC drivers need to specify which encoding to use with the ODBC Driver Manager. By default, the CData ODBC Drivers for Unix are configured to use UTF-16 which is compatible with unixODBC, but other Driver Managers may require alternative encoding.

Alternatively, if you are using the ODBC driver from an application that uses the ANSI ODBC API it may be necessary to set the ANSI code page. For example, to import Japanese characters in an ANSI application, you can specify the code page in the config file '/opt/cdata/cdata-odbc-driver-for-sql/lib/cdata.odbc.sql.ini': 

[Driver]
AnsiCodePage = 932

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.8970