Polybase DMVs erroring out

Question

All,

I have a SQLServer 2019 installation on Windows 10 Pro (Desktop at home) and I have enabled Polybase on it. However, I get the below error whenever I run any of the Polybase DMVs.

*select * from sys.dm_exec_compute_nodes
select * from sys.dm_exec_dms_services*

Msg 782, Level 16, State 1, Line 2
SQL Server Network Interfaces: No credentials are available in the security package

The version of SQLServer 2019 is : "Microsoft SQL Server 2019 (RTM-CU6) (KB4563110)"

grajee

Answer 1

I can't say that I have that much more success. When I'm logged in with Windows authentication, I get "Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication." I believe is due to that I am in a workgroup.

If I log in as sa, I can run

select * from sys.dm_exec_compute_nodes

However, if I try:

select * from sys.dm_exec_dms_services

I get "Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". Error processing metadata query sp_execute_memo: Object reference not set to an instance of an object." On the first attempt the query ran for five minutes before dying. I also find a dump in the log directory for Polybase. (On subsequent executions the error message is instant.)

I also find that if I try to stop the Polybase services, the Data Movement service stops, but the Polybase engine service does not. If I kill it through Process Explorer and restart the service, the query again runs for five minutes before producing the same error message.

For the record, I'm on CU7. (But you should not be on this CU, since Microsoft has pulled it due to an issue with database snapshots.)

Answer 2

Erland,

Tried using sql login and it is working. However, I tried it on the Standalone SQLServer 2019 @ work and it works with Integrated authentication.

Could it be the case that it will not work on Windows Authentication but will work with SQL/"Integrated AD" authentication?

Thanks,
grajee

Answer 3

Erland,
This is Tom. You helped me with the polybase log file recently. I got polybase to connect to a local 19c instance of Oracle. I include how I did it below. You were right. Nothing worked unless I logged in with sql server authentication uid=sa .... . Using a Windows Auth Management Studio session doesn't work

Can you talk about the Polybase architecture a little bit? Why the emphasis on "Create External Table" that links, one by one, to the oracle tables? Wouldn't people just want to read the Oracle tables from ms sql like the old MS Sql Linked Servers used to do?

Oh and funny. I thought really using Polybase would possibly make the log file creation go nuts again, but I checked and it didn't

More data about this. Once you set up with sql server auth, that is the only way you can see the oracle create external table. In future sessions if you go into mgmt studio with windows auth, you can't see the externally created oracle table. It fails with a:
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Cannot generate SSPI context".
Msg -2146893042, Level 16, State 1, Line 0
SQL Server Network Interfaces: No credentials are available in the security package

/* Example: using Polybase to link to a locally installed Oracle 19c server */

/*
create master key encryption by password = 'xpw'
*/

/*
create database scoped credential oracleoci_creds
with identity = 'uid', secret = 'xpw'
*/

/*
CREATE EXTERNAL DATA SOURCE cdata_oracleoci_source
WITH (
LOCATION = 'oracle://localhost:1521',
CREDENTIAL = oracleoci_creds
);
*/

-- polybase can be installed but not enabled
--SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;

-- i think this is a one time command
--exec sp_configure @configname = 'polybase enabled', @configvalue = 1;
--RECONFIGURE;

-- this link to an oracle table worked, character fields are difficult
-- and I couldn't get one to come across from oracle to ms sql. that COLLATE ... is messing it up.
-- wierd upper/lower case thing going on here, had to make all this stuff including the location upper case.
-- brought 3 of the 4 columns over, couldn't get the character column to come across
/*
CREATE external TABLE glfact(
FK_FISCPER int NULL,
FK_ACCTNBR int NULL,

PERBALANCE decimal(19, 7) NULL
)
WITH (
location=N'ORCL.XUID.GLFACT',
DATA_SOURCE= cdata_oracleoci_source
);
*/

select * from glfact