none
Google Sheets to Azure database connection error (Jdbc) RRS feed

  • Question

  • Hi,

    I am using the following code in google sheets to connect to an azure database, but the connection errors and doesn't establish.

    function onOpen() {
      
      var user = '';
      var password = '';
      var db = '';
      var servername = '';
    
      var userString = user + '@' + servername
      var connectionString = 'jdbc:sqlserver://' + servername + '.database.windows.net:1433;databaseName=' + db
      var conn = Jdbc.getConnection(connectionString,userString,password);
    }

    Error message is Failed to establish a database connection. Check connection string, username and password. (line 11, file "Code")

    I did originally use the suggested azure connection string but got the following error 

    The following connection properties are unsupported: trustServerCertificate,hostNameInCertificate,database,encrypt,loginTimeout. (line 13, file "Code")

    credentials tested on SSMS, removed here for security.

    Thanks


    Monday, December 2, 2019 11:49 PM

Answers

  • I managed to solve this by adding the google IP Addresses to the Azure firewall.  So the code is:

    function onOpen() {
      
      var user = '';
      var password = '';
      var db = '';
      var servername = '';
    
      var userString = user + '@' + servername
      var connectionString = 'jdbc:sqlserver://' + servername + '.database.windows.net:1433;databaseName=' + db
      var conn = Jdbc.getConnection(connectionString,userString,password);
    
    }

    IP Addresses:

    https://developers.google.com/apps-script/guides/jdbc

    How to add the addresses, in summary, log into Azure, click on your database and at the top there is a "Set server firewall" then under rules add the start and end ip's.  The names don't mean anything, I just used numbers 1-10.  And that's it, your google sheets should connect.

    Tuesday, December 3, 2019 9:49 PM

All replies