Authenticate to Azure SQL Data Warehouse
Learn how to authenticate to Azure SQL Data Warehouse by using Azure Active Directory (AAD) or SQL Server authentication.
To connect to SQL Data Warehouse, 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 data warehouse, see Secure a database in SQL Data Warehouse.
To connect to SQL Data Warehouse, you must provide the following information:
- Fully qualified servername
- Specify SQL authentication
- 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.
The Transact-SQL statement USE MyDatabase; is not supported for changing the database for a connection. For guidance connecting to SQL Data Warehouse with SSDT, refer to the Query with Visual Studio article.
Azure Active Directory (AAD) authentication
Azure Active Directory authentication is a mechanism of connecting to Microsoft Azure SQL Data Warehouse by using identities in Azure Active Directory (Azure AD). With Azure Active Directory 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 SQL Data Warehouse users and simplifies permission management.
Azure Active Directory benefits include:
- Provides an alternative to SQL Server authentication.
- Helps stop the proliferation of user identities across database servers.
- Allows password rotation in a single place
- Manage database permissions using external (AAD) groups.
- Eliminates storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure Active Directory.
- Uses contained database users to authenticate identities at the database level.
- Supports token-based authentication for applications connecting to SQL Data Warehouse.
- Supports Multi-Factor authentication through Active Directory Universal Authentication for SQL Server Management Studio. For a description of Multi-Factor Authentication, see SSMS support for Azure AD MFA with SQL Database and SQL Data Warehouse.
Azure Active Directory is still relatively new and has some limitations. To ensure that Azure Active Directory is a good fit for your environment, see Azure AD features and limitations, specifically the Additional considerations.
Follow these steps to configure Azure Active Directory authentication.
- Create and populate an Azure Active Directory
- Optional: Associate or change the active directory that is currently associated with your Azure Subscription
- Create an Azure Active Directory administrator for Azure SQL Data Warehouse.
- Configure your client computers
- Create contained database users in your database mapped to Azure AD identities
- Connect to your data warehouse by using Azure AD identities
Currently Azure Active Directory users are not shown in SSDT Object Explorer. As a workaround, view the users in sys.database_principals.
Find the details
- The steps to configure and use Azure Active Directory authentication are nearly identical for Azure SQL Database and Azure SQL Data Warehouse. Follow the detailed steps in the topic Connecting to SQL Database or SQL Data Warehouse By Using Azure Active Directory Authentication.
- Create custom database roles and add users to the roles. Then grant granular permissions to the roles. For more information, see Getting Started with Database Engine Permissions.
To start querying your data warehouse with Visual Studio and other applications, see Query with Visual Studio.