abhishek[MS] (Moderator):
Hi everyone. Welcome to today's chat on SQL Server Security. the chat will begin at 5:00 PM.

abhishek[MS] (Moderator):
We will be starting the chat today on SQL Server Security in another 15 mins at 5:00 PM.

abhishek[MS] (Moderator):
hello everybody :) a very good evening to all of you !

abhishek[MS] (Moderator):
Welcome to today's chat on SQL Server Security

abhishek[MS] (Moderator):
hosted by our two SQL Server MVPs: Vinod M Kumar and Srinivas Sampath

abhishek[MS] (Moderator):
You can know more about the MVP program at: www.microsoft.com/india/mvp

abhishek[MS] (Moderator):
Let's begin with a quick intro about our experts

abhishek[MS] (Moderator):
Srinivas Sampath works for SCT Software Solutions(Bangalore). He graduated with a bachelors in Computer Technology(PSG College of Technology) and later pursued MCA (Madras University).He joined SCT (then called Exeter Systems) in 1997 and has been with them for the past 6 years.

abhishek[MS] (Moderator):
His area of expertise include database architectures (using SQL Server) and enterprise application architectures using Microsoft Technologies. Srinivas currently is a Product Development Manager and also head of Technology Labs, a group that focuses on latest Microsoft technologies and their applicability into their product lines.

abhishek[MS] (Moderator):
His interests include SQL Server 2000 programming, Office 2003 programming and .NET. And you can reach him at ssampath@sct.co.in 
abhishek[MS] (Moderator):
To introduce Vinod M Kumar,

abhishek[MS] (Moderator):
Vinod Kumar did his Bachelors of Engineering in Mechanical from Anna University, Chennai. After graduation, he joined SCT Software Solutions (then Exeter Systems) where he worked for about 4 years. He has worked for a team called Technology Labs which evaluates various Microsoft technologies and products and their relevance in this line of business. He is presently with Intel Technologies, Bangalore. He has been working on Microsoft Technologies all along his professional career and is very inclined towards learning every new concept that Microsoft comes out with. In this process, he has gathered tons of knowledge to share with all.

abhishek[MS] (Moderator):
Catch all the resources and articles Vinod has written at www.ExtremeExperts.com,the site he co-hosts. He has been very passionate about SQL Server programming and loves to experiment different programming tricks in SQL Server.

Vinod is an active volunteer in INETA and PASS. He has initiated a SQL Server community in Bangalore. To capture more events on SQL Server, join the group at http://groups.msn.com/SQLBang

abhishek[MS] (Moderator):
and you can reach Vinod at Email ID: vinodk@mvps.org 

Web Site: www.ExtremeExperts.com 
abhishek[MS] (Moderator):
A few chat rules as usual before we start the chat ..

abhishek[MS] (Moderator):
1. Please refrain from sending any private messages to the expert during the chat

abhishek[MS] (Moderator):
2. This chat will last for one hour. During this hour, our Experts will respond to as many questions as they can. Please understand that there may be some questions we cannot respond to due to lack of information or because the information is not yet public. We encourage you to submit questions for our Experts.

abhishek[MS] (Moderator):
3. We ask that you stay on topic for the duration of the chat. This helps the Guests and Experts follow the conversation more easily. We invite you to ask off topic questions after this chat is over.

abhishek[MS] (Moderator):
4. Please use the radial button "submit a question " to ask any questions to the expert

abhishek[MS] (Moderator):
So we are all set for a great chat session by our MVPs , we thank you all for joining us today..

abhishek[MS] (Moderator):
and let's get the chat rolling ...

SriSamp [MVP] (Expert):
Its great to be back again. Hi all!

Vinod_MVP (Expert):
Hi all. Good Afternoon, good evening or good morning depending on where you are.

Vinod_MVP (Expert):
Welcome to the chat on some of the security enhancements in SQL Server 2005.

Vinod_MVP (Expert):
So, in this chat we will walk through some of the security enhancements that have been introduced in SQL Server 2005. Even thought the chat will give you a glimpse of what is in store in SQL Server 2005. We will also try to demystify some of the jargons that fly around when you hear SQL Server 2005.

Vinod_MVP (Expert):
And the fun part being there has been enough empahsis by Microsoft on security and this topic is always near and dear to SQL lovers. Having said that this is also an area which is least explored from the developer point of view.

Vinod_MVP (Expert):
Going forward SQL Server 2005 will surely change this trend and it forces even developers to think out-of-the-box to build robust and follproof application architecture.

Vinod_MVP (Expert):
We thought we will walk through a couple of interesting enhancements in this particular chat.

Vinod_MVP (Expert):
Let us start with separation of users and schema:

Vinod_MVP (Expert):
The big change in the security model in SQL Server 2005 simplifies the relationship between users and objects and lets you add and drop users without needing to worry about objects the users own.

Vinod_MVP (Expert):
For ANSI compliance, SQL Server 2005 distinguishes between users and schemas.

Vinod_MVP (Expert):
Schema as a collection of database objects that one user owns and that form one namespace.

Vinod_MVP (Expert):
You can think of a schema as a container of objects.

Vinod_MVP (Expert):
Every new database you create in SQL Server 2005 includes several schemas.

Vinod_MVP (Expert):
Corresponding to the dbo, INFORMATION_SCHEMA, and guest "users" in SQL Server 2000, each SQL Server 2005 database has schemas with these names.

Vinod_MVP (Expert):
You can assign new users a default schema that might not exist when you create the user.

Vinod_MVP (Expert):
A user's default schema is used for name resolution during object creation or object reference.

Vinod_MVP (Expert):
Hence in SQL Server 2005, the name resolution for a table <table1> will happen in order of: Look for sys.table1, then Look for <UserName>.table1 and finally Look for dbo.table1.

Vinod_MVP (Expert):
Hence you can see that the name resolution of UserName.ObjectName will now translate to SchemaName.ObjectName.

Vinod_MVP (Expert):
We still hold all the backward compatibility and hence when we migrate objects from SQL Server 2000 a default schema as the username is created automatically.

Vinod_MVP (Expert):
And the concept of objects created by sysadmins reside under the dbo schema still holds good in SQL Server 2005.

Vinod_MVP (Expert):
Some of the points to keep in mind include:

Vinod_MVP (Expert):
a. Database can contain multiple schemas

Vinod_MVP (Expert):
b. Each schema has an owning principal – user or role: hence only one user can own a schema. But multiple users can have access to the schema.

Vinod_MVP (Expert):
c. Each user has a default schema for name resolution, this can be defined during the user creation or can be later altered for a user. If unspecified it resides on the dbo schema.

Vinod_MVP (Expert):
d. Object creation inside schema requires CREATE permission and ALTER or CONTROL permission on the schema

Vinod_MVP (Expert):
e. Ownership chaining is still based on owners not schemas. This has been facilitated for backward compatibility.

Vinod_MVP (Expert):
So that was a quick breeze of what User-Schema Separation concept is all about.

Vinod_MVP (Expert):
Any questions on the concept?

SriSamp [MVP] (Expert):
Now that Vinod has given the basics of the user-schema separation semantics, I hope you can see the flexibility that this feature provides you.

SriSamp [MVP] (Expert):
Using this feature, you can easily segregate the objects of your application based on business needs and associate users with the same.

SriSamp [MVP] (Expert):
To understand the semantics of user-schema separation, here is a small example that you can try out.

SriSamp [MVP] (Expert):
The example creates a couple of accounts and a schema and demonstrates object creation in the created schema.

SriSamp [MVP] (Expert):
-- Create two logins for usage

CREATE LOGIN Fred WITH PASSWORD = 'pa$$w0rD'

CREATE LOGIN Sam WITH PASSWORD = 'pa$$w0rD'

-- Create a schema owned by Fred

CREATE SCHEMA HumanResources AUTHORIZATION Fred

-- Create a user with an associated schema and provide

-- relevant permissions

CREATE USER Fred FOR LOGIN Fred WITH DEFAULT_SCHEMA = HumanResources

GRANT CREATE TABLE TO Fred
SriSamp [MVP] (Expert):
Note the syntax for the create user wherein we specify a default schema to be used. Once done, any object created by the Fred user will go to that schema.

SriSamp [MVP] (Expert):
Fred can also explicitly create objects using the <schema>.<object> syntax, but it is not required.

SriSamp [MVP] (Expert):
-- Create another user, but with no default schema. In this

-- case, the schema defaults to dbo

CREATE USER Sam FOR LOGIN Sam

-- Query system tables to see what has actually happened

SELECT name, type_desc, default_schema_name

                FROM sys.database_principals

SriSamp [MVP] (Expert):
After running the above query, you can see the schema's that are associated with the users that we created earlier.

SriSamp [MVP] (Expert):
-- Switch context to Fred and try some commands

EXECUTE AS User = 'Fred'

GO

-- This command will succeed, since the default schema for

-- Fred is HumanResources

CREATE TABLE testTable (colA INT)

-- Check out the details of the created table

SELECT

                t.name, t.object_id, t.schema_id, t.type_desc, s.name

FROM sys.tables t

                JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE

                t.name = 'testTable'

SriSamp [MVP] (Expert):
When you run the above command, you can see that the table got created in the schema that was associated with Fred.

SriSamp [MVP] (Expert):
-- Go back to the original login

REVERT

-- Switch context to Sam and try some commands

EXECUTE AS User = 'Sam'

GO

-- None of these commands will execute, since Sam does not have

-- any permissions on any schema

CREATE TABLE sampleTable (colA INT)

SELECT * FROM HumanResources.testTable

-- Go back to the original login

REVERT

SriSamp [MVP] (Expert):
Now, when Sam or Fred leaves, you do not have to worry about changing the permissions of the various objects that they created.

SriSamp [MVP] (Expert):
Since Fred was associated with a schema, all you have to do is change the owner of the schema to someone else and then drop the Fred user.

SriSamp [MVP] (Expert):
Everything else is maintained as is and its business as usual.

SriSamp [MVP] (Expert):
Any questions?

Vinod_MVP (Expert):
Kishore, to answer your question on the default option, in SQL Server 2000 compatibility mode then this mapping is automatically created provided the user is NOT a sysadmin. But in case of SQL Server 2005 you need to create a schema manually of the same name and use that as the default schema for the user.

Vinod_MVP (Expert):
Q: I had a question on transferring schema privileges from a local sql server 2005 database to a remote one, is this possible and how can it be achieved
A: You need to understand that Schema is also an object inside SQL Server. So the principle's that were applied earlier will apply to Schema's too wrt permissions. Now users are always associated within a database instance and cannot operate beyond the boundary of the Server. So owning of objects by a user outside the instance is not supported.

Vinod_MVP (Expert):
Q: How do I associate the Part of the Fred Schema to some X?
A: What do you mean by part of Fred schema? A schema can be used as a container and permissions can be granted to the user to the schema to operate. The permissions can be grannular to the point where a user can create views on the schema and cannot create any tables. So this level of detailing is possible. Schema is an abstraction layer and this is as per the ANSI standards definition.

Vinod_MVP (Expert):
Q: Now by default, MS SQL provides secure by default settings, for a new installation , after its completed, how does one, change the security settings
A: To re-inforce the SD3C initiative that Microsoft took seriously. Namely the secure by design, secure by default, and secure in deployment and communication.

The fundamental way to reduce security issues are:

1. Restricting user access to the server - strong authorization model

2. Disabling services and restricting service configuration - Enable what you need specifically. Secure by default logic.

3. Reducing the surface area of attack for new features - reduce DOS attacks

SriSamp [MVP] (Expert):
Q: Now by default, MS SQL provides secure by default settings, for a new installation , after its completed, how does one, change the security settings
A: There are different ways to do it. You can use regular T-SQL commands or use the new Surface Area Configuration tool to change some server wide settings.

Vinod_MVP (Expert):
Ok, if there are no further questions we will move ahead.

Vinod_MVP (Expert):
Moving to the next interesting topic and something which is least explored by all, Signed Modules.

Vinod_MVP (Expert):
Lets start and understand "What it is and Why its there?"

Vinod_MVP (Expert):
SQL server 2005 supports the ability to sign a particular module with a view to achieve the following scenario.

Vinod_MVP (Expert):
If you have a requirement that users can access a particular table or perform a particular operation, but only when going through a particular stored procedure, then signing the module and granting permissions on the module can help you. This way the operation or the table/resource access (performed within the proc) can be achieved only through the proc but not directly. All they need is permissions on the proc and not permissions for the underlying operations.

Vinod_MVP (Expert):
This kind of upfront permission checking behavior, where the end user’s permissions are checked on the proc and not for the underlying operations is a capability that signed modules provides.

Vinod_MVP (Expert):
So lets understand "How is it different from Execute AS:"

Vinod_MVP (Expert):
When the access is made to a table it looks like the execution context performed that operation, not the calling user. Of course the audit trail will also capture the original context that caused the invocation to happen in the execute as context. But from a SQL server authorization perspective, the access is made in the context of the execute as principal. But in Signed modules on the other hand do not change the execution context. It works differently.

Vinod_MVP (Expert):
Lets move to "How does Signed Modules work?" then ...

Vinod_MVP (Expert):
To explain this we will take a scenario and walk through the same.

Vinod_MVP (Expert):
Lets assume a proc (say SP1) is signed by a certificate (say C1). And lets assume that the proc selects from a table T1. User1 executes the proc and has permissions on the proc, but does not have permissions on the underlying table.

Vinod_MVP (Expert):
When the procedure is invoked, underneath the covers the signature of the procedure is checked. If the signature is verified and found to be valid, the certificate (in this case C1) is added to the context information of User1.

Vinod_MVP (Expert):
When the access is made to the table (in this case T1) we check whether the user has access to the table. This authorization check works as follows:

Vinod_MVP (Expert):
We check if the user, or any group or role that the user is a member of, or the certificate has been denied permissions on the table. If so we deny access.

Vinod_MVP (Expert):
If the user, or any group/role that the user is a member of, or the certificate has been granted permissions on the table, we grant access.

Vinod_MVP (Expert):
In reality, we don’t grant or deny permissions to the certificate directly. Since in the SQL server security model, permissions are granted to users or logins we map the certificates to users or logins and grant them access.

Vinod_MVP (Expert):
To summarize our scenario and the steps we took to ensure that user1 gets access to T1 but only when going through SP1, we do the following:

Vinod_MVP (Expert):
1)       We sign the proc with C1

ADD SIGNATURE TO <<Proc Name>> BY CERTIFICATE <<My Certificate>> WITH PASSWORD = 'C0mplexP@55word'
Vinod_MVP (Expert):
2)       We create a user mapped to C1

CREATE USER <<User>> FOR CERTIFICATE <<My Certificate>>
Vinod_MVP (Expert):
3)       We grant select permissions on T1 to the user mapped to C1

GRANT SELECT ON T1 to <<My Certificate>>
Vinod_MVP (Expert):
4)       We don’t grant any permissions on T1 to User1

Vinod_MVP (Expert):
5)       We grant execute permissions on SP1 to User1

GRANT EXECUTE ON <<Proc Name>> to <<User>>
Vinod_MVP (Expert):
So you will notice one other difference between execute as and signed modules that falls out of the fact the in execute as the execution context is changed, whereas in the signed modules case it is not. That difference has to do with deny permissions. In the case of a signed module, the deny permissions on the underlying table against the end user (in this case User1) will be honored. But in the case of execute as it will not because the access check is being made against the execution context.

Vinod_MVP (Expert):
More on certificates will be taken in the next section where we explore Encryption.

Vinod_MVP (Expert):
That was a quick demo of how we use signed modules. It is powerful but using it at the right time is the key here ...

Vinod_MVP (Expert):
Any questions on this fabulous concept?

SriSamp [MVP] (Expert):
OK, with some of the new security features covered, let us move to the next and final interesting feature - Encryption.

SriSamp [MVP] (Expert):
Its a fairly long topic. So let me see how much I can cover here...

SriSamp [MVP] (Expert):
In the past, SQL Server 2000 did not have any built-in support for data encryption.

SriSamp [MVP] (Expert):
People had to rely on 3rd party procedures (which were usually implemented as extended stored procedures) for data encryption and decryption in the database.

SriSamp [MVP] (Expert):
Now, in SQL Server 2005, you have built-in support for data encryption.

SriSamp [MVP] (Expert):
SQL Server 2005 encrypts data with a hierarchical encryption and key management infrastructure.

SriSamp [MVP] (Expert):
Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys.

SriSamp [MVP] (Expert):
Corresponding T-SQL statements and functions are provided that will allow you to create these entities and use them for encrypting or decrypting data.

SriSamp [MVP] (Expert):
The hardest part of encryption is key management - which is basically protecting the keys themselves.

Vinod_MVP (Expert):
Q: yes, Can we generate a different Certificate using a different certificate creation tool, and then apply it on our proc ?
A: the certificate concept is used from within SQL Server and no external certificates can be used for this process.

SriSamp [MVP] (Expert):
If an attacker gets access of say, the symmetric key that has been used to encrypt the data, then all your data can be decrypted back.

SriSamp [MVP] (Expert):
To protect the key itself, you can either allow SQL Server to manage it for you, or you can manage the keys yourself.

SriSamp [MVP] (Expert):
If you allow SQL Server to manage the keys for you, it uses a hierarchy of encryption objects to protect various types of keys at various scopes in the server.

SriSamp [MVP] (Expert):
We have to understand this scope properly to understand how everything comes together. In the following points, we will understand this hierarchy.

abhishek[MS] (Moderator):
We are almost at middle point of chat. I would like to encourage you to ask your queries to the Experts on the topic of  discussions.

Vinod_MVP (Expert):
Q: Generally NT security is considered to be more secured compared to SQL. Is this been rectified in SQL 2005
A: Administrators will be able to specify Microsoft Windows-style policies on standard logins so that a consistent policy is applied across all accounts in the domain. This has increased the scope and standardizing policies for SQL Logins too.

Regardless of authentication mode and policy enforcement, SQL Server 2005 Setup Wizard does not permit non-blank passwords for sa account during the installation. Another Secure by default implementation.

You can use CHECK_EXPIRATION and CHECK_POLICY clauses when creating new logins with the CREATE LOGIN T-SQL statement. CHECK_EXPIRATION controls password expiration, while CHECK_POLICY determines both the state of password complexity and account lockout settings. Including the MUST_CHANGE clause forces the user to change the password when logging in for the first time.

SriSamp [MVP] (Expert):
At the top of the hierarchy, you have the "Service Master Key" which is a symmetric key created when SQL Server is installed.

SriSamp [MVP] (Expert):
The Service Master key is protected by Windows DPAPI.

SriSamp [MVP] (Expert):
SQL Server takes care of managing the Service Master key for you, but you can do some maintenance tasks on it, like backing it up to a file, restoring etc.

SriSamp [MVP] (Expert):
At each database level, you can have a "Database Master Key", which acts as the root encryption object for all keys, certificates and data in the database.

SriSamp [MVP] (Expert):
Each database can have only 1 database master key. The Database Master Key is created with the following command.

SriSamp [MVP] (Expert):
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pa$$w0rD'

Vinod_MVP (Expert):
Q: Is it similar to public- privte key in .net?
A: Very much. More on symmetric and a-symmetric keys to follow as Srini will discuss in detail.

SriSamp [MVP] (Expert):
Once you have created the database master key, you can use it to create any of three types of keys. They are:

SriSamp [MVP] (Expert):
(a) Asymmetric keys - used for public key cryptography with a private and public key pair.

SriSamp [MVP] (Expert):
Wrong smiley!! (1) Asymmetric keys - used for public key cryptography with a private and public key pair.

SriSamp [MVP] (Expert):
(2) Symmetric keys - used for sharing secrets where the same key can be used for encryption and decryption.

SriSamp [MVP] (Expert):
(3) Certificates - which are essentially wrappers for public keys.

Vinod_MVP (Expert):
Q: what is the extension for these keys, ???
A: It is an SQL Server object. A certificate is like any other object in SQL Server like table, view, SP etc.

SriSamp [MVP] (Expert):
Each of the above keys and certificates can be used to encrypt other keys and data.

SriSamp [MVP] (Expert):
Asymmetric keys can encrypt symmetric keys and data.

SriSamp [MVP] (Expert):
Symmetric keys can encrypt other symmetric keys and data.

SriSamp [MVP] (Expert):
Certificates can encrypt symmetric keys and data.

SriSamp [MVP] (Expert):
If you want to handle key management yourself, you can also provide a password.

SriSamp [MVP] (Expert):
Let us first understand certificates and see how to create and use them to encrypt data.

SriSamp [MVP] (Expert):
A certificate, is a digitally-signed statement that binds the public key to the identity of the person, device, or service that holds the corresponding private key.

SriSamp [MVP] (Expert):
Certificates are usually issued and signed by a certification authority (CA).

SriSamp [MVP] (Expert):
Certificates issued by SQL Server are self-signed.

SriSamp [MVP] (Expert):
The self-signed certificates created by SQL Server follow the X.509 standard and support the X.509 v1 fields.

SriSamp [MVP] (Expert):
Here is how you can create a certificate.

Vinod_MVP (Expert):
Q: IS SQL authentication is safer as NT in SQL 2005?
A: It very much closely matches the implementation. but the protocol and the mechanism is slightly different. The requirement to enforce password policy etc you need to have SQL Server installed on Win Server 2003 SP1 or Windows XP SP2

SriSamp [MVP] (Expert):
CREATE CERTIFICATE TestCertificate

                WITH SUBJECT = 'Test Certificate. Can be deleted',

                START_DATE = '01/01/2005',

                EXPIRY_DATE = '01/01/2007'

SriSamp [MVP] (Expert):
Since we have not specified any password for encrypting the private key, the database master key is used to encrypt the private key.

SriSamp [MVP] (Expert):
Once the certificate has been created, you can see its details by querying the catalog view "sys.certificates".

SriSamp [MVP] (Expert):
Note that certificates and its associated information is stored in the database itself in an excrypted form. No external location is used for storage.

SriSamp [MVP] (Expert):
Once the certificate has been created, you can use it to encrypt and decrypt data. Here is a script that demonstrates the same.

SriSamp [MVP] (Expert):
CREATE TABLE testTableForPeople (LastName NVARCHAR(50), Identification VARBINARY(MAX))

GO

INSERT INTO testTableForPeople VALUES ('Srinivas',

                EncryptByCert (CERT_ID ('TestCertificate'), '1234-345-434'))

GO

SELECT * FROM testTableForPeople

SriSamp [MVP] (Expert):
The new function that we have used here is "EncryptByCert". This function takes as input an identification for a certificate and the value to encrypt.

SriSamp [MVP] (Expert):
The output returned by the encryption is a VARBINARY column and for certificate based encryption, it can be quite big, which is why we have used the MAX data type.

SriSamp [MVP] (Expert):
You can decrypt the information in the above table using the following script.

SriSamp [MVP] (Expert):
SELECT LastName,

                CONVERT (VARCHAR,

                                DecryptByCert (CERT_ID ('TestCertificate'), Identification)) AS Identification

FROM

                testTableForPeople

GO

SriSamp [MVP] (Expert):
The function to use for decryption is "DecryptByCert" which takes as input the certificate ID and the column name.

SriSamp [MVP] (Expert):
The output of the decryption is binary again and thus has to be converted into the actual clear text (here we use the CONVERT function).

SriSamp [MVP] (Expert):
You can also export the contents of the certificate from the database into a file using the following command.

SriSamp [MVP] (Expert):
BACKUP CERTIFICATE TestCertificate TO FILE = 'C:\Temp\TestCertificate'

SriSamp [MVP] (Expert):
You can also use the ALTER CERTIFICATE command to change the private key used to encrypt a certificate, or add one if none is present.

SriSamp [MVP] (Expert):
Finally, let us cleanup whatever we did using the following script.

SriSamp [MVP] (Expert):
DROP CERTIFICATE TestCertificate

DROP TABLE testTableForPeople

SriSamp [MVP] (Expert):
Another common mode of encryption is using Asymmetric keys.

SriSamp [MVP] (Expert):
An asymmetric key is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other.

SriSamp [MVP] (Expert):
Asymmetric encryption and decryption are relatively resource-intensive, but they provide a higher level of security than symmetric encryption.

SriSamp [MVP] (Expert):
Asymmetric keys in SQL Server use the RSA algorithm with key sizes of 512, 1024 or 2048 bits.

SriSamp [MVP] (Expert):
The private key generated can be encrypted either with the database master key or with a custom password if required.

SriSamp [MVP] (Expert):
The creation and usage of asymmetric keys is very similar to that of certificates. Here is a script that plays around with asymmetric keys.

SriSamp [MVP] (Expert):
CREATE ASYMMETRIC KEY TestAsymKey

                WITH ALGORITHM = RSA_2048

GO

SELECT * FROM sys.asymmetric_keys

GO

DECLARE @clearText VARCHAR(20); SET @clearText = 'Secret Data'

DECLARE @cipherText VARBINARY(MAX)

SET @cipherText = EncryptByAsymKey (ASYMKEY_ID ('TestAsymKey'), @clearText)

SET @clearText = DecryptByAsymKey (ASYMKEY_ID ('TestAsymKey'), @cipherText)

SELECT @cipherText, @clearText

GO

SriSamp [MVP] (Expert):
You can see that you have two functions called "EncryptByAsymKey" and "DecryptByAsymKey" for the encryption and decryption of data.

SriSamp [MVP] (Expert):
The rest of the script should be quite simple to understand.

SriSamp [MVP] (Expert):
You can use the ALTER ASYMMETRIC KEY to alter some properties of the key and DROP ASYMMETRIC KEY to drop the key completely.

SriSamp [MVP] (Expert):
One caveat that you have to understand is that encryption/decryption with an asymmetric key is very costly compared to encryption/decryption with a symmetric key.

SriSamp [MVP] (Expert):
Ideally, this method is not recommended when working with large datasets such as user data in tables.

SriSamp [MVP] (Expert):
Finally, let us see how to create symmetric keys.

SriSamp [MVP] (Expert):
A symmetric key is one key that is used for both encryption and decryption.

SriSamp [MVP] (Expert):
Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database.

SriSamp [MVP] (Expert):
When a symmetric key is created, it needs to be encrypted using either a password, certificate, asymmetric key or symmetric key.

SriSamp [MVP] (Expert):
SQL Server supports different types of algorithm for symmetric keys. These are: DES, TRIPLE_DES, RC2, RC4, DESX, AES_128, AES_192, AES_256.

SriSamp [MVP] (Expert):
Let us see how to create and use symmetric keys.

SriSamp [MVP] (Expert):
CREATE CERTIFICATE TestCertificate

                WITH SUBJECT = 'Test Certificate For Symmetric Keys'

GO

CREATE SYMMETRIC KEY TestSymKey

                WITH ALGORITHM = RC4

                ENCRYPTION BY CERTIFICATE TestCertificate

GO

SriSamp [MVP] (Expert):
Here, we create a symmetric key and use a certificate to encrypt the key. Note that the certificate has to be present before the key uses the same.

SriSamp [MVP] (Expert):
Once created, you can use the "sys.symmetric_keys" catalog view to query information about the key.

SriSamp [MVP] (Expert):
You can now use this key to encrypt and decrypt data as shown below.

SriSamp [MVP] (Expert):
-- Decrypt the key and keep it ready for usage

OPEN SYMMETRIC KEY TestSymKey

                DECRYPTION BY CERTIFICATE TestCertificate

-- Use the key to encrypt and decrypt the data

DECLARE @clearText VARCHAR(20); SET @clearText = 'Secret Data'

DECLARE @cipherText VARBINARY(MAX)

SET @cipherText = EncryptByKey (KEY_GUID ('TestSymKey'), @clearText)

SET @clearText = DecryptByKey (@cipherText)

SELECT @cipherText, @clearText

-- Close the key

CLOSE SYMMETRIC KEY TestSymKey

SriSamp [MVP] (Expert):
You will notice that a few things are different from the earlier scripts.

SriSamp [MVP] (Expert):
(1) You will have to explicitly "open" the symmetric key for use. Since the symmetric key was encrypted, it needs to be decrypted and then used.

SriSamp [MVP] (Expert):
(2) When decrypting data, note that we do not have to specify the key to use, since it is already present and SQL Server knows what key to use.

SriSamp [MVP] (Expert):
(3) When done using the key, you need to close the key so that it is no longer available for use.

Vinod_MVP (Expert):
Q: As the Security statics says that more than 50% of the hackers are with in the organization, In this context, If the Certificate is intensionally droped just to damage the database, what is the solution?
A: Take a backup of the system from time to time. You can also create quick availability options using features like SNAPSHOT Databases and recover the same from the snapshot database. Or use technique like Srini showed in example, persist it ourside the system in a secure place and then even if you drop it from the database it can be restored later from the backup device.

SriSamp [MVP] (Expert):
Q: can we delete teh certificate from the environment after encrypting the result
A: Yes. There is no reference to the key in the data. But to decrypt the data back, you will the certificate again.

SriSamp [MVP] (Expert):
Q: are these keys stored somewhere in a hidden location, where SQL Stores it, or is it somewhere in the filesystem ?
A: As mentioned in my transcript, all information about the keys, certificates etc are stored in system tables like sys.certificates, sys.symmetric_keys etc. No external location is used.

SriSamp [MVP] (Expert):
Q: Then How do and Where do I store the Private Key?
A: The private key is automatically stored by SQL Server, but you can export it separately to a file if needed.

Vinod_MVP (Expert):
Q: Whats the basic algorithem it follows for generation of key?
A: The process is not basic but there is multiple levels from which the encryption happens. there is a great article on the Books online of the architecture of the hierarchy used for this. How master keys, service keys etc are created.

SriSamp [MVP] (Expert):
The security mechanism cannot be used to encrypt regular objects like procedures, views etc. It is only for data encryption.

SriSamp [MVP] (Expert):
There are other syntactical variations in each of the commands that we have seen, however, the time does not permit us to go into all of them.

SriSamp [MVP] (Expert):
I encourage you to refer to SQL Server Books Online for more details about the syntax and the other commands.

SriSamp [MVP] (Expert):
Also note that with the new CLR integration with SQL Server, you can do other exotic types of encryptions (if needed) through the .NET FX and expose it to SQL.

Vinod_MVP (Expert):
Q: What about these features in Express Edition as the SQL Express Edition is a file based?
A: The Express is NOT file based. The engine is running on the background and it shares much of the architecture that SQL Server 2005 Developer edition has. But there are features that are not available on them. Thats about it.

SriSamp [MVP] (Expert):
Q: we had encrypted the result and at the same time the server has been crashed. Now can we decryption with the relevent certificate? 
A: If you have a backup, you can restore the backup and then decrypt the data using the certificate.

SriSamp [MVP] (Expert):
Any other questions?

SriSamp [MVP] (Expert):
Hopefully, the above examples have kindled your interest in exploring the encrpytion capabilities of SQL Server further :-)

SriSamp [MVP] (Expert):
We encourage you to try out these samples and understand the concepts well, because, securing a system is very important and you need to plan for it well ahead.

SriSamp [MVP] (Expert):
Q: what does one do, for some reason, the database crashes, (I understand backup must be taken @ regular intervals), what happens then, what recovery strategy must one use and can we recover the keys as well
A: Recovery is from backups only. Each backup will also backup the keys.

Vinod_MVP (Expert):
Q: what does one do, for some reason, the database crashes, (I understand backup must be taken @ regular intervals), what happens then, what recovery strategy must one use and can we recover the keys as well
A: You need to also look at the Snapshot database feature that allows you to recover under such cases.

SriSamp [MVP] (Expert):
Q: does SQL holds any master key which will be sueful any encrypted data?
A: The keys are present at different levels. As explained, there is a Service Master Key (the mother of all keys). Each database also has a Database Master Key. Any other keys that are then created are encrypted through these keys. There is no "default" key that is present by default for you to use. You need to create a key and then start using it.

Vinod_MVP (Expert):
Ok people. I need to ruch now. Having said that you can ping me anytime:

Mail: vinodk@mvps.org

Site: http://www.ExtremeExperts.com

Blog: http://blogs.sqlxml.org/vinodkumar

SriSamp [MVP] (Expert):
Q: How do I leverage these encryption/decryption features from the Client (Webform - Forms Authentication)
A: From the client, if you are fetching the data, you can either call procedures that do the decryption for you and send you the clear text or you send in pass-through SQL directly to do the operations.

SriSamp [MVP] (Expert):
Sure. 20 minutes is too short a time :-)

Vinod_MVP (Expert):
Thanks for asking some great questions. Hope you liked it. Mail us and we will be more than happy to have more detailed topics online over the web.

SriSamp [MVP] (Expert):
Thank you all for your presence and we hope to meet you all in another chat session on SQL Server.

SriSamp [MVP] (Expert):
My contact details:

abhishek[MS] (Moderator):
We are almost at the end of the chat today.

abhishek[MS] (Moderator):
Thanks to all of you for joining us for this chat .

SriSamp [MVP] (Expert):
Mail: srisamp@gmail.com

Site: http://www32.brinkster.com/srisamp

Blog: http://blogs.sqlxml.org/srinivassampath

abhishek[MS] (Moderator):
Thanks to Vinod and Srinivas for delivering such an informative session

abhishek[MS] (Moderator):
This brings us to the closure of today's chat

 
SriSamp [MVP] (Expert):
Thanks all.

abhishek[MS] (Moderator):
In case you have more questions, please email your queries to Vinod and Srinivas.

abhishek[MS] (Moderator):
Alternatively, you can also post them at the SQLBang group at: http://groups.msn.com/sqlbang .

abhishek[MS] (Moderator):
you can mail Vinod at: vinodk@mvps.org

 
abhishek[MS] (Moderator):
check out the sql user groups in India at http://www.microsoft.com/india/communities/usergroups/default.aspx

 
abhishek[MS] (Moderator):
Please pool in your feedback about the chat at commind@microsoft.com

 
SriSamp [MVP] (Expert):
Will answer back over the mail ASAP. Been very busy this week.

abhishek[MS] (Moderator):
Also feel free to email at commind@microsoft.com  if you would like to host a webchat ! :)

abhishek[MS] (Moderator):
Thanks all of you for joining us and thanks to Srinivas and Vinod for their time and expertise