Category: SQL Server 2008

introduction

in SQL Server we can Create Stored procedure , View and Function with Encryption option by this option  no body can see the text of the object so a lot of Developer and DBA use this option to protect his Code but the problem down when any one of them forget what he write in this Encryption object it’s big problem because he can’t make Alter to this Encryption object but today we will learn how to troubleshot this  problem by creating stored procedure make Decryption to any Encrypted object it will show to me the text write in the encryption object

NOTE : this SP I’m not create it but I take it copy from the internet

1- Create Encryption Stored procedure

Create PROC getdatabase_Name

With Encryption option

as

Begin

Select name from sys.Databases where Database_id > 4

End

2- try to display code text

EXEC sp_helptext ‘getdatabase_Name’;

SQL server will tell you this Stored is Encrypted so you can’t see the Code text

3- Create SP to show Code text for any encryption object

CREATE PROCEDURE dbo.ShowDecrypted(@ProcName SYSNAME = NULL)

AS

–Jon Gurgul 27/09/2010

–Adapted idea/code from shoeboy/joseph gama

SET NOCOUNT ON

IF EXISTS

(

SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e

on (ec.[endpoint_id]=e.[endpoint_id])

WHERE e.[name]=’Dedicated Admin Connection’

AND ec.[session_id] = @@SPID

)

BEGIN

DECLARE @i BIGINT,@a NVARCHAR(MAX),@b NVARCHAR(MAX),@d NVARCHAR(MAX),@c NVARCHAR(MAX)

SET @a=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)

SET @b=’ALTER PROCEDURE ‘+ @ProcName +’ WITH ENCRYPTION AS ‘+REPLICATE(‘-‘, 8000)

BEGIN TRANSACTION

EXECUTE (@b)

SET @c=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)

ROLLBACK TRANSACTION

SET @d = REPLICATE(N’A’, (DATALENGTH(@a) /2 ))

SET @i=1

WHILE @i<=(DATALENGTH(@a)/2)

BEGIN

SET @d = STUFF(@d, @i, 1,NCHAR(UNICODE(SUBSTRING(@a, @i, 1)) ^(UNICODE(SUBSTRING(‘CREATE PROCEDURE ‘+ @ProcName +’ WITH ENCRYPTION AS ‘ + REPLICATE(‘-‘, 8000), @i, 1)) ^UNICODE(SUBSTRING(@c, @i, 1)))))

SET @i=@i+1

END

SELECT @d [StoredProcedure]

END

ELSE

BEGIN

PRINT ‘Use a DAC Connection’

END

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

4- no try to Execute your SP

EXCE  ShowDecrypted ‘getdatabase_Name’

Special thanks to the engineer Ahmed Abdel Moteleb

Introduction

Database Encryption this very Important Feature in SQL Server 2008 becouse in 2005 you can make Encryption but not in Whole Database you can make it on Tables but when Comming SQL Server 2008 Come With him this New Feature Database Encryption nooooooooooooooooooooooow You Can Encrypt The Whole Database i write Article in this Subject Since one year (02/11/2011)Check it :

https://mostafaelmasry.wordpress.com/2011/11/02/transparent-data-encryption-tde-2/

Before 2 days my Manager Aske me to make Encryption to Whole Database on one from My Client but the Problem this Client use SQL Server 2005 and i forget it’s Impossible to make Encryption on Whole Database in SQL Server 2005 So i asked my Friend

Eng.Ahmed Abdel Moteleb Database Administrstrator and Developer Team Leader

He told me you can make it on SQL Server 2008 and i try it more than once so try this Code and he Send to me this Script : Lets go to See the Script

1-CREATE MASTER KEY

USE MASTER;

GO

IF EXISTS(SELECT*FROMsys.symmetric_keysWHEREname=N’##MS_DatabaseMasterKey##’)

Begin

DROPMASTERKEY;

CREATEMASTERKEYENCRYPTIONBYPASSWORD=‘@dmin$afeP@$$w0rd’;

Print‘Master Key Drop + Created’

END

Else

begin

CREATEMASTERKEYENCRYPTIONBYPASSWORD=‘@dmin$afeP@$$w0rd’;

print‘masteer key Created’

end

GO

2-CREATE CERTIFICATE

IF EXISTS(SELECT*FROMsys.certificatesWHEREname=N’MYDATABASECERT’)

Begin

DropCertificateMYDATABASECERT

CREATECERTIFICATEMYDATABASE_CERTWITHSUBJECT=‘MYDATABASECERT’

End

Else

begin

Create CERTIFICATEMYDATABASE_CERTWITHSUBJECT=‘MYDATABASECERT’

End

Go

3-Backup certificate

Backup certificateMYDATABASE_CERTtofile=‘G:\T\MYDATABASECERT_CERT’with private key(file=‘G:\T\MYDATABASECERT_KEY’, encryption bypassword=‘@dmin$afeP@$$w0rd’);

4- Encrypt database with my created Certificate

create databaseencryptionkeywithalgorithm=aes_128

encryption byservercertificateMYDATABASECERT;

alter database{Database Name}setencryptionon;

5- take full backup 4 ur database with over write option

6- Take log backup 4 ur database with appen option

7- delete ur database orders_book and delete ur certificate on root master–>security–>certificate

8- try to restore ur backup normally — u will get error no certificate

Fix the Error

 

go

create certificateMYDATABASE_CERTfromfile=‘G:\T\MYDATABASECERT_CERT’with private key(file=‘G:\T\MYDATABASECERT_KEY’,

decryption bypassword=‘@dmin$afeP@$$w0rd’);

Now try to restore ur backup again u will successfuly restore

Special thanks to the engineer Ahmed Abdel Moteleb