Polybase DMVs erroring out

grajee 341 Reputation points
2020-09-27T00:52:52.887+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,322 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 99,546 Reputation points MVP
    2020-09-27T09:40:50.147+00:00

    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.)

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. grajee 341 Reputation points
    2020-09-27T15:28:45.437+00:00

    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


  2. Flash Gordon 176 Reputation points
    2021-03-30T21:38:28.663+00:00

    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 glfact82993-polybaseexternaltable.png