The target principal name is incorrect when querying from Azure SQL DB External Table

Question

I've successfully created a Master Key, Database Scoped Credential, External Data Source, and External Table to reference another AZ SQL Database hosted on the same server, but am unable to query the External Table.

The following code runs successfully:

--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword';

--Create DB Scoped Credential
CREATE DATABASE SCOPED CREDENTIAL DB_scoped_credential 
WITH 
IDENTITY = 'MyUserName',
SECRET = 'MySecret';

-- Create External Data Source
CREATE EXTERNAL DATA SOURCE DS_External_DB 
WITH 
(
TYPE = RDBMS,
LOCATION = 'MyServerName.database.windows.net',
DATABASE_NAME = 'MyDBName',
CREDENTIAL = DB_scoped_credential
);

--Create External Table
CREATE EXTERNAL TABLE agencies_ext (
id                      int,
[name]                  varchar(150),
coc                     varchar(50),
[status]                tinyint,
ref_county              int,
clients                 tinyint,
added_date              datetime,
last_updated            datetime,
ref_user_updated        int,
victim_service_provider tinyint,
ref_geolocation         int,
deleted                 tinyint
)
WITH 
(
DATA_SOURCE = DS_External_DB,
SCHEMA_NAME = 'dbo',
OBJECT_NAME = 'agencies'
);

But when running the simple query:

SELECT * FROM agencies_ext;

I get the following error message:

Msg 46832, Level 16, State 3, Line 42
An error occurred while establishing connection to remote data source: [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: The target principal name is incorrect.
[Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection

I've triple checked all of the string arguments to make sure there are no typos and am unsure why I'm unable to establish a remote connection to the data source and query the external table. Are there any settings in Azure SQL DB that could prevent this from going through? Is there a fundamental problem with the creation of the External Data Source and External Table? I would expect this query to run successfully and show all the rows and columns from the agencies table in the database referenced by the DS_External_DB Data Source. Again, both databases are hosted on the same server.

Answer 1

SSL Provider: The target principal name is incorrect.

signifies that the server name you're using in the connection string does not match a CommonName in the SSL certificate provided by the SQL Server.

The resolution of the issue is to mention TrustServerCertificate=yes; in the connection string as suggested by AlwaysLearning so it will not check the name of SQL server is matching with CommonName in the SSL certificate provided by the SQL Server.

I created DataSource with TrustServerCertificate=yes; with below code.

CREATE EXTERNAL DATA SOURCE DS_External_DB6
WITH 
(
TYPE = RDBMS,
LOCATION = 'YourServer.database.windows.net;TrustServerCertificate=yes',
DATABASE_NAME = 'Database1',
CREDENTIAL = DB_scoped_credential5
);

Execution: