无域环境使用证书启用SQLServer的Always On

1.主库执行脚本（创建主密钥、证书和端点）

先创建 master 库的主密钥，再生成主库证书并备份，最后创建用于 Always On 的数据库镜像端点，绑定证书做身份验证，脚本如下：

USE master;
GO
-- 创建主密钥，密码自定义，需符合复杂度要求
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '这里填写密码';
GO
-- 创建主库证书，设置有效期至2099年
CREATE CERTIFICATE Primary_Server_Cert 
WITH SUBJECT = 'Primary Server Certificate for AlwaysOn',
EXPIRY_DATE = '2099-12-31';
GO
-- 备份证书到共享目录（需确保辅助库能访问该目录）
BACKUP CERTIFICATE Primary_Server_Cert 
TO FILE = 'C:\AlwaysOn_Certs\Primary_Server_Cert.cer';
GO
-- 创建可用性组端点，端口默认5022，支持所有角色
CREATE ENDPOINT AG_Endpoint
AUTHORIZATION sa
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = CERTIFICATE Primary_Server_Cert,
    ENCRYPTION = REQUIRED ALGORITHM AES
);
GO

2.辅助库执行脚本（创建对应密钥、证书和端点）

操作逻辑和主库一致，生成辅助库专属证书并备份，脚本如下：

USE master;
GO
-- 辅助库主密钥，密码可与主库一致或单独设置
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '这里填写密码';
GO
-- 创建辅助库证书
CREATE CERTIFICATE Secondary_Server_Cert 
WITH SUBJECT = 'Secondary Server Certificate for AlwaysOn',
EXPIRY_DATE = '2099-12-31';
GO
-- 备份辅助库证书到共享目录
BACKUP CERTIFICATE Secondary_Server_Cert 
TO FILE = 'C:\AlwaysOn_Certs\Secondary_Server_Cert.cer';
GO
-- 创建辅助库端点，端口需与主库一致
CREATE ENDPOINT AG_Endpoint
AUTHORIZATION sa
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = CERTIFICATE Secondary_Server_Cert,
    ENCRYPTION = REQUIRED ALGORITHM AES
);
GO

3.互换证书并授权访问

将主库备份的证书拷贝到辅助库，辅助库备份的证书拷贝到主库，然后相互导入对方证书，并授予端点的连接权限。

• 主库导入辅助库证书并授权：

  USE master;
  GO
  -- 创建登录名用于辅助库访问
  CREATE LOGIN Secondary_Server_Login WITH PASSWORD = '这里填写密码';
  CREATE USER Secondary_Server_User FOR LOGIN Secondary_Server_Login;
  GO
  -- 导入辅助库证书，关联创建的用户
  CREATE CERTIFICATE Secondary_Server_Cert
  AUTHORIZATION Secondary_Server_User
  FROM FILE = 'C:\AlwaysOn_Certs\Secondary_Server_Cert.cer';
  GO
  -- 授予辅助库登录名访问端点的权限
  GRANT CONNECT ON ENDPOINT::AG_Endpoint TO Secondary_Server_Login;
  GO

• 辅助库导入主库证书并授权，脚本类似，只需替换对应名称：

USE master;
GO
CREATE LOGIN Primary_Server_Login WITH PASSWORD = '这里填写密码';
CREATE USER Primary_Server_User FOR LOGIN Primary_Server_Login;
GO
CREATE CERTIFICATE Primary_Server_Cert
AUTHORIZATION Primary_Server_User
FROM FILE = 'C:\AlwaysOn_Certs\Primary_Server_Cert.cer';
GO
GRANT CONNECT ON ENDPOINT::AG_Endpoint TO Primary_Server_Login;
GO

 

如果查询到同名或同端口的端点，执行删除命令（替换为你的冲突端点名称）：

-- 先停止端点（避免删除时占用）
ALTER ENDPOINT [冲突端点名称] STATE = STOPPED;
GO
-- 删除端点（替换为你的冲突端点名称）
DROP ENDPOINT [冲突端点名称];
GO