Node JS Sequelize cannot connect to Azure SQL Database

Question

I'm currently trying to connect a Node JS app to a single database that I created using the Azure SQL Database. In order to connect to the database, I use Sequelize. In order to do that, I set up the firewall to accept my IP address as explained here, and I configured a config.json file like so

"username": "SERVER_ADMIN_NAME@MY_IP_ADDRESS",
"password": "ADMIN_PASSWORD",
"database": "DATABASE_NAME",
"host": "SERVER_NAME",
"port": 1433,
"dialect": "mssql",
"dialectOptions": {
   "options": {
        "encrypt": true
    }
 }

However, after running the application it fails to connect to the database and returns the following message

"Cannot open server '211' requested by the login. Client with IP address 'MY_IP_ADDRESS' is not allowed to access the server.  To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect."

I've already waited for more than 5 minutes but the result is still the same. Now, the first thing that came into my mind was how I provided the values for the config.json file. However, after checking the sys.database_firewall_rules using the following query

SELECT * FROM sys.database_firewall_rules;

The table was EMPTY. From here on I'm not really sure what I'm supposed to do. I was wondering if anybody could point out what I was missing? Thanks in advance!

Alberto Morillo · Answer 1 · 2020-04-26T02:21:34.493

You should not connect to Azure SQL Database using the IP address because it can change any time.

Could you try a connection like below using tedious driver?

var Sql = require('sequelize');
var sql = new Sql('dbname', 'UserName@server', 'password', {
  host: 'server.database.windows.net',
  dialect: 'mssql',
  driver: 'tedious',
  options: {
    encrypt: true,
    database: 'dbname'
  },
  port: 1433,
  pool: {
    max: 5,
    min: 0,
    idle: 10000
  }
});

Make sure you are adding your public IP address not your local IP address to the firewall rules. To verify the firewall rules you have added already, please run the following query:

SELECT * FROM sys.firewall_rules;

The above query shows rules at the server level. You have created your rules at that level.

Node JS Sequelize cannot connect to Azure SQL Database

1 Answers1