Connecting SQL Server with Node.js

Now a days node.js is gaining popularity. Many applications are getting migrated to node.js from different technology stacks. We will discuss about connecting SQL server with node.js.

Let us consider we have one asp.net application and we are trying to migrate the application to node.js on the server side and Angular on the client side. In most of the ASP.Net applications, the connection to SQL server is done with a service account. A service account is nothing but a windows account. Typically IIS runs with the same account. Some applications also use sql server authentication while connecting to database.

Anyways, there are two approaches while connecting to SQL Server.

a) Windows Authentication:

var sql = require("mssql/msnodesqlv8"); 

//Initiallising connection string
var dbConfig = {    
    driver: 'msnodesqlv8',
    connectionString:'Driver={SQL Server Native Client 11.0};Server={localhost\\SQLExpress};Database={studentdb};Trusted_Connection={yes};'
};


var getStudents = function(res){
    sql.connect(dbConfig, function(err){
        if(err){
            console.log("Error while connecting database :- " + err);
            res.send(err);
            sql.close();
        }
        else {                       
            var request = new sql.Request();          
            request.execute('USP_GetStudents',function(err, data){
                if(err){
                    console.log("Error while connecting database :- " + err);
                    res.send(err);
                }
                else{
                    res.send(data.recordset);
                }
                sql.close();    
            });            
        }
    });
}

 

b) SQL Server Authentication:

var sql = require("mssql"); 

//Initiallising connection string
var dbConfig = {
    user: 'xxx',
    password: 'xxx',
    server: 'localhost\\SQLExpress', // You can use 'localhost\\instance' to connect to named instance
    database: 'studentdb',
 
    options: {
        encrypt: false // Use this if you're on Windows Azure
    }
}


var getStudents = function(res){
    sql.connect(dbConfig, function(err){
        if(err){
            console.log("Error while connecting database :- " + err);
            res.send(err);
            sql.close();
        }
        else {                       
            var request = new sql.Request();          
            request.execute('USP_GetStudents',function(err, data){
                if(err){
                    console.log("Error while connecting database :- " + err);
                    res.send(err);
                }
                else{
                    res.send(data.recordset);
                }
                sql.close();    
            });            
        }
    });
}

The only difference between the two approaches is on the connection string settings. However, I prefer windows authentication over sql server authentication because security can be delegated to windows. And also, here the risk of handling username and password is by passed.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.