25

I am working on a third party database.

When I try to view the definition of a view by right clicking, CREATE TO and then to NEW QUERY EDIT WINDOW, I am getting an error:

This property may not exist for this object or may not be retrievable due to insufficient access rights. The text is encrypted.

  • If needed you could also run a SQL profiler trace and capture what the SP SQL is doing this way too. – Pimp Juice IT May 22 '16 at 7:00
  • Is it possible to get the encrypted tsql through sql profiler? @PimpJuiceIT – wenzzzel Mar 24 at 15:18
  • @wenzzzel I believe it is but I don't recall exactly to tell you with 100% accuracy but I believe int he past with an encrypted SP, I ran a profiler trace to see what was needed. I don't support any environments at the time that I can confirm this but I believe you can see what's run with the profile trace even if it comes from an encrypted process. – Pimp Juice IT Mar 24 at 15:59
  • Okay, I'm not so sure about that cause all I get is -- Encrypted text – wenzzzel Mar 24 at 16:05
12

Another third party tool you could use to decrypt encryted objects on the fly is Red Gate's SQL Prompt: http://www.red-gate.com/products/sql-development/sql-prompt/features

Hovering over the stored procedure will then allow you to see the decrypted creation script.

Disclaimers: this tool is commercial (with a 14 day free trial) and I work for Red Gate.

17

I have a fairly detailed description about this problem here.

In short, the object is not really encrypted, but rather obfuscated. Therefore we can retrieve the original back. The method is a little involved but it consists of these steps:

  1. Connect to the instance using the Dedicated Admin Connection
  2. Select the obfuscated code like this:

    SELECT @secret = imageval
    FROM   sys.sysobjvalues
    WHERE  objid = OBJECT_ID(@object_name);
    
  3. Replace the object with another one that has the same name same object_id and the same length in bytes (e.g. using ALTER PROCEDURE)

  4. Get the newly obfuscated code the same way as above
  5. XOR the three values together (obfuscated original, replacement and obfuscated replacement)

That will give you the original code. However, as Kin mentioned, there might be support and even legal implications with doing this so be sure to consult your lawyer first.

13

The module text is encrypted using an RC4 stream cipher.

The RC4 initialization key is computed from the SHA-1 hash of:

  • The database family GUID (from sys.database_recovery_status)
    Converted from uniqueidentifier to binary(16)
  • The module's object ID (from the catalog views)
    Converted from integer to little-endian binary(4)
  • The module's object sub-ID
    Converted from smallint to little-endian binary(2).

The module's object sub-ID is:

  • 1 for an unnumbered stored procedure; or
  • The procedure number for a (deprecated) numbered stored procedure; or
  • 0 otherwise.

A suitably privileged user can then decrypt the module by:

  1. Obtaining the encrypted binary from sys.sysobjvalues (using the DAC)
  2. Computing the RC4 key as detailed above
  3. Running the well-known standard RC4 algorithm on the binary
  4. Converting the result from binary to nvarchar(max)

More details and a full code implementation in my article:

The Internals of WITH ENCRYPTION

  • I think this is the most straightforward, non-commercial, approach where installing a 3rd party tool isn't a feasible solution. – John Eisbrener Jun 29 '18 at 14:48
12

You can connect to SQL Server using the Dedicated Administrator Connection (DAC) then view the sql text of the stored procedure. Connect to the DAC using:

admin:Your_Servername

You can find the full steps needed in this answer on Stack Overflow by Martin Smith.

Another alternative is to use some third party scripts as mentioned in Decrypting encrypted stored procedures, views, functions in SQL Server 2005, 2008, & R2

As a side note - if it is a third party database and if you do it on prod, will the vendor support it? There may be a good reason to encrypt the SPs or views. It's probably better to take a backup, and then fiddle with that.

8

If you want to preview original DDL script or to decrypt an encrypted object, you can check out ApexSQL Decrypt

It’s a free standalone tool, with an option to integrate it into SSMS, preview original DDL script. Also, you can decrypt objects located on multiple servers in one turn. One more tool that can help you, is dbForge SQL Decryptor

0

A method I use often for decrypting several stored procedures at a time...

Use RedGate's SQL Compare and compare your database against an empty database (or any database you know will not have the stored procedures). Create a deploy script and copy into SSMS. Find and replace WITH ENCRYPTION with white space. Then change the CREATE PROCEDURE to ALTER PROCEDURE. Run the RedGate script against the original database and you've removed all stored procedure encryption.

I had a database with 400+ stored procedures and while SQL Prompt is handy, it was not worth my time to right click, copy, paste against 400+ stored procedures. Using RedGate SQL Compare I was able to remove encryption from my 400+ stored procedures in roughly 10 minutes start to finish.

protected by Community Jul 7 '15 at 15:42

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.