Unable to connect to Azure SQL database with further processing, but SQL Server on localhost works fine

Question

I deployed several sites to Heroku with MongoDB, but this is the first time I created a site with SQL and tried to install it on Azure, so I probably missed something obvious.

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

When I tried to deploy to Azure, I tried to use 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 to this line, an error occurs:

 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 db connection string.

When I try to configure my connection with the following, the server no longer crashes or does not give an error, but not a single content that should be created by the code in the scheme is created. This code is as follows:

 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 initial connection to my localhost (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 your help!

Answer 1

To connect to the Azure Sql server running tedious, we need to set an additional option: encrypt: true in the factory connection. In Sequelize, we can specify dialectOption in the initialization 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 reference a similar issue in the Sequelize issues repo on GitHub

Answer 2

You need to enable Azure SQL Firewall to add Azure Services to it. in not your application will not be able to communicate with SQL Azure,