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.

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-

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.

 ---

Don Pinto, PM, SQL Server Engine

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:

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

In SQL Server:

1. Create a login for the Windows groups. (If using SQL Server authentication, skip the Active Directory steps, and create SQL Server authentication logins here.)
2. Create a database user for the login representing the Windows groups.
3. Create one or more user-defined database roles, each representing a similar function. For example financial analyst, and sales analyst.
4. Add database users to one or more user-defined database roles.
5. 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;

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

    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;

go

-- 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.

   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));

go

CREATE CERTIFICATE cert_demo WITH SUBJECT = 'Encryption demo';

go

CREATE SYMMETRIC KEY key_employee WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE cert_demo;

go

OPEN SYMMETRIC KEY key_employee DECRYPTION BY CERTIFICATE cert_demo;

go

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)));

go

--1   Alice       5000

--2   Bob         1000

--3   Mallory     1000

SELECT employee_id, name, CONVERT(decimal,

DECRYPTBYKEY(salary_crypt)) AS salary FROM employees;

go

CLOSE SYMMETRIC KEY key_employee;

Go

CLOSE SYMMETRIC KEY key_employee;

go

CREATE USER [mallory] WITHOUT LOGIN;

go

GRANT UPDATE ON employees TO [mallory];

go

GRANT SELECT ON employees TO [mallory];

go

  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';

go

--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;

go

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

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

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

SELECT * FROM employees;

go

-- 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;

go

--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;

go

REVERT;

go

  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;

Go

  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;

go

CREATE TABLE employees( employee_id int identity primary key,

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

go

OPEN SYMMETRIC KEY key_employee DECRYPTION BY CERTIFICATE cert_demo;

go

--   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);

go

-- 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;

go

--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;

go

CLOSE SYMMETRIC KEY key_employee;

go

GRANT UPDATE ON employees TO [mallory];

go

GRANT SELECT ON employees TO [mallory];

go

  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;

Go

-- ... 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;

go

  I hope this information helps.

  -Raul

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

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

GO

--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

     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

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.