How do I connect to my Azure SQL Database using JDBC and Google Apps Script-CodePudding

I am currently looking to create a pipeline that pushes data from a google sheet to an Azure Database table whenever the sheet gets edited.

Currently I am testing an Apps Script I am writing to connect to the database that I have set up to store the information however I keep getting errors, and am not sure what the issue in my connection string could be as the username and password are both correct.

I also have every ipv4 range allowed on the allow list in my firewall settings.

I have currently been working off of the example that Google provided, and this is the code that is currently giving me the error:

Failed with an error Failed to establish a database connection. Check connection string, username and password.

var conn = Jdbc.getConnection('jdbc:sqlserver://{server}.database.windows.net:1433;databaseName={database};user={username}@{server};password={password};');

CodePudding user response：

I took a look at the SQL Database instance in my Azure subscription and the JDBC connection string is like this,

jdbc:sqlserver://{server}.database.windows.net:1433;
database={dbname};
user={username}@{server};
password={your_password_here};
encrypt=true;trustServerCertificate=false;
hostNameInCertificate=*.database.windows.net;loginTimeout=30;

I think you are missing the last few options. Can you try this and see if it works?

CodePudding user response：

I created azure SQL database in azure portal and I added client Ip address to the server:

I try to connect with below connection string:

jdbc:sqlserver://<serverName>.database.windows.net:1433;database=<DatabaseName>;user=<userName>@<serberName>;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Image for reference:

enter image description here

I got below error:

enter image description here

I tried with below code:

function  AcessaVendas () {
      var  URL='jdbc:sqlserver://<serverName>.database.windows.net:1433'
      var  USER= '<userName>'
      var  PASS= '<password>'  
      var  conn = Jdbc.getConnection(URL, USER, PASS);

}

I also try with clow code:

function  myFunction() {

var  conn = Jdbc.getConnection('jdbc:sqlserver://<serverName>.database.windows.net:1433;databaseName=<databaseName>;user=<userName>@<serverName>;password=<password>;');

}

First I got below error:

"Failed to establish a database connection. Check connection string, username and password."

I added fire wall rule with below Ip address:

"64.18.0.0 - 255.255.255.255"

enter image description here

After that My azure sql database is connected successfully. Image for reference:

enter image description here