Assymmetric encrpytion – Enabling TDE

What is TDE
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.

TDE and TempDB
The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server.

Enabling TDE
The TDE model uses a new specialized symmetric key called the database encryption key (DEK) to encrypt your database. The DEK is protected by a server certificate—a certificate created in the master database. The server certificate is protected by the master database DMK, which must be protected in turn by the SMK.

The first step to enabling TDE is to create a DMK in the master database (if one doesn’t already exist).
USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N’StrongPassword’;
GO

After you’ve created the master database DMK you need to create a server certificate in the master database. This is the certificate that will protect the DEK that you’ll create in subsequent steps.

USE master;
GO
CREATE CERTIFICATE TDE_Certificate
WITH SUBJECT = N’TDE Encryption Server Certificate’;
GO

After you create your server certificate you should immediately back it up using the BACKUP CERTIFICATE statement. Immediately store the certificate backup in a secure facility. You’ll need the server certificate if you want to restore the encrypted database from a backup or attach the database files to a different server in the future.

USE master;
GO
BACKUP CERTIFICATE TDE_Certificate
TO FILE = N’c:\Server_Certificate.cer’
WITH PRIVATE KEY
(
FILE = N’c:\Server_Certificate.pvk’,
ENCRYPTION BY PASSWORD = N’AnyStrongPassword’
);
GO
Now that you have a server certificate you can switch back to your user database and create a DEK with the CREATE DATABASE ENCRYPTION KEY statement. You can only create one DEK per user database.

USE AdventureWorksLT2008;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

Finally enable encryption

ALTER DATABASE CustRecords
SET ENCRYPTION ON;
GO

 

Ownership Chains

Each object within a database has an owner associated to it – the schema owner. You can also build objects that reference other objects within a database, such as a stored proc that calls functions which issue SELECT statements against views that are based on tables. the owner of each object that is referenced in a calling stack forms an ownership chain as the code transits from one object to the next within the calling Stack. So long as the owner of the object ad any other objects that it references have the same owner, you have an intact ownership chain. SQL Server checks your permissions on an object at the top of the calling stack, as well as each time the object owner changes within a calling stack.

 

Source : MCTS self paced training kit. page275

 

Logon Trigger

• Logon triggers fire stored procedures in response to a LOGON event.
• This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.
•  Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails.
• You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login.
• Note that the LOGON event corresponds to the AUDIT_LOGIN SQL Trace event, which can be used in event notifications.
• The primary difference between triggers and event notifications is that triggers are raised synchronously with events, whereas event notifications are asynchronous. This means, for example, that if you want to stop a session from being established, you must use a logon trigger. An event notification on an AUDIT_LOGIN event cannot be used for this purpose.

 

Password policy enforcement

If check_policy is on,

• check_expiration is also on, unless set to off explicitly
• Password history is initialized with the value of current password hash

If check_policy is off,

• check_expiration is set to off
• Password history is cleared
• Value of lock_out time is reset

 

Server Roles and Fixed server roles

• Roles are like groups in Windows operating system
• Server-level roles, as the name suggests, are server wide in their permission scope
• Fixed Server Roles are Server Level Roles but their permissions cannot be changed
• Each member of a fixed server role, can add other logins to that same role
• The Fixed server level roles are bulkadmin, dbcreator, diskadmin, processadmin, sysadmin, serveradmin, setupadmin, securityadmin
• securityadmin should be treated like sysadmin because it can grant access to the Database Engine and can configure user permissions
• Public is a server level role, but not fixed because the permissions can be changed
• Every sql server login belongs to the public role. A user that has not yet been granted or denied permissions on an object, inherits the permissions that are granted to the public role on that object. If public permissions are granted to an object, the object is available to all users.

What can the roles do

1. Bulkadmin – Members of the bulkadmin fixed server role can run the BULK INSERT statement.
2. DBCreator – Members of the dbcreator role can create databases, and can alter and restore their own databses
3. Diskadmin – manages disk files
4. Processadmin – Members of the processadmin role can terminate processes that are running in a sql server instance
5. Securityadmin – Members of the securityadmin role mange logins and their properties. They can GRANT, REVOKE AND DENY server level, and database level permissions. They can reset passwords for sql server logins.
6. Server admin – Members of the serveradmin can change server – wide configuration options and can shut down server.
7. Setupadmin – Memebers of the setupadmin can add and remove linked servers and also execute some stored procedures
8. Sysadmin – Members of the sysadmin fixed server role can perform any activity in the server. By default, all members of the Windows BUILTIN\Administrators group, the local administrator’s group, are members of the sysadmin fixed server role.

Sources : http://msdn.microsoft.com/en-us/library/ms188659.aspx

http://msdn.microsoft.com/en-us/library/ms175892.aspx

http://msdn.microsoft.com/en-us/library/ms188659%28v=sql.90%29.aspx

 

Database Principals

Principals are entities that can request resources. Principal can either be an individual or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection.

sys.database_principals gives a list of all the principals

Windows-level principals

• Windows Domain Login
• Windows Local Login

SQL Server-level principal

• SQL Server Login

Database-level principals

• Database User
• Database Role
• Application Role

IMG Source: http://www.codeproject.com/KB/database/sql-server-logins.aspx

Source:http://msdn.microsoft.com/en-us/library/ms181127.aspx