ODBC Driver for SQL Server

Build 24.0.9060

Windows DSN Configuration

Using the Microsoft ODBC Data Source Administrator

You can use the Microsoft ODBC Data Source Administrator to edit the DSN configuration. Note that the DSN is created during the installation process.

Complete the following steps to edit the DSN configuration:

  1. Select Start > Search, and enter ODBC Data Sources in the Search box.
  2. Choose the version of the ODBC Administrator that corresponds to the bitness of your application (32-bit or 64-bit).
  3. Click the System DSN tab.
  4. Select the system data source and click Configure.
  5. Edit the information on the Connection tab and click OK.

Note: For .NET Framework 4.0, the driver distributes Microsoft Visual C++ 2015-2022 Redistributable. For .NET Framework 3.5, the driver distributes Microsoft Visual C++ 2008 Redistributable.

Ensuring Registry Access

The driver stores connection information in the Windows registry. To ensure that the driver can write to the registry, perform either of the following actions:

  1. Run the calling application as an administrator.
  2. Connect via a User DSN instead of a System DSN.

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 authenticate with a username and password using the NTLM authentication mechanism. If a username and password are not specified, the driver authenticates using your current Windows credentials (only supported in .NET Framework).

To authenticate, set the following:

  • AuthScheme: NTLM (Windows Credentials).
  • User: The name of the authenticating user.
  • 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.

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