Azure App + User Assigned Identity + Azure SQL woes

[ashic]

I'm using node-mssql 6.3.1, which uses tedious 6.7.0, which in turn uses @azure/ms-rest-nodeauth 3.0.6 (i.e. it's not a v2 of ms-rest-nodeauth issue). Some details about my issue:

1. I have a node azure function with a user assigned identity (we'll call it uid1). The uid1 is given permissions to access some storage blob and db owner (started off with limited, but trying to get it working) on an Azure SQL database.

2. I've created a user in the SQL database for uid1 via the following:

   CREATE USER [uid1] FROM EXTERNAL PROVIDER;
   ALTER ROLE db_datareader ADD MEMBER [uid1];
   ALTER ROLE db_datawriter ADD MEMBER [uid1];
   ALTER ROLE db_ddladmin ADD MEMBER [uid1];

3. I've set AZURE_CLIENT_ID as an app setting to equal the client id of uid1 (not the object id).

Observations:

1. The azure function can access storage blobs fine - both if I generate an access token and pass it, or if I use a corresponding @azure/identity credential.
2. Access to SQL just does not work with the user assigned id.
3. If I switch to the principal that created the database (via environment variables), then the query works fine. For this, I use @azure/identity to get an access token and pass that to mssql, which passes it to tedious to create a connection. This works.
4. If I generate the access token from the same DefaultAzureIdentity for the database scope, but using the user assigned identity, then the token has the expected tenant and principal (the tenant and object id of the user assigned identity), but the connection to sql fails with ConnectionError: Login failed for user '<token-identified principal>'.
5. If I try to use azure-active-directory-msi-app-service with the clientId parameter, then it fails with ConnectionError: Security token could not be authenticated or authorized.

I've tried the following two configs:

function getConfig(accessToken) {
    var config = {
        "authentication": {
            "type": "azure-active-directory-access-token",
            "options": {
                "token": accessToken
            },
        },
        "server": `${process.env.SQL_SERVER_NAME}.database.windows.net`,
        "options": {
            "encrypt": true,
            "database": process.env.SQL_SERVER_DB_NAME,
        }
    };

    return config;
}

and

function getConfigAF() {
    var config = {
        "authentication": {
            "type": "azure-active-directory-msi-app-service",
            "options": {
                clientId: process.env.AZURE_CLIENT_ID
            },
        },
        "server": `${process.env.SQL_SERVER_NAME}.database.windows.net`,
        "options": {
            "encrypt": true,
            "database": process.env.SQL_SERVER_DB_NAME,
        }
    };

    return config;
}

Using the first, if the access token is from the principal that created the database, it works. But it doesn't if it's using the user assigned identity.

I'm thinking this is related to needing a SQL user / and permissions for the user assigned identity in the SQL database, but I've already done that. Any guidance on this will be appreciated.

[ashic]

Got some updates

1. If I make the user assigned id part of a group that has AD Admin rights to the database, then the access token based approach works. I'm guessing this is because the AD Admin doesn't require a sql user inside the database to function. However, I had previously created a sql user for the corresponding user assigned identity (as mentioned), but didn't get any joy. Any idea how a non-AD Admin user assigned id can function with an access token? Is there something else I need to do in the database?

2. This works now:

   function getConfig(accessToken) {
   var config = {
       "authentication": {
           "type": "azure-active-directory-access-token",
           "options": {
               "token": accessToken
           },
       },
       "server": `${process.env.SQL_SERVER_NAME}.database.windows.net`,
       "options": {
           "encrypt": true,
           "database": process.env.SQL_SERVER_DB_NAME,
       }
   };
   
   return config;
   }

where I get the access token with @azure/identity with:

const cred = new identity.DefaultAzureIdentity();  // AZURE_CLIENT_ID is set to the user assigned identity's client id
const token = await cred.getToken("https://database.windows.net/.default")
const conf = getConfig(token.token)

The following does not work for me:

function getConfigAF() {
    var config = {
        "authentication": {
            "type": "azure-active-directory-msi-app-service",
            "options": {
                clientId: process.env.AZURE_CLIENT_ID,
                resource: "https://database.windows.net/.default"
            },
        },
        "server": `${process.env.SQL_SERVER_NAME}.database.windows.net`,
        "options": {
            "encrypt": true,
            "database": process.env.SQL_SERVER_DB_NAME,
        }
    };

    return config;
}

With this, I get Security token could not be authenticated or authorized.

Is there something I'm missing?

[MichaelSun90]

Hi @ashic, I found an older issue that could be related to your issue: #1146. The root cause for this is on ms-rest-nodeauth, and they had made a fix for this. On the tedious side, Ian has made a PR that embedded their fix and this PR has been merged and released in tedious 9.2.2. You can check the detail in the #1146 comment thread. Sadly, node-mssql has not been actively maintaining for a while, so it still using a pretty old version of tedious. Could you try your code with the latest tedious without the node-mssql layer? See if this resolves the issue.