Node.js Connect to Local SQL (Trusted User)

Introduction

Connecting to mssql server for developing code using node.js has taken several days to connect. Here is my experience as I have done it.

Background

Using node.js to develop web API while using MS SQL database was what I wanted to do in this project. It sounds easy (and was easy) but not so clear. That is what I thought this may be useful for some other people too.

Using the Code

Starting code is very straight forward, you may find many samples that are all correct, here is the simplest that I started with.

You need one file, usually called app.js, add all code to that:

First, you need install tedious package:

npm install tedious

Then, calling packages and create vars needed:

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

Now, create a config for web:

var config = {
    userName: 'userName', // update me
    password: 'password', // update me
    database: 'db_name', // update me
    server: '???.???.???.???' // update me
} 

If you are going to work with local server and connection, this one is much better:

var config = {
    userName: 'userName', // update me
    password: 'password',  // update me
    server: 'usually pc name', // update me
    database: 'db_name', // update me
    option: {
        instanceName: "MSSQLSERVER", // update me
        database: 'db_name' // update me
    }
}

Here, the difference is that you indicated instance name and also initialized use of database to avoid adding "use db_name" in every request.

Now create connection:

var connection = new Connection(config);

Then we initialize it:

connection.on('connect', function (err) {
    if (err) {
        console.log(err);
    } else {
        executeStatement();
    }
});

Here, we said that when connection is ready to connect to database do commands.

When connection is ready, this function is called:

function executeStatement() {
    request = new Request("USE NodeSql; select * from Code ", function (err, rowCount) {
        if (err) {
            console.log(err);
        } else {
            console.log(rowCount + ' rows');
        }
        connection.close();
    });

    request.on('row', function (columns) {
        columns.forEach(function (column) {
            if (column.value === null) {
                console.log('NULL');
            } else {
                console.log(column.value);
            }
        });
    });

    connection.execSql(request);
}

As you can see, here also we provided a request and when the response is ready, we process it.

Code is finished, that is all.

Run it, it certainly will connect to web based database, but what about local database, it will fail.

Local database usually uses trusted user access. There are many recommendations on how to add trusted user to config and connect without user name and password.

I have tried many of them, and none of them helped.

For Local Database

My recommendation is that you stop struggling more, do these more steps just to add a user to local server and map this newly created user to database you need.

Add a New User to MsSql

Step 1

In mssql server manager, open Object Explorer > Databases > Security > Logins > Right click> New Login...

Add login name, remove enforce password expiration, and leave default database as master, click OK. A new user will be added. This allows node.js to access this server. So it is necessary.

Step 2

Now you have to provide access to your database for new user .

Right click on your database > Security > Schemas> New Schema... then type name and now for Schema owner, click on search button and in search roles and users window, click browse to open Browse for objects. In opened form, find user you created in the first step and mark check box next to it, then click OK, again OK, and the last OK will add this new schema to your database.

Step 3

From left panel, General do.

Now we have to map user to our database, so again right click on database name > Security> Users > New user... to open form. Select SQL user with login from User type menu, then for user name and login name, write user name added in step 1. But for Default schema, click browse(...) button, to open Select Schema form, click browse to open Browse for object and find your user of Step 1 again and bring it back to main form.

From Left panel, select Owned Schema and check box next to your schema.

From Left panel, select Membership and check box next to db_owner.

Now click OK. This user must be added to your database.

Finished.

History

I hope this really help others who wish to use Ms SQL while using node.js. It really was hard to find out.

I hope that this problem to be solved in the next version.