Engine creation issue when attempting to use an Azure Data Warehouse DB using mssql+pyodbc

[Goltred]

Whenever trying to create an engine object using the mssql dialect and pyodbc driver, an error will be raised indicating the database_principal_id() does not exist.

According to Microsoft Documentation, this is true for their data warehouse, but is still supported in normal Azure Databases. (https://msdn.microsoft.com/en-us/library/ms187319.aspx)

With my limited knowledge, I applied a workaround but I am not completely sure the impact it might have in other connections.

Edited the base.py module to have the following:

Line 532:

   MS_AZURE_VERSION = (12,)

Line 1654:

def _get_default_schema_name(self, connection):
        if self.server_version_info < MS_2005_VERSION:
            return self.schema_name
        elif self.server_version_info >= MS_AZURE_VERSION:
            query = sql.text("""
                SELECT user_name()
            """)
        else:
            query = sql.text("""
                SELECT default_schema_name FROM
                sys.database_principals
                WHERE principal_id=database_principal_id()
            """)
        default_schema_name = connection.scalar(query)
        if default_schema_name is not None:
            return util.text_type(default_schema_name)
        else:
            return self.schema_name

[Mike Bayer]

OK we aren't looking for "user name" here, we need the default schema where the current logged in user will querying from and creating tables within. This is normally "dbo" but it may be different. See https://msdn.microsoft.com/en-us/library/bb669061(v=vs.110).aspx. How do we get that name on this newer version ?

[Goltred]

So, it appears you can get the default_schema_name by either doing "SELECT SCHEMA_NAME()" or, "SELECT default_schema_name FROM sys.database_principals where name = user_name()"

I have tested this today and nothing broke when establishing a connection to azure (but I don't know if it might affect the connection to other mssql servers)

def _get_default_schema_name(self, connection):
        if self.server_version_info < MS_2005_VERSION:
            return self.schema_name
        elif self.server_version_info >= MS_AZURE_VERSION:
            query = sql.text("""
                SELECT SCHEMA_NAME()
            """)
        else:
            query = sql.text("""
                SELECT default_schema_name FROM
                sys.database_principals
                WHERE principal_id=database_principal_id()
            """)
        default_schema_name = connection.scalar(query)
        if default_schema_name is not None:
            return util.text_type(default_schema_name)
        else:
            return self.schema_name

[Mike Bayer]

"The ID of the schema. schema_id is an int. If schema_id is not defined, SCHEMA_NAME will return the name of the default schema of the caller." looks good. let me see if i can figure out who gave us the database_principals query.

[Mike Bayer]

here we are #1258. no particular rationale for that method.

[Mike Bayer]

oh! but interestingly, they're comparing on name. so lets find out where this broke to change to principal_id.

[Mike Bayer]

and that...is #3025.

[Mike Bayer]

Use schema_name() for SQL Server default schema

Changed the query used to get "default schema name", from one that queries the database principals table to using the "schema_name()" function, as issues have been reported that the former system was unavailable on the Azure Data Warehouse edition. It is hoped that this will finally work across all SQL Server versions and authentication styles.

Change-Id: Ic11bd4162c0d6a60432ae44876e86512703c1f81 Fixes: #3810

→ <<cset 562e37ee7eaa>>

[Mike Bayer]

Use schema_name() for SQL Server default schema

Changed the query used to get "default schema name", from one that queries the database principals table to using the "schema_name()" function, as issues have been reported that the former system was unavailable on the Azure Data Warehouse edition. It is hoped that this will finally work across all SQL Server versions and authentication styles.

Change-Id: Ic11bd4162c0d6a60432ae44876e86512703c1f81 Fixes: #3810 (cherry picked from commit 562e37ee7eaab8b19a9f081d4b170b95fce4f619)

→ <<cset ed7d78a73cde>>

[Thomas Haederle]

Hi Michael,

In sqlserver < 2012, when a user is logged in via an AD Group and Trusted Security SELECT SCHEMA_NAME() will return the user name, not "dbo". For users logged in via an individual account, default schema will normally default to "dbo" or can be set by the admin to "dbo". But SQLServer does not allow the admin to set a "Default Schema" for AD Groups, only for users with a login.

see this related post: https://www.mssqltips.com/sqlservertip/4036/default-schema-for-windows-group-in-sql-server/

Alembic should allow the user to configure the schema. When I use Alembic on a 2008 SQLServer via an AD group, Alembic creates it's own tables under my username and it only detects migrations for tables under my username schema. It cannot detect changes to "dbo"-tables and I have not been able to set the default schema to dbo anywhere.

[Mike Bayer]

hello @tomanizer

once again, I please ask that you use the appropriate venues for usage questions, which is the mailing list at https://groups.google.com/forum/#!forum/sqlalchemy . But in fact in my answer to you at https://bitbucket.org/zzzeek/sqlalchemy/issues/3025/revise-mssql-default-schema-to-take#comment-31998542 I even answered your question that you appear to be asking in a different way over here (I will gladly clarify my answer on the mailing list).

thanks!