Using OAuth 2.0

Four types of authentication work flow are available when using OAuth 2.0, token pass-through, client credentials, browser based authentication, or Azure Managed Identity.

This authentication mechanism is available for Spark Thrift Server instances only. When you use OAuth 2.0 authentication, HTTP is the only Thrift transport protocol available. Client credentials and browser based authentication work flow only works when SSL is enabled.

There is a discovery mode that enables the connector to auto-fill some endpoints or configurations. The endpoint discovery is enabled by default, you can disable it by setting EnableOIDCDiscovery=0. You can also pass the OIDC discovery endpoint by using OIDCDiscoveryEndpoint. The connector automatically discovers OAuth2AuthorizationEndPoint and OAuth2TokenEndPoint.

Token Pass-through

This authentication mechanism requires a valid OAuth 2.0 access token. Be aware that access tokens typically expire after a certain amount of time, after which you must either refresh the token or obtain a new one from the server. To obtain a new access token, see Obtaining a New Access Token.

To configure OAuth 2.0 token pass-though authentication:

  1. Set the AuthMech property to 11.
  2. Set the Auth_Flow property to 0.
  3. Set the Auth_AccessToken property to your access token.

Obtaining a New Access Token

Once an access token expires, you can obtain a new access token for the connector.

Note: When an access token expires, the connector returns a "SQLState 08006" error.

To obtain a new access token:

  1. In the connection string, set the Auth_AccessToken property with a new access token.
  2. Call the SQLSetConnectAttr function with SQL_ATTR_CREDENTIALS (122) as the attribute and the new connection string as the value. The connector will update the current connection string with the new access token.

    Note: Calling the SQLGetConnectAttr function with SQL_ATTR_CREDENTIALS (122) returns the entire connection string used during connection.

  3. Call the SQLSetConnectAttr function with SQL_ATTR_REFRESH_CONNECTION (123) as the attribute and SQL_REFRESH_NOW (-1) as the value. This signals the connector to update the access token value.
  4. Retry the previous ODBC API call. After obtaining the new access token, the open connection, statements, and cursors associated with it remain valid for use.

Client Credentials

This authentication mechanism requires SSL to be enabled.

To configure OAuth 2.0 client credentials authentication:

  1. Set the AuthMech property to 11.
  2. Set the Auth_Flow property to 1.
  3. Set the Auth_Client_ID to your client ID.
  4. Set the Auth_Client_Secret to your client secret.
  5. Optionally, set the Auth_Scope to your OAuth scope.

  6. When connecting to Microsoft Synapse, set the Auth_Tenant_ID to your tenant id.

To configure OAuth 2.0 JWT assertion client credentials authentication:

  1. Set the AuthMech property to 11.
  2. Set the Auth_Flow property to 1.
  3. Set the Auth_Client_ID to your client ID.
  4. Set the Auth_Scope to your OAuth scope.
  5. Set the Auth_KID to your key identifier.
  6. Set the Auth_JWT_Key_File to the canonical path to the private key .pem file that matches the public key on the authentication source.
  7. Optionally, set the Auth_JWT_Key_Passphrase to the private key's password, if it is encrypted.
  8. Set EnableOIDCDiscovery to 1 and set OIDCDiscoveryEndpoint to the discovery endpoint.

Browser Based

This authentication mechanism requires SSL to be enabled.

To configure OAuth 2.0 browser based authentication:

  1. Set the AuthMech property to 11.
  2. Set the Auth_Flow property to 2.
  3. Set the TokenCachePassPhrase property to a password of your choice. This is the key used for refresh token encryption.

Note: When the browser based authentication flow completes, the access token and refresh token are saved in the token cache and the connector does not need to authenticate again. For more information, see Enable Token Cache.

Azure Managed Identity

This authentication mechanism requires SSL to be enabled.

To configure Azure Managed Identity authentication:

  1. Set the AuthMech property to 11.
  2. Set the Auth_Flow property to 3.
  3. Optionally, set the Auth_Client_ID to user-assigned managed identity.
  4. Optionally, set the Azure_workspace_resource_id to your assigned Resource ID.