CODEWITHSUNDEEP
sql-servernode.js
user9233100
user9233100

Reputation:

unable to connect to ms sql window authetication using node js

Here is my node js code

app.get('/', function (req, res) {
var config = {
    server: 'localhost\\SQLEXPRESS2008',
    database: 'TestingDB'
};

// connect to your database
sql.connect(config, function (err) {

    if (err) console.log(err);

    // create Request object
    var request = new sql.Request();

    // query to the database and get the records
    request.query('select * from userform', function (err, recordset) {

        if (err) console.log(err)

        // send records as a response
        res.send(recordset);

        console.log(recordset);
    });
});

});

Please suggest me to connect sql and getting this error after running this in command prompt

Server is running.. on Port 8020

{ Error: Failed to connect to localhost:undefined in 15000ms

    at Connection.tedious.once.err (D:\Nodejs\UsersCreate\node_modules\mssql\lib\tedious.js:216:17)
    at Object.onceWrapper (events.js:293:19)
    at emitOne (events.js:96:13)
    at Connection.emit (events.js:191:7)
    at Connection.connectTimeout (D:\Nodejs\UsersCreate\node_modules\tedious\lib\connection.js:795:12)
    at ontimeout (timers.js:386:14)
    at tryOnTimeout (timers.js:250:5)
    at Timer.listOnTimeout (timers.js:214:5)
  code: 'ETIMEOUT',
  originalError:
   { ConnectionError: Failed to connect to localhost:undefined in 15000ms
       at ConnectionError (D:\Nodejs\UsersCreate\node_modules\tedious\lib\errors.js:12:12)
       at Connection.connectTimeout (D:\Nodejs\UsersCreate\node_modules\tedious\lib\connection.js:795:28)
       at ontimeout (timers.js:386:14)
       at tryOnTimeout (timers.js:250:5)
       at Timer.listOnTimeout (timers.js:214:5)
     message: 'Failed to connect to localhost:undefined in 15000ms',
     code: 'ETIMEOUT' },
  name: 'ConnectionError' }
{ ConnectionError: Connection is closed.
    at Request._query (D:\Nodejs\UsersCreate\node_modules\mssql\lib\base.js:1299:37)
    at Request._query (D:\Nodejs\UsersCreate\node_modules\mssql\lib\tedious.js:497:11)
    at Request.query (D:\Nodejs\UsersCreate\node_modules\mssql\lib\base.js:1242:12)
    at D:\Nodejs\UsersCreate\app.js:118:17
    at _poolCreate.then.catch.err (D:\Nodejs\UsersCreate\node_modules\mssql\lib\base.js:269:7) code: 'ECONNCLOSED', name: 'ConnectionError' }
undefined

Upvotes: 1

Views: 1146

Answers (3)

max
max

Reputation: 467

you have to write the database instance in config.options.instanceName

var config = {
    server: 'localhost\\SQLEXPRESS2008',
    database: 'TestingDB'
};

instead:

const config = {
    user: '...',
    password: '...',
    server: 'localhost',
    database: 'TestingDB',
    options: {
        encrypt: false, // Use this if you're on Windows Azure
        instanceName: 'SQLEXPRESS2008'
    } 
};

Upvotes: 0

Fernando Paz
Fernando Paz

Reputation: 623

Installing mssql package I had a similiar problem and I got fixed addiyng an instance and database name to my config.

I thought it was better create an user on db.

After I need to handling the errors, addiyng try catch to my function that will connect in db and closing the connection if the function get an error.

All database connections are in a different file, so I export the modules to use in my application and if I need to use in other application just add a reference to that

let QueryDB = {
    Query_SelectAll: 'SELECT [COLUMN] \
                                , [COLUMN] \
                                , [COLUMN] \
                FROM [DATABASE].[dbo].[TABLE_NAME]',

    Query_Delete: 'DELETE FROM [DATABASE].[dbo].[TABLE_NAME] WHERE COLUMN = '
};

I put all queries in an object to simplify the use for me.

let ConnectionString = {
    user: 'YOUR_DBUSER',
    password: '****',
    server: '0.0.000.00',
    options: {
        instance: 'YOURINSTANCE',
        database: 'DATABASE_NAME'
    }
};

In the server property, I put the server IP, after add the options with instance and database name.

function SQL_Server(Query, ret) {
    sql.close();
    sql.connect(ConnectionString).then(() => {
        console.log("Connected");
        return request(Query, ret);
    }).catch((error) => {
        console.log("Connect error");
        console.error(error);
        sql.close();
    });

}

function request(Query, ret) {
    let returning = undefined;
    let request = new sql.Request();
    (request.query(Query).then((recordset) => {
        sql.close();
        returning = recordset.recordset;
    }).catch((error) => {
        console.error(error);
        sql.close();
    })).then(() => {
        if (ret != undefined)
            ret(returning);
        console.log("Successful object return");
    });
}

Those are my connect and request functions.

module.exports.SQL_Server = SQL_Server;
module.exports.QueryDB = QueryDB;

So I export the modules to use the functions.

An example of using:

let DataBase = require("./Connection_Query");
let FuncSql = DataBase.SQL_Server;
let SQL = DataBase.QueryDB;

APP.get(ProjectName + '/Home', (req, resp) => {
    FuncSql(SQL.Query_SelectAll, (rec) => {
        resp.render("Home", { YourObjects: rec });
    });
});

Think that you use this answer to guide you.

Upvotes: 0

sanatsathyan
sanatsathyan

Reputation: 1763

This might help :

  1. Start the "SQL SERVER BROWSER" Service in Windows services (I've configured it to start automatically)

  2. allow SQL Server Express to accept remote connections over TCP/IP for port 1433 : http://support.webecs.com/kb/a868/how-do-i-configure-sql-server-express-to-allow-remote-tcp-ip-connections-on-port-1433.aspx

Finally restart 'SQL Server' and 'SQL Browser Agent' services

Upvotes: 1

Related Questions