Clever Geek Handbook

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

I am trying to connect a Google Sheet to an Azure-hosted SQL Server (DBaaS) instance using a Google Script app. I keep getting an error stating that my connection string is not valid despite numerous attempts to change the code. I can connect to this SQL Server instance on Azure using both Microsoft SQL Server Management Studio and HeidiSQL from my local machine. Please note that I have a whitelist of allIPP addresses (0.0.0 to 255.255.255.255) to make sure it is not 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");
+3


source to share


2 answers


You can find the exact connection string you should use for your database in the portal. Click on the database on the current portal ( https://manage.windowsazure.com ) and you will 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 newer version of the Azure portal ( https://portal.azure.com ), you can find the connection strings through Browse All> SQL Databases> MyDBName> Show Database Connection Strings.



The example he gives me looks like this: JDBC: SQLServer: //server21.database.windows.net :. 1433; database = Test, user = MyUser @ server21, password = {your_password_here}, encrypt = true; hostNameInCertificate = * database.windows.net; loginTimeout = 30;

+3


source


I am trying to connect to an Azure database and found that the JDBC connection string provided in the Azure portal and the sample from Google does not work as is. Azure contains several properties that the server script says is not supported, and Google one (updated from MySQL) does not work. This is what I have to work with:

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 encryption and certificate tags are supported.

In addition, there is a large set of IP ranges that you must add to the firewall rules on your server instance. If you use the portal to add these notes, you can only make one change to the firewall rules at a time; it means adding a range, saving, repeating. 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

+1


source






More articles:


All Articles