From 9a438db3e6046ef12164968b8e835fc95a63c349 Mon Sep 17 00:00:00 2001 From: Alex Lowe Date: Mon, 15 Mar 2021 14:50:45 -0400 Subject: [PATCH] Add documentation for connecting to Azure SQL instances with managed identities Fixes: #6031 Closes: #6032 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/6032 Pull-request-sha: 00abfffd198a4c0e81bb5d15219dd4e40670ce94 Change-Id: Id67b21e6d2cc93f88d78e291e3bc8f87463377a6 --- doc/build/core/engines.rst | 24 ++++++++ lib/sqlalchemy/dialects/mssql/pyodbc.py | 79 ++++++++++++++++++++++--- 2 files changed, 94 insertions(+), 9 deletions(-) diff --git a/doc/build/core/engines.rst b/doc/build/core/engines.rst index 7cfa1bddd0..af11a8a68c 100644 --- a/doc/build/core/engines.rst +++ b/doc/build/core/engines.rst @@ -393,6 +393,30 @@ collections can then be modified in place to alter how they are used:: def receive_do_connect(dialect, conn_rec, cargs, cparams): cparams['connection_factory'] = MyConnectionFactory +.. _engines_dynamic_tokens: + +Generating dynamic authentication tokens +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +:meth:`.DialectEvents.do_connect` is also an ideal way to dynamically +insert an authentication token that might change over the lifespan of an +:class:`_sa.engine.Engine`. For example, if the token gets generated by +``get_authentication_token()`` and passed to the DBAPI in a ``token`` +parameter, this could be implemented as:: + + from sqlalchemy import event + + engine = create_engine("postgresql://user@hostname/dbname") + + @event.listens_for(engine, "do_connect") + def provide_token(dialect, conn_rec, cargs, cparams): + cparams['token'] = get_authentication_token() + +.. seealso:: + + :ref:`mssql_pyodbc_access_tokens` - a more concrete example involving + SQL Server + Modifying the DBAPI connection after connect, or running commands after connect ------------------------------------------------------------------------------- diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py index 96ac2dff72..c4ad888587 100644 --- a/lib/sqlalchemy/dialects/mssql/pyodbc.py +++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py @@ -4,7 +4,6 @@ # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php - r""" .. dialect:: mssql+pyodbc :name: PyODBC @@ -56,7 +55,7 @@ Other keywords interpreted by the Pyodbc dialect to be passed to ``authentication``. Note that in order for the dialect to recognize these keywords (including the ``driver`` keyword above) they must be all lowercase. -Multiple additional keyword arguments must be separated by an +Multiple additional keyword arguments must be separated by an ampersand (``&``), not a semicolon:: engine = create_engine( @@ -70,15 +69,76 @@ Pass through exact Pyodbc string ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ A PyODBC connection string can also be sent in pyodbc's format directly, as -specified in `ConnectionStrings -`_ into the driver -using the parameter ``odbc_connect``. The delimeters must be URL encoded, as -illustrated below using ``urllib.parse.quote_plus``:: +specified in `the PyODBC documentation +`_, +using the parameter ``odbc_connect``. A :class:`_sa.engine.URL` object +can help make this easier:: + + from sqlalchemy.engine import URL + connection_string = "DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password" + connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string}) + + engine = create_engine(connection_url) + +.. _mssql_pyodbc_access_tokens: + +Connecting to databases with access tokens +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +Some database servers are set up to only accept access tokens for login. For +example, SQL Server allows the use of Azure Active Directory tokens to connect +to databases. This requires creating a credential object using the +``azure-identity`` library. More information about the authentication step can be +found in `Microsoft's documentation +`_. + +After getting an engine, the credentials need to be sent to ``pyodbc.connect`` +each time a connection is requested. One way to do this is to set up an event +listener on the engine that adds the credential token to the dialect's connect +call. This is discussed more generally in :ref:`engines_dynamic_tokens`. For +SQL Server in particular, this is passed as an ODBC connection attribute with +a data structure `described by Microsoft +`_. + +The following code snippet will create an engine that connects to an Azure SQL +database using Azure credentials:: + + import struct + from sqlalchemy import create_engine, event + from sqlalchemy.engine.url import URL + from azure import identity - import urllib - params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password") + SQL_COPT_SS_ACCESS_TOKEN = 1256 # Connection option for access tokens, as defined in msodbcsql.h + TOKEN_URL = "https://database.windows.net/" # The token URL for any Azure SQL database + + connection_string = "mssql+pyodbc://@my-server.database.windows.net/myDb?driver=ODBC+Driver+17+for+SQL+Server" + + engine = create_engine(connection_string) + + azure_credentials = identity.DefaultAzureCredential() + + @event.listens_for(engine, "do_connect") + def provide_token(dialect, conn_rec, cargs, cparams): + # remove the "Trusted_Connection" parameter that SQLAlchemy adds + cargs[0] = cargs[0].replace(";Trusted_Connection=Yes", "") + + # create token credential + raw_token = azure_credentials.get_token(TOKEN_URL).token.encode("utf-16-le") + token_struct = struct.pack(f"`_, + stating that a connection string when using an access token must not contain + ``UID``, ``PWD``, ``Authentication`` or ``Trusted_Connection`` parameters. - engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params) Pyodbc Pooling / connection close behavior ------------------------------------------ @@ -161,6 +221,7 @@ driver in order to use this flag:: """ # noqa + import datetime import decimal import re -- 2.47.2