PolyBase and Windows Authentication in SQL Server 2022

Back in the SQL Server 2019 days, I laid out a post which covered a really annoying bug around Windows authentication and PolyBase. The upshot is that SQL Server 2019 CU2 broke Windows authentication, meaning that you could not create external objects on a SQL Server instance while using a Windows authenticated session. In CU2, if you tried, you would get a Level 20 error which would kill your session.

Over the next several CUs, the SQL Server team improved things incrementally: first, the error that you got was a Level 16 error, so your statement would fail but the session would remain active. Then, in CU6, they made it so that if your SQL Server instance is on a server running on a domain, then you would be okay—you could create external objects while connected with a domain-authenticated Windows account.

I decided to give this a try in SQL Server 2022 RC0 and ran into some interesting results. The first time I tried this, I got back the original error from that blog post:

Msg 46721, Level 20, State 1, Line 5
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

This was at a severity level of 20 in SQL Server 2022 RC0 as well, so it killed the session.

After trying some other things out, it appears that PolyBase access is broken (so bad that it reverted to 2019 CU2 behavior) whereas non-PolyBase data virtualization works. What this means is:

• CREATE EXTERNAL FILE FORMAT works fine.
• CREATE EXTERNAL DATA SOURCE and CREATE EXTERNAL TABLE will work if you are accessing resources relating to Azure Blob Storage (abs://), Azure Data Lake Storage Gen2 (adls://), or Amazon S3 (s3://).
• CREATE EXTERNAL DATA SOURCE or CREATE EXTERNAL TABLE will fail if you are attempting to connect to any of the PolyBase V2 services (SQL Server, MongoDB, Teradata, Oracle, generic ODBC). Interestingly, sometimes the error message occurs when creating the external data source (SQL Server, generic ODBC with Excel) and sometimes when creating the external table (Cosmos DB with MongoDB driver, Cosmos DB with generic ODBC).

The workaround remains what it was before: if you are not on a domain, use SQL authentication to create all external objects. If you are on a domain, it should work, though I have not tested that as of the time of this post.

Advertisement