It's well known that the stored procedure encryption in SQL Server
2000 has been cracked, but I've been unable to find a discussion of the
algorithm used and what its weaknesses are. I did some digging and found
that not only can the key be retrieved by anyone with sa privileges (as
dOMNAR has so aptly demonstrated with his dSQLSRVD utility), but the algorithm
is incorrectly implemented, making both key retrieval and sa privileges
unneccessary.
So here's how stored procedure (and view and trigger) encryption works on
SQL Server 2000:
1. Take the the database's GUID (generated when the db is created), the
object id (from sysobjects) and the colid (from syscomments) and
concatenate them.
2. Hash the key using SHA.
3. Use the SHA hash as an RC4 key, generate a sequence of bytes equal in
length to the stored procedure text.
4. XOR this stream of bytes against the stored procedure text.
This is a bit short on detail and is based on a dimly remembered
conversation with an MS employee I bummed a cigarette off while visiting
the campus, so it may not be 100 % accurate.
Anyway, there are 2 ways to set about recovering the plaintext. One is to
retrieve the components of the key (the guid is retrievable through dbcc
dbinfo, but you have to be sa to run that command) and this is the
approach taken by dSQLSRVD.
The second option is to find a way to encrypt your own plaintext with the
same key. If you can do this, the encryption algorithm degenerates to
simple XOR encryption with a reusable pad.
It turns out that it's trivial to do this thanks to the "ALTER PROCEDURE"
statement. Kind of makes you wonder why Microsoft chose to waste cpu cycles
with SHA and RC4 since it doesn't buy any extra security.
Anyway, here's some sample code:
SET NOCOUNT ON
CREATE TABLE #tempcomments (
ID int PRIMARY KEY NOT NULL
,ctext nvarchar(4000) NOT NULL
)
GO
CREATE PROCEDURE bob
WITH ENCRYPTION
AS
PRINT 'I encrypted this procedure and forgot to check the source into cvs!'
PRINT 'Now I don''t work here any more and you can''t find me!'
GO
INSERT INTO #tempcomments
SELECT 1, ctext FROM syscomments WHERE id = object_id('bob')
GO
ALTER PROCEDURE bob
WITH ENCRYPTION
AS
------------------------------------------------------------------------------
------------------------------------------------------------------------------
print 'I know a secret.'
GO
INSERT INTO #tempcomments
SELECT 2, ctext FROM syscomments WHERE id = object_id('bob')
GO
DECLARE @origcryptstr nvarchar(4000)
,@origplainstr nvarchar(4000)
,@knownplainstr nvarchar(4000)
,@knowncryptstr nvarchar(4000)
DECLARE @length int
,@counter int
SELECT @origcryptstr = ctext FROM #tempcomments WHERE ID = 1
SELECT @knowncryptstr = ctext FROM #tempcomments WHERE ID = 2
SELECT @knownplainstr = N'CREATE PROCEDURE bob
WITH ENCRYPTION
AS
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
print ''I know a secret.''
'
set @length = datalength(@origcryptstr)
set @origplainstr = replicate(N'A', (@length / 2))
set @counter = 1
while (@counter <= (@length / 2))
begin
SELECT @origplainstr = stuff(@origplainstr, @counter, 1,
NCHAR(UNICODE(substring(@origcryptstr, @counter, 1)) ^
(UNICODE(substring(@knowncryptstr, @counter, 1)) ^
UNICODE(substring(@knownplainstr, @counter, 1)))))
set @counter = @counter + 1
end
select @origplainstr
exec('drop procedure bob')
exec(@origplainstr)
GO
drop table #tempcomments
GO
Note that at the end I replace the second version of the bob procedure
with
the original. If you're using this approach on a production code, don't
leave that out.
--Shoeboy
Randal Schwartz is my bitch:
http://slashdot.org/comments.pl?sid=11547&cid=308659
This archive was generated by hypermail 2b30 : Tue Dec 18 2001 - 09:16:42 PST