查询Azure SQL DB外部表时目标主体名称错误问题排查

问题描述 #

已成功创建Master Key、Database Scoped Credential、External Data Source和External Table，用于引用同一服务器上的另一个Azure SQL数据库，但查询该External Table时失败。

成功执行的创建代码 #

--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'
);

查询语句与错误信息 #

执行查询：

SELECT * FROM agencies_ext;

返回错误：

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

已确认所有字符串参数无拼写错误，两个数据库位于同一Azure SQL服务器，预期查询能返回目标数据库中agencies表的所有数据。

问题排查与解决方法 #

• 修改外部数据源的LOCATION参数
  同一服务器内的数据库连接使用公网域名可能触发SSL主体名称校验失败，尝试使用localhost或带端口的内部连接地址：

  ALTER EXTERNAL DATA SOURCE DS_External_DB 
  WITH (LOCATION = 'localhost');
  

  或指定端口明确连接：

  ALTER EXTERNAL DATA SOURCE DS_External_DB 
  WITH (LOCATION = 'tcp:MyServerName.database.windows.net,1433');
  

• 验证凭据账号的权限
  确保凭据中的MyUserName在目标数据库MyDBName中拥有agencies表的读取权限：

  -- 在目标数据库MyDBName中执行
  GRANT SELECT ON dbo.agencies TO MyUserName;
  

• 调整SSL连接选项（测试用）
  如果SSL校验严格导致内部连接失败，可临时添加连接选项降低加密要求（生产环境不建议长期使用，优先解决主体名称问题）：

  ALTER EXTERNAL DATA SOURCE DS_External_DB 
  WITH (
      LOCATION = 'localhost',
      CONNECTION_OPTIONS = 'Encrypt=Optional'
  );
  

• 核对外部表与目标表结构一致性
  确认agencies_ext的字段类型、长度、空值属性与目标表dbo.agencies完全一致，结构不匹配可能触发隐藏的连接或查询错误。

• 检查服务器防火墙规则
  确保Azure SQL服务器的防火墙允许自身IP访问，可临时添加0.0.0.0至允许IP范围测试（生产环境需删除该规则，设置严格的IP限制）。

内容的提问来源于stack exchange，提问作者alwayslearning