Encrypting an SP

[Sminair]

Can we encrypt a stored procedure using xp_crypt ? pls guide me on the same and if so do add a method of decrypting it also.
Thnks in advance.

 

[JamesLean]

You can use the WITH ENCRYPTION clause in your CREATE PROC statement to store the SP text in encrypted form. However this is one way (you can't decrypt the stored text) so you need to save the CREATE PROC script somewhere externally in order to make any changes.

--James

 

[Sminair]

My problem is exactly that ...james. If I encrypt the sp using WITH Encryption the code for decrypting is also nowadays readily available in the website. Is there some other secure way??like xp_crypt which will always need a password for decrypting ??

 

[JamesLean]

I doubt using another encryption technique would work as SQL Server wouldn't be able to recognise the text in syscomments.

BTW, this is similar to the response you already got in your previous post: thread183-1081384. Is there a reason you posted again?

--James

 

[Sminair]

james thnks for your reply .
I have another problem regarding this decrypting sp . I downloaded the code for the sp which decrypted the encrypted sp and gave some value add to it . What i actually intended was that my decrypting sp will also except a password along with the sp name (which has to be decrypted) as a parameter. i encrypt the pwd inside the sp using pwdencrypt function and then compare it with the encrypted password in my table using the function pwdcompare and if only they match it shld encrypt ...
But this logic is not working here as each encryption wil produce a different result . I am pasting the code for the decryting sp. Can u pls check whether u can help me out.

CREATE PROCEDURE DECRYPTSP2K (@objName varchar(50),@userpwd nchar(20))

AS
DECLARE @a nvarchar(4000), @b nvarchar(4000), @c nvarchar(4000), @d nvarchar(4000), @i int, @t bigint ,@pwd varbinary(255)
DECLARE @userlogin varbinary(255)

Select @pwd =convert(varbinary(255),pwdencrypt(@userpwd))
print @pwd
--select @userlogin =userid from userlogin where username ='Smitha'
--this can be selected from a table also
if @pwd = '0x0100192ACD19BDD93BB0F7A98ABD7D41BD7CD50E27DDF70550F66ED824EFDB3FEF240D99EE3D7F5868032F09A52C'

Begin
print 1
--get encrypted data
SET @a=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))

SET @b='ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)

EXECUTE (@b)
--get encrypted bogus SP
SET @c=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))

SET @b='CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
--start counter
SET @i=1
--fill temporary variable
SET @d = replicate(N'A', (datalength(@a) / 2))

--loop
WHILE @i<=datalength(@a)/2
BEGIN
--xor original+bogus+bogus encrypted
SET @d = stuff(@d, @i, 1,
NCHAR(UNICODE(substring(@a, @i, 1)) ^
(UNICODE(substring(@b, @i, 1)) ^
UNICODE(substring(@c, @i, 1)))))
SET @i=@i+1
END

--drop original SP
EXECUTE ('drop PROCEDURE '+ @objName)
--remove encryption
--try to preserve case
SET @d=REPLACE((@d),'WITH ENCRYPTION', '')

SET @d=REPLACE((@d),'With Encryption', '')


SET @d=REPLACE((@d),'with encryption', '')

IF CHARINDEX('WITH ENCRYPTION',UPPER(@d) )>0
SET @d=REPLACE(UPPER(@d),'WITH ENCRYPTION', '')
--replace SP
execute( @d)

end
else
print 'The credentials supplied are not valid'