如何使用 mssql 模块从 Node.JS 通过 Windows 身份验证连接到 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33709807/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to connect to SQL Server with windows authentication from Node.JS using mssql module
提问by F0r3v3r-A-N00b
Hi I'm unable to connect to SQL server that is using windows authentication in node js. I'm using the mssql module. The error message is :
嗨，我无法连接到在节点 js 中使用 Windows 身份验证的 SQL 服务器。我正在使用 mssql 模块。错误信息是：
[ConnectionError: Login failed for user ''. The user is not associated with a trusted SQL Server connection.]
name: 'ConnectionError',
message: 'Login failed for user \'\'. The user is not associated with a trusted SQL Server connection.',
code: 'ELOGIN' }
Here is my code:
这是我的代码：
config = {
server : "localhost\MSSQLSERVER",
database : "mydatabase",
port : 1433
}
function loadDepts() {
var conn = new sql.Connection(config);
var request = sql.Request(conn);
conn.connect(function(err) {
if (err) {
console.log(err);
return;
}
request.query("select deptid, deptname from departments", function(err, table) {
if (err) {
console.log(err);
return;
}
else {
console.log(table);
}
conn.close();
});
});
}
loadDepts();
回答by Aaron Ballard
Since this is a fairly visible answer, I wanted to add in a code snippet that worked for me with Trusted Connection. Got to it from getglad'sedited answer.
由于这是一个相当明显的答案，我想添加一个适用于受信任连接的代码片段。从getglad编辑的答案中得到了它。
const sql = require("mssql");
require("msnodesqlv8");
const conn = new sql.Connection({
database: "db_name",
server: "server_name",
driver: "msnodesqlv8",
options: {
trustedConnection: true
}
});
conn.connect().then(() => {
// ... sproc call, error catching, etc
// example: https://github.com/patriksimek/node-mssql#request
});
Using trusted connection, I was able to execute stored procedures, log the output, and close the connection without any trouble, and msnodesqlv8has been updated more recently than any of the other drivers (latest release was October 2016 as of 11/3/2016), so that seems to be a safe choice as well.
使用受信任的连接，我能够执行存储过程、记录输出并关闭连接而没有任何问题，并且msnodesqlv8 的更新比任何其他驱动程序都更新（最新版本是 2016 年 10 月，截至 2016 年 11 月 3 日） )，所以这似乎也是一个安全的选择。
And here's an example using [email protected]. The only changes are the initial require, which pull in msnodesqlv8 from within mssql, and sql.Connection is now sql.ConnectionPool. You will also need to change your stored procedure calls since the response is different, noted here. Credit to Jon's answer since he updated mine before I did!
这是使用 [email protected] 的示例。唯一的变化是初始要求，它从 mssql 中引入 msnodesqlv8，而 sql.Connection 现在是 sql.ConnectionPool。您还需要更改您的存储过程调用，因为响应不同，请在此处注明。归功于 Jon 的回答，因为他在我之前更新了我的！
const sql = require("mssql/msnodesqlv8");
const conn = new sql.ConnectionPool({
database: "db_name",
server: "server_name",
driver: "msnodesqlv8",
options: {
trustedConnection: true
}
});
conn.connect().then(() => {
// ... sproc call, error catching, etc
// example: https://github.com/patriksimek/node-mssql#request
});
回答by Jon Koala
I have been struggling too for some time about how to use mssql + Windows Auth, here is how i got it to work on my project.
我也一直在为如何使用 mssql + Windows Auth 苦苦挣扎，这是我如何让它在我的项目中工作。
As pointed out in the mssql documentation, you need msnodesqlv8 installed too.
正如mssql 文档中所指出的，您也需要安装 msnodesqlv8。
npm install msnodesqlv8
Now, following on Aaron Ballard's answer, you use it like this:
现在，按照Aaron Ballard 的回答，您可以这样使用它：
const sql = require('mssql/msnodesqlv8')
const pool = new sql.ConnectionPool({
database: 'database',
server: 'server',
driver: 'msnodesqlv8',
options: {
trustedConnection: true
}
})
pool.connect().then(() => {
//simple query
pool.request().query('select 1 as number', (err, result) => {
console.dir(result)
})
})
As a note, i tried to add this as a comment on Aaron's answer, as mine is just a complement/update to his, but i don't have enough reputation to do so.
作为说明，我试图将其添加为对 Aaron 答案的评论，因为我的只是对他的补充/更新，但我没有足够的声誉来这样做。
回答by getglad
I have never been able to get mssql+ windows auth to work for any of my projects. Try edgeand edge-sql- it has worked for me. Be sure you install allthe required packages.
我从来没有能够让mssql+ windows auth 为我的任何项目工作。尝试edge和edge-sql-它已经对我来说有效。确保您安装了所有必需的软件包。
https://github.com/tjanczuk/edge
https://github.com/tjanczuk/edge
https://github.com/tjanczuk/edge-sql
https://github.com/tjanczuk/edge-sql
From there, it's pretty steamlined.
从那里，它是相当蒸汽线。
var edge = require('edge');
var params = {
connectionString: "Server=YourServer;Database=YourDB;Integrated Security=True",
source: "SELECT TOP 20 * FROM SampleData"
};
var getData = edge.func( 'sql', params);
getData(null, function (error, result) {
if (error) { console.log(error); return; }
if (result) {
console.log(result);
}
else {
console.log("No results");
}
});
EDIT
编辑
Well... 10 days after my original answer, apparently mssqladded Windows Auth to the package. They heard our cries :) See here. I have not tested it yet, but it is officially in my backlog to test integration. I will report back.
嗯......在我最初的回答后 10 天，显然mssql在包中添加了 Windows 身份验证。他们听到了我们的呼喊：)看这里。我还没有测试它，但它正式在我的待办事项中以测试集成。我会回来报告的。
FWTW, if mssqlfits your needs, I would go with it, as 1) edge-sqlhas been dormant for 2 years and 2) the primary contributor has said he has left projects like this "in the caring hands of Microsoft", since he no longer works there.
FWTW，如果mssql符合您的需求，我会选择它，因为 1)edge-sql已经休眠了 2 年，并且 2) 主要贡献者说他已经将这样的项目“交给了微软的关心之手”，因为他不再工作了那里。
EDIT 2
编辑 2
This keeps getting upvotes and there are comments saying some of the other answers' code examples either aren't working or aren't working on Windows.
这不断获得赞成票，并且有评论说其他一些答案的代码示例要么不起作用，要么在 Windows 上不起作用。
This is my code using mssql, working on Windows, with msnodesqlv8also installed:
这是我使用的代码mssql，在 Windows 上工作，msnodesqlv8还安装了：
var sql = require('mssql/msnodesqlv8');
var config = {
driver: 'msnodesqlv8',
connectionString: 'Driver={SQL Server Native Client XX.0};Server={SERVER\NAME};Database={dbName};Trusted_Connection={yes};',
};
sql.connect(config)
.then(function() {
...profit...
})
.catch(function(err) {
// ... connect error checks
});
回答by Dave
I've tried many variations and this is my complete solution.
I'm using SQL server Express.
I'm connecting, in the first instance, to the MASTERdatabase only.
You only NEED to change "YOURINSTANCE\\SQLEXPRESS".
(Be sure to maintain the double-slash above!!!)
I'm using INTEGRATED SECURITYtoo.
The query relies on nothing at all (in your database).
You need to add your node packages
==> NPM INSTALL MSSQLand
==> NPM INSTALL msnodesqlv8
Hopefully, your connection issues will be a thing of the past.
Maybe.
Please.
我尝试了很多变体，这是我的完整解决方案。
我正在使用SQL 服务器 Express。
首先，我只连接到MASTER数据库。
您只需要更改“ YOURINSTANCE\\SQLEXPRESS”。
（一定要保持上面的双斜杠！！！）
我也在使用集成安全。
查询完全不依赖（在您的数据库中）。
您需要添加您的节点包
==> NPM INSTALL MSSQL和
==> NPM INSTALL msnodesqlv8
希望您的连接问题将成为过去。
也许。
请。
// More here -> https://www.npmjs.com/package/mssql
var sql = require('mssql/msnodesqlv8');
var config = {
connectionString: 'Driver=SQL Server;Server=YOURINSTANCE\SQLEXPRESS;Database=master;Trusted_Connection=true;'
};
sql.connect(config, err => {
new sql.Request().query('SELECT 1 AS justAnumber', (err, result) => {
console.log(".:The Good Place:.");
if(err) { // SQL error, but connection OK.
console.log(" Shirtballs: "+ err);
} else { // All is rosey in your garden.
console.dir(result);
};
});
});
sql.on('error', err => { // Connection borked.
console.log(".:The Bad Place:.");
console.log(" Fork: "+ err);
});
回答by John
I could only get a Trusted Connection working using msnodesqlv8 (limited to Windows environments) with a connection string (rather than a config object).
我只能使用带有连接字符串（而不是配置对象）的 msnodesqlv8（仅限于 Windows 环境）来获得可信连接。
const sql = require("msnodesqlv8");
const connectionString = function(databaseName) {
return "Server=.;Database=" + databaseName + ";Trusted_Connection=Yes;Driver={SQL Server Native Client 11.0}";
}
sql.query(connectionString("DatabaseName"), "SELECT * FROM dbo.Table1" , (err, recordset) => {
if(err) {
// Do something with the err object.
return;
}
// else
// Do something with the recordset object.
return;
});
回答by vandana agrawal
Below code is working for me......
下面的代码对我有用......
const sql = require('mssql/msnodesqlv8')
// config for your database
var config = {
driver: 'msnodesqlv8',
server: 'serverNAme\SQLEXPRESS',
database: 'Learn' ,
options: {
trustedConnection: true
}
};
回答by Rajesh Kumar
It worked for me need to install msnodesqlv8 and mssql. also .......:)
它对我有用，需要安装 msnodesqlv8 和 mssql。还 .......：）
var dbConfig = {
driver: 'msnodesqlv8',
server: "DESKTOP-66LO4I3",
database: "FutureHealthCareWeb",
user: "sa",
password: "pass@123",
options: {
trustedConnection: true
},
debug: true,
parseJSON: true
};
var sql = require('mssql/msnodesqlv8');
sql.connect(dbConfig, function (err) {
if (err) { console.log(JSON.stringify(err)+'..............') }
else {
console.log('Connected')
}
}
);
回答by Олег Шиловский
I've just add domain: "DNAME", in config, and as result this config helps me connect to MS SQL with windows auth.
我刚刚在配置中添加了domain: "DNAME"，因此该配置帮助我使用 Windows 身份验证连接到 MS SQL。
const config = {
driver: 'msnodesqlv8',
domain: "DNAME",
user: 'username',
password: 'pass',
server: '7.6.225.22',
database: 'DBNAME',
requestTimeout: 3600000, //an hour
options: {
trustedConnection: true
},
debug: true,
parseJSON: true
};
回答by MAFAIZ
I struggled to connect with mssql server which run in remote windows server using windows authentication mode. Then i found the solution just used like below code.
我努力连接使用Windows 身份验证模式在远程 Windows 服务器中运行的 mssql 服务器。然后我找到了像下面代码一样使用的解决方案。
sql.connect("Data Source=172.25.x.x,1433;User Id=CSLx\Name;Password=xxxxxx1234;Initial Catalog=giveTHedataabseNamel;Integrated Security=True",function(err){ }
