Encrypting DDL
-
Working in Microsoft Sql Server 2000 Is there a way to encrypt a database object like a stored procedure so the end user can not see the text of SP in their database management tool like Enterprise Manager. Maybe a third party tool or Does Sql Server 2000 provide this somewhere?
-
Working in Microsoft Sql Server 2000 Is there a way to encrypt a database object like a stored procedure so the end user can not see the text of SP in their database management tool like Enterprise Manager. Maybe a third party tool or Does Sql Server 2000 provide this somewhere?
Use the WITH ENCRYPTION option in your stored procedure. Remember, once it is encrypted, there is no way to get it back. You must keep a text copy of the procedure somewhere so you can modify it later if needed. So a stored proc definition would look like: CREATE PROC Test WITH ENCRYPTION AS your code here
-
Use the WITH ENCRYPTION option in your stored procedure. Remember, once it is encrypted, there is no way to get it back. You must keep a text copy of the procedure somewhere so you can modify it later if needed. So a stored proc definition would look like: CREATE PROC Test WITH ENCRYPTION AS your code here
How to decrypt stored procedures[^]. The SQL Server 2000 stored procedure encryption suffers a fundamental flaw - if you have a known plaintext and the corresponding ciphertext, you can deduce the key. You can then decrypt any other ciphertext using the same key. When you use ALTER PROCEDURE, the same key is used as for the original procedure. You can save the encrypted procedure text you're trying to decrypt by accessing the syscomments table. You can then ALTER PROCEDURE to a known plaintext, read back the new ciphertext and deduce the key. You can then decrypt the original. I don't know if this is improved in SQL Server 2005. Stability. What an interesting concept. -- Chris Maunder
-
How to decrypt stored procedures[^]. The SQL Server 2000 stored procedure encryption suffers a fundamental flaw - if you have a known plaintext and the corresponding ciphertext, you can deduce the key. You can then decrypt any other ciphertext using the same key. When you use ALTER PROCEDURE, the same key is used as for the original procedure. You can save the encrypted procedure text you're trying to decrypt by accessing the syscomments table. You can then ALTER PROCEDURE to a known plaintext, read back the new ciphertext and deduce the key. You can then decrypt the original. I don't know if this is improved in SQL Server 2005. Stability. What an interesting concept. -- Chris Maunder
Thanks for the info! I had remembered reading that SQL Server 2000 encryption was better than before, and that the old ways to decrypt no longer worked. Obviously, a way was quickly found. Fortunately, I don't need absolute encryption, since I just use it to prevent casual tampering. Maybe I will have to add a clause to my support contracts dealing with intentional tampering.