Confidentiality is the ability to ensure that data can be accessed only by authorized users. It's about protecting your data from prying eyes or from inadvertent leakage by using multiple technologies, including the following:

SQL Azure offers new features, such as a firewall (as previously discussed); however, it doesn't yet support data encryption natively (such as Transparent Data Encryption [TDE] and field-level encryption), which places more emphasis on the other confidentiality techniques.

SQL Server, on the other hand, doesn't provide a native firewall (although it's possible to purchase after-market database firewalls), but it offers strong encryption capabilities. Finally, both SQL Server and SQL Azure offer hashing capabilities.

Because SQL Azure doesn't provide native encryption, your code needs to do all the hard work. Not to worry! In this chapter, you see how to implement hashing and encryption using C# and how to store the ciphertext in SQL Azure (or SQL Server, for that matter).

Data integrity refers to the objective of ensuring that information is modified only by authorized users. Integrity of data can be compromised in multiple ways, such as a malicious SQL Injection attack or the unintentional execution of a TRUNCATE statement on a table, wiping out all the records. You can implement integrity measures in a database as follows:

From an integrity standpoint, SQL Azure doesn't yet provide the same capabilities as SQL Server. SQL Azure does deliver strong authorization capabilities, similar to SQL Server 2008. However, regular database backups and activity auditing aren't available as of this writing. Microsoft is building new backup mechanisms for SQL Azure, above and beyond the BCP (Bulk Copy Program) operations available now. See Chapter 5 for more information about how to back up your data in SQL Azure.

Availability ensures service uptime so your data can be accessed when it's needed. Designing highly available systems can be very complex and requires advanced knowledge in multiple areas including disk configuration, system administration, disaster-recovery locations, and more. The following are some of the technologies involved in high availability:

In addition to ensuring redundancy of infrastructure components, you need to understand the recovery objectives of your business to determine how to best implement your availability requirements.

SQL Azure offers a unique platform because all the areas just listed are automatically provided for. SQL Azure offers a 99.9% availability guarantee through its service-level agreement (SLA). In order to deliver this high availability, SQL Azure transparently keeps two additional standby databases for each user database you create. If anything happens to one of your user databases, one of the two backups takes over within a few seconds; you may not even notice the failover process. SQL Azure also provides automatic handling of DoS attacks.

SQL Azure accomplishes failover using the architecture shown in Figure 4-1. You interact with a proxy that directs your request to whichever of your databases is current. The standby databases aren't accessible to you.

Figure 4.1. SQL Azure's standby database architecture

Let's take an example of a project that needs to deploy a new application with high availability requirements. The following items would need to be planned for in a traditional SQL Server installation but are provided to you automatically with SQL Azure:

And of course, you must consider the costs associated with all these activities, the time it takes to plan and execute such a project, and the specialized resources needed to implement a highly available database environment.

By now, you can see that although SQL Azure falls short in certain areas of security, it excels in others, especially its availability model. Deploying a highly available SQL Azure database is quick and extremely simple.

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;

9. }
10.
11. /// <summary>
12. /// The encryption class that encapsulates the complexity behind encrypting
13. /// and decrypting values
14. /// </summary>
15. public class Encryption
16. {
17.    private byte[] _SECRET_KEY_ = new byte[] { 160, 225, 229, 3,
18.       148, 219, 67, 89, 247, 133, 213, 26, 129, 160, 235, 41,
19.       42, 177, 202, 251, 38, 56, 232, 90, 54, 88, 158, 169,
20.       200, 24, 19, 27 };
21.
22./// <summary>
23./// Encrypt using AES
24./// </summary>
25./// <param name="value">The string to encrypt</param>
26.public CipherText EncryptAES(string value)
27.{
28.   // Prepare variables...
29.   byte[] buffer = UTF8Encoding.UTF8.GetBytes(value);
30.   CipherText ct = new CipherText();
31.   System.Security.Cryptography.Aes aes = null;
32.   ICryptoTransform transform = null;
33.
34.   // Create the AES object
35.   aes = System.Security.Cryptography.Aes.Create();
36.   aes.GenerateIV();
37.   aes.Key = _SECRET_KEY_;
38.
39.   // Create the encryption object
40.   transform = aes.CreateEncryptor();
41.
42.   // Encrypt and store the result in the structure
43.   ct.cipher = transform.TransformFinalBlock(buffer, 0, buffer.Length);
44.   // Save the vector used for future use
45.   ct.vector = aes.IV;
46.
47.   return ct;
48.   }
49.}

The CipherText structure (line 5) is used as a return value. Each encrypted byte array comes with its initialization vector, which is a security mechanism that prevents dictionary attacks on your database. The Encryption class contains an EncryptAES method that performs the actual encryption of a string value; this method returns CipherText.

Because AES requires a secret key, you created one in the form of a byte array on line 17. The secret key must be 32 bytes in length. You can easily generate your own by using the GenerateKey method provided by the Aes class provided by .NET.

On line 29, you transform the string value to its byte representation using UTF-8 encoding. UTF-8 encoding is very practical because it automatically chooses between ASCII and Unicode based on the input value.

You declare the AES object on line 31 and instantiate it on line 35 using the static Create() method on the Aes class. This method creates the vector automatically on line 36 and sets the private key discussed earlier.

On line 40, you create a cryptographic object using the CreateEncryptor() method. A call to its TransformFinalBlock() method does the trick and outputs a variable-length byte array that you store in the CipherText structure instance on line 43. You save the previously generated vector as well and return the structure on line 47.

That was simple, right? Now all you have to do is store the CipherText content in the UserProperties table. But before doing this, let's discuss hashing.

Hashing isn't nearly as complicated as you've seen so far. And although you can store the values you've encrypted so far in the database, in this example you hash all the columns of the rows (except the ID value) to make sure they're unchanged. Why? The answer goes back to the integrity concern of the CIA triad discussed earlier. You want a way to tell whether your data has been modified outside of your code. Encrypting your secret value makes it virtually impossible to break the confidentiality aspect of the triad, but someone can still update the PropertyName column—or, worse, the Value column. Hashing doesn't prevent data from being modified, but you have a way to detect whether it was changed without your authorization.

To simplify the code, start by creating a couple of extension methods. Extension methods are a handy way to extend the methods available to a class (or data type) even if you don't have the original source code. Here you can see how to declare an extension method on the string and DateTime data types:

1.  public static class Extensions
2.  {
3.     public static byte[] GetBytes(this string value)
4.     {
5.        byte[] buffer = UTF8Encoding.UTF8.GetBytes(value);
6.        return buffer;
7.     }
8.
9.     public static byte[] GetBytes(this DateTime value)
10.    {
11.       return value.ToString().GetBytes();
12.    }
13. }

This code adds a GetBytes() method to the string and DateTime data types. You also create a utility class that allows you to create a hash value based on a collection of byte arrays. The following code shows that class:

1.  public class Util

2.  {
3.  /// <summary>
4.  /// Computes a hash value based on an array of byte arrays
5.  /// </summary>
6.  /// <param name="bytes">Array of byte arrays</param>
7.  public static byte[] ComputeHash(params byte[][] bytes)
8.  {
9.      SHA256 sha = SHA256Managed.Create();
10.    MemoryStream ms = new MemoryStream();
11.
12.    for (int i = 0; i < bytes.Length; i++)
13.    ms.Write(bytes[i], 0, bytes[i].Length);
14.
15.    ms.Flush();
16.    ms.Position = 0;
17.
18.    return sha.ComputeHash(ms);
19.    }
20. }

This Util class is very handy shortly. Note on line 7 the declaration of the variable as params byte[][]; this means each parameter passed to this method must be a byte array. You declare a memory stream, loop on each byte-array variable, and append it to the memory stream on line 13. Finally, you return the computed hash of the memory stream on line 18. You see how to call this method shortly.

The UserProperties class is next, in the following example, and makes the actual call to the SQL Azure database. It takes two input parameters: the property name to save and its encrypted value stored in the CipherText structure. On line 13, you retrieve the connection string from another class and open the database connection on line 15. You then create the command object, specifying a call to a stored procedure. The code for the stored procedure is provided later. The hash value is then created on line 39; as you can see, you call the ComputeHash method just reviewed by passing each stored procedure parameter as a byte array. This is where you use both the extension methods created earlier and the hashing method. After the hash result is calculated, you pass it into the last stored procedure parameter on line 45:

1.   using System.Data.SqlDbType;
2.   public class UserProperties
3.   {
4.
5.       /// <summary>
6.       /// Saves a property value in a SQL Azure database
7.       /// </summary>
8.       /// <param name="propertyName">The property name</param>
9.       /// <param name="ct">The CipherText structure to save</param>
10.      public static void Save(string propertyName, CipherText ct)
11.      {
12.          using (SqlConnection sqlConn =
13.                     new SqlConnection(CDatabase.ConnectionString))
14.          {
15.              sqlConn.Open();
16.
17.             using (SqlCommand sqlCmd = new SqlCommand())
18.             {
19.

20.                 DateTime dateUpdated = DateTime.Now;
21.
22.                 sqlCmd.Connection = sqlConn;
23.                 sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
24.                 sqlCmd.CommandText = "proc_SaveProperty";
25.                 sqlCmd.Parameters.Add("name", NVarChar, 255);
26.                 sqlCmd.Parameters.Add("value", VarBinary, int.MaxValue);
27.                 sqlCmd.Parameters.Add("vector", VarBinary, 16);
28.                 sqlCmd.Parameters.Add("lastUpdated", DateTime);
29.                 sqlCmd.Parameters.Add("hash", VarBinary, 32);
30.                 sqlCmd.Parameters[0].Value = propertyName;
31.                 sqlCmd.Parameters[1].Value = ct.cipher;
32.                 sqlCmd.Parameters[2].Value = ct.vector;
33.                 sqlCmd.Parameters[3].Value = dateUpdated;
34.
35.                 // Calculate the hash of this record...
36.                 // We pass the list of values that should be hashed
37.                 // If any of these values changes in the database,
38.                 // recalculating the hash would yield a different result
39.                 byte[] hash = Util.ComputeHash(
40.                     propertyName.GetBytes(),
41.                     ct.cipher,
42.                     ct.vector,
43.                     dateUpdated.GetBytes());
44.
45.                 sqlCmd.Parameters[4].Value = hash;
46.
47.                 int res = sqlCmd.ExecuteNonQuery();
48.
49.             }
50.
51.             sqlConn.Close();
52.
53.         }
54.     }
55.
56. }

As promised, following is the code for the stored procedure. You create a stored procedure because it allows you to provide additional security from an access-control standpoint. As you see later, you create a schema that contains the tables and a separate schema for the stored procedures that access the tables. This provides greater control over your database security. You review schemas later in this chapter:

IF (Exists(SELECT * FROM sys.sysobjects WHERE Name = 'proc_SaveProperty' AND Type = 'P'))
   DROP PROC proc_SaveProperty

GO

-- SELECT * FROM UserProperties
CREATE PROC proc_SaveProperty
   @name nvarchar(255),
   @value varbinary(max),
   @vector binary(16),

@lastUpdated datetime,
   @hash binary(32)
AS

IF (Exists(SELECT * FROM UserProperties WHERE PropertyName = @name))
BEGIN
   UPDATE UserProperties SET
      Value = @value,
      Vector = @vector,
      LastUpdated = @lastUpdated,
      Token = @hash
   WHERE
      PropertyName = @name
END
ELSE
BEGIN
   INSERT INTO UserProperties
        (PropertyName, Value, Vector, LastUpdated, Token)
   VALUES (
      @name,
      @value,
      @vector,
      @lastUpdated,
      @hash )
END

This stored procedure performs both updates and inserts depending on the property name. Note the use of varbinary(max); because you don't know how long the encrypted value will be, you allow large but variable binary objects to be stored. However, the vector is always 16 bytes in length and the hash 32.

Running the Save() method on the UserProperties class creates a record in the UserProperties table. The following code shows how to call the Save method:

1.     class Program
2.    {
3.        static void Main(string[] args)
4.        {
5.            // Declare the encryption object and encrypt our secret value
6.            Encryption e = new Encryption();
7.            CipherText ct = e.EncryptAES("secret value goes here...");
8.
9.            UserProperties.Save("MySecret", ct);
10.
11.       }
12.   }

Figure 4-3 shows the content of the table. The Value column is your encrypted value, the Vector is the @vector variable from the stored procedure, and the Token column is the calculated hash passed as the @hash variable.

Figure 4.3. Record with the encrypted value, a hash, and a vector

Last but not least, you should know that SQL Server and SQL Azure both support hashing natively. Unfortunately, support for hashing in both database platforms is limited to the MD5 and SHA-1 algorithms. The hashing method used in the C# code shown previously uses SHA-256 as its algorithm, which is much stronger. Here is a quick example of how to compute an SHA-1 hash in SQL:

SELECT HASHBYTES('sha1', 'MySecret')

The output of HASHBYTES() is a byte array as well:

0xEABBEC6F31804EB968E2FAEAAEF150546A595FC3

So far, you've seen a way to encrypt sensitive information for confidentiality, hashed certain columns of a record for increased integrity, and deployed in Azure for strong availability. As you can see, developing encryption and hashing routines can be very complex and requires a strong command of the programming language. You may find it beneficial to create a generic encryption library, like the one shown in the previous examples, that can be reused across projects.

As discussed previously, SQL Azure doesn't support X.509 certificates, although you can deploy X.509 certificates in Windows Azure. Your client code (either hosted on your company's network or in Windows Azure) can use certificates to encrypt and decrypt values. The use of certificates implies that you're encrypting using a public/private key pair. The public key is used to encrypt data, and the private key is used to decrypt data.

You can easily create a self-signed certificate using the MakeCert.exe utility. To create a certificate on your machines, run the following command at a command line. You need to execute this statement as an Administrator or the command will fail:

makecert -ss root -pe -r -n "CN=BlueSyntaxTest" -sky Exchange -sr LocalMachine

Here is a brief overview of the options used to create this certificate:

To verify that your certificate was properly installed, open mmc.exe. Select File→ Add/Remove Snap In. Then, select Certificates, click Add, choose Computer, and click OK. Expand the tree on the left to view the certificates under Trusted Root Certification Authorities. Figure 4-4 shows the BlueSyntaxTest certificate that was created with the earlier command.

Figure 4.4. Viewing certificates on your machine

Now that you have a certificate installed, you can search for and locate it with code. Usually, a certificate is searched for by its unique identifier (thumbprint) or by its common name (CN). To view the thumbprint of your certificate, double-click the certificate, select the Details tab, and scroll down until you see the Thumbprint property, as shown in Figure 4-5.

Figure 4.5. Obtaining a certificate's thumbprint

You can select the thumbprint and copy it into a string variable. The following code shows a new private variable and a new method in the Encryption class you saw earlier. Line 1 contains the thumbprint as seen in Figure 4-5, line 13 opens the root certificate store on LocalMachine, and line 17 instantiates an X.509 object by searching the thumbprint. Note that the Find method returns a collection; you're interested in the first certificate because only one will match this thumbprint. On line 24, you create the RSA encryption object and call its Encrypt method on line 27. Because encrypting with RSA automatically incorporates a vector, there is no need to keep track of it. So, the CipherText vector variable is set to 0:

1.  private string _THUMBPRINT_ =
2.     "01 71 11 17 0a b4 96 7b ca 1f f3 e5 bc 0f 68 9d c6 c0 3b 7b";
3.
4.          /// <summary>
5.          /// Encrypts a string value using a self-signed certificate
6.          /// </summary>
7.          /// <param name="value">The value to encrypt</param>
8.          /// <returns></returns>
9.          public CipherText EncryptByCert(string value)
10.         {
11.             byte[] buffer = UTF8Encoding.UTF8.GetBytes(value);
12.
13.             X509Store store = new X509Store(StoreName.Root,
14.                 StoreLocation.LocalMachine);
15.             store.Open(OpenFlags.ReadOnly);
16.
17.             X509Certificate2 x509 =
18.                 store.Certificates.Find(
19.                 X509FindType.FindByThumbprint,
20.                 _THUMBPRINT_, true)[0];
21.
22.             store.Close();
23.
24.             RSACryptoServiceProvider rsaEncrypt = null;
25.             rsaEncrypt = (RSACryptoServiceProvider)x509.PublicKey.Key;
26.
27.             byte[] encryptedBytes = rsaEncrypt.Encrypt(buffer, false);
28.
29.             CipherText ct = new CipherText();
30.             ct.cipher = encryptedBytes;
31.             ct.vector = new byte[] {0, 0, 0, 0, 0, 0, 0, 0, 0,
32.                 0, 0, 0, 0, 0, 0, 0};
33.
34.             return ct;
35.         }

The decryption code is shown next and is very similar to the preceding example. You make a call to Decrypt instead of Encrypt on the RSA object:

1.          public string DecryptByCert(CipherText ct)
2.          {
3.              X509Store store = new X509Store(StoreName.Root,
4.                  StoreLocation.LocalMachine);
5.              store.Open(OpenFlags.ReadOnly);
6.
7.              X509Certificate2 x509 =
8.                  store.Certificates.Find(
9.                  X509FindType.FindByThumbprint,
10.                 _THUMBPRINT_, true)[0];

11.             store.Close();

12.
13.             RSACryptoServiceProvider rsaEncrypt = null;
14.             rsaEncrypt = (RSACryptoServiceProvider)x509.PrivateKey;
15.
16.             byte[] bytes = rsaEncrypt.Decrypt(ct.cipher, false);
17.
18.             return UTF8Encoding.UTF8.GetString(bytes);
19.         }

The following code calls the RSA encryption routine and saves to the UserProperties table as previously described. The table now contains two records. Note that the length of the ciphertext is much greater with the certificate encryption approach:

1.     class Program
2.     {
3.         static void Main(string[] args)
4.         {
5.             // Declare the encryption object and encrypt our secret value
6.             Encryption e = new Encryption();
7.             CipherText ct = e.EncryptAES("secret value goes here...");
8.             CipherText ct2 = e.EncryptByCert("another secret!!!");
9.
10.            UserProperties.Save("MySecret", ct);
11.            UserProperties.Save("MySecret2", ct2);
12.
13.        }
14.    }

AUTHN is a process that verifies you're indeed who you say you are. In SQL Server, the AUTHN process is done through one of two mechanisms: network credentials (or Security Support Provider Interface [SSPI]) or SQL Server credentials. Connection strings must specify which AUTHN is being used. And when you use SQL Server AUTHN, a password must be provided before attempting to connect, either by a user at runtime or in a configuration file.

Keep the following items in mind when you're considering AUTHN with SQL Azure:

Authorization gives you the ability to control who can perform which actions after being authenticated. It's important to define a good AUTHZ model early in your development cycle, because changing access-control strategy can be relatively difficult.

Generally speaking, a strong AUTHZ model defines which users can access which objects in the database. This is typically performed in SQL Azure and SQL Server by defining relationships between logins, users, schemas, and rights.

CREATE LOGIN MyTestLogin WITH PASSWORD='MyT3stL0gin'
GO
CREATE USER MyTestLoginUser FROM LOGIN MyTestLogin
GO
EXEC sp_addrolemember 'loginmanager', MyTestLoginUser
GO

Schemas

A schema is a container that holds database objects; schemas reside inside a database. Schemas are part of the three-part naming convention of database objects; they're considered namespaces. Each object in a schema must have a unique name.

By default, objects created are owned by the DBO schema. For example, the CREATE TABLE statement showed previously for the UserProperties table uses DBO as the schema owner (schema_id is always 1 for DBO). See Figure 4-6.

Right now, the new user MyTestLoginUser can't read from this table. Attempting to issue a SELECT statement against UserProperties returns a SELECT permission denied error. So, you have a choice: you can either give that user account SELECT permission or create a schema for that user and assign the SELECT permission to the schema.

Figure 4.6. Viewing an object's schema ownership

It's usually much easier to manage access rights through schemas instead of users directly. To do this properly, you need to change the ownership of the UserProperties table to a new schema (other than DBO) and then assign access rights to the schema.

To create a new schema, you must be connected to the desired user database where MyTestLoginUser has been created. Then, run the following statement:

CREATE SCHEMA MyReadOnlySchema AUTHORIZATION DBO

At this point, a schema as been created; it's owned by DBO. You now need to change the ownership of the UserProperties table to MyReadOnlySchema:

ALTER SCHEMA MyReadOnlySchema TRANSFER DBO.UserProperties

The table now belongs to the schema, as shown in Figure 4-7.

Figure 4.7. Viewing the new schema owner

However, you aren't done just yet. MyTestLoginUser can no longer see the table. Issuing a select statement on the table returns an Invalid object name message, as shown in Figure 4-8.

Figure 4.8. Error when the user can't see an object

The default schema of MyTestLoginUser is DBO, as shown in Figure 4-9. The default schema of a user is the schema that's used if none is specified in a T-SQL statement. To make it easier on developers, change the default schema to MyReadOnlySchema, so it doesn't have to be specified in T-SQL statements.

Figure 4.9. Schema owner of a login

To change the user's default schema, you need to execute this statement:

ALTER USER MyTestLoginUser WITH DEFAULT_SCHEMA = MyReadOnlySchema

Now that the user has MyReadOnlySchema as its default schema, it can see the objects owned by that schema directly, without having to specify the object owner. However, you haven't set the access rights yet. Let's grant SELECT rights to MyTestLoginUser:

GRANT SELECT ON SCHEMA :: MyReadOnlySchema TO MyTestLoginUser

The following statement works again for the MyTestLoginUser account:

SELECT * FROM UserProperties

Why did you go through all this trouble? Because creating your own schemas is a great way to simplify access control by granting rights to schemas instead of objects directly. In a way, schemas can be used as a group, like a Windows Group, on which rights are granted or denied.

Figure 4-10 shows how you've switched the security model around for greater flexibility and control.

Figure 4.10. Moving to a security model through schemas

SQL Azure comes with its own firewall, which you can configure directly from the SQL Azure portal, as previously covered in Chapter 3. You can also view and change firewall rules in T-SQL. Let's take a quick look at the available SQL statements.

To view the current firewall rules, execute this statement:

SELECT * FROM sys.firewall_rules

You can see that each rule has a name; the name is used as a unique key. The sp_set_firewall_rule command allows you to add a new rule.

It may take a few minutes for the new rules to take effect. For example, the following statement adds a new rule called NewRule. Notice that the first parameter must be a Unicode string:

sp_set_firewall_rule N'NewRule', '192.168.1.1', '192.168.1.10'

To delete a rule, run the following statement:

sp_delete_firewall_rule N'NewRule'