How do I connect to Azure SQL database using an App Service service principal?

Question

I'm having trouble getting my service to connect to the database using Active Directory Integrated authentication.

I have an app service running on linux and a SQL Server instance/database running in the same region. The app service has a system-assigned managed identity with a SQL DB Contributor role for the database. I have a virtual network and have configured access to the SQL Server instance via the vnet, and have the app service integrated with the vnet.

The sql instance provides an example connection string like so:

Server=tcp:foo.database.windows.net,1433;Initial Catalog=foo-db;Persist Security Info=False;User ID={your_username};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Integrated";

I'm not sure what I should be using for the User ID. At first I just tried omitting it, assuming integrated authentication would be able to infer the service principal, which it turned out it couldn't (perhaps it's because I'm running on a linux VM, or perhaps the user always needs to be supplied).

I tried again using the object id for the system-assigned managed identity for the app service. That also didn't work:

Failed to authenticate the user 190c6dd1-6b34-4181-b6b2-f9da12d7d8fc in Active Directory (Authentication=ActiveDirectoryIntegrated). Error code 0xunknown_user_type Unsupported User Type 'Unknown'

So, how do I supply a user for the system assigned identity for my app service in the connection string?

Can you be specific in which driver & version you're using this connection string with? — esqew
– esqew, Commented Apr 8, 2024 at 19:03

esqew · Accepted Answer · 2024-04-08 20:08:43Z

2

I would expect the guidance from Connect to Azure SQL with Microsoft Entra authentication and SqlClient§Using managed identity authentication to apply here, specifically using Authentication=Active Directory Managed Identity and dropping the username altogether:

Server=tcp:foo.database.windows.net,1433;Authentication=Active Directory Managed Identity;Initial Catalog=foo-db;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;

Your premise around your authorization configuration is also flawed, considering the role you've assigned your service principal (SQL DB Contributor) doesn't do what you think it does; from Microsoft's docs:

SQL DB Contributor Lets you manage SQL databases, but not access to them. Also, you can't manage their security-related policies or their parent SQL servers.

(Further reading)

Instead, add the service principal to the RBAC in the database itself by selecting the target database and then running the following statement as a user with ALTER ANY USER permissions:

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;

edited Apr 8, 2024 at 20:08

answered Apr 8, 2024 at 19:02

esqew

45k28 gold badges139 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

w.brian Over a year ago

This seems to have gotten me further along in the process, however I now get a new error: Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'.

esqew Over a year ago

@w.brian You've got a flaw in your understanding of how the SQL DB Contributor role works & what accesses it grants - check my update.

Collectives™ on Stack Overflow

How do I connect to Azure SQL database using an App Service service principal?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related