在 NodeJS 中使用服务主体连接到 Azure SQL，但令牌被拒绝 [英] Connecting to Azure SQL using Service Principal in NodeJS, but token is rejected

问题描述

我无法让我的 NodeJS 应用程序使用服务主体连接到 Azure SQL 数据库.但是，当我尝试用 C# 代码段做同样的事情时，它工作得很好.我注意到两种语言的身份验证返回的令牌有点不同，如果我从 C# 中获取正确的令牌并将其硬编码到 NodeJS 中，我的 SQL 连接现在成功了.

我首先使用 ms-rest-azure 执行身份验证，并提供我的 clientId、tenantId 和 clientSecret.这将返回一个有效的凭据，我从中提取 accessToken.

然后，我使用繁琐的方法尝试连接到 *.database.windows.net 上的 Azure SQL 并在配置中提供 accessToken 值.

我刚刚收到用户<token-identified principal>"登录失败

我在 ms-rest-azure 登录中做错了什么，给了我一个被 Azure SQL 拒绝的令牌?我看到的一件事是，工作令牌的受众是 database.windows.net，其中来自 ms-rest-azure 的是 management.core.windows.net.

我已经被困了几天，如果有人在这里有任何线索，那就太棒了.ms-rest-azure 上的文档似乎根本不存在，只是为您提供了 Azure 销售页面的临时解决方案.

const msRestAzure = require('ms-rest-azure');常量 { 拒绝 } = 要求('异步');让clientSecret =xxx"；让 serverName = "xxx.database.windows.net";让数据库名称=xxx"；让clientId =xxx"；让租户Id =xxx"；azureCredentials = msRestAzure.loginWithServicePrincipalSecret(clientId，clientSecret，tenantId，函数(错误，凭据){如果(错误)返回控制台.log(错误)；credentials.getToken((err, results) => {如果(错误)返回拒绝(错误)；让 accessToken = results.accessToken;var Connection = require('乏味').Connection;var Request = require('乏味').Request;变量配置 = {服务器:服务器名称，验证: {类型:'azure-active-directory-access-token'，选项: {令牌:访问令牌}}，选项: {调试:{数据包:真实，数据:真实，有效载荷:真，令牌:假，日志:真},数据库:数据库名称，加密:真}};var connection = new Connection(config);连接.连接()；connection.on('connect', function(err) {如果(错误){控制台日志(错误)；}执行语句()；});connection.on('调试'，函数(文本){控制台.log(文本)；});函数执行语句(){request = new Request("select * from Text", function(err, rowCount) {如果(错误){控制台日志(错误)；} 别的 {console.log(rowCount + '行');}连接.close();});request.on('行'，函数(列){columns.forEach(函数(列){if (column.value === null) {console.log('NULL');} 别的 {控制台.log(column.value);}});});request.on('done', function(rowCount, more) {console.log(rowCount + '返回的行数');});connection.execSql(request);}});})

解决方案

当我们使用ms-rest-azure包中的证书获取token时，默认情况下，token的受众是https://management.core.windows.net/，它只是用来调用Azure rest api.如果我们想使用 Azure AD 令牌连接 sql，令牌的受众应该是 https://database.windows.net/.所以我们应该更新用于获取令牌的代码为

msrestAzure.loginWithServicePrincipalSecret(客户标识，客户秘密，租户 ID，{tokenAudience:https://database.windows.net/"，},

例如

1. 创建服务主体

az 登录az ad sp create-for-rbac -n 'MyApp' --skip-assignment

1. 配置 SQL 数据库

一个.

更多详情请参考这里

I am having trouble getting my NodeJS application to connect to an Azure SQL Database using the Service Principal. However, when I try to do the same thing with a C# Snippet, it works fine. What I have noticed is that the tokens returned by the auth on both languages is a bit different, and if I take the correct token from C# and hard code it into NodeJS, my SQL Connection now succeeds.

I am first using ms-rest-azure to perform my authentication, and providing my clientId, tenantId and clientSecret. This returns back a valid credential, from which I am extracting the accessToken.

Then, I am using tedious to try to connect to the Azure SQL at *.database.windows.net and providing the accessToken value in the configuration.

I just get Login failed for user '<token-identified principal>'

What am I doing wrong in the ms-rest-azure login to give me a token that is rejected by Azure SQL? One thing I saw is that the working token has an audience of database.windows.net, where-as the one from ms-rest-azure is management.core.windows.net.

I've been stuck for a few days, if anybody has any clues here that would be awesome. The documentation on ms-rest-azure seems pretty non-existent and just gives you the runaround to Azure Sales pages.

const msRestAzure = require('ms-rest-azure');
const { reject } = require('async');


let clientSecret = "xxx";
let serverName = "xxx.database.windows.net";
let databaseName = "xxx";
let clientId = "xxx";
let tenantId = "xxx";

azureCredentials = msRestAzure.loginWithServicePrincipalSecret(clientId, clientSecret, tenantId, function(err, credentials) {
    if (err) return console.log(err);
    credentials.getToken((err, results) => {
        if(err) return reject(err);
        
        let accessToken = results.accessToken;

        var Connection = require('tedious').Connection;
        var Request = require('tedious').Request;

        var config = {
            server: serverName,
            authentication: {
                type: 'azure-active-directory-access-token',
                options: {
                    token: accessToken
                }
            }
            ,options: {
                debug: {
                packet: true,
                data: true,
                payload: true,
                token: false,
                log: true
                },
                database: databaseName,
                encrypt: true
            }  
        };

        var connection = new Connection(config);

        connection.connect();

        connection.on('connect', function(err) {
            if(err) {
                console.log(err);
            }
            executeStatement();
        }
        );

        connection.on('debug', function(text) {
            console.log(text);
        }
        );

        function executeStatement() {
        request = new Request("select * from Text", function(err, rowCount) {
            if (err) {
            console.log(err);
            } else {
            console.log(rowCount + ' rows');
            }

            connection.close();
        });

        request.on('row', function(columns) {
            columns.forEach(function(column) {
            if (column.value === null) {
                console.log('NULL');
            } else {
                console.log(column.value);
            }
            });
        });

        request.on('done', function(rowCount, more) {
            console.log(rowCount + ' rows returned');
        });

        connection.execSql(request);
        }
    });   
})

解决方案

when we use the certificates in the package ms-rest-azure to get token, In default, the token's audience is https://management.core.windows.net/, it just can be used to call Azure rest api. If we want to use Azure AD token to connect sql, the token's audience should be be https://database.windows.net/. So we should update the code used to get token as

msrestAzure.loginWithServicePrincipalSecret(
    clientId,
    clientSecret,
    tenantId,
    {
      tokenAudience: "https://database.windows.net/",
    },

For example

1. Create A service principal

az login
az ad sp create-for-rbac -n 'MyApp' --skip-assignment

2. Configure SQL Database

a. Use your Azure Sql AD admin to connect Azure SQL vai SSMS

b. Add the service principal to the database you need use

create user [<Azure_AD_principal_name>] from external provider
ALTER ROLE db_owner ADD MEMBER [<Azure_AD_principal_name>]
     

3. code

var msrestAzure = require("ms-rest-azure");
var { Connection, Request } = require("tedious");

let clientSecret = "xxx";
let serverName = "xxx.database.windows.net";
let databaseName = "xxx";
let clientId = "xxx";
let tenantId = "xxx";

async function getConnect() {
  // way for Azure Service Principal
  let databaseCredentials = await msrestAzure.loginWithServicePrincipalSecret(
    clientId,
    clientSecret,
    tenantId,
    {
      tokenAudience: "https://database.windows.net/",
    },
  );

  // getting access token
  let databaseAccessToken = await new Promise((resolve, reject) => {
    databaseCredentials.getToken((err, results) => {
      if (err) return reject(err);
      resolve(results.accessToken);
    });
  });
  var config = {
    server: serverName,
    authentication: {
      type: "azure-active-directory-access-token",
      options: {
        token: databaseAccessToken,
      },
    },
    options: {
      debug: {
        packet: true,
        data: true,
        payload: true,
        token: false,
        log: true,
      },
      database: databaseName,
      encrypt: true,
    },
  };

  var connection = new Connection(config);
  connection.connect();
  connection.on("connect", function (err) {
    if (err) {
      console.log(err);
    }
    executeStatement(connection);
  });

  connection.on("debug", function (text) {
    console.log(text);
  });
}
function executeStatement(connection) {
  request = new Request("select * from CSVTest", function (err, rowCount) {
    if (err) {
      console.log(err);
    } else {
      console.log(rowCount + " rows");
    }

    connection.close();
  });

  request.on("row", function (columns) {
    columns.forEach(function (column) {
      if (column.value === null) {
        console.log("NULL");
      } else {
        console.log(column.value);
      }
    });
  });

  request.on("done", function (rowCount, more) {
    console.log(rowCount + " rows returned");
  });

  connection.execSql(request);
}

getConnect()
  .then(() => {
    console.log("run successfully");
  })
  .catch((err) => {
    console.log(err);
  });

For more details, please refer to here

这篇关于在 NodeJS 中使用服务主体连接到 Azure SQL，但令牌被拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！