Add SQL Server to Cloud SQL Cloud Run quickstart. (GoogleCloudPlatform#7379)

jsimonweb · web-flow · commit 8540237bb81e · 2022-01-19T09:58:05.000-08:00
* Add SQL Server to Cloud SQL Cloud Run quickstart.

* Lint.
diff --git a/cloud-sql/sql-server/sqlalchemy/Dockerfile b/cloud-sql/sql-server/sqlalchemy/Dockerfile
@@ -16,6 +16,18 @@
 # https://hub.docker.com/_/python
 FROM python:3.10-buster
 
+RUN apt-get update
+RUN apt install unixodbc-dev -y
+
+# Add SQL Server ODBC Driver 17 for Ubuntu 18.04
+RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
+RUN curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
+RUN apt-get update
+RUN ACCEPT_EULA=Y apt-get install -y --allow-unauthenticated msodbcsql17
+RUN ACCEPT_EULA=Y apt-get install -y --allow-unauthenticated mssql-tools
+RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
+RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
+
 # Copy application dependency manifests to the container image.
 # Copying this separately prevents re-running pip install on every code change.
 COPY requirements.txt ./
@@ -29,6 +41,12 @@ ENV APP_HOME /app
 WORKDIR $APP_HOME
 COPY . ./
 
+# Copy any certificates if present.
+COPY ./certs /app/certs
+
+# Use server certificate for encrypted connection.
+COPY ./certs/server-ca.pem /usr/local/share/ca-certificates/server-ca.crt
+RUN update-ca-certificates
 
 # Run the web service on container startup. Here we use the gunicorn
 # webserver, with one worker process and 8 threads.
diff --git a/cloud-sql/sql-server/sqlalchemy/certs/.gitkeep b/cloud-sql/sql-server/sqlalchemy/certs/.gitkeep
diff --git a/cloud-sql/sql-server/sqlalchemy/main.py b/cloud-sql/sql-server/sqlalchemy/main.py
@@ -66,9 +66,53 @@ def init_connection_engine():
         # [END cloud_sql_server_sqlalchemy_lifetime]
     }
 
+    if os.environ.get("DB_ROOT_CERT"):
+        return init_tcp_sslcerts_connection_engine(db_config)
     return init_tcp_connection_engine(db_config)
 
 
+def init_tcp_sslcerts_connection_engine(db_config):
+    # [START cloud_sql_postgres_sqlalchemy_create_tcp_sslcerts]
+    # Remember - storing secrets in plaintext is potentially unsafe. Consider using
+    # something like https://cloud.google.com/secret-manager/docs/overview to help keep
+    # secrets secret.
+    db_user = os.environ["DB_USER"]
+    db_pass = os.environ["DB_PASS"]
+    db_name = os.environ["DB_NAME"]
+    db_host = os.environ["DB_HOST"]
+
+    # Extract port from db_host if present,
+    # otherwise use DB_PORT environment variable.
+    host_args = db_host.split(":")
+    if len(host_args) == 1:
+        db_hostname = host_args[0]
+        db_port = int(os.environ["DB_PORT"])
+    elif len(host_args) == 2:
+        db_hostname, db_port = host_args[0], int(host_args[1])
+
+    pool = sqlalchemy.create_engine(
+        # Equivalent URL:
+        # mssql+pyodbc://<db_user>:<db_pass>@/<host>:<port>/<db_name>?driver=ODBC+Driver+17+for+SQL+Server
+        sqlalchemy.engine.url.URL.create(
+            "mssql+pyodbc",
+            username=db_user,
+            password=db_pass,
+            database=db_name,
+            host=db_hostname,
+            port=db_port,
+            query={
+                "driver": "ODBC Driver 17 for SQL Server",
+                "Encrypt": "yes",
+                "Trusted_Connection": "no"
+            },
+        ),
+        **db_config
+    )
+    # [END cloud_sql_postgres_sqlalchemy_create_tcp_sslcerts]
+    pool.dialect.description_encoding = None
+    return pool
+
+
 def init_tcp_connection_engine(db_config):
     # [START cloud_sql_server_sqlalchemy_create_tcp]
     # [START cloud_sql_sqlserver_sqlalchemy_create_tcp]
diff --git a/cloud-sql/sql-server/sqlalchemy/requirements.txt b/cloud-sql/sql-server/sqlalchemy/requirements.txt
@@ -2,6 +2,6 @@ Flask==2.0.2
 SQLAlchemy==1.4.27
 python-tds==1.11.0
 sqlalchemy-pytds==0.3.4
+pyodbc==4.0.32
 pyopenssl==21.0.0
 
-
