等保测评命令——SQL server

五、数据安全（8.1.4.5/8.1.4.8）

5.1 加密配置检查

-- 49. 查看透明数据加密(TDE)状态
SELECT
name AS[数据库名],
is_encrypted AS[已加密],
CASE is_encrypted
WHEN1THEN'TDE已启用'
ELSE'未加密'
ENDAS[加密状态],
compatibility_level
FROM sys.databases
ORDERBY is_encrypted DESC, name;

-- 50. 查看TDE加密详细配置（对加密数据库）
USE[master];
SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length,
dm.server_name,
dm.create_date
FROM sys.databases db
LEFTJOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id
WHERE db.is_encrypted =1OR dm.encryption_state ISNOTNULL;

-- 51. 查看列主密钥（Always Encrypted）
SELECT
name AS[密钥名],
column_encryption_key_id,
create_date,
modify_date
FROM sys.column_master_keys;

-- 52. 查看列加密密钥
SELECT
name AS[密钥名],
column_encryption_key_id,
create_date
FROM sys.column_encryption_keys;

-- 53. 查看加密列定义
SELECT
t.name AS[表名],
c.name AS[列名],
cek.name AS[加密密钥],
cmk.name AS[主密钥],
ct.name AS[加密类型]
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.column_encryption_keys cek ON c.column_encryption_key_id = cek.column_encryption_key_id
JOIN sys.column_encryption_key_values cekv ON cek.column_encryption_key_id = cekv.column_encryption_key_id
JOIN sys.column_master_key_definitions cmk ON cekv.column_encryption_key_id = cmk.column_encryption_key_id
JOIN sys.symmetric_keys ct ON cekv.column_encryption_key_id = ct.symmetric_key_id
WHERE c.encryption_type ISNOTNULL;

-- 54. 查看连接加密状态统计
SELECT
encrypt_option,
COUNT(*)AS[连接数],
CAST(COUNT(*)*100.0/SUM(COUNT(*))OVER()ASDECIMAL(5,2))AS[百分比]
FROM sys.dm_exec_connections
GROUPBY encrypt_option;

-- 55. 查看非加密连接详情（风险）
SELECT
s.session_id,
s.login_name,
s.program_name,
s.host_name,
c.client_net_address,
c.net_transport,
c.encrypt_option
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE c.encrypt_option ='FALSE'
AND c.net_transport ='TCP'
AND s.is_user_process =1;

5.2 备份与恢复安全

-- 56. 查看数据库备份历史
SELECT
database_name,
backup_start_date,
backup_finish_date,
typeAS[备份类型],-- D=完整, I=差异, L=日志
backup_size/1024/1024AS[大小MB],
is_password_protected AS[密码保护],
is_encrypted AS[已加密],
encryptor_thumbprint,
recovery_model
FROM msdb.dbo.backupset
ORDERBY backup_start_date DESC;

-- 57. 查看备份文件位置
SELECT
database_name,
physical_device_name AS[备份路径],
type,
backup_start_date
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHEREtype='D'
ORDERBY backup_start_date DESC;

-- 58. 查看数据库恢复模式
SELECT
name,
recovery_model_desc AS[恢复模式],
log_reuse_wait_desc AS[日志等待]
FROM sys.databases;

-- 59. 查看AlwaysOn可用性组（高可用）
SELECT
group_name,
replica_server_name,
role_desc,
synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states;

-- 60. 查看日志传送配置
SELECT
primary_server,
primary_database,
backup_directory,
copy_job_name,
restore_job_name,
monitor_server
FROM msdb.dbo.log_shipping_primary_databases;