Can't connect to cloud sql instance (sql server) from GCP Dataflow python environment

Question

I'm running a Python Jupyter Notebook in a GCP Dataflow environment. From there, I need to connect to a SQL sever database that's also hosted on GCP Cloud SQL. Both, the Dataflow environment and the Cloud SQL database belong to the same project and the database is in the same region as the Dataflow environment. In the notebook I import pymssql and I try to connect:

import pymssql
conn = pymssql.connect(private_IP_address,  'sqlserver', my_password, my_database, port=1433)

And then I'm getting this error_log from the database log:

{
textPayload: "
2020-11-05 22:32:11.39 Logon       Login failed for user '0b6ac33c6f3ffac\0b6ac33c6f3ffac$'. Reason: Token-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission. [CLIENT: 127.0.0.1]
"
insertId: "s=78a0260e8570497095a8a31a7c378bb3;i=1f23ae;b=0539ace45f6f4b56b03c745b0479107b;m=10a6f7f8e7e;t=5b363aac03490;x=9030d75bb0dadd2-0@a1"
resource: {2}
timestamp: "2020-11-05T22:32:11.410576Z"
severity: "INFO"
labels: {23}
logName: "projects/shapiro-metals/logs/cloudsql.googleapis.com%2Fsqlserver.err"
receiveTimestamp: "2020-11-05T22:32:12.582961041Z"
}

I've been searching around and I couldn't find any simple explanation of exactly what I'm missing; hoping someone can help?

Are they both in the same VPC network? If yes, is your port allowed in VPC firewall rules? Then, have you check if there's running on port 1433 or try to use different port and try to use the public IP address of Cloud SQL. If you did all above, are you still facing the same error? — JM Gelilio
Yes, they are in the same VPC network. The default project network. I've added rules that give permissions but it doesn't seem to work whatever I do. I also don't understand some of the nomenclature: What is the target and the source? Is the source the database instance and the targets the calling code? I've tried using the Public IP address as well and I added the Dataflow IP address to the allowed addresses for the cloud SQL instance and then got the same error in the logs of the SQL instance. — Johannes Castner
Also of interest might be that if I type gcloud sql connect shapiro-sql --user=sqlserver into a terminal that is in the Dataflow environment, I get this error: ERROR: (gcloud.sql.connect) HTTPError 403: The client is not authorized to make this request. But when I issue the same command on my laptop terminal, it asks me for the passwork and when I supply it it connects. — Johannes Castner
Update; after issuing this command from cloud shell: gcloud projects add-iam-policy-binding shapiro-metals --member serviceAccount:[email protected] --role roles/editor I can now successfully run gcloud sql connect shapiro-sql --user=sqlserver but the python interface still doesn't work — Johannes Castner

JM Gelilio JM Gelilio · Accepted Answer · 2020-11-12T06:36:26

Facing the same error after changing to Public IP address and other troubleshooting, this issue is the same with this public tracker and Google Cloud SQL Engineering Team is working on solving this issue.

You can star the public issue tracker feature requests and add comment in the thread that you are also affected to ensure that you will receive updates about it.

Can't connect to cloud sql instance (sql server) from GCP Dataflow python environment

1 Answers