什么是链接服务器？

链接服务器 是 SQL Server 中的一个配置对象，它允许你将一个 SQL Server 实例（我们称之为“本地服务器”）连接到另一个 OLE DB 或 ODBC 数据源（我们称之为“远程数据源”），一旦建立了链接服务器，你就可以在本地服务器上使用 Transact-SQL (T-SQL) 语句，像查询本地表一样查询远程数据源中的表、视图或执行存储过程。

核心思想： 将异构数据源“虚拟”成一个逻辑上的服务器，从而实现跨数据库、跨平台的数据访问和集成。

为什么使用链接服务器？

1. 数据集成： 将来自不同系统（如 Oracle, MySQL, Excel, Access）的数据与 SQL Server 中的数据联合查询,生成报表。
2. 分布式查询： 在一个查询中同时访问本地和远程数据，将 SQL Server 中的销售数据与 Oracle 中的产品数据进行关联。
3. 执行远程存储过程： 在远程服务器上执行存储过程并获取结果。
4. 数据迁移： 通过链接服务器,可以方便地将数据从源服务器批量插入到目标服务器。
5. 简化应用程序逻辑： 应用程序只需连接一个 SQL Server 实例，由该实例负责处理对其他数据源的访问,降低了应用层的复杂性。

如何创建链接服务器？（步骤详解）

创建链接服务器主要通过系统存储过程 sp_addlinkedsrvlogin 和 sp_addlinkedserver 来完成。

第 1 步：创建链接服务器本身

使用 sp_addlinkedserver 存储过程，这个过程定义了你要连接的“服务器”的基本信息。

语法：

sp_addlinkedserver
    [ @server = ] 'server'
    [ , [ @srvproduct = ] 'product_name' ]
    [ , [ @provider = ] 'provider_name' ]
    [ , [ @datasrc = ] 'data_source' ]
    [ , [ @location = ] 'location' ]
    [ , [ @provstr = ] 'provider_string' ]
    [ , [ @catalog = ] 'catalog' ];

常用参数说明：

• @server: 必需，你为这个链接服务器起的逻辑名称，这个名字将在你的 T-SQL 查询中使用。
• @srvproduct: 数据源产品的名称，对于大多数 OLE DB 提供程序，可以填写 NULL 或 SQL Server，对于非 SQL Server 数据源，通常填写产品名，如 Oracle。
• @provider: 关键，指定 OLE DB 提供程序的 ProgID。
  • SQL Server: MSDASQL (用于 ODBC) 或 SQLOLEDB (旧的 OLE DB Provider for SQL Server)。
  • Oracle: OraOLEDB.Oracle
  • Excel: Microsoft.ACE.OLEDB.12.0 (Office 2007+) 或 Microsoft.Jet.OLEDB.4.0 (Office 2003)。
  • Access: Microsoft.ACE.OLEDB.12.0 或 Microsoft.Jet.OLEDB.4.0。
  • MySQL: MySQLProv (需要安装 MySQL OLE DB Provider)。
• @datasrc: 关键，指向数据源的路径或名称。
  • Oracle: Oracle 服务器的 TNS 名称 (如 ORCL)。
  • Excel: Excel 文件的完整路径 (如 C:\data\sales.xlsx)。
  • SQL Server: 远程 SQL Server 实例的名称或 IP 地址 (如 REMOTE-SERVER\SQLEXPRESS)。
• @provstr: 连接字符串,某些提供程序需要这个字符串来建立连接。
• @catalog: 默认的数据库或目录，对于 Oracle 是 schema，对于 SQL Server 是数据库名。

第 2 步：配置登录映射

创建了链接服务器后，SQL Server 还需要知道本地用户如何映射到远程用户，这通过 sp_addlinkedsrvlogin 完成。

语法：

sp_addlinkedsrvlogin
    [ @rmtsrvname = ] 'server'
    [ , [ @useself = ] 'useself' ]
    [ , [ @locallogin = ] 'local_login' ]
    [ , [ @rmtuser = ] 'remote_user' ]
    [ , @rmtpassword = ] 'remote_password' ]

常用参数说明：

• @rmtsrvname: 必需,你在第一步中创建的链接服务器名称。
• @locallogin: 可选，本地服务器的登录名，可以是特定的 Windows 登录名、SQL Server 登录名或 NULL（表示适用于所有本地登录）。
• @useself: 关键。
  • TRUE (默认): 使用本地登录的凭据去连接远程服务器，本地用户 sa 会尝试用 sa 的身份登录远程服务器。
  • FALSE: 使用指定的 @rmtuser 和 @rmtpassword 连接远程服务器。
• @rmtuser: 当 @useself 为 FALSE 时,指定的远程用户名。
• @rmtpassword: 当 @useself 为 FALSE 时,指定的远程用户密码。

常见数据源的创建示例

示例 1：链接到另一个 SQL Server 实例

这是最常见的场景。

-- 1. 创建链接服务器
EXEC master.dbo.sp_addlinkedserver
    @server = N'MyRemoteSQL', -- 链接服务器的逻辑名称
    @srvproduct = N'SQL Server',
    @provider = N'SQLOLEDB', -- 或使用 MSDASQL
    @datasrc = N'REMOTE-SERVER\SQLEXPRESS'; -- 远程服务器的实际名称或IP
-- 2. 配置登录映射 (示例：将本地 sa 映射到远程 sa)
-- 使用本地 sa 的凭据去登录远程 sa
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'MyRemoteSQL',
    @useself = 'TRUE',
    @locallogin = N'sa';
-- 或者，使用固定的远程用户名和密码
-- EXEC master.dbo.sp_addlinkedsrvlogin
--     @rmtsrvname = N'MyRemoteSQL',
--     @useself = 'FALSE',
--     @rmtuser = N'remote_user',
--     @rmtpassword = N'password';

示例 2：链接到 Oracle 数据库

-- 1. 创建链接服务器
EXEC master.dbo.sp_addlinkedserver
    @server = N'MyOracleDB', -- 逻辑名称
    @srvproduct = N'Oracle',
    @provider = N'OraOLEDB.Oracle', -- Oracle OLE DB Provider
    @datasrc = N'ORCL'; -- Oracle TNS 名称 (在 tnsnames.ora 中配置)
-- 2. 配置登录映射
-- 将本地用户映射到 Oracle 用户
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'MyOracleDB',
    @useself = 'FALSE',
    @locallogin = N'sa', -- 本地登录名
    @rmtuser = N'oracle_user', -- Oracle 用户名
    @rmtpassword = N'oracle_password'; -- Oracle 密码

示例 3：链接到 Excel 文件

需要先安装 Access Database Engine (Office 的驱动)。

-- 1. 创建链接服务器
EXEC master.dbo.sp_addlinkedserver
    @server = N'MyExcelFile', -- 逻辑名称
    @srvproduct = N'Excel',
    @provider = N'Microsoft.ACE.OLEDB.12.0', -- Office 2007+ 驱动
    @datasrc = N'C:\path\to\your\file.xlsx', -- Excel 文件完整路径
    @provstr = N'Excel 12.0 Xml'; -- 指定 Excel 版本格式 (HDR=Yes 表示第一行是列名)
-- 2. 配置登录映射 (通常使用匿名访问即可)
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'MyExcelFile',
    @useself = 'TRUE',
    @locallogin = N'sa';

如何使用链接服务器进行查询？

创建并配置好链接服务器后，就可以在 T-SQL 中使用它了,主要有四种方式：

四部分命名法

这是最标准、最推荐的方式，格式为：[链接服务器名].[数据库名].[架构名].[对象名]

-- 查询远程 SQL Server 中的表
SELECT *
FROM [MyRemoteSQL].[AdventureWorks2025].[Person].[Person];
-- 查询 Oracle 中的表
SELECT *
FROM [MyOracleDB].[HR].[EMPLOYEES];
-- 查询 Excel 中的工作表 (工作表名后加 $)
SELECT *
FROM [MyExcelFile].[Sheet1$];

OPENQUERY

OPENQUERY 是一个函数，它会在远程服务器上执行指定的查询，然后将结果返回给本地服务器，这通常比四部分命名法更高效,因为它减少了网络传输的数据量。

-- 语法: SELECT * FROM OPENQUERY(链接服务器名, '远程SQL语句')
SELECT *
FROM OPENQUERY(MyRemoteSQL, 'SELECT TOP 10 * FROM AdventureWorks2025.Person.Person WHERE BusinessEntityID > 1000');
-- 将远程查询结果插入到本地表
INSERT INTO LocalSalesData (CustomerID, CustomerName)
SELECT CustomerID, CustomerName
FROM OPENQUERY(MyOracleDB, 'SELECT CUST_ID, CUST_NAME FROM CUSTOMERS WHERE REGION = ''EAST''');

OPENROWSET

OPENROWSET 和 OPENQUERY 类似，但它不需要预先创建链接服务器对象，它直接在查询中提供连接信息,适合一次性或临时的查询。

-- 语法: SELECT * FROM OPENROWSET('Provider', 'Data Source', 'User ID';'Password';'Catalog', 'SQL语句')
SELECT *
FROM OPENROWSET('SQLOLEDB', 'REMOTE-SERVER\SQLEXPRESS';'sa';'password',
    'SELECT * FROM AdventureWorks2025.Person.Person WHERE BusinessEntityID = 1');
-- 查询 Excel
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=C:\path\to\your\file.xlsx', 'SELECT * FROM [Sheet1$]');

EXECUTE (执行远程存储过程)

用于在远程服务器上执行存储过程。

-- 在远程 SQL Server 上执行存储过程
EXECUTE ('usp_GetOrderDetails @OrderID = 12345') AT MyRemoteSQL;
-- 在远程 Oracle 上执行存储过程
EXECUTE ('BEGIN HR_PKG.GetEmployeeInfo(123, :p_name, :p_salary); END;') AT MyOracleDB;

安全性注意事项

1. 最小权限原则： 为映射到远程服务器的账户授予最小必要权限，不要轻易使用 sa 或管理员账户。
2. 凭据管理： 将密码存储在 sp_addlinkedsrvlogin 中是不安全的，可以考虑使用凭证来管理密码，以提高安全性。
   • 创建凭证：CREATE CREDENTIAL MyRemoteCred WITH IDENTITY = 'remote_user', SECRET = 'password';
   • 将凭证映射到链接服务器：ALTER LINKED SERVER MyRemoteSQL WITH (CREDENTIAL = MyRemoteCred);
3. 网络安全性： 确保本地和服务器之间的网络通信是安全的，最好使用 VPN 或其他加密方式。

管理链接服务器

• 查看所有链接服务器：

  SELECT * FROM sys.servers;

• 修改链接服务器属性：

  EXEC sp_serveroption 'MyRemoteSQL', 'rpc out', 'true'; -- 启用远程过程调用

• 删除链接服务器：

  EXEC sp_dropserver 'MyRemoteSQL', 'droplogins'; -- droplogins 会同时删除相关的登录映射

• 删除登录映射：

  EXEC sp_droplinkedsrvlogin 'MyRemoteSQL', 'sa';

链接服务器是 SQL Server 中一项非常基础且重要的功能,掌握它能极大地提升你处理复杂数据环境的能力。