Having trouble with node-sqlserver in node.js

Question

I am having trouble connecting to a database on a computer on my network using node-sqlserver by Microsoft in node.js. I am using sql server 2008 on a windows 2008 server. I am remotely running my node.js on different machine within the same domain. I think the issue has to do with the connection string.

var sql = require('node-sqlserver');
var conn_str = 'Driver={SQL Server Native Client 10.0};Server=(win08-srv);Database=DB;Trusted_Connection={Yes}';

Also, there should be a user name and password to get into the Database, however this wasn't in the example connection string from the node module.

//open the DB connection
sql.open(conn_str, function (err, conn) {
    if (err) {
        console.log("Error opening the connection!");
    return;
    } else {
        console.log('Connection successful!');
        return;
    }
}); 

Always results in printing Error opening the connection! when run.

Answer 1

I had the same problem. The first thing you need to do in order to diagnose the problem is change the sample code as bellow:

sql.open(conn_str, function (err, conn) {
    if (err) {
        console.log("Error opening the connection! Error was: " + err);
    return;
}

Once I did the above, I got a bit more information on the error. My error was:

"Error opening the connection! Error: IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

This basically means you've specified the wrong driver in the connection string. I solved my issue by changing the driver version from 11.0 to 10.0. You can see what drivers are installed locally (if you're on windows, which I assume you are) by going to:

Control Panel > Administrative Tools > Data Sources (ODBC) > Drivers(tab).

Look for the SQL Server Native Client and see the version number. If you don't have this driver installed, you'll need to download it.