I'm trying to access a serverless azure SQL database from an Azure Web Application running a docker container under Linux. The container is a .Net Core 3.1 web application using the latest EF Core. The web app has been configured to use a system assigned identity.
For the SQL user, I use the following PS script to get an SID, where the object ID is the system assigned identity object ID:
$principal = Get-AzADServicePrincipal -ObjectId $objectId
foreach ($byte in $principal.ApplicationId.ToByteArray())
{
$byteGuid += [System.String]::Format("{0:X2}", $byte)
}
$sid = "0x" + $byteGuid
Then, I created the user with db_owner role to the database using the SID like this...
'CREATE USER [AppUser] WITH DEFAULT_SCHEMA=[dbo], SID=' + '$(AppSiD)' + ' , TYPE = E'
...similar to the process described here:
https://blog.bredvid.no/handling-azure-managed-identity-access-to-azure-sql-in-an-azure-devops-pipeline-1e74e1beb10b
I've also updated the EF instance using the following method:
https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi#modify-aspnet-core
When the application tried to access the DB, the following exception is thrown:
Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'.
EDIT I don't believe the SQL user to be causing the issue, as the error occurs whether the user exists or not.
I'd prefer to fix the error above but if there is is an alternative way to connect to the DB without using a SQL user I would appreciate the advice.
Thanks
EDIT
Elaborated on the SQL user process as the link is down at the time of posting
First you need to make your SQL database AAD-enabled. Then you can create a user for your Managed Identity like this
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
And then of course assign permissions to that user as needed.
User contributions licensed under CC BY-SA 3.0