Google Cloud - Connect Sql Server to Google Apps Script

Question

I have problems to connect sql server on google cloud to google apps script, have tried many options do url connection like: Jdbc.getCloudSqlConnection("jdbc:google:mysql://apis-para-pap:southamerica-east1:revistamarcasserver","sqlserver", "*****"); but is not connecting, Exception: Failed to establish a database connection. Check connection string. Do you can help me to solve this problem to connect Sql Server to Google Apps Script?

Information about google cloud Sql Server:

DB Type: SQL Server 2017 Standard
Location: southamerica-east1-b
Instance name: apis-para-pap:southamerica-east1:revistamarcasserver
Public address: 34.95.157.142
White list: (72.14.192.0/18) (64.233.160.0/19) (209.85.128.0/17) (66.102.0.0/20) (74.125.0.0/16) (173.194.0.0/16) (66.249.80.0/20) (64.18.0.0/20) (216.239.32.0/19) (207.126.144.0/20)

(observation: Using sql server management studio, i have tested and connected successfully, with this informations).

Thank you so much

Are you passing user credentials to access the database? As in Jdbc.getCloudSqlConnection(connectionString, username, password). Be sure you do not use the 'root' user and password to access the database. Create a dedicated user in Google Cloud console. Check the connection string for your mysql instance. Append '/mydatabasename' to the connection string. Also, not connecting could be a thousand different things. Please post all error messages and your code (without personal information). — Anton Dementiev
Hello, thanks for your help Anton Dementiev this is very important to me. Yes, I'm putting the credentials as an example: Jdbc.getCloudSqlConnection ("jdbc: google: mysql: // apis-to-pap: southamerica-east1: magazinemarkederver", "sqlserver", "*****"); "Error: Failed to establish a connection to the database. Check connection string, username and password." A part of the login password is correct as I have already tested it in sql server management studio. — Martentes Adm
Again, try connecting not as a root user. Also, what database are you connecting to? Please append it to the end of your string. I had the same issue with my MySQL Google database that was resolved by creating another user and connecting directly to the DB. You can create a database on your instance via Google cloud shell or console UI — Anton Dementiev
Follow this example and put in the DB name github.com/gsuitedevs/apps-script-samples/blob/master/service/… — Anton Dementiev
Thanks for repply Anton Dementiev, I have followed the tutorial that you shared, created a new user, database, and append into the url string connection: Jdbc.getCloudSqlConnection("jdbc:google:mysql://apis-para-pap:southamerica-east1:revistamarcasserver/SGBDMARCAS","adm", "*****"); But the same error have ocurred. Maybe is an bug for connecting cloud sql server into google apps script? — Martentes Adm

marian.vladoi marian.vladoi · Accepted Answer · 2019-12-04T11:18:28

I created a Cloud SQL instance, authorize the following IP ranges and was able to connect to it using the Public Ip Address. I could not connect using Instance connection name.


var db = 'mydatabase';
var instanceUrl = "jdbc:mysql://Public_IP_address_SQL";
var dbUrl = instanceUrl + '/' + db;

/**
* Create a new database within a Cloud SQL instance.

*/
function createDatabase() {
var conn = Jdbc.getConnection(instanceUrl,{user: 'root', password: '****'} );
conn.createStatement().execute('CREATE DATABASE ' + db);
}

/**
* Create a new user for your database with full privileges.
*/
function createUser() {
var conn = Jdbc.getConnection(dbUrl,{user: 'root', password: '****'});

var stmt = conn.prepareStatement('CREATE USER ? IDENTIFIED BY ?');
stmt.setString(1, user);
stmt.setString(2, userPwd);
stmt.execute();

conn.createStatement().execute('GRANT ALL ON `%`.* TO ' + user);
}

/**
* Create a new table in the database.
*/
function createTable() {
var conn = Jdbc.getConnection(dbUrl, {user: 'new_user', password: '****'});
conn.createStatement().execute('CREATE TABLE entries '
    + '(guestName VARCHAR(255), content VARCHAR(255), '
    + 'entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));');
}


createDatabase()
createUser() 
createTable()

}

Google Cloud - Connect Sql Server to Google Apps Script

1 Answers