Time and again, I have talked about security as being a high concern for DBA’s. So, that is why it is good to familiarize ourselves with the various types of security within SQL Server. In a previous post, I discussed encryption at the database level (https://sqlbadboy.wordpress.com/2015/03/02/how-sql-server-can-make-things-more-secure-use-encryption/). Today, I am going to focus on encrypting and decrypting SQL Server objects, such as stored procedures, views and user defined functions.
*** PLEASE NOTE: All my examples will focus on encryption with stored procedures.***
Why might we see database object encryption? Well, sometimes a software vendor or even someone in our own company may want to obfuscate the text of a database object so that it is not readily available in any of the catalog views within SQL Server. This object may contain sensitive logic that handles business operations.
In these cases, the object will appear with a little lock in SQL Server Management Studio (SSMS):
If you try to get the objects definition or text, it will not work by standard methods, even if you are a sysadmin on that SQL Server instance.
- Using SSMS to script out the text
- Using the system stored procedure, sp_helptext (https://msdn.microsoft.com/en-us/library/ms176112.aspx)
- Using the system view, sys.syscomments (https://msdn.microsoft.com/en-us/library/ms186293.aspx), or the catalog view, sys.sql_modules (https://msdn.microsoft.com/en-us/library/ms175081.aspx)
How to encrypt a database object
This is extremely simple to do! All that is needed is to include the WITH ENCRYPTION keywords.
CREATE PROCEDURE dbo.spTest_Encrypted WITH ENCRYPTION AS SELECT name FROM dbo.test GO
*** NOTE: CLR procedures can NOT be encrypted. And any procedure created with this option can NOT be published as part of SQL Server replication.***
How to decrypt a database object
Unfortunately, decrypting a database object is not that difficult. In fact, a database object can be decrypted by either using a privileged user who can either access system tables over the dedicated administrator connection (DAC) or by attaching a debugger to the server process that can retrieve the decrypted procedure from memory at runtime, which is what many third party tools do.
Method 1: Using the Dedicated Administrator Connection (DAC) (https://msdn.microsoft.com/en-us/library/ms189595.aspx) with a sysadmin account. You can connect to the DAC via SSMS by prefixing the server name with ADMIN:
By default, this connection is only allowed from a client running on a server. If you don’t enable the remote admin connection option (https://msdn.microsoft.com/en-us/library/ms190468.aspx) of sp_configure, you will get the following error:
Once you are connected, follow these steps:
- Get the encrypted text for the original object from sysobjvalues, which is a base system table
- Create an encrypted fake object with dummy text
- Get the encrypted text for the fake object
- XOR the original with the fake object
The following posts contain sample code for the steps above:
Method 2: Using a third party tool, such as RedGate’s SQL Prompt (http://www.red-gate.com/products/sql-development/sql-prompt/).
So, hopefully this post helps give a basic understanding of database object encryption. It is yet another simple way that many use to provide a level of security for database objects.