Connect to MS SQL using Node.js and SequelizeJS

Today I tried using Node.js together with SequelizeJS to connect to a named MS SQL instance. SequelizeJS is an ORM providing abstractions to work with databases like PostgreSQL, MySQL, MS SQL and more. While the documentation is really good, it is missing the part for named MS SQL instances. If you take a look at documentation for setting up a connection, it reads, that you just have to specify the mssql  dialect. But – bummer – that didn’t work for me. After reading some code – that’s the true power of Open Source! – I found a line of code which looks promising. To configure the named instance, you have to use the property dialectOptions  together with – tada! – the instanceName  property:

sequelize = new Sequelize(database, username, password, {  
    host: host,
    dialect: 'mssql',
    dialectOptions: {
        instanceName: 'MSSQLSERVER2014'
    }
});

With a thrill of anticipation I restarted my server to get … a connection error. Oh my! Behind the scenes SequelizeJS uses Tedious. Tedious implements Microsoft’s Tabular Data Stream (TDS) protocol to speak with various versions of MS SQL Server as well as Azure SQL Database (kudos to the contributors! You will not implement this protocol within a few days.). Reading through the docs of Tedious, I found this little part:

options.instanceName  The instance name to connect to. The SQL Server Browser service must be running on the database server, and UDP port 1444 on the database server must be reachable.

SQL Server Browser is disabled by default (at least, if you almost click Yes during the SQL Server setup process :-)). To enable the SQL Server Browser, you have to start the Sql Server Configuration Manager, which can be done by either typing that into search or by executing SQLServerManager12.msc  via Windows+R (Run). Don’t forget: 12 is SQL Server 2014, 11 is SQL Server 2012. That brings up this nice little dialog:

SQL Server Configuration Manager

On the left side select SQL Server Services and then on the right side SQL Server Browser. Since this service is disabled, you have to right click it, select Properties, change the tab to Service and then change the Start Mode to Automatic. After that you can start the service (by right click and Start).

Last but not least, we need to enable the TCP/IP protocol for our instance. To do that, expand SQL Server Network Configuration on the left side and select Protocols for (in my case: MSSQLSERVER2014). On the right side right click TCP/IP and click Enable. That’s it! SequelizeJS can now connect to our server and do its ORM magic.

Happy sqlizing!