Encryption & Decryption Part II
Hi all me again here with a post about Encryption & Decryption in Sql Server 2005.I have received a couple of questions regarding my article Encryption & Decryption in Sql Server 2005 , so i started responding through mail but because i found it really useful so here i decided to post it….
So here we go…
Q: Is it possible to pull data from table using the Encrypted column in where clause and if yes then how?
A: Yes it is possible to fetch data from Table using Encrypted column in where clause.. and this how it works…
referring to the last post Table and data is same….
-- Try like this
select * from users where DecryptBykey(UPassword)='MYPASSWORD'
So this throw you records matching your password field
Q: How i Encrypt My Store Procedure i don’t want to show my procedure to anyone?
A: Yes you can Encrypt Store procedure at the time of creating or Altering the procedure….
You can achieve it by just adding a simple “WITH ENCRYPTION” clause when Creating or Altering your procedure like this..
-- Creating a procedure
CREATE procedure [dbo].[TestEncrypt]
WITH ENCRYPTION AS
SELECT 'Hi This is An Encrypted Procedure'
-- Execute Procedure
EXEC TestEncrypt
So it will give you results but when you tried to modify it through SSMS then you find that “MODIFY” option is not open at all and you tried to “Create Script then it also gives you error” here the attached images for this…
Blocked Modify Option
And Error when tried to Crete Script for this…
So in this we can Encrypt our Store Procedures but you have to remember some thing when Encrypting ..
- When you create a Procedure or Function with “WITH ENCRYPTION” so it is not possible to decrypt it back using SQL.
- CLR SP or UDF can not be encrypted.
- Replication doesn’t support encrypted Procedures and Functions
So thanks and Happy Quering…