patriksimek / connect-mssql

SQL Server session store for Connect/Express

connect-mssql

SQL Server session store for Connect/Express based on node-mssql.

Installation

npm install connect-mssql

Prerequisites

Before you can use session store, you must create a table. Recomended table name is sessions but you can change it via options.

CREATE TABLE [dbo].[sessions](
    [sid] [varchar](255) NOT NULL PRIMARY KEY,
    [session] [varchar](max) NOT NULL,
    [expires] [datetime] NOT NULL
)

Usage

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

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
    }
}

app.use(session({
    store: new MSSQLStore(config, options), // options are optional
    secret: 'supersecret'
}));

### Options

• options.table - Table to use as session store. Default: [sessions]
• options.ttl - (Time To Live) Determines the expiration date. Default: 1000 * 60 * 60 * 24 (24 hours)
• options.autoRemove - Determines if expired sessions should be autoremoved or not. If value is interval then a new function, destroyExpired(), will autodelete expired sessions on a set interval. Default: never
• options.autoRemoveInterval - Sets the timer interval for each call to destroyExpired(). Default: 1000 * 60 * 10 (10 min)
• options.autoRemoveCallback - Is the callback function for destroyExpired(). Default: undefined

Advanced usage

var store = new MSSQLStore(config, options);

store.on('connect', function() {
	// ... connection established
});

store.on('error', function() {
	// ... connection error
});

app.use(session({
    store: store
    secret: 'supersecret'
}));

Configuration

To see all options please visit node-mssql docs.

## License

Copyright (c) 2014-2016 Patrik Simek

The MIT License

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

connect-mssql's Issues

Violation of PRIMARY KEY

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);
};

Sessions not actually expiring?

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.

Support for SQL 2005

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

Connection Closed Issue

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?

Cannot read property 'Store' of undefined

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;
}

Connection is closed.

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"}

Connection is closed

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.

Example functional of connect-mssql + node-mssql + passport

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(...)

Upgrade to mssql 4.x

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

Config Documentation unclear

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.

Can't throw error on db connect

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 ~

Strange log files spamming my server home directory for every session-relevant statement

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++)

Delete session from sessions table

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?