无法使用sequelize连接到SQL Azure数据库，但localhost上的SQL Server工作正常

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.

我已经使用MongoDB为Heroku部署了一些站点，但这是我第一次使用SQL创建一个站点并尝试部署到Azure，所以我可能会遗漏一些明显的东西。

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.

我一直在使用Node.js，一个SQL Server数据库和Sequelize作为ORM在我的开发机器上开发一个网站。一切正常，但当我尝试使用连接字符串部署到Azure时，我无法连接SQL Azure数据库。我可以使用SQL Server Management Studio连接Azure上的空数据库，所以我确定我的连接信息是正确的。

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

当我尝试部署到Azure时，我尝试使用Azure提供的连接字符串：

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.

db-routes.js：68：11是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:

我与localhost的原始连接（工作正常）如下所示：

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!

在此先感谢您的帮助！

2 个解决方案

#1

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:

要使用繁琐的连接到Azure Sql服务器，我们需要在连接工厂中设置其他选项：encrypt：true。在Sequelize中，我们可以在初始化函数中指定dialectOption：

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

你可以在GitHub上参考Sequelize的问题回购中的类似问题

#2

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,

您需要启用SQL Azure防火墙以向其添加Azure服务。在你的应用程序将无法与SQL Azure通信，

#1