Skip to content

Failed to create ‘CREATE EXTERNAL DATA SOURCE’

Let’s say you have a SQL Server 2019 installed in Windows server. The Windows server is not in domain, and you are connecting SQL Server using windows authentication.

You may fail to create external data source and get following error message raised and recorded in SQL Server errorlog.

Msg 46721, Level 20, State 1, Line 17
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

polybase log(MSSQL\LOG\Polybase\MSSQLSERVER_sql2019_DWEngine_server.log) will record following error message

12/6/2019 9:25:08 AM [Thread:2748] [PdwTdsNativeToManagedInterop:InformationEvent] (Info, Normal): Incoming TDS connection, Client TDS version: 7 (TDS74). 
12/6/2019 9:25:08 AM [Thread:2748] [EngineInstrumentation:TdsAddConnectionEvent] (Info, Low): Starting TDS connection: 1833532018448 Client: 192.168.2.111:54922, isFromDataSecurityProxy: False, isVnetAddress: False, clientAddressSafe: 192.168.2.111:54922 
12/6/2019 9:25:08 AM [Thread:2748] [DataClassificationConfig:InformationEvent] (Info, Normal): Returning FeatureSwitch DataClassificationCoreEnabled status: False [Session.SessionId:SID119][Session.IsTransactional:False]
12/6/2019 9:25:08 AM [Thread:2748] [EngineInstrumentation:ServerStartSessionEvent] (Info, Low): Started new session, in reset connection: False. [Session.SessionId:SID119][Session.IsTransactional:False]
12/6/2019 9:25:08 AM [Thread:2748] [PdwTdsNativeToManagedInterop:InformationEvent] (Info, Normal): Failed TDS login, client will be disconnected. 
12/6/2019 9:25:08 AM [Thread:2748] [TdsRequestProcessor:InformationEvent] (Info, Normal): CancelBatch request started [Session.SessionId:SID119] 
12/6/2019 9:25:08 AM [Thread:3564] [EngineInstrumentation:EngineCancelQueryBeginEvent] (Info, Low): Cancel requested. [Session.SessionId:SID119][Session.IsTransactional:False]
12/6/2019 9:25:08 AM [Thread:2748] [EngineInstrumentation:TdsRemoveConnectionEvent] (Info, Low): Stopping TDS connection: 1833532018448 
12/6/2019 9:25:08 AM [Thread:2748] [EngineInstrumentation:EngineStopSessionBeginEvent] (Info, Low): Stopping session, in reset connection: False. [Session.SessionId:SID119][Session.IsTransactional:False]

In case you hit issue, please use SQL Authentication or join the Windows server to a domain.

Here is a sample code

create database polyDB
go
use polyDB
go
CREATE MASTER KEY ENCRYPTION BY  PASSWORD = 'S0me!nfo'
go
CREATE DATABASE SCOPED CREDENTIAL CredentialTest WITH IDENTITY = 'system', Secret = 'Password1';
go
CREATE EXTERNAL DATA SOURCE [OracleSource]  WITH ( location = 'Oracle://192.168.2.112:1521',CREDENTIAL = CredentialTest)
---The data type does not matter, it fails even you use SQL Server source

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: