Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to retrieve the source code of an encrypted object (WITH ENCRYPTION)

Views: 4.816 views
Reading Time: 8 minutes

Hello people,
All right with you ?

In this post I would like to demonstrate to you How to retrieve source code from an encrypted object (WITH ENCRYPTION) in SQL Server. How many times have I seen programmers encrypting objects in SQL Server in the false hope that this code will really be protected from changes and previews by other users.


For those who have been working in IT for a while, I think they are unanimous that there is no really effective source code protection. If you program in .NET, you have the JetBrains dotPeck. If you encrypt your objects in Oracle Database, there is the website Unwrap It! that breaks this ONLINE encryption. In PHP, I've used Zend Guard to encrypt source files and there are online tools that also break encryption.

In SQL Server this is no different either. There are even tools (Redgate SQL Prompt, ApexSQL Complete and many others) that do this for you automatically, showing the source code of encrypted objects so naturally and transparently that you don't even realize that the object was encrypted.

Although it is possible to break object encryption in SQL Server, it does not directly mean a security breach. In order to break this encryption, you will need to use a DAC connection, which I already explained how to enable and use in the post. Enabling and using dedicated remote administrator connection (DAC) in SQL Server.

The ACD connection is a dedicated connection for sysadmins (which greatly limits users who can perform this procedure) who need to connect to instances that are experiencing issues that prevent new connections, such as limiting concurrent users of the instance reached.

As the form of connection is different (including using a dedicated port), this “special” connection is always available, even in this situation. It is worth mentioning that this connection only allows 1 user connected at a time, so if someone is already connected, someone else will not be able to use it.

All this requirement of using the DAC connection is simply so that you can read data from the sys.sysobjvalues ​​table. Even if you are sysadmin, you will only be able to access the data in this table using the DAC connection.

Example WITHOUT using the DAC connection

Msg 208, Level 16, State 1, Line 16
Invalid object name 'sys.sysobjvalues'.

Example Using DAC Connection

SQL Server uses the RC4 ™ stream cipher encryption algorithm to encrypt objects and stores this data in the imageval column of the sys.sysobjvalues ​​table. In this post, I will demonstrate an algorithm that allows you to retrieve this information and return the original source code of the encrypted object.

How to list encrypted objects in the database

To identify which objects in your database are encrypted, I separated 4 queries to achieve this goal:

Using the sys.sql_modules view:

Using the OBJECTPROPERTY function:

Using the sys.syscomments view:

Using the INFORMATION_SCHEMA database:

How to create an encrypted SP

For this post, I will create a very simple and encrypted SP, and I will demonstrate how to get its source code.

As you can see below, you cannot view the source code of an encrypted object using the sys.sql_modules view, even if you are a member of role sysadmin.

Not even using the sp_helptext system procedure is it possible to obtain the source code for this object, returning the alert message “The text for object 'dbo.stpTeste_Decrypt' is encrypted.”, As shown below:

Breaking Object Encryption by SSMS

To break the encryption of objects by SQL Server Management Studio (SSMS), start a DAC connection on the desired instance. To do so, just add the prefix “ADMIN:” before the name of your instance, thus: “ADMIN: server \ instance”, as shown in the image below:

Now use the code below and you will now be able to view the source code of your encrypted object.

Source code


Breaking object encryption by CLR (C #)

Now that I've explained how to break objects, I'll show you a more practical and functional solution in everyday life that you can allow even non-sysadmin users to see the source of encrypted objects without having to open it. a new DAC connection, even from another server or instance.

For this, I will use the CLR to make the connection via DAC, using a sysadmin user. This way, even if the person is not sysadmin, just if they have permission in this role, they can view the source of unencrypted objects. To break the code, I will use the same code shown above, just running it through CLR (C #) on a DAC connection, transparent to the user using the function.

Source code

After you create the role in your CLR project and publish the change, let's use it:

Remember that in this solution, the user who is using the function need not (and should not) be using a DAC connection.


That's it folks!
I hope you enjoyed this post and see you next time.

SQL Server how to retrieve view get decrypt stored procedure source code encrypted objects WITH ENCRYPTION

SQL Server how to retrieve view get decrypt stored procedure source code encrypted objects WITH ENCRYPTION