patriksimek / connect-mssql Goto Github PK

I'm running a Ubuntu 16 via VirtualBox on Windows 7, and I connect to an external SQL Server Express 2012 via Internet. I use connect-mssql 1.5.0 on Node 6.0.0.

Session management works as expected, but I get a sh..load of log files in my ubuntu home directory, for each statement that is session-relevant. The log files are hardly readable, but after removing NUL characters, obviously contain sql statements, and the filename contains my local ip combined with the sql server's ip, with format:
192.168.002.208.nnnnn-071.218.124.145.01433
After executing my webservice for some minutes, my home directory is spammed with these files, with nnnnn in the filename constantly changing! What is their purpose, and how do I get rid of them being created?
Thank you!

192.168.002.208.36598-072.104.144.125.01433.txt
(example file attached has the extension .txt added and NUL values removed via notepad++)

I'm using connect-mssql with Tedious in order to connect to an Azure DB.

When the connection is closed an unhandled exception is thrown:

ConnectionError: Connection is closed.
    at /node_modules/mssql/lib/main.js:1487:17
    at process._tickCallback (node.js:355:11)

I believe this should be catched and return an empty session and not causing the server to stop delivering the page.

This is using a 3.x release, which causes various issues (e.g. tediousjs/tedious#515)

I found that connect-mssql tends to have connection closed issues at random.

I had the good fortune of finding out that for reasons I have yet to comprehend, in the _ready prototype function, the connection was closed.

My workaround was to do the following:

mssql_session_store.__proto__._ready = function (callback) {
    if (this.connection.connected) {
        return callback.call(this);
    }

    if (this.connection.connecting) {
        return this.connection.once('connect', callback.bind(this));
    }

    try {
        const current_instance = this;

        this.connection.connect().then(function () {
            return callback.call(current_instance);
        }).catch(error => {
            console.error('Unable to reopen closed connection', error);
            return callback.call(this, new Error(error.message));
        });
    } catch (error) {
        console.error('Unable to reopen closed connection', error);
        return callback.call(this, new Error("Connection is closed."));
    }
}

The issue hasn't cropped up again but that is not saying much as they happen at random. Can you verify if this is indeed a proper workaround?

I've got my session storage setup as follows:

app.use(session({
store: new mssqlStore(
    {
        user: sqlConfig.accessParameters.userName,
        password: sqlConfig.accessParameters.password,
        server: sqlConfig.accessParameters.server,
        database: sqlConfig.accessParameters.options.database
    },
    {
        ttl: 60 * 1000,
        autoRemove: "interval",
        autoRemoveInterval: 70 * 1000
    }
),
secret: "MySecret",
resave: false,
saveUninitialized: false
}));

My time to live on a session is 60 seconds and my auto remove is 70 seconds.

Problem: With this config, if a clients session gets created at second 0 and never again touched/updated it should expire at second 60. However, because the autoremove function has not yet run if the client makes another request anytime between second 60 and 70 the session ID is returned and the clients session is supposedly valid.

Expected: The session has timed out and the session should be forced to be recreated.

Possible solution: The get functions query should contain a where clause dictating that the expired time has not already passed.

The documentation specifies this is correct:

var config = {
user: '...',
password: '...',
server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
database: '...',

options: {
    encrypt: true // Use this if you're on Windows Azure
}
}

However in reality the config object is not supposed to contain the options as they are separate params.

constructor: (config, options) ->
        if options

Possible solutions: Update README to show passing in two different objects, one containing the config and the other containing the options.

OR

Update the code to check if the config param object contains a key named "options" and, if so, set options = config.options.

EDIT: Looking into it more, the config.options values are used within mssql. So the second possible solution isn't really a valid one. So a simple README update to better clarify that optional params for mssql go into config.options and optional params for connect-mssql go into a second parameter.

I don't know why, but the insert statement is fired multiple times with same sid, so the following error is displayed:

RequestError: Violation of PRIMARY KEY constraint 'PK__sessions__DDDFDD36A2D8E3BA'. Cannot insert duplicate key
 in object 'dbo.sessions'. The duplicate key value is (iLc_mW_ZwVJWPGePZdaXSncROoDNh0cD).

I think we need to check before insert a new record, right? Something like this:

MSSQLStore.prototype.set = function(sid, data, callback) {

...

        return request.query('MERGE INTO [' + this.table + '] WITH (HOLDLOCK) s' +
        ' USING (VALUES(@sid, @session)) ns(sid, session) ON (s.sid=ns.sid)' +
        ' WHEN MATCHED THEN UPDATE SET s.session=@session, s.expires=@expires' +
        ' WHEN NOT MATCHED THEN INSERT (sid, session, expires) VALUES (@sid, @session, @expires);', callback);
};

Can't throw error if i use a error password

var mssql_config = {
    user: 'test_dba',
    password: 'xxxxxx',
    server: 'xxx.xxx.xxx.xx', // You can use 'localhost\\instance' to connect to named instance
    database: 'ASPState'
}

var store = new MSSQLStore(mssql_config);

store.on('connect', function(err) {
    console.log(err);
});

store.on('error', function(err) {
    console.log(err);
});

app.use(session({
  store: store,
  secret: 'keyboard cat'
}))

The event error or connect can't output anything.

You should add catch on "this.connection.connect()" like this

this.connection.connect().then((function(_this) {
  return function() {
    if (_this.autoRemove === 'interval') {
      _this.destroyExpired();
      return setInterval(_this.destroyExpired.bind(_this), _this.autoRemoveInterval);
    }
  };
})(this)).catch(this.emit.bind(this, 'error'));

So please fix this bug ~

Hi , I will not be obsessive but could you give me a functional example of "connect-mssql + node-mssql + passport". I would be very grateful and maybe can resolve my problem. Or maybe a good implementation of the 'node-mssql' with passport without the sessions, only the part of the 'strategy'. Something like this:

passport.use('mssql', new LocalStrategy(
        {
            usernameField: 'username',
            passwordField: 'password', 
            passReqToCallback: true
        },
        function(req, username, password, done) {

              //METHOD OF CONNECTION USING NODE-MSSQL 

        } 

    )); // end passport.use(...)

When the users log out, is there any function in connect-mssql to delete their sessions from sessions table or I should implement this function by myself?

My app gets into bad state with below error. any idea ?

{"level":"error","message":"Error: Connection is closed.
at MSSQLStore._ready (D:\\home\\site\\auth\\node_modules\\connect-mssql\\lib\\store.js:79:36)
at MSSQLStore.set (D:\\home\\site\\auth\\node_modules\\connect-mssql\\lib\\store.js:120:21)
at Session.save (D:\\home\\site\\auth\\node_modules\\express-session\\session\\session.js:72:25)
at Session.save (D:\\home\\site\\auth\\node_modules\\express-session\\index.js:381:15)
at ServerResponse.end (D:\\home\\site\\auth\\node_modules\\express-session\\index.js:330:21)
at issued (D:\\home\\site\\auth\\node_modules\\oauth2orize\\lib\\exchange\\password.js:123:11)
at config.generateTokens.then.tokens (D:\\home\\site\\xxx\\xxxx.js:33:13)
at process._tickCallback (internal/process/next_tick.js:103:7)","timestamp":"2017-04-30T16:43:53.724Z"}

Would it be possible to add support for SQL Server 2005 which does not have merge keyword?

I got this error

This is my code:

var express = require('express'),
        session = require('express-session'),
       MSSQLStore=require('connect-mssql'),
       passport = require('passport');

    var configDB = {
                user: 'random',
                password: 'randomerp',
                server: '192.168.20.200',
                database:'9654490MM',
                stream: true,
                connectionTimeout: 60000,
                requestTimeout : 60000,

                pool: {
                    max: 10,
                    min: 0,
                    idleTimeoutMillis: 30000
                },
                options: {
                    tdsVersion: '7_1',
                    encrypt: true // Use this if you're on Windows Azure
                }
            },
      optionsDB= {
                table: 'SESSIONS'
        };

module.exports = function() {
    var app = express();
      app.use(bodyParser.urlencoded({
        extended: true
    }));
    app.use(bodyParser.json());
     app.use(cookieParser());


 //Using MSSQLStore
       app.use(session({
        //secret: config.sessionSecret,
        store: new MSSQLStore(configDB,optionsDB)
    }));

    // use passport session
    app.use(passport.initialize());
       app.use(passport.session());

    // connect flash for flash messages
    app.use(flash());

   ....

   return app;
}