Post reply

SP_Encrypted with SQL CLR how to DEcrypt?

  • April 4, 2011 at 10:00 am

    #248218

    Hello All:

    This is my situation-I have a lone important stored procedure on my production server that needs to be modified. It has a lock symbol next to it, and assuming the 1 SQL_CLR userid sitting in the security group belongs to this stored procedure. This userid is referenced by "KeyName" which I also deduce this userid(which can't have it's password changed) is the keypair for this stored procedure.

    I have copied the DB onto my development server just so I can decrypt this SP. I don't have the original source code(.NET SQL CLR) for this SP & when running a decrypt stored procedure I found on the internet...I get this error:

    Cannot perform alter on 'problemSP' because it is an incompatible object type.

    environment

    Production server:x64-SQL05

    Development Server: x32-SQL08

    VS-2008

    My Questions: Is there anyway to decrypt/unlock this stored procedure?...If not is there any way I can just "view" the SP to possibly get the code(trace or something else)?

    Is there a way to access the code(SP) thru VS.NET(with/without a keycode)?

  • April 8, 2011 at 3:04 am

    #1309019

    CLR stored procedure is not encrypted, it's just the way it's displayed in the SSMS object tree. You can't dump code for this particular StoredProc as it's implementation is embedded in assembly that is stored in your database.

    I guess you don't have access source code from implementation of this CLR SP?

    If you don't have you can dump assembly file from sys.assembly_files view.

    Follow instructions from this post:

    http://stackoverflow.com/questions/4244482/retrieve-clr-dll-from-sql-server

    and use .NET Reflector to view the source code.

    Hope this helps.

    Cheers, Max

    Check-out free open-source utility Sql Server Replication Explorer[/url]

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

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