Encrypting XML Typed Data in SQL Server

Two issues:

1. W3C XML Encryption is broken by most accounts.
2. SQL Server encryption functions cannot digest XML (pun intended).

A double whammy!

The first issue is a bigger deal. If you are encrypting sensitive data with a web service – and you probably are – then your data is vulnerable. If that data is personally identifiable information about individuals your are doing business with then it is your duty and responsibility to stop using those solutions until the XML Encryption standard is corrected. Not until the vendors roll out their fixes and the application is appropriately updated should the application be re-enabled. To knowingly continue to rely upon the broken encryption is a criminal activity in my opinion.

Here’s why. In 2003 the Federal Trade Commission (FTC) reported that over 27 million Americans were “victims of identity theft”. In 2007 ars Technica reported that 159 million people were “affected by data breaches” between 2005 and 2007. The Privacy Rights Clearing House reports 542,355,201 records breached from 2747 data breaches between 2005 and 2011. According to the New York Daily News Facebook alone is attacked 600,000 times a day. Almost all Facebook has to offer is your person information . The trend is much more than obvious. At the same time Reuters reports that MasterCard hits an all-time high on the stock market, “MasterCard Inc shares jumped 7.4 percent to $359.12 after the credit card processor reported its quarterly profit easily beat estimates on double-digit increases in volumes.”

The Information Commissioner of the UK, Christopher Graham told the Telegraph the other day that database breaches are up 58% in the UK in the last year. FWIW, the commissioner can levy fines up to £500,000 under the Kingdom’s Data Protection Act (DPA). Sounds like they have the possibility to agree with my sentiment regarding the undeniable responsibility to protect other people’s information. Sadly, I don’t think the penalty has been leveraged much – if at all – judging by the obvious parallel trend in the USA and the UK.

The broken status quo of capitalism that places profit above human decency, human dignity and often even human rights is rampant and unraveling around the world regardless of corporate hegemony and political corruption. Infosys co-chair Kris Gopalakrishnan seems to agree with that appraisal – calling out a systemic income imbalance that advantages the wealthiest 3%-5% in western and emerging economies. Infosys is the largest IT organization in the world. He also states that the middle class in the west is already being diminished in reciprocation to the rise of the middle class in the emerging economies and that the decline in the west is needed to achieve sustainability for the world population. Another double whammy for IT workers in the US, where the distribution of income grows ever more skewed and the downward spiral of the middle class is at least on the edge if not fully out of control. The wealthiest amass more wealth while the middle class has now lost the collateral it blew to Reagan Up: consume to help the economy, and now we have nothing to do but wait for some corporation to pee its record profits down on us. And wait. And wait. How much more obvious does it need to be for intelligent people to understand that the wealth that continues to accumulate in the accounts of a few – that have no practical purpose for more wealth – is coming not from vapor but out of the pockets of workers that too often don’t quite have enough and deserve better?  

In the USA the worst that can happen to an entity that carelessly exposes other’s private and/or personal information is a bit of hate mail and perhaps a few underfunded civil suits by folks who’s lives have been ruined and accounts depleted as a direct result of corporate negligence and stonewalling. There are rumblings about accountability measures from the government. What ever the government does, it will not be adequate: too many politicians are in the pockets of the people that would make a bundle from that unsecure data. Enough is enough!. Anytime someone can waltz in and steal other people’s personally identifiable information from a data center or business network, the  systems owners and administrators were carelessly negligent. If the theft recurs or is ongoing that negligence has gone beyond the point of criminal culpability. For effective deterrence, the penalty must match the crime. That’s all I’m saying.

If the next 5 businesses that failed to protect personally identifiable details that had been demanded from customers were shut down for even a week by the government the landscape would be changed significantly for the better for as long as the threat of meeting a similar fate for similar negligence might endure. This is what the government used to do in the mines in Wyoming where I worked in the 70s when safety would get too far out of hand in the push to produce, but even then someone often had to die to get the attention of the government inspectors.  An equitable solution would be to require the leaker to appropriately compensate the folks that were violated but such fairness will not find its way into the legal system in my lifetime I am sure. The leaker, of course, could pursue the attacker to recover costs.  The essential ingredient in a solution is unavoidable accountability. effective regulation could create accountability were the bureaucracy not so corrupt. Even the DOJs National Institute for Justice cannot uncover the real cost of identity theft noting, “A serious lack of data on these issues inhibits research into possible intervention strategies that could reduce the harm.” Until the effective costs to corporations is established as more than the cost to not protect data as it to do the right thing and protect our information, the necessary change is unlikely as long as the effective costs for corporations can be avoided. According to Mike Vizard, as he lays out a a survey based accounting in “The Cost of Insecurity” (CTOEdge, DEC 2010), “… the majority of the IT organizations surveyed by [IT security vendor] Lumension have yet to deploy any of these security technologies, even as their operational security costs continue to rise.” Follow the article link ink to see the secure computing adoption rate charts by technology and 12 other mind blowing charts built from a survey of IT shops.

The simple fact is, the technologies needed to protect data and prevent the vast majority of hacker access to information is at hand. Sure it is more costly to implement those technologies than to skip them, but how can it be negative for the profit motive of a corporation when that corporation takes genuine and necessary steps to increase the customer/consumer confidence in that corporation with a comparatively small incremental rise in development costs? The payoff for a corporation that takes the high road is insufficient in today’s capitalism. It just simply isn’t done. I would argue that it is a perversion of the profit motive that we have ever allowed contrivance and deception to drive profit. Profit is not inherently bad. Dishonesty is bad. To profit from dishonesty is perverse. Corporations know they cut corners on data security. That is one of the main reasons they are corporations. So the individuals calling the shots can hide from personal responsibility. Customers/consumers know that corporations cut corners on data security. Government knows that corners are being cut on data security. Nothing changes. That is sick. And inadequate data security in corporate data centers is a primary reason people’s information is exposed and can subsequently be used to do harm to that person.

I am certainly not saying all that is necessary is to punish violations vigorously in this post to solve the worlds data security problems. Secure data solutions will be more complex with extensive defense-in-depth and well worth the effort and cost. They will probably always be under attack as well, requiring on-going hardening. The goal I advocate is to simply do what is right. There is no gray area around what of your personal information I have the right to be careless with, let alone negligently so…

Update January 13, 2012:

Consumer Reports story today on Yahoo indicates that the financial industry has found a way to profit from the fear over identity theft: http://finance.yahoo.com/news/debunking-hype-over-id-theft-080000873.html

The  story also breaks down some identity theft data for 2010. Highlights:

• 50 million Americans paid $120 -$300 a year (that’s $6-$15 billion) for identity theft protection in 2010 – for liability protection the banks already must provide
• Identity fraud was down 27% in 2010 to a mere 8.1 million victims
• Personal identity theft – “in which someone uses your name, birth date, and Social Security number to open new credit accounts, tap your health insurance, earn taxable income, or commit crimes in your name” was at 765,000 households in 2010

---

On the second issue, the incompatibility of SQL Server ‘s encryption functions (ENCRYPTBYASYMKEY, ENCRYPTBYCERT, ENCRYPTBYKEY, ENCRYPTBYPASSPHRASE) and the XML data type is not such a big deal. Even before SQL Server had the XML type, XML has been stored in the database as VARCHAR. That conversion between the XML type and the VARCHAR type is really all that is necessary to encrypt XML at the database. From there the door is open to sending encrypted oData or XML document that may or may not rely upon XML Encryption. That’s right! You could actually work around the XML Encryption vulnerability now without breaking the web services in many case by encrypting the XML at the database and decrypting only where needed.

In addition to missing XML – and UNIQUEIDENTIFIER, and CLR types btw – compatibility, SQL Server encryption functions are also limited to VARBINARY(8000) output in length. That is not a problem for the error handler but may be for many XML documents. Large XML documents are where much of the pain of the broken XML encryption will be felt.  To work around this limitation consider encrypting only sensitive XML attributes at the database and then providing public decryption keys in a secure manner. only to those that must decrypt that data. This is more or less how W3C XML Encryption goes after the problem. One risk in a piecemeal roll-ur-own XML document encryption like I’m suggesting is that the XML attributes not encrypted may still reveal enough information for a successful attack that eventually breaks the encryption. It is of utmost importance that XML encryption is ‘fixed’ ASAP. Using SQL Server or another work-around to plug the hole should not even pretend to replace XML Encryption. SQL Server should be considered only as an augmenting and/or interim solution until the better suited technology is made trustworthy. 

When data originates in the application, SQL Server is almost always a less desirable place for encryption. In part this is because the data must move to the database unprotected.  However, there are many SOA scenarios where the data enters the distributed environment via the database. (i.e. peer to peer transfers, XML feeds, performance data, database errors, etc.) When the database is a de facto gateway for the data into the SOA then the database is a great place for the encryption.

To see how this might work to relieve the XML Encryption woes, consider the following error handler that writes errors to a SPARSE XML column of a logging table. This is data that absolutely originates at the database so is very clearly the best place to encrypt any sensitive data in the error’s message. An encrypted error message is also an effective way to combat padding oracle attacks where-in the messages returned from unsuccessful decryption attempts are mined and analyzed to eventually crack the encryption. The padding oracle attack is proving especially effective against cipher-block chained (CBC) digests such as is used in the XML Encryption . SQL Server also uses CBC digest, yet is malleable to a more resistant and somewhat slower hash based digest. (see http://en.wikipedia.org/wiki/Block_cipher_modes_of_operation for an overview of block cipher modes. See the post https://bwunder.wordpress.com/2011/10/07/repel-dictionary-attacks-with-checksum-truncation-of-the-random-lambda/ for some of my thoughts on hash based digests.)

I don’t mean to exclude streaming modes in the possibilities here, there just isn’t any support for that possibility in SQL Server. At some level streaming encryption makes sense for XML, especially for humongous documents. In that scenario, a streaming resume-able – possibly even transactional but that may be only wishful thinking – encryption would be ideal.

The following example is based loosely on the error handler modeled in the TRY-CATCH topic of Books On-Line.  The example is intended to be extend such that the error event is persisted to a SPARSE XML column in an activity logging table. Once encrypted, the column becomes be a SPARSE VABINARY(8000) column.   

DECLARE @Id NCHAR(36);
SET @Id = NEWID();
BEGIN TRY
  RAISERROR('test', 16, 1);
END TRY
BEGIN CATCH

  SELECT ERROR_NUMBER() AS ErrorNumber
       , ERROR_SEVERITY() AS ErrorSeverity
       , ERROR_STATE() as ErrorState
       , ERROR_PROCEDURE() as ErrorProcedure
       , ERROR_LINE() as ErrorLine
       , ERROR_MESSAGE() as ErrorMessage FOR XML RAW;

  OPEN SYMMETRIC KEY ErrorKey
  DECRYPTION BY CERTIFICATE ErrorCertificate
  WITH password = 'Au&6Gf% 3Fe14CQAN@wcf?';
    SELECT ENCRYPTBYKEY ( KEY_GUID( 'ErrorKey' )
            , ( SELECT ERROR_NUMBER() AS ErrorNumber
                      , ERROR_SEVERITY() AS ErrorSeverity
                      , ERROR_STATE() as ErrorState
                      , ERROR_PROCEDURE() as ErrorProcedure
                      , ERROR_LINE() as ErrorLine
                      , ERROR_MESSAGE() as ErrorMessage 
                FOR XML RAW ) 
            , 1
            , @Id );

    SELECT ERROR_NUMBER() AS ErrorNumber
         , ERROR_SEVERITY() AS ErrorSeverity
         , ERROR_STATE() as ErrorState
         , ERROR_PROCEDURE() as ErrorProcedure
         , ERROR_LINE() as ErrorLine
         , sys.fn_varbintohexstr( 
              ENCRYPTBYKEY ( KEY_GUID( 'ErrorKey' )
                           , ERROR_MESSAGE() 
                           , 1
                           , @Id ) ) AS ErrorMessage 
    FOR XML RAW;

  CLOSE SYMMETRIC KEY ErrorKey; 
END CATCH

The resulting  XML from the first of the three error message formats is clear text:

<row ErrorNumber="50000" 
     ErrorSeverity="16" 
     ErrorState="1" 
     ErrorLine="5" 
     ErrorMessage="test" />

The second is completely encrypted (I added line feeds for display rendering):

0x00335906BE45EA4AB8BF0D94BFB6FC9D010000005E2DAA7F20DA4D827612ECFF
506ABBCBB7BF182711576E4E71E6B3C019123A88E2A7BD47F14A9EA45FB3F55BCB
642B25CF3A5BD9B44C0F58024113774D582C77F3D87D5816E877D643C19230CFDC
70D6A8FDE3269CF136A7111C362E6CB934800DA4D28FEE3E7425F53E14330816E7
C0407E798006FC3B434EC127BD05FCA61597E66150A5E2D8F4616070711CB2C27F
A2B51D7FE242E8F9A6ABB58C6697B6A4AF5B1CFC423E5927E983E4E1D7F0B8571D
7DDCC70184016F4B7EC0D823D4DF615F30ABC5B114738D373FDC0D973DB20EB977
E5CC8246CA7F3CF2E487456DA6C1BC95851BC60890C9FD290DFBAC46B8B2

And the third is a hybrid that encrypts only one sensitive XML attribute (again I added the CR+LFs so you could see the result):

<row ErrorNumber="50000" 
     ErrorSeverity="16" 
     ErrorState="1" 
     ErrorLine="5" 
     ErrorMessage="0x00335906be45ea4ab8bf0d94bfb6fc9d010000
       00537a48339b1669c2de290be82c0895acca90a74f19011a4d00
       6b09adb0930d49b116993af0a3be9e23172b5d0cd9855b379ce8
       85a510e3ca46556ef5bf15fcf2" />

The third option represents a middle ground between encryption and storage requirements. This demonstrates the ‘divide and conquer’ tactic that may be necessary to apply the available tools to a workable solution. Chunking for SQL Server’s VARBINARY(8000) encryption result limitation may be necessary. 

(And of course in the example it is important to persist that @Id to the logging table as well if you ever want to decrypt the value. In the solution I lifted the example from the procedure name is captured by the row so instead of keeping it here as redundant encrypted data, ErrorProcedure() is not captured in the XML column.)

Considering Cryptography in SQL Server Database Architectures

It is useful to have a ‘big picture’ understanding of the possible layers of encryption available for data-at-rest in a data-centric application. A visual that conveys those layers is challenging due to the number of dimensions that must be considered. Here is my best shot at the visual – for today anyway. If you find it easy to understand the diagram at a glance there is probably nothing of interest in what follows (and there is a jar in the Smithsonian with your brain’s name on it):

On the other hand, if your are like me it may look confusing and way too busy. Makes me want another cup of coffee.  Nonetheless, I happen to know it will make perfect sense to all once broken down into 3  coherent parts. 

I will begin with the middle part. You may notice that a user database with a data file group and a FILESTREAM file group – dangling precariously off to the side of the database -is depicted. Along side the user database are the four system databases. The only piece of this picture that may be unfamiliar to some is the FILESTREAM file group that is part of the database but actually lives outside of the database. This is intended to represent a typical FILESTREAM enabled configuration. Nothing special.

  generic db (select to view full size)

The databases are enveloped in various cryptographic building blocks:

Behind, architecturally underlying the databases are SQL Server’s Transparent Data Encryption (TDE), a file encryption container that is less than the physical volume in size (e.g. an encrypted file that is able to present a VHD) and also a fully encrypted system (e.g a drive, storage array, or complete file system).  These encryption layers are designed to encrypt everything placed in storage in the background. No application change is supposed to be required. Also, the encryption protection cannot be avoided or forgotten in development. In fact, implementation is a configuration issue and should be done by the database’s Administrator.

container scoped encryption layers

The main thing to notice here is that I have stretched and skewed the building blocks to cover as many scenarios as possible in the diagram. With 3 blocks 9 dimensional states can be represented: state 1 being no encryption and at the other end state 9 – the darkest pinkest rectangle toward the center in the image above – being TDE of database pages into an encrypted file exposed as a VHD on top of an encrypted file system. In between are all the combinations visible as overlap of the blocks in the image.

Perhaps of interest, these container scoped layers are not available to a SQL Azure database.

In front of, or structurally within the database are several useful database object and column level cryptographic layers. These layers scramble data, verify that nothing has changed, protect intellectual property, protect personally identifiable information,  determine authenticity and extend the usability of data – primarily by creating HASHBYTES() or other checksum lookup surrogates. These layers are generally implemented by the application developer. They are also the most important layers in terms of the strength of data protection and therefore deserve consideration for integration at the the application layer. In almost all cases, if there is an option, cell value encryption at the application with key storage external to the database server will yield a more secure and scalable implementation (all other things being equal).  

cell scoped encryption layers

With the exception of HASHBYTES(), SQL Server cryptographic functions are not available in SQL Azure. Certainly designs that rely upon equivalent client side methods are able to store encrypted values in a properly typed column in SQL Azure, but good to keep in mind that the server-side approach to protecting data in SQL Azure inadequate at this time.

There are an undetermined number of ways to successfully implement cell or column encryption. To my way of thinking this is good. If everyone does it a bit different, all the more difficult to get past for the malicious attacker: a little obscurity actually can improve a good layered defense but cannot be a replacement. There are a wide assortment of cell and column encryption schemes in the introductory Encryption Hierarchy Administration template post and even more in the hardened Encryption Hierarchy Administration 2.0 template.

In this post the goal is to create a visual that shows the possibilities. For that purpose I overlay the three explained images to arrive at that diagram that started this post. The difference is that now the many ways that the cryptographic elements can be used together to build various depths of encryption should jump right out at you. Completely demystified as promised. Toward the center of the diagram are solutions where all encryption layers might be in play. Toward the periphery of the diagram, the layers fall away leaving only clear text and unprotected storage. Performance and usability are examples of the hard to control centrifugal forces pushing application designs to the periphery, while security, authenticity and compliance are core drivers to concentrate applications toward the center.  The size, shape and location of the encryption dimensions allow a two dimensional view of the architectural possibilities. 

This is not a diagram of a dynamic and configurable run time environment mind you. Instead, this is a visual aid to explore adequately robust cryptographic solutions when designing and developing a data application with encryption requirements. The divisions and overlaps in the diagram indicate what is possible to create in all the combinations and permutations not what must be created in all scenarios. The application defines the requirements not the encryption technology. This is a tool to help define the environment needed to support the requirements.

Spend a moment contemplating the diagram now that you have some background of the diagrams structure and purpose. I expect that you will find your mind exploring some highly secure possibilities. Better yet, the next time you are working on a project with high data protection requirements take a few minutes to contemplate the diagram at that time in the context of your requirements. I am equally confident the diagram can help identify strong defense-in-depth strategies quickly and with ease when used for that purpose. 

Repel Dictionary Attacks With Checksum Truncation of The Random lambda

One brute force attack is an all out assault that tries every possibility to guess the content of a cell. One brute force target is a ‘word’ stored in a hashed cell. A cell in SQL Server cell-level encryption is generally taken as a column.  A dictionary attack is a digital commando raid: a smaller more coordinated attack; and very likely to be more effective than brute force.  Both can target hash values stored in SQL Server whether SQL Server did the hashing (HASHBYTES) or the application generated the values. The dictionary attacker assembles the most likely phrases or keys – the dictionary, then ‘joins’ the dictionary to the values to be hacked to identify the key or phrase used to generate the hash. The dictionaries can be built off-line and before-hand to create a swift and powerful attack. The more information exposed to the attacker a priori, the more precise the possible attack. The brute force attacker uses every possible key or phrase so is usually slower. 

Dictionary attacking seems to be a popular past-time judging by the number of freely downloadable dictionary attacking applications available. There does not appear to be reliable research to be certain of the prevalence of brute force and dictionary attacks against hashed values in SQL server. My sense is that there may be a disturbing number of attacks that go completely undetected and/or unreported. And as more applications necessarily wade into the cryptographic swamp, attacks will surely increase.

It is important to mention that without encryption attackers have a cake walk. Information is lost the instant the attacker makes that first connection. No need to build a dictionary or try even a single possibility of brute force. Poor data governance will reduce or eliminate the number of brute force and dictionary attacks on your systems!

A simple hash of a value is particularly vulnerable to a dictionary attack. A simple hash being one that simply applies the encryption algorithm to the value.

HASHBYTES( N'SHA1', @Value );

Salting a value before hashing helps to confound the dictionary attacker essentially by injecting nonsense into the value. If you can use it, the more salt the better. A salt of comparable size to the value being hashed is recommended.

HASHBYTES( N'SHA1', @Value + @Salt);

With salt, the dictionary attack is useless provided the attacker does not have access to the salt. The salt must be carefully protected. A brute force attack may still succeed though may take significantly longer to complete than without salt. Salt exposed as clear text in another column of the table or in another table or even hard coded creates vulnerability to the dictionary attack once discovered by the attacker.

This underscores the relentless high-stakes battle being waged between data application owners and hackers over hashing and encryption. If a reasonably skilled hacker is determined to crack your encryption, they can eventually do so. SQL Server is particularly vulnerable because the encryption keys are stored with the data. Good defense from dictionary and brute force attacks include:

• strong, well tested, multi-dimensional security (defense-in-depth)
• strongest encryption possible (security above performance)
• anonymous lookups – using salted HASHBYTES()  (decrypt only when needed)
• securely store and backup all salt – (store local and remote copies) 
• securely share public keys (never expose private keys)

• encrypt data at rest and all data backups (TDE, file system encryption) 
• backup encryption keys (store local and remote copies)

• securely backup all encryption passphrases (store local and remote copies) 
• rotate encryption keys and passphrases (force brute force attack restarts)

• encryption by combination of keys and passphrases (separation of responsibilities) 
• very strong passphrase strength

• stringent access approval requirements (separation of responsibilities)
• continuously test and update roll-back and roll-forward recovery plans

Dynamic key management can get messy and/or expensive real quick in recovery scenarios. However, every time you rotate in a new key most attacks reset to square one. The benefit of having a known good backup when you need it is priceless. The headache of sound key management is worth the benefit. Rotate keys faster than the secrets can be guessed and you should be good. How big that window is depends on the strength of the security, strength of the encryption algorithm, strength of the salt as well as the general level of interest in the data and the sophistication of the attacker. 

Hashing only part of the value is a common way to further explode the attacker’s dictionary. This approach includes some unknown part of the value – of length lambda – in the hash. The excluded part of the value in the hash – along with the salt – makes it that much more difficult for the attacker to assemble the dictionaries. 

HASHBYTES( N'SHA1'
         , LEFT( @Value
               , FLOOR( LEN( @Value ) / 2.0 )  + @Salt)

Hashing also increases the risk that two rows with different values will produce the same hash. It is a small possibility but that risk is always there with HASHBYTES() or any function that tries to uniquely identify a value using a type with a domain too narrow to uniquely identify all possible values. 15 years ago DATETIME columns with a 1/3 mille-second minimum resolution granularity were being used to uniquely identify rows with some success. Few would even consider a DATETIME as a candidate key today. In less than 15 years the same will be true for HASHBYTES.  It is not safe in T-SQL to presume a hash absolutely and uniquely identifies the value represented by the hashed value unless the hash is defined as a unique constraint or unique index of the table where the encrypted value is stored. When the uniqueness of the hash is enforced by DRI, it is also a good idea to always handle the duplicate key error. It is more likely that other columns must be evaluated to determine if a match on the hash is in fact a match on the value. Often – sometimes even intentionally – another column is available to resolve the query; others time – and again sometimes even intentionally – an encrypted value may need to be unencrypted to complete the join. Having to un-encrypt to fully identify the row can cause application pain if queries must decrypt large sets, but nicely reduces the vulnerability to attack. It is possible in almost all cases to first get the rows that match a specified hash value into a derived table and then to decrypt only the few resulting rows to find the one requested by a query that knows more about the value it wants than just the hash. The bigger the encrypted blobs, the more the encryption of a few resulting rows can become a significant effort for both process and database server. In this regard it is essential to attenuate the hash processing to the application. Probably a good idea to do a sanity check about the value of the hash for a large cell. In general, it is more likely that full-text query processing is needed than an exact match on a 1GB blob.

In light of this unmitigated potential for conflict a de-duplication external to the hashing operation is needed. Never treat a hash of a value as unique unless you put a constraint on the value that it must be unique in the set. I’m thinking, if the hash cannot be considered unique anyway and the logic must deal with possibility of a duplicate and the data I intend to encrypt is huge (< million rows of customer info is not huge) or under exceptionally heavy load (<5 short running concurrent batches per second is not a heavy load for my laptop), it seems reasonable to me to reduce the hash one step further and define a bucket of hashes that can be represented by the same BINARY_CHECKSUM of the hash. It is not necessary to explicitly put any values into the buckets. That will (may) happen by itself as the hashed value is persisted to the database. In most of my use cases the chance of a duplicate even at the integer level of narrowing is somewhere close to the chance of moon falling out of the sky. The important aspect to recognize is that the buckets do not necessarily represent a single hash just as the hash does not necessarily represent as single value; and then to assure that when duplicates are found in the bucket there is enough information in the query or schema to resolved which of the multiple matches is the one desired. Narrowing the type of the hash to an integer will result in an easier number to work with for the developer, a reduced execution context footprint and reduced read-ahead requirements.

The narrowing method used here also opens the door to randomly generating row identity values at the INT level – or why not SMALLINT or BIGINT or NUMERIC(7,5) or any primitive SQL type? – so that the sequence of creation is not revealed in the surrogate key.

Raul Garcia blogged a hash like Message Authentication Code (MAC) scheme that protected against dictionary attacks a while back. In Mr Garcia’s example the salt appended to the value is the hash of an encrypted NEWID(). As he points out, the encryption of the NEWID produces a non-deterministic value that is a much better random number generator than the SQL Server NEWID() or RAND() functions.  The statement he uses to compute a MAC is:

HASHBYTES( N'SHA1'
         , ENCRYPTBYKEY( KEY_GUID( N'ValueKey' )
                                 , CONVERT( VARBINARY(100) 
                                          , NEWID() ) ) )

The new GUID is cast to a VARBINARY because SQL Server encryption functions  do not have the UNIQUEIDENTIFIER in its vocabulary. The GUID cast as VARBINARY is encrypted to produce a nondeterministic and therefore random value. The encryption function returns a random VARBINARY(8000) that is subsequently hashed with the SHA1 algorithm to produce a VARBINARY(20) digest that represents the encrypted new GUID in a way that has no means to be reverse-engineered. The result is non-deterministic so may be interesting as an unordered identity column – but there remains always that chance of a duplicate hash value.

CRYPT_GEN_RANDOM can also be used but the name I would have to give the technique is not near as fun. Instead of “checksum truncation of the random lambda” we get “VARBINARY truncation of the random random.” My guess is this might not scale quite as well either due to the overhead of encryption functions versus hashing functions – but I have not test that, just guessing…

CAST( 
  CRYPT_GEN_RANDOM
       ( 4
       , ENCRYPTBYKEY( KEY_GUID('ValueKey')
                     , N'type align with NEWID cast' 
                     , 1
                     , CAST( NEWID() AS NCHAR(36) ) )
        ) AS INT );

Instead of substring truncation as described above I now propose in most scenarios to use a BINARY_CHECKSUM of the hash to capture an integer representation of the original GUID (Correct me if I am wrong but is not that still deterministic truncation akin SUBSTRING, but in a way that considers the complete underlying value to reduce the probability of any brute force guess being correct instead of throwing part of the values unique identity on the floor?). An index of 4 bytes INT values will fit on 1/10th the number of pages as would an index of the same number of rows with about 40 bytes per row in a VARBINARY(8000). Integers will get you to about the same place in terms of selectivity of the hash value with a significantly reduced total scan length requiring  less read-ahead and therefore less memory pressure. Data keyed on whole numbers  is endlessly easier to humanly work with than data keyed on a large binary value.

For the MAC that looks something like:

BINARY_CHECKSUM( HASHBYTES( N'SHA1' 
                          , ENCRYPTBYKEY( KEY_GUID( N'ValueKey' ) 
                                         , CONVERT( VARBINARY(100) 
                                                  , NEWID() ) ) ) )

And for a deterministic hashed matching value more like:

BINARY_CHECKSUM(        
        HASHBYTES( N'SHA1'
                 , LEFT( @Value
                       , FLOOR( LEN( @Value ) / 2.0 ) + @Salt) )

;where @SALT is a well protected static value, perhaps generated as a MAC akin to Raul Garcia’s original proposal?

The per row storage advantage of the INT returned by BINARY_CHECKSUM over the VARBINARY(20) returned by HASHBYTES using the ‘SHA1′ algorithm is tiny. But it will add-up in a table with millions and millions of rows. Plus, the overhead difference between the INT and the VARBINARY is significant in other ways. As already mentioned, indexes and therefore scan lengths will be somewhat shorter. Sorting will be slightly faster. Development and testing are also marginally easier with the INT type. I’m spending too much time here to be certain that any performance benefit of INT of VARBINARY is not exaggerated – if it even exists.      

It may also be controversial to claim CHECKSUM as a truncation method. CHECKSUM bucketing is a well established and accepted practice however the de facto truncation method at this time is based upon the left most (higher order) bits of the value.  The NIST publication SP-800-133, “Recommendation for Cryptographic Key Generation” seems to at least allow for some flexibility in truncation of the hash (I highlighted a few spots in red):

Let K be the directly-generated key or the seed to be used to generate a key. K is a bit string value of the following form:
K = U ⊕ V, (1)
where
• U and V are of the same length as K,
• U and V are independent of each other, and
• U is derived from the output of an approved Random Bit Generator (RBG)
within the key-generating module that is generating K; the output of the RBG may have been transformed by an approved post-processing method (see Section 5.2) to obtain U.

FIPS 198-1, “Keyed-hash Message Authentication Code (HMAC)” specification – the more recent NIST darling over the classic Cipher Block Chaining MAC (CBC-MAC) that SQL Server uses – has something to say about truncation as well:

A well-known practice with MACs is to truncate their outputs (i.e., the length of the MACs used is less than the length of the output of the HMAC function L). Applications of this standard may truncate the outputs of the HMAC. When truncation is used, the λ leftmost bits of the output of the HMAC function shall be used as the MAC. For information about the choice of λ and the security implications of using truncated outputs of the HMAC function, see SP 800-107

Note that in the HMAC specification, the bits to use are stated as an option-less directive! (“…shall be used.“) SP-800-107, “Recommendation for Applications Using Approved Hash Algorithms”  seems pretty clear that the standard truncation method for a hash is only a recommendation for the readers convenience. (Is this really how standards are born? One person writes down an example to try to be helpful. Later someone needs to borrow the captured helpful thought. e viola!  In the process of the borrow, the convenience morphs into an edict. Right or wrong, a standard is born.)

SP-800-107 says:

5  Cryptographic Hash Function Usage
5.1      Truncated Message Digest
Some applications may require a message digest that is shorter than the (full-length) message digest provided by an approved cryptographic hash function specified in [FIPS 180-3]. In such cases, it may be appropriate to use a subset of the bits produced by the cryptographic hash function as the (shortened) message digest.
For application interoperability, a standard method for truncating cryptographic hash function outputs (i.e., message digests) is provided strictly as a convenience for implementers and application developers. The proper use of a truncated message digest is an application-level issue.
Let the shortened message digest be called a truncated message digest, and let λ be its desired length in bits. A truncated message digest may be used if the following requirements are met:
1. If collision resistance is required, λ shall be at least twice the required collision resistance strength s (in bits) for the truncated message digest (i.e., λ ≥ 2s).
2. The length of the output block of the approved cryptographic hash function to be used shall be greater than λ (i.e., L >  λ).
3. The λ left-most bits of the full-length message digest shall be selected as the truncated message digest

In Raul Garcia’s MAC the lambda – the length of bits being hashed- is not exploited to forge the authentication code. It is based on a new GUID at its core, cast to VARBINARY(100) – somewhat arbitrarily using that type and size – because the UNIQUEIDENTIFIER type cannot be used in the hashing function.

In the NIST SP-800-133 sample method the left-most lambda bytes of the value the code authenticates are used. The protection from dictionary attacks is about as perfect as it gets in Mr. Garcia’s example, however the NIST method can be reproduced as needed in  hashed password authentication schemes. Raul’s method may be better for use in queries where the MAC’s value is always part of the question (e.g. DRI, joins and indexes). The NIST recommended method is the only option of the two that could correctly be used in a uniqueness test that reproduces the hash and searches for a match in the data set – or at least a pre-test to get it down to one bucket of MACs that must be cracked open to finally determine the desired row.

In all cases, the only way to assure that a hash or hash-bucket collision in T-SQL does not crash or corrupt the application it to code for collisions. The duplicate hash value collision  is a fundamental keyed set dilemma comparable to the old school design mistake of using a DATETIME as a unique key. When a data type cannot describe all the unique values that it are assigned in the set that type can be described as narrowing. Hashed values are narrowing columns for clear text values, buckets are narrowing columns for hashes. A collision occurs when a duplicate value is actually placed in a narrowing column. When a narrowing column is a candidate key an additional piece or two of uniquely identifiable information must be included to resolve the collision. The same hash value must be usable to describe two or more unique values or the application must be open to spurious duplicate key errors. Specifically, a table with a narrowing column must include additional columns in any unique index or key to assure uniqueness. A narrowing column requires query and indexing that do not presume uniqueness of hash or bucket columns along with DRI that can correctly disambiguate a collision.

Whether and how a collision resolver deals with duplicates at the VARBINARY(20) bucket size or the INT (4 bytes) bucket size is not meaningfully different. The beauty of using BINARY_CHECKSUM  as a truncation method is that instead of using a fixed subset of bytes from the value as the original value, a subset of each byte is used. With the NIST substring method, the hacker can still perpetrate a dictionary attack on the hash, but is left with the task of mapping the part of the original value not reached by the lambda. That could take a while. With Raul’s random method, a dictionary attack using his MAC is not possible. The only relationship they have is that the MAC was created because the value already existed.

Using CHECKSUM instead of BINARY_CHECKSUM will reduce the number of buckets as will removing the sign (ABS()).

Interestingly, RSA defines MAC as synonymous with checksum: http://www.rsa.com/rsalabs/node.asp?id=2177

Until convinced otherwise, I plan to make use of checksum truncation for bucketing of lookups in my encrypted data where I can.

Using Authenticity, Signing, Nesting in T-SQL to Protect Sensitive Data from (even) Highly Privileged Users

Digital Non-Repudiation is about distributed communication authenticity. Non-repudiation in the digital security context is a proactive design concept intended to simulate the authentication of a hand written authorization. Evidence that this is a hard problem is found in the still widespread requirements for notarized hand written signatures in the year 2011.

Non-repudiation as a data layer design element has been widely rejected as unnecessary and invalid because the database is implicitly a trusted internal resource: why would anyone want to verify their own signature and what real use could it serve? Most argue that protecting the endpoint is protecting the data layer. While there is some short term profitability in that position,  the breakdown in data layer authenticity is rife with nightmare stories. It would be silly to ignore the risk just because non-repudiation alone does not solve it yet that seems to be the status quo.

Elevation of authority attacks are wide spread. Furthermore, the sophistication of attack as well as identified vectors of attack are expanding.

One major authenticity risk area is forgery or fakery of referenced objects.   Even in a change controlled SQL Server database, objects and configuration can be modified in a ways that are not reflected in the current version in the source control system. It is common for shops that develop T-SQL to require periodic re-synchronization between the “current” source control “version” and the actual objects on a SQL Server. It is essential to eliminate this drift. No matter how astute the data access monitoring tools (DAM), if you do not have the T-SQL source truly under control you don’t have much in the way of authenticity.

Modes of Authenticity

To combat forgery and fakery a method to determine the authenticity of the other ‘players’ in a given communication. Players in a database communication could include client applications, users, SQL Server instances, databases, tables,stored procedures, functions, triggers, queues, etc. Unless your DAM can do this you are not doing enough. Consider this table that lists several ways that are used to determine when a communication is authentic:

authority	authenticator	weakness	strength
verbal	trust	deniable	convenient
signature	trust	deniable	durable
signed in triplicate	signed copy	deniable signature	durable
signed and notarized	Public Notary	inconvenient	+ trusted authenticator
network communication	trust	deniable	convenient
signed network communication	trust broker	posers & rogues	non-repudiation
data access	SQL principal hierarchy	posers & rogues	KERBEROS
DML objects	SQL catalog	posers & rogues	principal of least privilege
signed DML objects	SQL encryption hierarchy	rogues	schema authenticity

Whether schema authenticity at the data layer is non-repudiation at the network layer is unimportant provided there is some acceptance for using the strengths and weaknesses listed in identifying relative value of authenticity in various architectural styles of communication and at various structural layers to the degree that looking at non-repudiation helps provide a stronger conceptual basis to establish adequate strong authenticity at the data layer. 

Given:

that a T-SQL attacker that obtains an ability to connect with elevated sysadmin or dbo credentials is unstoppable from carrying out their will

and,

that users with dbo database credentials – including the sysadmin server role members that are automatically made dbo when they enter any database, have implicit permissions to change all database configuration and object definitions in ways that will alter results returned to the application yet go undetected by the application or by internal database signature validation of the objects directly involved in legitimate database operations

and,

that it is always a plausible scenario that a currently trusted user with elevated authority can unexpectedly become an attacker using sysadmin or dbo authority

  then,

signature based non-repudiated protocols alone cannot assure the authenticity of the simplest of database operations. 

In non-repudiation, about all that is required is common access of the two parties to a public/private key pair (CREATE CERTIFICATE) to certify the communication. The objective of non-repudiation is mostly to assure that everyone gets the message at a pragmatic level. Non-repudiation doesn’t care what the two parties do after the communication or if the information exchange was useful; only that both parties are who they say they are and perhaps the content of – or altered state resulting from – the communication.

“Non-repudiation protocols are designed for verifying that, when two parties exchange information over a network, neither one nor the other can deny having participated to this communication.”  (Klay & Vigneron)

Obviously, the ‘parties’ in a database operation that runs completely within the SQL Server’s memory space do not normally exchange information over a network; but they can if someone wants that functionality — or injects that functionality. Likewise, there should be no argument that the ‘two parties’ in a database operation are really two software components of a common host most of time and in fact are more likely to be three or more components than the always dyadic constraint of communication between so called network endpoint . The party can grow quite large when you start looking at tall the database objects that are involved – and so can be manipulated maliciously – in a database operation. Limiting the communication at the database to two parties is beyond unrealistic. This goes to the very important to a point I wish to clearly establish above all else: a malicious user with elevated authority in the database can easily modify the unprotected database in ways that can significantly alter the database and/or the data returned.

At the sports book where I did a little DBA work one clever syndicate (organized group of thieves and crooks) figured out how to flip their bets to the winner on the over and under at just the moment between the end of a game and when the business layer automation could settled the wagers for the event. As far as I know it was never possible to say they were actually caught… It is nothing short of astonishing that this could have been done without insider assistance. If you saw the complexity of what they had done to manipulate wagers and the thoroughness used to cover the tracks you would likely agree. Yet “it was outsiders” seems to be the conclusion they (the management team mostly but Interpol and even Scotland Yard were involved somehow) reached in the investigation. This all even before the crooks started to get an idea anyone was on to them, but nabbing international attackers is not such an easy thing to do; to some (large?) extent due to corruption. Another part of this particular scam involved playing poker with the dishonest winnings against another syndicate member and losing intentionally to launder the ill-gotten winnings from the tainted account where the bit had been flipped into an account not directly tied to the hack. (Probably a good table to sitting at for the honest gambler too!) I was never really in on the skinny but did hear a few of the details: investigators somehow decided that there was no insider involved. I even saw a drivers license photo of the top guy in the syndicate from the other side of the world. I also finally got the permission I had been so far hopelessly seeking to reduce the permissions on some of the legacy application level SQL Logins. Still could not get them to budge on switching to Windows Authentication because they could not get their act together to move away from the 1 ginormous database connected directly to scads of web servers in the DMZ architecture. No idea how it ended with the attackers.  It was the sort of thing that went on continuously in that industry but these guys were a little more insidious and a little more successful than most.

I predict that the state of siege that the on-line sports book has been under for the last 10 years is a precursor to what will be common in the near future.  

It is the rare application that is able to detect let alone respond to the morphing or injection of database objects and the resulting bogus operations at run time. Pretty sure that statement can be applied to all flavors of SQL. For T-SQL application DML to be aware of the integrity of the data layer I know to be both quite possible and almost never done. The accepted practice instead is an existential presumption of authenticity of referenced database objects. (e.g. tables, stored procedures, functions, triggers, views, permissions, users, roles, certificates, etc.)? For the most part, this presumption relies upon the intrinsic hierarchical role based security model and absolute confidence in the integrity of the system(s). This is most realistic when only a trusted few are able to access the database from a role at the top of the hierarchy and at the lower rungs of the hierarchy the power to make change is highly restricted or revoked.

I’m thinking that is backward. It is obviously necessary to vigilantly assure that all relevant schema objects and configuration settings have not changed. Especially when changed in such a way that the result of database operation are altered? Only when there is certainty about the integrity all relevant schema objects would it be possible to certify the authenticity of the database operation. I need an example to even feel like I am explaining my premise adequately. 

A Facetious, Fictitious, Farcical Far Too Likely to be True Example

Consider this schema that inserts a Boolean value using a stored procedure. There is no other functionality. Period.

create table dbo.tally 
  ( id int identity(1,1) PRIMARY KEY
  , user_choice BIT ); 
go
create proc dbo.submit_choice 
  ( @choice BIT ) 
as 
  insert dbo.tally (user_choice) 
  values (@choice);
go

Let’s say this is a temporary polling station set up in a location restricted to only employees yet openly accessible to all employees of a business. The poll  promises an anonymous result yet with a stated request (rule of trust) that everyone only vote once and assurance of an anonymous result: there is no way to tell who placed any vote. The kitchen user’s are asked if they would like to discontinue the currently catered snacks and lunches in the kitchen in response to a widespread outcry after a recent outbreak of food born illness among workers when the potato salad was left out over the weekend. A polling station is a laptop placed on the end of the kitchen counter by the toaster with text instructions for the one question survey loaded into Notepad telling kitchen users how to open the SSMS query tool, connect to the correct database and place a vote by executing a stored procedure from an SSMS query connection. Examples of the exact syntax for yes and no that can be copied from notepad and pasted to SSMS are included as is a request for “each user to PLEASE! vote only once” and the query that will be used to determine the result. Not a second thought is given to security or user interface due to the high confidence that only trusted users could access the location.

-- YES - paste this to query window and execute to vote yes
exec dbo.submit_choice 1;
-- NO - paste this to query window and execute to vote no
exec dbo.submit_choice 0;
-- this query will determine the winner
select TOP(1) user_choice as [winner]
     , count(*) AS votes
from dbo.tally 
group by user_choice
order by votes desc; 

Drop in an few votes an see how things look:

exec dbo.submit_choice 0;
exec dbo.submit_choice 0;
exec dbo.submit_choice 1;
exec dbo.submit_choice 0;
select TOP(1) user_choice as [winner]
     , count(*) AS votes
from dbo.tally 
group by user_choice
order by votes desc;

The result is

winner votes
------ -----------
0      3

That looks right to me.

What no one could have known is that a militant raw vegan PETA loony colleague – a web developer with adequate SQL skills and exceptional attitude that actually did not use the kitchen much because of her now 4 year running raw vegan ‘fad’ diet – would be one of the first to enter the kitchen after the voting begins. This person’s naked agenda is the abolition of meat and meat by products from the human diet by all non-violent means possible. She pops open the connection to the database as described in the note and needs only a few seconds to understand the schema and the stored procedure, a moment to think and a few keystrokes to add a trigger that would assure the outcome yet risk minimal additional scrutiny that might be caused by a completely outlandish results.

create trigger dbo.MSIndexMaintenance
on dbo.tally 
with encryption 
instead of insert   
as
set nocount on;
insert dbo.tally (user_choice) 
select case when ( t.now < .55 ) 
            then 1 else i.user_choice end
from inserted as i
cross join 
  (select sum(cast(user_choice as float))/count(*) as now
   from dbo.tally ) as t;

after running the exact same set of inserts again (remember the other 4 votes are already there)

exec dbo.submit_choice 0;
exec dbo.submit_choice 0;
exec dbo.submit_choice 1;
exec dbo.submit_choice 0;
select TOP(1) user_choice as [winner]
     , count(*) AS votes
from dbo.tally 
group by user_choice
order by votes desc;

There are now 8 rows in the table. Two (2) rows should hold the value zero (0). The other six (6) should be 1. Right? The result from the summary query is now:

winner votes
------ -----------
1      4

Oops…

This may explain why so many corporate kitchens have gone vegan! What’s worse (better?), signature checking of the compiled objects to establish a non-repudiated communication would not have helped the bottom line of a single feedlot or hot dog factory. The signature of a SQL Server table is unaffected by the addition, modification or removal of a trigger or a permission placed on that table.

Adding a trigger is not the only hi-jinx possible. A Synonym pointing to a Linked Server location that replaces the table is another possibility for ‘stealing’ votes. Or how about simply adding the trigger logic to the stored procedure? Other possibilities may occur to the reader. Many exist.

The example shows the ease and stealth with which the T-SQL outcome can be manipulated. The result is made invalid and any claim of authenticity left looking foolish by the facts after a hack if steps are not taken to assure schema authenticity. This hole in the signature seems like the utility company allowing you an electrical outlet that does not go through the meter (guess where I plug everything in). One thing is for sure: the hackers will continue to bang away at Microsoft implementations in search of vulnerabilities as long as Windows is a leader in the server space. And if there is a problem it will continue to become known before too many people get stomped on. I can only hope it does not become known with my data… Notice I mentioned only Windows and not SQL Server. That is because an elevation of authority attack will most likely occur before the stolen highly privileged user credentials are use to gain database access. The only other real possibilities are an insider attack or SQL Injection combined with patience. 

Of course the base farcical ‘mistake’ in the example application is reliance upon the honesty of people. Almost every application makes this mistake of foolish trust to one degree or another, it is admittedly presented as self portrait in caricature in the example. In computer applications trust is used as an acceptable calculate risk in the interest of efficiency and supportability. On the Internet, even that friend, loved one, colleague or boss can be convincingly spoofed. The only rule you need to stay digitally safe, especially when the Internet is in the picture is, “trust no-one”. Most of us step over that boundary far to often and don’t even realize it. We forget that computers are not people.

And there are many other errors in design in the made up example. Some even intentional. For example, the presumption that the data is safe because it is in a secure physical location is not only fallacious but proved to be an invalid assumption based solely on hope. Speculative design is not likely to produce authentic results.

The status quo is that database operations do no need to meet the test of non-repudiation. OK, fine. What I cannot agree with though is the unspoken consequential decision to simply not worry about authenticity at the data layer. It makes my head spin to think too long about all the ways the integrity of a database operation can be maliciously corrupted or compromised. Although non-repudiation sets a pretty low bar for authenticity because it presumes that communication is dyadic and because it has no interest in the process, only the participants.

Simplistic shared secret verification a la non-repudiation is inadequate to assure communication authenticity in SQL Server. Many changes to an object’s component attributes (indexes, triggers, permissions, etc.) and dependencies (called modules and referenced tables) are not encapsulated by an object’s signature when signed using T-SQLs ADD SIGNATURE statement. For example, object signing alone on a table does not see any CREATE/ALTER/DROP of triggers or indexes on the table or any GRANT/DENY/REVOKE permission applied to that table. Likewise changing the table in a way that does invalidate the signature would do nothing to the T-SQL signature of a stored procedure that references the table or a trigger attached to the table.

Toward Authenticity at the Data Layer

Actions to gain confidence in the authenticity of database operation include:

• Control Change. Allow only schedule, reviewed and well tested changes. Be unequivocal about this. Shoot for only planned change too, but be willing to schedule the occasional one-off as long as it meets the established review and testing standards. Be willing to adjust the standard to improve results.
• Limit change. Limit the number of individuals that can apply changes to the environment and limit the the number of changes any individual can make to those needed to do what they do. Always align the permissions of the database user with the access required by the user as precisely as practical (principle of least privilege).  
• Digitally sign all objects in the schema at creation. Use a carefully guarded key and then ‘continuously’ verify that the signature of self and all legitimately referenced objects are in tact at run-time. The private key of a certificate can be removed from the SQL Server when not needed. Object signatures can be verified using T-SQLs IS_OBJECTSIGNED() function without the private key yet a signature cannot be reapplied until the private key is restored.  Consider a password signed certificate with a very strong password when security requirements granularity for the key is not aligned with the role/principal hierarchy.   
• Silently monitor for changes. An asynchronous process that captures all change is essential for monitoring and forensics. One method is to use EVENT NOTIFICATIONS that include all DDL_DATABASE_LEVEL_EVENTS. Log and protect the log from unauthorized access. Maintain high confidence that not only the message but also the message delivery system is not compromised. Any process able to leverage dbo or sysadmin authority can compromise any SQL Server change control system; including event notifications.  
• Proactively prevent unauthorized change.  The intersections of authority for a role based provisioning model and the permissions based access model can create threat holes. Some users end up with more access than they need. The most likely suspect is when an group from the domain or tree is plopped into a role in SQL Server. Most never know it. The easiest way to become convinced is to try to plug that hole. A DDL trigger that rolls back all DDL_DATABASE_LEVEL_EVENTS. stop all change by all users. And it does except for one change for any user with ALTER ANY DATABASE DDL TRIGGER permission can make: DROP TRIGGER on that DDL trigger. ALTER ANY DATABASE DDL TRIGGER   If you add another identical triggers it is not possible to drop either trigger without first disabling the other trigger. That is not very much defense since the user that can ALTER a trigger can also disable the trigger and there is no event available to the trigger that can detect the disable trigger event. SQL Trace can see DISABLE TRIGGER events. An alert from a custom trace event watching for the text “DISABLE TRIGGER” could be configured to protect against DISABLE TRIGGER. This actually could be effective in restricting a dbo authorized attacker in some scenarios but would be just another trivial obstacle for a sysadmin authorized attacker. 
• Encrypt Sensitive Data. Use SQL Server encryption hierarchy(ies) that require strong password(s) or encryption based on keys not available to other database users. It is recommended that secure on-site and off-site backups of all keys be maintained at all times. Restrict key management to a very small group of trusted users. 
• Verify schema integrity at run time. The most straightforward verification would be to compare the object tree in the schema to a hardcoded static list encapsulated in an encrypted executable object. The object cannot be changed by other users without the loss of the signature as the object is changed. Other users cannot reapply the well protected signature thus reducing the check for change to an inexpensive IF EXISTS  on the catalog view of encrypted objects filtered by the key/cert of your signature. In other scenarios it my provide more confidence to include signed data value validation between executable database objects a la non-repudiation – especially for critical operations. Complementary to object signing, there should be some assurance that the object signature or key used are not compromised. Also the verification must include all reference objects – expected or unexpected. Consider signing a parameter or introducing signatures based on a different well protected key than the one used to sign the objects between called database objects to build greater confidence in the objects authenticity. The key of the data signature gives you an additional common shared local resource that is expected to be difficult to compromise for cross-validation of the objects’ authenticity.

• Use system encryption for all objects (WITH ENCRYPTION). This helps prevent the uninvited from following along and provides the added benefit of protecting strong and long secrets that can be compiled into encrypted objects without risk of exposure as clear text. The combination of DML object Encryption and SQL Trace’s built-in encryption statement masking are key to effective run-time secret protection at this time. (Windows Debugger attackers may still be able to steal secrets from buffers where they might be placed in clear text. It should be possible to make sure live systems do not have the necessary debugger symbols.) As a DBA there is nothing more frustrating than trying to support a 3rd party database with encrypted objects. I concur and will do what I can to avoid that thankless task. Encrypting yourself is not so painful because you can unencrypt in development to debug. Wipe that unencrypted database clean when done debugging and protect it extra carefully while it is visible. 
• Disable database-chaining and trustworthy database options. This will reduce the exposure to cross database elevation of authority attacks.

• Don’t help hackers with error details. Watch the @@NESTLEVEL value in CATCH block and always use try-catch error handling. When @@NESTLEVEL = 0 then the error is on the command line and should not be displayed. WHEN the NESTLEVEL = 1 then the caller is a command line and any error information should not be returned to the command line call. When @@NESTLEVEL > 1 then return just enough information to the caller for locating the logged error. 

This is a listing of the minimum necessary to be confident in the authenticity at the data layer.  Without meeting all of these measure in combination, database data is not safe from posers ( including elevation of authority attackers, brute force password attackers, SQL Injection attackers, etc.) – and the rogue insiders. There is more that could be done. For example in highly secured settings it may be worthwhile to sign object interactions by using a technique that looks a lot like non-repudiation.  Eventually even strong security must succumb to the law of diminishing returns and the level of perceived risk. I think it sufficient to acknowledge that perfect security is not attainable but must remain always the objective. 

A revised application with much higher assurance of object authenticity based upon that insert into a bit column would look a bit different. The example revision explodes the original applications 10 lines script to create the database objects  to over 200 lines of T-SQL. 

:SETVAR SIGNATURE_PASSWORD "a_$1LLY-n-str0nG_fraze"
CREATE DATABASE test;
ALTER DATABASE test SET TRUSTWORTHY OFF;
ALTER DATABASE test SET DB_CHAINING OFF;
GO 
USE test;
-- silent monitor
CREATE QUEUE DDLChangesQueue WITH RETENTION = ON;
CREATE SERVICE DDLChangesService
ON QUEUE DDLChangesQueue 
  ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] );
CREATE EVENT NOTIFICATION DDLChanges 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
TO SERVICE 'DDLChangesService', 'current database' ;
GO
ALTER AUTHORIZATION ON DATABASE::test TO sa;
GO
CREATE ROLE testAdministrator;
-- add current user to role
DECLARE @CreateUserDDL NVARCHAR(512);
SET @CreateUserDDL = N'CREATE USER [' + ORIGINAL_LOGIN() 
                   + N']; EXEC sp_addrolemember ''testAdministrator'', ''' 
                   + ORIGINAL_LOGIN() + N'''' ;
EXEC sp_executesql @CreateUserDDL;
GO
-- cert encrypted by DMK
CREATE CERTIFICATE SigCert 
ENCRYPTION BY PASSWORD = '$(SIGNATURE_PASSWORD)'
WITH SUBJECT = 'object signatures' ;
GO
CREATE TABLE dbo.tally 
  ( Id UNIQUEIDENTIFIER NOT NULL -- int identity(1,1)
  , User_choice BIT ); 
GO
ADD SIGNATURE TO dbo.tally
BY CERTIFICATE SigCert
WITH PASSWORD = '$(SIGNATURE_PASSWORD)';
GO
-- does not affect signature of table
CREATE CLUSTERED INDEX ixc_tally ON dbo.tally(user_choice);
GO
CREATE PROC dbo.submit_choice 
  ( @choice BIT ) 
AS 
BEGIN
  BEGIN TRY
    IF (SELECT IS_OBJECTSIGNED('OBJECT' 
                              , @@PROCID
                              , 'CERTIFICATE'
                              , thumbprint )  
             + IS_OBJECTSIGNED('OBJECT'
                              , OBJECT_ID( 'dbo.tally', 'U' ) 
                              , 'CERTIFICATE'
                              , thumbprint ) 
             + IS_OBJECTSIGNED('OBJECT'
                              , OBJECT_ID( 'dbo.trg_tally', 'TR' ) 
                              , 'CERTIFICATE'
                              , thumbprint )
             +  ( SELECT COUNT(*) AS [NbrTriggers] 
                  FROM sys.triggers
                  WHERE parent_id = OBJECT_ID( 'dbo.tally', 'U' )
                  AND object_id <> OBJECT_ID( 'dbo.trg_tally', 'TR' ) )                
        FROM sys.certificates
        WHERE name = 'SigCert'
        AND object_name(@@PROCID) = 'submit_choice' ) <> 3
      RAISERROR('unable to verify object signatures',16,1);  
    INSERT dbo.tally (user_choice) 
    VALUES (@choice);
  END TRY
  BEGIN CATCH
    SELECT  ERROR_NUMBER() AS ErrorNumber
          , ERROR_SEVERITY() AS ErrorSeverity
          , ERROR_STATE() AS ErrorState
          , ERROR_PROCEDURE() AS ErrorProcedure
          , ERROR_LINE() AS ErrorLine
          , ERROR_MESSAGE() AS ErrorMessage;
  END CATCH
END
GO
ADD SIGNATURE to dbo.submit_choice
BY CERTIFICATE SigCert
WITH PASSWORD = '$(SIGNATURE_PASSWORD)';
GO
CREATE PROC dbo.get_results 
AS 
  SELECT user_choice as choice
        , rank() OVER 
          (ORDER BY COUNT(*) DESC) AS rank
        , count(*) AS votes
  FROM dbo.tally
  GROUP BY user_choice
  ORDER BY rank DESC;
GO
ADD SIGNATURE to dbo.get_results
BY CERTIFICATE SigCert
WITH PASSWORD = '$(SIGNATURE_PASSWORD)';
GO
-- instead trigger fires BEFORE constraints 
-- constraints are applied before after triggers
CREATE TRIGGER dbo.trg_tally
ON dbo.tally 
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
  BEGIN TRY
    -- only inserts, only 1 row at a time
    IF (SELECT COUNT(*) FROM inserted) > 1
      RAISERROR('no multi-row', 16, 1); 
    IF (SELECT COUNT(*) FROM deleted ) > 0
      IF (SELECT COUNT(*) FROM inserted) = 1
        RAISERROR('no update', 16, 1); 
      ELSE  
        RAISERROR('no delete', 16, 1);       
    INSERT dbo.tally  (id, user_choice)
    SELECT NEWID(), user_choice FROM inserted;
  END TRY
  BEGIN CATCH
    SELECT  ERROR_NUMBER() AS ErrorNumber
          , ERROR_SEVERITY() AS ErrorSeverity
          , ERROR_STATE() AS ErrorState
          , ERROR_PROCEDURE() AS ErrorProcedure
          , ERROR_LINE() AS ErrorLine
          , ERROR_MESSAGE() AS ErrorMessage;
  END CATCH
END
GO
ADD SIGNATURE to dbo.trg_tally
BY CERTIFICATE SigCert
WITH PASSWORD = '$(SIGNATURE_PASSWORD)';
GO
CREATE TRIGGER trg_ddlChanges1
ON DATABASE 
WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
BEGIN
DECLARE @User NVARCHAR(128); 
  BEGIN TRY
    SET @User = USER_NAME();
    -- error if user making the change is not DataAdministrator
    -- even though IS_MEMBER('DataAdministrator') would be false
    IF NOT EXISTS 
      ( SELECT *
        FROM (SELECT ddl.event.value('LoginName[1]'
                                 , 'NVARCHAR(128)') AS LoginName
              FROM 
                (SELECT EVENTDATA() AS change) this
              CROSS APPLY 
                change.nodes('/EVENT_INSTANCE') AS ddl(event) ) q
        JOIN sys.database_role_members r
        ON q.LoginName = USER_NAME(r.member_principal_id)
        WHERE r.role_principal_id = USER_ID('DataAdministrator') )
    AND @User <> 'cdc'
      RAISERROR('Invalid User %s',16,1,@User) WITH LOG; 
  END TRY
  BEGIN CATCH
    ROLLBACK;
    RAISERROR('fatal error',20,1, @User) WITH LOG;
  END CATCH
END
GO
DISABLE TRIGGER ALL ON DATABASE
GO
CREATE TRIGGER trg_ddlChanges2
ON DATABASE 
WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
BEGIN
DECLARE @User NVARCHAR(128); 
  BEGIN TRY
    SET @User = USER_NAME();
    -- error if user making the change is not DataAdministrator
    -- even though IS_MEMBER('DataAdministrator') would be false
    IF NOT EXISTS 
      ( SELECT *
        FROM (SELECT ddl.event.value('LoginName[1]'
                                 , 'NVARCHAR(128)') AS LoginName
              FROM 
                (SELECT EVENTDATA() AS change) this
              CROSS APPLY 
                change.nodes('/EVENT_INSTANCE') AS ddl(event) ) q
        JOIN sys.database_role_members r
        ON q.LoginName = USER_NAME(r.member_principal_id)
        WHERE r.role_principal_id = USER_ID('DataAdministrator') )
    AND @User <> 'cdc'
      RAISERROR('Invalid User %s',16,1,@User) WITH LOG; 
  END TRY
  BEGIN CATCH
    ROLLBACK;
    RAISERROR('fatal error',20,1, @User) WITH LOG;
  END CATCH
END
GO
GO
ENABLE TRIGGER ALL ON DATABASE
GO
SELECT O.NAME
     , ISNULL(IS_OBJECTSIGNED( 'OBJECT'
                             , o.object_id
                             , 'CERTIFICATE'
                             , c.thumbprint ), 0) as [IsSigned]
     , c.name
FROM sys.objects o
LEFT JOIN sys.crypt_properties cp
ON o.object_id = cp.major_id
LEFT JOIN sys.triggers t
ON o.object_id = t.object_id
LEFT JOIN sys.certificates c
on cp.thumbprint = cp.thumbprint
WHERE o.is_ms_shipped = 0
GO

 

The best prevention for unauthorized sysadmin level access to the database is to keep anyone and anything that should not be doing admin level work from connecting with admin level credentials. Limiting the number of sysadmins is also nice, but the reality is pretty much anyone that can set another users password or access the local system running SQL Server as a local Windows Administrator can find a way to connect to a SQL Server with sysadmin access. Once a connection to a SQL Server is established using valid credentials, there are no permissions, privileges, rights or roles that cannot be overridden by that sysadmin empowered connection. At this time only the protection from compromise of sensitive data by unauthorized sysadmin members looks possible. Any sysadmin can delete complete data rows, drop tables and even drop databases or corrupt the data: no matter what is done to prevent data compromise. It is only possible to prevent the sysadmin from knowing the meaning of encrypted data. A mildly determined malicious user with sysadmin authority and the intent to destroy data cannot be stopped from that goal. They can be delayed and – in most cases – identified after the fact. Delaying features are also useful to help show intent during Forensic . The best protections against the malicious insider are to hire trustworthy employees and  to collect adequate forensic data into user protected containers. Ideally, an audit trail that cannot be modified by a sysadmin privileged user is needed.

The information protection of encryption/signing is durable provided the private key is safe. Encryption scrambles data with a public key that can later be unscrambled with a private key. In contrast, signing scrambles data with the private key of a key pair and later unscrambles the signature with the public key to verify the data. In either case, the encryption keys – especially the private key – must be  safeguarded.

The simple act of successfully sharing a well protected key to sign and verify a signature is often considered adequate to establish non-repudiation. A weakness in this approach is the ease with which T-SQL can be manipulated without violating the signature. It is also necessary to verify that all other relevant schema elements continue to sport their signatures, that unsigned objects have not been introduces, and that none of the expected signed objects are missing.

A digital object that is expected to check its own signature is fatally flawed. This would be like airport security using the ‘honor system’ or nuclear power regulators deferring to the plant operators to establish safety levels or airline pilots left to judge their own soberness before flight.  As we now know – self-policing strategies are effective in well over 99% of cases. And very cost effective… until there is an event that falls into the range of that other way less than 1 percent. A significantly more reliable and consistent outcome results when the signature is always verified by a trusty outsider. (Ultimately this is the reason that outside accounting firms are needed to count the votes in an awards show, there are observers at ballot locations, “fact checking” has become a political pastime and neighbors are nosey: simply to bear witness to the obvious and expose – or get mysteriously wealthy – over occasional anomalies.) 

A higher assurance that any entity is what it claims it is is will come from a reliable source external and independent from that entity than from that entity itself. This is why auditors are in great demand and the words “Fact Checking” garner such attention in the title of an article or news story.” This external check could be done – in our scenario – by:

1. introducing a trigger behind the table
2. via a called module – a stored procedure – placed between the external request and the table operation
3. as a check constraint or default value.

Also, to be able to claim the current process as the scope for verification of trust, the validity of each called module and referenced object needs to be verified before use and the signature of each calling module ought to be verified by the caller before returning control. That leaves defaults and constraints less useful since they may not occur with every process that uses the data.  

A called stored procedure or a trigger will do. A view cannot be signed.  It may be a little less confounding for the developer not to have to create two stored procedures to do one thing.  Using a trigger also allows a division of responsibility and prevents everyone from having to know every business rule to succeed. Perhaps the DBA takes ownership of the triggers or called procedure(s) that verify authenticity of the participants allowing the developers to continue to code mostly unaffected by and unaware of the signature trigger logic and other defensive layers enveloping the application logic. All in all, a trigger is probably a better way to impose corroboration than the addition of a second stored procedure IMHO, but not by much. One of the few scenarios when triggers are in fact appropriate: to assure the encryption complexity does not get in the way of progress!  On the other hand, if the database supports abstraction layers (ORM, OOP)  that will suffer serious side effects or degradation from a trigger an additional stored procedure or view introduced into this now obviously secondary secure application is mandated to assure the authenticity of the communication.

If you are looking for this level of assurance of authenticity through your ORM – fagetaboutit. Man-in-the-middle protection at the ORM data access layer is essential but must be done well outside the bounds of the data layer. You still want to do everything described to protect and monitor at the database server. In addition, it is necessary to apply similar protection at the ORM layer. 

FWIW: the original hack, tacking on an instead trigger no longer works due to the one instead trigger per action limitation. The legitimate trigger uses up the one allowed trigger on all three actions.

Encryption Hierarchy Administration – a T-SQL Template

This is a SQL Server template to standardize and track backup and  restore of SQL Server 2005/2008 Encryption Hierarchy (EH) Master Keys and Certificates. Unlike most templates, this one creates a complete utility with several interdependent database objects – including the database if it does not already exist. Any existing user database can be used as long as the consequences of executing a script from this template are understood and acceptable.

January 25, 2012 Update – A nicely hardened version of the Encryption Hierarchy Administrator is now available. I would still strongly recommend that you become familiar with the technologies demonstrated in the script posted here if you do not already administer an encryption hierarchy. This script demonstrates the fundamentals required for sound EH administration. The new version adds extensive cryptographic support that will be documented through a few blog posts.

https://github.com/bwunder/EHAdmin.

and now back to our regularly scheduled program…

keyback Administrator.sql

1. – Encryption Hierarchy Administration Template
2. – WARNING! script changes master database and creates a database if it does not already exist 
3. – run in SSMS SQLCMD mode or from a sqlcmd command line
4. – best practice is to use SSMS and NEVER save the script once the secrets have been entered.
5. – PASSPHRASE cannot contain any of these symbols or substrings # , . ; : _  ” ‘
6. – or the words GO DROP CREATE ALTER SELECT INSERT UPDATE DELETE GRANT DBO EXEC USE (regardless of case)
7. :setvar DB_NAME                             “<Db to create or use if exists,SYSNAME,keyback>”                                   
8. :setvar SMK_BACKUP_PHRASE                   “<Secret for Service Master Key backup file encryption,PASSPHRASE – NVARCHAR(128),>”
9. – if master already has a DMK you must provide the existing encryption passphrase – don’t make one up – it will corrupt keyback
10. :setvar master_DMK_ENCRYPTION_PHRASE        “<Secret for master Database Master Key encryption,PASSPHRASE – NVARCHAR(128),>”    
11. :setvar master_DMK_BACKUP_PHRASE            “<Secret for master Database Master Key backup file,PASSPHRASE – NVARCHAR(128),>”   
12. :setvar keyback_DMK_ENCRYPTION_PHRASE       “<Secret for keyback Database Master Key encryption,PASSPHRASE – NVARCHAR(128),>”   
13. :setvar keyback_DMK_BACKUP_PHRASE           “<Secret for keyback Database Master Key backup file,PASSPHRASE – NVARCHAR(128),>”  
14. :setvar TDE_CERTIFICATE_NAME                “<Name of cert in master for keyback db TDE Encryption Key ,SYSNAME,TDECertificate>”
15. :setvar TDE_CERTIFICATE_BACKUP_PHRASE       “<Secret for master db TDE Cert backup file encryption,PASSPHRASE – NVARCHAR(128),>”
16. :setvar VALUE_CERTIFICATE_NAME              “<Name of cert in keyback db used to encrypt column values,SYSNAME,ValueCertificate>”
17. :setvar VALUE_CERTIFICATE_ENCRYPTION_PHRASE “<Secret for keyback db value certificate encryption,PASSPHRASE – NVARCHAR(128),>”  
18. :setvar VALUE_CERTIFICATE_BACKUP_PHRASE     “<Secret for keyback db value certificate backup file,PASSPHRASE – NVARCHAR(128),>” 
19. – http://blogs.msdn.com/b/ace_team/archive/2007/09/07/aes-vs-3des-block-ciphers.aspx : use AES
20. :setvar DEK_ALGORITHM                       “<Database Encryption Key Algorithm,AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY,AES_256>”
21. :setvar COLUMN_ENCRYPTION_ALGORITHM         “<Symmetric Key Algorithm,AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY | Other (most already broken), AES_256>”  
22. :setvar DB_SYMMETRIC_KEY                    “<Symmetric key used for data value encryption,SYSNAME,ValueKey>”                   
23. :setvar ROLE_NAME                           “<Role -membership required to access NameValues,SYSNAME,keybackAdmin>”             
24. :setvar MASTER_KEY_BACKUP_EXT               “<file extension for key backups,FILE_EXTENSION,.keybak>”                           
25. :setvar PUBLIC_KEY_BACKUP_EXT               “<file extension for certificate backups,FILE_EXTENTION,.cerbak>”                   
26. :setvar PRIVATE_KEY_BACKUP_EXT              “<file extension for certificate private key backups,FILE_EXT,.prvbak>”             
27. :setvar SPARSE                              “<SPARSE XML error columns if 2008 otherwise blank,SPARSE | blank,SPARSE>”          
28. RAISERROR(‘Protect all Encryption Hierarchy backup files and maintain a copy in a secure offsite location.’,0,0);
29. GO
30. SET NOCOUNT ON;
31. USE [master];
32. GO
33. IF DB_ID(‘$(DB_NAME)’) IS NULL
34. CREATE DATABASE [$(DB_NAME)];
35. GO
36. USE $(DB_NAME);
37. GO
38. IF DB_NAME() <> ‘$(DB_NAME)’
39.   RAISERROR(‘Database $(DB_NAME) not found. Script aborted.’,20,1) WITH LOG;
40. GO
41. ALTER DATABASE [$(DB_NAME)] SET TRUSTWORTHY OFF;    
42. GO
43. ALTER DATABASE [$(DB_NAME)] SET DB_CHAINING OFF;
44. GO
45. ALTER DATABASE [$(DB_NAME)] SET RECOVERY FULL;  
46. GO
47. ALTER DATABASE [$(DB_NAME)] SET READ_COMMITTED_SNAPSHOT ON;
48. GO
49. – db_owner, dbcreator, sysadmin.
50. ALTER DATABASE [$(DB_NAME)] SET RESTRICTED_USER;
51. GO
52. USE [master];
53. – encryption hierarchy backups
54. EXEC sp_addmessage 2147483631,  1, ‘keyback info – database %s certificate %s %s complete.’, ‘us_english’,‘FALSE’ ,‘replace’
55. EXEC sp_addmessage 2147483632,  1, ‘keyback error – database %s certificate %s %s failed with return_code %d.’, ‘us_english’,‘FALSE’ ,‘replace’
56. EXEC sp_addmessage 2147483633,  1, ‘keyback info – database %s Master Key %s complete.’, ‘us_english’,‘FALSE’ ,‘replace’
57. EXEC sp_addmessage 2147483634,  1, ‘keyback error – database %s Master Key %s failed with return_code %d.’, ‘us_english’,‘FALSE’ ,‘replace’
58. EXEC sp_addmessage 2147483635,  1, ‘keyback info – Service Master Key backup complete.’, ‘us_english’,‘FALSE’ ,‘replace’
59. EXEC sp_addmessage 2147483636,  1, ‘keyback error – Service Master Key backup failed with return_code %d.’, ‘us_english’,‘FALSE’ ,‘replace’
60. – encrypted name value secrets
61. EXEC sp_addmessage 2147483641,  1, ‘namevalue info – %s complete.’, ‘us_english’,‘FALSE’ ,‘replace’
62. EXEC sp_addmessage 2147483642, 16, ‘namevalue error – %s failed with @return_code = %d.’, ‘us_english’,‘FALSE’ ,‘replace’
63. EXEC sp_addmessage 2147483643, 16, ‘namevalue error – Unable to verify save of secret “%s”.’, ‘us_english’,‘FALSE’ ,‘replace’
64. EXEC sp_addmessage 2147483644, 16, ‘namevalue error – Invalid “%s”.’, ‘us_english’,‘FALSE’ ,‘replace’
65. EXEC sp_addmessage 2147483645,  1, ‘namevalue info – the %s is valid.’, ‘us_english’,‘FALSE’ ,‘replace’
66. GO
67. – Certificate in master for TDE is encrypted by master Database Master Key
68. IF NOT EXISTS (SELECT * FROM [sys].[symmetric_keys] WHERE [symmetric_key_id] = 101)
69. CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘$(master_DMK_ENCRYPTION_PHRASE)’;
70. GO
71. –  private key of cert is autogenerated and encrypted by DMK
72. – if no private key specified and no Master Key exists this create will fail
73. IF NOT EXISTS (SELECT * FROM sys.certificates WHERE [name] = ‘$(TDE_CERTIFICATE_NAME)’)
74.   CREATE CERTIFICATE $(TDE_CERTIFICATE_NAME) WITH SUBJECT = ‘$(DB_NAME)_DatabaseEncryptionKey’;
75. GO
76. USE $(DB_NAME);
77. – move dbo to sa so no conflicts when current user added to role
78. – sa should also be disabled, and could also be renamed
79. – SQL authentication should also be disabled.
80. IF (SELECT owner_sid FROM sys.databases where name = DB_NAME()) <> 0×01
81. EXEC sp_changedbowner ‘sa’;
82. GO
83. IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ORIGINAL_LOGIN())
84. BEGIN
85.   DECLARE @CreateUserDDL NVARCHAR(MAX);
86.   SET @CreateUserDDL = ‘CREATE USER [' + ORIGINAL_LOGIN() + '] FROM LOGIN [' + ORIGINAL_LOGIN() + '];’
87.   EXEC sp_executesql @CreateUserDDL;
88. END
89. GO
90. IF NOT EXISTS (SELECT *
91.                FROM sys.database_principals
92.                WHERE name = ‘$(ROLE_NAME)’)  
93. BEGIN
94.   DECLARE @CreateRoleDDL NVARCHAR(MAX);
95.   SET @CreateRoleDDL = ‘CREATE ROLE $(ROLE_NAME) AUTHORIZATION [' + ORIGINAL_LOGIN() + '];’
96.   EXEC sp_executesql @CreateRoleDDL;
97. END
98. GO
99. IF IS_MEMBER(‘$(ROLE_NAME)’) <> 1
100. BEGIN
101.   DECLARE @AddToRoleDDL NVARCHAR(MAX);
102.   SET @AddToRoleDDL = ‘EXEC sp_addrolemember ”$(ROLE_NAME)”,[' + ORIGINAL_LOGIN() + ']‘;
103.   EXEC sp_executesql @AddToRoleDDL;
104. END  
105. GO
106. – sys.dm_database_encryption_keys unknown to 2005 and lesser 2008 SKUs – if true will skip this
107. – has to spexecuted to avoid failed parse in 2005.
108. IF ISNULL(PARSENAME ( CONVERT(NVARCHAR(128), SERVERPROPERTY(‘ProductVersion’)) , 4 ), 0) > 9
109. AND SERVERPROPERTY(‘Edition’) IN (‘Developer Edition’,‘Enterprise Edition’,‘Enterprise Evaluation Edition’)
110. BEGIN
111.   IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys
112.                  WHERE [database_id] = DB_ID()
113.                  AND DB_NAME() = ‘$(DB_NAME)’)
114.      EXEC sp_executesql N’CREATE DATABASE ENCRYPTION KEY
115.                           WITH ALGORITHM = $(DEK_ALGORITHM)
116.                           ENCRYPTION BY SERVER CERTIFICATE [$(TDE_CERTIFICATE_NAME)];
117.                           ALTER DATABASE $(DB_NAME)
118.                           SET ENCRYPTION ON;’;
119. END
120. GO
121. IF NOT EXISTS (SELECT *
122.                FROM [sys].[symmetric_keys]
123.                WHERE [symmetric_key_id] = 101)
124.   CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘$(keyback_DMK_ENCRYPTION_PHRASE)’;
125. GO
126. – certs need not have a password, most probably should not. here password is better because
127. – malicious hackers are prevented from accessing the secrets in an elevation of authority attack 
128. – only those able to view the secret can open the key required to view the secrets 
129. – the password will be encrypted in the necessary stored procedures so will be required
130. – only when opening the symmetric in ad hoc query
131. IF NOT EXISTS (SELECT * FROM [sys].[certificates] WHERE [name] = ‘$(VALUE_CERTIFICATE_NAME)’)
132. CREATE CERTIFICATE [$(VALUE_CERTIFICATE_NAME)]
133.     ENCRYPTION BY PASSWORD = ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’
134.     WITH SUBJECT = ‘NameValue Column Encryption’;
135. GO
136. IF NOT EXISTS (SELECT *
137.                FROM [sys].[symmetric_keys]
138.                WHERE [name] = ‘$(DB_SYMMETRIC_KEY)’)
139.     CREATE SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)]
140.     WITH ALGORITHM = $(COLUMN_ENCRYPTION_ALGORITHM)
141.     ENCRYPTION BY CERTIFICATE [$(VALUE_CERTIFICATE_NAME)];
142. GO
143. IF OBJECT_ID(‘dbo.NameValues’) IS NULL
144.     CREATE TABLE [dbo].[NameValues]
145.         ( [Name] [NVARCHAR](128) NOT NULL
146.         , [Version] INT NOT NULL
147.           CONSTRAINT dft_NameValues__Version
148.           DEFAULT (1)
149.         , [Value] [VARBINARY] (256) NOT NULL
150.         , CreateDT [DATETIME] NOT NULL
151.           CONSTRAINT dft_NameValues__CreateDT
152.           DEFAULT (CURRENT_TIMESTAMP)
153.         , CreateUser [NVARCHAR](128) NOT NULL
154.           CONSTRAINT dft_NameValues__CreateUser
155.           DEFAULT (ORIGINAL_LOGIN())  
156.         , CONSTRAINT pk_NameValues__Name__Version
157.           PRIMARY KEY ([Name], [Version]));
158. GO
159. IF OBJECT_ID(‘dbo.itrg_NameValues’) IS NOT NULL
160. DROP TRIGGER dbo.itrg_NameValues
161. GO
162. CREATE TRIGGER dbo.itrg_NameValues
163. ON  dbo.NameValues
164. WITH ENCRYPTION
165. INSTEAD OF UPDATE, DELETE
166.   AS
167.   BEGIN
168.    SET NOCOUNT ON;
169.    IF EXISTS (SELECT * FROM inserted)
170.     INSERT dbo.NameValueHistory
171.      ( Name
172.      , Version
173.      , Action
174.      , Status)
175.     SELECT d.Name
176.          , d.Version
177.          , ‘UPDATE’
178.          , ‘NO-OP’  
179.     FROM deleted d;
180.    ELSE
181.     INSERT dbo.NameValueHistory
182.      ( Name
183.      , Version
184.      , Action
185.      , Status)
186.     SELECT d.Name
187.          , d.Version
188.          , ‘DELETE’
189.          , ‘NO-OP’  
190.     FROM deleted d;
191.   END
192. GO
193. IF OBJECT_ID(‘dbo.NameValueHistory’) IS NULL
194.     CREATE TABLE [dbo].[NameValueHistory]
195.         ( [Id] INT IDENTITY(1,1) NOT NULL
196.         , [Name] [NVARCHAR](128) NOT NULL – implicit FK
197.         , [Version] INT NULL – if add fails there may be no version to insert
198.         , [Action] [VARCHAR] (30) NOT NULL
199.         , [Status] [VARCHAR] (30) NOT NULL
200.     CONSTRAINT [ck_NameValueHistory__Status]
201.     CHECK (Status IN (‘Complete’, ‘Error’, ‘NO-OP’, ‘Valid’, ‘Invalid’))
202.         , [ErrorData] [XML] $(SPARSE) NULL
203.         , [CreateDT] [DATETIME] NOT NULL
204.           CONSTRAINT [dft_NameValueHistory__CreateDT]
205.           DEFAULT (CURRENT_TIMESTAMP)
206.         , [CreateUser] [NVARCHAR](128)
207.           CONSTRAINT [dft_NameValueHistory__CreateUser]
208.           DEFAULT (ORIGINAL_LOGIN())  
209.         , CONSTRAINT [pk_NameValueHistory__Id]
210.           PRIMARY KEY ([Id]));
211. GO
212. IF OBJECT_ID(‘dbo.itrg_NameValueHistory’) IS NOT NULL
213. DROP TRIGGER dbo.itrg_NameValueHistory
214. GO
215. CREATE TRIGGER dbo.itrg_NameValueHistory
216. ON dbo.NameValueHistory
217. WITH ENCRYPTION
218. INSTEAD OF UPDATE, DELETE
219.   AS
220.   BEGIN
221.    SET NOCOUNT ON;
222.    IF EXISTS (SELECT * FROM inserted)
223.     INSERT dbo.NameValueHistory
224.      ( Name
225.      , Version
226.      , Action
227.      , Status )
228.     SELECT d.Name
229.          , d.Version
230.          , ‘UPDATE of Id ‘ + CAST(Id as NVARCHAR(10))
231.          , ‘NO-OP’  
232.     FROM deleted d
233.    ELSE
234.     INSERT dbo.NameValueHistory
235.      ( Name
236.      , Version
237.      , Action
238.      , Status )
239.     SELECT d.Name
240.          , d.Version
241.          , ‘DELETE of Id ‘ + CAST(Id as NVARCHAR(10))
242.          , ‘NO-OP’  
243.     FROM deleted d
244.   END
245. GO
246. IF OBJECT_ID(‘dbo.MasterKeyBackupHistory’) IS NULL
247.     CREATE TABLE [dbo].[MasterKeyBackupHistory]
248.         ( [Id] INT IDENTITY(1,1) NOT NULL
249.         , [ServerName] [NVARCHAR](128) NOT NULL
250.           CONSTRAINT [dft_MasterKeyBackupHistory__ServerName]
251.           DEFAULT (@@SERVERNAME)
252.         , [DbName] [NVARCHAR](128) NULL
253.         , [KeyName] [NVARCHAR] (128) NOT NULL
254.         , [BackupName] [NVARCHAR](128) NOT NULL
255.         , [BackupPath] [NVARCHAR](1024) NOT NULL
256.         , [Action] [NVARCHAR] (128) NOT NULL
257.         , [Status] [NVARCHAR] (30) NOT NULL
258.     CONSTRAINT [ck_MasterKeyBackupHistory__Status]
259.     CHECK (Status IN (‘Complete’, ‘Error’))
260.         , [ErrorData] [XML] $(SPARSE) NULL
261.         , [CreateDT] [DATETIME] NOT NULL
262.           CONSTRAINT [dft_MasterKeyBackupHistory__CreateDT]
263.           DEFAULT (CURRENT_TIMESTAMP)
264.         , [CreateUser] [NVARCHAR](128)
265.           CONSTRAINT [dft_MasterKeyBackupHistory__CreateUser]
266.           DEFAULT (ORIGINAL_LOGIN())  
267.         , CONSTRAINT [pk_MasterKeyBackupHistory__Id]
268.           PRIMARY KEY ([Id]));
269. GO
270. IF OBJECT_ID(‘dbo.CertificateBackupHistory’) IS NULL
271. BEGIN
272.      CREATE TABLE [dbo].[CertificateBackupHistory]
273.         ( [Id] INT IDENTITY(1,1) NOT NULL
274.          , [ServerName] [NVARCHAR](128) NOT NULL
275.            CONSTRAINT [dft_CertificateBackupHistory__ServerName]
276.            DEFAULT (@@SERVERNAME)
277.          , [DbName] [NVARCHAR](128) NOT NULL
278.          , [CertificateName] [NVARCHAR] (128) NOT NULL
279.          , [BackupName] [NVARCHAR](128) NOT NULL
280.          , [BackupPath] [NVARCHAR](1024) NOT NULL
281.          , [Action] [VARCHAR] (30) NOT NULL
282.          , [Status] [VARCHAR] (30) NOT NULL
283.      CONSTRAINT [ck_CertificateBackupHistory__Status]
284.      CHECK (Status IN (‘Complete’, ‘Error’))
285.    , [Thumbprint] [VARBINARY] (32) NOT NULL
286.    , [PrivateKeyEncryptionType] [CHAR] (2) NOT NULL
287.      CONSTRAINT [ck_CertificateBackupHistory__PrivateKeyEncryptionType]
288.      CHECK (PrivateKeyEncryptionType IN (‘NA’, ‘MK’, ‘SK’, ‘PW’, ”))
289.          , [ErrorData] [XML] $(SPARSE) NULL
290.          , [CreateDT] [DATETIME] NOT NULL
291.            CONSTRAINT [dft_CertificateBackupHistory__CreateDT]
292.            DEFAULT (CURRENT_TIMESTAMP)
293.          , [CreateUser] [NVARCHAR](128) NOT NULL
294.            CONSTRAINT [dft_CertificateBackupHistory__CreateUser]
295.            DEFAULT (ORIGINAL_LOGIN())   
296.          , CONSTRAINT [pk_CertificateBackupHistory__Id]
297.            PRIMARY KEY ([Id]));
298.   CREATE NONCLUSTERED INDEX ixn_CertificateBackupHistory__ServerName__DbName__CertificateName
299.   ON dbo.CertificateBackupHistory(ServerName, DbName, CertificateName) INCLUDE (Action, Status);
300.   CREATE NONCLUSTERED INDEX ixn_CertificateBackupHistory__Thumbprint
301.   ON dbo.CertificateBackupHistory(Thumbprint) INCLUDE (Action, Status);
302. END
303. GO
304. IF OBJECT_ID (N’dbo.CheckFile’) IS NOT NULL
305.    DROP FUNCTION dbo.CheckFile
306. GO
307. /******************************************************************************
308. **    Auth: Bill Wunder
309. **    Desc: make sure nothing flakey about a file name
310. *******************************************************************************/
311. CREATE FUNCTION dbo.CheckFile
312. ( @Name NVARCHAR(128) )
313. RETURNS BIT
314. WITH EXECUTE AS CALLER, ENCRYPTION
315. AS
316. BEGIN
317.   DECLARE @Result BIT;
318.   SET @Name = UPPER(@Name);
319.   – SQL Injection prevention
320.   IF ( PATINDEX(‘%[#,.;:"''', @Name)
321.      + PATINDEX('%--%', @Name)
322.      + PATINDEX('%*/%', @Name)
323.      + PATINDEX('%/*%', @Name)
324.      + PATINDEX('%DROP%', @Name)
325.      + PATINDEX('%CREATE%', @Name)
326.      + PATINDEX('%SELECT%', @Name)
327.      + PATINDEX('%INSERT%', @Name)
328.      + PATINDEX('%UPDATE%', @Name)
329.      + PATINDEX('%DELETE%', @Name)
330.      + PATINDEX('%GRANT%', @Name)
331.      + PATINDEX('%ALTER%', @Name)
332.      + PATINDEX('%AUX%', @Name)
333.      + PATINDEX('%CLOCK$%', @Name)
334.      + PATINDEX('%COM[1-8]%’, @Name)
335.      + PATINDEX(‘%CON%’, @Name)
336.      + PATINDEX(‘%LPT[1-8]%’, @Name)
337.      + PATINDEX(‘%NUL%’, @Name)
338.      + PATINDEX(‘%PRN%’, @Name) ) = 0
339.    SET @Result = 1;
340.   ELSE
341.    SET @Result = 0;
342.   RETURN (@Result);
343. END
344. GO
345. IF OBJECT_ID (N’dbo.CheckPhrase’) IS NOT NULL
346.    DROP FUNCTION dbo.CheckPhrase
347. GO
348. /******************************************************************************
349. **    Auth: Bill Wunder
350. **    Desc: see if the unicode Password/Passphrase meets the policy
351. *******************************************************************************/
352. CREATE FUNCTION dbo.CheckPhrase
353. ( @Phrase NVARCHAR(128) )
354. RETURNS BIT
355. WITH EXECUTE AS CALLER, ENCRYPTION
356. AS
357. BEGIN
358. DECLARE @Result BIT;
359. – dft password policy as described in 2008R2 BOL + SQL Injection prevention
360. – a password can be generated as: SELECT CAST(newid() AS VARCHAR(128));
361. – sp_ and xp_ are covered by the match on underscore
362. – sp_ and xp_ are included by the match on underscore
363. IF LEN(@Phrase) > 7                                                       – at leaset 8 characters
364. AND PATINDEX(‘%[#,.;:__"'']%’, @Phrase) = 0                               – none of these symbols (added _ ‘ “)
365. AND ( CASE WHEN PATINDEX(‘%[A-Z]%’, @Phrase) > 0 THEN 1 ELSE 0 END        – at least 3 of 4
366.      + CASE WHEN PATINDEX(‘%[a-z]%’, @Phrase) > 0 THEN 1 ELSE 0 END        – uppercase, lowercase 
367.      + CASE WHEN PATINDEX(‘%[0-9]%’, @Phrase) > 0 THEN 1 ELSE 0 END        – numeric, symbol
368.      + CASE WHEN PATINDEX(‘%[^A-Z,^a-z,^0-9]%’, @Phrase) > 0 THEN 1 ELSE 0 END) > 2
369.   BEGIN
370.    SET @Phrase = UPPER(@Phrase);
371.    IF ( PATINDEX(‘%DROP%’, @Phrase)
372.       + PATINDEX(‘%CREATE%’, @Phrase)
373.       + PATINDEX(‘%SELECT%’, @Phrase)
374.       + PATINDEX(‘%INSERT%’, @Phrase)
375.       + PATINDEX(‘%UPDATE%’, @Phrase)
376.       + PATINDEX(‘%DELETE%’, @Phrase)
377.       + PATINDEX(‘%GRANT%’, @Phrase)
378.       + PATINDEX(‘%ALTER%’, @Phrase)
379.       + PATINDEX(‘%–%’, @Phrase)
380.       + PATINDEX(‘%*/%’, @Phrase)
381.       + PATINDEX(‘%/*%’, @Phrase) ) = 0
382.    SET @Result = 1;
383.   ELSE
384.    SET @Result = 0;   
385.   END   
386. RETURN (@Result);
387. END
388. GO
389. IF OBJECT_ID (N’dbo.IsAuthorized’) IS NOT NULL
390.    DROP FUNCTION dbo.IsAuthorized
391. GO
392. /******************************************************************************
393. **    Auth: Bill Wunder
394. **    Desc: deterine if the user is authorize to use the system
395. *******************************************************************************/
396. CREATE FUNCTION dbo.IsAuthorized()
397. RETURNS BIT
398. WITH EXECUTE AS CALLER, ENCRYPTION
399. AS
400. BEGIN
401.   DECLARE @Result BIT;
402.   – sysadmins will always be dbo in the database
403.   IF USER_NAME() = ‘dbo’
404.   AND ORIGINAL_LOGIN() = SUSER_SNAME() – SYSTEM_USER
405.   AND IS_SRVROLEMEMBER(‘sysadmin’) = 1
406.   AND EXISTS (SELECT *
407.               FROM sys.database_role_members
408.               WHERE role_principal_id = USER_ID(‘$(ROLE_NAME)’)
409.               AND USER_NAME(member_principal_id) = SUSER_SNAME() )  
410.    SET @Result = 1;
411.   ELSE
412.    SET @Result = 0;  
413.   RETURN (@Result);
414. END
415. GO
416. IF OBJECT_ID (N’dbo.NewBackupPath’) IS NOT NULL
417.    DROP FUNCTION dbo.NewBackupPath
418. GO
419. /******************************************************************************
420. **    Auth: Bill Wunder
421. **    Desc: get the path where the backups will be stored
422. **    ASSERT: the backup is stored in same folder as the .mdf of the database
423. **            no ACL changes are required, if the local backup copy is kept here
424. **            the permissions could be locked down further  
425. *******************************************************************************/
426. CREATE FUNCTION dbo.NewBackupPath (@DbName NVARCHAR(128))
427. RETURNS NVARCHAR(1024)
428. WITH EXECUTE AS CALLER, ENCRYPTION
429. AS
430. BEGIN
431. – backup to the .mdf’s folder,
432. –  no ACL change is required – svc acct writes the files
433. –  no dependency on specific local configurations to write file
434.     RETURN (SELECT LEFT( physical_name
435.                        , LEN(physical_name) - CHARINDEX( ‘\’
436.                                                        , REVERSE(physical_name)) + 1)
437.          FROM sys.master_files
438.          WHERE database_id = DB_ID(@DbName)
439.          AND file_id = 1
440.          AND type = 0);   
441. END
442. GO
443. IF OBJECT_ID (N’dbo.NewCertificateBackupName’) IS NOT NULL
444.    DROP FUNCTION dbo.NewCertificateBackupName
445. GO
446. /******************************************************************************
447. **    Auth: Bill Wunder
448. **    Desc: get the name of the file the certificate backup will use
449. *******************************************************************************/
450. CREATE FUNCTION dbo.NewCertificateBackupName (@DbName NVARCHAR(128), @CertificateName NVARCHAR(128))
451. RETURNS NVARCHAR(1024)
452. WITH EXECUTE AS CALLER, ENCRYPTION
453. AS
454. BEGIN
455.     RETURN (REPLACE(@@SERVERNAME,‘\’,‘$’) + ‘__’ + @DbName + ‘__’ + @CertificateName + ‘__’
456.          + REPLACE(REPLACE(CONVERT(NVARCHAR(20), CURRENT_TIMESTAMP, 20),SPACE(1),‘__’),‘:’,‘-’));   
457. END
458. GO
459. IF OBJECT_ID (N’dbo.NewMasterKeyBackupName’) IS NOT NULL
460.    DROP FUNCTION dbo.NewMasterKeyBackupName
461. GO
462. /******************************************************************************
463. **    Auth: Bill Wunder
464. **    Desc: get the name of the file a service master key backup
465. **    or a database master key backup
466. *******************************************************************************/
467. CREATE FUNCTION dbo.NewMasterKeyBackupName (@DbName NVARCHAR(128))
468. RETURNS NVARCHAR(1024)
469. WITH EXECUTE AS CALLER, ENCRYPTION
470. AS
471. BEGIN
472.     RETURN (REPLACE(@@SERVERNAME,‘\’,‘$’) + ‘__’ +
473.            + CASE WHEN @DbName is NULL
474.                   THEN ‘Server__SMK’
475.                   ELSE @DbName + ‘__DMK’ END + ‘__’ +
476.      + REPLACE(REPLACE(CONVERT(NVARCHAR(20), CURRENT_TIMESTAMP, 20),SPACE(1),‘__’),‘:’,‘-’));   
477. END
478. GO
479. IF OBJECT_ID(‘dbo.AddNameValue’,‘P’) IS NOT NULL
480.     DROP PROCEDURE [dbo].[AddNameValue]
481. GO
482. /******************************************************************************
483. **    Auth: Bill Wunder
484. **    Desc: encrypt sensitive data
485. *******************************************************************************/
486. CREATE PROCEDURE [dbo].[AddNameValue]
487. ( @Name [NVARCHAR] (128)
488. , @Value [NVARCHAR] (128) )
489. WITH EXECUTE AS CALLER, ENCRYPTION
490. AS
491. BEGIN
492. SET NOCOUNT ON;
493. DECLARE @Version INT;
494. DECLARE @Err XML;
495. OPEN SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)]
496. DECRYPTION BY CERTIFICATE [$(VALUE_CERTIFICATE_NAME)]
497. WITH PASSWORD = ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’;
498.     BEGIN TRY
499.   IF (dbo.IsAuthorized()) = 0
500.       RAISERROR(2147483644,16,1,‘User’);
501.   – password policy + SQL Injection prevention
502.   IF (dbo.CheckPhrase(@Value) = 0)
503.    RAISERROR(2147483644,16,1,@Name);
504.   BEGIN TRANSACTION  
505.    – negative magnitude means attempt to insert existing value for name
506.    SET @Version = (SELECT TOP(1) VERSION * CASE WHEN DecryptByKey(Value, 1, Name) =  @Value THEN -1 ELSE 1 END
507.                    FROM [dbo].[NameValues] – WITH(ROWLOCK,XLOCK)
508.                    WHERE Name = @Name
509.                    ORDER BY Version DESC);              
510.    IF ISNULL(@Version,1) > 0
511.     BEGIN
512.      INSERT [dbo].[NameValues] ([Name], [Version], [Value])
513.      SELECT @Name, ISNULL(@Version+1,1), EncryptByKey(Key_GUID(‘$(DB_SYMMETRIC_KEY)’), @Value, 1, @Name);
514.      INSERT dbo.NameValueHistory (Name, Version, Action, Status)
515.      SELECT @Name, ISNULL(@Version+1,1), OBJECT_NAME(@@PROCID), ‘Complete’;    
516.     END  
517.    COMMIT TRANSACTION;
518.   END TRY
519.   BEGIN CATCH
520.    WHILE @@TRANCOUNT > 0
521.     ROLLBACK TRANSACTION;
522.    INSERT dbo.NameValueHistory
523.     ( Name
524.     , Version
525.     , Action
526.     , Status
527.     , ErrorData)
528.    SELECT @Name
529.         , ISNULL(@Version+1,1)
530.         , OBJECT_NAME(@@PROCID)
531.         , ‘Error’    
532.         , (SELECT ERROR_NUMBER() AS ErrorNumber
533.                 , ERROR_SEVERITY() AS ErrorSeverity
534.                 , ERROR_STATE() as ErrorState
535.                 , ERROR_PROCEDURE() as ErrorProcedure
536.                 , ERROR_LINE() as ErrorLine
537.                 , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
538.   END CATCH;
539.   CLOSE SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)];
540. END
541. GO
542. IF OBJECT_ID(‘dbo.GetValueByName’,‘P’) IS NOT NULL
543.     DROP PROCEDURE [dbo].[GetValueByName];
544. GO
545. /******************************************************************************
546. **    Auth: Bill Wunder
547. **    Desc: fetch encrypted value
548. *******************************************************************************/
549. CREATE PROCEDURE [dbo].[GetValueByName]
550. ( @Name [NVARCHAR] (128)
551. , @Value [NVARCHAR] (128) OUTPUT)
552. WITH EXECUTE AS CALLER, ENCRYPTION
553. AS
554. BEGIN
555. DECLARE @Version INT;
556. SET NOCOUNT ON;
557. OPEN SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)]
558. DECRYPTION BY CERTIFICATE [$(VALUE_CERTIFICATE_NAME)]
559. WITH PASSWORD = ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’;
560. BEGIN TRY
561.   IF (dbo.IsAuthorized()) = 0
562.    RAISERROR(2147483644,16,1,‘User’);
563.   – password policy + SQL Injection prevention
564.   IF (dbo.CheckPhrase(@Value) = 0)
565.    RAISERROR(2147483644,16,1,@Name);
566.   SELECT TOP(1) @Version = Version
567.               , @Value = DecryptByKey( Value, 1, Name)
568.   FROM [dbo].[NameValues]
569.   WHERE [Name] = @Name
570.   ORDER BY [Version] DESC;
571.   INSERT dbo.NameValueHistory
572.    ( Name
573.    , Version
574.    , Action
575.    , Status )
576.   SELECT @Name
577.        , @Version
578.        , OBJECT_NAME(@@PROCID)
579.        , ‘Complete’;    
580. END TRY
581. BEGIN CATCH
582.   INSERT dbo.NameValueHistory
583.    ( Name
584.    , Version
585.    , Action
586.    , Status
587.    , ErrorData)
588.   SELECT @Name
589.        , ISNULL(@Version+1,1)
590.        , OBJECT_NAME(@@PROCID)
591.        , ‘Error’    
592.        , (SELECT ERROR_NUMBER() AS ErrorNumber
593.                , ERROR_SEVERITY() AS ErrorSeverity
594.                , ERROR_STATE() as ErrorState
595.                , ERROR_PROCEDURE() as ErrorProcedure
596.                , ERROR_LINE() as ErrorLine
597.                , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
598. END CATCH
599. CLOSE SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)];
600. END
601. GO
602. IF OBJECT_ID (N’dbo.ValidateNameValue’) IS NOT NULL
603.    DROP PROCEDURE dbo.ValidateNameValue
604. GO
605. /******************************************************************************
606. **    Auth: Bill Wunder
607. **    Desc: validate encrypted value
608. *******************************************************************************/
609. CREATE PROCEDURE dbo.ValidateNameValue
610. ( @Name NVARCHAR(128)
611. , @Value NVARCHAR(128)
612. , @IsValid BIT OUTPUT )
613. WITH EXECUTE AS CALLER, ENCRYPTION
614. AS
615. BEGIN
616. SET NOCOUNT ON;
617. DECLARE @Version INT;
618. SET @IsValid = 0
619. OPEN SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)]
620. DECRYPTION BY CERTIFICATE [$(VALUE_CERTIFICATE_NAME)]
621. WITH PASSWORD = ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’;
622. BEGIN TRY
623.   IF (dbo.IsAuthorized()) = 0
624.    RAISERROR(2147483644,16,1,‘User’);
625.   – password policy + SQL Injection prevention
626.   IF (dbo.CheckPhrase(@Value) = 0)
627.    RAISERROR(2147483644,16,1,@Name);
628.   SELECT TOP(1) @Version = Version
629.               , @IsValid = 1
630.   FROM [dbo].[NameValues]
631.   WHERE [Name] = @Name
632.   AND DecryptByKey( Value, 1, Name) = @Value
633.   ORDER BY [Version] DESC;
634.   INSERT dbo.NameValueHistory
635.     ( Name
636.     , Version
637.     , Action
638.     , Status)
639.   SELECT @Name
640.        , @Version
641.        , OBJECT_NAME(@@PROCID)
642.        , CASE WHEN @IsValid = 1 THEN  ‘Valid’ ELSE ‘Invalid’ END;    
643.   END TRY
644.   BEGIN CATCH
645.    INSERT dbo.NameValueHistory
646.     ( Name
647.     , Version
648.     , Action
649.     , Status
650.     , ErrorData)
651.    SELECT @Name
652.         , ISNULL(@Version+1,1)
653.         , OBJECT_NAME(@@PROCID)
654.         , ‘Error’    
655.         , (SELECT ERROR_NUMBER() AS ErrorNumber
656.                 , ERROR_SEVERITY() AS ErrorSeverity
657.                 , ERROR_STATE() as ErrorState
658.                 , ERROR_PROCEDURE() as ErrorProcedure
659.                 , ERROR_LINE() as ErrorLine
660.                 , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
661.   END CATCH
662. CLOSE SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)];
663. END
664. GO
665. IF OBJECT_ID (N’dbo.BackupServiceMasterKey’) IS NOT NULL
666.    DROP PROCEDURE dbo.BackupServiceMasterKey
667. GO
668. /******************************************************************************
669. **    Auth: Bill Wunder
670. **    Desc: backup the service master key of the SQL Server Instance
671. *******************************************************************************/
672. CREATE PROCEDURE dbo.BackupServiceMasterKey
673. ( @BackupPhrase NVARCHAR(128) )
674. WITH EXECUTE AS CALLER, ENCRYPTION
675. AS
676. BEGIN
677. DECLARE @Name NVARCHAR(128)
678.       , @BackupDDL NVARCHAR(MAX)
679.       , @BackupName NVARCHAR(128)
680.       , @BackupPath NVARCHAR(128)
681.       , @return_code INT;
682. SET NOCOUNT ON;
683. BEGIN TRY
684.   BEGIN TRANSACTION
685.    SET @Name = ‘SMK_BACKUP_PHRASE’;
686.    EXEC dbo.AddNameValue @Name, @BackupPhrase;   
687.    SET @BackupName = dbo.NewMasterKeyBackupName(NULL);
688.    SET @BackupPath = dbo.NewBackupPath(‘master’);
689.    SET @BackupDDL = ‘USE MASTER;’ + SPACE(1)
690.       + ‘BACKUP SERVICE MASTER KEY’ + SPACE(1)
691.       + ‘TO FILE = ”’ + @BackupPath + @BackupName + ‘$(MASTER_KEY_BACKUP_EXT)”’ + SPACE(1)
692.       + ‘ENCRYPTION BY PASSWORD = ”’ + @BackupPhrase + ”’;’;
693.    EXEC @return_code = sp_executesql @BackupDDL;
694.    IF @return_code <> 0
695.      RAISERROR(2147483636,16,1,‘backup’,@return_code);
696.    ELSE
697.      RAISERROR(2147483635,0,0,‘backup’);
698.    INSERT MasterKeyBackupHistory
699.      ( DbName
700.      , KeyName
701.      , BackupName
702.      , BackupPath
703.      , Action
704.      , Status)
705.    SELECT NULL
706.           , ‘Service Master Key’
707.           , @BackupName
708.           , @BackupPath
709.           , OBJECT_NAME(@@PROCID)
710.           , ‘Complete’;
711.   COMMIT TRANSACTION;
712. END TRY
713. BEGIN CATCH
714.   IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
715.   INSERT MasterKeyBackupHistory
716.     ( DbName
717.     , KeyName
718.     , BackupName
719.     , BackupPath
720.     , Action
721.     , Status
722.     , ErrorData)
723.   SELECT NULL
724.          , ‘Service Master Key’
725.          , @BackupName
726.          , @BackupPath
727.          , OBJECT_NAME(@@PROCID)
728.          , ‘Error’
729.    , (SELECT ERROR_NUMBER() AS ErrorNumber
730.            , ERROR_SEVERITY() AS ErrorSeverity
731.            , ERROR_STATE() as ErrorState
732.            , ERROR_PROCEDURE() as ErrorProcedure
733.            , ERROR_LINE() as ErrorLine
734.            , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
735. END CATCH
736. END
737. GO
738. IF OBJECT_ID (N’dbo.RestoreServiceMasterKey’) IS NOT NULL
739.    DROP PROCEDURE dbo.RestoreServiceMasterKey
740. GO
741. /******************************************************************************
742. **    Auth: Bill Wunder
743. **    Desc: restore the service master key of the SQL Server Instance from backup
744. *******************************************************************************/
745. CREATE PROCEDURE dbo.RestoreServiceMasterKey
746. WITH EXECUTE AS CALLER, ENCRYPTION
747. AS
748. BEGIN
749. DECLARE @RestoreDDL NVARCHAR(MAX)
750.       , @BackupPhrase NVARCHAR(128)
751.       , @BackupName NVARCHAR(128)
752.       , @BackupPath NVARCHAR(128)
753.       , @return_code INT;
754. SET NOCOUNT ON;
755. BEGIN TRY
756.   EXEC [dbo].[GetValueByName] ‘SMK_BACKUP_PHRASE’, @BackupPhrase OUTPUT;
757.   SELECT TOP(1) @BackupName = BackupName
758.               , @BackupPath = BackupPath
759.   FROM dbo.MasterKeyBackupHistory
760.   WHERE KeyName = ‘Service Master Key’
761.   AND Action = ‘BackupServiceMasterKey’
762.   AND Status = ‘Complete’
763.   ORDER BY Id DESC;           
764.   SET @RestoreDDL = ‘USE MASTER;’ + SPACE(1)
765.      + ‘RESTORE SERVICE MASTER KEY’ + SPACE(1)
766.      + ‘FROM FILE = ”’ + @BackupPath + @BackupName + ‘$(MASTER_KEY_BACKUP_EXT)”’ + SPACE(1)
767.      + ‘DECRYPTION BY PASSWORD = ”’ + @BackupPhrase + ”’;’;
768.   EXEC @return_code = sp_executesql @RestoreDDL;
769.   IF @return_code <> 0
770.     RAISERROR(2147483636,16,1,‘restore’,@return_code);
771.   ELSE
772.     RAISERROR(2147483635,0,0,‘restore’);
773.   INSERT MasterKeyBackupHistory
774.     ( DbName
775.     , KeyName
776.     , BackupName
777.     , BackupPath
778.     , Action
779.     , Status)
780.   SELECT NULL
781.    , ‘Service Master Key’
782.    , @BackupName
783.    , @BackupPath
784.    , OBJECT_NAME(@@PROCID)
785.    , ‘Complete’;
786. END TRY
787. BEGIN CATCH
788.   INSERT MasterKeyBackupHistory
789.     ( DbName
790.     , KeyName
791.     , BackupName
792.     , BackupPath
793.     , Action
794.     , Status
795.     , ErrorData)
796.   SELECT NULL
797.          , ‘Service Master Key’
798.          , @BackupName
799.          , @BackupPath
800.    , OBJECT_NAME(@@PROCID)
801.          , ‘Error’
802.    , (SELECT ERROR_NUMBER() AS ErrorNumber
803.            , ERROR_SEVERITY() AS ErrorSeverity
804.            , ERROR_STATE() as ErrorState
805.            , ERROR_PROCEDURE() as ErrorProcedure
806.            , ERROR_LINE() as ErrorLine
807.            , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
808. END CATCH
809. END
810. GO
811. IF OBJECT_ID (N’dbo.BackupDatabaseMasterKey’) IS NOT NULL
812.    DROP PROCEDURE dbo.BackupDatabaseMasterKey
813. GO
814. /******************************************************************************
815. **    Auth: Bill Wunder
816. **    Desc: backup a database master key
817. *******************************************************************************/
818. CREATE PROCEDURE dbo.BackupDatabaseMasterKey
819. ( @DbName NVARCHAR(128)
820. , @BackupPhrase NVARCHAR(128)
821. , @KeyPhrase NVARCHAR(128) = NULL )
822. WITH EXECUTE AS CALLER, ENCRYPTION
823. AS
824. BEGIN
825. DECLARE @OpenDDL NVARCHAR(184)
826.        , @BackupDDL NVARCHAR(MAX)
827.        , @CloseDDL NVARCHAR(184)
828.        , @BackupPhraseName NVARCHAR(128)
829.        , @KeyPhraseName NVARCHAR(128)
830.        , @BackupName NVARCHAR(128)
831.        , @BackupPath NVARCHAR(128)
832.        , @return_code INT;
833. SET NOCOUNT ON;
834. BEGIN TRY
835.   BEGIN TRANSACTION;
836.    IF DB_ID(@DbName) IS NULL
837.      RAISERROR(2147483644,16,1,@DbName);
838.    SET @BackupPhraseName = @DbName + N’_DMK_BACKUP_PHRASE’;
839.    EXEC dbo.AddNameValue @BackupPhraseName, @BackupPhrase;   
840.    SET @BackupName = dbo.NewMasterKeyBackupName (@DbName);
841.    SET @BackupPath = dbo.NewBackupPath(@DbName)
842.    SET @KeyPhraseName = @DbName + N’_DMK_ENCRYPTION_PHRASE’;
843.    EXEC GetValueByName @KeyPhraseName, @KeyPhrase OUTPUT;
844.    SET @BackupDDL = ‘USE [' + @DbName + '];’
845.       + ‘OPEN MASTER KEY DECRYPTION BY PASSWORD = ”’ + @KeyPhrase + ”’;’
846.       + ‘BACKUP MASTER KEY’ + SPACE(1)
847.       + ‘TO FILE = ”’ + @BackupPath + @BackupName + ‘$(MASTER_KEY_BACKUP_EXT)”’ + SPACE(1)
848.       + ‘ENCRYPTION BY PASSWORD = ”’ + @BackupPhrase + ”’;’
849.       + ‘CLOSE MASTER KEY;’;
850.    EXEC @return_code = sp_executesql @BackupDDL;
851.    IF @return_code <> 0
852.      RAISERROR(2147483634,16,1,@DbName, ‘backup’, @return_code);
853.    ELSE
854.      RAISERROR(2147483633,0,0,@DbName, ‘backup’);
855.    INSERT MasterKeyBackupHistory
856.      ( DbName
857.      , KeyName
858.      , BackupName
859.      , BackupPath
860.      , Action
861.      , Status)
862.    SELECT @DbName
863.           , ‘Database Master Key’
864.           , @BackupName
865.           , @BackupPath
866.           , OBJECT_NAME(@@PROCID)
867.           , ‘Complete’;
868.   COMMIT TRANSACTION;
869. END TRY
870. BEGIN CATCH
871.   WHILE @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
872.   INSERT MasterKeyBackupHistory
873.     ( DbName
874.     , KeyName
875.     , BackupName
876.     , BackupPath
877.     , Action
878.     , Status
879.     , ErrorData)
880.   SELECT @DbName
881.          , ‘Database Master Key’
882.          , ISNULL(@BackupName,”)
883.          , ISNULL(@BackupPath,”)
884.          , OBJECT_NAME(@@PROCID)
885.          , ‘Error’
886.    , (SELECT ERROR_NUMBER() AS ErrorNumber
887.            , ERROR_SEVERITY() AS ErrorSeverity
888.            , ERROR_STATE() as ErrorState
889.            , ERROR_PROCEDURE() as ErrorProcedure
890.            , ERROR_LINE() as ErrorLine
891.            , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
892. END CATCH
893. END
894. GO
895. IF OBJECT_ID (N’dbo.RestoreDatabaseMasterKey’) IS NOT NULL
896.    DROP PROCEDURE dbo.RestoreDatabaseMasterKey
897. GO
898. /******************************************************************************
899. **    Auth: Bill Wunder
900. **    Desc: restore a database master key from a backup
901. *******************************************************************************/
902. CREATE PROCEDURE dbo.RestoreDatabaseMasterKey
903. ( @DbName NVARCHAR(128) )
904. WITH EXECUTE AS CALLER, ENCRYPTION
905. AS
906. BEGIN
907. DECLARE @DMKRestoreDDL NVARCHAR(MAX)
908.       , @BackupValueName NVARCHAR(128)
909.       , @KeyValueName NVARCHAR(128)
910.       , @BackupName NVARCHAR(128)
911.       , @BackupPath NVARCHAR(128)
912.       , @BackupPhrase NVARCHAR(128)
913.       , @KeyPhrase NVARCHAR(128)
914.       , @return_code INT;
915. SET NOCOUNT ON;
916. BEGIN TRY
917.   SET @BackupValueName = @DbName + ‘_DMK_BACKUP_PHRASE’;
918.   EXEC [dbo].[GetValueByName] @BackupValueName, @BackupPhrase OUTPUT;
919.   SET @KeyValueName = @DbName + ‘_DMK_ENCRYPTION_PHRASE’;
920.   EXEC [dbo].[GetValueByName] @KeyValueName, @KeyPhrase OUTPUT;
921.   SELECT TOP (1) @BackupPath = BackupPath
922.                , @BackupName = BackupName                 
923.   FROM dbo.MasterKeyBackupHistory
924.   WHERE KeyName = ‘Database Master Key’
925.   AND DbNAme = @DbName
926.   AND Action = ‘BackupDatabaseMasterKey’
927.   AND Status = ‘Complete’
928.   ORDER BY Id DESC;  
929.   SET @DMKRestoreDDL = ‘USE ‘ + @DbName + ‘;’ + SPACE(1)
930.      + ‘RESTORE MASTER KEY’ + SPACE(1)
931.      + ‘FROM FILE = ”’ + @BackupPath + @BackupName + ‘$(MASTER_KEY_BACKUP_EXT)”’ + SPACE(1)
932.      + ‘DECRYPTION BY PASSWORD = ”’ + @BackupPhrase + ””   
933.      + CASE WHEN @KeyPhrase IS NULL
934.             THEN ”
935.             ELSE ‘ENCRYPTION BY PASSWORD = ”’ + @KeyPhrase + ””
936.             END + ‘;’
937.   EXEC @return_code = sp_executesql @DMKRestoreDDL;
938.   IF @return_code <> 0
939.     RAISERROR(2147483634,16,1,@DbName, ‘restore’, @return_code);
940.   ELSE
941.     RAISERROR(2147483633,0,0,@DbName, ‘restore’);
942.   INSERT MasterKeyBackupHistory
943.     ( DbName
944.     , KeyName
945.     , BackupName
946.     , BackupPath
947.     , Action
948.     , Status)
949.   SELECT @DbName
950.          , ‘Database Master Key’
951.          , @BackupName
952.          , @BackupPath
953.          , OBJECT_NAME(@@PROCID)
954.          , ‘Complete’;
955. END TRY
956. BEGIN CATCH
957.   INSERT MasterKeyBackupHistory
958.     ( DbName
959.     , KeyName
960.     , BackupName
961.     , BackupPath
962.     , Action
963.     , Status
964.     , ErrorData)
965.   SELECT ISNULL(@DbName,”)
966.          , ‘Database Master Key’
967.          , ISNULL(@BackupName,”)
968.          , ISNULL(@BackupPath,”)
969.          , OBJECT_NAME(@@PROCID)
970.          , ‘Error’
971.    , (SELECT ERROR_NUMBER() AS ErrorNumber
972.            , ERROR_SEVERITY() AS ErrorSeverity
973.            , ERROR_STATE() as ErrorState
974.            , ERROR_PROCEDURE() as ErrorProcedure
975.            , ERROR_LINE() as ErrorLine
976.            , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
977. END CATCH
978. END
979. GO
980. IF OBJECT_ID (N’dbo.BackupCertificate’) IS NOT NULL
981.    DROP PROCEDURE dbo.BackupCertificate
982. GO
983. /******************************************************************************
984. **    Auth: Bill Wunder
985. **    Desc: backup a certificate 
986. *******************************************************************************/
987. CREATE PROCEDURE dbo.BackupCertificate
988. ( @CertificateName NVARCHAR(128)
989. , @DbName NVARCHAR(128)
990. , @BackupPhrase NVARCHAR(128) = NULL – use stored value if exists
991. , @KeyPhrase NVARCHAR(128) = NULL) – value needed only when type = PW
992. WITH EXECUTE AS CALLER, ENCRYPTION
993. AS
994. BEGIN
995. DECLARE @CertificateList TABLE (name NVARCHAR(128));
996. DECLARE @CertificateListDDL NVARCHAR(165)
997.       , @ThumbprintDDL NVARCHAR(MAX)
998.       , @Thumbprint VARBINARY(32)
999.       , @PrivateKeyEncryptionType CHAR(2)
1000.       , @BackupDDL NVARCHAR(MAX)
1001.       , @BackupName NVARCHAR(128)
1002.       , @BackupPath NVARCHAR(128)
1003.       , @BackupValueName NVARCHAR(128)
1004.       , @KeyValueName NVARCHAR(128)
1005.       , @return_code INT;
1006. SET NOCOUNT ON;
1007. BEGIN TRY
1008.   BEGIN TRANSACTION
1009.    SET @BackupValueName = @DbName + ‘_’ + @CertificateName + ‘_CERTIFICATE_BACKUP_PHRASE’
1010.    IF @BackupPhrase IS NULL
1011.     EXEC [dbo].[GetValueByName] @BackupValueName, @BackupPhrase OUTPUT;
1012.    ELSE
1013.      EXEC dbo.AddNameValue @BackupValueName, @BackupPhrase;   
1014.    SET @KeyValueName = @DbName + ‘_’ + @CertificateName + ‘_CERTIFICATE_ENCRYPTION_PHRASE’
1015.    IF @KeyPhrase IS NULL
1016.     EXEC [dbo].[GetValueByName] @KeyValueName, @KeyPhrase OUTPUT;
1017.    ELSE
1018.      EXEC dbo.AddNameValue @KeyValueName, @KeyPhrase;   
1019.   – verify @DbName and @CertificateName before parsing into any query
1020.   IF DB_ID(@DbName) IS NOT NULL   
1021.    BEGIN
1022.     SET @CertificateListDDL = ‘SELECT name from ‘ + @DbName + ‘.sys.certificates’
1023.     INSERT @CertificateList (name)    
1024.     EXEC sp_executesql @CertificateListDDL;
1025.    END
1026.   – @CertificateName is used in the where clause first to complete sql injection prevention
1027.   IF NOT EXISTS (SELECT name FROM @CertificateList WHERE name = @CertificateName)
1028.    RAISERROR(2147483644,16,1,‘Certificate’);
1029.   SET @ThumbprintDDL = ‘USE ‘ + @DbName + ‘;’ + SPACE(1)
1030.                + ‘SELECT @Thumbprint = Thumbprint’ + SPACE(1)
1031.                + ‘     , @PrivateKeyEncryptionType = pvt_key_encryption_type’ + SPACE(1)
1032.                + ‘FROM sys.certificates’ + SPACE(1)
1033.                + ‘WHERE name = @CertificateName’;
1034.   EXEC sp_executesql @ThumbprintDDL
1035.                , N’@CertificateName NVARCHAR(128), @Thumbprint VARBINARY(32) OUTPUT, @PrivateKeyEncryptionType CHAR(2) OUTPUT’
1036.                , @CertificateName, @Thumbprint OUTPUT, @PrivateKeyEncryptionType OUTPUT;
1037.   SET @BackupName = dbo.NewCertificateBackupName (@DbName, @CertificateName);
1038.   SET @BackupPath = dbo.NewBackupPath(@DbName);
1039.   SET @BackupDDL = ‘USE ‘ + @DbName + ‘;’
1040.      + ‘BACKUP CERTIFICATE ‘ + @CertificateName + SPACE(1)
1041.      + ‘TO FILE = ”’ + @BackupPath + @BackupName + ‘$(PUBLIC_KEY_BACKUP_EXT)”’ + SPACE(1)
1042.   IF @PrivateKeyEncryptionType <> ‘NA’
1043.    BEGIN
1044.     SET @BackupDDL = @BackupDDL + ‘WITH PRIVATE KEY’ + SPACE(1)
1045.        + ‘( FILE = ”’ + @BackupPath + @BackupName + ‘$(PRIVATE_KEY_BACKUP_EXT)”’
1046.        + ‘, ENCRYPTION BY PASSWORD = ”’ + @BackupPhrase + ””;
1047.     IF @PrivateKeyEncryptionType = ‘PW’
1048.      SET @BackupDDL = @BackupDDL + ‘, DECRYPTION BY PASSWORD = ”’ + @KeyPhrase + ””;
1049.     SET @BackupDDL = @BackupDDL + ‘)’;
1050.    END
1051.   EXEC @return_code = sp_executesql @BackupDDL;
1052.   IF @return_code <> 0
1053.     RAISERROR(2147483632,16,1,@DbName,@CertificateName,@return_code,‘backup’);
1054.   ELSE
1055.     RAISERROR(2147483631, 0,0,@DbName,@CertificateName,‘backup’);
1056.   INSERT dbo.CertificateBackupHistory
1057.     ( DbName
1058.     , CertificateName
1059.     , BackupName
1060.     , BackupPath
1061.     , Action
1062.     , Status
1063.     , Thumbprint
1064.     , PrivateKeyEncryptionType)
1065.   SELECT @DbName
1066.        , @CertificateName
1067.        , @BackupName
1068.        , @BackupPath
1069.        , OBJECT_NAME(@@PROCID)
1070.        , ‘Complete’
1071.        , @Thumbprint
1072.        , @PrivateKeyEncryptionType;
1073.   COMMIT TRANSACTION
1074. END TRY
1075. BEGIN CATCH
1076.   WHILE @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
1077.   INSERT dbo.CertificateBackupHistory
1078.    ( DbName
1079.    , CertificateName
1080.    , BackupName
1081.    , BackupPath
1082.    , Action
1083.    , Status
1084.    , Thumbprint
1085.    , PrivateKeyEncryptionType
1086.    , ErrorData)
1087. SELECT ISNULL(@DbName,”)
1088.       , ISNULL(@CertificateName, ”)
1089.       , ISNULL(@BackupName,”)
1090.       , ISNULL(@BackupPath,”)
1091.       , OBJECT_NAME(@@PROCID)
1092.       , ‘Error’
1093.       , ISNULL(@Thumbprint,0×0)
1094.       , ISNULL(@PrivateKeyEncryptionType,”)
1095.       , (SELECT ERROR_NUMBER() AS ErrorNumber
1096.               , ERROR_SEVERITY() AS ErrorSeverity
1097.               , ERROR_STATE() as ErrorState
1098.               , ERROR_PROCEDURE() as ErrorProcedure
1099.               , ERROR_LINE() as ErrorLine
1100.               , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
1101. END CATCH      
1102. END
1103. GO
1104. IF OBJECT_ID (N’dbo.RestoreCertificate’) IS NOT NULL
1105.    DROP PROCEDURE dbo.RestoreCertificate
1106. GO
1107. /******************************************************************************
1108. **    Auth: Bill Wunder
1109. **    Desc: restore a certificate
1110. **    ASSERT: the backup is located in same folder as the .mdf of the database
1111. **            and that the .mdf is never moved from that folder
1112. *******************************************************************************/
1113. CREATE PROCEDURE dbo.RestoreCertificate
1114. ( @CertificateName NVARCHAR(128)
1115. , @DbName NVARCHAR(128) )
1116. WITH EXECUTE AS CALLER, ENCRYPTION
1117. AS
1118. BEGIN
1119. DECLARE @RestoreDDL NVARCHAR(4000)
1120.       , @LookupDDL NVARCHAR(165)
1121.       , @Thumbprint VARBINARY(32)
1122.       , @PrivateKeyEncryptionType CHAR(2)
1123.       , @CertificateId INT
1124.       , @BackupName NVARCHAR(128)
1125.       , @BackupPath NVARCHAR(1024)
1126.       , @BackupPhrase NVARCHAR(128)
1127.       , @BackupValueName NVARCHAR(128)
1128.       , @KeyPhrase NVARCHAR(128)
1129.       , @KeyValueName NVARCHAR(128)
1130.       , @return_code INT;
1131. SET NOCOUNT ON;
1132. BEGIN TRY
1133.    – sql injections should blow up here
1134.    SELECT TOP(1)
1135.       @PrivateKeyEncryptionType = PrivateKeyEncryptionType
1136.     , @Thumbprint = Thumbprint
1137.     , @BackupPath = BackupPath
1138.     , @BackupName = BackupName
1139.    FROM dbo.CertificateBackupHistory
1140.    WHERE ServerName = @@SERVERNAME
1141.    AND DbName = @DbName
1142.    AND CertificateName = @CertificateName
1143.    AND Action = ‘BackupCertificate’
1144.    AND Status = ‘Complete’
1145.    ORDER BY Id DESC;   
1146.    IF DB_ID(@DbName) IS NULL
1147.     RAISERROR(2147483644,16,1,‘Database’);
1148.    SET @LookupDDL = ‘SELECT @CertificateId = certificate_id’ + SPACE(1)
1149.                   + ‘FROM ‘ + @DbName + ‘.sys.certificates’ + SPACE(1)
1150.                   + ‘WHERE name = @CertificateName ‘ + SPACE(1)  
1151.                   + ‘AND Thumbprint = @Thumbprint’;
1152.    EXEC sp_executesql @LookupDDL
1153.                     , N’@CertificateName NVARCHAR(128), @Thumbprint VARBINARY(32), @CertificateId INT OUTPUT’
1154.                     , @CertificateName, @Thumbprint, @CertificateId OUTPUT;
1155.    IF (@CertificateId IS NOT NULL) – certificate exist – cannot restore
1156.     RAISERROR(2147483637,16,1,@DbName,@CertificateName,‘cannot restore over existing certificate’);
1157.    SET @BackupValueName = @DbName + ‘_’ + @CertificateName + ‘_CERTIFICATE_BACKUP_PHRASE’
1158.    EXEC dbo.GetValueByName @BackupValueName, @BackupPhrase OUTPUT;  
1159.    SET @KeyValueName = @DbName + ‘_’ + @CertificateName + ‘_CERTIFICATE_ENCRYPTION_PHRASE’
1160.    EXEC dbo.GetValueByName @KeyValueName, @KeyPhrase OUTPUT;  
1161.    SET @RestoreDDL = ‘USE [' + @DbName + '];’ + SPACE(1)
1162.                    + ‘CREATE CERTIFICATE ‘ + @CertificateName + SPACE(1)
1163.                    + ‘FROM FILE = ”’ + @BackupPath + @BackupName + ‘$(PUBLIC_KEY_BACKUP_EXT)”’;
1164.    IF @PrivateKeyEncryptionType <> ‘NA’
1165.     BEGIN
1166.      SET @RestoreDDL  = @RestoreDDL  + SPACE(1) + ‘WITH PRIVATE KEY’ + SPACE(1)
1167.                       + ‘(FILE = ”’ + @BackupPath + @BackupName + ‘$(PRIVATE_KEY_BACKUP_EXT)”’ + SPACE(1)
1168.                       + ‘, DECRYPTION BY PASSWORD = ”’ + @BackupPhrase + ”” ;
1169.      IF @PrivateKeyEncryptionType = ‘PW’
1170.       SET @RestoreDDL = @RestoreDDL + ‘, ENCRYPTION BY PASSWORD = ”’ + @KeyPhrase + ””;
1171.      SET @RestoreDDL = @RestoreDDL + ‘);’;
1172.     END
1173.    EXEC @return_code = sp_executesql @RestoreDDL;
1174.    IF @return_code <> 0
1175.     RAISERROR(2147483632,16,1,@DbName,@CertificateName,@return_code,‘restore’);
1176.    ELSE
1177.     RAISERROR(2147483631, 0,0,@DbName,@CertificateName,‘restore’);
1178.    INSERT dbo.CertificateBackupHistory
1179.      ( DbName
1180.      , CertificateName
1181.      , BackupName
1182.      , BackupPath
1183.      , Action
1184.      , Status
1185.      , Thumbprint
1186.      , PrivateKeyEncryptionType)
1187.    SELECT @DbName
1188.         , @CertificateName
1189.         , @BackupName
1190.         , @BackupPath
1191.         , OBJECT_NAME(@@PROCID)
1192.         , ‘Complete’
1193.         , @Thumbprint
1194.         , @PrivateKeyEncryptionType;
1195.   END TRY
1196.   BEGIN CATCH
1197.     INSERT dbo.CertificateBackupHistory
1198.       ( DbName
1199.       , CertificateName
1200.       , BackupName
1201.       , BackupPath
1202.       , Action
1203.       , Status
1204.       , Thumbprint
1205.       , PrivateKeyEncryptionType
1206.       , ErrorData)
1207.     SELECT ISNULL(@DbName,”)
1208.          , ISNULL(@CertificateName, ”)
1209.          , ISNULL(@BackupName,”)
1210.          , ISNULL(@BackupPath,”)
1211.          , OBJECT_NAME(@@PROCID)
1212.          , ‘Error’
1213.          , ISNULL(@Thumbprint,0×0)
1214.          , ISNULL(@PrivateKeyEncryptionType,”)
1215.          , (SELECT ERROR_NUMBER() AS ErrorNumber
1216.                  , ERROR_SEVERITY() AS ErrorSeverity
1217.                  , ERROR_STATE() as ErrorState
1218.                  , ERROR_PROCEDURE() as ErrorProcedure
1219.                  , ERROR_LINE() as ErrorLine
1220.                  , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
1221.   END CATCH
1222. END
1223. GO
1224. IF OBJECT_ID (N’dbo.CertificateBackupsByThumbprint’) IS NOT NULL
1225.    DROP PROCEDURE dbo.CertificateBackupsByThumbprint
1226. GO
1227. /******************************************************************************
1228. **    Auth: Bill Wunder
1229. **    Desc: list all certificate backups for the provided thumbprint
1230. *******************************************************************************/
1231. CREATE PROCEDURE dbo.CertificateBackupsByThumbprint
1232. ( @Thumbprint VARBINARY(32) )
1233. WITH EXECUTE AS CALLER, ENCRYPTION
1234. AS
1235. BEGIN
1236. SELECT ServerName
1237.       , DbName
1238.       , CertificateName
1239.       , BackupName
1240.       , BackupPath
1241.       , Action
1242.       , Status
1243.       , Thumbprint
1244.       , PrivateKeyEncryptionType
1245.       , ErrorData
1246. FROM dbo.CertificateBackupHistory
1247. WHERE Thumbprint = @Thumbprint;
1248. END
1249. GO
1250. – dogfood certificate encryption secrets to dbo.NameValue
1251. – backup the hierearchy, verify the secrets
1252. DECLARE @Name NVARCHAR(128)
1253.       , @Value [NVARCHAR] (128)
1254.       , @IsValid BIT
1255.       , @DbName NVARCHAR(128);
1256. SELECT @DbName = DB_NAME();
1257.  
1258. SELECT @Name = ‘SMK_BACKUP_PHRASE’
1259.     , @Value = ‘$(SMK_BACKUP_PHRASE)’;
1260. EXEC dbo.BackupServiceMasterKey @Value;
1261. SET @Value = NULL
1262. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
1263. SET @IsValid = NULL
1264. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
1265. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
1266.  
1267. SELECT @Name = ‘master_DMK_ENCRYPTION_PHRASE’
1268.      , @Value = ‘$(master_DMK_ENCRYPTION_PHRASE)’;
1269. EXEC [dbo].[AddNameValue] @Name, @Value;
1270. SET @Value = NULL
1271. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
1272. SET @IsValid = NULL
1273. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
1274. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
1275.  
1276. SELECT @Name = ‘master_DMK_BACKUP_PHRASE’
1277.      , @Value = ‘$(keyback_DMK_BACKUP_PHRASE)’;
1278. EXEC dbo.BackupDatabaseMasterKey ‘master’, @Value;
1279. SET @Value = NULL
1280. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
1281. SET @IsValid = NULL
1282. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
1283. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
1284.  
1285. SELECT @Name = DB_NAME() + ‘_DMK_ENCRYPTION_PHRASE’
1286.      , @Value = ‘$(keyback_DMK_ENCRYPTION_PHRASE)’;
1287. EXEC [dbo].[AddNameValue] @Name, @Value;
1288. SET @Value = NULL
1289. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
1290. SET @IsValid = NULL
1291. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
1292. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
1293.  
1294. SELECT @Name = DB_NAME() + ‘_DMK_BACKUP_PHRASE’
1295.      , @Value = ‘$(keyback_DMK_BACKUP_PHRASE)’
1296. EXEC dbo.BackupDatabaseMasterKey @DbName, @Value;
1297. SET @Value = NULL
1298. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
1299. SET @IsValid = NULL
1300. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
1301. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
1302.  
1303. – Master Key encrypted certs need the back file encryption password
1304. SET @Name = ‘master_$(TDE_CERTIFICATE_NAME)_CERTIFICATE_BACKUP_PHRASE’;
1305. SET @Value = ‘$(TDE_CERTIFICATE_BACKUP_PHRASE)’;
1306. EXEC dbo.BackupCertificate ‘$(TDE_CERTIFICATE_NAME)’, ‘master’, @Value;
1307. SET @Value = NULL;
1308. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
1309. SET @IsValid = NULL;
1310. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
1311. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
1312.  
1313. – password encrypted certs also need the private key encryption password at time of export
1314.  
1315. SET @Name = DB_NAME() + ‘_$(VALUE_CERTIFICATE_NAME)_CERTIFICATE_ENCRYPTION_PHRASE’;
1316. SET @Value = ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’;
1317. EXEC [dbo].[AddNameValue] @Name, @Value;
1318. SET @IsValid = NULL;
1319. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT;
1320. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
1321.  
1322. SET @Name = DB_NAME() + ‘_$(VALUE_CERTIFICATE_NAME)_CERTIFICATE_BACKUP_PHRASE’;
1323. SET @Value = ‘$(VALUE_CERTIFICATE_BACKUP_PHRASE)’;
1324. EXEC dbo.BackupCertificate ‘$(VALUE_CERTIFICATE_NAME)’, @DBName, @Value, ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’;
1325. SET @Value = NULL;
1326. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
1327. SET @IsValid = NULL;
1328. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
1329. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
1330.  
1331. GO
1332. /*
1333. SELECT * FROM [dbo].[MasterKeyBackupHistory];
1334. SELECT * FROM [dbo].[CertificateBackupHistory];
1335. SELECT * FROM [dbo].[NameValues];
1336. SELECT * FROM [dbo].[NameValueHistory];
1337. SELECT * FROM sys.dm_database_encryption_keys
1338. */
1339.  
1340. /* UNINSTALL
1341. remove any backup files created – find path + name
1342. SELECT * FROM [dbo].[MasterKeyBackupHistory];
1343. SELECT * FROM [dbo].[CertificateBackupHistory];
1344.  
1345. USE keyback;
1346. ALTER DATABASE keyback SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
1347.  
1348. USE master;
1349. DROP DATABASE keyback;
1350. EXEC sp_dropmessage 2147483631;
1351. EXEC sp_dropmessage 2147483632;
1352. EXEC sp_dropmessage 2147483633;
1353. EXEC sp_dropmessage 2147483634;
1354. EXEC sp_dropmessage 2147483635;
1355. EXEC sp_dropmessage 2147483636;
1356. EXEC sp_dropmessage 2147483641;
1357. EXEC sp_dropmessage 2147483642;
1358. EXEC sp_dropmessage 2147483643;
1359. EXEC sp_dropmessage 2147483644;
1360. EXEC sp_dropmessage 2147483645;
1361. DROP CERTIFICATE TDECertificate;
1362. DROP MASTER KEY; — only drop master’s Master Key if it was added by this script!
1363. */

(The template can also be downloaded here if you’d like to check it out but would rather not copy/paste 1000+ lines of T-SQL. An auxiliary test script is here.)

SQL Server Encryption Hierarchy Overview

In a basic configuration, a SQL Server’s Database Master Keys (DMK) are encrypted by the Service Master Key (SMK).  Certificates – always at a database scope – are encrypted by the DMK of the containing database. The SMK is always encrypted by the SQL Server Service Account user name and password. The EH may also properly include certificates originated externally from Assemblies or Hardware Security Devices HSD – either by loading certificates from files or using Extensible Key Management (EKM) in more advanced configurations.

---

If the SQL Server service account is changed, plan for enough time to decrypt and re-encrypt the Service Master Key and everything encrypted from it!  Use ALTER SERVICE MASTER KEY to move the key to the new account. When Database Master Keys are changed, plan for adequate time for the encryption hierarchy within the database to refresh using the new key. Use the REGENERATE option of  ALTER SERVICE MASTER KEY or ALTER MASTER KEY to begin the refresh of the hierarchy of encrypted data using the changed key. 

---

A password is required to encrypt any Encryption Hierarchy backup file. When backups are done using the stored procedures created from the template, the passwords are encrypted and stored along with the audit history in the selected user database. Members of the keybackAdmin database role can export, import or regenerate master keys at will – being a sysadmin alone is not enough, you must also be a member of the keybackAdmin role. Carefully restrict the membership. Non keybackAdmin sysadmins can still corrupt the database and they can still backup encryption hierarchy nodes using ad hoc methods, but the ability to recover secrets is far less likely. Consistently storing secrets used to encrypt the encryption hierarchy objects and secrets used to backup the object are both necessary in order to be able to restore using the keyback stored procedures. 

BACKUP and RESTORE syntax for the SMK and any DMKs are pretty straight forward. The password or phrase to encrypt the backup is always required. Database Master Keys may also have been encrypted with a password – instead of – or in addition to – the Service Master Key. Certificates’ have even more options to consider. The certificate may have been imported and have no dependency upon the database Master Key. The private key may have been intentionally dropped.

I recommend Master Keys and Certificates are not backed up routinely.  There is nothing in the template that should be scheduled to run once a day, week or even month in every database on the server. Instead, Encryption Hierarchy Management should be a part of the software lifecycle change process. Save any passwords/phrases to the encrypted NameValue store at the time the keys and certificates are created or changed.  Backup keys and certificates when created or changed, then monitor for unexpected change, get that secure offsite location up and running, and establish a test environment for use to restore and validate your keys held in backup. You may even want to consider detaching the database when not in use. Under TDE or file system encryption this will be more secure than leaving the idle database for window shoppers…

As you use the template you may find a need to customize it to suit your needs. Go for it. It is a template. As with restores, just be sure to test your changes before you throw them at a live SQL Server or at an existing keyback data set.

Executing a script built from this template will establish an Encryption Hierarchy secret store and logging database – keyback is the database name I use – and then dog food backups of the pieces of the Encryption Hierarchy of the master and keyback database that are used by the tool to the same folder as the .mdf for the database. The Encryption Hierarchy is backed up, the secret store is validated, and you get to see the tool in action.

Suggested Template Deployment

Add the script to the template library of the clients used to work from the Central Management Server for the environment.

When you load the script into a SQL Server Management Studio query window – whether or not is has been saved to the template library, hit CTRL+SHFT+M to see the parameters you must provide to complete the template.

The values with defaults can be changed or you can use the default, the secrets have no defaults. Enter words or phrases that meet the password strength requirements of the local environment.

Choose any existing database or specify a database name that does not exist and the database will be created.

In addition to creating the database the script will:

• Backup the Service Master Key
• Create – if necessary – and backup the Database Master Key for the master
• Create and backup a certificate in master for use in TDE of the keyback database
• Create and backup the Database Master Key for the keyback database
• Create a Database Encryption Key in the keyback database (SQL 2008 Enterprise, DataCenter and Developer editions only) You my want to use file system encryption such as TrueCrypt if your SQL Server instance is not TDE capable. I wrote a bit about that in a previous post. With file system encryption the key or certificate backup is better secured from the moment it is created.
• Create and backup a certificate used to encrypt the password/passphrases use in administration of the Encryption Hierarchy.
• Encrypt and save all secrets used in the script. This means you do not need (ought never?) to save the script once your secrets are entered into the template.

The keyback database also includes some executable objects all of which are encrypted to discourage tourists. You can remove the encryption if you like before you replace the template parameter tokens.

Scalar functions to support a standard backup file naming standard

--returns the path to the .mdf of the Db but not the .mdf file name
dbo.NewBackupPath @DbName
--returns a string with embedded timestamp to uniquely identify backup files 
dbo.NewCertificateBackupName @DbName, @CertificateName
--returns a string with embedded timestamp to uniquely identify backup files 
dbo.MasterKeyBackupName @DbName

 

Scalar functions for SQL Injection filtering and elevation of authority attacks. You can flush them out in the template.

Stored procedures for saving and retrieving secrets. You can make the secrets as strong as you like ‘em! I limit to NVARCHAR(128) but that can be changed easily if you need more.

-- every secret needs a name and a value
-- the @Value is stored in an encrypted columns 
-- the @Name is always used as the encryption authenticator  
dbo.AddNameValue @Name, @Value
dbo.GetValueByName @Name, @Value OUTPUT
dbo.ValidateNameValue @Name, @Value, @IsValid OUTPUT

 

Stored procedures for use in backup up any and all Master Keys and Certificates on the server.

-- @BackupPhrase encrypts the backup file
-- @KeyPhrase decrypts a certificate's private key before backup
dbo.BackupCertificate @CertificateName, @DbName, @BackupPhrase, @KeyPhrase(optional)
dbo.BackupDatabaseMasterKey @DbName, @BackupPhrase, @KeyPhrase(optional)
dbo.BackupServiceMasterKey @BackupPhrase

 

Stored procedures for use in restoring Master Keys and Certificates that have been previously backed up by the tool . This tool is intended for use only when restoring a backup taken using the tool. Keeping the backups up-to-date is quite important. The thing is, you will never know what day you will need them until the day you need one.

-- often all the information a message provides about a missing certificate is the thumbprint
dbo.CertificateBackupsByThumbprint @Thumbprint

  -- these guys are made simple to helpful in an emergency
  -- always gets the most recent backup of an object
  dbo.RestoreCertificate @CertificateName, @DbName
  dbo.RestoreDatabaseMasterKey @DbName
  dbo.RestoreServiceMasterKey

Successful Execution of the script produces output something like this. The warning message (2) is a system generated message that will only be seen when the database Encryption Key is created. If, for example, the key already exists, this message is not seen.

Typical Script Output

Protect all Encryption Hierarchy backup files and maintain a copy in a secure offsite location.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

keyback info – Service Master Key backup complete.

keyback info – database master Master Key backup complete.

keyback info – database keyback Master Key backup complete.

keyback info – database master certificate TDECertificate backup complete.

keyback info – database keyback certificate ValueCertificate backup complete.

 

Creates backups of the newly created Encryption Hierarchy:

And records a complete history of all actions taken or attempted (clicking the image should open it in a bigger view).

In environments where the sysadmin membership is greater than 2 or 3, the storage location (BackupPath and BackupName) should also be encrypted. The membership in the keybackAdmin role should definitely be limited to 1 individual in all cases. Accountability is essential. Instead of two people sharing one keyback, consider having both establish separate keyback monitoring protocols. Other means to improve security when considered in the context of the local environment where the template is applied should also be considered. The stronger the layers of security around the secret store database, the better.

If you can recreate the TDE and reliably produce the keyback_ValueCertificate_CERTIFICATE_ENCRYPTION_PHRASE when needed, the likelihood that any Encryption Hierarchy Backup in the collection can be reliably restored is quite high. Under normal operation there will be no need to either recreate the TDE or manually enter the ValueCertificates’s  encryption password.

Enjoy!

The Emperor’s New Transparent Data Encryption

Translucent Databases  (9780967584416) by Peter Wayner (2002) describes a database environment that reaches for security by separating information from data. Sort of a Hotel SQL Server: the data goes in but the information cannot get out. The result is an encryption driven environment where no single view inside or outside of the organization exposes all the data in the database in an unencrypted state at one time. The land where only the hash of your password is buffered, passed or persisted. Never the password. (Imagine! You, and of course that dude across the room that can memorize any 50 keystrokes you type from across the room, are the only one(s) that know your passwords! Yes there are such people…) A place where different parts of the data are encrypted with different keys, and keys belongs to disparate organizational units. Where the DBA sees a customer table with mostly VARBINARY columns and perhaps other somehow index-able values that the various key authorities or application layer indexers have generated as that authority encrypts data. Wayner, in 2002 called it Translucent Data Encryption. Was he way off base since we have not vigorously pursued the mechanisms he postulated? I don’t think so. Instead, what has happened is the standards we strive for are too weak. Furthermore, organizations fear security strategy that works because they fear the secure environment will be grinding slow and unusable, so they default to compliance. And then the organizations point helplessly to the standards while being hacked mercilessly: giving up our personally identifiable information in the process.

I suspect that RSA and Siemens and Sony and Citi and Sega and all the others were not far out of compliance with current standards yet millions of people have had pieces of their identity stolen recently in the small number of disclosed events. One is left to ponder just how many organizations are dishonest enough not to disclose breaches. (Update February 2, 2012 – you doubt it? VeriSign admits they were “successfully hacked several times in 2010” but saw no need for the admission until today. Wonder when they will get around to telling us about what happened in 2011? There must be some reason they decided to come clean all of the sudden.) Not that big of deal to disclose I suppose. The only recourse we have is whether to save, trash or recycle the sympathy card from the organization that may have just cost a customer a lifetime of financial misery. Why have organizations accepted the weak standards? Why do the organizations now verbalized that that strong security is a pipe dream? hmmm….

“Translucent Databases: A Precursor to Privacy Sensitive Databases (.pdf)“, a paper I stumbled on recently by students Palaka Bhattacharya and Morteza Karya from an advanced survey of security course at Harvard from 2004 is drawn upon Wayner’s ideas. Database scenarios for cancer research, rape victims and a suggestion box are thoughtfully and creatively considered in the paper. Several good ideas about extending the Translucent Database to the specific security requirements particular to personally identifiable information are described. That students in a survey course can so lucidly map a convergence between the real world problems and the concepts from a book says good things about the students to be sure, something about the validity of the concepts in the book, and something about how little real progress we’ve made in the last decade on data security and in particular the ever-shrinking respect for the privacy of the customer/user.

Here we are many years later:

• People steadfastly clinging to an obviously false belief that privacy policies are there to safeguard our data and that we are using best of breed solutions on problems that we must ultimately accept as unsolvable. Those privacy policies often do just the opposite. Facebook is a good example of an identity puppy mill. Google is no better. I can’t prove it from here but I am quite certain I got clobbered with a man-in-the-middle exploit when I linked my WordPress Blog to my Linked-In account. Strangely, the next day my yahoo email contact list was compromised (fixed it with a password change).
• Organizations continuing to pretending that compliance & self regulation is as good as security gets. What they really know all it is is as cheap as it gets. Real security infrastructure costs considerably more than the false front we see. Sadly, in too many cases all that is behind the false front is a couple of long wobbly sticks wedged to the ground to prop it up.

As a bit of proof that we aren’t that good at even recognizing good security standards,  I offer up the message I receive when I try to upload to the BLOG from the Windows Live Write client when the local Security Policy is checking for FIPS compliance:

WindowsLiveWriter,1.8800,Fail,00025,22-Jun-2011 16:43:05.969,

"System.TypeInitializationException:

 The type initializer for 'WindowsLive.Writer.CoreServices.HttpRequestHelper' threw an exception.

 ---> System.TypeInitializationException:

      The type initializer for 'WindowsLive.Writer.CoreServices.WsseAuthenticationModule' threw an exception.

      ---> System.InvalidOperationException:

      This implementation is not part of the Windows Platform FIPS

      validated cryptographic algorithms.

at System.Security.Cryptography.SHA1Managed..ctor()

at WindowsLive.Writer.CoreServices.WsseAuthenticationModule..cctor()

 

The fix: disable the FIPS compliance policy to upload to the blog. Not sure if  other Microsoft Cloud technology will have this problem, have not tested it.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Transparent Data Encryption (TDE) is an encryption technique offered first for Oracle 10g Databases then later introduced into Microsoft SQL Server 2008. SQL Server TDE encrypts only at the database level while the Oracle TDE can also handle column and tablespace encryption strategies. TDE is included only in SQL Server 2008 Enterprise, Data Center, and of course Developer flavors. Correct me if I am wrong, but I think you can add Oracle TDE to any 10g/11i database but it is priced separately. I mention this competition to help establish why SQL Server even offers TDE and also clarify why the technology is available only in the high end SQL Server SKUs. I think it fair to say TDE came to SQL Server as “me too” technology.

---

“me too”

How corporate competition stifles software innovation

Say Company O has a product in wide use. Company M enters the market with a lower cost, easier to use product that serves almost the same purpose for the customer but with fewer and different features and without interchangeable compatibility with Company O’s product. Instead of purchasing the less expensive product, customers tell Company M’s sales staff that the product is just not as robust as Company O’s product. The sales staff collects the information and funnels it back to Company M HQ. The potential customers either choose not to switch from Company O’s product or to purchase from Company O instead of Company M in more cases than not. As things get desperate, Company M’s sales & marketing folks initiate ever more urgent and decisive enhancement requests for the ‘features needed to compete’! The risk assessment and/or cash flow and/or leadership egos do not allow time for vibrant R&D and anyway, the sales staff is adamant that the risk of upsetting customer’s with educational requirements for new or significantly different product features is too great. Development becomes fixated on delivering a functional – even if not yet fully usable – version 1 feature set to match targeted competitor features. The strategic purpose is to be seen as an equal competitor to Company O – at least in the silly mirror.  The message from leadership is that this vacuous short term play will solve the direct and immediate threat to long term product viability. Thus leaving the technology staff no choice but to make hay of it. Before you know it, when customers complain, account managers can say  we have that feature too now. At this point the commonly practiced “wait for SP1″ avoidance behavior of the (ill-conceived?) feature(s) can begin. Eventually a sufficiently robust competitive feature list evolves while the competitor moves on as well.

The time to deliver those iterations is taken from time available for any other R&D investments. In this way,  free market competition leads to a product targeting a competitor’s  feature list rather than any real customer need and time available for true innovation is diminished product wide. The net effect will hopefully include increased short term earnings for corporations and, unfortunately,  fewer real choices for consumers of the product.

There is likely no way to even make a good correlation between “me-too” R&D and mid or long term benefit to the corporation. Nonetheless, a business strategy that aims to beat others at their own game is risky, expensive, and ultimately counter-productive. It’s the opposite of a game changer.

---

(Ok, I’ve already exceeded my knowledge of Oracle’s TDE so I will limit my remarks to Microsoft SQL Server’s TDE from here.)

SQL Server 2008 Transparent Data Encryption

The Encryption Hierarchy is equally available in all SQL Server SKUs. However, TDE is included only in Enterprise, Developer and Data Center editions. TDE is an emerging version 1 “me too” technology. It has seen a couple service releases. Predictably, the adoption rate was delayed at introduction but is now picking up some steam. Fortunately, I suspect there is not a lot of code involved in TDE so the unexpected consequences for jumping in early were small. Roy Ernest found a few good ones. Given the lack of disaster stories – somewhat confirming that the code changes were not large – I can only speculate as to why the technology in not available in all $KUs. The additional logic is to implement CryptoAPI hooks into SQL Server Buffer Pool management and plop in a CREATE and ALTER DDL statement – but no DROP – to manage the Database Encryption Key (DEK) stored in a small and always unencrypted space within the encrypted database. If you are the DBA and you enable TDE and manage the keys – or nobody manages the keys – don’t expect your risk exposure to go down by much. Most of what TDE is about is removing implicit need to trust DBAs and other administrators. What DBAs can benefit from is the always encrypted backups from a TDE database and as result a mechanism to allow the organization to better control who can use that encrypted backup and where. The other main protection that TDE provides – data at rest encryption – is pretty close to never in play for a service like SQL Server that is typically running 24×7, has a lock on the file at all times, and exposes all data only in unencrypted form to all user database connections while running anyway. Most of what you need encryption for – protecting sensitive data – cannot be done with TDE. Lets face it, TDE is a low cost compliance alternative or supplement to proven application layer encryption, hashing and cryptographic signing approaches to data protection.

Compliant corporations absolve liabilities, litigate and lobby. Secure corporations  protect your personally identifiable information with all their might.

SQL Server TDE occurs in low level IO processing. The data is encrypted just before the bus ride to secondary storage and then decrypted just as it transits back to primary storage. The implementation should benefit from XEON encryption acceleration. The documentation state that TDE uses the, “Windows Cryptographic API (CAPI)”. Interestingly, the number one hit in my Google for, “Windows Cryptographic API (CAPI)” is a sardonic wikipedia entry who’s author(s) seems to have shared my longstanding confusion about all these names for Microsoft Cryptographic technologies being bantered about. The second result is the link to the “Microsoft CryptoAPI“. Not one nor the other ever mentions or uses the phrase “Windows Cryptographic API” – not even the one that shows a long list of synonyms for the CryptoAPI. Lucky for me, the search engine seems to know what I want. I think this is a case where the SQL Server Documentation adds confusion where there was already enough. (It’s almost like the documentation team was trying to make the Wikipedia volunteer look ill-informed by using a name not listed. They are coming after me next I sure.)

The main benefits of TDE over other encryption techniques are:

• the encryption requires no modification to the application
• the encryption is generally recognized as compliant to PCI-DSS Data at Rest requirements
• TDE database backups are also encrypted using the DEK
• no data is exposed as clear text if files are copied, stolen or probed in-situ - unless the probing is done via a SQL Server connections, in which case all data is exposed as clear text.

Nothing about the data being better protected from application level attacks!? This is because TDE exposes only decrypted data to all application and ad hoc user connections to the database. Authenticated users connected to the database – whether for legitimate or malicious purposes – see decrypted data in all query results. There is no option to view the cipher text. The encryption is at the page level so the page must be decrypted to even find out much about the rows on that page I would imagine. There is no need for the user to provide credentials other than those presented for a ‘normal’ connection without TDE enabled. From the outside world the database is the same as it ever was. The data is encoded as it moves from primary, i.e. SQL Server data buffers to secondary storage, i.e. a RAIN (redundant array of independent NAND) of 750,000 IOP Micron p320hs SSDs or a few direct attached storage enclosures stuffed with commodity SAS drives or a zone of Brocade 96 gigabits per second encrypted LUNs.

The Database Encryption Key (DEK) is the core secret of TDE. It is not mentioned in any catalog views and is not accessible in the same way as other keys in the database. From Books Online:

“The database encryption key cannot be exported from the database. It is available only to the system, to users who have debugging permissions on the server, and to users who have access to the certificates that encrypt and decrypt the database encryption key.”

There should be no reason for application user’s to access the DMK or DEK.  Execution of all DMK DDL requires CONTROL permission on the database and VIEW DEFINITION permission on any asymmetric key or certificate mentioned in the DDL. If any application user requires CONTROL of the database they inherit control the DEK. Application users requiring elevated database permission increase the of risks of a breach. (HINT: Applications that require CONTROL of the database for anything are not secure by design.)

If all the application does at the database is INSERT, UPDATE, SELECT and maybe DELETE data, yet has authority – explicitly or implicitly – to mess with the DEK then consider tuning the security dial toward the principal of least privilege. IMHO, application users should not have direct INSERT, UPDATE, or DELETE table permissions. This is even more true when the data is at all sensitive or private. Use stored procedures instead. Even for SELECT queries stored procedures are recommended – prepared statements are almost as good but the dependency chain is strongest when only stored procedures are used and they need direct table permission.  With stored procedures, the only permission the application user needs is EXECUTE on the procedure.  The application user should never own tables, schemas or databases. Only the rarest of application users should be a member of any Fixed Server Role. The access level of the application users must be carefully considered because application users are the most likely way bad guys will initially access the system.

Any database user can still view/copy/modify any data maliciously that they are also able to view/copy/modify legitimately. This is equally true if the user identity is taken over for malicious use or if the user identity is otherwise compromised.  This is true whether the data store is protected by TDE or file system encryption (an alternative to TDE described below).  TDE encryption protects the data from a file copy attack and some other trust exploitation attacks. TDE also encrypts all backup of the database.  By transparent is meant: applications and ad hoc SQL Server connections never know the data at rest is encrypted. Chris Kranz, in “A NetApp Technical Diary”, blogs a similar concern about the real benefit of storage level encryption from a systems perspective.

All secrets and certificates that are used to configure the TDE must be protected in order to protect the TDE. There are more than a couple important secrets to protect. The actual number can vary depending up the tactics selected for encryption. If encryption provides a choice between a certificate and secret, most of time I choose the certificate. Ideally, the data administrator must provide one password and the HSM or key vault administrator must provide one other password in order to restore a TDE database backup anywhere but the SQL Server that generated that backup.

The architecture and configuration options for TDE are nicely covered in Zubair Ahmed Mughal’s post on “Database Encryption Key Management” in the MSDN SQL Server Security weblog. This should be in Books online.

The configuration options for hashing, signing and encryption of identified data values are not as easily condensed to a single link. Look for Raul Garcia’s posts in the SQL Server Security weblog for insights into SQL Server’s implementation. But even before you go there, a decision whether to encrypt in the application or in the database is needed. A developer/architect starting point is this “VISA BEST PRACTICES: Data Field Encryption” (.pdf)  that advocates , “data field encryption as a complement to, rather than a substitute for PCI-DSS compliance requirements”

As with the EKM + HSM solution, if encryption occurs at the point of data creation or entry, the data administrators need not know the unencrypted values to administer the data. She probably cannot help you much with searching or sorting based on that data values though. Further, the data administrator is not involved in the administration of certificates or secrets other than Certificates encrypted by database master keys .

---

TDE is useless against 90% of the attacks on your data!

---

Hopefully I have painted the picture enough to clearly show that TDE really isn’t so much protection in the grand scheme of things. According  to this 2009 ACE Team weblog post that provides 6 “Simple Rules to Stop the Bad Guys” 90% of attacks come at the application layer. TDE does nothing to stop that 90%. TDE targets some part of the other 10%. It’s strength is in embargoing the insider attack and securing the backup, but is only effective if the organization has the fortitude to truly protect the encryption key from the DBA and all other administrators. Few take the necessary steps. TDE in that context can help prevent data from walking out the back door. A DBA managed TDE DEK should decelerate the speed with which the back door swings wide open. Any careless use of TDE will increase the CPU load and can result in loss of data if a corruption issue requiring a restore from last known good backup is incurred only to have an untested restore process fail. The good news is, TDE is pretty much plug and play as long as nothing goes wrong.

Column level encryption, implemented in such a way that only authorized users can access the keys required to decrypt the column – perhaps in conjunction with TDE or file system encryption of the database file(s) – is a more realistic way to protect sensitive data at rest because it provides protection from application layer attacks. If the key is stored in the SQL Server it cannot be secured from all data administrator and may be more vulnerable to hacking. Keeping the encryption keys in a physical store that administrators of the database, database server OS and database subnet do not control will improve protection of the data from internal attacks. At a minimum, a good separation of responsibilities strategy would require that at least two admins be in cahoots to be able to steal a backup.

EKM enables separation of responsibilities by moving the certificate store to a Hardware Security Modules (HSM), and thereby breaking away from the single principle in control condition of the SQL Server Encryption Hierarchy. At least a few currently available HSMs advertise that they have the required Cryptographic Provider .dll for use with the SQL Server EKM.  I have no hands on experience with an HSM. Unless I am mistaken, the 5 HSMs reviewed in this Internet Infrastructure Foundation commissioned HSM review (.pdf) all serve keys to the EKM? A good place to jump in and learn a little anyway.

---

Are there alternatives when you do not have a TDE enable SQL Server SKU, cannot change the application, and yet have important and sensitive data that must be protected when at rest for compliance?

---

Lets set the bar low and say we want to be able to use SQL Server 2005 EXPRESS or better. Better meaning that the solution will also work on other SQL Server 2005 SKUs or any SQL Server 2008 SKU, or any SQL Server 2008 R2 SKU -only better because SQL Server 2005 EXPRESS is the poodle in the wolf pack.

Microsoft has published a white paper titled, “Database Encryption in SQL Server 2008 Enterprise Edition“. Mostly it is a yet another how-to on implementing TDE. Though it does give a bit of valid consideration to several options. The most interesting of those options is file system encryption.

Transparent File System Encryption -vs- TDE

One option to encrypt the file system is BitLocker integrated file system encryption included with Windows 7+ Vista + Server 2008. Or its predecessor the Encrypting File System in Windows 2000+XP-Pro+Server 2003, or even one of the 3rd party disk encryption software.  One vendor, gosecure, provides a nice comparison of their product to a couple other encryption software products with a list price and to TrueCrypt, an open source, free, on-the-fly and transparent encryption software that could even encrypt your MySQL databases that runs on a Linux box if you were so inclined. I’ll focus on TrueCrypt because I have actually used the product.

TrueCrypt allows three kinds encryption containers. Files, non-boot operating system logical drives, and the boot device. Encrypting the all drives including the boot device – and entering the strong password to complete the encryption key during each system startup – is the best encryption protection because the pagefile.sys and any installation and configuration files that get created on the boot device and do reveal clues about sensitive data are found on the boot device. This a far more protection than TDE affords. Encrypting all drives including the boot device also carries the most overhead. Encrypting non-boot Volumes is attractive in a SQL Server direct attached storage configuration. The optimal SQL Server configuration with separate physical RAID arrays for data, log, and tempdb could be encrypted in this configuration. On a SQL Server I would expect the addition of the boot device to the encryption scheme would not add so much burden except at start-up since the IO on a database server is almost all about the data cache. (If a SQL Server is writing to the OS paging file it is already in performance hell. If you can’t fix that problem, I recommend you not mess with even open source, open license encryption solutions until you lay your hands on adequate memory.)

A SQL Server database device cannot be encrypted by encryption software. Instead, the .mdf and .ldf files are placed in already created encryption containers. If tempdb is to be encrypted place it in a TrueCrypt devices as well. Another subtle difference is that anyone that can read from a mounted TrueCrypt drive can get to the unencrypted data. Not the encrypted container created by TrueCrypt, that is definitely encrypted. I’m referring to the drive letter that shows up in Windows Explorer when a TrueCrypt container is mounted in the file system. Although, anyone with enough access to initiate such access would have a much easier time of it and a lower risk of being caught using the same credentials and T-SQL.

Unlike BitLocker, TrueCrypt can encrypt the boot drive or a file container.  There is a nicely detailed comparison of BitLocker and TrueCrypt at Tom’s Hardware.

TrueCrypt exposes the decrypted data when the encrypted container is accessed through the mounted file system device. This is an intended characteristic of transparent encryption. Once the mounting process acquires a drive letter, the drive must be secured at least as well as the SQL Server installation. If you are a Member of the Super Users Group on the machine, when you mount the container file, the permissions could allow other Super Users to access your containers.

TrueCrypt VHDs must be mounted and dismounted in the correct sequence. For encrypted disk to protect the database, a commitment to detach the database with sensitive data or alternately shut down the SQL Server Service and dis-mount the TrueCrypt device when the database is not in use and then remount and re-attach (or restart as the case may be) on demand may be a good option. Easy enough to automate…

TrueCrypt-ed data can be viewed in clear text. The TDE encrypted database is presented to everyone that connects to the database as unencrypted data. This is also true when the disk is encrypted except that in addition, decrypted data can also be exposed through the logical file system device as unencrypted data – for example if the file is copied using the logical drive to unencrypted storage the encryption is removed. The way to move encrypted TrueCrypt data is to un-mount the logical device and copy the encrypted container file. This does not integrate well with SQL Server. Better to have a good plan to start. To be safe from prying eyes, the logical device must have very restrictive (principle of least privilege) ACLs . With BitLocker a pre-boot password is required. With TrueCrypt it may only mean that a file is mounted to the already running local system before the SQL Server can be started. Although TrueCrypt can also be configured to encrypt the boot disk – and would also require the pre-boot password in this context.

TrueCrypt cannot encrypt existing files. Nor can it encrypt a SQL Server data file. Instead, the SQL Server data file must be placed inside the TrueCrypt created file container after it is mounted. When mounted, the encrypted file is assigned a logical drive letter and is used like any other drive. Care must be taken that the same drive letters are used for each device else the attach script will need to be edited before each use.

TrueCrypt is open source and free. TDE is integrated with SQL Server. TrueCrypt container encryption does not use SQL Server in any way. In both cases the encryption algorithm, certificate and password are user specified. And in both cases the FIPs complaint AES encryption algorithms and SHA1 hashing algorithm are available for selection. (Why use anything else? You want to be compliant don’t you?)

TrueCrypt uses Intel’s Advanced Encryption Standard New Instructions (AES-NI) hardware acceleration. By some accounts AES-NI hardware acceleration is over 4x faster than without. No reason not to believe that even TDE is using the hardware acceleration although I do not see much propaganda to that effect. TrueCrypt identifies AES-NI capable CPUs and allows enable/disable configuration of AES hardware acceleration.

TrueCrypt is not integrated with SQL Server. I said this already. It deserves repeating because there are so many pros and cons to that condition. With TDE there is no intermediate step like mounting a file and starting the SQL Server as is required when a TrueCrypt file container is used. SQL Server can auto-starts with the OS as designed with TDE. TrueCrypt will prevent SQL server from starting and could result in a database being marked suspect if the service is able to start but the database file cannot be found. The TrueCrypt piece of the startup/shutdown process can be automated and can include the startup/shutdown of the SQL Server service if the automation user is able to start services.

TrueCrypt can prevent users with CONTROL of the SQL Server from overriding the encryption. The password needed to mount the TrueCrypt device could, for example, belong to the data center operations group (Ops) and Ops could be excluded from CONTROL of the server and from access to the database.

With TDE you could use the EKM Cryptographic Provider (.dll) from an HSM. TDE  with EKM in a configuration where the HSM administrator does not have access to database subnet or a login to the SQL server is the clear winner but the cost difference is very significant. The scenario where domain administrators do not control the SQL Server and the database administrators are not local administrators on the SQL Server box is almost achievable. (There is always a way for a local administrator to gain authorized access to a SQL Server. For this reason, it is better to entrust the key management  to a group that has no elevated authority as a database user, as a local system user, or on the domain and all other ACLs of the database subnet.)

As with my recommendation for TDE: if the SQL instance can work with an encrypted file system you may as well use it.  The main costs are a CPU overhead that will never bother most SQL Servers,  and the time the DBA and any external key store owners must invest to verify, document and test the various security configurations and restore scenarios.   

T-SQL Encryption 

That VISA document above says to consider encrypting the data for defense in depth along with TDE. As if data value encryption had little or no protection to offer or was at most a supplemental option. I’m here to tell you that is 100% backwards! And the TDE documentation seems to agree with me. I want to take it one step farther and state that encryption efforts must be redoubled in scope and hardness and TDE can be used as the supplemental technology to protect the database data at rest when the SQL Server is down. If we don’t roll up our sleeves and do as has been advocated by security experts and damn soon, surfing the Internet is going to feel like a ride on Charon’s boat. To be clear, the TDE documentation gets it right. The first sentence of the article, “Understanding Transparent Data Encryption (TDE)” states,

You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. …

Whether the SQL Server supports TDE or file encryption must be used to protect the data files and the backups, the increase level of security possible with data value level encryption is far more than the net security benefit of TDE or encrypted files. Most security guidelines recommend using transparent encryption along with data value encryption of identified sensitive values.

Encrypting at the application layer provides the most protection al la Translucent Data Encryption. Accurately identifying personally identifiable information seem to be tricky as well. You just have to do your homework. Do you care if the primary key to the customer table is exposed as clear text as long as the name, address and  or the news story BLOBS that are served up to all Internet users on demand? If you were dumb enough to build customer IDs that contain any meaningful tidbits – say a three letter prefix that can be used to group customers you just may have to encrypt the primary key to be secure. If all data access occurs by stored procedure then the encoding and decoding can pretty easily be done in the stored procedures, but I’m telling you this is almost for sure not the best way, even if it is better than not encrypting data.  At least the rest of the application will not have to know anything about the encrypted data, but you still have that problem with a single principal responsible for the encryption hierarchy.

In summary, until the focus is turned toward security and away from compliance in lieu of security expect many a profitable company to give up your personally identifiable data unabated and unrequited but don’t expect them to tell you it happened.

Protecting Sensitive Data at Rest in SQL Azure

“Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction. This cloud model promotes availability and is composed of five essential characteristics, three service models, and four deployment models.”  – The NIST Definition of Cloud Computing U.S. Department of Commerce National Institute of Standards and Technology (NIST)

The five characteristics:

1. On-demand self-service
2. Broad network access
3. Resource pooling
4. Rapid elasticity
5. Measured Service

The 3 service models:

1. Software as a Service (SaaS)
2. Platform as a Service (PaaS)
3. Infrastructure as a Service (IaaS)

The 4 deployment models

1. Private
2. Community
3. Public
4. Hybrid

Also in January 2011, NIST published a draft “Guidelines on Security and Privacy in Public Cloud Computing“.  4 broad guidelines are expanded in some detail in this document.

1. Carefully plan the security and privacy aspects of cloud computing solutions before
   engaging them
2. Understand the public cloud  computing  environment offered by the  cloud provider and ensure that  a cloud computing solution satisfies  organizational security and privacy requirements.
3. Ensure that the client-side computing environment meets organizational security and privacy requirements for cloud computing
4. Maintain accountability over the privacy and security of data  and applications implemented and deployed in public cloud computing environment

Even at this highest level, the enormous distance that must be bridged to bring SQL Azure into the organizational security strategy should be obvious.

It can’t be that bad. Can it? Well maybe… at issue is the compatibility of internally developed custom code based on long standing organizational security decisions and a dynamic resource on a public network. The disparity is huge. Sounds likes an interface with some new avenues of exploit for the malicious user base to me. Most of the holes should be filled in  few years. Just in time for the next wave…

Consider that SQL Azure:

• only knows SQL Authentication

Microsoft has long discouraged SQL Authentication because it is, well…, less secure. From the Books Online topic “Security Considerations for a SQL Server Installation” the security best practice recommendation is unequivocal, “Require Windows Authentication for connections to SQL Server.”

The SQL Azure documentation’s Development: How-to Topics includes Guidelines for Connecting to SQL Azure Database and several detailed documents for connecting to SQL Azure from a short list of Microsoft selected client application platforms. There is also a fairly detailed Technet document about “SQL Azure Connection String Security“.  Another very important item for SQL Azure is the .NET Protected Configuration Provider used to encrypt the login and password in connection configuration files – e.i. web.config, app.config, etc – on the client. (see “SQL Azure Connection Security” for more on Protected Configurations including a custom provider when the SQL Azure client is Windows Azure.)

The biggest concern I have with SQL Authentication is shared logins (see below) The second most concerning issue is password attacks. I do not know if SQL Azure will deal with a brute force password attack. I threw bad password at the connection from SSMS 10 times. The connection failed every time but it let me retry with no lockout.

• allows only 1 SQL Authenticated server level principal

not that there is much the server level principal needs to do:  backups, firewall configuration, create databases and manage logins. Probably won’t be more than… oh… say 6 or 8 people in a typical organization with the keys to the car. Anybody that thinks that that is secure absolutely must speak with me about keeping their money in my bank account.

• provides 2 Fixed Server Roles proxied through user roles in the master database

“Like the securityadmin role for an on-premise instance of SQL Server, the loginmanager role in SQL Azure Database is required in order to create logins.”

“The SQL Azure Database dbmanager role is similar to the dbcreator role for an on-premise instance of SQL Server.”

(I notice a slightly more exhaustive table that calls out some SQL Azure vs SQL Server differences in Managing Databases and Logins in SQL Azure.)

• requires firewall configuration

Before the first user, the server principal, can access a SQL Azure instance, that user must open the IP address of that user’s connection in the SQL Azure Firewall – and any necessary steps to open the path from the user side of the connection must be completed.

SQL Azure is available only through port 1433 and only by TCP.

The MicrosoftServices firewall rule was already in place on my SQL Azure instance before I signed is as the server principal. I am able to add and remove this rule, however I am not able to ascertain the consequences of either action. Also, the documentation tells me to add a second application rule, “Allow Windows Azure” to use my SQL Azure instance with my Windows Azure services.

select * from sys.firewall_rules

exec sp_set_firewall_rule N'MicrosoftServices', '0.0.0.0', '0.0.0.0'
exec sp_delete_firewall_rule N'MicrosoftServices' 
exec sp_set_firewall_rule N'Allow Windows Azure', '0.0.0.0', '0.0.0.0' 
exec sp_delete_firewall_rule N'Allow Windows Azure'

• does not support distributed transactions

• cannot be used in SQL Server Replication

SQL Azure does provide Sync services.

• does not allow impersonation or provide a local task scheduler or Service Broker

Impersonation by manipulating a scheduled task or handing work to a background process has long been an accepted way to circumvent security configuration for the on-premise SQL Server. Lesser privileged users to modify data in some table, the agent or broker picks up the data and executes a task restricted to higher privileged users: start a backup, add a login, TRUNCATE a table, etc. None of this will work with SQL Azure. More recently stored procedures could be marked WITH EXECUTE AS  to allow code to impersonate a more entitled user. This implicit sharing of database users will work Unfortunately, explicitly sharing database users will also  work…

• backup is un-like the sunny SQL Server backup

To backup a database the CREATE DATABASE statement is used. BACKUP DATABASE is not supported. The options are, you can create a database copy on the local SQL Azure instance or on another SQL Azure instance.

Use the sys.dm_database_copies view to see the currently active copies… er… I mean backups. OK, I have copy o my database. But how do I back it up? And what does the restore look like?

A well defined strategy is needed and you don’t get many building blocks with SQL Azure. Watch for some 3rd party cloud ware…

• Limited Cryptographic support

No Certificate store, no support for the SQL Server’s Encryption Hierarchy, no Transparent Data Encryption (TDE),  no Cryptographic Providers,  no EKM, and no CLR integration. Direct cryptographic support in SQL Azure looks pretty much limited to HASHBYTES and VARBINARY storage for already encrypted data.

You do have to load a key in order to access the instance, so some chance that key is used to protect data at rest, but I do not know is this is so or how it works.

Look ma, no cryptography!

The state of SQL Azure cryptography may actually be good where it is. Waiting until data reaches the database to encrypt is too long anyway. The longer encryption is postponed the less certainty that the data is not compromised. Furthermore, the more user friendly the encryption – to wit TDE – the easier to circumvent intended data protection or discombobulate expected recoverability. And the encryption is not fully protective. TDE offers no protection from SQL injection, cross-site scripting or in-flight attacks like man-in-the-middle. A TDE restore to a development environment will still inappropriately expose sensitive data and could easily compromise the Database Encryption Key. As long as a user can get a connection to the database instance, the data is transparently available in unencrypted form. From the opposite view, loss of data due to a missing or forgotten key export or key export password only adds to the woes of a data center during a calamity. Perhaps better to risk a breach than to risk a mis-step due to the added complexity regardless of the database weather conditions? (I don’t really believe that but it is worth thinking about.)

From another perspective, threats to cloud hosted data are notably different than threats to local network hosted data; not that either scenario should leave you with any feeling other than the creeps.  I really don’t know how a malicious cloud user behaves. Are there spiders crawling around the underlying infrastructure? Are there additional SQL Injection/cross site scripting benefits or risks. I do not know. I have a suspicion the cloud providers’ are building risk profiles from the by now most established trails as you read.

My SQL Azure instance doesn’t know what a certificate or a database master key is so is not likely that the SaaS service model would expose TDE any time soon. More likely the encryption requirement, and ultimately privacy compliance from the cloud would demand the Platform-as-a-Service Paas or Infrastructure-as-a-service Iaas service model or perhaps SQL Azure will become able to store certificates. A reasonable common sense alternative would be to encrypt the data in the application- where it originates – and unencrypt the data in the locally hosted or Windows Azure application – only where it is needed in unencrypted form. Store the encrypted bytes in an appropriate VARBINARY typed column in SQL Server or SQL Azure. End of problem.

Windows Azure can store Services keys (.pfx – public+private) and Management keys (.cer – the public key only from a public+private asymmetric pair). (protecting the private key is of utmost importance.)

Compliance Standards such as FIPS 140-2, the US standard for encryption certification or PCI-DSS, the credit card industry standard for protecting sensitive data include explicit requirements for protecting sensitive data at rest. Unfortunately, the standards may be ineffective. Sony’s ongoing (is it unstoppable?) breach and the RSA breach lost sensitive data from apparently compliant systems. Due diligence is in order. Consideration of the standards is prudent, but ultimately protecting the data must be the objective.

T-SQL  HASHBYTES() is a deterministic cryptographic hash function built from a block cipher – a randomly generated symmetric key. HASHBYTES() performs a a one way encryption or hash on the passed string parameter. The value is not intended to be decrypted. Block Ciphers are useful for authentication, data comparison and searching by digest (the hashed byte array output that provides a meaningless and 16 or 20 bytes values – depending on the hashing algorithm used). Using HASHBYTES() could eliminate much application logic that instead now uses the sensitive data directly.

Without a doubt the movement of sensitive data to public networks will increase demand for developers adept in current cryptography practices. The Technet article “SQL Azure Connection Security” includes best practices for writing secure applications and secure connection strings that use SQL Azure. There is quite a lot of information to be found concerning Windows Azure cryptography. The MSDN’s Security Resources for Windows Azure is great place to begin to dig deeper as is the MSDN Windows Development Security topic.