将Google连接到Azure上的Server (DBaaS)

Question

我试图使用将Google连接到Azure (DBaaS)上托管的Server实例。我不断收到一条错误消息，表明我的连接字符串无效，尽管我多次尝试修改代码。我可以使用和本地计算机上的HeidiSQL连接Azure上的Server实例。请注意，我已经列出了白色的everyIPp地址(0.0.0.0到255.255.255)，以确保它不是防火墙问题，阻碍了我的连接。

    //var conn = Jdbc.getConnection('jdbc:sqlserver:MyDBName.database.windows.net:1433/MyDBName', 'MyDBUserName', 'MyDBPassword');

  // SECOND ITERATION OF CONNECTION STRING
  //var conn = Jdbc.getConnection('jdbc:sqlserver://MyDBName.database.windows.net:1433;'+ 'databaseName=MyDBName;user=MyDBUserName;password=MyDBPassword;');

  // THIRD ITERATION OF CONNECTION STRING
  //var conn = Jdbc.getConnection('jdbc:sqlserver://MyDBName.database.windows.net/MyDBName:1433', 'MyDBUserName', 'MyDBPassword');

  // FOURTH ITERATION OF CONNECTION STRING
  //var conn = Jdbc.getConnection('MyDBName.database.windows.net','MyDBUserName', 'MyDBPassword');

  // FIFTH ITERATION OF CONNECTION STRING
  //var conn = Jdbc.getConnection('jdbc:sqlserver://MyDBName.database.windows.net', {user:'MyDBUserName', password:'MyDBPassword'});

  // SIXTH ITERATION OF CONNECTION STRING
  //var conn = Jdbc.getConnection('MyDBName.database.windows.net', {user:'MyDBUserName', password:'MyDBPassword'});  

  // SEVENTH ITERATION OF CONNECTION STRING
  //var conn = Jdbc.getConnection('jdbc:sqlserver://MyDBName.database.windows.net:1433/MyDBName', {user:'MyDBUserName', password:'MyDBPassword'});  

  // EIGHT ITERATION OF CONNECTION STRING
  //https://developers.google.com/apps-script/reference/jdbc/jdbc
  //var conn = Jdbc.getConnection('jdbc:sqlserver://MyDBName.database.windows.net/MyDBName:1433', {user:'MyDBUserName', password:'MyDBPassword'});    

  // NINTH ITERATION OF CONNECTION STRING  - Now I'm just throwing anything at the wall and seeing what sticks!
  //var conn = Jdbc.getConnection('jdbc:sqlserver://MyDBName.database.windows.net/MyDBName;user=MyDBUserName;password=MyDBPassword');

  // TENTH ITERATION OF CONNECTION STRING
  //var conn = Jdbc.getConnection('jdbc:mysql://MyDBName.database.windows.net/MyDBName:1433', {user:'MyDBUserName', password:'MyDBPassword'});    

  // ELEVENTH ITERATION OF CONNECTION STRING
  //var conn = Jdbc.getConnection('jdbc:sqlserver://MyDBName.database.windows.net:1433;databaseName=MyDBName','MyDBUserName','MyDBPassword');

  //TWELVTH
  //var conn = jdbc:sqlserver://MyDBName.database.windows.net;database=MyDBName;user=MyDBUserName;password=MyDBPassword;

  // THIRTEENTH
  //var conn = Jdbc.getConnection('jdbc:sqlserver://MyDBName.database.windows.net;user=MyDBUserName;password=MyDBPassword;databaseName=MyDBName;');

  // FOURTEENTH 
  //var conn = Jdbc.getConnection("jdbc:sqlserver//MyDBName.database.windows.net:1433;databaseName=MyDBName;user=MyDBUserName;password=MyDBPassword");

  // FIFTEENTH
  //var conn = Jdbc.getConnection("jdbc:sqlserver://MyDBName.database.windows.net:1433;databaseName=MyDBName","MyDBUserName","MyDBPassword");

  // SIXTEENTH
  //  http://stackoverflow.com/questions/18978380/error-when-connecting-to-mssql-server-with-google-apps-script-via-jdbc?rq=1
  //var conn = Jdbc.getConnection("jdbc:sqlserver://NumericalIPAddress:1433;" + "databaseName=MyDBName;user=MyDBUserName;password=MyDBPassword;");

  // SEVENTEENTH
  //  same as above with one less semicolon
  //var conn = Jdbc.getConnection("jdbc:sqlserver://NumericalIPAddress:1433;" + "databaseName=MyDBName;user=MyDBUserName;password=MyDBPassword");

  //EIGHTEENTH
  // http://stackoverflow.com/questions/15440939/querying-sql-server-with-google-apps-script-via-jdbc
  var conn = Jdbc.getConnection("jdbc:sqlserver://MyDBName.database.windows.net:1433;databaseName=MyDBName","MyDBUserName","MyDBPassword");

Answer 1

您可以在门户中找到用于数据库的确切连接字符串。单击当前门户(https://manage.windowsazure.com)中的数据库，您将在下面看到一个“连接到数据库”的部分，其中有一个链接，显示“查看ADO .Net、ODBC、.Net和JDBC的数据库连接字符串”。

或者，如果您正在使用Azure门户(https://portal.azure.com)的新版本，您可以通过浏览所有> SQL数据库> MyDBName >显示数据库连接字符串找到连接字符串。

它为我提供的示例如下: jdbc:sqlserver://server21.database.windows.net:1433;database=Test;user=myuser@server21;password={your_password_here};encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30；

Answer 2

我试图连接到Azure数据库，发现Azure门户中指定的JDBC连接字符串和Google 中的示例无法按原样工作。Azure one包含脚本引擎说不支持的几个属性，Google (从MySQL更新)不起作用。我要做的是：

var user = 'USER@SERVER';
var userPwd = 'PASSWORD';
var database = 'DB_NAME'
var connectionString = 'jdbc:sqlserver://SERVER.database.windows.net:1433;databaseName=' + database;

var conn = Jdbc.getConnection(connectionString , user, userPwd);

注意:不支持 "database=“，但支持"databaseName=”。不支持任何加密或证书标记。

此外，您还必须在服务器实例的防火墙规则中添加大量IP范围。如果您使用门户添加这些注意事项，您一次只能对防火墙规则进行一次更改；这意味着添加一个范围、保存、重复。不要添加所有的10，然后在试图保存它们时得到一个错误(就像我第一次做的那样)。请参阅：https://developers.google.com/apps-script/guides/jdbc

Answer 3

关于您的信息，在Google脚本和Azure DBaaS上的Server之间创建ODBC连接的正确连接字符串(截至2018年6月5日)如下：

​

var conn = Jdbc.getConnection('jdbc:sqlserver://SERVER.database.windows.net:1433;databaseName={yourDatabaseName};user={yourUsername}@{yourServer};password={yourPassword}');

​