CODEWITHSUNDEEP
node.jssql-servervisual-studio-code
Martin
Martin

Reputation: 1

Can't excecute SQL query to SQL Server express from VS Code using node-mssql

I am using node mssql (https://www.npmjs.com/package/mssql/v/9.0.1) to execute SQL statements within Visual Studio Code to get data from a SQL Server Express database on localhost. However, each time I execute my code, I get this error:

Login failed for user 'xx'. Reason: Password did not match that for the login provided. [CLIENT: 127.0.0.1]
Error: 18456, Severity: 14, State: 8.

I have already enabled mixed server authentication (Windows and SQL Server authentication mode) within SQL Server properties -> security and restarted the service multiple times as suggested in other posts.

The users I have tried to create are set with SQL Server authentication within the login properties, connection permission is granted and login is enabled. Furthermore they have owner permissions over the database in question.

I don't understand why I am getting the password error as I am sure that I provide the correct password and username for the users that I am attempting to connect with.

Here is my code

const sql = require("mssql");

const config = {
    server: "localhost",
    port: 1433,
    user: "xx",
    passwword: "xxx",
    database: "SQL_tutorial",
    options: {
        enableArithAbort: true,
        trustServerCertificate: true,
        encrypt: false,
        TRUSTED_CONNECTION: true,
    },
    connectionTimeout: 150000,
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000,
    },
};

sql.on('error', err => {
    console.log(err.message)
})

async function getData(){
    try {
        let pool = await sql.connect(config)
        let result1= await pool.request().query("select * from Employeedemographics");
        console.log(result1);
        sql.close();
    } catch (error) {
        console.log(error.message);
        sql.close();
    }
}

getData();

Any idea how to fix this or what the problem could be?

Upvotes: 0

Views: 98

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415901

I see both this:

user: "xx",
passwword: "xxx",

and this:

TRUSTED_CONNECTION: true,

That's not right.

Trusted_Connection is the same as integrated security. It means use the authentication token granted to your Windows user account when you first signed in to your computer (or, if your app is running as a service or scheduled task, whatever token was issued for the user account running the service when the process was created). Setting username/password means to use the sql logins defined within SQL Server. You don't use both username and password and TRUSTED_CONNECTION. It's either one or the other.

But I also see this:

The users I have tried to create are set with SQL Server authentication within the login properties, connection permission is granted and login is enabled.

So you should remove the TRUSTED_CONNECTION entry from your options.

If that still doesn't work, based on the error message, I would double and triple check the provided password... maybe even set a different password. Also check whether they are still flagged to need to change their password at first login.

Upvotes: 1

Related Questions