Category: Encryption Tips

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

 

 

 

 

 

 

 

The WITH ENCRYPTION clause is used to encrypt the definition of SQL Server objects (views, stored procedures, functions ), so that no one may be able to view the definition/code of that specific object. Using the clause while creating an object is fairly easy, but there are some serious points and possible complications.

Denying View Definition permission will hide the object for a specific user and also the user will not be able to see the definition using sp_helptext.

Introduction: Encrypting at the Database Level :

Transparent data encryption (TDE) is a new encryption feature introduced in Microsoft® SQL Server™ 2008. to use encryption in Microsoft SQL Server 2005, the column data type must be changed to varbinary;

Q & A :

What happen if you need to restore or detach/attach an encrypted database on a different server or instance?
The certificate that was used to encrypt the database encryption key must be available. Otherwise, the operation will fail.

TDE :
Transparent data encryption is the new database-level encryption feature introduced in SQL Server 2008.
How to Enable TDE :
To enable TDE, you must have the normal permissions associated with creating a database master key and certificates in the master database. You must also have CONTROL permissions on the user database.
To enable TDE :

1- Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some password’;
2- Create Certificate
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’;
3- Create Database Encryption
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert
4- Enable TDE in Database
ALTER DATABASE myDatabase SET ENCRYPTION ON

Finally, let’s find the state of the database encryption.
SELECT db_name(database_id), encryption_state
FROM sys.dm_database_encryption_keys

Because of some security policies we need to require our code to be safe from Users who are going to use SQL Server database and objects of them and some outside threat. We have different different users to access the database objects or used for application.

We have so many ways to encrypt data, but here i am talkig about the code encryption. With this encryption security, Users can execute the stored procedures but can not view the code.

Let us check the how the Stored Procedures can be encrypted.

–Create Database Demo

Create Database Demo

go

— Creating table

IF (OBJECT_ID(‘UserMaster’,‘U’)> 0)

DROP TABLE UserMaster

GO

CREATE TABLE UserMaster

( UserId INT,

UserName VARCHAR(100),

UserPwd NVARCHAR(100) )

GO

— Inserting demo records

INSERT INTO UserMaster

SELECT ‘1’,‘User1’,‘pwd1’

UNION ALL

SELECT ‘2’,‘User2’,‘pwd2’

UNION ALL

SELECT ‘3’,‘User3’,‘pwd3’

GO

———————————————-

— Creating Stored Procedure without encryption

CREATE PROCEDURE GetUserDataWithoutEncrypt

AS

BEGIN

SET NOCOUNTON

SELECT UserID , UserName , UserPwd FROM UserMaster

END

GO

— Creating Stored Procedure with encryption

CREATE PROCEDURE GetUserDataWithEncrypt

WITH ENCRYPTION

AS

BEGIN

SET NOCOUNTON

SELECT UserID , UserName , UserPwd FROM UserMaster

END

GO

————————————–

EXEC GetUserDataWithoutEncrypt

EXEC GetUserDataWithEncrypt

GO

——————————————-

EXEC SP_HELPTEXTGetUserDataWithoutEncrypt

EXEC SP_HELPTEXTGetUserDataWithEncrypt