May 21 2020

Authenticate to a WebAPI using AAD with SQL Server

Prerequisites

  1. Have an Azure subscription.
  2. Have an Azure SQL Server in that subscription.
  3. Have an Azure SQL database named “Logbook” hosted by that Azure SQL Server.
  4. Install the Az PowerShell Module.
  5. Install the latest version of SQL Server Management Studio (SSMS) (currently v18.5).

Background Information

  1. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication
  2. https://docs.microsoft.com/en-us/azure/app-service/configure-authentication-provider-microsoft
  3. https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-mvc-app/start-mvc?view=aspnetcore-3.1&tabs=visual-studio
    1. This uses SQL Server Express LocalDB, not full SQL Server.
  4. https://docs.microsoft.com/en-us/azure/active-directory/hybrid/choose-ad-authn
    1. I don’t think we need hybrid authentication at all.
  5. Azure SQL options to consider:
    1. Azure SQL Database
    2. I don’t think we need a Managed Instance.
  6. Authentication methods to consider:
    1. Active Directory Universal Authentication, which includes Multi-Factor Authentication (MFA).
      1. This works with SSMS.
      2. Is this only on-premises (AD) vs. AAD?
    2. Azure AD cloud-only identities
    3. Active Directory Interactive Authentication

Configuration Steps

      1. Create and populate Azure AD.
        1. https://stackoverflow.com/questions/40370571/how-do-i-add-a-microsoft-account-to-azure-active-directory
      2. Optional: Associate or change the active directory that is currently associated with your Azure Subscription.
      3. Create an Azure Active Directory administrator for the Azure SQL Database server, the managed instance, or Azure Synapse.
      4. Configure your client computers.
      5. Create contained database users in your database mapped to Azure AD identities.
      6. Connect to your database by using Azure AD identities.
  2. Create AAD App:
    1. Certificates & secrets > New client secret.
      1. Enter a description, select the validity duration, and select Add.
  3. Create an Azure AD administrator for Azure SQL server
    1. Each Azure SQL server (which hosts a SQL Database) starts with a single server administrator account that is the administrator of the entire Azure SQL server.
    2. A second SQL Server administrator must be created, that is an Azure AD account.
    3. This principal is created as a contained database user in the master database.
    4. As administrators, the server administrator accounts are members of the db_owner role in every user database, and enter each user database as the dbo user.
    5. Provision an Azure Active Directory administrator for your Azure SQL Database server
      1. In the Azure portal, in the upper-right corner, select your connection to drop down a list of possible Active Directories. Choose the correct Active Directory as the default Azure AD. This step links the subscription-associated Active Directory with Azure SQL server making sure that the same subscription is used for both Azure AD and SQL Server.
      2. Chose “Visual Studio Ultimate with MSDN”.
      3. Search for and select SQL server in the Azure Portal.
      4. On SQL Server page, select Active Directory admin.
      5. In the Active Directory admin page, select Set admin.
        1. I could not select my Azure Portal login account.
      6. Created the LogbookAdministrators group in AAD.
        1. Added my Azure Portal login account as Owner and Member of the group.
        2. I was able to use this group as the AAD administrator.
    6. Run SSMS to connect to the SQL database with an account that is a member of the LogbookAdministrators AAD group.
    7. Logged into the SQL Server using SSMS with:
      1. Server Type: Database Engine
      2. Server Name: <Logbook Server Name>
      3. Authentication: Azure Active Directory – Universal with MFA
      4. User Name: member of LogbookAdministrators AAD group
      5. Click “Connect”.
      6. Enter your administrator account name and password when prompted.
  4. Create contained database users in your database mapped to Azure AD identities.
    1. Azure Active Directory authentication requires database users to be created as contained database users. A contained database user based on an Azure AD identity, is a database user that does not have a login in the master database, and which maps to an identity in the Azure AD directory that is associated with the database. The Azure AD identity can be either an individual user account or a group.
    2. Database users (with the exception of administrators) cannot be created using the Azure portal. The access permission must be granted directly in the database using Transact-SQL statements.
    3. Invited my personal e-mail account to this AAD.
      1. Created the LogbookUsers group in AAD.
      2. Added my personal e-mail account to it.
      3. Successfully accepted the invitation via e-mail!
    4. To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
      1. CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
      2. Azure_AD_principal_name can be the user principal name of an Azure AD user or the display name for an Azure AD group.
        1. I think the reason this didn’t work against my user account is because it was invited to the AAD tenant and isn’t an actual AAD account.
      3. You can do this while logged into SSMS as a member of the LogbookAdministrators AAD group.
      4. Right-click on the Logbook database.
      5. Select “New Query” on the Logbook database.
      6. CREATE USER "user@domain.com" FROM EXTERNAL PROVIDER;
        1. Msg 33130, Level 16, State 1, Line 1
          Principal ‘user@domain.com’ could not be found or this principal type is not supported.
        2. Also tried with [] around the account name instead of double-quotes.
        3. Maybe the administrator hasn’t been added to the Logbook database yet?
          1. Read this post on Stack Overflow.
        4. Get-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "logbook" -ServerName "logbook" | Format-List
        5. This returned my group DisplayName for an administrator, not a specific account.
        6. To create a contained database user representing an Azure AD or federated domain group, provide the display name of a security group:
          1. CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;
        7. Tried this instead: CREATE USER [LogbookUsers] FROM EXTERNAL PROVIDER;
        8. Success!
          1. I’m still not sure why my user account didn’t work.
    5. When you create a database user, that user receives the CONNECT permission and can connect to that database as a member of the PUBLIC role. Initially the only permissions available to the user are any permissions granted to the PUBLIC role, or any permissions granted to any Azure AD groups that they are a member of. Once you provision an Azure AD-based contained database user, you can grant the user additional permissions, the same way as you grant permission to any other type of user. Typically grant permissions to database roles, and add users to roles. For more information, see Database Engine Permission Basics. For more information about special SQL Database roles, see Managing Databases and Logins in Azure SQL Database. A federated domain user account that is imported into a managed domain as an external user, must use the managed domain identity.
  5. Note: Removing the Azure Active Directory administrator for Azure SQL server prevents any Azure AD authentication user from connecting to the server. If necessary, unusable Azure AD users can be dropped manually by a SQL Database administrator.
    1. Tried logging in using AAD Password Authentication and got the following:
      1. Cannot connect to logbook.database.windows.net.
      2. ADDITIONAL INFORMATION:
      3. Login failed for user ‘<token-identified principal>’. (Microsoft SQL Server, Error: 18456)
      4. For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
    2. Unfortunately, that link took me to the Microsoft home page.
    3. I had to specify “logbook” for the database name in the options.
  6. Assign contained database users to SQL roles.
    1. ALTER SERVER ROLE db_datareader ADD MEMBER LogbookUsers; failed.
    2. ALTER ROLE db_datareader ADD MEMBER LogbookUsers; succeeded!
    3. ALTER ROLE db_datawriter ADD MEMBER LogbookUsers; succeeded!
  7. I successfully tested creating and deleting rows in logbook database tables!
  8. Create a WebAPI that can create and delete rows in the logbook database.
  9. https://docs.microsoft.com/en-us/azure/active-directory/develop/scenario-web-api-call-api-overview
  10. http://docs.identityserver.io/en/latest/quickstarts/5_entityframework.html