Node.js Connection with SQL Server windows authentication

Question

I'm trying to connect node.js to mssql in Windows Authentication mode. I installed the tedious,mssql and msnodesqlv8 modules, but I still can't figure out how to open a connection.

This is what I tried:

    var sql = require('mssql');
    var config = {
    driver: 'msnodesqlv8',
    server: 'POPPY-GI\\SQLEXPRESS',
    database: 'NodinSports',
    options:{
    trustedConnection: true,
    useUTC: true}}

    sql.connect(config).then(function() { 
    new sql.Request().query('select * from users')
    .then(function(recordset){
    console.log(recordset);
    }).catch(function(err) {
    console.log(err);});
    }).catch(function(err) {
    console.log(err);});

After running I get a long error saying:

    `ConnectionError`: Port for `SQLEXPRESS` not found in 
 `ServerName`;POPPYGI;`InstanceName;SQLEXPRESS;IsClustered`;No;Version;12.0.2000.8;;

    at Connection.tedious.once.err (D:\Never Lazy\University\`AN2, SEM 2\WEB\`Projek`\node_modules\`mssql`\lib\`tedious.js:216:17`)

    at Connection.g (events.js:291:16)
    at emitOne (events.js:96:13)
    at Connection.emit (events.js:188:7)
    at D:\Never Lazy\University\AN2,SEM2\WEB\Projekt\node_modules\tedious\lib\connection.js:570:27
    at D:\Never Lazy\University\AN2,SEM2\WEB\Projekt\node_modules\tedious\lib\instance-lookup.js:91:24
    at Socket.onMessage (D:\Never Lazy\University\AN2,SEM2\WEB\Projekt\node_modules\tedious\lib\sender.js:140:9)
    at emitTwo (events.js:106:13)
    at Socket.emit (events.js:191:7)
    at UDP.onMessage (dgram.js:549:8)
    code: 'EINSTLOOKUP',

I would be really thankful for any help.

FIXED:

In services.msc check if the followings are enabled:

SQL Server(*server_name*) -- in my case `SQLEXPRESS`
SQL Server Browser
SQL Server Agent(*server_name*) -- if you are using `SQLEXPRESS` this doesn't need to run

In SQL Server Configuration Manager -> Protocols for server_name: enable TCP/IP.

To make sure everything will be fine, check the port the server is using (SQL Server Configuration Manager -> SQL Native Client Configuration -> Client Protocols -> double click on TCP/IP -> Default Port ) , and add the port: *your_port* to the code in var config = { ... }.

Lastly, change var sql = require('mssql'); to var sql = require("mssql/msnodesqlv8");

Is POPPYGI a remote server? Is the SQL Browser service running? Can you connect using SSMS to this instance? What port is the service actually using (the SQL Logs will tell you)? — David Browne - Microsoft, Jun 15 '17 at 20:26
Local server, SQL Browser service is running , port is 1433. I don't really understand what do you mean about " connect using SSMS to this instance". I added the port:1433 to the 'config' , and now i get an error saying : `ConnectionError: Login failed for user ' '.` [...] — Brigitta, Jun 16 '17 at 13:49
If you solved your problem, [you can answer your own question](https://stackoverflow.com/help/self-answer)! This also removes it from the unanswered queue. — Kris Lawton, Feb 01 '18 at 16:26

beatoss · Answer 1 · 2018-02-22T13:55:24.687

2

Install the following modules:

"dependencies": {
    "msnodesqlv8": "^0.4.14",
    "mssql": "^4.1.0"
  }

My node version: v8.1.4

const sql = require("mssql/msnodesqlv8");

const main = async () => {
  const pool = new sql.ConnectionPool({
    server: "myservername",
    database: "mydbname",
    options: {
      trustedConnection: true
    }
  });

  await pool.connect();

  const request = new sql.Request(pool);

  const query = `SELECT [FirstName]
    ,[LastName]
    ,[Email]
FROM [Dev].[Users]`;

  const result = await request.query(query);

  console.dir(result);
};
main();

(You can do it without async or older versions: https://stackoverflow.com/a/40406998/639575)

edited Feb 22 '18 at 13:55

answered Feb 22 '18 at 13:35

beatoss

395
6
13

1

If I'm not mistaken, msnodesqlv8 only runs on Windows... how to do on other platforms? – Michael Oct 30 '18 at 17:36
Good job on the packages versions for prev node versions! – Matteo Pieroni Apr 30 '19 at 09:04

Subhash Prajapati · Answer 2 · 2020-02-06T22:38:17.487

1

The solution given by beatoss works for windows but not for Linux/mac. The msnodesqlv8 driver only works for Windows. For Linux/mac environment, try odbc instead.

edited Feb 06 '20 at 22:38

answered Feb 06 '20 at 00:54

Subhash Prajapati

271
3
17

1

Hey, your answer should be specific to question not in general, you can edit it your answer though. – Ajay Kharade Feb 06 '20 at 01:01

Uzair_07 · Answer 3 · 2023-09-02T09:41:42.353

It may be too late to answer, but this recently happened to me and drove me crazy!!! I was trying to connect my db to express and working with Windows authentication mode. For two long days, I kept googling and refreshing until I got this article: https://www.liquidweb.com/kb/troubleshooting-microsoft-sql-server-error-18456-login-failed-user/
So in a nutshell;
First I installed the msnodesqlv8 driver for Windows authentication, in my server on ssms, I right-clicked on my server then in properties and then security, I enabled SQL server and Windows authentication mode, then in the object explorer, clicked on the plus next to the server, then security, then logins. There I saw sa with a cross next to it. In its properties, I changed my password to something easier to remember (optional), then set the login to enable in the status. PHEW!
Now my config code:

    const config = {
    user: 'sa',
    password: '123',
    driver: "msnodesqlv8",
    server: 'UZAIR-S_PC\\SQLEXPRESS',
    database: 'LearningExpressJS',
    options: {
        trustedconnection: true,
        enableArithAbort : true, 
        trustServerCertificate: true,
        instancename: 'SQLEXPRESS'
    },
    port: 58018
}

This works finally !!!!!!!!!!!!!!!

your answer is not about windows authentication. Your answer suggests allowing SQL Server Authentication which is not the same as Windows Authentication. — Hassaan, Jul 13 '22 at 09:11

Node.js Connection with SQL Server windows authentication

3 Answers3