Query Azure SQL Database from local Azure Function using Managed Identities

Question

I want to query an Azure SQL Database from an Azure Function executing on my machine in debug using Managed Identities (i.e. the identity of my user connected to Visual Studio instead of providing UserId and Password in my connection string).

I followed this tutorial on Microsoft documentation so my Azure SQL Server has an AD user as admin which allowed me to give rights (db_datareader) to an Azure AD group I created with my Azure Function Identity and my user in it (and also my Function App deployed in Azure).

If I deploy and run in Azure my Azure Function, it is able to query my database and everything is working fine. But when I run my Azure Function locally, I have the following error :

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The code of my function is the following:

    public async Task<IActionResult> Run(
        [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = "test")] HttpRequest req,
        ILogger log)
    {
        log.LogInformation("C# HTTP trigger function processed a request.");


        using (var connection = new SqlConnection(Environment.GetEnvironmentVariable("sqlConnectionString")))
        {
            connection.AccessToken = await (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net");
            log.LogInformation($"Access token : {connection.AccessToken}");
            try
            {
                await connection.OpenAsync();
                var rows = await connection.QueryAsync<Test>("select top 10 * from TestTable");
                return new OkObjectResult(rows);
            }
            catch (Exception e)
            {
                throw e;
            }

        }
    }

The code retrieves a token correctly, the error occurs on line await connection.OpenAsync().

If I open the database in Azure Data Studio with the same user than the one connected to Visual Studio (which is member of the AD group with the rights on the database), I can connect and query the database without any issue.

Is it a known issue or am I missing something here ?

Is the environment variable sqlConnectionString set when running locally? — Turbo
Yes it is set in my local.settings.json file : "sqlConnectionString": "Server=tcp:myservername.database.windows.net,1433;Initial Catalog=mydatabasename;", — TechWatching
I see. The only time I have seen this error earlier was when I specified the server name, but missed the database name. (because AAD logins have access at db level, not server level). But that error is pretty generic, so not sure what could be the cause. — Turbo
It could be linked to this issue : github.com/Azure/azure-sdk-for-net/issues/7022 — TechWatching
My tokens are similar, just with different identities. I noted however that the token I retrieve is not of the correct user, Visual Studio seems not to take the correct one... — TechWatching

rickvdbosch rickvdbosch · Accepted Answer · 2019-08-19T20:11:03

After trying your specific scenario, I tested quite a few ways to try and get it to work locally. This didn't work, giving the same error message you're getting.

I discussed it with some people, when a possible solution came up. I tested it: it works!

The main issue in my case was that my subscription (and my user) is a Microsoft account (Outlook). Because of this, you need to specify the tenantId in the GetAccessTokenAsync() call.

Apparently, for managed identities you do not have to specify the tenantId. With a user, it's a good idea to explicitly specify it. In case of a personal MS account, specifying it is mandatory.

My code (sort of):

var tokenProvider = new AzureServiceTokenProvider();

using (var connection = new SqlConnection(CONNECTIONSTRING))
using (var command = new SqlCommand(QUERY, connection))
{
    connection.AccessToken = await tokenProvider.GetAccessTokenAsync("https://database.windows.net/", "<YOUR_TENANT_ID>");

    await connection.OpenAsync();
    var result = (await command.ExecuteScalarAsync()).ToString();

    return new OkObjectResult(result);
}

This solution has been tested and works both when specifying the tenantId (or Directory ID, the tenant's GUID) and the 'onmicrosoft'-name (xxx.onmicrosoft.com).

Query Azure SQL Database from local Azure Function using Managed Identities

2 Answers