Post reply

DECRYPTSP2K Issues in 2008

  • January 19, 2012 at 6:27 am

    #148947

    Hi,

    I am a DBA, I have tried to decrypt my encrypted SP, with your DECRYPTSP2K in 2008 SQL server, but that dropped my inputed procedures, is there is any way for 2008

  • January 19, 2012 at 6:29 am

    #1435776

    easiest way is to simply connect via the DAC: Dedicated Admin connection.

    procs are not encrypted when connecting via the DAC.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 19, 2012 at 7:08 am

    #1435816

    ok a correction. it's more than just the DAC, as i just double checked to make sure my post was correct.

    you need to connect via DAC, and then use the stored procedure from this web site;

    http://msdynamicstips.com/2008/12/24/decrypt-sql-2005-stored-procedures-functions-views-and-triggers/

    with THAT, you can read the proc contents.

    here's my step by step if you care to recreate it.

    first, i created an encrypted procedure:

    --for users who are too lazy to type "SELECT * FROM"

    CREATE procedure sp_show

    --USAGE: sp_show gmact

    @TblName varchar(128)

    WITH ENCRYPTION

    As

    Begin

    exec('Select * from ' + @TblName)

    End

    GO

    EXEC sp_helptext sp_show --returns "The text for object 'sp_show' is encrypted."

    next, i connected to the DAC via SSMS: you might need to enable the DAC by going to facets>>Surface Area Configuration toggle "RemoteDACEnabled" to true, then stop and start the service (you are doing this on TESt, no production, right!?)

    next i connected via the DAC:

    next, in that DAC window, i created the stored procedure [dbo].[SQLDecryptor]

    finally, i simply ran the command

    EXEC [dbo].[SQLDecryptor] 'dbo','sp_show'

    and it printed the definition in the Meassages field of the SSMS window.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply