Skip to main content

.Net Framework 4.8: Azure SQL Database connection from App Service using a managed identity


Azure SQL Database connection from App Service using a managed identity


Azure App Service(Web App) provides a highly scalable, self-patching web hosting accommodation in azure.  It offers a managed identity for your app, which is a turn-key solution for securing access to the Azure SQL database and other azure services.  Managed identities in-app provides a mechanism to your app more secure by eliminating secrets from your app, such as credentials in the connection strings.  

 Here we'll integrate managed identity to the sample web app and also with zero lines of code. we'll utilize full configuration capability to make a connection to the Azure SQL database.

Prerequisites:

Following resource are required to run/complete this demo

  • Azure subscription
    • Create an Azure web app
    • Create a key vault resource
  • Visual studio 2019 ready to use on your machine
  • .Net Framework 4.8 installed

You will learn followings:

  • Enable managed identities
  • Grant SQL Database access to the managed identity
  • Connect to SQL Database from Visual Studio using Azure AD authentication

Azure Database Setup

Let's create a database for you according to give screenshot

azure-sql-database-create

Setup Azure Active Directory

Open you newly create azure SQL server and add your email id(that you have used to login to azure portal)

aad-admin-setup

Open Azure Database(for example: emp) and click on Query Editor(Preview) on and log-in with the option “Active Directory authentication” and run following command

login-as-aad


Grant Access to your Web App to Azure SQL Database

This step is not required for the local running the app in visual studio

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

[<identity-name>] : Your web app Identity that will be required only when your code hosted over underlying web app to make the connection between an azure web app and your emp database.

execute-query





Set up Local Develop Environment(Visual Studio)

You must be login into the visual studio with same principle/user name that you have used to access https://portal.azure.com/

  • To enable development and debugging in Visual Studio, add your Azure AD user in Visual Studio by selecting File > Account Settings from the menu, and click Add an account.
  • Add  latest Nuget Package “Microsoft.Azure.Services.AppAuthentication” to your underlying project
  • Open your Web.config,  file and the following configurations

  <configSections>
    <section name="SqlAuthenticationProviders" type="System.Data.SqlClient.SqlAuthenticationProviderConfigurationSection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  </configSections>
  <SqlAuthenticationProviders>
    <providers>
      <add name="Active Directory Interactive" type="Microsoft.Azure.Services.AppAuthentication.SqlAppAuthenticationProvider, Microsoft.Azure.Services.AppAuthentication" />
    </providers>
  </SqlAuthenticationProviders>
  <connectionStrings>
    <add name="BasicDatabaseConnectionString" connectionString="server=msidemoserver.database.windows.net;database=emp;UID=ManagedIdentity;Authentication=Active Directory Interactive" />
  </connectionStrings>

Note: update the connection string  with your Azure SQL server name and database

Sample web.config file: 
web-config-file-changes

You're now ready to develop and debug your app with the SQL Database as the back end, using Azure AD authentication.


Let's run from your local machine and if your running given sample code(Download Sample Code from Git Hub) so you will see the following screen:

successfull-db-connection-running app.JPG


Please provide your comment and feedback, that'll be highly appreciated.
Labels:

Comments

Post a Comment

Popular posts from this blog

Azure key vault with .net framework 4.8

Azure Key Vault  With .Net Framework 4.8 I was asked to migrate asp.net MVC 5 web application to Azure and I were looking for the key vault integrations and access all the secrete out from there. Azure Key Vault Config Builder Configuration builders for ASP.NET  are new in .NET Framework >=4.7.1 and .NET Core >=2.0 and allow for pulling settings from one or many sources. Config builders support a number of different sources like user secrets, environment variables and Azure Key Vault and also you can create your own config builder, to pull in configuration from your own configuration management system. Here I am going to demo Key Vault integrations with Asp.net MVC(download .net framework 4.8). You will find that it's magical, without code, changes how your app can read secretes from the key vault. Just you have to do the few configurations in your web config file. Prerequisite: Following resource are required to run/complete this demo · ...
Post a Comment
Read more

How to Make a Custom URL Shortener Using C# and .Net Core 3.1

C# and .Net Core 3.1:  Make a Custom URL Shortener Since a Random URL needs to be random and the intent is to generate short URLs that do not span more than 7 - 15 characters, the real thing is to make these short URLs random in real life too and not just a string that is used in the URLs Here is a simple clean approach to develop custom solutions Prerequisite:  Following are used in the demo.  VS CODE/VISUAL STUDIO 2019 or any Create one .Net Core Console Applications Install-Package Microsoft.AspNetCore -Version 2.2.0 Add a class file named ShortLink.cs and put this code: here we are creating two extension methods. public   static   class   ShortLink {      public   static   string   GetUrlChunk ( this   long   key ) =>            WebEncoders . Base64UrlEncode ( BitConverter . GetBytes ( key ));      public   static   long   GetK...
Post a Comment
Read more

Azure Logic Apps Send Email Using Send Grid Step by Step Example

Azure Logic Apps Send Email Using Send Grid Step by Step     Step 1- Create Send Grid Account Create a SendGrid Account  https://sendgrid.com/ Login and Generate Sendgrid Key and keep it safe that will be used further to send emails You can use Free service. it's enough for the demo purpose Step 2- Logic App Design Login to  https://portal.azure.com Go to Resources and Create Logic App Named "EmailDemo" Go To Newly Created Rosoure Named "EmailDemo" and Select a Trigger "Recurrence", You can choose according to your needs like HTTP, etc. Note* Without trigger you can not insert new steps or Actions Click on Change Connection and add Send Grid Key  Click on Create and Save Button on the Top. As we have recurrence so it will trigger according to our setup(every 3 months) so just for the test click on "RUN" button  Finally, you should get an email like below one:
Post a Comment
Read more