Primary/Foreign/Identity keys & Encryption

Hi all!

I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:

== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)

== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)

== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)

The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?

Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?

Any ideas are appreciated.

Thank you,
Ben

Hey Ben,

You have the principle behind encryption correct.

The tricky thing is when you use encryption on keys, a lot of the value of having these keys goes away. For eample, because encryption is non-deterministic, you won't be able to use OrderID as a FK in the OrderDetails table (unless if you encrypt once and then insert into both tables, but this leaks information. Then again, this might be acceptable in your application). You can still use PK, but they will behave differently. For example, because encryption is non-deterministic, just having the primary keys no longer guarantees that the columns will be unique. If you try to insert Encrypted("id1") and then Encrypted("id1") again, you actually end up with two different cipher values so the the table will allow both inserts.

Security basically destroys information (well encrypted data is indistinguishable from random data) while the point of using keys is to preserve information for reference. Consider, for example, the difficulties you will encounter attempting to do searches or joins on CustomerID and OrderID if they are encrypted (Laurentiu has a good blog entry on this here: http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx).

You can also check Laurentiu's blog for an example of creating an application using encryption: http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx this doesn't completely solve your problems, but it might be useful in seeing one way to apply encryption.

Please let us know if you would like more information or have further questions.

Sung

sql

Prevent users from seeing Table structures or underlying data

I encrypt my procedures using with encryption clause, but I do not how to decrypt again.

Is there a command or utility for encrypt and decrypt in Sql 2000? How about Sql 2005?

Thanks

Haydee

Decryption is weak and can be cracked by searching on google for the specific algorithms, there was a thread sometime ago, which might be useful to you:

http://groups.google.de/group/comp.databases.ms-sqlserver/browse_frm/thread/34b309b76ba574b4

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens is right, the procedure encryption is actually referred to as obfuscation in Books Online. Also, there is no SQL Server command for decrypting it back.

Thanks
Laurentiu

|||

Thanks for your comments

and is there a tool in Sql Server 2005 in order to protect the code? What can I do? I need to install a project in the customer, and I would like to protect it.

Thanks again for your help.

Haydee

|||You could use third party components to accomplish this, there sure can be found some by searching in google for them.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

HI,

I am using MSDE 2000 and I will be deploying it with my software application. I have invested a good bit into my database schema and I don't want it to be viewed by others.

I can not see why some user can not take the .mdf (multiple mdf's actually) and sp_AttachDB or attach them to their instanced SQL server using EM. I of course do not want this.

Maybe someone can clear up the limitations and types of SQL security that can assure no one can simply attach the MDF to see the structure, let alone the data.

As far as I can see there is Network security as to authentication for a live/instanced SQL server and this has no ability to prevent an MDF from being re-attached and viewed/queried.

I also see EncryptByPassPhrase which I can use prior to executing a query (if I understand this process which is data remains in encrypted state until its about to be used, then decrypted in memory (I presume ? otherwise someone could grab a snapshot of the mdf while it's in decrypted state ? {or SQL server has a temp region when using encryption where it places the decrypted data I take it}) and then I have to encrypt it again after processing.

Neither of these look like they can obfuscate or lock the db schema information, such as table names, structures, fields, field types/attributes etc.

Sooooooo.....

How can I prevent a user from seeing the underlying table structures and does anyone know if column encryption will cost me 10 years off my life time wise on large data sets ?

Thanks

|||

See this recent thread for a discussion of this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1

Thanks
Laurentiu

|||

The only feature for protecting code in SQL Server is the WITH ENCRYPTION clause that we discussed so far. It is weak not necessarily because the encryption is weak (it uses RC4), but because the encryption key can be easily found. An attacker will focus on finding the encryption key rather than breaking the encryption algorithm in such a solution. This is a general problem and for any solution you consider, you should look at how easy it is for someone to find the encryption key.

This is basically a DRM solution, and I have talked about the difficulty of creating an unbreakable DRM solution on other threads, more recently in:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1

Thanks
Laurentiu