CODEWITHSUNDEEP
node.jsazureexpressazure-sql-databasesequelize.js
oikonomiyaki
oikonomiyaki

Reputation: 7951

My Node.js local application won't connect to Azure SQL DB

I am developing a Node.js-Express-EJS web application, with Azure SQL DB (cloud version of MS SQL Server).

I try to test my connection first on the remote Azure SQL DB, using SQL Server Management Studio. First, I set my machine's IP to be allowed in Azure SQL DB firewall rules. Then I was able to get into the database, create and populate table, and query using the Management Studio.

Now, I have the Node.js app locally first. However, it seems it can't connect to the remote Azure SQL DB using same credentials.

First, I use the tutorial in node-mssql:

/* Using node-mssql */
var sql = require('mssql');

router.get('/test/mssql', function(req, res, next) {

    console.log('Testing node-mssql')

    sql.connect("mssql://<username>:<password>@<server>.database.windows.net/<db>")
        .then(function() {

            new sql.Request()
                .query('SELECT * FROM mytable')
                .then(function(recordset) {
                    console.dir(recordset);
                }).catch(function(err) {
                    console.log(err);
                });


        }).catch(function(err) {
            console.log(err);
        });
});

I was only able to get from the console 

'Testing node-mssql'
GET /contact/test/mssql  - - ms - -

But no response, it seems its waiting forever, it didn't even throw error.

Same thing happens when I use Sequelize.js

/* Using Sequelize.js */
var Sequelize = require('sequelize');

var sequelize = new Sequelize('<db>', '<username>', '<password>', {
  host: '<mydb>.database.windows.net',
  dialect: 'mssql',

  pool: {
    max: 5,
    min: 0,
    idle: 10000
  },

});

router.get('/test/sequelize', function(req, res, next) {

    console.log('Testing sequelize');

    sequelize.authenticate()
        .then(function(err) {
            console.log('Connection has been established successfully');
        })
        .catch(function(err) {
            console.log('Unable to connect to the database:', err);
        });
});

Result:

Testing sequelize
GET /test/sequelize - - ms - -

Here are the versions that I am using:

Node.js - 4.6.0
express - 4.14.0
mssql - 3.3.0
sequelize - 3.30.2
tedious - 1.14.0

(I wonder if this has to do with me connected on WiFi? But impossible, bec. Management Studio works fine right?)

Upvotes: 1

Views: 1413

Answers (2)

Aaron Chen
Aaron Chen

Reputation: 9940

If you're trying it out with Azure SQL Database, you'd need to add ?encrypt=true to your connection string that will look like:

mssql://<username>:<password>@<server>.database.windows.net/<db>?encrypt=true

For Sequelize.js, you'd need this: dialectOptions: { encrypt: true }. The code will look like:

var sequelize = new Sequelize('<db>', '<username>', '<password>', {
  host: '<mydb>.database.windows.net',
  dialect: 'mssql',

  // Use this if you're on Windows Azure
  dialectOptions: { 
    encrypt: true 
  },

  pool: {
    max: 5,
    min: 0,
    idle: 10000
  },

});

Upvotes: 2

David Makogon
David Makogon

Reputation: 71033

I think the issue is with your connection string, as that doesn't look like a SQL Database connection string that would work.

For mssql, try connecting like this:

var config = {
 server: "<servername>.database.windows.net",
 database: "<dbname>",
 user: "<username>",
 password: "<password>",
 port: 1433,
 options: {
       encrypt: true
   }
};

sql.connect(config).then(function() { ... } )

FYI for reference, SQL Database (at least through .net) expects a connection string like this, so you might have success with a similar connection string directly passed to sql.connect() (though I haven't tried it):

Server=tcp:[serverName].database.windows.net;Database=myDataBase;
User ID=[LoginForDb]@[serverName];Password=myPassword;Trusted_Connection=False;
Encrypt=True;

Upvotes: 0

Related Questions