Skip to main content

How to solve export issue in SQL Database if any of the database objects is encrypted

 Introduction

Data safe ensured by some special kind of mechanisms in modern database providers. I like the way of MSSQL is designed and it is still improvising or shaping better in all aspects. In MSSQL, if we need to hide business logic of stored procedure, then stored procedure encryption is comes handy with MSSQL in order to ensure data security. By the way decryption is not straight forward in MSSQL. Let's see what are all the impacts of stored procedure encryption and see how to overcome those.

Encrypt Stored Procedure

Stored procedure encryption only restrict anyone from viewing or editing. It will still allow us to execute the stored procedure with all needed parameter values. We can "CREATE OR ALTER PROC" stored procedure with "WITH ENCRYPTION" to encrypt stored procedure.

USE [pubs]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[reptq1] 
WITH ENCRYPTION
AS
select
	case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id,
	avg(price) as avg_price
from titles
where price is NOT NULL
group by pub_id with rollup
order by pub_id

What happens after encryption of stored procedure?

After encryption applied to the stored procedure, we could not able to view or modify the stored procedure like


If you see modify menu is disabled and script stored procedure as menu will throws the following error
Property TextHeader is not available for StoredProcedure '[dbo].[reptq1]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)


Executing sp_helptext of encrypted stored procedure will throws an error The text for object 'reptq1' is encrypted.


What will happen if we choose extract data tier application?

Extract data tier application in SSMS database menu will help us to generate .dacpac file. If any one of our database objects are encrypted then dacpac file will not be generated and this will throw the following error

Issue in export data tier application

Export data tier application in SSMS database menu will help us generate .bacpac extension files. If any one of stored procedures is encrypted and if it is selected for export then we will get the following issue

What's the major problem?

If we apply the encryption of any database objects for security reasons it will impact database from taking export the database to any other forms like dacpac or bacpac. Sometimes exporting database or cloning the database will be very difficult of these encryption reasons.
Our team works very hard to resolve this kind of backup issue in one of our client environment. Finally we got only two option to resolve this issue.

Note : We have done all our backup or exporting steps as super admin

Option 1 - Drop the encrypted procedure(not recommended)

I will not recommend this option but I want to explore you there is an option to solve this backup problem. You can drop all your encrypted database objects if it is not internally referred in another database objects. For example,

drop proc reptq1

Option 2 - Using third party tools to reverse engineer the encrypted procedure

I would say reverse engineering the stored procedure to remove WITH Encryption option is only way to resolve this exporting issue. Lets see what are the few third party tools available for us to solve this problem. Thanks dbForge and ApexSQL for your fantabulous job to overcome such kind of issue
  1. dbForge
  2. ApexSQL
Lets see dbForge methodology to solve this issue.

How to decrypt an encrypted stored procedure through dbForge?

Here is the demo to reverse engineer the encrypted store procedure using dbForge. 

Conclusion

I hope this article is very useful to you all. Please share your comments to improve this article to get in better shape. Thank you!!
Labels:

Comments

Post a Comment

Popular posts from this blog

How to resolve ASP.NET core web API 2 mins timeout issue

Introduction We are in the new world of microservices and cross-platform applications which will be supported for multiple platforms and multiple heterogeneous teams can work on the same application. I like ASP.NET Core by the way its groomed to support modern architecture and adhere to the software principles. I am a big fan of dot net and now I become the craziest fan after seeing the sophisticated facility by dot net core to support infrastructure level where we can easily perform vertical and horizontal scaling. It very important design aspect is to keep things simple and short and by the way, RESTFul applications are build and it is a powerful mantra for REST-based application and frameworks. Some times we need to overrule some principles and order to handle some situations. I would like to share my situation of handling HTTP long polling to resolve the ASP.Net core 2 mins issue. What is HTTP Long polling? In the RESTFul term, when a client asks for a query from the serv
6 comments
Read more

How to Resolve ASP.NET Core Key Protection Ring Problem in AWS Lambda

Introduction When it comes to server less web application design using asp.net core razor pages, we definitely need to consider a factor of data protection key management and its lifetime in asp.net core. I developed a site using AWS toolkit of ASP.NET Core Razor Pages. The main advantage of ASP.NET Core is cross-platform from where we can deploy our application in MAC, Linux or windows. I deployed my site initially in IIS Server from which I got the results as expected .but later period I decided to host my site in AWS Lambda in order to meet our client requirement. Strangely, I got unexpected behavior from my site. I just refer the cloud information Lambda Log to identify or pinpoint the case, I got the error Information like “Error Unprotecting the session cookie” from the log. In this article, I tried to explain the root cause of the problem and its solution to overcome such kind of issue. Data Protection in ASP.NET Core This is feature in ASP.NET Core which acts as repl
4 comments
Read more

Which linq method performs better: Where(expression).FirstorDefault() vs .FirstOrDefault(expression)

 Introduction When it comes to LINQ, we always have multiple options to execute the query for the same scenario. Choosing correct one is always challenging aspect and debatable one. In one of our previous articles   Any Vs Count  , we have done performance testing about best LINQ methods over .NET types. In this article, I would like to share about  Where(expression).FirstorDefault() vs .FirstOrDefault(expression) Approaches Performance testing for  Where(expression).FirstorDefault() vs .FirstOrDefault(expression) is very interesting IEnumerable<T> or ICollcetion<T>  .FirstOrDefault(expression) is better than  Where(expression).FirstorDefault() Public API To check the performance, I need some amount of data which should already available. So I decided to choose this  public api . Thanks to publicapis Public API Models Entry class using System ; using System.Collections.Generic ; using System.Text ;   namespace AnyVsCount { public class Entry { pub
Post a Comment
Read more