使用NodeJS中的Service Principal连接到Azure SQL，但令牌被拒绝 [英] Connecting to Azure SQL using Service Principal in NodeJS, but token is rejected
问题描述
我无法让我的NodeJS应用程序使用服务主体连接到Azure SQL数据库.但是，当我尝试对C#代码段执行相同的操作时，效果很好.我注意到的是，两种语言的auth返回的令牌都有点不同，如果我从C#中获取正确的令牌并将其硬编码到NodeJS中，则我的SQL连接现在可以成功.
我首先使用ms-rest-azure进行身份验证，并提供我的clientId，tenantId和clientSecret.这将返回一个有效的凭证，我将从该凭证中提取accessToken.
然后，我使用乏味的方法尝试在* .database.windows.net上连接到Azure SQL，并在配置中提供accessToken值.
我只是用户'<令牌标识的主体>'
登录失败在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');const {reject} = require('async');让clientSecret ="xxx"；让serverName ="xxx.database.windows.net";让databaseName ="xxx"；让clientId ="xxx";让tenantId ="xxx"；azureCredentials = msRestAzure.loginWithServicePrincipalSecret(clientId，clientSecret，tenantId，function(err，凭据){如果(err)返回console.log(err);certificate.getToken((err，results)=> {if(err)返回拒绝(err);让accessToken = results.accessToken;var Connection = require('tedious').Connection;var Request = require('乏味').Request;var config = {服务器:serverName，验证: {类型:"azure-active-directory-access-token"，选项: {令牌:accessToken}}，选项: {调试:{数据包:是的，数据:是的，有效负载:true，令牌:false，日志:真}，数据库:databaseName，加密: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);});函数executeStatement(){request = new Request("select * from Text"，function(err，rowCount){如果(错误){console.log(err);} 别的 {console.log(rowCount +'rows');}connection.close();});request.on('row'，function(columns){column.forEach(function(column){如果(column.value === null){console.log('NULL');} 别的 {console.log(column.value);}});});request.on('done'，function(rowCount，more){console.log(rowCount +'返回的行数');});connection.execSql(request);}});}) 当我们使用软件包 ms-rest-azure 中的证书获取令牌时，默认情况下，令牌的受众是 https://management.core.windows.net/，它只能用于调用Azure rest api.如果我们要使用Azure AD令牌连接sql，则该令牌的读者应为 https://database.windows.net/.因此，我们应该更新用于获取令牌的代码
msrestAzure.loginWithServicePrincipalSecret(clientId，clientSecret，tenantId，{tokenAudience:"https://database.windows.net/"，}， 例如
- 创建服务主体
az登录az ad sp-rbac -n'MyApp'--skip-assignment - 配置SQL数据库
a.
有关更多详细信息，请在此处
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
- Create A service principal
az login
az ad sp create-for-rbac -n 'MyApp' --skip-assignment
- 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>]
- 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中的Service Principal连接到Azure SQL，但令牌被拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
