maandag 24 februari 2020

SQL Server 2019 : Login failed with polybase

I was trying to investigate usage of polybase on my local SQL Server 2019 installation, the on -premise version and I was wondering whether I could use Polybase for loading Parquet files into SQL Server and I ran in a couple of errors that I would like to share with you.

The first error I would like to discuss is an obvious one : When creating an EXTERNAL FILE FORMAT in SQL Server an error happens because I've not installed the polybase software.

-- Create an external file format for PARQUET files.  
WITH (  

This is resulting in an error

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'EXTERNAL'.

Therefore we need to install the "Polybase Query Service for External Data" with the installation disk

and so I did. Now the next thing that is needed is enabling Polybase with the sp_configure command.

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

When the above statement is finished the following message appears

Configuration option 'polybase enabled' changed from 0 to 1. 
Run the RECONFIGURE statement to install.

Then, I tried the following statement again, but now again but a different error occurred.

-- Create an external file format for PARQUET files.  
WITH (  

After a while waiting for some response from SQL Server the following error occurred. Now at least we don't get an error near 'EXTERNAL'.

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message 
 "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. 
 Server is not found or not accessible. Check if instance name is correct and if SQL Server is 
 configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 10061, Level 16, State 1, Line 10
TCP Provider: No connection could be made because the target machine actively refused it.

A hint from Bob Duffy helped me a bit further. You will need to set TCP/IP protocol to Enabled under SQL Server Network Configuration\Protocols for MSSQLSERVER.  Then restart the main SQL Service so settings take effect, and then the SQL Server PolyBase Engine service should start.

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

Now from Pinal Dave I was pointed to that the login was set on Windows Authentication.

But no luck with that adjustment. On Stackoverflow I've found that there is an issue with using Polybase with a non-domain joined machine and I haven't installed an AD controller and so that must be the problem

Are you running the DDL from a local Windows account 
(i.e., non-domain joined machine)? There is a regression in SQL Server 2019 
where you will get this error when trying to use PolyBase. 
We are in the process of fixing the issue in an upcoming CU.

So it seems that the machine should be joined in a Active Directory Domain service or we have to wait for a CU.

Another thing I experienced was that Polybases services was continously stating that they were 'starting' but nothing happens, SQL Server agent was also not restarting because of the Polybase services. Killing the Polybase services and restarting the services did the trick. Off course, take precautions in a non local dev environment, like production systems

As far as I know now it seems that reading local parquet files on disk in SQL Server is not possible. I've seen examples with Hadoop and Blob storage, but no luck finding a solution for reading parquet files into SQL Server.


Geen opmerkingen:

Een reactie posten