I wanted to throw out a blog post in case anybody runs into this bug. It started happening with SQL Server 2019 CU2 and I have confirmed that it is still a problem in SQL Server 2019 CU3.
This was fixed in SQL Server 2019 CU6 IF YOUR SERVER IS IN A DOMAIN. After installing CU6, I was able to create external objects using my Windows-authenticated account, so the bug introduced in CU2 is finally gone. You can also query data from external objects using Windows accounts, meaning that everything is peachy now IF YOUR SERVER IS IN A DOMAIN.
If you have a machine which is not in an Active Directory domain, it appears that you still get the error from CU5. So it looks like this is halfway fixed.
With SQL Server 2019 CU5 released, I wanted to check if this is still an issue. The issue is still here, but it only generates a Level 16 error, so although Windows authentication fails, it at least no longer kills your session immediately. Here’s the error message I get:
OLE DB provider “MSOLEDBSQL” for linked server “(null)” returned message “Cannot generate SSPI context”.
Msg -2146893042, Level 16, State 1, Line 65
SQL Server Network Interfaces: No credentials are available in the security package
PolyBase and Windows Authentication
Here’s the short version of the bug. If you are connected using a Windows authenticated account and attempt to perform a PolyBase-related action, such as creating an external data source or querying from an external table, you receive the following error:
The error text:
Msg 46721, Level 20, State 1, Line 5
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.
Because this is an error with a severity level of 20, it kills your session.
The Workaround: SQL Authentication
For now, the only workaround is to perform all of your PolyBase-related work using SQL authentication. This includes creating or dropping external data sources, file formats, and tables, as well as querying any external tables.
Clarification: PolyBase and Kerberos
Just to clarify, I’m talking about work that happens on the local side of a PolyBase system, that is, things which happen on the local SQL Server instance(s). This bug appeared in CU2, but prior to that, you could use Windows authenticated accounts to create PolyBase objects.
If you are connecting to a Hadoop cluster, you can still use Kerberos to make that connection.
If you are connecting to a remote SQL Server instance, Oracle or Teradata server, MongoDB cluster, etc., Kerberos is not supported. This has been the case since the first CTPs of SQL Server 2019. So, for example, let’s suppose you want to connect to a remote SQL Server instance, SQLRemote. Your database scoped credential that you use when connecting to SQLRemote must be a SQL authenticated login; that is the current design, and even after they fix the bug I’m talking about, it won’t change this design.