Node-mssql: Trouble connecting to SQL server (SQL instance name missing)

[aruss]

Hi I am having troubles connecting to SQL server

My MSSQL version is 12.0.2000.8 (SQL Server 2014, local instance not azure)

My code is mostly just from examples

var sql = require('mssql');

var config = {
    user: 'devuser',
    password: 'devuser',
    server: 'GRIFF\\SQLEXPRESS',
    database: 'devdb',
    port: 1433,
    debug: true,
    options: {
        encrypt: false // Use this if you're on Windows Azure
    }
}

var connection = sql.connect(config, function(err) {

    console.log(err);

    var tpl = 'insert into Users (DisplayName, Email) values ("@displayName", "@email")';
    var ps = new sql.PreparedStatement(connection);
    ps.input('displayName', sql.NVarChar(255));
    ps.input('email', sql.NVarChar(255));
    ps.prepare(tpl, function(err) {

        console.log(err);

        var user = {
            displayName: 'Jim',
            email: '[email protected]'
        };

        ps.execute(user, function(err, recordset) {

            console.log(err);
            console.log(recordset);

            ps.unprepare(function(err) {

                console.log(err);
            });
        });
    });
});

I get following response

{ [ConnectionError: Failed to connect to GRIFF:undefined in 15000ms]
  name: 'ConnectionError',
  message: 'Failed to connect to GRIFF:undefined in 15000ms',
  code: 'ETIMEOUT' }

C:\Temp\mssqlfoo\node_modules\mssql\lib\main.js:583
        this.connection.pool.acquire(done);
                             ^
TypeError: Cannot call method 'acquire' of null
    at PreparedStatement._prepare (C:\Temp\mssqlfoo\node_modules\mssql\lib\main.js:583:30)
    at PreparedStatement.prepare (C:\Temp\mssqlfoo\node_modules\mssql\lib\main.js:513:21)
    at C:\Temp\mssqlfoo\sql.js:22:5
    at C:\Temp\mssqlfoo\node_modules\mssql\lib\main.js:268:20
    at C:\Temp\mssqlfoo\node_modules\mssql\lib\tedious.js:394:20
    at C:\Temp\mssqlfoo\node_modules\mssql\node_modules\generic-pool\lib\generic-pool.js:274:11
    at Connection.<anonymous> (C:\Temp\mssqlfoo\node_modules\mssql\lib\tedious.js:347:26)
    at Connection.g (events.js:180:16)
    at Connection.emit (events.js:95:17)
    at Connection.connectTimeout (C:\Temp\mssqlfoo\node_modules\mssql\node_modules\tedious\lib\connection.js:709:10)

Content of mssql_debug_1429881975435.log

Failed to connect to GRIFF:undefined in 15000ms

That GRIFF:undefined thing looks pretty wrong, the SQL instance name is missing. Do I do something wrong here?

[tracker1]

@aruss according to tedious docs on the connection api...

var config = {
    user: 'devuser',
    password: 'devuser',
    server: 'GRIFF',
    database: 'devdb',
    port: 1433,
    debug: true,
    options: {
        encrypt: false // Use this if you're on Windows Azure
        ,instanceName: 'SQLEXPRESS'
    }
}

[aruss]

Found the problem, it was my SQL server configuration the TCP/IP protocol was disabled.

[benoror]

@aruss would you mind sharing your solution? I'm having the exact same issue

[benoror]

Found this: https://msdn.microsoft.com/en-us/library/bb909712(v=vs.90).aspx

But TCP/IP is enabled, although TCP Dynamic Ports is enabled, could that be the issue?

[benoror]

Update: Fixed with: http://dba.stackexchange.com/questions/62165/i-cant-connect-to-my-servers-sql-database-via-an-ip-address

Although I would like to use instance names for other scenarios

[malcolmswaine]

Make sure that the SQL Server Browser Service is running. Turning it off will cause this error to be raised.

[Kishanvikani]

I have enabled the TCP Connections and Also the browser service is running still i am facing the same issue. Could have a look at the images i have attached and provide my with a solution. I have attched the error , sql server connection and TCP protocol snippets.

My server.js is as follows:

var express = require('express');
var app = express();

app.get('/', function (req, res) {

var sql = require("mssql");

// config for your database
var config = {
    //user: 'HP-SYS\Kishan',
    //password: '',
    server: 'HP-SYS\SQLEXPRESS', 
    database: 'FootballLeague' 
};

// 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 Leagues', function (err, recordset) {

        if (err) console.log(err)

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

    });
});

});

var server = app.listen(5000, function () {
console.log('Server is running..');
});

TCP Settings:

[aruss]

You need to create user for the database e.g. devuser and assign this user to database, if you try to login with trusted connection you should read the docs of that npm package. but you should go with a sql user instead of a windows user.

[Kishanvikani]

@aruss

I have now given a user name to login bow i am facing this issue which appears on chrome page itself

Error: Global connection already exists. Call sql.close() first.
at Object.connect (D:\Work\Nodejs\node_modules\mssql\lib\base.js:1591:31)
at D:\Work\Nodejs\server.js:17:9
at Layer.handle [as handle_request] (D:\Work\Nodejs\node_modules\express\lib\router\layer.js:95:5)
at next (D:\Work\Nodejs\node_modules\express\lib\router\route.js:137:13)
at Route.dispatch (D:\Work\Nodejs\node_modules\express\lib\router\route.js:112:3)
at Layer.handle [as handle_request] (D:\Work\Nodejs\node_modules\express\lib\router\layer.js:95:5)
at D:\Work\Nodejs\node_modules\express\lib\router\index.js:281:22
at Function.process_params (D:\Work\Nodejs\node_modules\express\lib\router\index.js:335:12)
at next (D:\Work\Nodejs\node_modules\express\lib\router\index.js:275:10)
at expressInit (D:\Work\Nodejs\node_modules\express\lib\middleware\init.js:40:5)

[aruss]

after you added the user to your sql db, set it to dbowner if the user should be able to create/alter tables, then you have to verify that your user works, just try to login with that user on your sql server via ssms

Enable SQL authentication
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode#SSMSProcedure

Create that user

Assign the database to that user (double click on user and go to "User Mappings" section)

An then try to login with that user with ssms tool, if you configured it properly you will be able to login and will be able to see the database.

Hope it solves your problem.

Regarding the NPM package, I have no clue about it, if you still have problems you should create a new issue.

Cheers

[ownspark]

Your code has a very common mistake. If you read the error carefuly the answer is right there: you forgot to use a double-backslash on the instance name for the server parameter

server: 'server\instance_name'
instead you have it as:
server: "server\instance_name"

in the error you see the backslash suppressed.
it shows as: hp-sysEXPRESS (notice that the single backslash is suppressed in the error message). The connection attempted to contact a server that "doesn't exist".