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 replicate the SQL Azure setup currently running in production.
I tried the following to set it up
- I installed SQL Server 2019 Express
- 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);
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