0

I am trying to setup an external data source in SQL Server 2019 to another database on the same server.

I am doing this to create external table

I tried the following to set it up

  • I installed SQL Server 2019 Enterprise
  • I installed Polybase
  • I enabled TCP/IP and made sure services were running

I then ran

USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxxxxxxxxxxxxxx'

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

USE mydb
CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
WITH IDENTITY = 'sa', SECRET = 'xxxxxxxx';

CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH ( LOCATION = 'sqlserver://.\sqlexpress',
    PUSHDOWN = ON,
    CREDENTIAL = SQLServerCredentials);


 CREATE EXTERNAL TABLE customers(
 [customerID] int null,
 [Price] float NULL
 )
 WITH (
 LOCATION='mydb.customer',
 DATA_SOURCE= SQLServerInstance
 );

On the final CREATE EXTERNAL DATA SOURCE statement I get this error

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

I can't seem to figure out why I get this error

0

The error seems to be with Kerberos/NTLM, not specific to SQL Server. Please check under which account runs the SQLServerExpress service, and under which account runs the SQL Server 2019 service. Depending on this, change the service account to a domain account, or create a local Administrator account and use it.

4
  • I did this, but again the problem was not solved Oct 19 at 15:28
  • Can you please elaborate on what is the setup, what did you change, and if the error is the same?
    – pabechevb
    Oct 20 at 16:04
  • i Changed Log On As MSSQLSERVER to Network Service and Get Error to execute top query : Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. Oct 21 at 15:25
  • Did you restart the service after the change? What about SQL Server 2019, under which account is it running?
    – pabechevb
    Oct 22 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.