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.
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.
