- Filtering (obfuscating) Sensitive Text in SQL Server
-
A very common concern when dealing with sensitive data such as passwords is how to make sure that such data is not exposed through traces. SQL Server can detect and filter the SQL statements in traces that include the usage of DDL and built-ins (such as OPEN SYMMETRIC KEY, and EncryptByKey) that are known to include potentially sensitive data. For example:
-- T-SQL Script:
-- Create an open a symmetric key with password
--
CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'D3m0 p4SSw0Rd&'
go
OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = 'D3m0 p4SSw0Rd&'
go
The resulting traces should look similar to the following (including all batch starting/complete and audit events, but for simplicity including only some of the relevant columns):
|
Event class |
TextData |
Event subclass |
Object name |
Object Type |
|
SQL: Batch Starting |
--*CREATE SYMMETRIC KEY---------------… |
|
|
|
|
Audit: DB Object Mgr |
CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = '******' |
1 - Create |
key00 |
19283 - SK |
|
SQL: Batch Completed |
--*CREATE SYMMETRIC KEY------------------ |
|
|
|
|
SQL: Batch Starting |
--*OPEN SYMMETRIC KEY------------------- |
|
|
|
|
Audit: DB Object Mgr |
OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = '******' |
10 - Open |
key00 |
19283 - SK |
|
SQL: Batch Completed |
--*OPEN SYMMETRIC KEY----------------… |
|
|
|
Unfortunately when calling such statements via dynamic SQL or when using user defined stored procedures (following the best practices in order to minimize SQL injection potential), SQL Server engine has no way to identify that the underlying statements will involve any of the DDL or built-ins marked to be filtered. For example:
EXEC( 'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''')
EXEC( 'OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''')
|
Event class |
TextData |
Event subclass |
Object name |
Object Type |
|
SQL: Batch Starting |
EXEC( 'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''')
EXEC( 'OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''') |
|
|
|
|
Audit: DB Object Mgr |
CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = '******' |
1 - Create |
key00 |
19283 - SK |
|
Audit: DB Object Mgr |
OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = '******' |
10 - Open |
key00 |
19283 - SK |
|
SQL: Batch Completed |
EXEC( 'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''')
EXEC( 'OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&''') |
|
|
|
In such cases, the audit events based on the DDL execution will be correctly filtered, but the SQL events (batch starting/completed) cannot be filtered as the system has no information specific to the string containing the T-SQL script to be executed.
While there is no mechanism available to register user defined for filtering, there is a potential workaround that may help. The idea is to force the filtering mechanism by having a builtin that we know will be filtered, but avoid executing it if possible. For example:
-- T-SQL Script:
-- Create an open a symmetric key with password
--
DECLARE @Secret nvarchar(max)
-- The conditional expression will always be true,
-- therefore it will always select the statement
-- but the inclusion of EncryptByPassphrase will
-- enable the trace filtering mechanism
--
SELECT @Secret = CASE WHEN 1=1 THEN
'CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&'''
ELSE EncryptByPassphrase('','') END
EXEC(@Secret)
SELECT @Secret = CASE WHEN 1=1 THEN
'OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = ''D3m0 p4SSw0Rd&'''
ELSE EncryptByPassphrase('','') END
EXEC(@Secret)
go
As you can see in the following traces, the assignment sections of the batch will be replaced, filtering the sensitive data, but the auditing information is still available.
|
Event class |
TextData |
Event subclass |
Object name |
Object Type |
|
SQL: Batch Starting |
DECLARE @Secret nvarchar(max)
--*ASSIGN---------------------------
EXEC(@Secret)
--*ASSIGN---------------------------
EXEC(@Secret) |
|
|
|
|
Audit: DB Object Mgr |
CREATE SYMMETRIC KEY key00 WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = '******' |
1 - Create |
key00 |
19283 - SK |
|
Audit: DB Object Mgr |
OPEN SYMMETRIC KEY key00 DECRYPTION BY PASSWORD = '******' |
10 - Open |
key00 |
19283 - SK |
|
SQL: Batch Completed |
DECLARE @Secret nvarchar(max)
--*ASSIGN---------------------------
EXEC(@Secret)
--*ASSIGN---------------------------
EXEC(@Secret) |
|
|
|
It is important to remark that audit events (as well as audit events based on SQL Server 2008 architecture) are working the same way.
-Raul Garcia
SDE/T
SQL Server Engine
- Link to Lyudmila’s blog
-
My teammate Lyudmila is maintaining her own TechNet blog where she writes articles related to SQL Server security. You can access her blog at http://blogs.technet.com/lyudmila_fokina. Her blog is written in Russian, but the samples she includes should be easy to follow, and you can use an online translation tool for the rest of the text.
Her latest article is a very interesting suggestion on how to use SQL Server Agent jobs to provide a scheduled temporary elevation of privileges. You can find the whole article at http://blogs.technet.com/lyudmila_fokina/archive/2009/06/11/sql-server-agent-jobs.aspx.
-Raul Garcia
SDE/T
SQL Server Engine
- Arx the latest vendor to support EKM
-
With the increasing popularity of the EKM feature in SQL Server 2008, more vendors are adding their support for this great feature. I'm very happy to announce that Arx has just announced their releaese of their EKM provider dll:
http://www.arx.com/about/PR/PR-PrivateServer-HSM-Secures-Sensitive-Information-for-Microsoft-SQL-Server-2008.php
I'll keep you up-to-date with future vendor support.
Il-Sung.
- How To Choose Audit Action Group When Using Auditing in SQL Server 2008
-
SQL Sever 2008 introduces auditing feature which can audit both server-level events and database-level events and several specific database actions. Please check http://msdn.microsoft.com/en-us/library/cc280386.aspx for more details.
One difficulty the user may have is which action group should be used when trying to audit the interested events. For example if the user wants to audit all of the “create login” actions, first it should be a server audit specification to be created because “create login” is a server-level event, and now we need to decide which action group should be added to this server audit specification. The DMV sys.dm_audit_actions can help the user to find such information, for the above example we can do the following query:
select * from sys.dm_audit_actions where name='create' and class_desc='login'
And the returned result is:
|
action_id |
name |
class_desc |
covering_action_name |
parent_class_desc |
|
CR |
CREATE |
LOGIN |
NULL |
SERVER |
|
covering_parent_action_name |
configuration_level |
|
SERVER_PRINCIPAL_CHANGE_GROUP |
NULL |
|
containing_group_name |
action_in_log |
|
SERVER_PRINCIPAL_CHANGE_GROUP |
1 |
The value of the containing_group_name is the action group you should add to the server audit specification for auditing “create login”.
Another example is that if want to audit “alter shema” events, then do the following query:
select * from sys.dm_audit_actions where name='alter' and class_desc='schema'
will tell you the event to audit is DATABASE_OBJECT_CHANGE_GROUP.
One thing to be noted is that for all schema-scoped objects, such as table, function, procedure etc., they are all deemed as object and the audit action group for create/alter/drop these object is SCHEMA_OBJECT_CHANGE_GROUP, the query to the DMV is:
select * from sys.dm_audit_actions where name='alter' and class_desc='object'
- Thales/nCipher announces EKM support for SQL Server 2008
-
I'm very please to announce that last week during the RSA Conference, Thales announced their support for SQL Server 2008 with their nCipher product line of hardward security modules (HSMs) (http://www.ncipher.com/en/Company/News/Press%20Releases/2009/SQL%20Server%202008%20integration.aspx). This will be of interest to those of you who are interested in leveraging the Extensible Key Management (EKM) feature of SQL Server 2008 which provides support for integrating with HSMs and key managers to centrally manage and secure keys outside of the database.
Il-Sung.
- PCI DSS Compliance with SQL Server 2008
-
Since PCI Compliance seems to be popular subject for SQL Server users (by which I mean that a quite a few of you are forced to deal with it) here's something that may help. Parente Randolph is a PCI QSA (Qualified Security Assessor) and they recently released a whitepaper entitled Deploying SQL Server 2008 Based on Payment Card Industry Data Security Standards (PCI DSS) Version 1.2. It's definitely worth taking a look.
Il-Sung Lee
Program Manager
SQL Server Engine Security
- SQL Server EncryptByKey cryptographic message description
-
Since the introduction of SQL Server 2008 extensible key management (EKM), new opportunities may arise to handle data encryption on the client while still making the plaintext data accessible to authorized users in SQL Server. One issue between SQL Server and third party clients has been already discussed in the SQL Server Security forum in the past: describing the cryptographic message elements used by the resulting ciphertext generated by EncryptByKey builtin.
While we are in the process of finalizing our documentation regarding this subject matter, I would like to take this opportunity to bring you an early draft view of the information and to get your feedback.. Below I will explain each one of the parts of the message along with an example. Notice that in all cases we are using little-endian byte ordering.
CipherTextMessage := KeyGUID + EncryptionHeader + EncryptedMessage
KeyGUID := {16 bytes} Key_guid. This GUID serves as an identifier for the key and it is stored in metadata (SELECT key_guid FROM sys.symmetric_keys). It is used during decryption for finding the corresponding key in the keyring.
EncryptionHeader := Headerversion + ReservedBytes
Headerversion := {1 byte} SQL Server encryption message version. Current version = 1
ReservedBytes := {3 byte} Reserved. Must be 0.
EncryptedMessage := InitializationVector + _EncryptFunction(SymKey, InitializationVector, InnerMessage)
InitializationVector := {1 block} the length of this field depends on the algorithm being used. All AES family keys will be 16 bytes per block, while the DES family keys are 8 bytes per block. Initialization vectors are used to initialize the block algorithm. It is not intended to be a secret, but must be unique for every call to the encryption function in order to avoid revealing patterns. For simplicity we will refer to the Initialization Vector as IV.
_EncryptFunction(IV, key, plaintext) := {variable length} function used to encrypt the plaintext data using the key (the algorithm is specified by the key itself) and the IV. In the case of SQL Server, this function is the CryptEncrypt, but since the supported encryption algorithms are standard, this function should be considered generic.
Interoperability note: We use the block cipher in Cipher Block Chaining (CBC) mode. (default mode for most algorithms for Microsoft cryptographic providers, see AES provider Algorithms in BOL for further details).
InnerMessage := InnerMessageHeader + IntegrityBytes + Plaintext
InnerMessageHeader := MagicNumber + IntegrityBytesLength + PlaintextLength
MagicNumber := {4 bytes} As the name suggests, it is only an arbitrary value used to identify the message format. The current expected value is 3131961357 (decimal, unsigned). The main goal of this value is to detect if the message format is the expected one (if the value is incorrect, the message will be discarded). Extra points for anyone who identified the hexadecimal representation for this value which is (DWORD) 0xBAADF00D. For anyone curious about magic numbers, here is an interesting article about hexspeak.
IntegrityBytesLength := {2 bytes} The length of the IntegrityBytes field. May be either 0 or 20 (decimal).
IntegrityBytes := {IntegrityBytesLength bytes} This field is used when the @authenticator parameter is used when calling EncryptByKey. When this parameter is used, the Integrity field will be the cryptographic hash (SHA1) of the @ClearText (Plaintext) concatenated with the @authenticator parameter.
PlaintextLength := {2 bytes} The length of the Plaintext field.
Plaintext := {PlaintextLength bytes} The content of the @ClearText parameter (binary representation) when calling EncryptByKey.
Let’s use concrete examples and analyze each part of the message. For example, a call to SELECT ENCRYPTBYKEY( key_guid('key1'), 'Hello World!') resulted in the following ciphertext:
0x0096F42B8789694F87002E54D30FA0210100000013BDD2DD73F4392654565D3D156A073D4E8B16E0E11D0984F8E564E986268BF7D5C21158F1A511347F0177C5B1B18D24
Detailed analysis of each field:
|
Field |
Value (hex) |
Notes |
|
KeyGUID |
0x0096F42B8789694F87002E54D30FA021 |
2BF49600-8987-4F69-8700-2E54D30FA021
This value should match the GUID for our key. |
|
EncryptionHeader |
0x01000000 |
Version 1
Reserved bytes = 0 |
|
IV |
13BDD2DD73F4392654565D3D156A073D |
Randomly generated |
Since the rest of the message is encrypted, it would be of little value to try to analyze it in the current form, but the following analysis will describe the inner message before being encrypted:
0x0DF0ADBA00000C0048656C6C6F20576F726C6421
|
Field |
Value (hex) |
Notes |
|
MagicNumber |
0x0DF0ADBA |
3131961357 |
|
IntegrityBytesLength |
0x0000 |
0 bytes since we didn’t use the @authenticator parameter |
|
PlainTextLength |
0x0C00 |
@ClearText length = 12 bytes |
|
IntegrityBytes |
Empty |
Field not used |
|
Plaintext |
0x48656C6C6F20576F726C6421 |
ASCII representation for “Hello World!” |
If we would have called the EncryptByKey builtin using the @authenticator parameter, the inner message would have looked slightly different; for example when calling SELECT ENCRYPTBYKEY( key_guid('key1'), 'Hello World!' ,1, 'SQL Server 2008 demo'):
0x0DF0ADBA14000C00429DF3A3B39257F69C3359CA7F94B3F26AF3BE2B48656C6C6F20576F726C6421
|
Field |
Value (hex) |
Notes |
|
MagicNumber |
0x0DF0ADBA |
3131961357 |
|
IntegrityBytesLength |
0x1400 |
20 bytes since we used the @authenticator |
|
PlainTextLength |
0x0C00 |
@ClearText length = 12 bytes |
|
IntegrityBytes |
0x429DF3A3B39257F69C3359CA7F94B3F26AF3BE2B |
SHA-1 hash of “Hello World!SQL Server 2008 demo” |
|
Plaintext |
0x48656C6C6F20576F726C6421 |
ASCII representation for “Hello World!” |
-Raul Garcia
SDE/T
SQL Server Engine
- Enforce Windows Password Policy on SQL Server Logins
-
If users choose to use SQL login to connect to SQL Server rather than using NT authenticating, it is worth to remind that SQL server does provide the option of enforcing window password policy on SQL logins.
When creating a SQL login you can specify CHECK_POLICY=on, which will enforced on this login of the Windows password policies of the computer on which SQL Server is running.
On WinXP and Win2k system, the Windows password policy only checks if the password is complex enough (a complex password should include at least three combinations of lower-case alphabet, upper-case alphabet, number, or special characters etc).
On Win2003 and above system, the windows password policy check against password minimum length, password history (password can’t be used if it is the same as previous N password), the password minimum life (password can’t be changed within a minimal time since creation) and maximum life (password is forced to expire after the maximum life), and login can be locked out if inputting wrong password continuously for certain times. The option of CHECK_EXPIRATION will enforce password expiration policy, and you can only specify CHECK_EXPIRATION=on when the CHECK_POLICY=on.
By default, both CHECK_POLICYand CHECK_ EXPIRATION are off. If you do not turn on the password policy at login creation time, you can always use ALTER LOGIN to change the value of these options. Also you can query sys.sql_logins to look at the current property value of the login.
For more information check
http://msdn.microsoft.com/en-us/library/ms189751.aspx
- Interested in Compliance?
-
I'm pretty sure that there are many of you who have to deal with regulatory compliance but how many of you are aware that we have a SQL Server Compliance web portal? Check out http://www.microsoft.com/sqlserver/2008/en/us/compliance.aspx. There's a lot of information listed there covering a variety of topics that are interesting to compliance situations.
While we're talking about compliance, if you're dealing with PCI DSS compliance, you may want to check out this webcast which is scheduled for the end of the month: SQL Server 2008 Capabilities for Meeting PCI Compliance Needs
Il-Sung Lee
Program Manager
SQL Server Engine Security
- Feedback requested: Default schemas for Windows groups
-
We would like your feedback on the scenarios where you need to assign default schemas to Windows groups. We have a post in the forums, but there has only been one reply so far. Please, if you have an opinion or even just want to express your support of us doing anything about this, head over and leave a comment.
- Performance of Impact of Auditing in SQL Server 2008
-
Il-Sung Lee and Art Rask’s whitepaper, Auditing in SQL Server 2008, just hit the web. Congratulations!
I just wanted to add to what Il-Sung already has said about this paper that this is a great resource that will answer some of the big questions we get repeatedly asked. Such as performance impact – the whitepaper includes some hard numbers about performance impact of some workloads typical of customer environments. If you have any interest in SQL Server Audit, this is the paper for you! Hope you enjoy.
- Auditing in SQL Server 2008 white paper
-
In continuation to the post by Jack back in October, we've added Auditing in SQL Server 2008 to our list of security focused white papers (http://msdn.microsoft.com/en-us/library/dd392015.aspx).
We'll let you know as more white papers are published. But in the meantime, if there's a topic that you really feel would be a good topic for a white paper, let us know.
Il-Sung Lee
Program Manager
SQL Server Engine Security
- Data Protection Day, January 28th
-
Thought some readers of this blog might be interested in Data Protection Day, tomorrow, January 28. The Council of Europe established this day to raise awareness of data privacy and data protection issues and how we, as technology professionals, can do something about it. You can read more about their aims here.
Microsoft is joining in by holding a Data Privacy Day event in San Francisco at the San Francisco Public Library and participating in an event in Brussels, Belgium. Lot’s of information from microsoft.com about privacy for IT, government, teachers, students, and parents here.
If you are reading this blog, this is likely right up your ally; take a moment and give a little more thought about the data entrusted to you and what you can do to protect it.
- About DEK rotation and log backup in Transparent Database Encryption (TDE)
-
Regarding the DEK rotation in TDE, after a DEK has been rotated twice, a log backup must be performed before the DEK can be modified again, otherwise in the third time of rotation the following error message will be popped up:
“This command requires a database encryption scan on database 'user_db'. However, the database has changes from previous encryption scans that are pending log backup. Take a log backup and retry the command.”
However, if the log backup chain hasn’t been established then you will not see this error. The first full backup establishes the log backup chain.
On the other hand if you don’t want to take log backups and still be able to rotate the DEK as many times as you want, the solution is to alter database using SIMPLE recovery model, since log backups are disallowed in SIMPLE recovery model.
- First HSM for SQL Server 2008 released!
-
Today, January 15th 2009, Safenet announced its release of Luna SA HSM support for SQL Server 2008 and became the first EKM provider available in the market!
SQL Server 2008 introduced Extensible Key Management (EKM) for managing keys outside of SQL Server. Traditionally, all Symmetric and Asymmetric Keys used by SQL Server reside in the database itself, however EKM allows key creation, storage, encryption and decryption to be done outside the database using an HSM. To use this feature HSM vendors (also called EKM providers) have to write a module which implements certain interfaces that SQL Server uses to for key management and crypto operations. Key creation and management DDL supports key creation using a EKM providers. With Safenet’s announcement today, they have become the first HSM to support SQL Server 2008’s Extensible Key Management.
Congratulations to Safenet!
