CODEWITHSUNDEEP
node.jssql-servertypescriptvisual-studio-codevscode-extensions
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

Can't connect to SQL Server from my VS Code extension (node.js)

I'm developing VS Code extension and want to get some data from SQL Server database. I've tried many different examples but non of them are working for me.

        const Connection = require('tedious').Connection;

        const config = {
            user: 'ssrs', // tried userName, username
            password: 'ssrs', 
            server: 'localhost',
            options: {
                database: 'imdb' 
            }
          }

          const connection = new Connection(config);
          connection.on('connect', function(err: string) {
            if (err) {
              console.log(err);
            } else {
              console.log('Connected');
            }
          });

OR

        await sql.connect('mssql://ssrs:ssrs@localhost/imdb')
        const result = await sql.query`select 1 as one`
        console.dir(result)

OR

    const sql = require("mssql");
    const conn = new sql.ConnectionPool({
        database: "imdb",
        server: "localhost",
        driver: "msnodesqlv8",
        userName: "ssrs",
        password: "ssrs"
    });

    conn.connect().then(() => {
        console.log('Connected');
    });

ConnectionError: Login failed for user ''.

enter image description here

Connecting via sqlcmd:

enter image description here

The port is also open (1433) as I can telnet to it.

Messages from SQL Server log:

Date 2019-05-08 11:19:02 AM Log SQL Server (Current - 2019-05-05 2:53:00 PM)

Source Logon

Message Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: 127.0.0.1]

Date 2019-05-08 11:19:02 AM Log SQL Server (Current - 2019-05-05 2:53:00 PM)

Source Logon

Message Error: 18456, Severity: 14, State: 58.

Trying to connect with Powershell:

  PS C:\WINDOWS\system32> Invoke-Sqlcmd -query "select CURRENT_USER, USER_NAME()" -ServerInstance "localhost" -username "ssrs" -password "ssrs" -Database 'imdb'

Column1 Column2
------- -------
ssrs    ssrs

Upvotes: 7

Views: 13089

Answers (2)

Alberto Solano
Alberto Solano

Reputation: 8227

As shown in your screenshot, you're able to connect via SQLCMD utility. This means there's no issue with the user or the authentication mode in SQL Server. The error message is however misleading and other users already experienced this here.

I'm no expert of node.js but I'd recommend you to better understand how the mssql plugin and Connection object work. I think that some missing setting in your configuration is making the connection attempt fail.

For example, this code you posted is wrong (userName is not valid):

const sql = require("mssql");
const conn = new sql.ConnectionPool({
    database: "imdb",
    server: "localhost",
    driver: "msnodesqlv8",
    userName: "ssrs",
    password: "ssrs"
});

conn.connect().then(() => {
    console.log('Connected');
});

It should be:

const sql = require("mssql");
const conn = new sql.ConnectionPool({
    database: "imdb",
    server: "localhost",
    driver: "msnodesqlv8",
    user: "ssrs",
    password: "ssrs"
});

conn.connect().then(() => {
    console.log('Connected');
});

Upvotes: 1

tukan
tukan

Reputation: 17347

The reason you can't connect is written in the log:

Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only.

You have to change the authentication mode (which you already did according to the screenshot posted) and restart the SQL Server service with Agent. Did you restart the service after changing the mode?

Upvotes: 1

Related Questions