In my recent travels, I've had to do a bit of discovery work.  Specifically, if we move a DB from one server to another, what will break, and how?

SQL Profiler is great at telling me who's doing what with a particular DB.  With it, I can figure out which SQL clients need their connection strings modified.  Great news!  But here's an interesting twist.  In all the Profiles I captured, I noticed that some of the UPDATE and INSERT statements contained references to UNC paths.  Wouldn't you know it, the paths refer to the DB server that's being decommissioned.

So even if we successfully moved the DB and reconfigured its clients, the application would still break because it would query the DB and get a path that didn't exist anymore.  Well, that's nice to know.  But I wanted to know if there was anything else, anywhere, within the DB that made reference to that server.

I found a most excellent procedure here:  http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions–1#2  The code trawls through and returns the table and column names that contain the string you're looking for.  It's enough to point you in the right direction.

If you don't feel like trawling through the entire post, here's the relevant TSQL  Call it by running a command like:

exec FindMyData_string 'google', 0

CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON
 
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
 
    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = 'Base Table'
            And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
 
 
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
 
SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                            THEN 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       = ''' + @DataToFind + '''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            ELSE 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       Like ''%' + @DataToFind + '%''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            END,
        @PARAMETERS = '@DataExists Bit OUTPUT',
        @i = 1
 
SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp
 
WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i
 
 
        PRINT @SQL
        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
 
        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
 
        SET @i = @i + 1
    END
 
SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

Shamelessly lifted from http://www.sqlteam.com/article/finding-the-biggest-tables-in-a-database, with thanks.  😀

/**************************************************************************************
*
*  BigTables.sql
*  Bill Graziano (SQLTeam.com)
*  graz@sqlteam.com
*  v1.1
*
**************************************************************************************/

declare @id int  
declare @type character(2)  
declare @pages int  
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB  dec(15,0)

create table #spt_space
(
objid  int null,
rows  int null,
reserved dec(15) null,
data  dec(15) null,
indexp  dec(15) null,
unused  dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

 /* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
  select objid = @id, sum(reserved)
   from sysindexes
    where indid in (0, 1, 255)
     and id = @id

 select @pages = sum(dpages)
   from sysindexes
    where indid < 2
     and id = @id
select @pages = @pages + isnull(sum(used), 0)
  from sysindexes
   where indid = 255
    and id = @id
update #spt_space
  set data = @pages
where objid = @id

 /* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
  set indexp = (select sum(used)
    from sysindexes
    where indid in (0, 1, 255)
    and id = @id)
       - data
  where objid = @id

 /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
  set unused = reserved
    - (select sum(used)
     from sysindexes
      where indid in (0, 1, 255)
      and id = @id)
  where objid = @id

 update #spt_space
  set rows = i.rows
   from sysindexes i
    where i.indid < 2
    and i.id = @id
    and objid = @id

 fetch next from c_tables
into @id
end

select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

from  #spt_space, master.dbo.spt_values d
where  d.number = 1
and  d.type = 'E'
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

My last few posts about SQL have been about how to exploit a SQL box within the confines of a company network.  I wrote about xp_cmdshell and how it can be combined with a poorly-configured SQL box to produce some very unpleasant results.  This week, someone at work (an apps developer, of course), wanted me to publish a SQL box to the internet.  My answer was an emphatic NO, given this particular app person's history for writing crap code.  This person wanted our network guys to poke a hole through the firewall, sending TCP 1433 to the SQL box.  Talk about a disaster waiting to happen.

Long-time followers of my blog know that I have a Masters degree in Networking and Systems Administration.  One of the subjects of that degree was supposedly about Linux, but actually turned out to be more about security.  At the time I was running an e-commerce site, so I had an interest in securing that site.  Learning about my own site, the server it ran on, the applications that ran on it etc was very enlightening.  I was able to see through the web server logs that my server was being hit 24/7 by automated attack scripts.

The scripts weren't really all that interesting; mostly they were remote file inclusion probes.  Some contained URLs to files on servers that had already been exploited.  I downloaded these files and most of them were pretty stock-standard footprinting scripts.  The sort of thing that details what OS the compromised system is running, what version of its components (eg SSH, which at the time had a pretty nasty vulnerability) etc.  One file contained a huge script that created an IRC bot network.  It was actually fascinating.

But that's not what this post is about.  All of this study and research got me onto SQL injection vulnerabilities.  At the time, there was a poorly-coded osCommerce module called something like Customer Testimonials.  It did not sanitise its inputs, so a properly-crafted URL could reveal information contained within osCommerce's tables…. including credit card details, if the site operator was silly enough to save them (BTW, this is a fundamental breach of PCI standards, which I'll not go into, but is worth learning about if you're interested in e-commerce).  I set up a test VM running osCommerce plus the vulnerable version of the testimonials plugin, and sure enough, I was able to exploit it to view data from osC's tables.

Remote File Inclusion attacks are pretty easy to defend against.  Tell the web server (or relevant module eg PHP) not to run content from URLs or remote file systems.  Defending against SQL Injection attacks can be more complicated.  At its worst, it means checking hundreds of lines of code for silly things like (pseudo-code): 

$query = content of web form or specially-crafted URL

execute $query

There are server-level defences to filter out SQL injection attacks, which I won't go into here, but the reality is that bad code will always be bad code.  And bad coders often don't know their code is bad until long after it's in the wild.  With that in mind, and bearing in mind some of the xp_cmdshell exploits I demonstrated in my previous posts, have a wander over to this page.  What I really like about some of the stuff demonstrated here is that one of the examples (example 8) leverages bad SQL security to inject a file, which in turn can be run to perform some malicious task.. or even better, run as part of an IRC botnet.  The possibilities really are endless.  Click some of the links at the bottom of that page to read some very interesting articles on SQL injection attacks.

Bad configuration within a corporate network is one thing.  Sure, it sucks, but it can usually be contained (perhaps by design of other systems such as firewalls, content filters etc).  An internet-facing server, once compromised, can be used to do some pretty bad stuff.  Anyone who thinks publishing a SQL box to the internet consists of only poking a firewall hole on port 1433 needs to rethink their approach to system security.  Actually, anyone who thinks whacking a firewall in front of an internet-facing server will magically protect it.. think again.

What I find really interesting about SQL injection attacks is that they are a multi-layered thing.  They rely on poorly-configured web servers, crappy code, badly-configured SQL boxes etc.  But what's really cool is that the SQL boxes themselves don't need to be presented to the internet.  The only requirement is that a the web server (or more generically, the application server) have connectivity to the SQL box.  Hiding those SQL boxes on a private subnet won't help.  Hiding them behind an internal firewall won't help.  Hiding them on protected VLANs won't help.  It won't matter.  Because once the web server manages to talk to the SQL box, once the web-server-generated query traverses the wire from web box to SQL box, the scope for damage is already present.

What I'm saying, folks, is that security is a multi-layered thing.  I don't claim to have all the answers.  I'm not a security expert.  But I am security-aware.  All good sysadmins ask questions about the code that's running on their boxes.  What does this code do?  Why does it do it that way?  Can it be done in a more secure fashion?  What does it really need access to?  Can the code be run isolated?  Do we have sufficient logging to keep track of breaches?  If this box is compromised, what else can it access?  If there is a breach, what is the scope of information disclosure?  There's so many questions to ask.  Don't be afraid to ask them.  If your apps people can't tell you this stuff off the top of their heads, stamp a great big red DENIED across the request.

I've been struggling to get my head around schemas in SQL 2008.  BOL has this incredibly useful piece of wisdom to impart:  "Database schemas act as namespaces or containers for objects, such as tables, views, procedures, and functions, that can be found in the sys.objects catalog view".  Erm.  OK.

That actually didn't help me much.  What do they do?  What are they for?  Why not just apply permissions directly?

Finally, I read this article.  I know Googling will provide lots of hits on "SQL 2008 schema", and all of them say much the same as this article, but for some reason, this article made it all click.  Schemas are roughly analagous to folders in a file system.  An individual file can only live within a single folder at any given time.  A folder can contain lots of files.  In order to apply permissions within the file system, one doesn't waste one's time by applying permissions against each and every file within a folder.  Permissions are applied at the folder level, and the files within the folder inherit those permissions.  That's kind of how SQL schemas work.  I think.

I'm still getting it all straight.  But I think this article does a good job of putting it together.  Unless I've got it all wrong.  In which case, it's a terrible article.

After my previous two posts about xp_cmdshell exploits, a friend asked this question:

"Same issue if the SQL service account is a domain admin (rather than Local system)?"

My immediate answer to this is, yes – the Domain Admins group is a member of the local Administrators group on every member machine by default.  So it stands to reason they have the keys to the city.  What is interesting about this scenario, however, is not just the risk associated with compromising the local machine.  It's the risk associated with being able to compromise every other member of the domain – including Domain Controllers.

If this doesn't give you a reason to check your SQL Server's service accounts, nothing will.  Consider this scenario, one which I consider to be a near-perfect storm of stupidity:

1. The SQL Server service account is configured to use an account that is a member of the Domain Admins group.
2. The SQL Proxy account is configured with an account that is a member of Domain Admins – perhaps the same account as the SQL Server service account
3. xp_cmdshell is enabled.

You'll recall that xp_cmdshell lets you run pretty much anything under the context of the SQL Server service account (if you are a sysadmin) or the context of the SQL Proxy account (if you're not in the sysadmin role).  As you saw in my last couple demos, this seems to be limited to commands that run non-interactively.  But that doesn't mean there's any less risk associated with it.

Let's first of all try this as someone who's a member of the Domain Admins group.

xp_cmdshell 'whoami.exe'

Let's see if we can use DSRM to trash an OU in Active Directory.

xp_cmdshell '\\dc1\c$\windows\system32\dsrm.exe -subtree -noprompt -c OU=Marketing,DC=sql,DC=local'

Those of you familiar with Windows Server 2008 R2 will no doubt be pleased that objects created in AD have this option selected by default:

If you're using an ealier revision of Windows for your DCs, or if you have not yet moved up to the appropriate Functional Level, this option won't be available to you.  Let's see what happens if you do not have this option enabled:

xp_cmdshell '\\dc1\c$\windows\system32\dsrm.exe -subtree -noprompt -c OU=Marketing,DC=sql,DC=local'

It should not really be a surprise that someone who is a Domain Admin, running TSQL commands on a box that is poorly-configured, can do this.  I wonder about a regular user?

Don't forget that for that to happen, we need to create a SQL login associated with that user, the user needs to be granted permission to run xp_cmdshell, and we need to configure the SQL Proxy account.  And because we're talking about a very silly administrator, the Proxy account is configured to use an account that is a member of Domain Admins.

use master
go
EXEC sp_xp_cmdshell_proxy_account 'SQL\administrator','password'

grant execute on xp_cmdshell to [sql\gpotest]

Now when the user runs the whoami command, they see this:

Yay!  A regular unprivileged account can now run commands under the context of the Domain's administrator!  Great news!

And just to prove it…

xp_cmdshell '\\dc1\c$\windows\system32\dsrm.exe -subtree -noprompt -c OU=Marketing,DC=sql,DC=local'

Oh, fuck.

So, your boss is in your ear about how all the people in the Marketing department have lost their user accounts – and all the things they want to access with those accounts.  Your boss wants to know who did this!  Let's look at the Domain Controller logs:

The event logs are clear.  It's not the unprivileged account that deleted the user accounts.  It was the Administrator account.  Sure, the logs will tell you.. with a bit of hunting.. that the Administrator account was logged onto the SQL box, but by then, it won't really matter.  The finger will still be pointing at the person who's supposed to be safeguaring the Administrator account.  And that's not Mr Unprivileged.  No.  It's a naive sysadmin who should've known better than to configure his SQL box to use elevated accounts.

This could've easily been prevented.  Even if the SQL Server service account was privileged (which it should not be, but for the purposes of this discussion….), using an unprivileged account for the SQL Proxy account would've prevented it:

xp_cmdshell 'whoami.exe'

xp_cmdshell '\\dc1\c$\windows\system32\dsrm.exe -subtree -noprompt -c OU=Marketing,DC=sql,DC=local'

The regular user account can't access \\dc1\c$\windows\system32.  A good thing, too.  But what if this person is really determined, and makes a copy of dsrm (and its supporting files) somewhere else?

xp_cmdshell '\\fileshare\openshare\dsrm.exe -subtree -noprompt -c OU=Marketing,DC=sql,DC=local'

In spite of the poorly-configured service account, this particular disaster was averted because the Proxy account is unprivileged.

Windows, SQL Server, and to an extent, the tools that come with them, prevent us from doing overtly stupid things – so long as we follow a few basic rules.  If we configure our servers loosely, against best practice, ignoring all conventional advice… then we really do deserve what we get.  In the words of the great Forrest Gump, stupid is as stupid does.