SQL Server身份验证模式步骤和示例代码

SQL Server 身份验证模式详解

1. 身份验证模式概述

SQL Server 支持两种身份验证模式：

1.1 Windows 身份验证模式

使用 Windows 账户凭据
无需单独管理 SQL Server 登录名
更安全，支持 Kerberos 身份验证

1.2 混合模式 (SQL Server 和 Windows 身份验证)

同时支持 Windows 身份验证和 SQL Server 身份验证
SQL Server 身份验证使用用户名/密码
适合需要跨平台访问的场景

2. 设置身份验证模式的步骤

2.1 安装时设置

在 SQL Server 安装过程中选择身份验证模式：

选择 "Windows 身份验证模式" 或 "混合模式"
如果选择混合模式，需要为 sa 账户设置密码

2.2 安装后修改身份验证模式

方法1：使用 SQL Server Management Studio (SSMS)

连接到 SQL Server 实例右键点击服务器 → 属性选择 "安全性" 页更改 "服务器身份验证" 重启 SQL Server 服务

方法2：使用 T-SQL

USE [master]
GO
EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'LoginMode', 
    REG_DWORD, 
    2  -- 2 表示混合模式，1 表示 Windows 身份验证
GO
-- 重启 SQL Server 服务使更改生效

3. 创建和管理 SQL Server 登录名

3.1 创建 SQL Server 登录名

-- 创建 SQL Server 登录名
CREATE LOGIN [TestUser] WITH PASSWORD = 'StrongPassword123!',
    DEFAULT_DATABASE = [master],
    CHECK_EXPIRATION = ON,
    CHECK_POLICY = ON

-- 创建带选项的登录名
CREATE LOGIN [AppUser] WITH PASSWORD = 'App@Pass123',
    DEFAULT_DATABASE = [YourDatabase],
    DEFAULT_LANGUAGE = [us_english],
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = OFF

3.2 修改登录名属性

-- 更改密码
ALTER LOGIN [TestUser] WITH PASSWORD = 'NewStrongPassword456!'

-- 启用/禁用登录名
ALTER LOGIN [TestUser] DISABLE
ALTER LOGIN [TestUser] ENABLE

-- 解锁被锁定的账户
ALTER LOGIN [TestUser] WITH CHECK_POLICY = OFF
ALTER LOGIN [TestUser] WITH CHECK_POLICY = ON

3.3 删除登录名

DROP LOGIN [TestUser]

4. 创建数据库用户并分配权限

USE [YourDatabase]
GO

-- 创建数据库用户
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO

-- 分配角色
EXEC sp_addrolemember 'db_datareader', 'TestUser'
EXEC sp_addrolemember 'db_datawriter', 'TestUser'
GO

-- 授予特定权限
GRANT EXECUTE ON [dbo].[YourStoredProcedure] TO [TestUser]
GRANT SELECT ON [dbo].[YourTable] TO [TestUser]
GRANT INSERT, UPDATE ON [dbo].[AnotherTable] TO [TestUser]
GO

-- 拒绝权限
DENY DELETE ON [dbo].[SensitiveTable] TO [TestUser]
GO

5. 完整示例：设置混合模式并创建用户

5.1 启用混合模式（需要管理员权限）

-- 检查当前身份验证模式
SELECT 
    CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
        WHEN 1 THEN 'Windows Authentication'
        WHEN 0 THEN 'Mixed Mode'
    END AS [Authentication Mode]

-- 启用混合模式（需要重启服务）
EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'LoginMode', 
    REG_DWORD, 
    2

5.2 完整的用户创建示例

-- 1. 创建登录名
CREATE LOGIN [WebAppUser] 
WITH PASSWORD = 'WebApp@Secure#2024',
    DEFAULT_DATABASE = [AppDatabase],
    DEFAULT_LANGUAGE = [us_english],
    CHECK_EXPIRATION = ON,
    CHECK_POLICY = ON

-- 2. 在特定数据库中创建用户
USE [AppDatabase]
GO

CREATE USER [WebAppUser] FOR LOGIN [WebAppUser]
GO

-- 3. 分配数据库角色
ALTER ROLE [db_datareader] ADD MEMBER [WebAppUser]
ALTER ROLE [db_datawriter] ADD MEMBER [WebAppUser]
ALTER ROLE [db_executor] ADD MEMBER [WebAppUser]  -- 如果存在此角色

-- 4. 授予额外权限
GRANT VIEW DEFINITION TO [WebAppUser]
GRANT EXECUTE TO [WebAppUser]
GO

-- 5. 创建架构并分配权限
CREATE SCHEMA [WebApp]
GO

ALTER USER [WebAppUser] WITH DEFAULT_SCHEMA = [WebApp]
GO

GRANT CONTROL ON SCHEMA::[WebApp] TO [WebAppUser]
GO

6. 连接字符串示例

6.1 SQL Server 身份验证连接字符串

// C# / .NET
string connectionString = "Server=YourServerName;Database=YourDatabase;User Id=TestUser;Password=StrongPassword123!;";

// 包含更多选项的连接字符串
string connString = @"Server=YourServerName\InstanceName;
                     Database=YourDatabase;
                     User Id=TestUser;
                     Password=StrongPassword123!;
                     MultipleActiveResultSets=True;
                     Connection Timeout=30;
                     Encrypt=True;
                     TrustServerCertificate=False";

6.2 Python 连接示例

import pyodbc

# SQL Server 身份验证
conn_str = (
    "Driver={SQL Server};"
    "Server=YourServerName;"
    "Database=YourDatabase;"
    "UID=TestUser;"
    "PWD=StrongPassword123!;"
)

conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

7. 安全最佳实践

7.1 密码策略

-- 强制密码策略（需要 Windows 密码策略）
CREATE LOGIN [SecureUser] WITH 
    PASSWORD = 'ComplexP@ssw0rd!2024',
    CHECK_POLICY = ON,
    CHECK_EXPIRATION = ON,
    MUST_CHANGE = ON  -- 首次登录时必须更改密码

7.2 定期维护脚本

-- 检查过期的登录名
SELECT 
    name,
    create_date,
    modify_date,
    is_expired,
    is_disabled
FROM sys.sql_logins
WHERE is_expired = 1 OR is_disabled = 1

-- 查找弱密码（空密码或简单密码）
SELECT name 
FROM sys.sql_logins 
WHERE PWDCOMPARE('', password_hash) = 1
   OR PWDCOMPARE(name, password_hash) = 1

7.3 备份登录名脚本

-- 生成创建登录名的脚本
SELECT 
    'CREATE LOGIN [' + name + '] ' +
    'WITH PASSWORD = 0x' + CONVERT(VARCHAR(MAX), password_hash, 2) + ' HASHED, ' +
    'SID = 0x' + CONVERT(VARCHAR(MAX), sid, 2) + ', ' +
    'DEFAULT_DATABASE = [' + default_database_name + '], ' +
    'DEFAULT_LANGUAGE = [' + default_language_name + '], ' +
    'CHECK_EXPIRATION = ' + 
        CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END + ', ' +
    'CHECK_POLICY = ' + 
        CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
FROM sys.sql_logins
WHERE name NOT IN ('sa', '##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##')

8. 故障排除

8.1 常见问题及解决方案

-- 1. 无法连接到 SQL Server
-- 检查 SQL Server 是否正在运行
-- 检查 TCP/IP 协议是否启用
-- 检查防火墙设置

-- 2. 登录失败
-- 检查用户名和密码是否正确
-- 检查账户是否被禁用
SELECT name, is_disabled 
FROM sys.sql_logins 
WHERE name = 'YourLoginName'

-- 3. 重置 sa 密码
ALTER LOGIN sa WITH PASSWORD = 'NewSAPassword!'
ALTER LOGIN sa ENABLE

-- 4. 检查身份验证模式
EXEC xp_loginconfig 'login mode'

-- 5. 查看当前登录信息
SELECT 
    session_id,
    login_name,
    host_name,
    program_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1