使用SQL Server身份验证创建SQL Server镜像端点的T-SQL实现问题

SQL Server的数据库镜像端点**不支持直接使用SQL Server登录账号（SQL Server身份验证）**进行身份验证，官方仅提供Windows身份验证和证书身份验证两种选项。如果需要实现非域环境下的身份验证，通常采用证书身份验证来替代——这也是SSMS中选择"SQL Server身份验证"配置镜像时底层实际执行的逻辑。

以下是完整的T-SQL实现步骤：

步骤1：在主服务器创建加密主密钥与证书，并备份证书 #

-- 在主服务器执行
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongMasterKeyPassword!';
GO

CREATE CERTIFICATE MirrorCert_Primary
    WITH SUBJECT = 'Mirroring Cert for Primary Server';
GO

-- 将证书备份到指定路径（需确保SQL Server服务账户有该路径读写权限）
BACKUP CERTIFICATE MirrorCert_Primary
    TO FILE = 'D:\SQL_Certs\MirrorCert_Primary.cer';
GO

步骤2：在镜像服务器导入主服务器证书，并创建授权登录 #

-- 在镜像服务器执行
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongMasterKeyPassword_Mirror!';
GO

-- 导入从主服务器备份的证书
CREATE CERTIFICATE MirrorCert_Primary
    FROM FILE = 'D:\SQL_Certs\MirrorCert_Primary.cer';
GO

-- 创建用于镜像连接的SQL登录
CREATE LOGIN MirrorLogin_Primary WITH PASSWORD = 'YourStrongLoginPassword!';
GO

CREATE USER MirrorUser_Primary FOR LOGIN MirrorLogin_Primary;
GO

-- 授权该登录连接镜像端点
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO MirrorLogin_Primary;
GO

步骤3：在镜像服务器创建自身证书并备份，同步到主服务器 #

-- 在镜像服务器执行
CREATE CERTIFICATE MirrorCert_Mirror
    WITH SUBJECT = 'Mirroring Cert for Mirror Server';
GO

BACKUP CERTIFICATE MirrorCert_Mirror
    TO FILE = 'D:\SQL_Certs\MirrorCert_Mirror.cer';
GO

-- 在主服务器执行
CREATE CERTIFICATE MirrorCert_Mirror
    FROM FILE = 'D:\SQL_Certs\MirrorCert_Mirror.cer';
GO

CREATE LOGIN MirrorLogin_Mirror WITH PASSWORD = 'YourStrongLoginPassword_Mirror!';
GO

CREATE USER MirrorUser_Mirror FOR LOGIN MirrorLogin_Mirror;
GO

GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO MirrorLogin_Mirror;
GO

步骤4：创建使用证书身份验证的镜像端点 #

分别在主服务器和镜像服务器执行以下脚本，注意替换证书名称：

-- 主服务器执行
CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        AUTHENTICATION = CERTIFICATE MirrorCert_Primary,
        ENCRYPTION = SUPPORTED,
        ROLE = ALL
    );
GO

-- 镜像服务器执行
CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        AUTHENTICATION = CERTIFICATE MirrorCert_Mirror,
        ENCRYPTION = SUPPORTED,
        ROLE = ALL
    );
GO

说明 #

通过证书身份验证，两台服务器会互相验证对方的证书有效性，同时通过SQL登录账号授予端点连接权限，实现了与SSMS中"SQL Server身份验证"配置等价的非域身份验证能力。

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