For the last couple of weeks, I was thinking about finding a solution to encrypt the stored procedures of my database in SQL Server 2005. The reason behind this goal is that normally our stored procedures contain complex business logic. This business logic is the intellectual property of the company and it is our job to keep it safe and protected.
There are many third party solutions and ‘WITH ENCRYPTION’ in SQL Server 2005 but the encrypted code is readable with the help of any free Decoders available on the internet. We needed a better solution to keep the code encrypted yet executable.
After searching through the internet, I came up with an idea ‘Why not to create SP at runtime and drop it after executing?’ I got this idea by the post of someone in one of the SQL Server Forums on this subject. And I feel that the forum poster was not serious about the idea.
But I thought why not give it a try. Table of Contents Core LogicExplanationTo Add All Stored Procedures on the FlyConclusion
The logic is very simple. I decided to keep the actual code of the stored procedure in a table. This table will have the following columns;
ID will be used to refer and execute the actual code.
In the following steps, I will tell you how I performed this task;
Step 1:
Create a table tblSQLTable. This table will be encrypted. Here is the CREATE TABLE statement for tblSQLTable;
if Not Exists(Select * from sys.objects where name = 'tblSQLTable' and Type = 'U') begin CREATE TABLE [dbo].[tblSQLTable] ([tID] [int] NOT NULL, [SNO] [int] NOT NULL, [vSQL] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SQL_Encrypted] [varbinary](max) NULL, CONSTRAINT [PK_tblSQLTable] PRIMARY KEY CLUSTERED ( [tID] ASC, [SNO] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] end else begin Delete from tblSQLTable end
The column vSQL will have the original text of the stored procedure. SQL_Encrypted will contain the encrypted text of the stored procedure. Initially, I will work with vSQL column to explain my point. At last, I will discuss usage and implementation of SQL_Encrypted column.
Step 2:
Open the stored procedure that you want to encrypt. To insert the code in the table we will have to delete the SET statements at the top of each stored Procedure. After this, replace the single quote with two single quotes. Replace ALTER PROCEDURE with CREATE PROCEDURE and add # operator at the beginning of the stored procedure name. Finally, create a simple Insert statement for tblSQLTable against the respective stored procedure code.
Actual Stored procedure;
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: Atif Sheikh -- Create date: 18-May-2010 -- Description: Sp to be Encrypted -- =============================================
ALTER PROCEDURE sp_who123 @pInt int, @pVarchar varchar(10) AS BEGIN SET NOCOUNT ON; Select @pInt, @pVarchar PRINT 'SP Executed successfully…' END
Insert statement of stored procedure;
Insert into tblSQLTable (tID,vSQL) Select 2,' CREATE PROCEDURE #sp_who123 @pInt int, @pVarchar varchar(10) With Encryption AS BEGIN SET NOCOUNT ON; Select @pInt, @pVarchar Print ‘'SP Executed successfully…’' END ',1
I have added 'WITH ENCRYPTION' in the above statement so that the code may not be visible in SQL Profiler at the time of execution.
Step 3:
Now that we have inserted a record in tblSQLTable with the actual stored procedure code, now is the time to execute it. Replace the code of stored procedure with the following code;
ALTER PROCEDURE [dbo].[sp_who123] @pInt int, @pVarchar varchar(Max) AS BEGIN SET NOCOUNT ON; Declare @vSql varchar(max) Select @vSql = vSQL from dbo. tblSQLTable Where tID = 1 Exec(@vSql) exec [dbo].[#sp_who123] @pInt,@pVarchar Drop Procedure [dbo].[#sp_who123] END
Important thing to note is that the parameters are the same. Application will call this stored procedure normally. The body of the stored procedure is changed. We are retrieving the record from tblSQLTable according to its respective ID. Create the stored procedure from tblSQLTable with EXEC statement. As the name of stored procedure is starting with # sign in the tblSQLTable, it will be created in the session. Execute the newly created stored procedure with # sign with the same parameters and finally, drop it. That’s it.
Step 4:
Now the question rises that still the code can be seen in the tblSQLTable. Answer is that we will encrypt the table column having code of the stored procedures. I applied encryption to tblSQLTable as;
Master Key;
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj' GO
Create Certificate;
CREATE CERTIFICATE sp_who123_SPs WITH SUBJECT = 'sp_who123 Stored Procedures'; GO
CREATE SYMMETRIC KEY StoredProcedures_Key11 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE sp_who123_SPs; GO
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY StoredProcedures_Key11 DECRYPTION BY CERTIFICATE sp_who123_SPs;
To add all the stpred proceddures in the table, ewe need to execute;
Insert into [dbo].[tblSQLTable] (tid,SNO,vSQL) Select id ,colid,[text] from syscomments where id > 0 and (colid > 1 or [text] like '%CREATE%PROC%') and number = 1 order by id,colid
Some Important updates for tblSQLTable. This is to replace # sign before each SP Name.
Update [dbo].[tblSQLTable] Set vSQL = Replace (vSQL,'CREATE PROCEDURE sp','CREATE PROCEDURE #sp') Update [dbo].[tblSQLTable] Set vSQL = Replace (vSQL,'CREATE PROCEDURE usp','CREATE PROCEDURE #usp') Update [dbo].[tblSQLTable] Set vSQL = Replace (vSQL,'CREATE PROCEDURE MIG_','CREATE PROCEDURE #MIG_') Declare @vName varchar(1000)
Declare C1 Cursor for Select [name] from sys.schemas where principal_id = 1 Open C1 Fetch Next from C1 into @vName while @@Fetch_status = 0 begin print @vName Update [dbo].[tblSQLTable] Set vSQL = Replace (vSQL,'CREATE PROCEDURE ['+ @vName +'].[sp','CREATE PROCEDURE [' + @vName + '].[#sp') Update [dbo].[tblSQLTable] Set vSQL = Replace (vSQL,'CREATE PROCEDURE [' + @vName + '].[usp','CREATE PROCEDURE [' + @vName + ']. #usp')
Update [dbo].[tblSQLTable] Set vSQL = Replace (vSQL,'CREATE PROCEDURE ['+ @vName +'].[Mig_','CREATE PROCEDURE [' + @vName + '].[#Mig_')
Update [dbo].[tblSQLTable] Set vSQL = Replace (vSQL,'CREATE PROCEDURE [' + @vName + '].[Mig_','CREATE PROCEDURE [' + @vName + '].[#Mig_')
Fetch Next from C1 into @vName end Deallocate C1
Now updating the SQL_Encrypted column;
UPDATE tblSQLTable SET SQL_Encrypted = EncryptByKey(Key_GUID('StoredProcedures_Key11') , vSQL, 1, HashBytes('SHA1', CONVERT( varbinary(max), tID))); GO
And finally, check the SQL_Encrypted column;
SELECT vSQL, SQL_Encrypted AS 'Encrypted SQL', CONVERT(varchar(max), DecryptByKey(SQL_Encrypted, 1 , HashBytes('SHA1', CONVERT(varbinary(max), tID))) ) AS 'Decrypted SQL' FROM tblSQLTable; GO
The above query should give the following result;
Now use the SQL_Encrypted column in the actual stored procedure (as the vSQL column containing actual text should be deleted and should not be there.) After applying encryption to the tblSQLTable, the actual stored procedure will look like this;
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- ============================================= -- Author: Atif Sheikh -- Create date: 12-May-2010 -- Description: Encryption SP -- =============================================
ALTER PROCEDURE [dbo].[sp_who123] @pInt int, @pVarchar varchar(Max) AS BEGIN SET NOCOUNT ON; Declare @vSql = varchar(MAx) Select @vSql = CONVERT(varchar(MAx), DecryptByKey(SQL_Encrypted, 1 , HashBytes( ‘SHA1’, CONVERT(varbinary(Max), tID)))) from dbo.tblSQLTable Where tID = 1
Exec(@vSql) exec [dbo].[#sp_who123] @pInt,@pVarchar Drop Procedure [dbo].[#sp_who123] END
The Key to open the Key will be executed when the connection is made.
To check and call the stored procedure from the query window, here is the code;
-- This will be done in application soon after connection is made OPEN SYMMETRIC KEY StoredProcedures_Key11 DECRYPTION BY CERTIFICATE sp_who123_SPs;
----------------------------------
exec [dbo].[sp_who123] 2,'Atif'
The above statements will be passed from the application normally.
Now I will discuss to add all stored procedures. First Create Certificate and Symmetric Keys. Then execute the given code. Remember to take backup of the database. The function to get the parameters of stored procedure with Default values is;
-- ============================================= -- Author: Atif -- Create date: 24-May-2010 -- Description: Get Default Value of a Parameter -- =============================================
ALTER FUNCTION [dbo].[fnGetParameterDefaultValue] (
@pSPName varchar(1000), @pParameterName varchar(100), @pDataType varchar(100) ) RETURNS varchar(1000) AS BEGIN Declare @pOutPut varchar(1000) Declare @vStartPosition int Declare @vStartPosition2 int Declare @vStartPosition3 int Declare @vStartPosition4 int Declare @vStartPosition5 int Declare @vSPText varchar(max) Declare @vSPText2 varchar(max)
-- Get the text fro syscomments (first 4000 characters if length of SP is > 4000) SELECT @vSPtext = (SELECT text FROM syscomments WHERE id = object_id(@pSPName) and colid=1 and number = 1) Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE PROCEDURE',@vSPtext),4000) if IsNull(@vSPtext,'') = '' begin -- Exit if SP Name Not found in syscomments.... Select @pOutPut = '' RETURN @pOutPut end
Set @pOutPut = '' While 1=1 Begin -- Get the position of the parameter definition. Select @vStartPosition = PatIndex('%' + @pParameterName + '%',@vSPText) -- Check if parameter exists
if @vStartPosition > 0 begin -- Get the Definition String select @vSPText = RIGHT ( @vSPText, DataLength(@vSPText)-(@vStartPosition -1)) -- Get the string breaker if (CharIndex(',',@vSPText) > 0) or (CharIndex('-',@vSPText) > 0) or (CharIndex(Char(10),@vSPText) > 0) or (CharIndex('AS',@vSPText) > 0) begin Set @vStartPosition = CharIndex(',',@vSPText,Len(@pParameterName))-1 Set @vStartPosition2 = CharIndex('-',@vSPText,Len(@pParameterName))-1 Set @vStartPosition3 = CharIndex(Char(10),@vSPText,Len(@pParameterName))-1 Set @vStartPosition4 = CharIndex('AS',@vSPText,Len(@pParameterName))-1 Set @vStartPosition5 = CharIndex('OUT',@vSPText,Len(@pParameterName)) -1 if @vStartPosition <= Len(@pParameterName) Set @vStartPosition = 10000000 if @vStartPosition2 <= Len(@pParameterName) Set @vStartPosition2 = 10000000 if @vStartPosition3 <= Len(@pParameterName) Set @vStartPosition3 = 10000000 if @vStartPosition4 <= Len(@pParameterName) Set @vStartPosition4 = 10000000 if @vStartPosition5 <= Len(@pParameterName) Set @vStartPosition5 = 10000000 Select Top 1 @vStartPosition = [value] from dbo.fnSplit(Cast(@vStartPosition as varchar(10)) + ',' + Cast(@vStartPosition2 as varchar(10)) + ',' + Cast(@vStartPosition3 as varchar(10)) + ',' + Cast(@vStartPosition4 as varchar(10)) + ',' + Cast(@vStartPosition5 as varchar(10)) ,',') order by Cast([value] as int) end else begin -- SP text must atleast have AS to break the parameter definition string Set @vStartPosition = CharIndex('AS',@vSPText) - 1 end -- Get the specific Definition String Set @vSPText2 = Left(@vSPText,@vStartPosition) -- Check if you got the right one by data type... if CharIndex(@pDataType,@vSPText2) > 0 begin --Select 'IN' --Select @text2 if CharIndex('=',@vSPText2) > 0 begin -- check the default value Select @pOutPut = Right(@vSPText2,DataLength(@vSPText2) - CharIndex('=',@vSPText2)) -- We have default value assigned here if Right(@pOutPut,1) = ',' Set @pOutPut = Left(@pOutPut,DataLength(@pOutPut)-1) end else begin --Set @pOutPut = 'No Default Value Defined...' -- We DO NOT have default value assigned here Set @pOutPut = '' end --No need to work further with this parameter BREAK end else begin --Set @vSPText = SubString(@vSPText,@vStartPosition + Len(@vSPText2),4000) -- Cut the SP text short and loop again Set @vSPText = SubString(@vSPText,@vStartPosition,4000) end -- This should never be the case. Just a check.... if Datalength(@vSPText) < Datalength(@pParameterName) Break end else begin --Set @pOutPut = 'Parameter Not Found...' -- Wrong parameter search... Set @pOutPut = '' Break end End Select @pOutPut = rtrim(ltrim(@pOutPut)) RETURN @pOutPut END
The following code will generate the ALTER PROCEDURE script and will execute against all the stored procedures in the database.
Begin Tran Declare @voName varchar(1000) Declare @voName2 varchar(1000) Declare @vtID bigint Declare @vAlterSQL varchar(Max) Declare @vAlterSQL2 varchar(Max) Declare @vAlterParams varchar(Max) Declare @vActualAlterParams varchar(Max) Declare @vDeclareStr varchar(Max) Declare @vAddSQL varchar(Max) Declare C1 Cursor For Select Distinct tID,'[' + c.[name] + '].[' + b.[name] + ']' as oName ,'[' + c.[name] + '].[#' + b.[name] + ']' as oName2 from tblSQLTable a inner Join sys.objects b on b.Object_id = a.tid inner join sys.schemas c on c.Schema_id = b.schema_id where (vSQL like '%CREATE PROCEDURE %#usp%' or vSQL like '%CREATE PROCEDURE %#Mig_%' or vSQL like '%CREATE PROCEDURE %#sp_%') -- And a.tid = 1721577717 Open C1 Fetch Next from C1 into @vtID,@voName,@voName2 While @@Fetch_Status = 0 begin print @voName Set @vAlterParams = '' Set @vActualAlterParams = '' Set @vAlterSQL = '' Set @vAlterSQL2 = '' Set @vDeclareStr = '' Set @vAddSQL = '' Select @vAlterParams = @vAlterParams + ',' + a.[Name] + ' ' + b.[Name] + case when b.system_type_id in (34,35,36,48,52,56,58,59,60,61,62,68,99,104,122,127,189,241,231) then '' else '(' + case when a.max_length = -1 then 'Max' else Cast(a.max_length as varchar(100)) end + ')' end + case when dbo.fnGetParameterDefaultValue(@voName,a.[Name],b.[Name]) = '' then '' else '=' + dbo.fnGetParameterDefaultValue(@voName,a.[Name],b.[Name]) end
+ case when is_output = 1 then ' OUTPUT' else '' end ,@vActualAlterParams = @vActualAlterParams + ',' + a.[Name] + case when is_output = 1 then ' OUTPUT' else '' end from sys.parameters a Inner Join sys.types b on b.system_type_id = a.system_type_id where Object_id = @vtID and b.name <> 'sysname' and b.name <> 'FloatPercentage' order by parameter_id Set @vAlterParams = Stuff(@vAlterParams,1,1,'') Set @vActualAlterParams = Stuff(@vActualAlterParams,1,1,'') Set @vAlterSQL = ' ALTER PROCEDURE ' + @voName + ' ' + @vAlterParams + ' AS BEGIN SET NOCOUNT ON; Declare @vSql varchar(max) Set @vSql = '''' ' Select @vAlterSQL2 = @vAlterSQL2 + ' Select @vSql' + Cast(SNO as varchar(10)) + ' = CONVERT(varchar(MAX), DecryptByKey(SQL_Encrypted, 1 , HashBytes( ''SHA1'', CONVERT(varbinary(Max), tID)))) from dbo.tblSQLTable Where tID = ' + Cast(@vtID as varchar(100)) + ' And SNO = ' + Cast(SNO as Varchar(10)) ,@vDeclareStr = @vDeclareStr + 'Declare @vSQL' + Cast(SNO as Varchar(10)) + ' varchar(MAX) ' + Char(10) + Char(13) ,@vAddSQL = @vAddSQL + '+ @vSQL' + Cast(SNO as Varchar(10)) From tblSQLTable where tID = @vtID
Set @vAddSQL = ' Set @vSQL = ' + Stuff(@vAddSQL,1,1,'') Set @vAlterSQL = @vAlterSQL + @vDeclareStr + @vAlterSQL2 + @vAddSQL + ' print @vSql Exec(@vSql) exec ' + @voName2 + ' ' + @vActualAlterParams + ' Drop Procedure ' + @voName2 + ' END'
print @vDeclareStr print @vAlterSQL2 print @vAddSQL Exec (@vAlterSQL) Set @vAlterSQL = '' Fetch Next from C1 into @vtID,@voName,@voName2 end Deallocate C1 commit
I hope you will not get any error in the above code. I tested on 3 databases of different domains and it was working fine. After executing the above code, you can delete the column vSQL from tblSQLTable.
I will not discuss Encryption here in detail. Just one thing to mention is that the EncryptByKey returns varbinary with a maximum size of 8000 bytes.
I hope this will help someone who is on SQL Server 2005 and looking for encryption of stored procedures. The same pattern can be applied on functions but I have not tested it yet on functions.
I know that there are some performance issues like possible Temp DB contention in this solution. I will be expecting a lot of suggestions and comments in this regard so that we together can make this solution more workable and efficient.