I'm having issues creating an external table in SQL Server which points to another SQL Server.
These are the steps I've taken:
CREATE EXTERNAL DATA SOURCE Polybase_ServerTest
WITH (
LOCATION = 'sqlserver://ServerTest:1433',
--PUSHDOWN = ON,
CREDENTIAL = Credential_PolyBase
);
CREATE EXTERNAL TABLE ext.Person
(
[person_id] [varchar](9) NULL,
[given_names] [varchar](20) NULL,
[surname] [varchar](30) NULL
)
WITH (DATA_SOURCE = Polybase_ServerTest, LOCATION = 'DB_Name.Schema.TableName')
The error I get when creating the table is
Msg 110045, Level 16, State 1, Line 28
110045;User authorization failed: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'TestUser'. Additional error <2>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute
There are two things I would check: Is your SQL Server patched to the latest version? And, have you created the credential as indicated here? https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-sql-server?view=sql-server-ver16