CODEWITHSUNDEEP
sql-serverdatabasejdbcgoogle-apps-scriptdatabase-connection
Andrew
Andrew

Reputation: 43

Connection to a MS SQL database from Google scripts

I'm trying to connect to a MS SQL database (2012) using google script using the JDBC service https://developers.google.com/apps-script/guides/jdbc

I'm having issues connecting and keep getting the message 'Failed to establish a database connection. Check connection string, username and password. (line 4, file "Code")'

I have setup the port and followed the instructions from the website above. Here is the statement below that I am using:

var conn = Jdbc.getConnection("jdbc:sqlserver://<ip>:<port>;databaseName=<db name>;user=<user>;password=<password>");

Does anyone see anything wrong. I keep reading about needing the JDBC driver installed on the MS SQL server, do I need to call it in the script somewhow?

Upvotes: 2

Views: 4223

Answers (3)

terpimost
terpimost

Reputation: 91

Google Scripts and Azure SQL Server worked for me with this:

var conn = Jdbc.getConnection('jdbc:sqlserver://your-server.database.windows.net:1433;databaseName=your-db-name;user=username@your-catalog;password=yourpassword;'); Logger.log(conn);

So you got to take JSBC connection string from Azure and replace portion of that string: instead of: ...1433;database=... in Google Script you should have: ...1433;databaseName=

your-catalog for me is the same as your-server.database.windows.net

Upvotes: 0

Navrattan Yadav
Navrattan Yadav

Reputation: 2113

i got same error but now resolved after doing following things :

var conn = Jdbc.getConnection("jdbc:sqlserver://hostname:1433;databaseName={dbname};user={user@hostname};password={passowrd@hostname};");

and whit-list following ip on firewall :

64.18.0.0 - 64.18.15.255 64.233.160.0 - 64.233.191.255 66.102.0.0 - 66.102.15.255 66.249.80.0 - 66.249.95.255 72.14.192.0 - 72.14.255.255 74.125.0.0 - 74.125.255.255 173.194.0.0 - 173.194.255.255 207.126.144.0 - 207.126.159.255 209.85.128.0 - 209.85.255.255 216.239.32.0 - 216.239.63.255

Upvotes: 1

Andrew
Andrew

Reputation: 43

I figured it out. Didn't need a driver. What I had above was correct. On the MS SQL server, I had the sql username and password set as 'Change on next Logon'. so I unchecked this. Make sure your firewall and port settings are correct.

Check the Event log on the server. This is how I found out that it was connecting and it was a username/password issue.

Upvotes: 1

Related Questions