Securing your SQL Server stored procedures!

We have read that SQL Queries in code would lead us to many problems and writing stored procedures is a way to secure our code from SQL Injections.

I agree that stored procedures are better than writing queries. But, give a thought. The stored procedures which you write; are they really secure?. This question can be answered by none other than ourselves as we are the guys writing them.

If our procedures are not secure then, what are the ways of securing them and also what are the pros and cons? This article gives a brief insight of the stored proc basic security. However, it's is not a complete guide to secure your stored procedure.

Providing security to stored procedure can be viewed in 2 contexts. One being making it non readable, and the other being granting access to it for authorized people.

Here comes the first Approach.

Encryption in Stored Procedures.

I know at some point of time you might have come across this and due to some reasons you might have ignored this. But let us see some details on it assuming it helps at least few people who might not know already.

There is a keyword WITH ENCRYPTION to encrypt the stored procedure content.

Here is the example
CREATE PROC GetCustomers
WITH ENCRYPTION
AS
SELECT * FROM [Customers]
That’s it. Create the above procedure. And the procedure is encrypted. A lock icon is displayed on the procedure in the SQL Server Management studio.

You will be able to run this procedure without any problem as any other procedure. There is no official way to view the content of your stored procedure back!

Here are some basic operations you can do on this stored procedure now after
encryption

1. Rename it
2. Execute it
3. Delete it

Here are some operations which you cannot do
1. View the content directly as you were doing it for any normal procedure
2. SCRIPT TO- Any window or clipboard
3. Modify

If you run a query to play a trick
SP_HELPTEXT GetCustomers
Your SQL Server will say,
The text for object ' GetCustomers' is encrypted.
Here are the things you should consider when going for this approach.

1. Just take a complete backup of you stored procedure before encrypting it.
2. Do it at the END When you are sure you don’t have to modify it anymore
3. Encrypt it in production if required and not on development environment

The above way of encrypting is the simplest way and that’s the advantage. Now, why
are we not using it? Intelligent programmers and a few entry level hackers found
some ways to decrypt it!!

Few ways discussed in communities are
1. Attach a debugger to server process and decrypt it to get the content back
2. Write a decryptor stored procedure hack which can decrypt.
3. Use third party tools available to decrypt the procedure and get the content back.

However, I will not be writing detailed ways of decrypting it and this article is not intended for that!

One thing we can notice here is all the ways to decrypt it back needs a fair amount of knowledge and some effort to do a Google...

So, the above approach should be used minimally and only when required but definitely this is also a way to secure and forms a layer of protection.

But don’t call me when you encrypt in this way and did not find a way to decrypt! After all am writing this article after doing some research and not a master in decrypting an encrypted object!

Let’s see an interesting way of securing your store procedures at a higher level!

Signing a stored procedure with a certificate

What is the purpose?
1. When you want the user to require permissions on a stored procedure
2. When you want to use trace to find out the original caller

How to do ?
Microsoft provides 7 easy steps to follow to add a certificate to stored procedure.
I do not want to repeat the steps here. Please refer to the tutorial
http://msdn.microsoft.com/en-us/library/bb283630.aspx

Few other areas where certificates can be used
1. Get control over Cross database access
2. Bulk Copy permissions

Everything is an advantage when certificates are used. Few disadvantages I see are
1. When you have used DENY restricting access to an object in SQL Server, you cannot give access to that object from a stored procedure which is signed with a certificate. Of course, the DENY Takes precedence over the GRANT.
2. It’s a bit difficult process to understand permissions and roles till you get used to it and know the problems that might occur when using it.

If you are too keen in securing the things,

These are the things you can try.
1. Do some research and Try to use ASSYMETRIC KEYS Instead of Certificates. Asymmetric keys never expire but the certificates do.
2. Find Other ways of securing the stored procedures and write to msguyblog@gmail.com

Will share something interesting next week :) have a great week ahead.

Popular posts from this blog

Facebook Javascript API : Feed and Share Dialog for Beginners

What's new and expected in .NET Framework 4.5

Real time Push Notifications with SignalR & PNotify (Pines Notify)