Database Reference
In-Depth Information
Figure 4-2 . Object model used in the examples
Encryption
As mentioned previously, data encryption isn't available. Why? Because SQL Azure doesn't support
X.509 certificates yet. Certificates are necessary for many encryption-related features, such as
Transparent Data Encryption (TDE), column-level encryption, and certain T-SQL commands, such as
FOR ENCRYPTION and SIGNBYCERT .
However, SQL Azure requires the use of SSL encryption for its communication. This means your
sensitive data is always transmitted safely between your clients and your SQL Azure database. There is
nothing you need to do to enable SSL encryption; it's required and automatically enforced by SQL Azure.
If an application tries to connect to SQL Azure and the application doesn't support SSL, the connection
request fails.
But SSL doesn't encrypt data at rest; it only encrypts data in transit. How can you protect your data
when it's stored in SQL Azure? Because SQL Azure doesn't support encryption natively, you must
encrypt and decrypt your data in the application code.
The Security.sql script contains the following T-SQL statement:
1. CREATE TABLE UserProperties
2. (
3. ID int identity(1,1) PRIMARY KEY, -- identity of the record
4. PropertyName nvarchar(255) NOT NULL, -- name of the property
5. Value varbinary(max) NOT NULL, -- encrypted value
6. Vector binary(16) NOT NULL, -- vector of encrypted value
7. LastUpdated datetime NOT NULL, -- date of last modification
8. Token binary(32) NOT NULL -- record hash
9. )
Each record contains a property name (line 4) that can be used as a search key and an encrypted
value (line 5). The value itself is a binary data type, which lends itself well to encryption. A vector is used
for additional security; this column is explained shortly. The Token and LastUpdated columns are
addressed later when discussing hashing.
The following C# code shows how to encrypt a string value using the Advanced Encryption Standard
(AES) algorithm; you can easily add support for Triple Data Encryption Standard (3DES) or other
algorithms. It uses a shared secret to create the ciphertext and returns a byte array. The byte array is
stored later in the Value column in the database:
1. /// <summary>
2. /// A result structure that stores the encrypted value
3. /// and its associated vector
4. /// </summary>
5. public struct CipherText
6. {
7. public byte[] cipher;
8. public byte[] vector;
Search WWH ::




Custom Search