CODEWITHSUNDEEP
sql-servernode.jsazureazure-sql-databasesequelize.js
CascadiaJS
CascadiaJS

Reputation: 2515

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

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!

Upvotes: 10

Views: 9167

Answers (2)

user5701745
user5701745

Reputation:

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

Upvotes: 11

ZeeMoussa
ZeeMoussa

Reputation: 183

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,

Upvotes: 1

Related Questions