Skip to content

Latest commit

 

History

History
 
 

WebApp-Connect-To-Azure-Sql-Database

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
Client
Client
 
 
README.md
README.md
 
 
blazorserver-Connect-To-Azure-SQL-Database.sln
blazorserver-Connect-To-Azure-SQL-Database.sln
 
 

README.md

page_type name services platforms urlFragment description languages products
sample
How to use an authenticated user's credentials for log-in to an Azure SQL Database from Blazor Web Server App
active-directory
dotnet
ms-identity-dotnet-blazor-azure-sql
This sample demonstrates how to use access token obtained from AAD for connecting to Azure SQL Server database as a user that is logged in into the application.
csharp
t-sql
aspnet-core
blazor
azure-active-directory
azure-sql

How to use an authenticated user's credentials for log-in to an Azure SQL Database from Blazor Web Server App

Build status

Table Of Contents

Scenario

This sample demonstrates a Blazor Server App querying an Azure SQL Database with the same authenticated user logged-in into the database. In other words, SQL Database will act exactly for user logged-in instead of active with administrator access rights.

Scenario Image

Prerequisites

Setup the sample

Step 1: Clone or download this repository

From your shell or command line:

    git clone https://github.com/Azure-Samples/ms-identity-blazor-server.git

or download and extract the repository .zip file.

⚠️ To avoid path length limitations on Windows, we recommend cloning into a directory near the root of your drive.

Step 2: Setup Azure SQL Database and grant user permissions for managed identity

  1. Create an Azure SQL Database.

    • The Sql database Server should have either Use only Azure Active Directory (Azure AD) authentication or Use both SQL and Azure AD authentication set up as Authentication method.

  2. Add one or more of this Azure AD tenant's user as or "Azure Active Directory admin". You would use this user to execute the next set of Sql statements.

  3. Install SQL Server Management Studio and connect to your newly created Azure SQL database using the account you set as "Azure Active Directory admin".

  4. In your newly created Database, run the following SQL statements to create and populate a database table to be used in this sample.

    CREATE TABLE [dbo].[Summary](
[Summary] [nvarchar](50) NOT NULL) 
    Insert into [dbo].Summary values ('Freezing'),('Bracing'),('Chilly'),('Cool'),('Mild'),('Warm'),('Balmy'),('Hot'),('Sweltering'),('Scorching')
    CREATE FUNCTION [dbo].[UsernamePrintFn]()
RETURNS nvarchar(500)
AS
BEGIN
    declare @host nvarchar(100), @user nvarchar(100);
    SELECT @host = HOST_NAME() , @user = SUSER_NAME()
    declare @result nvarchar(500) = cast(@user + ' at ' + @host as nvarchar(500))
    -- Return the result of the function
    return @result
END   
    /**
You can use the following command to ensure that the table and function were correctly created and work as expected
**/
SELECT * FROM [dbo].Summary
GO

SELECT [dbo].[UsernamePrintFn] ()
GO
       /**
Create a user in database from users in your Tenant and grant them EXECUTE permission by running next set of commands.
You can add more directory users to this database by running these statements repeatedly.
**/
DECLARE @AADDBUser nvarchar(128)
SET @AADDBUser = '<myusername>@<mytenant>.onmicrosoft.com'

DECLARE @sql as varchar(max)
SET @SQL = 'CREATE USER [' + @AADDBUser + '] FROM EXTERNAL PROVIDER;
EXECUTE sp_addrolemember db_datareader, ''' + @AADDBUser + ''';
grant execute to ''' + @AADDBUser +''''

EXEC @SQL

  5. Update connection string inside appsettings.json with server and database names

  6. You might need to update the database Firewall with your IP address.

Step 3: Application Registration

There is one project in this sample. To register it, you can:

Follow the manual steps

OR

Run automation scripts

  • use PowerShell scripts that:

    • automatically creates the Azure AD applications and related objects (passwords, permissions, dependencies) for you.
    • modify the projects' configuration files.
    Expand this section if you want to use this automation:

    WARNING: If you have never used Azure AD Powershell before, we recommend you go through the App Creation Scripts guide once to ensure that your environment is prepared correctly for this step.

    1. On Windows, run PowerShell as Administrator and navigate to the root of the cloned directory

    2. In PowerShell run:

      Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope Process -Force

    3. Run the script to create your Azure AD application and configure the code of the sample application accordingly.

    4. For interactive process - in PowerShell run:

      cd .\AppCreationScripts\
.\Configure.ps1 -TenantId "[Optional] - your tenant id" -Environment "[Optional] - Azure environment, defaults to 'Global'"

    5. In case the previous script fails with error about duplicate App Registration, you might want to run the next cleanup script prior to re-running Configure.ps1

      cd .\AppCreationScripts\
.\Cleanup.ps1

      Other ways of running the scripts are described in App Creation Scripts guide The scripts also provide a guide to automated application registration, configuration and removal which can help in your CI/CD scenarios.

Manual Steps

Note: skip this part if you've just used Automation steps

Follow the steps below for manually register and configure your apps

Expand this section if you want to use the steps:
  1. Sign in to the Azure portal.
  2. If your account is present in more than one Azure AD tenant, select your profile at the top right corner in the menu on top of the page, and then switch directory to change your portal session to the desired Azure AD tenant.
Register the client app (ClientApp-blazor-azuresql)
  1. Navigate to the Azure portal and select the Azure AD service.
  2. Select the App Registrations blade on the left, then select New registration.
  3. In the Register an application page that appears, enter your application's registration information:
    • In the Name section, enter a meaningful application name that will be displayed to users of the app, for example ClientApp-blazor-azuresql.
  4. Under Supported account types, select Accounts in this organizational directory only
  5. Click Register to create the application.
  6. In the app's registration screen, find and note the Application (client) ID. You use this value in your app's configuration file(s) later in your code.
  7. In the app's registration screen, select Authentication in the menu.
    • If you don't have a platform added, select Add a platform and select the Web option.
  8. In the Redirect URI section enter the following redirect URIs:
    • https://localhost:44348/
    • https://localhost:44348/signin-oidc
  9. In the Front-channel logout URL section, set it to https://localhost:44348/signout-oidc.
  10. Select ID tokens (used for implicit and hybrid flows) checkbox.
  11. Click Save to save your changes.
  12. In the app's registration screen, select the Certificates & secrets blade in the left to open the page where you can generate secrets and upload certificates.
  13. In the Client secrets section, select New client secret:
    • Optionally you can type a key description (for instance app secret),
    • Select recommended Expire duration.
    • The generated key value will be displayed when you select the Add button. Copy and save the generated value for use in later steps.
    • You'll need this key later in your code's configuration files. This key value will not be displayed again, and is not retrievable by any other means, so make sure to note it from the Azure portal before navigating to any other screen or blade.
  14. Open API Permissions blade and add 'user_impersonation' scope for 'Azure SQL Database' API:
    • Open Add a permission
    • Switch to APIs my organization uses
    • Search for Azure SQL Database
    • Click on Delegated permissions
    • Check user_impersonation
    • Click Add permissions
Configure the client app (ClientApp-blazor-azuresql) to use your app registration

Open the project in your IDE (like Visual Studio or Visual Studio Code) to configure the code.

In the steps below, "ClientID" is the same as "Application ID" or "AppId".

  1. Open the Client\appsettings.json file.
    1. Find the key Domain and replace the existing value with your Azure AD tenant name.
    2. Find the key TenantId and replace the existing value with your Azure AD tenant ID.
    3. Find the key ClientId and replace the existing value with the application ID (clientId) of ClientApp-blazor-azuresql app copied from the Azure portal.
    4. Find the key ClientSecret and replace the existing value with the key you saved during the creation of ClientApp-blazor-azuresql copied from the Azure portal.

For more information, visit Register Application AAD

Step 4: Running the sample

To run the sample, run the following commands in the console:

    cd ./WebApp-Connect-To-Azure-Sql-Database/Client
    dotnet run

Troubleshooting

Expand for troubleshooting info

Use Stack Overflow to get support from the community. Ask your questions on Stack Overflow first and browse existing issues to see if someone has asked your question before. Make sure that your questions or comments are tagged with [azure-active-directory adal msal dotnet].

If you find a bug in the sample, please raise the issue on GitHub Issues.

To provide a recommendation, visit the following User Voice page.

Using the sample

Expand to see how to use the sample

Running from VS Code:

 dotnet run

If you're running from Visual Studio, press F5 or Ctrl+F5 (for no debug run)

On the main page you will be offered to Log In or to go to a "Fetch data" page If you choose to go to "Fetch data" page without logging-in, you will be asked to login with a standard UI. When the application will be logged in, it will try to connect to Azure SQL Database with an Access Token it acquired for the currently logged-in user. Successful connection will be indicated when the page will state that the user is logged-in into the database and a table with mock forecast data is displayed.

fetch_data_page

The page displays a message with user and host names that are values of @user and @host on SQL Database.

Did the sample not work for you as expected? Did you encounter issues trying this sample? Then please reach out to us using the GitHub Issues page.

Consider taking a moment to share your experience with us.

About the code

Expand the section

The main purpose of this sample is to show how to propagate AAD user to SQL server. The scenario is as follows:

  1. Get Access Token through interactive log-in process and cache it. To enable caching we have to add the 2 last lines to AAD configuration inside Program.cs:
  builder.Services.AddAuthentication(OpenIdConnectDefaults.AuthenticationScheme)
              .AddMicrosoftIdentityWebApp(builder.Configuration.GetSection("AzureAd"))
              .EnableTokenAcquisitionToCallDownstreamApi() 
              .AddInMemoryTokenCaches();
  1. Every time, the new SQL connection is created, acquire the cached token and add it to the connection object. If the cached token is unavailable, the MsalUiRequiredException will be thrown and interactive Authorization process will be kicked-off. Here is relevant code snippet from UserAADServices.cs:
  public async Task<string> GetAccessToken(AuthenticationState authState)
      {
          string accessToken = string.Empty;

          //https://database.windows.net/.default
          var scopes = new string[] { _azureSettings["Scopes"] };

          try
          {
              var accountIdentifier = GetAccountIdentifier(authState);

              IAccount account = await _app.GetAccountAsync(accountIdentifier);

              AuthenticationResult authResult = await _app.AcquireTokenSilent(scopes, account).ExecuteAsync();
              accessToken = authResult.AccessToken;
          }
          catch (MsalUiRequiredException)
          {
              _consentHandler.ChallengeUser(scopes);
              return accessToken;
          }

          return accessToken;
      }

Notice that the code is using a special default scope to be able to work with SQL Server - https://database.windows.net/.default

Next Steps

Learn how to:

Contributing

Additional information about AAD authentication can be found here

If you'd like to contribute to this sample, see CONTRIBUTING.MD.

This project has adopted the Microsoft Open Source Code of Conduct. For more information, see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

Learn More

For more information, visit the following links:

To learn more about the application registration, visit: