Connection error when trying to connect to Cloud-sql sql-server from cloud-functions

Question

I am using firebase functions to connect to cloud-sql (Micorsoft SQL Server). I tried the following to connect to SQL Server via public ip:

/*Tedious Start*/
var TediousConnection = require('tedious').Connection;
var TediousRequest = require('tedious').Request;
var TediousTypes = require('tedious').TYPES;
const config = {
  user: 'admin',
  password: 'password',
  server: '12.345.5.678',
  database: 'mma',
  //host: 'myproject:us-central1:my-db'
};
app.get('/mssql/', async(req, res) => {
  var sql = require('mssql');
  sql
    .connect(config)
    .then(function() {
      //6.
      var request = new sql.Request();
      //7.
      request
        .query('SET NOCOUNT ON;select * from student')
        .then(function(recordset) {
          // send records as a response
          res.send(recordset.recordset);
          sql.close();
        })
        .catch(function(err) {
          //8.
          console.log(err);
          sql.close();
        });
      res.end();
    })
    .catch(function(err) {
      //9.
      console.log(err);
    });
});

I get the following error:

{ ConnectionError: Failed to connect to 12.345.5.678:1433 in 15000ms
    at Connection.tedious.once.err (/srv/node_modules/mssql/lib/tedious/connection-pool.js:68:17)
    at Object.onceWrapper (events.js:315:30)
    at emitOne (events.js:116:13)
    at Connection.emit (events.js:211:7)
    at Connection.connectTimeout (/srv/node_modules/mssql/node_modules/tedious/lib/connection.js:1195:10)
    at Timeout.connectTimer.setTimeout [as _onTimeout] (/srv/node_modules/mssql/node_modules/tedious/lib/connection.js:1157:12)
    at ontimeout (timers.js:498:11)
    at tryOnTimeout (timers.js:323:5)
    at Timer.listOnTimeout (timers.js:290:5)
  code: 'ETIMEOUT',
  originalError: 
   { ConnectionError: Failed to connect to 12.345.5.678:1433 in 15000ms
    at ConnectionError (/srv/node_modules/mssql/node_modules/tedious/lib/errors.js:13:12)
    at Connection.connectTimeout (/srv/node_modules/mssql/node_modules/tedious/lib/connection.js:1195:54)
    at Timeout.connectTimer.setTimeout [as _onTimeout] (/srv/node_modules/mssql/node_modules/tedious/lib/connection.js:1157:12)
    at ontimeout (timers.js:498:11)
    at tryOnTimeout (timers.js:323:5)
    at Timer.listOnTimeout (timers.js:290:5)
     message: 'Failed to connect to 12.345.5.678:1433 in 15000ms',
     code: 'ETIMEOUT' },
  name: 'ConnectionError' }

I think the cloudfunction is unable to reach the sql server. I also tried to add the service account to have the cloudsql permissions.

I thought I was doing something wrong with my cloud functions and tried cloudsql-postgresql from cloud funtions. I am able to connect successfully to ClouSQL-postgresql from cloud function.

For e.g in case of cloudsql-postgresql, the host takes a value similar to the following using unix domain socket :

config.host = `/cloudsql/${process.env.CLOUD_SQL_CONNECTION_NAME}`;

In case of Cloudsql-mssql, I am providing the ipaddress for server. I do not know what I am missing. Is there a firewall issue I need to take care of?

Chris32 Chris32 · Accepted Answer · 2020-05-27T18:38:05

As per the documentation:

Cloud Functions does not support connecting to Cloud SQL for SQL Server over public IP. Use private IP instead

instead you should connect directly using the private IP address and port 1433 for your instance.

Connection error when trying to connect to Cloud-sql sql-server from cloud-functions

1 Answers