Decrypt SQL Server Stored Procedure via Different Methods

SQL Server user makes the frequent use of stored procedures in the database in order to maintain the integrity and consistency of the data. Simply, a block of SQL query is embedded into the stored procedure, which can be called at any time in the program. Since, these procedures are associated in the encrypted form with the database application, one finds it quite difficult to view the code behind a particular stored procedure. Only the permitted users are allowed to view the source code behind the stored procedure. More importantly, the developers who wish to update the already included stored procedures in an application, finds it much necessary to decrypt encrypted SQL server stored procedure so that they can easily work on the code and may update the behavior of the procedure. Keeping in mind the need of decryption, we have attempted to provides way to decrypt SQL Server stored procedure using different methods.

Manual Approach to Decrypt SQL Server Stored Procedure

Any stored procedure can be decrypted by using the DAC(Dedicated Administrator Connection) with SQL Server.

To connect to DAC: The DAC can be connected to SQL Server Management Studio by prefixing ADMIN to the server name in query editor.

Once a connection is established with DAC, the decryption can be performed using the following steps:

1. Using DAC connection, fetch the encrypted values of the stored procedure
2. Obtain the encrypted value for the blank procedure having ‘-’ character in its definition
3. Obtain the plain text blank procedure statement in unencrypted form
4. Now, XOR the results of step 2 and step 3 to get the final decrypted value of the procedure

Consider the following script that allows to decrypt encrypted stored procedure named ‘dbo.MyDecryption’ using DAC. The scripts needs to be executed carefully, otherwise if any of the parameter is missing, it will generate an error.

SET NOCOUNT ON
GO

ALTER PROCEDURE dbo.MyDecryption WITH ENCRYPTION AS
BEGIN
PRINT 'This is the decrypted text preview..'
END
GO

DECLARE @encrypted NVARCHAR(MAX)
SET @encrypted =(
SELECT imageval
FROM sys.sysobjvalues
WHERE OBJECT_NAME(objid) = 'MyDecryption’)
DECLARE @encryptedLength INT
SET @encryptedLength = DATALENGTH(@encrypted) / 2

DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N'ALTER PROCEDURE dbo.MyDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))
EXEC sp_executesql @procedureHeader
DECLARE @blankEncrypted NVARCHAR(MAX)
SET @blankEncrypted = (
SELECT imageval
FROM sys.sysobjvalues
WHERE OBJECT_NAME(objid) = 'MyDecryption’)

SET @procedureHeader = N'CREATE PROCEDURE dbo.MysDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))

DECLARE @cnt SMALLINT
DECLARE @decryptedChar NCHAR(1)
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptedMessage = ''
SET @cnt = 1
WHILE @cnt <> @encryptedLength
BEGIN
SET @decryptedChar =
NCHAR(
UNICODE(SUBSTRING(
@encrypted, @cnt, 1)) ^
UNICODE(SUBSTRING(
@procedureHeader, @cnt, 1)) ^
UNICODE(SUBSTRING(
@blankEncrypted, @cnt, 1))
)
SET @decryptedMessage = @decryptedMessage + @decryptedChar
SET @cnt = @cnt + 1
END
SELECT @decryptedMessage

Decrypt Encrypted SQL Server Stored Procedure using Third Party Tool

Various third party tools for decrypting SQL Server database objects are available in the industry, which cater the demand to easily decrypt the encrypted database objects. One such efficient tool is SQL Decryptor. The tool carries out decryption in the following easy steps:

1. Enter all database credentials such as server name, login mode, user credentials(name & password) to connect to server

2. On the preview page, choose the required stored procedure and preview the decrypted script in the text box.

3. Under the export options, choose to export the decrypted file to SQL Server directly or export as SQL Compatible Script.

So, the users can make the use of automated decryption tool to decrypt the stored procedures without running any complicated queries.

Conclusion

Considering user’s requirement to decrypt various database objects like the stored procedures, we have tried to explain the method to decrypt encrypted SQL Server stored procedure using manual methods and third party software. Users are advised to run the SQL script carefully to completely decrypt the stored procedure. However, to get rid of the complicated queries the user can also go for the third party SQL Decryptor software. It can efficiently decrypt the database objects and the decrypted script can also be exported using the tool.

Resolve Error “SQL Database is in Use” Using Manual Steps

Problem while Working On Database:

While working on SQL Server users perform various actions such as Rename, Detach, Restore, and Drop on SQL Server. Due to which an error occurs “SQL Database is in use” with different error numbers while performing such actions on SQL database.

How to Tackle this Problem ?

Some scenarios are discussed below, which helps users to resolve the occurrence of an error while executing various actions on SQL database.

Scenario 1: While Restoring the Database

When the user tries to restore the database and other users are accessing the same database at that time. Then, they face SQL error message 3101, which is due to SQL database is in use. There are some solutions, which helps users to remove the error occurrence.

Exclusive Access

User can get the exclusive access by dropping all the other connections to restore the database. There is another way to check the connections usage of the database, which user needs to restore, i.e. sp_who2 and SSMS.

Using KILL Command

After getting exclusive access, user can use KILL command that helps to kill every connection that is using the database. Before this, user must be aware that which connections are killed as the rollback issue may be required.

ALTER DATABASE

There is another option, in which user can put the database in single user mode after that they can restore. It also rollback depends on the option user use, but it will all do connections at once.

Scenario 2: Renaming the Database

While renaming the database user receives SQL error 5030. This error occurs when the database is in Multi User mode where various users are utilizing the database at same time. To resolve this issue, user first need to set the database to single user mode and then rename the database and after that set it to Multi user mode again. User can follow the steps given below to perform the process.

• Firstly set the database to single user mode,
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE
• Now rename the database
ALTER database AdventureWorks MODIFY NAME = NewAdventureWorks
• Set database to Multiuser mode
ALTER DATABASE AdventureWorks SET MULTI_USER WITH ROLLBACK IMMEDIATE

Scenario 3: While Dropping the Database

When the user tries to drop a database when users are connected to SQL Server database, then they will receive SQL error message 3702. User can follow the mentioned steps to remove the error occurrence by getting the exclusive excess to drop database in Server.

1. Connect to SSMS and expand the database node.
2. Right click on the databases that are required to drop.
3. Choose the delete option from drop down list to view the delete object.
4. Now choose the checkbox “close existing connections” to drop existing connections before dropping the SQL Server database.
5. Select option “Delete backup and restore history information for database,” user will be able to remove the database backup and restore history that is stored in database.

Using TSQL Query

User can execute the TSQL code to drop the database in SQL Server by using TSQL Query as mentioned below:

EXEC msdb.dbo.sp_delete_database_bakuphistory @database_name = N‘AdventureWorks’
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Drop DATABASE [AdventureWorks]
GO

Scenario 4: Detaching the Database

When the users detach the database, they face SQL Server error 3703. This error is due to the reason that multi users are using the same database at the time. To resolve this, user can kill the respective accessing DB processes; sometimes it does not work. For this, user needs to force DB delete/detach by ending the existing connections by leaving the DB in single user mode.

Select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d name like ‘%mydb%’
go
kill 53
go

Users need to fix the DB offline with ‘SET OFFLINE WITH ROLLBACK IMMEDIATE’ setting so that the DB will be turn offline immediately and after that detach DB will work correctly. When the user needs to attach the DB they can make the DB online.

ALTER DATABASE  SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [Database Name] SET ONLINE

Conclusion

With the help of the discussed solution, user can easily resolve an error “SQL Database is in Use” to perform various actions such as restore, rename, detach, and drop on the SQL server database.