2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohamed Cassim
  • 153
  • 1
  • 10
  • I don't know what Polybase is, but the server that wrote you "Cannot generate SSPI contex" seems to be configured to windows authentication only, and you need to change it to mixed mode – sepupic Nov 05 '20 at 16:06
  • You can find out authenticatio mode in the first rows of errorlog of your SQL Server. By default, if you don't change it, SQL Server is installed in Windows only authentication mode – sepupic Nov 05 '20 at 16:08
  • Polybase is what allows external data sources in sql server. – Mohamed Cassim Nov 05 '20 at 17:15
  • This instance is set to mixed mode. I can login to management studio using the sql credentials "sa" and password – Mohamed Cassim Nov 05 '20 at 17:16
  • @MohamedCassim you can login to .\sqlexpress with the sa account? Just confirming which instance you verified was in mixed mode. – GregGalloway Nov 07 '20 at 13:17
  • Yes, I only have a single instance installed, the .\sqlexpress instance. And I have tested the sa login is correct. – Mohamed Cassim Nov 07 '20 at 14:24

1 Answers1

-2

You can try using Sql login. That should work.

Adriaan
  • 17,741
  • 7
  • 42
  • 75
Alireza Maranaki
  • 1
  • 3
    Welcome to Stack Overflow! Please read [answer]. Why should "Sql login" work? Please [edit] your answer with more details. – Adriaan Feb 24 '23 at 07:48