Issue Connecting to Mssql Instance From Google Cloud Function-CodePudding

I am trying to connect to a mssql instance in cloud sql in a cloud function. I have gone through the necessary steps of setting up a private IP, serverless VPC connector, and connecting my function to the VPC. I have been able to connect to the instance in nodejs but python suits my current needs more. The error I'm getting in the logs is:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server'

From all the examples I have read it does not appear that you need to import them or anything. This is my process of connecting and executing a simple request.

import sqlalchemy
import pyodbc

def hello_world(request):
        # connect_simple()
        db = connect_tcp_socket()
        a = execute_request(db)
        return a
    
def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
    db_host = 'my_private_ip'  
    db_user = 'my_db_user'
    db_pass = 'my_db_pass'
    db_name = 'my_db_name'
    db_port = 'my_db_port' 

    connection_string = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' db_host ';PORT=' db_port 'DATABASE=' db_name ';UID=' db_user ';PWD='  db_pass ';Encrypt=no'
    connection_url = sqlalchemy.engine.url.URL.create("mssql pyodbc", query={"odbc_connect": connection_string})
    engine = sqlalchemy.create_engine(
        connection_url
    )

def execute_request(db: sqlalchemy.engine.base.Engine):
        print('ok')
        with db.connect() as conn:
            result = conn.execute('SELECT @@VERSION')
            barray= []
            for row in result:
                barray.append(row)
        return barray

CodePudding user response：

I'd recommend using the Cloud SQL Python Connector to connect to Cloud SQL from Python as it will not require the ODBC driver and is much easier to use within Cloud Functions/Cloud Run.

Just replace your connect_tcp_socket with the below connect_with_connector function.

from google.cloud.sql.connector import Connector, IPTypes
import pytds

import sqlalchemy

def connect_with_connector() -> sqlalchemy.engine.base.Engine:
    def getconn() -> pytds.Connection:
        with Connector() as connector:
            conn = connector.connect(
                "project-id:region:instance-name",  # Cloud SQL connection name
                "pytds",
                user="my-user",
                password="my-password",
                db="my-database",
                ip_type=IPTypes.PRIVATE
            )
            return conn

    engine = sqlalchemy.create_engine(
        "mssql pytds://localhost",
        creator=getconn,
    )
    return engine

You can find a code sample for the Python Connector similar to the one you are using for establishing a TCP connection.

Note: Pytds driver is not super great with error handling. If you see the OSError: [Errno 9] Bad file descriptor error it usually means your database user is missing proper permissions and should grant them the necessary grants from a root user.

Your requirements.txt should include the following:

cloud-sql-python-connector
SQLAlchemy
python-tds
sqlalchemy-pytds

There is also an interactive getting started Colab Notebook that will walk you through using the Python Connector without you needing to change a single line of code!

It makes connecting to Cloud SQL both easy and secure from Cloud Functions.