Edit

Connect to Azure Synapse Analytics with Microsoft Entra authentication

This article shows you how to use Microsoft Entra authentication to connect to Azure Synapse Analytics.

Note

Microsoft Entra server principals are currently in preview for Azure Synapse Analytics.

Prerequisites

To connect, you need to configure Microsoft Entra authentication for your Azure Synapse resource.

To confirm the Microsoft Entra administrator is properly set up, connect to the master database by using the Microsoft Entra administrator account.

To create a Microsoft Entra-based contained database user, connect to the database by using a Microsoft Entra identity with access to the database and at least the ALTER ANY USER permission.

Connect with SSMS or SSDT

The following procedures show you how to connect to Synapse SQL with a Microsoft Entra identity by using SQL Server Management Studio (SSMS) or SQL Server Database Tools (SSDT).

Microsoft Entra Integrated

Use this method when you want to sign in by using your Windows credentials that are federated into Microsoft Entra ID. For more information, see Microsoft Entra seamless single sign-on.

  1. Start SSMS or SSDT and in the Login tab of the Connect to Server (or Connect to Database Engine) dialog box:

    1. Enter the fully qualified Server name for your SQL endpoint. For endpoint formats, see Connect to Synapse SQL.
    2. For Authentication, select Microsoft Entra Integrated. You don't need to enter a password because your existing credentials are presented for the connection.
    3. For Encryption, select Strict.

  2. On the Connection properties tab, in the Connect to database field, type the name of the user database you want to connect to.

Microsoft Entra Password

Use this method when connecting with a Microsoft Entra principal name by using the Microsoft Entra managed domain. You can also use it for federated accounts without access to the domain when, for example, you're working remotely.

Use this method to authenticate to Synapse SQL with Microsoft Entra cloud-only identity users or users who have Microsoft Entra hybrid identities. This method supports users who want to use their Windows credential when their local machine isn't joined to the domain, such as when using remote access.

  1. Start SSMS or SSDT and on the Login tab of the Connect to Server (or Connect to Database Engine) dialog box:

    1. Enter the fully qualified Server name for your SQL endpoint. For endpoint formats, see Connect to Synapse SQL.
    2. For Authentication, select Microsoft Entra Password.
    3. In the User name box, type your Microsoft Entra user name in the format username@domain.com. User names must be an account from Microsoft Entra ID or an account from a managed or federated domain with Microsoft Entra ID.
    4. In the Password box, type your user password for the Microsoft Entra account or managed/federated domain account.
    5. For Encryption, select Strict.

  2. On the Connection properties tab, in the Connect to database field, type the name of the user database you want to connect to.

Microsoft Entra MFA

Use this method for interactive authentication with multifactor authentication (MFA), with the password requested interactively. This method can be used to authenticate to Synapse SQL for Microsoft Entra cloud-only identity users or users who have Microsoft Entra hybrid identities.

The following steps show how to connect by using multifactor authentication in the latest version of SSMS.

  1. To connect by using MFA, on the Connect to Server dialog box in SSMS select Microsoft Entra MFA.

  2. Fill the Server name box with your server's name. Fill the User name box with your Microsoft Entra credentials, in the format user_name@domain.com.

  3. Select Connect.

  4. When the Sign in to your account dialog box appears, it is prepopulated with the User name you provided in step 2. No password is required if a user is part of a domain federated with Microsoft Entra ID.

  5. You'll be prompted to authenticate using one of the methods configured based on the MFA administrator setting.

  6. When verification is complete, SSMS connects normally, presuming valid credentials and firewall access.

Microsoft Entra Service Principal

Use this method to authenticate to Synapse SQL with Microsoft Entra service principals.

Microsoft Entra Managed Identity

Use this method to authenticate to Synapse SQL with Microsoft Entra managed identities. For more information, see Managed identities in Microsoft Entra for Azure Synapse Analytics.

Microsoft Entra Default

The Default authentication option with Microsoft Entra ID enables authentication through password-less and non-interactive mechanisms, including managed identities.

Connect from a client application

The following procedures show you how to connect to Synapse SQL with a Microsoft Entra identity from a client application. This list doesn't include all authentication methods. For more information, see Microsoft Entra authentication and SqlClient.

Configure your client applications

Note

System.Data.SqlClient uses the Azure Active Directory Authentication Library (ADAL), which is deprecated. If you're using the System.Data.SqlClient namespace for Microsoft Entra authentication, migrate applications to Microsoft.Data.SqlClient and the Microsoft Authentication Library (MSAL). To understand the connection methods available in .NET, see Microsoft Entra authentication and SqlClient.

If you must continue using ADAL.DLL in your applications, use the links in this section to install the latest ODBC or OLE DB driver, which contain the latest ADAL.DLL library.

On all client machines from which your applications or users connect to Azure Synapse Analytics by using Microsoft Entra identities, install the following software:

You can meet these requirements by:

  • Installing the latest version of SQL Server Management Studio or SQL Server Data Tools to meet the .NET Framework 4.6 requirement.
    • SSMS installs the x86 version of ADAL.DLL.
    • SSDT installs the amd64 version of ADAL.DLL.
    • The latest Visual Studio from Visual Studio Downloads meets the .NET Framework 4.6 requirement but doesn't install the required amd64 version of ADAL.DLL.

Microsoft Entra integrated authentication

To use integrated Windows authentication, your domain's Active Directory must be federated with Microsoft Entra ID, or be a managed domain that's configured for seamless single sign-on for pass-through or password hash authentication. For more information, see Microsoft Entra seamless single sign-on.

To connect to the database, an application (or a service) must run on a domain-joined machine under a user's domain credentials.

To connect to a database using integrated authentication and a Microsoft Entra identity, set the Authentication keyword in the database connection string to Active Directory Integrated. Replace <server_name> with your logical server name. The following C# code sample uses ADO .NET.

string ConnectionString = @"Data Source=<server-name>.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=testdb;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

The connection string keyword Integrated Security=True isn't supported for connecting to Synapse SQL. When making an ODBC connection, remove spaces and set authentication to ActiveDirectoryIntegrated.

Microsoft Entra password authentication

To connect to a database using Microsoft Entra cloud-only identity user accounts, or users who use Microsoft Entra hybrid identities, set the Authentication keyword to Active Directory Password. The connection string must contain User ID/UID and Password/PWD keywords and values. Replace <server_name>, <email_address>, and <password> with the appropriate values. The following C# code sample uses ADO .NET.

string ConnectionString =
@"Data Source=<server-name>.database.windows.net; Authentication=Active Directory Password; Initial Catalog=testdb; UID=<email_address>; PWD=<password>";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

Learn more about Microsoft Entra authentication methods by using the demo code samples available at Microsoft Entra authentication GitHub Demo.

Microsoft Entra ID access token

This authentication method allows middle-tier services to obtain JSON Web Tokens (JWT) from Microsoft Entra ID to connect to Synapse SQL by obtaining a token from Microsoft Entra ID. This method enables various application scenarios including service identities, service principals, and applications using certificate-based authentication. You must complete four basic steps to use Microsoft Entra token authentication:

  1. Register your application with Microsoft Entra ID and get the client ID for your code.
  2. Create a database user representing the application (as described in the Create contained users mapped to Microsoft Entra identities section.)
  3. Create a certificate on the client computer that runs the application.
  4. Add the certificate as a key for your application.

Sample connection string. Replace <server-name> with your logical server name:

string ConnectionString = @"Data Source=<server-name>.database.windows.net; Initial Catalog=testdb;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.AccessToken = "Your JWT token";
conn.Open();

For information about adding a certificate, see Get started with certificate-based authentication in Microsoft Entra ID.

Microsoft Entra multifactor authentication

Microsoft Entra multifactor authentication is a supported authentication method for all SQL tools. For information on programmatically authenticating with Microsoft Entra ID, see the Overview of the Microsoft Authentication Library (MSAL).

sqlcmd

The following statements connect by using version 13.1 of sqlcmd. Download Microsoft Command Line Utilities 14.0 for SQL Server.

Note

sqlcmd with the -G command doesn't work with system identities, and requires a user principal login.

sqlcmd -S <database or datawarehouse name>.<server-name>.database.windows.net -G
sqlcmd -S <database or datawarehouse name>.<server-name>.database.windows.net -U adrian@contoso.com -P <password> -G -l 30

Related content

  • Last updated on