SQL Server 2017 Always On AG on Linux（二）SQL Server 证书及权限配置

既然没有了域认证，需要配置证书认证，步骤：

1. 启用S​​QL Server Always On Availability Groups功能
2. 在所有服务器上为SQL Server Always On Availability Groups安装Linux资源代理
3. 在所有Linux服务器启用防火墙上的Always On Availability Group端点端口
4. 在主副本上创建数据库主密钥
5. 创建将用于加密可用性组端点的证书
6. 使用证书为主副本创建Always On Availability Group端点进行身份验证
7. 将证书导出到文件
8. 将证书文件复制到辅助副本
9. 授予证书文件的SQL Server帐户权限
10. 在主副本上创建登录账号以供辅助副本使用
11. 为登录账号创建用户
12. 将步骤5中创建的证书与用户关联
13. 授予登录账号的CONNECT权限

 

1. 所有服务器：启用 hadr 功能（Always On Availability Groups）

/opt/mssql/bin/mssql-conf set hadr.hadrenabled  1 
systemctl restart mssql-server 

2. 所有服务器：为 SQL Server AlwaysOn AG 安装Linux资源代理。

群集资源代理程序 mssql-server-ha 是 Pacemaker 和 SQL Server 之间的接口

yum install -y mssql-server-ha 
yum info mssql-server-ha 

3. 所有服务器：启用防火墙上的 AlwaysOn AG 端点默认端口5022 （或关闭防火墙！）

firewall-cmd --zone=public --add-port=5022/tcp --permanent 
firewall-cmd --reload

SSMS 连接到任意一个实例，[查询] 选项启用 [SQLCMD模式]，批量执行以下脚本

----------------------------------------------------------------------
--#【 server111 副本】
----------------------------------------------------------------------
:CONNECT 192.168.2.111 -U sa -P sa@PWS123456
GO
USE master  
GO
-- 4. 创建数据库主密钥
--（数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥）
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456';  
GO
-- 5. 创建将用于加密可用性组端点的证书
CREATE CERTIFICATE LINUXHA_SQLAG1_cert 
WITH SUBJECT = 'server111 certificate for Availability Group' 
GO
--6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证
CREATE ENDPOINT Endpoint_AvailabilityGroup 
STATE = STARTED 
AS TCP 
(
   LISTENER_PORT = 5022, LISTENER_IP = ALL
)  
FOR DATABASE_MIRRORING 
(
   AUTHENTICATION = CERTIFICATE LINUXHA_SQLAG1_cert, 
   ENCRYPTION = REQUIRED ALGORITHM AES,
   ROLE = ALL
);  
GO
-- 7. 主副本：将证书导出到文件
BACKUP CERTIFICATE LINUXHA_SQLAG1_cert 
TO FILE = '/var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer';  
GO

----------------------------------------------------------------------
--#【 server112 副本】
----------------------------------------------------------------------
:CONNECT 192.168.2.112 -U sa -P sa@PWS123456
GO
USE master  
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456';  
GO 
CREATE CERTIFICATE LINUXHA_SQLAG2_cert
WITH SUBJECT = 'server112 certificate for Availability Group' 
GO  
CREATE ENDPOINT Endpoint_AvailabilityGroup 
STATE = STARTED  
AS TCP
(
LISTENER_PORT = 5022, LISTENER_IP = ALL 
)  
   FOR DATABASE_MIRRORING 
(
   AUTHENTICATION = CERTIFICATE LINUXHA_SQLAG2_cert, 
   ENCRYPTION = REQUIRED ALGORITHM AES,
   ROLE = ALL
); 
GO
BACKUP CERTIFICATE LINUXHA_SQLAG2_cert
TO FILE = '/var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer';  
GO

----------------------------------------------------------------------
--#【 server113 副本】
----------------------------------------------------------------------
:CONNECT 192.168.2.113 -U sa -P sa@PWS123456
GO
USE master  
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456';  
GO 
CREATE CERTIFICATE LINUXHA_SQLAG3_cert
WITH SUBJECT = 'server113 certificate for Availability Group' 
GO  
CREATE ENDPOINT Endpoint_AvailabilityGroup 
STATE = STARTED  
AS TCP
(
LISTENER_PORT = 5022, LISTENER_IP = ALL 
)  
   FOR DATABASE_MIRRORING 
(
   AUTHENTICATION = CERTIFICATE LINUXHA_SQLAG3_cert, 
   ENCRYPTION = REQUIRED ALGORITHM AES,
   ROLE = ALL
); 
GO
BACKUP CERTIFICATE LINUXHA_SQLAG3_cert
TO FILE = '/var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer';  
GO

8. 各服务器上的证书相互传给其他服务器

# server111(192.168.2.111) 上执行
scp /var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer root@server112:/var/opt/mssql/data/
scp /var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer root@server113:/var/opt/mssql/data/

# server112(192.168.2.112) 上执行
scp /var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer root@server111:/var/opt/mssql/data/
scp /var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer root@server113:/var/opt/mssql/data/

# server113(192.168.2.113) 上执行
scp /var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer root@server111:/var/opt/mssql/data/
scp /var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer root@server112:/var/opt/mssql/data/

9. 所有服务器：授予证书文件所有者权限

chown mssql:mssql /var/opt/mssql/data/LINUXHA_SQLAG* 
ll /var/opt/mssql/data/LINUXHA_SQLAG* 

 

10.各实例证书还原及账号授权

SSMS 连接到任意一个实例，查询选项启用[SQLCMD模式]，批量执行以下脚本

----------------------------------------------------------------------
--#【 server111 副本】
----------------------------------------------------------------------
:CONNECT 192.168.2.111 -U sa -P sa@PWS123456
GO
USE master  
GO
-- 11 创建登录账号以供辅助副本使用
CREATE LOGIN login_ag WITH PASSWORD = 'AG@123456';  
GO
-- 12. 为登录账号创建用户
CREATE USER login_ag FOR LOGIN login_ag  
GO
-- 13. 创建其他副本的证书
CREATE CERTIFICATE LINUXHA_SQLAG2_cert  
AUTHORIZATION login_ag  
FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer'  
GO
CREATE CERTIFICATE LINUXHA_SQLAG3_cert  
AUTHORIZATION login_ag  
FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer'  
GO
-- 14. 授予登录账号的CONNECT权限
GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup 
TO [login_ag];   
GO

----------------------------------------------------------------------
--#【 server112 副本】
----------------------------------------------------------------------
:CONNECT 192.168.2.112 -U sa -P sa@PWS123456
GO
USE master  
GO
-- 11 创建登录账号以供辅助副本使用
CREATE LOGIN login_ag WITH PASSWORD = 'AG@123456';  
GO
-- 12. 为登录账号创建用户
CREATE USER login_ag FOR LOGIN login_ag  
GO
-- 13. 创建其他副本的证书
CREATE CERTIFICATE LINUXHA_SQLAG1_cert  
AUTHORIZATION login_ag  
FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer'  
GO
CREATE CERTIFICATE LINUXHA_SQLAG3_cert  
AUTHORIZATION login_ag  
FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer'  
GO
-- 14. 授予登录账号的CONNECT权限
GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup 
TO [login_ag];   
GO

----------------------------------------------------------------------
--#【 server113 副本】
----------------------------------------------------------------------
:CONNECT 192.168.2.113 -U sa -P sa@PWS123456
GO
USE master  
GO
-- 11 创建登录账号以供辅助副本使用
CREATE LOGIN login_ag WITH PASSWORD = 'AG@123456';  
GO
-- 12. 为登录账号创建用户
CREATE USER login_ag FOR LOGIN login_ag  
GO
-- 13. 创建其他副本的证书
CREATE CERTIFICATE LINUXHA_SQLAG1_cert  
AUTHORIZATION login_ag  
FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer'  
GO
CREATE CERTIFICATE LINUXHA_SQLAG2_cert  
AUTHORIZATION login_ag  
FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer'  
GO
-- 14. 授予登录账号的CONNECT权限
GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup 
TO [login_ag];   
GO

暂时配置到这里，接下来将进行 Always On Availability Group 的创建！！

 

 

参考：

Linux 上的 SQL Server

Install and Configure SQL Server 2017 Availability Groups on Linux - Part 2