mountainss Cloud and Datacenter Management Blog

Microsoft SystemCenter blogsite about virtualization on-premises and Cloud

Connecting to SQL Database By Using Azure Active Directory Authentication #Azure #SQL #Cloud

Leave a comment


1aad-auth-diagram

Azure Active Directory authentication is a mechanism of connecting to Microsoft Azure SQL Database 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 Database users and simplifies permission management. Benefits include the following:

  • It provides an alternative to SQL Server authentication.
  • Helps stop the proliferation of user identities across database servers.
  • Allows password rotation in a single place
  • Customers can manage database permissions using external (AAD) groups.
  • It can eliminate storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure Active Directory.
  • Azure Active Directory authentication uses contained database users to authenticate identities at the database level.

The configuration steps include the following procedures to configure and use Azure Active Directory authentication.

  1. Create and populate an Azure Active Directory
  2. Ensure your database is in Azure SQL Database V12
  3. Optional: Associate or change the active directory that is currently associated with your Azure Subscription
  4. Create an Azure Active Directory administrator for Azure SQL Server
  5. Configure your client computers
  6. Create contained database users in your database mapped to Azure AD identities
  7. Connect to your database by using Azure AD identities

The following diagram indicates the federation, trust, and hosting relationships that allow a client to connect to a database by submitting a token that was authenticated by an Azure AD, and which is trusted by the database. It’s important to understand that access to a database using Azure AD authentication requires that the hosting subscription is associated to the Azure Active Directory.

2subscription-relationship

Administrator structure

When using Azure AD authentication there will be two Administrator accounts for the SQL Database server; the original SQL Server administrator and the Azure AD administrator. Only the administrator based on an Azure AD account can create the first Azure AD contained database user in a user database. The Azure AD administrator login can be an Azure AD user or an Azure AD group. When the administrator is a group account, it can be used by any group member, enabling multiple Azure AD administrators for the SQL Server instance. Using group account as an administrator enhances manageability by allowing you to centrally add and remove group members in Azure AD without changing the users or permissions in SQL Database. Only one Azure AD administrator (a user or group) can be configured at any time.

3admin-structure

Permissions

To create new users you must have the ALTER ANY USER permission in the database. The ALTER ANY USER permission can be granted to any database user. The ALTER ANY USER permission is also held by the server administrator accounts, and database users with the CONTROL ON DATABASE or ALTER ON DATABASE permission for that database, and by members of the db_owner database role.

To create a contained database user in Azure SQL Database you must connect to the database using an Azure AD identity. To create the first contained database user, you must connect to the database by using an Azure AD administrator (who is the owner of the database). This is demonstrated in steps 4 and 5 below.

Azure AD features and limitations

The following members of Azure Active Directory can be provisioned in Azure SQL Server: – Native members: A member created in Azure AD in the managed domain or in a customer domain. For more information, see Add your own domain name to Azure AD. – Federated domain members: A member created in Azure AD with a federated domain. For more information, see Windows Azure now supports federation with Windows Server Active Directory. – Imported members from other Azure Active Directories who are native or federated domain members. – Active Directory groups created as security groups.

Microsoft accounts (for example outlook.com, hotmail.com, live.com) or other guest accounts (for example gmail.com, yahoo.com) are not supported. If you can login to https://login.live.com using the account and password, then you are using a Microsoft account which is not supported for Azure AD authentication for Azure SQL Database.

Additional considerations

  • To enhance manageability we recommended you provision a dedicated Azure Active Directory group as an administrator.
  • Only one Azure AD administrator (a user or group) can be configured for an Azure SQL Server at any time.
  • Only an Azure Active Directory administrator can initially connect to the Azure SQL Server using an Azure Active Directory account. The Active Directory administrator can configure subsequent Azure Active Directory database users.
  • We recommend setting the connection timeout to 30 seconds.
  • Some tools like BI and Excel are not supported.
  • Azure Active Directory authentication only supports the .NET Framework Data Provider for SqlServer (at least version .NET Framework 4.6). Therefor Management Studio (available with SQL Server 2016) and data-tier applications (DAC and .bacpac) can connect, but sqlcmd.exe cannot connect because sqlcmd uses the ODBC provider.
  • Two-factor authentication or other forms of interactive authentication are not supported.

Here you can read more about the steps Connecting to SQL Database By Using Azure Active Directory Authentication

 

MicrosoftAzure

Advertisements

Author: James van den Berg

I'm Microsoft Architect and ICT Specialist and Microsoft MVP System Center Cloud and Datacenter Management

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s