Decrypting Encrypted Stored Procedures

When you install CampusNexus CRM afresh, the required stored procedures are in a decrypted state. However, in an upgrade scenario, only new and existing stored procedures that are modified are in a decrypted state. Other stored procedures are in an encrypted state and are required to be decrypted.

To Decrypt Stored Procedures

1.  Identify stored procedures you want to decrypt.

2.  Log on to SQL Server Management Studio using a Dedicated Admin Connection (DAC). To do so, perform the following steps:

a.   In SQL Server Management Studio, with no other DACs open, in the New Query dropdown, select Database Engine Query.

Alternatively, select File > New > Database Engine Query.

The Connect to Database Engine dialog box is displayed.

b.   In the Server name field, type ADMIN: <Name of server instance>. For example, to connect to a server instance named ANALYTICS, type ADMIN: ANALYTICS.

c.   Provide user name and password credentials and then click Connect. The connection is made.

3.  Insert stored procedure names that you want to decrypt into the tblObjectsForDecrypt table.

To do so, type the command Insert into tblObjectsForDecrypt (tname) values ('<Name of stored procedure1>’), (‘<Name of stored procedure2>'). Replace the text in quotations with the names of stored procedures.

4.  To decrypt a stored procedure, type the command Exec Sproc_Wrapper_Sproc_To_Decrypt ‘<Network folder path>’. Replace the text in quotations with the network folder path. The logged in user must have read and write permissions to the path.

The stored procedure is decrypted and compiled on the server, and details of the decryption are stored in the tMsg column of the tblObjectsForDecrypt table. Additionally, the decrypted stored procedure is copied to the folder path specified in step 4.