Muokkaa

Jaa

Authenticate to dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics

  • Artikkeli

Learn how to authenticate to dedicated SQL pool (formerly SQL DW) in Azure Synapse by using Microsoft Entra ID or SQL Server authentication.

To connect to a dedicated SQL pool (formerly SQL DW), you must pass in security credentials for authentication purposes. Upon establishing a connection, certain connection settings are configured as part of establishing your query session.

For more information on security and how to enable connections to your dedicated SQL pool (formerly SQL DW), see securing a database documentation.

SQL authentication

To connect to dedicated SQL pool (formerly SQL DW), you must provide the following information:

  • Fully qualified servername
  • Specify SQL authentication
  • Username
  • Password
  • Default database (optional)

By default, your connection connects to the master database and not your user database. To connect to your user database, you can choose to do one of two things:

  • Specify the default database when registering your server with the SQL Server Object Explorer in SSDT, SSMS, or in your application connection string. For example, include the InitialCatalog parameter for an ODBC connection.
  • Highlight the user database before creating a session in SSDT.

Note

The Transact-SQL statement USE MyDatabase; is not supported for changing the database for a connection. For guidance connecting to a SQL pool with SSDT, refer to the Query with Visual Studio article.

Microsoft Entra authentication

Microsoft Entra ID authentication is a mechanism of connecting to SQL pool by using identities in Microsoft Entra ID. With Microsoft Entra authentication, you can centrally manage the identities of database users and other Microsoft services in one central location. Central ID management provides a single place to manage dedicated SQL pool (formerly SQL DW) users and simplifies permission management.

Benefits

Microsoft Entra ID benefits include:

  • Provides an alternative to SQL Server authentication.
  • Helps stop the proliferation of user identities across servers.
  • Allows password rotation in a single place
  • Manage database permissions using external (Microsoft Entra ID) groups.
  • Eliminates storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Microsoft Entra ID.
  • Uses contained database users to authenticate identities at the database level.
  • Supports token-based authentication for applications connecting to SQL pool.
  • Supports Multi-Factor authentication through Active Directory Universal Authentication for various tools including SQL Server Management Studio and SQL Server Data Tools.

Note

Microsoft Entra ID is still relatively new and has some limitations. To ensure that Microsoft Entra ID is a good fit for your environment, see Microsoft Entra features and limitations, specifically the Additional considerations.

Configuration steps

Follow these steps to configure Microsoft Entra authentication.

  1. Create and populate a Microsoft Entra ID
  2. Optional: Associate or change the active directory that is currently associated with your Azure Subscription
  3. Create a Microsoft Entra administrator for Azure Synapse
  4. Configure your client computers
  5. Create contained database users in your database mapped to Microsoft Entra identities
  6. Connect to your SQL pool by using Microsoft Entra identities

Currently Microsoft Entra users are not shown in SSDT Object Explorer. As a workaround, view the users in sys.database_principals.

Find the details

Next steps

To start querying with Visual Studio and other applications, see Query with Visual Studio.