Cannot connect to Cloud SQL SQL Server from Cloud Functions using private IP

Question

I have a Cloud Function that I want to connect to a SQL Server Instance. By documentation, you can only connect using a private IP.

Everytime I try to connect I get the error:

ERROR: (gcloud.functions.call) ResponseError: status=[400], code=[Bad Request], message= [Function failed on loading user code. Error message: {"code":"ELOGIN","originalError": {"message":"Logon failed for login 'sqlserver' due to trigger execution.","code":"ELOGIN"},"name":"ConnectionError"}]

My Cloud Function code:

const sql = require('mssql');

exports.test = (req, res) => {
  
  const config = {
                user: 'sqlserver',
                password: 'test',
                server: '10.60.80.3',
                port:1433,
                pool: {
                    max: 10,
                    min: 0,
                    idleTimeoutMillis: 30000
                }
            };
            
  const pool = new sql.ConnectionPool(config);
  pool.connect()
            .then(() => {
                res.status(200).send({message: "Connection ready."});
                })
            .catch(err => {
                res.status(500).send(err);
                pool.close();
            });
};

I have done the following configurations:

VPC Connector on default network

The default network was applied to Private IP configuration of SQL Server instance.

SQL Server Instance configuration

When creating the Cloud Function, you are required to select a service account and a VPC connector. I chose the connect-ip-sql connector. For service account, I tried with App Engine Default Service Account and Compute Engine Service Account ( both given the role of Cloud SQL client). Same error.

It seems to be a problem with SQL Server login and not about finding the network since I tried changing the network (setting the SQL Server in another VPC) and it returned CONNECTION TIMEOUT.

I can login using public ip in my SSMS using the default username-password. When I run the query:

select * from sys.server_triggers

There are three server triggers but there is no information what they do.

     gcloudsql_RoleManagement
     TRG_ProtectDropCustRootLogin
     TRG_DisableRemoteConnectionForDbRoot

You cannot drop or change them, because Cloud SQL is a managed service and you have no access on the 'sa' superuser.

Still open!! I had this bug when I tested this in Alpha, early this year (in January I think). I opened a case to the support and... I see that the problem is still here. There are issues open on the public tracker also. AFAIK, Cloud Run don't have this issue. Have a try with it (you can reuse the same connector, you simply have to package your function in a webserver) — guillaume blaquiere
– guillaume blaquiere, Commented Oct 14, 2020 at 12:58
I have tried also from App Engine. Same error. I tried from the VM instance and it connected successfully. However from Cloud Functions, App Engine still a problem. The docs say it is possible to connect.. — josh13
– josh13, Commented Oct 14, 2020 at 13:06

Frank · Accepted Answer · 2020-10-14 23:06:17Z

0

It would be best to request Google Cloud Support assistance (public tracker is mostly for bugs) since it could be a lot of things that could go wrong here.

But with that said, I assume you have the following in place:

GCP firewall rules that allows traffic from/to your Cloud Funtion and Cloud SQL MSSQL
Cloud SQL uses VPC peering for RFC-1918 connections, so I assume that your VPC connector route is there as exported and your MSSQL route is there as well as imported
Your Cloud Funtion VPC connector is in the same region as your Cloud MSSQL

I would recommend for you to enable flow logs and GCP firewall logs, also running connectivity test can also give you a hint on what might be blocking you

answered Oct 14, 2020 at 23:06

Frank

5552 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

josh13 Over a year ago

I created a VM Instance in the same network and using SQL Server Management Studio, it connected successfully with the private IP. The VM Instance doesn't use the VPC Connector, it connects directly. The problem happens when connecting Cloud Functions -> Cloud SQL. Same with App Engine - Cloud SQL and Cloud Run - Cloud SQL. It only happens with SQL Server because using the same setup without changing any configuration in the network, it worked with MySQL.

Collectives™ on Stack Overflow

Cannot connect to Cloud SQL SQL Server from Cloud Functions using private IP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related