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