Connecting Google App Script to SQL Server on Azure (DBaaS)

Question

I'm trying to connect a Google Sheet to an instance of SQL Server hosted on Azure (DBaaS) using Google App Script. I keep getting an error message indicating that my connection string is invalid despite my numerous attempts at modifying the code. I can connect to this instance of SQL Server on Azure using both Microsoft SQL Server Management Studio and HeidiSQL from my local machine. Note that I have white listed everyIPp address (0.0.0.0 to 255.255.255.255) to make sure that it wasn't a firewall issue preventing me from connecting.

    //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

You can find the exact connection string you should use for your database in the portal. Click on the database in the current portal (https://manage.windowsazure.com) and you should see a section that says "Connect to your database" below that there is a link that says "View SQL Database connection strings for ADO .Net, ODBC, PHP, and JDBC.

Alternatively, if you are using the new version of the Azure portal (https://portal.azure.com), you can find the connection strings via Browse All > SQL databases > MyDBName > Show database connection strings.

The example it provides for me looks like: 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

For your information, the right connection string that works(as at June 5th 2018) to create an ODBC connection between Google Apps Script and a SQL Server DB on Azure DBaaS is as follows:

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

Answer 3

I'm trying to connect to an Azure database and found that the JDBC connection string specified in the Azure portal and the sample from Google do not work as-is. The Azure one contains several properties that the script engine says are not supported and the Google one (updated from MySQL) doesn't work. Here's what I got to work:

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

NOTE: "database=" is not supported but "databaseName=" is supported. None of the encrypt or certificate tags are supported.

Also, there is a big set of IP ranges you must add to the firewall rules on your server instance. If you use the portal to add these note that you can only make one change to the firewall rules at a time; this means add a range, save, repeat. Don't add all 10 and then get an error when trying to save them (like I did the first time). see: https://developers.google.com/apps-script/guides/jdbc