Can't connect to SQL Azure Database with sequelize, but SQL Server on localhost works fine

Question

I have deployed a few sites to Heroku with MongoDB, but this is the first time I've made a site with SQL and tried to deploy to Azure, so I'm probably missing something obvious.

I have been developing a website on my dev machine using Node.js, a SQL Server Database, and Sequelize as the ORM. Everything works fine, but when I tried to deploy to Azure with a connection string I can't connect with the SQL Azure database. I can use SQL Server Management Studio to connect with the empty database on Azure, so I'm sure my connection info is correct.

When I tried to deploy to Azure, I tried with the connection string that Azure provides:

var Sql = require('sequelize');
var sql = new Sql('Driver={SQL Server Native Client 11.0};Server=tcp:server.database.windows.net,1433;Database=databasename;Uid=UserName@server;Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;');

When I try to connect with this string, the error I get is:

C:\Users\username\Documents\GitHub\event-site\node_modules\sequelize\lib\sequelize.js:110
    options.dialect = urlParts.protocol.replace(/:$/, '');
                                       ^

TypeError: Cannot read property 'replace' of null
    at new Sequelize (C:\Users\v-mibowe\Documents\GitHub\event-site\node_modules\sequelize\lib\sequelize.js:110:40)
    at Object.<anonymous> (C:\Users\v-mibowe\Documents\GitHub\event-site\routes\db-routes.js:68:11)
    at Module._compile (module.js:435:26)
    at Object.Module._extensions..js (module.js:442:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:311:12)
    at Module.require (module.js:366:17)
    at require (module.js:385:17)
    at Object.<anonymous> (C:\Users\v-mibowe\Documents\GitHub\event-site\server.js:16:1)
    at Module._compile (module.js:435:26)
    at Object.Module._extensions..js (module.js:442:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:311:12)
    at Function.Module.runMain (module.js:467:10)
    at startup (node.js:136:18)
    at node.js:963:3

db-routes.js:68:11 is the connection string to the db.

When I try to configure my connection with the following, the server no longer crashes or gives an error, but none of the content that should be created by the code in the schema is created. That code looks like this:

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
  }
});

My original connection to my localhost (which works fine) looks like this:

var Sql = require('sequelize');
var sql = new Sql('dbname', 'username', 'password', {
  host: 'localhost',
  dialect: 'mssql',

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

Thanks in advance for all the help!

If I understood correctly, your application is running on Heroku and your database in Azure, right? Did you check firewall? — Bruno Faria
– Bruno Faria, Commented Dec 18, 2015 at 18:50
@BrunoFaria Thanks for getting back to me. Nothing is on Heroku, the entire App is running on Azure. I only mentioned Heroku , because I haven't deployed on Azure before and I may be trying to do things in a "Heroku" kind of way when it should be done differently in Azure. — CascadiaJS
– CascadiaJS, Commented Dec 18, 2015 at 20:53
Is SQL Azure firewall checked for Azure Services? Otherwise, you have to manually add the frontend ip. — Bruno Faria
– Bruno Faria, Commented Dec 18, 2015 at 21:54
Hi, I have a very similar situation to yours and the solution for me was to add "@hostname" after the username, like so: const { Sequelize } = require ('sequelize'); const db = new Sequelize("myDBName", "[email protected]", "myPassword", { host: 'mySqlinstance.mysql.database.azure.com', dialect: 'mysql', }); hope it will help — Claudio M
– Claudio M, Commented Jul 14, 2022 at 14:36

user5701745 · Accepted Answer · 2015-12-21 05:10:10Z

11

To connect to Azure Sql server with ran tedious, we need to set additional option: encrypt: true in connection factory. In Sequelize, we can specify dialectOption in initializing function:

var Sequelize = require('sequelize');
var sequelize = new Sequelize('dbname', 'username', 'passwd', {
  host: 'hostname',
  dialect: 'mssql',
  pool: {
    max: 5,
    min: 0,
    idle: 10000
  },
  dialectOptions: {
    encrypt: true
  }
});

you can refer the similar issue in Sequelize's issues repo on GitHub

answered Dec 21, 2015 at 5:10

user5701745

Sign up to request clarification or add additional context in comments.

5 Comments

CascadiaJS Over a year ago

Thanks for the help Gary Liu - MSFT, I think I'm almost there. With the answer you gave, I'm able to connect to the online azure database with my connection string, but when I deploy to azure I still get the error: "The page cannot be displayed because an internal server error has occurred."

user5701745 Over a year ago

Usually, the code error or deployment error will raise the 500 error. If your application will run well without sequelize, it may be a code issue, you can check whether the node.js modules dependencies have been uploaded with your app, you can sign in KUDU console cmdlet of your site,which url should be https://<your_site_name>.scm.azurewebsites.net/DebugConsole. and about Node.js modules with Azure applications, you can refer to azure.microsoft.com/en-us/documentation/articles/…

user5701745 Over a year ago

additionally, you can enable diagnostic logs of your site, refer to azure.microsoft.com/en-us/documentation/articles/… for more. And you can download the system file tier logs via KUDU api https://<your_site_name>.scm.azurewebsites.net/api/dump

CascadiaJS Over a year ago

Thanks so much for your help Gary Liu - MSFT . It was an separate problem from connecting to the DB, but your links to the logs let me figure out the problem. My node modules were causing file names that exceeded 260 characters. In order to fix the problem I used a module from npm called flatten-packages which flattens out the deeply nested folders in node_modules and once I ran that I was able to push to Azure successfully!

user5701745 Over a year ago

Happy to hear that, congratulations.

ZeeMoussa · Accepted Answer · 2015-12-19 12:37:58Z

1

You need to Enable the SQL Azure Firewall to add Azure Services to it. in not your App will not be able to communicate with SQL Azure,

answered Dec 19, 2015 at 12:37

ZeeMoussa

18310 bronze badges

Collectives™ on Stack Overflow

Can't connect to SQL Azure Database with sequelize, but SQL Server on localhost works fine

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related