如何用T-SQL脚本连接SQL Server并执行ETL任务及排查登录失败问题

核心问题 #

你遇到的登录失败，本质是链接服务器的身份验证配置和SSMS的实际登录逻辑不匹配，或脚本参数有误。

修复方案 #

1. 修正身份验证配置 #

根据你的登录方式选择对应配置：

• Windows身份验证（SSMS用域/本地Windows账号登录）：不要硬编码SQL账号，直接复用当前Windows凭据：

  EXEC sp_addlinkedsrvlogin 'MySer', 'true', NULL;
  

• SQL Server身份验证：确保rmtuser和rmtpassword是远程服务器的有效账号密码，且参数顺序正确：

  EXEC sp_addlinkedsrvlogin 
      @rmtsrvname = 'MySer',
      @useself = 'false',
      @locallogin = NULL,
      @rmtuser = '远程SQL账号',
      @rmtpassword = '远程SQL密码';
  

2. 避免重复创建链接服务器 #

先清理已存在的同名链接服务器，防止冲突：

IF EXISTS(SELECT 1 FROM sys.servers WHERE name = 'MySer')
    EXEC sp_dropserver 'MySer', 'droplogins';

3. 完整可执行脚本 #

USE MyDB;
GO

-- 清理旧的链接服务器（若存在）
IF EXISTS(SELECT 1 FROM sys.servers WHERE name = 'MySer')
    EXEC sp_dropserver 'MySer', 'droplogins';

-- 创建链接服务器
EXEC sp_addlinkedserver 'MySer', N'SQL Server';

-- 配置身份验证（二选一）
-- 方式1：Windows身份验证
EXEC sp_addlinkedsrvlogin 'MySer', 'true', NULL;

-- 方式2：SQL Server身份验证
-- EXEC sp_addlinkedsrvlogin 'MySer', 'false', NULL, '远程SQL账号', '远程SQL密码';

-- 执行ETL任务
TRUNCATE TABLE MyDB.dbo.MyTable;
GO

INSERT INTO dbo.MyTable (ID, Name, NameEn, Acronym)
SELECT 
    r.R_Id, 
    r.R_Desc, 
    r.R_Desc_E, 
    ''
FROM MySer.RemoteDB.dbo.RemoteTable r;
GO

4. 额外排查方向 #

• 确认远程服务器允许当前SQL Server实例的IP访问（防火墙/端口配置）
• 检查远程账号对RemoteDB.dbo.RemoteTable有SELECT权限
• 验证链接服务器的RPC/RPC Out选项已开启（右键链接服务器→属性→服务器选项）

内容的提问来源于stack exchange，提问作者SS-KH