Edit

Linked server for SQL Server with Azure Active Directory authentication

  • Article

Applies to: SQL Server 2022 (16.x)

Linked servers can now be configured with Azure Active Directory (Azure AD) authentication, and it supports two mechanisms for providing credentials:

  • Password
  • Access token

For this article, it's assumed that there are two SQL Server instances (S1 and S2). Both have been configured to support Azure AD authentication, and they trust each other's SSL/TLS certificate. The examples below will be run on server S1 to create a linked server to server S2.

Note

The subject name of the SSL/TLS certificate used by S2 must match the server name provided in the provstr attribute. This should either be the Fully Qualified Domain Name (FQDN) or hostname of S2.

Prerequisites

Linked server configurations for Azure AD authentication

We'll go over configuring linked servers using password authentication and using an Azure application secret or access token.

Linked server configuration using password authentication

For password authentication, using Authentication=ActiveDirectoryPassword in the Provider string will signal the linked server to use Azure AD password authentication. A linked server login must be created to map each login on S1 to an Azure AD login on S2.

  1. In SSMS, connect to S1 and expand Server Objects in the Object Explorer window.

  2. Right-click Linked Servers and select New Linked Server.

  3. Fill in your linked server details:

    • Linked server: S2 or use the name of your linked server.
    • Server type: Other data source.
    • Provider: Microsoft OLE DB Driver for SQL Server.
    • Product name: leave empty.
    • Data source: leave empty.
    • Provider string: Server=<fqdn of S2>;Authentication=ActiveDirectoryPassword.
    • Catalog: leave empty.

    Screenshot of creating linked server with password authentication

  4. Select the Security tab.

  5. Select Add.

    • Local Login: specify the login name used to connect to S1.
    • Impersonate: leave unchecked.
    • Remote User: username of the Azure AD user used to connect to S2, in the format of user@contoso.com.
    • Remote Password: password of the Azure AD user.
    • For a login not defined in the list above, connections will: Not be made

  6. Select OK.

    Screenshot of setting security for linked server

Linked server configuration using access token authentication

For access token authentication, the linked server is created with AccessToken=%s in the Provider string. A linked server login is created to map each login in S1 to an Azure AD application, which has been granted login permissions to S2. The application must have a secret assigned to it, which will be used by S1 to generate the access token. A secret can be created by navigating to the Azure portal > Azure Active Directory > App registrations > YourApplication > Certificates & secrets > New client secret.

Screenshot of creating a new client secret for an application in the Azure portal

  1. In SSMS, connect to S1 and expand Server Objects in the Object Explorer window.

  2. Right-click Linked Servers and select New Linked Server.

  3. Fill in your linked server details:

    • Linked server: S2 or use the name of your linked server.
    • Server type: Other data source.
    • Provider: Microsoft OLE DB Driver for SQL Server.
    • Product name: leave empty.
    • Data source: leave empty.
    • Provider string: Server=<fqdn of S2>;AccessToken=%s.
    • Catalog: leave empty.

    Screenshot of creating linked server with access token authentication

  4. Select the Security tab.

  5. Select Add.

    • Local Login: specify the login name used to connect to S1.
    • Impersonate: leave unchecked.
    • Remote User: client ID of the Azure AD Application used to connect to S2. You can find the Application (client) ID in the Overview menu of your Azure AD Application.
    • Remote Password: Secret ID obtained from creating a New client secret for the application.
    • For a login not defined in the list above, connections will: Not be made

  6. Select OK.

See also