Consider the following problem, how would you give someone access to parts of a table without giving them permissions on the table directly? Column level permissions or views can be used to project only the required columns; however, when a permission check is done, it is intuitive to check access to the view and the underlying base table.

OWNERSHIP CHAINING

In SQL Server, when an object is accessed through a chain, the owner of the object referenced and the calling object is checked. If both objects have the same owner, permissions on the referenced object is not evaluated. This is called ownership chaining. This idea is extended beyond just database objects to work across databases as well (called Cross-database ownership chaining).

Author: Don Pinto (SQL Server Security PM, Microsoft Corporation)

You may have heard about Transparent Data Encryption (TDE), which was introduced in SQL Server 2008. But what does it do, what are its advantages and disadvantages and how can you leverage this technology in SQL Server? Keep reading this tips and tricks article to learn how.

Transparent Data Encryption (also called as TDE) is a technology in SQL Server that offers encryption of data-at-rest. This feature automatically encrypts the entire database (data and log files), as well as database backups, without requiring any programming or code changes to your application. The process is entirely transparent, hence the name Transparent Data Encryption.

When TDE is first enabled for a specific database, SQL Server encrypts the database in the background. During this process, the database remains online and responsive to client requests (similarly, when encryption is disabled, SQL Server decrypts the database in the background). Encryption is performed at the page level, and does not increase the size of the database in any way.

Author: Don Pinto (SQL Server Security PM, Microsoft Corporation)

A common scenario in data warehousing applications is knowing what source system records to update, what data needs to be loaded and which data rows can be skipped as nothing has changed since they were last loaded. Another possible scenario is the need to facilitate searching data that is encrypted using cell level encryption or storing application passwords inside the database.

Data Hashing can be used to solve this problem in SQL Server.

A hash is a number that is generated by reading the contents of a document or message. Different messages should generate different hash values, but the same message causes the algorithm to generate the same hash value.

Author: Don Pinto (SQL Server Security PM, Microsoft Corporation)

Imagine for a moment that you are a SQL Server production DBA. You arrive at the office and there are two new co-worker requests in your e-mail inbox: Bob works in merchandise and needs read-only access to all the sales data so that he can run reports to forecast purchase volumes, Alice is a new junior DBA who only requires access to the meta-data. These seem like easy requests to fulfill – or are they?

The SQL Server Permission model can be used to solve this.

Securables are entities that SQL Server controls access to through permissions. Permissions enable a principal to perform actions on a securable. Across all securable scopes, the primary commands to control access to a securable are GRANT, DENY and REVOKE.

Author: Don Pinto (SQL Server Security PM, Microsoft Corporation)

Now that you know how to setup the encryption key hierarchy based on the last security tips and tricks post, let us use encryption to encrypt sensitive data in SQL Server.

It is quite possible that you might have sensitive data that needs encryption at a finer level of detail than the entire database. Most of the row might need to be visible to users, while certain sensitive information such as employee salary might require encryption.

Author: Don Pinto (SQL Server Security PM, Microsoft Corporation)

Ever wonder how encryption keys are protected and managed in SQL Server? 

Microsoft SQL Server makes use of a key hierarchy, which helps to protect keys that are to be used for encryption. This hierarchy can best be viewed as a series of layers, in which each layer encrypts the layer below it.  This hierarchy provides a highly secure infrastructure for sensitive data.

Author: Don Pinto (SQL Server Security PM, Microsoft Corporation)

Security is an important aspect of a database system and has gradually worked its way up the priority list for today's organizations. The importance of protecting sensitive data has ushered in a plethora of compliance regulations that mandate the use of cryptography. Compliance with these regulations is necessary to promote effective business operation and organizations must protect sensitive data or suffer potential legislative, regulatory, legal and brand consequences. Towards this end, we ensure that the ciphers provided in SQL Server are strong to mitigate compromising user-data and have advised customers to avoid using the RC4 cipher because its implementation in SQL Server does not salt the keys.