Thursday, October 13, 2016

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.

Wednesday, July 20, 2016

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.

Thursday, July 11, 2013

Property Owner is not available for Database SSMS error

When you try to launch the database mirroring GUI or some other database property window in SSMS you get this error:

Cannot show requested dialog.

Additional information:
  Cannot show requested dialog.(SqlMgmt)
    Property Owner is not available for Database'[XXXX]'. This property may not exist for this
    object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
 
I saw this issue during our DR (Disaster Recovery) exercise. We were using the SSMS GUI and we successfully failed over one database from the principle server to the mirror server, but when we did a failback via the GUI we got this error.Visit below link to troubleshoot this issue:

Property Owner is not available for Database SSMS error


Visit my author profile to read my other articles: Manvendra Deo Singh

SQL Server 2008 R2 Upgrade Failure due to Security Group upgrade rule

Last month I was working on a SQL Server 2005 to SQL Server 2008 R2 upgrade.  Unfortunately, we were not able to complete our upgrade because of a failing a upgrade rule named "Security Group SID (Security Identifier)".  The SQL Server Upgrade Setup Wizard did not enable the 'Next' button in order to proceed with the upgrade until this failure was resolve.  In this tip, I will describe the reason for this failure and how I fixed the issue in order to have a successful upgrade. Read my article published on MSSQL Tips through below link:-

SQL Server 2008 R2 Upgrade Failure due to Security Group upgrade rule 

 

Visit my author profile to read my other articles. Manvendra Deo Singh

Sunday, March 17, 2013

How to protect My Stored Procedure Code.

When deploying applications to a client's server(s) or to a shared SQL Server, there is often a concern that other people might peek at your business logic. Since often the code in a stored procedure can be proprietary, it is understandable that we might want to protect our T-SQL work. Use "WITH ENCRYPTION" option to protect your SQL code as shown below.

CREATE PROCEDURE dbo.Manvendra
WITH ENCRYPTION
AS
BEGIN
SELECT 'SQL statements'
END
 
Now when you will try to run sp_helptext to see the code, below error will appear.

"The text for object 'Manvendra' is encrypted"