Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
1  
Thank you, i was late to see your response. I purchased sql-tools.net SQLDecryptor Tool. –  goofyui Mar 7 '14 at 22:04

You can connect to sql server using DAC and then view the sql text of the stored procedure.

admin:Your_Servername

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.

share|improve this answer
    
Thanks, Let me try –  goofyui Mar 4 '14 at 20:18
1  
Connecting via the DAC is only the first step. It doesn't automatically de obfuscate it for you. –  Martin Smith Mar 4 '14 at 20:26
    
I am sorry, How to connect using DAC. Is DAC is a separate tool. Just confused. –  goofyui Mar 4 '14 at 20:46
    
I tried using a Thirdparty tool sql-tools.net. They decrypted the sql syntax and showed a portion of the syntax. Inorder to view the complete syntax through third party tool, i need to purchase the tool –  goofyui Mar 5 '14 at 14:10
    
Thank you , I ended up purchasing the Third Party Tool - sql-tools.net . My work is fulfilled. Thanks –  goofyui Mar 5 '14 at 16:49

I have a fairly detailed description about this problem here: http://sqlity.net/en/1617/decrypting-encrypted-database-objects/

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 (http://technet.microsoft.com/en-us/library/ms178068(v=sql.105).aspx)
  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.

share|improve this answer
    
I think that's technically encryption (?) -- just really weak encryption... no? –  Code Jockey Aug 13 at 13:24
    
I guess you could call it "encryption". I like "obfuscation" because "encryption" implies something that is not the case here. ---- Btw, I am not sure this could have been solved better, as the encryption key has to be part of the database (otherwise you couldn't restore such a db to another server), and therefore could be read out by a determined enough hacker. With that in mind, it is possible that MS went for the fastest encryption algorithm, knowing that a "real" encryption would improve security only marginally. –  Sebastian Meine Aug 13 at 20:39

protected by Community Jul 7 at 15:42

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

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.