ODBC Driver for SQL Server

Build 21.0.7958

Creating a Custom OAuth App

When to Create a Custom OAuth App

Creating a custom OAuth app is optional as the driver is already registered with SQL Server and you can connect with its embedded credentials.

You might want to create a custom OAuth app to change the information displayed when users log into the SQL Server OAuth endpoint to grant permissions to the driver.

Follow the steps below to create a custom OAuth app and obtain the connection properties in a specific OAuth authentication flow.

Steps to Create a Custom OAuth App

Follow the steps below to obtain the OAuth values for your app, the OAuthClientId and OAuthClientSecret.

  1. Log in to https://portal.azure.com.
  2. In the left-hand navigation pane, select Azure Active Directory then App Registrations and click the New registration button.
  3. Enter an app name and set the radio button for the desired tenant setup. When creating a custom OAuth application in Azure Active Directory, you can define if the application is single- or multi-tenant. If you select the default option of "Accounts in this organizational directory only", you will need to set the AzureTenant connection property to the ID of the Azure AD Tenant when establishing a connection with the CData ODBC Driver for SQL Server. Otherwise, the authentication attempt will fail with an error. If your app is for private use only, "Accounts in this organization directory only" should be sufficient. Otherwise, if you want to distribute your app, choose one of the multi-tenant options.
  4. Then set the redirect url to something such as http://localhost:33333, the driver's default. Or, set a different port of your choice and set CallbackURL to the exact reply URL you defined.
  5. Define the app authentication type by going to the Certificates & Secrets section. There are two types of authentication available: using a client secret and using a certificate. The recommended authentication method is via a certificate, but you can also create an application secret.
    • Option 1 - Upload a certificate: In the Certificates & Secrets section, select Upload certificate and select the certificate to upload from your local machine.
    • Option 2 - Create a new application secret: In the Certificates & Secrets section, select New Client Secret for the app and select its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once, and it is used as the OAuthClientSecret.
  6. Select API Permissions and then click Add. If you plan for your app to connect without a user context, select the Application Permissions (OAuthGrantType = CLIENT). Otherwise, when selecting permissions, use the Delegated permissions.
  7. Save your changes.
  8. If you have selected to use permissions that require admin consent (such as the Application Permissions), you may grant them from the current tenant on the API Permissions page.
  9. Set the AuthScheme to AzureAD. This is required to create users for the OAuth app.
  10. Add the user to the database by running: CREATE USER [OAuth_APP] FROM EXTERNAL PROVIDER. This command must be run by the SQL Active Directory admin asssigned to the SQL Server instance.
  11. Enable the Directory readers role for the Active Directory admin user.

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7958