Microsoft SQL Server

Security & Encryption

Hamid J. FardMicrosoft Certified Master: SQL Server 2008

SpeakerHamid J. Fard Consultant/Trainer/Speaker Co-Founder & Data Platform Expert, Fard Solutions Sdn.

Bhd. Email: enquiry@fard-solutions.com Website: http://www.Fard-Solutions.com

Microsoft Certified Master: SQL Server 2008

AgendaVirtual Service AccountEncrypting the session with SSLConfiguring a firewall for SQL Server accessEncrypting data with symmetric keysAuthenticating stored procedures by signatureProtecting SQL Server against Denial of Service

Virtual Service AccountIt is a new feature in Windows Server 2008 R2.Requires no password management.Can access network with a computer identity, like

Network Service account.Gives Network Service account security benefits

with a distinct account per service.It cannot be deleted, and it is available whenever

the service is installed on the Windows Server 2008 R2.

It can part of a local group and like a normal user on local ACL rights.

Virtual Service Account (Cont.)

Encrypting the Session with SSLProtecting information from network sniffing

attempts.You need to purchase an SSL certificate from a

Certificate Authority (CA), such as VeriSign, Comodo or DigiCert.

Certificate must be installed using the same account running SQL Server service or an account with administrator privileges.

Encrypting the Session with SSL (Cont.)

Configuring a firewall for SQL Server access

Encrypting data with symmetric keysSymmetric key is faster than Asymmetric key,

but it is less secure.Symmetric key is accepting vary range of

algorithm, such as:DES , TRIPLE_DES , TRIPLE_DES_3KEY , RC2 , RC4 ,

RC4_128 , DESX , AES_128 , AES_192 , AES_256.Stick with AES, which is strongest, the longer the

key, the stronger the protection will be.The sys.Openkeys DMV shows you the keys

that are opened for the current session only.

Encrypting data with symmetric keys (Cont.)Consider the following statement:

--Create the Symmetric Key encrypted by password.Create Symmetric Key SymKey With Algorithm = AES_256 , Key_Source = 'SymKey Source’,Identity_Value = 'SymKey Value’Encryption by Password = 'SymKeyPassword',

Password = 'SymKeyPassword2';GoOpen Symmetric Key SymKey Decryption by Password =

'SymKeyPassword';-- Do something here…Close Symmetric Key SymKey;

Authenticating Stored Procedures by Signature

User A

User B

Cert User Cert

SP_T Table_T

Signed

Create

HasAccess

GrantedTo

Execute

SwitchContext

SwitchContext

Authenticating Stored Procedures by Signature(Cont.)

Consider the following statements:

Create Certificate SignCert Encryption By Password = 'Pa$$w0rd’With Subject = 'Certificate', Expiry_date = '12/12/2013';GoAdd Signature to TestCertSP By Certificate SignCert With

Password = 'Pa$$w0rd’ ;GoCreate User CertUser From Certificate SignCert;GoGrant Execute On TestCertSP To CertUser;

Protecting SQL Server against Denial of ServiceThe goal is to overload the server with requests

to crash it or make it unavailable for normal operations.

First solution is to protect the server by Network Firewall to block suspicious IP addresses.

Second solution is, SQL Server connections must be limited.

Third solutions is to enable Use Query Governor to Prevent long-running Queries.

Forth solution is using Resource Governor.

DemoHow to Encrypt the session by SSL.How to Configure the SQL Server Firewall.How to Encrypt data with symmetric key.How to Protect SP by certificate signature.How to protect SQL Server against DoS attack.

Questions and Answers

?