Encrypt all objects - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Encrypt all objects

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-22 : 08:08:58

Pejman Hashemi writes "I want to encrypt all SQL Server objects before deployment (i.g Stored Procedures and functions). I need a stored procedure to run on my database and it encrypt all objects.

Thanks for any help"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-22 : 11:51:00

couple of comments...

1. ensure your 'master' version of all your code is stored safely outside of SQL Server...encryption is a one-way process...

2. I think that you need to add the 'with encryption' statement to all your procedures.

3. there are examples here of code which will process multiple SQL objects....ie list all procs, etc...have a search (system objects, or schema) and see if one can be adapted to your requirement....

ie....a replace of "Create procedure xxxxxx" with "Create procedure xxxxxx with encryption"....may not be easy or worth the effort unless you have a lot of objects.

sterobhun
Starting Member

12 Posts

Posted - 2002-07-22 : 12:31:06

There is an alternative method to recreating all procs and triggers with 'with encryption', but it involves altering the system tables directly so backup your database first. Also I only tested this on SQL Server 7; it may be very different if you're using 2000.

The first step is to allow to system tables to be modified directly:

exec sp_configure 'allow updates', 0
reconfigure with override

Next, you'll want to get a list of object IDs for everything you want to encrypt. For example, this retrieves the IDs all stored procedures that don't start with 'dt_':

-- type 'P' indicates a stored procedure
select id from sysobjects where type = 'P' and name not like 'dt_%'

The syscomments table holds the text for the stored procedures. The least significant bit of the status field indicates if it encrypted or not, and the next one along from that describes compression status. So to encrypt, we can use the undocumented encrypt() function and add 1 to the status like this:

update syscomments set ctext = encrypt(ctext), status = status + 1
where encrypted = 0 and id in 
(select id from sysobjects where type = 'P' and name not like 'dt_%')

Hope this helps.

Argyle
Yak Posting Veteran

53 Posts

Posted - 2002-07-22 : 13:08:29

Note that the encryption for both SQL 7 and SQL 2000 have been cracked and tools exists on the net to decrypt your SQL objects. So if someone is determined to decrypt your objects they can.

/Argyle

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-22 : 18:31:13

I've taken notice of the comment that SQL encryption has been cracked.

I'm doing an external encryption of passwords stored in my database, but it might improve customer confidence if usernames, email addresses, and other "personal" information were also encrypted.

External encryption of these items would make searches slower, the data longer, and the ASP programming a headache.

So I was interested to read that there is an internal encryption implemented SQL 2000.

If there is a way to encrypt sensitive fields (name, department) without suffering an intolerable performance hit or crippling the ability to search, I'd like to read any paper or example.

SamC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-22 : 20:22:55

You can make your data more secure by simply revoking permissions on sensitive columns, or, even better, revoke permissions on the table itself and then create views to present the data. For instance, a human resources database could store name, address, salary, manager, and benefits information in one table. Two or more views can be created to show only name and manager for regular employees to use, while another can be created for HR personnel that shows all of the columns. This will go a lot further to keep sensitive data secure than encryption.

IMHO encryption is too much of a pain, especially for active databases. There's no way to get around the performance hit, and as you pointed out it can be circumvented. I think a lot of people have a "glamorous" view of encryption because it's associated with classified information and security, and they don't appreciate the pain involved with actually dealing with it.

If you're still interested in encryption, there's a 3rd party tool that's designed to work with SQL Server:

http://www.sqlteam.com/Item.asp?ItemID=6391

Edited by - robvolk on 07/22/2002 20:32:37

sterobhun
Starting Member

12 Posts

Posted - 2002-07-24 : 06:29:09

I was playing around with the encrypt() function in SQL Server 7 this morning and interestingly, it turns out that encrypting an encrypted string (4^n)-1 times will decrypt n bytes of it.


declare @vb varbinary(8000)
declare @i int

set @i = 0
set @vb = 0x123456789ABCDEF
print @vb

while @i < power(4,4)/4 begin
   set @vb = encrypt(encrypt(encrypt(encrypt(@vb))))
   print @vb
   set @i = @i + 1
end

Another thing I noticed is that substrings from the left of the string encrypt to the corresponding substrings from the left of the encrypted string. Example:


N'c'      -> 0x6C89
N'cr'     -> 0x6C89D9ED
N'cre'    -> 0x6C89D9ED5855
N'crea'   -> 0x6C89D9ED5855DC61
N'creat'  -> 0x6C89D9ED5855DC612DE7
N'create' -> 0x6C89D9ED5855DC612DE79ACF

So to decrypt a string you could do something like this:


-- @ctextin is the encrypted string, @ctextout is the decrypted string

declare @char int
declare @pos int
declare @len int
declare @ctextout nvarchar(4000)

set @ctextout = ''
set @len = len(@ctextin)

set @pos = 2
while @pos <= @len begin
   set @char = 0
   while @char < 128 begin
      if encrypt(@ctextout+char(@char))=convert(varbinary(8000),left(@ctextin, @pos)) begin
         set @ctextout = @ctextout+nchar(@char)
         set @char = 129
      end
      set @char = @char + 1
   end
   if @char = 128 begin
      print 'ctextin contains encrypted non-ascii text at pos '+cast(@pos as varchar(4))
   set @pos = @len
   end
   set @pos = @pos + 2
end

print @ctextout

You may need to adjust the ranges. For example, compressed stored procedures use the range 0-255. After decrypting, you can use the uncompress() function to get the original sp.

There is also an encrypt_a() function which operates per single byte rather than pairs of bytes as encrypt() does.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-24 : 08:10:36

...and thank you very much for publishing a method:

- that hackers can use to decrypt stored procedures and possibly compromise security features

- that ordinary SQL Server users can use to decrypt proprietary code that a professional developer invested time and effort into creating

I'm not trying to squash discussion about these things. I would like to suggest that you consider these factors and others before publishing code that circumvents security features, whether they are weak and vulnerable or not.

How would you feel if your paying clients decrypted your procedures and modified them instead of contracting you for the work, and they did it using your code?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-24 : 09:44:21

encrypt() in SQL Server 2000 doesn't seem to do anything at all.

sterobhun
Starting Member

12 Posts

Posted - 2002-07-24 : 11:05:33

I have to admit I hadn't considered those implications when posting the code. I had just been playing with the functions earlier on today and thought my observations may be of interest to others.

On the other hand, a few websearches revealed several other programs and code fragments very similar to what I came up with regarding the second method of decrypting SPs (the first method isn't viable for strings more than a few characters long as it uses an exponential proportion of encrypt() calls.) So, if any ordinary users really wanted to crack some SPs on version 7 then they would be able to anyway.

And given the amount of time it took me to come up with a simple crack, I would imagine that a hacker could figure out a decryption method on their own even quicker!

But I don't mind if you moderate that post to remove the code, if you feel that it will be used inappropriately. And I'm sorry if it does end up inconveniencing someone somehow, that really wasn't my intention.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources