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

AWS FREE ASP.NET CORE (.NET 6.0) HOSTING WITH FREE SSL

  FREE ASP.NET CORE (.NET 6.0) Hosting on AWS (Amazon Web Services) Today I was able to host my asp.net 6.0  + ANGULAR 14 application  on AWS Free  Initial Setup of your AWS Account and your Computer Get ready with your asp.net core 3.1 /.net 6 application Install  "AWS toolkit for visual studio 2022" as  visual studio extensions :  it will be required to deploy smoothly from Visual Studio 2022 itself, your life will be easy. Let's finish the AWS account setup  Get signed up with: its free but it will be required a valid credit card or debit card, they will charge nothing for the free services for 1 year * https://portal.aws.amazon.com/billing/signup#/start/email AWS console  for services and offering http://console.aws.amazon.com/ Create a user in AWS Console:  IAM With the help of AWS Identity and Access Management (IAM), you can control who or what has access to the services and resources offered by AWS, centrally manage fine-grained...
Post a Comment
Read more