Password free connections to SQL databases

In the old days, we used SQL Server Logins to access information. You usually sent the name and password in clear text as part of a connection string. That was 15 years ago.

A much cleaner and secure way is to use Azure Managed Identity. If your service supports it, use it. The following database services supports Managed Identity:

• Azure SQL Database
• Azure Database for MySQL
• Azure Database for PostgeSQL

Example configuration using an Azure Function

Configure your Azure Function

Update the function to use system assigned managed identity. Simply navigate to Identity under settings and enable it.

This will create an enterprise application in Entra with the given Object ID.

Setting it up using Bicep

Here is a Function App fully configured:

resource azureFunctionApp 'Microsoft.Web/sites@2020-12-01' = { 
  name: funcName 
  location: location 
  kind: 'functionapp,linux' 
  identity: { 
    type: 'SystemAssigned' 
  } 

  properties: { 
    serverFarmId: funcServicePlan.id 
    siteConfig: { 
      linuxFxVersion: nodeVersion 
    } 
  } 
  tags: resourceGroup().tags 
} 

Create a user in your SQL Server

Open a query window into you SQL Server. Add the following query text:

CREATE USER [The-Name-Of-Your-Function] FROM EXTERNAL PROVIDER; 
ALTER ROLE db_datareader ADD MEMBER [The-Name-Of-Your-Function]; 
ALTER ROLE db_datawriter ADD MEMBER [The-Name-Of-Your-Function]; 
ALTER ROLE db_ddladmin ADD MEMBER [The-Name-Of-Your-Function]; 
GO 

The access rights might vary for you scenario. If you get and error about duplicate identities or similar see below.

Verify user creation

You can verify that the user has been added by running this script.

select name as username, 
       create_date, 
       modify_date, 
       type_desc as type, 
       authentication_type_desc as authentication_type 
from sys.database_principals 
where type not in ('A', 'G', 'R') 
      and sid is not null 
      and name != 'guest' 
order by username; 

The new user should show up in the resulting list.

Use it in your code

The easiest way to find what you need is to follow the documentation from Microsoft.

Here is a TypeScript example:

const { DefaultAzureCredential } = require("@azure/identity"); 
const credential = new DefaultAzureCredential(); // system-assigned identity 

export const connect = async (): Promise
<void> => { 
  const baseConfig = { 
    database: process.env.MSSQL_DATABASE, 
    server: process.env.MSSQL_HOST, 
    options: { 
      encrypt: process.env.MSSQL_ENCRYPT === "true", 
      trustServerCertificate: 
        process.env.MSSQL_TRUST_SERVER_CERTIFICATE === "true", 
    }, 
  }; 

  let config = {}; 

  if (process.env.AZURE_FUNCTIONS_ENVIRONMENT === "Development") { 
    // Running locally 
    config = { 
      ...baseConfig, 
      user: process.env.MSSQL_USER, 
      password: process.env.MSSQL_PASSWORD, 
    }; 
  } else { 
    // Running in Azure 
    const accessToken = await credential.getToken( 
      "https://database.windows.net/.default" 
    ); 
    config = { 
      ...baseConfig, 
      authentication: { 
        type: "azure-active-directory-access-token", 
        options: { 
          token: accessToken.token, 
        }, 
      }, 
    }; 
  } 

  return await sql.connect(config); 
}; 

Duplicate identities error

There is a problem when adding the identity as a user in SQL Server. The identity lookup in Entra is done using the name (not the object ID or client ID) and if you have multiple identities with the same name, you get an error.

This is usually due to you having configured the Azure Function to use the Authorization feature. (More information here). When you do this, out of the box, an Enterprise Application with the same name as the function is created. This is not the same Application as the managed identity created above.

The way to solve this is to:

• Delete the automatically generated Enterprise Application created when you added Authorization. Remember to delete both the Enterprise application and the application registration.
• Add the identity as a user in SQL Server, as described above.
• Create a new application registration to be used in Authorization configuration. Tip: Use the same name as the function but put ‘app-’ as a prefix.
• Configure the Authorization option in the Azure Function and make sure you use your new application identity.