Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Usefulness of SQL Server "with encryption" statement

Recently a friend and I were talking about securing stored procedure code in a SQL server database.

From distant memory, I'm pretty certain that "with encryption" is incredibly easily broken in all versions of SQL Server, however he said it has been greatly improved in SQL 2005. As a result I have not seriously considered it as a security option in any systems I have ever worked on.

So in what scenarious could "with encryption" be used, and when should it be avoided at all costs?

like image 308
Ash Avatar asked Sep 09 '08 02:09

Ash


3 Answers

It can be used to hide your code from casual observers, but as you say: it's easily circumvented.

It really can't be any other way, since the server needs to decrypt the code to execute it. It's DRM, basically, and fails for the same reason as all the other DRM does - you can't simultaneously hide the data, and allow it to be accessed.

like image 61
Blorgbeard Avatar answered Sep 20 '22 12:09

Blorgbeard


@Blorgbeard

Good response, the MSDN documentation on "WITH ENCRYPTION" seems to agree with your point, now calling it "obfuscated" rather then encrypted.

I've met a few developers who were completely unaware of this point however. Hopefully this question/response will inform others too.

like image 27
Ash Avatar answered Sep 20 '22 12:09

Ash


Yes, it's easily broken. I had a situation this past week where I had to decrypt several sprocs that a former developer had encrypted for a client of mine. After decrypting it, which took a moderate effort, I wouldn't rely on that for any means of protecting intellectual property, passwords, user ids. Anything really.

like image 42
Taptronic Avatar answered Sep 20 '22 12:09

Taptronic