Connect Azure SQL Database from App Service with System Assigned Identity¶

Step 1: Grant app services access to the SQL server as Sql Server Contributor¶

Follow these steps for all app services (Content Api App Service, Identity Service App Service, Beacon App Service)

Find your all app-services in Azure Portal
In the left hand list of settings, click on “Identity”
Select System Assigned tab and click Azure role assignment button.
Add Role assigment preview
Scope : SQL
Resource : Your Database
Role Sql Server Contributer

Step 2: Grant yourself access to the SQL server¶

Using the Azure Portal, go to the settings page of your SQL server. Set your own account as the Active Directory admin and create a firewall rule allowing the client IP of your developer machine to access the SQL server.

To set admin and add firewall rule:

Search for your SQL server in “Search Resources dialog box” in Azure Portal.
In the left hand list of settings, click on “Admin Directory admin”
Click on “Set admin”, search for and select your Active Directory account, and then click “Save”.
Now, in the left hand list of settings, click on “Firewalls and virtual networks”
Click “Add client IP” and then click “Save” to add a firewall rule (or manually create for your client machine).

Step 3: Web Config and Appsetting.json file changes¶

Step 3.1: Pointr Cloud Content Api Web.config Changes¶

The connection string has been updated. Note that the value of the “Authentication” parameter matches the name of the SQL Authentication Provider in the SqlAuthenticationProviders configuration section.

<add name="DefaultConnection" connectionString="server=[Your SQL Server Name].database.windows.net;database=[Your SQL DB name];UID=AnyString;Authentication=Active Directory Interactive" providerName="System.Data.SqlClient" />

Also you should add below lines to web.config

A new SqlAuthenticationProviders configuration section is added, which specifies “SqlAppAuthenticationProvider” as a SQL authentication provider. The “SqlAppAuthenticationProvider” is a new class, available only in the .NET 4.7.2 targeted package of the AppAuthentication library. It uses the AzureServiceTokenProvider to acquire an access token for SQL.

<configuration>
    <configSections>
        <section name="SqlAuthenticationProviders" type="System.Data.SqlClient.SqlAuthenticationProviderConfigurationSection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
        <sectionGroup name="dotNetOpenAuth" type="DotNetOpenAuth.Configuration.DotNetOpenAuthSection, DotNetOpenAuth.Core">
            <section name="messaging" type="DotNetOpenAuth.Configuration.MessagingElement, DotNetOpenAuth.Core" requirePermission="false" allowLocation="true" />
            <section name="reporting" type="DotNetOpenAuth.Configuration.ReportingElement, DotNetOpenAuth.Core" requirePermission="false" allowLocation="true" />
            <section name="oauth" type="DotNetOpenAuth.Configuration.OAuthElement, DotNetOpenAuth.OAuth" requirePermission="false" allowLocation="true" />
            <section name="openid" type="DotNetOpenAuth.Configuration.OpenIdElement, DotNetOpenAuth.OpenId" requirePermission="false" allowLocation="true" />
        </sectionGroup>
    </configSections>
    <connectionStrings>
<add name="DefaultConnection" connectionString="server=[Your SQL Server Name].database.windows.net;database=[Your SQL DB name];UID=AnyString;Authentication=Active Directory Interactive" providerName="System.Data.SqlClient" />
    </connectionStrings>
    <SqlAuthenticationProviders>
        <providers>
            <add name="Active Directory Interactive" type="Microsoft.Azure.Services.AppAuthentication.SqlAppAuthenticationProvider, Microsoft.Azure.Services.AppAuthentication" />
        </providers>
    </SqlAuthenticationProviders>
    <appSettings>

Step 3.2: Beacon search service Appsetting.json file changes¶

"ConnectionStrings": {
  "DefaultConnection": "Data Source=[Your SQL Server Name].database.windows.net;Initial Catalog=[Your SQL DB name];Authentication=Active Directory Managed Identity; Encrypt=True;MultipleActiveResultSets=True;TrustServerCertificate=True"
}

Step 3.3: Identity search Appsetting.json file changes¶

"ConnectionStrings": {
  "DefaultConnection": "Data Source=[Your SQL Server Name].database.windows.net;Initial Catalog=[Your SQL DB name];Authentication=Active Directory Managed Identity; Encrypt=True;MultipleActiveResultSets=True;TrustServerCertificate=True"
}

Step 4: Grant App Services Managed Identity access to the SQL server¶

Using SQL Server Management Studio, authenticate using your account to connect to your Azure SQL database. Then edit the below query, replacing the App Service name with the name you used in Step #1, and execute it. This will grant your App Service permission to read and write to your database.

Pointr Cloud Content Api Service¶

CREATE USER [<App Service Name>] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [<App Service Name>] -- gives permission to read to database
ALTER ROLE db_datawriter ADD MEMBER [<App Service Name>] -- gives permission to write to database
ALTER ROLE db_owner ADD MEMBER [<App Service Name>] -- gives permission to migrate to database

Beacon Search Service¶

CREATE USER [<Beacon Search App Service Name>] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [<Beacon Search App Service Name>] -- gives permission to read to database
ALTER ROLE db_datawriter ADD MEMBER [<Beacon Search App Service Name>] -- gives permission to write to database
ALTER ROLE db_owner ADD MEMBER [<Beacon Search App Service Name>] -- gives permission to migrate to database

Identity Service¶

CREATE USER [<Identity App Service Name>] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [<Identity App Service Name>] -- gives permission to read to database
ALTER ROLE db_datawriter ADD MEMBER [<Identity App Service Name>] -- gives permission to write to database
ALTER ROLE db_owner ADD MEMBER [<Identity App Service Name>] -- gives permission to migrate to database

Last update: January 2, 2024