Credential-less SQL Authentication for Azure SQL
Azure SQL lets you authenticate with Managed Identities. No username or password in your app.
Microsoft Entra authentication must be configured in two places: on the Azure SQL resource itself and inside the database. I only figured that out after spending quite a bit of time troubleshooting.
My Web App’s managed identity had Contributor on the SQL server, but connections still failed with “Login failed for user” until I ran CREATE USER inside the database. RBAC on the Azure resource is not the same as a database login.
Here is the full setup:
- Configure an Entra administrator on the SQL server (Terraform)
- Create database users and assign roles (SQL)
- Optionally assign Azure RBAC roles for Portal management
I’m using Terraform with the avm-res-sql-server module because it handles Entra admin configuration cleanly.
For the SQL server administrator I use an Entra security group instead of a personal user account.
1data "azurerm_client_config" "current" {}
2
3data "azuread_group" "sql_admins" {
4 display_name = "my-sql-admins-group"
5}
This way I can add people to the group without changing the Terraform configuration.
This group can then be used in the AVM SQL Server module:
1module "avm_res_sql_server" {
2 source = "Azure/avm-res-sql-server/azurerm"
3 version = "0.1.9"
4
5 resource_group_name = "rg-sql-server"
6 location = "westeurope"
7 server_version = "12.0"
8 name = "my-sql-server"
9
10 azuread_administrator = {
11 login_username = data.azuread_group.sql_admins.display_name
12 object_id = data.azuread_group.sql_admins.object_id
13 tenant_id = data.azurerm_client_config.current.tenant_id
14 azuread_authentication_only = true
15 }
16}
The login_username is the display name of the Entra administrator. In this case, that is the name of the Entra security group.
The object_id is the unique object ID of the Entra group or user. Azure SQL uses this value to identify which Entra identity becomes the administrator.
Because azuread_authentication_only is set to true, SQL authentication is disabled. This means users cannot log in with a SQL username and password. Access to the SQL Server must go through Microsoft Entra ID.
After the SQL Server and database have been created with Terraform, there is still one important step left: database access.
Azure RBAC and Azure SQL database permissions are not the same thing.
Azure RBAC controls access to the Azure resource itself. For example, it controls whether someone can see the SQL Server in the Azure Portal, manage firewall rules, change configuration, or view resource metadata.
Azure SQL database permissions control whether someone can actually connect to the database and read or write data.
This means that giving someone an Azure RBAC role such as Reader, Contributor, or even Owner on the Azure resource does not automatically give that identity access inside the database. The identity still needs to be created as a user inside the database.
The managed identity must be created as a SQL user in the target database by using the CREATE USER statement. After that, database roles can be assigned.
1CREATE USER [my-app-managed-identity] FROM EXTERNAL PROVIDER;
2
3ALTER ROLE db_datareader ADD MEMBER [my-app-managed-identity];
4ALTER ROLE db_datawriter ADD MEMBER [my-app-managed-identity];
In this example, [my-app-managed-identity] is the name of the managed identity that should be allowed to access the database. For a system-assigned managed identity, this is usually the name of the Azure resource, such as the App Service or Function App name. For a user-assigned managed identity, this is usually the name of the managed identity resource.
Someone with permission to create users must run this SQL, usually a member of the Entra security group configured as the SQL server administrator.
For example, if my-sql-admins-group is the server administrator, a group member can connect with a SQL client using their own Entra identity and run the statements above. FROM EXTERNAL PROVIDER works well for manual, interactive connections.
If you need Portal access to manage firewall rules or server settings, assign an Azure RBAC role such as SQL Server Contributor or SQL DB Contributor separately. That controls the Azure resource, not data access inside the database.
Granting database access with Terraform
Creating the server and database in Terraform is only part of the setup. Application identities and users still need to exist inside the database.
There is no native Terraform resource for Azure SQL database users. I use data sources to look up Entra identities, then a terraform_data resource with a local-exec provisioner to run the SQL. terraform_data re-runs the provisioner when its triggers change; nothing else in Terraform models “execute this SQL.”
local-exec is usually a last resort in Terraform, but for this use case it works well.
In this example, I grant database access to:
- The managed identity of an Azure Linux Web App
- A set of Microsoft Entra users
First, I look up the Web App and its managed identity:
1data "azurerm_linux_web_app" "webapp" {
2 name = "app-example-${var.environment}"
3 resource_group_name = data.azurerm_resource_group.rg.name
4}
5
6data "azuread_service_principal" "webapp_managed_identity" {
7 object_id = data.azurerm_linux_web_app.webapp.identity[0].principal_id
8}
The Web App has a system-assigned managed identity. Azure exposes that identity as a service principal in Microsoft Entra ID. By looking up the service principal, I can retrieve values such as the display name and client ID.
Then I define the Microsoft Entra users that should get access to the database:
1data "azuread_user" "database_users" {
2 for_each = toset([
3 "user.one@example.com",
4 "user.two@example.com",
5 "user.three@example.com"
6 ])
7
8 user_principal_name = each.value
9}
Next, I define which databases and roles should be assigned.
1locals {
2 webapp_sql_user_name = data.azuread_service_principal.webapp_managed_identity.display_name
3 webapp_sql_client_id = data.azuread_service_principal.webapp_managed_identity.client_id
4
5 webapp_sql_databases = {
6 "app-db-${var.environment}" = "db_owner"
7 "data-db-${var.environment}" = "db_owner"
8 }
9
10 database_user_sql_roles = {
11 "app-db-${var.environment}" = ["db_datareader", "db_datawriter"]
12 "data-db-${var.environment}" = ["db_datareader", "db_datawriter"]
13 }
14
15 database_user_sql_permissions = {
16 "app-db-${var.environment}" = ["VIEW DEFINITION"]
17 "data-db-${var.environment}" = ["VIEW DEFINITION"]
18 }
19}
In this example, the Web App managed identity receives db_owner. This can be useful when the application is responsible for running database migrations. If the application only needs runtime access, a more limited role such as db_datareader and db_datawriter is usually better.
The individual users receive db_datareader, db_datawriter, and VIEW DEFINITION.
To make this dynamic, I flatten the users, databases, and roles into maps that can be used with for_each:
1locals {
2 database_user_sql_access = merge([
3 for upn, user in data.azuread_user.database_users : merge([
4 for database_name, roles in local.database_user_sql_roles : {
5 for role in roles :
6 "${upn}|${database_name}|${role}" => {
7 principal_name = upn
8 object_id = user.object_id
9 database_name = database_name
10 role = role
11 }
12 }
13 ]...)
14 ]...)
15
16 database_user_permission_access = merge([
17 for upn, user in data.azuread_user.database_users : merge([
18 for database_name, permissions in local.database_user_sql_permissions : {
19 for permission in permissions :
20 "${upn}|${database_name}|${permission}" => {
21 principal_name = upn
22 object_id = user.object_id
23 database_name = database_name
24 permission = permission
25 }
26 }
27 ]...)
28 ]...)
29}
The next step is to execute the SQL commands via terraform_data and local-exec. The runner needs sqlcmd installed and an Entra identity allowed to administer the database.
I run Terraform from Azure DevOps. The pipeline service connection authenticates as a service principal that is a member of the SQL admins Entra group, so sqlcmd -G works without -U and -P. The agent also needs network access to the server, either a firewall rule for its IP or a private endpoint in the same network.
Note: This example assumes Entra-only authentication. The
sqlcmdcommands use-Gfor Microsoft Entra auth, not SQL auth with-Uand-P.1
1resource "terraform_data" "webapp_database_access" {
2 for_each = local.webapp_sql_databases
3
4 triggers_replace = {
5 server_name = module.avm_res_sql_server.resource_name
6 database_name = each.key
7 principal = local.webapp_sql_user_name
8 client_id = local.webapp_sql_client_id
9 role = each.value
10 }
11
12 provisioner "local-exec" {
13 interpreter = ["/bin/bash", "-c"]
14
15 command = <<-EOT
16 sqlcmd \
17 -S "tcp:${module.avm_res_sql_server.resource_name}.${var.sql_server_domain_suffix},1433" \
18 -d "${each.key}" \
19 -G \
20 -C \
21 -b \
22 -Q "SET NOCOUNT ON;
23 DECLARE @principal_name sysname = N'${local.webapp_sql_user_name}';
24 DECLARE @client_id uniqueidentifier = '${local.webapp_sql_client_id}';
25 DECLARE @expected_sid varbinary(16) = CONVERT(varbinary(16), @client_id);
26 DECLARE @sid_literal nvarchar(34) = CONVERT(varchar(34), @expected_sid, 1);
27 DECLARE @principal_quoted nvarchar(258) = QUOTENAME(@principal_name);
28 DECLARE @role_name sysname = N'${each.value}';
29 DECLARE @role_quoted nvarchar(258) = QUOTENAME(@role_name);
30
31 IF DATABASE_PRINCIPAL_ID(@principal_name) IS NULL
32 BEGIN
33 EXEC(N'CREATE USER ' + @principal_quoted + N' WITH SID = ' + @sid_literal + N', TYPE = E;');
34 END
35 ELSE IF EXISTS (
36 SELECT 1
37 FROM sys.database_principals
38 WHERE name = @principal_name
39 AND sid <> @expected_sid
40 )
41 BEGIN
42 EXEC(N'DROP USER ' + @principal_quoted + N';');
43 EXEC(N'CREATE USER ' + @principal_quoted + N' WITH SID = ' + @sid_literal + N', TYPE = E;');
44 END;
45
46 IF NOT EXISTS (
47 SELECT 1
48 FROM sys.database_role_members drm
49 INNER JOIN sys.database_principals role_principal
50 ON role_principal.principal_id = drm.role_principal_id
51 INNER JOIN sys.database_principals member_principal
52 ON member_principal.principal_id = drm.member_principal_id
53 WHERE role_principal.name = @role_name
54 AND member_principal.name = @principal_name
55 )
56 BEGIN
57 EXEC(N'ALTER ROLE ' + @role_quoted + N' ADD MEMBER ' + @principal_quoted + N';');
58 END;"
59 EOT
60 }
61
62 depends_on = [module.avm_res_sql_server]
63}
You can run the SQL script more than once without breaking anything: it creates the user if missing, recreates it when the SID changed, and only adds the role when membership is not already present. In Terraform, use CREATE USER ... WITH SID instead of FROM EXTERNAL PROVIDER because the provisioner runs non-interactively.
For managed identities, map the SID from the service principal’s client_id. For Entra users, use object_id instead. The rest of the script stays the same. A second terraform_data resource with for_each = local.database_user_sql_access handles user role assignments.
For permissions like VIEW DEFINITION, use the same pattern again but check sys.database_permissions and run GRANT instead of ALTER ROLE ... ADD MEMBER. Drive it from local.database_user_permission_access and set depends_on on the role-assignment resource so users exist first.
Gotchas
- The identity running Terraform must connect to the database as an Entra administrator or as a user with permission to create users and assign roles.
- The runner must have
sqlcmdinstalled and network access to the SQL server. - Use least privilege where possible. Do not assign
db_ownerto an application unless it really needs it, for example to run schema migrations.
-Genables Microsoft Entra authentication insqlcmd. With SQL authentication enabled, you would use-Uand-Pinstead. ↩︎