Azure外部表突发故障：连接SQL Server失败及CONNECTION_OPTIONS配置问题

问题现象 #

• 初始创建的SQL Server Azure外部表运行正常，约8小时后突然无法查询，报错信息：

建立与远程数据源的连接时出错:
Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider:
无法打开与SQL Server的连接 [53]
[Microsoft][ODBC Driver 17 for SQL Server]登录超时已过期[Microsoft][ODBC Driver 17

• 尝试在外部数据源配置中添加CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly'无效，但可正常连接到外部表并生成脚本。

排查与修复步骤 #

1. 验证远程SQL Server网络连通性

   • 检查远程SQL Server的防火墙规则：确认Azure SQL实例的出口IP是否被远程服务器的防火墙拦截，若使用动态IP，需确保IP地址未过期或已添加到允许列表。
   • 启用TCP/IP协议：Named Pipes错误多与协议未启用有关，在远程SQL Server配置管理器中，确认SQL Server网络配置下的TCP/IP协议已启用。

2. 重置数据库范围凭据
   若凭据的用户名或密码过期/变更，重新创建凭据测试：

   DROP DATABASE SCOPED CREDENTIAL IF EXISTS SQLServerCredentials;
   CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
   WITH IDENTITY = 'adminadmin', SECRET = 'password1!';
   

3. 修正外部数据源配置
   使用完整服务器地址并显式指定TCP端口（默认1433），避免别名解析问题：

   CREATE EXTERNAL DATA SOURCE [dbI]
   WITH
   ( TYPE = RDBMS ,
   LOCATION = 'sql-1.database.windows.net,1433' , -- 替换为完整服务器地址+端口
   DATABASE_NAME = 'dbI' ,
   CREDENTIAL = SQLServerCredentials,
   CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly'
   ) 
   

查询外部表前1000行的方法 #

直接使用TOP关键字执行查询，语法与普通表一致：

SELECT TOP 1000 *
FROM [dbo].[你的外部表名称];

如需优化查询效率，可指定具体列或添加过滤条件：

SELECT TOP 1000 Column1, Column2, Column3
FROM [dbo].[你的外部表名称]
WHERE SomeCondition IS NOT NULL;

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