Your database has been protecting sensitive data at rest using the cell-level encryption features of SQL Server for quite sometime. The employees in the auditing department have been inviting you to their after-work gatherings and buying you drinks. Thousands of customers implicitly include you in their prayers of thanks giving as their identities remain safe in your company's database.

The cipher text resting snuggly in a column of the varbinary data type is great for security; but it can create some interesting challenges when interacting with other data types such as the XML data type. The XML data type is one that is often used as a message type for the Service Broker feature of SQL Server. It also can be an interesting data type to capture for auditing or integrating with external systems. The challenge that cipher text presents is that the need for decryption remains even after it has experienced its XML metamorphosis. Quite an interesting challenge nonetheless; but fear not. There is a solution.

To simulate this scenario, we first will want to create a plain text value for us to encrypt. We will do this by creating a variable to store our plain text value:

-- set plain text value
DECLARE @PlainText NVARCHAR(255);
SET @PlainText = 'This is plain text to encrypt';

The next step will be to create a variable that will store the cipher text that is generated from the encryption process. We will populate this variable by using a pre-defined symmetric key and certificate combination:

-- encrypt plain text value
DECLARE @CipherText VARBINARY(MAX);
OPEN SYMMETRIC KEY SymKey
    DECRYPTION BY CERTIFICATE SymCert
    WITH PASSWORD='mypassword2010';
    SET @CipherText = EncryptByKey
                         ( 
                          Key_GUID('SymKey'), 
                          @PlainText 
                         );
CLOSE ALL SYMMETRIC KEYS;

The value of our newly generated cipher text is 0x006E12933CBFB0469F79ABCC79A583--. This will be important as we reference our cipher text later in this post. Our final step in preparing our scenario is to create a table variable to simulate the existence of a table that contains a column used to hold encrypted values. Once this table variable has been created, populate the table variable with the newly generated cipher text:

-- capture value in table variable
DECLARE @tbl TABLE (EncVal varbinary(MAX));
INSERT INTO @tbl (EncVal) VALUES (@CipherText);

We are now ready to experience the challenge of capturing our encrypted column in an XML data type using the FOR XML clause:

-- capture set in xml
DECLARE @xml XML;
SET @xml = (SELECT  
            EncVal
            FROM @tbl AS MYTABLE
            FOR XML AUTO, BINARY BASE64, ROOT('root'));

If you add the SELECT @XML statement at the end of this portion of the code you will see the contents of the XML data in its raw format:

<root>
  <MYTABLE EncVal="AG4Skzy/sEafeavMeaWDBwEAAACE--" />
</root>

Strangely, the value that is captured appears nothing like the value that was created through the encryption process. The result being that when this XML is converted into a readable data set the encrypted value will not be able to be decrypted, even with access to the symmetric key and certificate used to perform the decryption. An immediate thought might be to convert the varbinary data type to either a varchar or nvarchar before creating the XML data. This approach makes good sense. The code for this might look something like the following:

-- capture set in xml
DECLARE @xml XML;
SET @xml = (SELECT 
            CONVERT(NVARCHAR(MAX),EncVal) AS EncVal
            FROM @tbl AS MYTABLE
            FOR XML AUTO, BINARY BASE64, ROOT('root'));

However, this results in the following error:

Msg 9420, Level 16, State 1, Line 26
XML parsing: line 1, character 37, illegal xml character

A quick query that returns CONVERT(NVARCHAR(MAX),EncVal) reveals that the value that is causing the error looks like something off of a genuine Chinese menu. While this situation does present us with one of those spine-tingling, expletive-generating challenges, rest assured that this approach is on the right track. With the addition of the "style" argument to the CONVERT method, our solution is at hand. When dealing with converting varbinary data types we have three styles available to us:

- The first is to not include the style parameter, or use the value of "0". As we see, this style will not work for us.

- The second option is to use the value of "1" will keep our varbinary value including the "0x" prefix. In our case, the value will be 0x006E12933CBFB0469F79ABCC79A583--

- The third option is to use the value of "2" which will chop the "0x" prefix off of our varbinary value. In our case, the value will be 006E12933CBFB0469F79ABCC79A583--

Since we will want to convert this back to varbinary when reading this value from the XML data we will want the "0x" prefix, so we will want to change our code as follows:

-- capture set in xml
DECLARE @xml XML;
SET @xml = (SELECT 
            CONVERT(NVARCHAR(MAX),EncVal,1) AS EncVal
            FROM @tbl AS MYTABLE
            FOR XML AUTO, BINARY BASE64, ROOT('root'));

Once again, with the inclusion of the SELECT @XML statement at the end of this portion of the code you will see the contents of the XML data in its raw format:

<root>
  <MYTABLE EncVal="0x006E12933CBFB0469F79ABCC79A583--" />
</root>

Nice! We are now cooking with gas. To continue our scenario, we will want to parse the XML data into a data set so that we can glean our freshly captured cipher text. Once we have our cipher text snagged we will capture it into a variable so that it can be used during decryption:

-- read back xml
DECLARE @hdoc INT;
DECLARE @EncVal NVARCHAR(MAX);
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml;

SELECT @EncVal = EncVal
FROM OPENXML (@hdoc, '/root/MYTABLE')
WITH ([EncVal] VARBINARY(MAX) '@EncVal');

EXEC sp_xml_removedocument @hDoc;

Finally, the decryption of our cipher text using the DECRYPTBYKEYAUTOCERT method and the certificate utilized to perform the encryption earlier in our exercise:

SELECT
    CONVERT(NVARCHAR(MAX),
                    DecryptByKeyAutoCert
                         ( 
                          CERT_ID('AuditLogCert'), 
                          N'mypassword2010', 
                          @EncVal 
                         )
                    ) EncVal;

Ah yes, another hurdle presents itself! The decryption produced the value of NULL which in cryptography means that either you don't have permissions to decrypt the cipher text or something went wrong during the decryption process (ok, sometimes the value is actually NULL; but not in this case). As we see, the @EncVal variable is an nvarchar data type. The third parameter of the DECRYPTBYKEYAUTOCERT method requires a varbinary value. Therefore we will need to utilize our handy-dandy CONVERT method:

SELECT
    CONVERT(NVARCHAR(MAX),
                    DecryptByKeyAutoCert
                         ( 
                           CERT_ID('AuditLogCert'), 
                           N'mypassword2010', 
                           CONVERT(VARBINARY(MAX),@EncVal) 
                         )
                    ) EncVal;

Oh, almost. The result remains NULL despite our conversion to the varbinary data type. This is due to the creation of an varbinary value that does not reflect the actual value of our @EncVal variable; but rather a varbinary conversion of the variable itself. In this case, something like 0x3000780030003000360045003--. Considering the "style" parameter got us past XML challenge, we will want to consider its power for this challenge as well. Knowing that the value of "1" will provide us with the actual value including the "0x", we will opt to utilize that value in this case:

SELECT
    CONVERT(NVARCHAR(MAX),
                    DecryptByKeyAutoCert
                         ( 
                          CERT_ID('SymCert'), 
                          N'mypassword2010', 
                          CONVERT(VARBINARY(MAX),@EncVal,1) 
                         )
                    ) EncVal;

Bingo, we have success! We have discovered what happens with varbinary data when captured as XML data. We have figured out how to make this data useful post-XML-ification. Best of all we now have a choice in after-work parties now that our very happy client who depends on our XML based interface invites us for dinner in celebration. All thanks to the effective use of the style parameter.