I have a C# dotnet core 3.1 Azure Function App (named func-utrngen-dev-001) that I'd like to configure to authenticate to a Sql Azure database via managed identity.
Steps Taken:
Created the AAD admin for sql server account
Used that account to open a connection to the sql server
Ran the following to created a "contained user" of the same name as the azure function app
CREATE USER [func-utrngen-dev-001] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [func-utrngen-dev-001]
ALTER ROLE db_datawriter ADD MEMBER [func-utrngen-dev-001]
Ensured that "Allow Azure services and resources to access this server" is set to "Yes" from the Azure Portal under the Sql Server\Security\Firewalls and virtual networks blas
Ensured that System assigned\Status is set to "On" on the function app's Identity blade
When the function app attempts to authenticate, I get the following error:
Login failed for user '<token-identified principal>'
So I installed Microsoft's "MSI Validator" tool and ran through the steps described here. I ran the following from the Powershell window of a Kudu session:
./msi-validator.exe test-connection -r sql -e "Data Source=sql-utrngen-dev-001.database.windows.net;Initial Catalog=utrngen;"
It successfully obtained a token but then failed with the same error as my function app:
Unable to connect to SQL. Exception : Login failed for user '<token-identified principal>'
- I ran select * FROM sys.fn_get_audit_file(path to tsql audit blob) but the results only showed activity from my account logged into SSMS
What should I try next?
