6

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");

Improve this question
4
  • 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)? Commented Jun 15, 2017 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 ' '. [...] Commented Jun 16, 2017 at 13:49
  • Thanks for your help, problem solved. ^_^ Commented Jun 16, 2017 at 14:19
  • If you solved your problem, you can answer your own question! This also removes it from the unanswered queue. Commented Feb 1, 2018 at 16:26

3 Answers 3

Reset to default
3

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

If I'm not mistaken, msnodesqlv8 only runs on Windows... how to do on other platforms?
Good job on the packages versions for prev node versions!
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.

Improve this answer

1 Comment

Hey, your answer should be specific to question not in general, you can edit it your answer though.
0

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 !!!!!!!!!!!!!!!

Improve this answer

1 Comment

your answer is not about windows authentication. Your answer suggests allowing SQL Server Authentication which is not the same as Windows Authentication.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.