# 启用透明数据加密(TDE)
USE master;
GO

# 创建主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘FgEdu123!@#’;
GO

# 创建证书
CREATE CERTIFICATE fgedu_tde_cert WITH SUBJECT = ‘FGEDU TDE Certificate’;
GO

# 备份证书（重要，用于恢复）
BACKUP CERTIFICATE fgedu_tde_cert TO FILE = ‘\fgedu1\backup\fgedu_tde_cert.cer’
WITH PRIVATE KEY (
FILE = ‘\fgedu1\backup\fgedu_tde_cert.pvk’,
ENCRYPTION BY PASSWORD = ‘FgEdu123!@#’
);
GO

# 为数据库启用TDE
USE fgedudb;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE fgedu_tde_cert;
GO

ALTER DATABASE fgedudb SET ENCRYPTION ON;
GO

# 检查TDE状态
SELECT
db.name,
db.is_encrypted,
dek.encryption_state,
dek.encryption_state_desc
FROM sys.databases db
JOIN sys.dm_database_encryption_keys dek ON db.database_id = dek.database_id;
GO

name is_encrypted encryption_state encryption_state_desc
——— ————- —————- ———————-
fgedudb 1 3 Encryption on

# 配置Always Encrypted
# 在SQL Server Management Studio中配置

# 1. 创建列主密钥
# 2. 创建列加密密钥
# 3. 加密列

# 使用PowerShell创建列主密钥
New-SqlColumnMasterKey -Name “CMK_Auto1” -InputObject $database -CryptographicProviderName “MSSQL_CERTIFICATE_STORE” -KeyPath “CurrentUser/My/” -AllowEnclaveComputations

# 使用T-SQL查看加密状态
SELECT
c.name AS column_name,
t.name AS table_name,
c.is_encrypted,
c.encryption_type_desc,
c.encryption_algorithm_name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.is_encrypted = 1;
GO

column_name table_name is_encrypted encryption_type_desc encryption_algorithm_name
————– ————– ————- ——————– ————————
salary fgedu_users 1 Deterministic AEAD_AES_256_CBC_HMAC_SHA_256
email fgedu_users 1 Randomized AEAD_AES_256_CBC_HMAC_SHA_256

# 创建审计
CREATE SERVER AUDIT fgedu_server_audit
TO FILE (
FILEPATH = ‘\fgedu1\audit\’,
MAXSIZE = 100 MB,
MAX_ROLLOVER_FILES = 10,
RESERVE_DISK_SPACE = OFF
);
GO

# 启用审计
ALTER SERVER AUDIT fgedu_server_audit WITH (STATE = ON);
GO

# 创建数据库审计规范
USE fgedudb;
GO

CREATE DATABASE AUDIT SPECIFICATION fgedu_db_audit_spec
FOR SERVER AUDIT fgedu_server_audit
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.fgedu_users BY PUBLIC),
ADD (EXECUTE ON SCHEMA::dbo BY PUBLIC);
GO

# 启用数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION fgedu_db_audit_spec WITH (STATE = ON);
GO

# 查看审计日志
SELECT
event_time,
action_id,
succeeded,
session_id,
server_principal_name,
database_principal_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘\fgedu1\audit\fgedu_server_audit*’, DEFAULT, DEFAULT);
GO

# 1. 禁用sa账户
ALTER LOGIN sa DISABLE;
GO

# 2. 重命名sa账户（可选）
ALTER LOGIN sa WITH NAME = [fgedu_sa_disabled];
GO

# 3. 启用密码策略
ALTER LOGIN fgedu WITH CHECK_POLICY = ON, CHECK_EXPIRATION = ON;
GO

# 4. 限制登录失败次数
ALTER LOGIN fgedu WITH PASSWORD_POLICY = ON, FAILED_LOGIN_ATTEMPTS = 5, PASSWORD_LOCKOUT_DURATION = 30;
GO

# 5. 配置数据库权限
# 移除不必要的数据库角色成员
EXEC sp_droprolemember ‘db_owner’, ‘guest’;
GO

# 6. 启用防火墙规则
# 在Windows防火墙中添加SQLServer端口规则
netsh advfirewall firewall add rule name=”SQL Server” dir=in action=allow protocol=TCP localport=1433

# 7. 配置IP安全策略
# 限制只允许特定IP访问SQLServer