[snap] Cryptography limitations

As mentioned before SQL Server 2005 brings several security improvements – one of them being the support for cryptography. The purpose of the built-in cryptiographic functions is to enhance server and/or database security and to provide additional methods in data security (by allowing the encryption of values in columns and variables). The SQL Server encryption hierarchy is well documented in Books Online, and so are the built-in cryptographic functions. What Books Online fail to mention, however, (even after being updated for the third time since the release) is the fact that there are limitations to consider regarding encryption in SQL Server 2005.

One limitation is the size of the clear text that can be encrypted. A limitation that practically renders the built-in functions useless for large values (larger than the available maximum length based on the length of the key), unless the value is encrypted in chunks. The behaviour of the SQL built-in functions is dependent on the CryptoAPI cryptographic functions, specifically the CryptEncrypt function.

Even before the release of SQL Server 2005 these limitations have been discussed, and the author of the article also posted appropriate T-SQL user-defined functions to serve as workarounds. These user-defined functions split the clear text into digestible chunks allowing the use of the built-in functions for larger values (and vice-versa). Which is nice. And – judging by the test scripts provided in that post – reliable as well. But are these functions as efficient as they should be? One thing is almost certain – T-SQL is not as efficient as CLR when it comes to operations on strings, so appropriate SQL CLR functions will perform better. This, of course, is not meant as a critique of the article mentioned above, it's rather a simple statement of facts – the built-in functions are limited in their usability, and the workarounds come with their own limitations as well.

In this Microsoft feedback article the issue regarding the encryptByCert built-in function is resolved as "by design".

Which brings us straight to the point of this post – while the built-in cryptographic functions work well with shorter values (e.g. encryption keys, passwords, credit card numbers, salaries etc.) it would be better to let the client application handle the encryption and decryption of larger values (e.g. longer text values). The encryption keys, however, could still be safely stored (encrypted, even) on the server.

ML

XPath/XQuery functions in SQL Server 2005

One of the key improvements in SQL Server 2005 is the implementation of XML as a native data type. At first glance this may not seem like such a big task (after all, it's just one more data type, right, and it's been a standard for years) but after considering the amount of additional features involved, the task of implementing it seems far from trivial:

• XML Schema - a proper data type requires a constrainable domain and the XML data type even comes with its own validation standard;
• XML Indexes - what good is a column data type that can't be indexed?
• Full-text indexing - ok, the XML word breaker already existed in SQL 2000, nonetheless it's a feature worth mentioning;
• XML Methods - complex data types (in contrast to primitive data types) usually come with native data access methods and the SQL XML implementation is no exception;
• XML DML - would the implementation of XML in SQL be of any use without appropriate additions to the data modification language?
• XPath - the XPath implementation is vital to the usability of XML: it's used in all XML retrieval and DML methods;
• XQuery - in contrast to other familiar complex data types the XML data type comes with its own querying language. Currently the standard is still in development, but numerous implementations are already available.

Of course when I say native data type, there are a few limitations to consider, and its size cannot exceed 2 GB.

The XPath and the XQuery language standards come with a variety of functions and operators - currently 179 of them according to the XQuery 1.0 and XPath 2.0 Functions and Operators W3C Candidate Recommendation. Obviously not every function in this Candidate Recommendation has been implemented in SQL Server 2005, but the W3C list serves as a valid point of reference.

The Microsoft SQL Server implementation of the XPath and the XQuery standards follows the XML Path Language (XPath) Version 1.0 W3C Recommendation and the XQuery 1.0 W3C Working Draft. As the development of the XQuery standard continues several changes have been made by the Working Group since the SQL Server implementation. The latest is the Candidate Recommendation of June, 8^th 2006.

Taken from the W3C Glossary and Dictionary:

• Working Draft (WD)
  A Working Draft is a document that W3C has published for review by the community, including W3C Members, the public, and other technical organizations.
• Candidate Recommendation (CR)
  A Candidate Recommendation is a document that W3C believes has been widely reviewed and satisfies the Working Group's technical requirements. W3C publishes a Candidate Recommendation to gather implementation experience.
• W3C Recommendation (REC)
  A W3C Recommendation is a specification or set of guidelines that, after extensive consensus-building, has received the endorsement of W3C Members and the Director. W3C recommends the wide deployment of its Recommendations. Note: W3C Recommendations are similar to the standards published by other organizations.

The decision to implement a W3C Working Draft in the release of SQL Server 2005 is a bold step forward for Microsoft. It also explains why only the very elementary XQuery functions have been included so far.

Since not all XPath and XQuery functions have been included in the SQL Server 2005 RTM, and as their implementation most probably exceeds the extent of changes and improvements reserved for Service Releases it's safe to assume that more functions will not be available until the next release of SQL Server. Another question is the evolution of the XQuery standard itself - will a W3C Recommendation be available before the next release of SQL Server?

That's why I've also decided to keep track of implementations as the functions are added in upcoming releases of SQL Server. I'm also keeping an eye on the XQuery Working Group to track any additions to the collection of functions.

Below is a list of all XPath XQuery Functions that are part of the W3C Candidate Recommendation mentioned above. The second column shows the release of SQL Server that first implemented them.

Function name	Release of first implementation
fn:abs	-
fn:adjust-date-to-timezone	-
fn:adjust-dateTime-to-timezone	-
fn:adjust-time-to-timezone	-
fn:avg	SQL 2005 RTM more...
fn:base-uri	-
fn:boolean	-
fn:ceiling	SQL 2005 RTM more...
fn:codepoint-equal	-
fn:codepoints-to-string	-
fn:collection	-
fn:compare	-
fn:concat	SQL 2005 RTM more...
fn:contains	SQL 2005 RTM more...
fn:count	SQL 2005 RTM more...
fn:current-date	-
fn:current-dateTime	-
fn:current-time	-
fn:data	SQL 2005 RTM more...
fn:dateTime	-
fn:day-from-date	-
fn:day-from-dateTime	-
fn:days-from-duration	-
fn:deep-equal	-
fn:default-collation	-
fn:distinct-values	SQL 2005 RTM more...
fn:doc	-
fn:doc-available	-
fn:document-uri	-
fn:empty	SQL 2005 RTM more...
fn:encode-for-uri	-
fn:ends-with	-
fn:error	-
fn:escape-html-uri	-
fn:exactly-one	-
fn:exists	-
fn:false	SQL 2005 RTM more...
fn:floor	SQL 2005 RTM more...
fn:hours-from-dateTime	-
fn:hours-from-duration	-
fn:hours-from-time	-
fn:id	SQL 2005 RTM more...
fn:idref	-
fn:implicit-timezone	-
fn:in-scope-prefixes	-
fn:index-of	-
fn:insert-before	-
fn:iri-to-uri	-
fn:lang	-
fn:last	SQL 2005 RTM more...
fn:local-name	SQL 2005 RTM more...
fn:local-name-from-QName	SQL 2005 RTM
fn:lower-case	SQL 2008 RTM more...
fn:matches	-
fn:max	SQL 2005 RTM more...
fn:min	SQL 2005 RTM more...
fn:minutes-from-dateTime	-
fn:minutes-from-duration	-
fn:minutes-from-time	-
fn:month-from-date	-
fn:month-from-dateTime	-
fn:months-from-duration	-
fn:name	-
fn:namespace-uri	SQL 2005 RTM
fn:namespace-uri-for-prefix	-
fn:namespace-uri-from-QName	SQL 2005 RTM
fn:nilled	-
fn:node-name	-
fn:normalize-space	-
fn:normalize-unicode	-
fn:not	SQL 2005 RTM more...
fn:number	SQL 2005 RTM more...
fn:one-or-more	-
fn:position	SQL 2005 RTM more...
fn:prefix-from-QName	-
fn:QName	-
fn:remove	-
fn:replace	-
fn:resolve-QName	-
fn:resolve-uri	-
fn:reverse	-
fn:root	-
fn:round	SQL 2005 RTM more...
fn:round-half-to-even	-
fn:seconds-from-dateTime	-
fn:seconds-from-duration	-
fn:seconds-from-time	-
fn:starts-with	-
fn:static-base-uri	-
fn:string	SQL 2005 RTM more...
fn:string-join	-
fn:string-length	SQL 2005 RTM more...
fn:string-to-codepoints	-
fn:subsequence	-
fn:substring	SQL 2005 RTM more...
fn:substring-after	-
fn:substring-before	-
fn:sum	SQL 2005 RTM more...
fn:timezone-from-date	-
fn:timezone-from-dateTime	-
fn:timezone-from-time	-
fn:tokenize	-
fn:trace	-
fn:translate	-
fn:true	SQL 2005 RTM more...
fn:unordered	-
fn:upper-case	SQL 2008 RTM more...
fn:year-from-date	-
fn:year-from-dateTime	-
fn:years-from-duration	-
fn:zero-or-one	-

Update (2009-03-07)

In SQL Server 2008 two aditional functions have been added to the collection:

• lower-case(); and
• upper-case().

ML

p.s. If you're interested in more information regarding the implementation of XML in SQL 2005 continue here.

Certificate dependency

Knowing whether a certificate exists and/or is in use by a dependent object is good, knowing how it's used is slightly better, but knowing exactly which objects are using a certificate is what is really useful.

This post is a follow-up to a previous post concerning the use of certificates, so without further ado...

...here's the function:

create function dbo.fnList_Certificate_Dependencies
 (
 @certificateName sysname  = null
 )
returns table
as
return (
 select sys.certificates.[name] as CertificateName
  ,sys.certificates.certificate_id as CertificateId
  ,'database principal' as DependentObjectType
  ,sys.database_principals.[name] as DependentObjectName
  ,sys.database_principals.principal_id as DependentObjectId
  from sys.certificates
   inner join sys.database_principals
     on sys.database_principals.sid = sys.certificates.sid
  where ((sys.certificates.[name] = @certificateName)
    or (@certificateName is null))
 union
 select sys.certificates.[name]
  ,sys.certificates.certificate_id
  ,'symmetric key'
  ,sys.symmetric_keys.[name]
  ,sys.symmetric_keys.symmetric_key_id
  from sys.certificates
   inner join sys.key_encryptions
     on sys.key_encryptions.thumbprint = sys.certificates.thumbprint
     inner join sys.symmetric_keys
       on sys.symmetric_keys.symmetric_key_id = sys.key_encryptions.key_id
  where ((sys.certificates.[name] = @certificateName)
    or (@certificateName is null))
 union
 select sys.certificates.[name]
  ,sys.certificates.certificate_id
  ,coalesce(lower(replace(sys.objects.type_desc, '_', ' ')), 'unknown')
  ,coalesce(sys.objects.[name], 'unknown')
  ,sys.objects.[object_id]
  from sys.certificates
   inner join sys.crypt_properties
     on sys.crypt_properties.thumbprint = sys.certificates.thumbprint
     left join sys.objects
       on sys.objects.[object_id] = sys.crypt_properties.major_id
  where ((sys.certificates.[name] = @certificateName)
    or (@certificateName is null))
 )
go

The function lists the name and the id of the certificate in question along with the names, ids and a description of the dependent object's type. Possible dependent objects are discussed in this post.

Two examples of use:

• to list dependencies of a specific certificate (e.g. 'SalesProxy'):

  select *
   from dbo.fnList_Certificate_Dependencies('SalesProxy')

• to list dependencies of all certificates:

  select *
   from dbo.fnList_Certificate_Dependencies(null)

Warning!
Note that neither of the functions posted here detect whether a certificate has been used to encrypt data, so backup your certificates before dropping them and unintentionally encrypting your data permanently!

ML