In the efforts to protect sensitive data there are some considerations that expand beyond its storage within database; more specifically, the backup files that are generated through the database backup process. Backup files are often kept on devices or media that are removed from the database server and stored in a separate location. If not properly secured these devices can be stealthily snatched away, exposing the backup files contained within.

If you were to open a backup file in a program like Notepad, your first impression is that the file contains a bunch of garbley-gunk resulting in the false perception that any plain text sensitive data in the backup file cannot be disclosed by simply scanning the raw backup file. In reality, the data in the database that is stored in plain text is not modified and appears in the backup files as plain text.

To see this for yourself, obtain a plain text value that has not been encrypted or hashed in your database, such as a credit card number or Social Security Number. Open your most recent backup file in an editor like Notepad, Wordpad or Textpad and perform a search for that value. You will discover that the sensitive data that is in plain text within the database also is in plain text in the backup file.

Microsoft SQL Server 2008, Enterprise Edition, introduced Transparent Data Encryption (TDE)  as a new feature that was designed to address this issue. This feature encrypts the physical files of the database. This includes any backup files that are generated through the native backup functionality.

The Database Encryption Key (DEK) is a key that was introduced to the encryption key hierarchy in support of the TDE feature. The DEK encrypts the physical files using one of the following algorithms:

• Advanced Encryption Standard (AES) using key length options of 128, 192 or 256 bit.
• Triple DES 3 Key which is an expanded key length version of Triple DES using a 192 bit key.

For a more in depth exploration of TDE and backup/recovery, check out my blog entry with the title "TDE: Under The Hood With Backup".

For those who do not have Microsoft SQL Server 2008, Enterprise Edition, or do not wish to implement TDE, the need to protect your database backup files remains. There are many third-party backup products available on the market that provide encryption to the backup files that they generate. Three popular options are:

LiteSpeed for SQL Server (Quest Software: Price not published)
This product encrypts backup files using:

• Advanced Encryption Standard (AES) which uses a 256 bit key.

The encryption feature of this product has been available for many versions. The current version of this product is 5.1 which added features such as the ability to define when to perform a differential or full backup, support for SQL Server 2000/2005 Standard Edition, native log shipping conversion and more. For more details on the latest release check out Quest Software's release information page.

SQL Safe Backup (Idera: $1,195 - estimated)
This product offers the user a choice of the algorithm used to protect the backup files. The available options are:

• Data Encryption Standard (DES) which uses a 64 bit key. (not recommended)
• Triple DES which uses a 168 bit key.
• RC2 which uses a 128 bit key.
• Advanced Encryption Standard (AES) which uses a 256 bit key.

The AES 256 bit key encryption option is new with its current version, 5.0, which became available on June 29, 2009. This version also provides log shipping as new functionality. For more details on the latest version of this product, check out Idera's "What's New" page.

SQL Backup Pro (Red-Gate Software: $795 - estimated)
This product encrypts backup files using:

• Advanced Encryption Standard (AES) algorithm with an option of 128 or 256 bit key length.

The encryption features of this product have existed since its initial release. Version 4 introduced the 256 bit key option. At the time of this blog entry the current version is 5.4. Version 6.0 is slated for release this Summer. This new release will include features such as handling of network outages during backup, "self-healing" log shipping, advanced compression algorithm and much more. For more details check out Red-Gate's fact sheet for the pre-release of version 6.0.

I was recently strolling through some of my old blog entries from my pre-Simple-Talk days for inspiration. I ran across an entry on the ISNUMERIC function that I thought that worth re-posting. Enjoy!

SQL Server 2005 Books Online describes the ISNUMERIC function as: "ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 indicates that expression can be converted to at least one of the numeric types."

To illustrate this, the following statement will return the value of 1 (true):

SELECT ISNUMERIC('486')

While the following statement will return the value of 0 (false):

SELECT ISNUMERIC('ABC')

There are some additional characters that return a positive response to the function that are not regularly considered numeric such as the dollar sign ("$"), the comma (",") and the period ("."). This occurs since they can be converted into other numeric data types. For example, the following statement will return the value of 1 (true) because it can be converted into a money data type:

SELECT ISNUMERIC('$4,860.00')

A strange thing happens when you introduce combinations of numbers and letters. Majority of the letters in the alphabet will return a 0 (false) when examined with the ISNUMERIC function; but when the letter "D" and "E" are introduced a 1 (true) is returned. On the surface, this occurrence may appear to be a bug, or inaccuracy of the function; but it is not.

Let's begin to explain this odd behavior by introducing scientific notation. Scientific notation is a method often used by Engineers, Mathematicians and Scientists to represent very large numbers in a more convenient and easier to read format. Without going into the detailed mathematician-speak regarding this notation please allow the following example to illustrate how scientific notation works: The number of 4,342,000,000,000,000,000,000 when scribed using scientific notation would appear as 4.342 x1021.

Working with scientific notation can be challenging for us Developers who must use a standard QWERTY keyboard to code our applications. Not to mention those who must document such numeric values in spreadsheets and scientific articles; thus, exponential notation was introduced to save the day.

A very simplified description of exponential notation is the representation of x10n with the letter of "E" and followed by the exponent and a character to designate positive and negative numbers. For example: 4.342 x1021 would be represented as 4.342E+21.

When programming those mega-numbers in SQL Server, and many other languages, using exponential notation returns a 32 bit signed single precision floating-point data type (also known simply as "single"). This means that if you exceed the range of -3.40E +38 through 3.40E +38, either truncation or error in conversion will occur. Try the following to illustrate the limitations of the single data type:

SELECT CONVERT(FLOAT(24),'3.40E+39')

The conversion to FLOAT(24), which is also known as a "real" number as well as a single data type, is too small to contain -3.40E +39 and returns the following error:

Arithmetic overflow error converting expression to data type real.

Replacing the letter "E" with "D" will force the scientific notation to return a 64 bit signed double-precision floating-point data type (also known affectionately as "double"). The use of "D" will extend the range returned from -1.79E +308 through 1.79E +308. This is very handy when calculating the expanse of the universe, counting atoms or keeping track of the National debt. Try the following to illustrate the expanse of the double data type:

SELECT CONVERT(FLOAT(53),'1.79D+308')

The conversion to FLOAT(53), which is a double data type, returns the exponential notation that is placed in the argument without error illustrating that the data type is large enough to store the data.
Based upon the definition of scientific notation and exponential notation you might expect the following statement to return 0 (false) because it does not follow the aspect that states that the coefficient (the number that precedes the "E") should be less than 10:

SELECT ISNUMERIC('1001D1')

Since the value can still be converted into a floating point number, the ISNUMERIC function will return 1 (true). This presents a challenge if the value "1001D1" is a serial number instead of scientific notation. If the intent is to evaluate this string to determine whether it includes a nonnumeric character, the consideration of the PATINDEX function would be recommended.

With the introduction of the regular expression "[^0-9]" as an argument to PATINDEX, the location of the non-numeric character is returned. If the value of 0 is returned, the string does not include any non-numeric values. An example of this function's syntax would be:

PATINDEX('%[^0-9]%', '1001D1')

Keeping this information in mind in regard to the tricky ISNUMERIC function and how to utilize the PATINDEX function as an alternate will save you so much time that you might just need to use scientific notation to represent the minutes available in your day for other activities such as sleep.

There are many good reasons to protect the sensitive data that is in your database: government regulation, corporate policy, managing legal liability and simply being a good steward of the information that has been entrusted to you. There are also industry standards defined and enforced by non-governmental agencies that dictate the management of sensitive data. The Payment Card Industry Security Standards Council (PCI) is an example of such an organization.

PCI is an organization that is comprised of representatives from American Express, Discover Financial Services, Master Card, Visa and JCB International for the purpose of establishment and enforcement of data security standards in regard to payment card data. It is through their data security standard (DSS) that they provide guidelines that aid in the consistent protection of this type of sensitive data.

In the PCI DSS there are twelve requirements that must be adhered to for any business that handles or stores data that is in relation to payment card data. Details of each of these twelve requirements can be found in their document titled: "Navigating PCI DSS".

For our interest as a DBA, the third requirement titled "Protect stored cardholder data" addresses the concerns of how the sensitive data that is involved in a transaction is stored. The PCI DSS specifically defines the following pieces of information as sensitive:

• Cardholder's name: This is located on the face of the card and identifies the owner of the credit account.
• Primary account number (PAN): This is located on the face of the card and identifies the account in which a payment is processed.
• Expiration date: This is located on the face of the card. This value indicates when the card expires.
• Service code: Found within the magnetic strip. Defines acceptance requirements for the card. This is a three or four digit number.
• Authentication data: This includes the full contents of the magnetic strip and the verification code which is located on the back of the card. The personal identification number (PIN) which is keyed by the card holder and contained within the magnetic strip is also considered in this category.

The development of a system in which payment cards are used requires careful consideration of the data that is to be stored within various means of storage be it a database, cookies, or cache. For PCI DSS compliance the items defined as "authentication data" cannot not be stored beyond the duration of a payment card transaction. This includes the full content of the magnetic strip, the verification code and the personal identification number (PIN).

The primary account number (PAN) can be stored after the payment card transaction; but it must be obfuscated through the use of encryption, truncation or other hashing methods. PCI DSS presents their requirements in a product agnostic manner, as they should. As Microsoft SQL Server DBAs one approach might be to encrypt the PAN by implementing the Transparent Data Encryption (TDE) feature of SQL Server 2008 rather than implementing encryption at the cell-level. If TDE is used then the access to the PAN must be through an account other than a local user account.

If the cardholder's name, service code and expiration date is stored with the PAN additional protection efforts are required for this data. Storage of these pieces of data independently from the PAN require no additional protection for PCI DSS purposes. Therefore, careful consideration of the storage schema of these elements can reduce the complexity of the storage of this information.

With the implementation of encryption comes the responsibility of key management. The importance of limiting the access to the keys that are used to decrypt the cipher text maintains the effectiveness of the encryption effort. Limiting the locations in which the key backups are stored not only ensures that when the need arises to restore a key that you have the most recent backup, it also is a way to limit who can obtain and restore the backup files.

Encryption keys are not a "set it and forget it" feature. PCI DSS requires that the keys used for encryption be changed at least once a year. SQL Server does not offer native functionality of key lifecycle management; although, in SQL Server 2008 the introduction of Extensible Key Management (EKM) does provide a means to integrate a third party product that provides this function.

On the surface an industry standard may not have as much teeth behind them as a government regulation. In some cases this may be true depending upon the goals of establishing the standard. In the case of PCI DSS the consequences to non-compliance is considerable. In addition to a $500K fine per incident of non-compliance, the business could be denied the ability to process payment card transactions which is a competitive vulnerability.

This was a cursory overview of requirement 3 of PCI DSS. To get more specifics on what can be done through SQL Server in regard to all of the PCI DSS requirements check out the one hour "SQL Server 2008 Capabilities for Meeting PCI Compliance Needs" webcast on TechNet.

Many of us play a specific role within a project. We may architect a schema based upon requirements that have been gathered by another person. Once the schema is complete, the specifications of that schema is passed on to another group who designs the user interface and reporting. Yet another group performs the final testing, implementation and maintenance of the solution that was developed. While we may be somewhat familiar with the tasks that proceed and follow our specific task there are always dimensions of those roles that we do not experience.

A participant in a project who has an appreciation and understanding of the context of their role will be a more effective one. Therefore; I approached Perpetual Technologies, Inc. (PTI) to see if they would be interested in co-hosting an event with IndyPASS where we would present the major aspects of a project and walk the audience through its paces giving them a holistic experience that they would not get otherwise. On Saturday, April 18th, in Indianapolis the vision became a reality.

With business intelligence becoming a very popular topic we agreed that our project of focus would be a business intelligence project. Our event consisted of five sessions:

• Gathering Requirements: Presented by Kristin Sheibley
• Building the Database Model: Presented by Ray Lucas
• Building ETL Processes: Presented by Arie Jones
• Building OLAP Cubes Using SSAS: Presented by Jung Choi
• Reporting Services: Presented by Arie Jones

Our approach to this event was to assemble a great team of experienced professionals to organize and develop the content of these presentations. The aforementioned presenters worked together amazingly well to provide the continuity between their presentations, provide technical review for each other's topics and develop the supporting storyline in which the technical aspects were presented. This approach to the presentation development was unique compared to most technical events; but was very effective with the team that we had assembled.

My partner in arranging the logistics of the event was Caroline Bailey from PTI. With her experience of organizing the various events that PTI offers throughout the year and her keen ability to keep all of the members of the team focused, the planning for this specific event was a smooth as butter.

We prepared a notepad-booklet in which we gave to each attendee that contained the schedule of the sessions, presenter biographies and plenty of space for taking notes. We utilized Lulu.com for the printing of these booklets. The quality of the printing was excellent and the price was equally impressive. I would certainly recommend them for anyone looking to produce booklets for their own events.

Our target was to host 100 attendees. Our no show rate was fairly low resulting in a total attendance of 95 in which majority arrived early and stayed late for the entire event. This was especially impressive considering that we had our first nearly Summer-like weather day in what seems to be ages. Wrox and O'Reilly provided business intelligence related books for SQL Server 2005 and SQL Server 2008 as give-aways at the end of each session which are always coveted by door prize seekers.

At the beginning of the event I encouraged all bloggers and tweeters in the audience to give a shout out to the event and share their experience. This is a great way to spread the word about our community's activities. To search on Twitter, go to: http://search.twitter.com 

Thank you to all that participated in the planning and attending this event.

In the energy circles there is a common reference to what is called the "carbon footprint". This is an individual's or business's direct contribution to climate change resulting in greenhouse gases. In the environmentalist circles there is the "ecological footprint". This measures the amount of land and sea that is used by an activity or population. Finally there is another measurement that references the amount of water used by an individual called the "water footprint".

The computing world, not to be left behind in the footprint trend, has what is called "digital footprint". This is the measurement of the data that is generated as the result of an individual's activity within a network, cell phone or on the Internet. This includes information or images that are directly posted when posting on your favorite social networking site. Your digital footprint even expands to the surveillance cameras that capture your image in public location. The more data that is captured from your digital activities, the larger your footprint. The larger your footprint the higher your risk of that information being observed by unintended parties.

EMC offers a digital footprint calculator that walks you through a series of questions to reveal a score. Ironically, you must download this item and install it before use. Another means to get an idea of your online digital footprint is to simply search for your name on your favorite search engine and see what comes up. When I search my name on Google, there are 1,570 pages returned. Not all of these pages pertain directly to me; but with a little reviewing and a little knowledge of me it is not difficult to identify the ones that do.

The effort of maintaining a small digital footprint does not imply that you must become a digital or social hermit. There are many benefits in utilizing the digital tools that are available to us. Pay close attention to the content of the digital information that you actively output. Brad McGehee has posted several blog entries in the past on developing your online brand. Seeing the data that is available online as a brand building process is an excellent way to manage your digital footprint.

Here are some ways you can reduce or improve the quality of your digital footprint:

• Avoid posting pictures on the Internet that you wouldn't show your parents or employer.
• Be cautious about the wording of critical statements or complaints.
• Avoid revealing information about your job or employer that is not public.
• Be cautious about the personal information that you share about yourself.
• Be cautious about the personal information that you share about your friends and family.
• Be cautious about sharing too much of your daily routines and travel plans.
• Utilize privacy settings on your browser or various web sites that offer them.

Remember that your personal information and reputation is an asset. Once damaged or exposed it is very difficult to control its use. Besides, it was George Bernard Shaw who once said: "The things most people want to know about are usually none of their business."

Federal identification numbers and credit card numbers are regulars when it comes to encrypting data. The US plain text version of the prior is eleven characters. The latter is typically sixteen characters. When these are encrypted using a symmetric key the size of the cipher text will range from thirty-nine to sixty eight bytes depending upon the selected algorithm. These lengths hardly raise the question of whether the cipher text will result in a breach of the size limit for a row.

In a nutshell, the row size limit in SQL Server can be explained as follows:

• The limitation of the row size is a page.
• A page comprises of 8 kilobytes (8,060 bytes).
• 8 kilobytes is approximately 8,000 characters.

There may be times when the data that is requiring encryption is very large. Book manuscripts, official documents, schematics, blue prints, images, and archived e-mail messages are just a few examples of data that are stored in a database and can easily be larger than the maximum row size.

Varbinary The Maximus
Encrypted data is stored in the database as a varbinary data type. When defining a column of this data type you have the option to define the size explicitly from 1 to 8,000. You can also define the size as "max" which is 2,147,483,647 bytes (2^31-1) which is approximately 266,437 pages.

The behavior of data that exceeds the row size limit when the "max" size option is utilized depends upon how the sp_tableoption system stored procedure is set for the table in question. If the "large value types out of row" option is set to the value of "1", the varbinary value will automatically be stored on a separate page whether the initial row has reached the row limit or not. If the option is set to "0" the data will be stored on a separate page only if it exceeds the row size limit. The subsequent pages that are used are linked to the initial page with a 16 byte pointer.

Using War and Peace
Leo Tolstoy's famed book "War and Peace" often is used as an example of a very long book. The English translation of this book contains approximately 3.1 million characters. This characteristic made it's text an excellent candidate for illustration of encrypting large text.

Consider the first chapter of this book which, in plain text, is 6,566 bytes in length. Using a symmetric key with the AES_256 algorithm, the results is a cipher text that is 6,612 bytes in length. This certainly fits within the row size limitations; but does not allow for much additional data to be stored within that same row.

Gotcha!
Based upon the definition and behavior of the varbinary(max) data type, the entire text of "War and Peace" should fit within approximately 388 pages. This is certainly within the boundaries of the "max" size limitations. Although, when attempting to encrypt the entire manuscript of this legendary book, using the very same symmetric key, we receive the following error message:

Msg 8152, Level 16, State 10, Line 13
String or binary data would be truncated.

The devil in the details is that the output of the ENCRYPTBYKEY method is a maximum size of 8,000 bytes. Therefore despite our column being set to accept cipher text larger than the row limits the encryption process in SQL Server does not allow us to create cipher text of that size. This is true for the other encryption methods including the HashBytes method.

What, Me Worry?
One possible solution to this challenge would be to break up the large text into segments of less than 8,000 bytes prior to encryption. Book manuscripts have logical break points. Each chapter can be encrypted and stored separately. Implementing a normalized table structure will provide an flexible and organized method of storage.

Another approach might be to seek encryption options externally from the database. Encrypting the large text before it is introduced to the database could yield a broader list of options.

A final consideration is to challenge why the large text requires encryption. The intent to obfuscate data does not necessarily call for encryption. As an alternative, consider converting the plain text directly into a varbinary data type by utilizing the CONVERT method. For example:

INSERT INTO MyTable ([MyVarbinaryColumn]) VALUES
(CONVERT(Varbinary(max),@YourPlainText))

This statement will return a varbinary version of the plain text that is equal to the full length of the string. It's resulting value is not discernable to the naked eye. To return this value to plain text requires only the conversion of the varbinary(max) data to a varchar(max) data type using the same CONVERT method. This approach does not offer the security level that encryption offers; but it may suffice for the intended obfuscation while overcoming the encryption method limitations.

Over the past month I have working on a collection of training videos. These videos cover various "how-to" topics related to encryption and data security. These videos are available through my profile on JumpstartTV.

This morning, March 2, my "Honeycombing in SQL Server 2005/2008" was featured. The title is a bit of a trick since implementing honeycombing in SQL Server 2005 is very difficult, if not impossible. With the introduction of the audit feature of SQL Server 2008 honeycombing is not only possible but very easy.

The experience of creating videos is a new one for me. I did enjoy creating them despite a few short periods of frustration. A lesson that was learned through the process was that scripting the dialog was very helpful. My early attempts were to speak ad hoc as if I was in a conversation; but this did not prove to be effective. The scripting allowed me to organize my thoughts and significantly reduce the Shatner-like pauses, periods of dead-air and the occurrences of "uh...".

Another lesson was to work on a couple and then step away. I found that after a couple of hours of speaking into the microphone that I began to stumble and transpose words at an increasing rate. I attribute this to fatigue; but it may be that I just need more practice. The first day was the hardest in this regard since it was a full-day sprint in the creation of these videos.

A light-hearted lesson that was learned was not to drink Ginger Ale (or any other carbonated beverage) before recording. This action lead to a few re-starts in the recording process; although it did provide some interesting versions of the videos that did bring on a bit of hilarity.

My thanks to Andy Warren who extended an invitation to this opportunity as well as his eternal patience and helpful coaching. Also, a thanks to Chris Rock who polished up my videos and made them look slick with his editing skills.

I do plan on preparing more videos in the near future. I would encourage anyone who may be thinking of creating a few of these tid-bits of knowledge to consider JumpstartTV.

I was recently tagged in a blog entry from Tim Mitchell titled "Things I Wish I Had Known". The concept appears to have started with an entry of a similar, but lengthier, title by Mike Walsh and passed along to other SQL bloggers. The idea is to bestow some wisdom that would benefit someone who is starting out in our profession. Here are a few morsels from my experience that I offer:

Ask, Ask and then Ask Again
The development process at times fells like trying to translate hieroglyphics without the Rosetta stone. This is true whether you are developing a database or the user interface of an application. Customers will present their needs in a way that they understand it. The developer will attempt to interpret the request into a system that meets their needs. You can be sure that the customer's request is never as simple as they present it and it is never as simple as the developer interprets it. The key to success in this task: Ask questions as if you were a layer.

Improve Processes Before Automating Them
I have been the recipient of many projects where the requested system is pursued as the savior of a severely broken process. Rather than go through the process of reviewing the process and identify the opportunities to improve it the thought is to throw code at it to speed up the excruciating and time consuming manual processes. This often results in a system that offers very little improvement in the situation and large masses of missing hair on the part of the developer and the requestor.

He Who Is Not Busy Being Born Is Busy Dying
This line, pulled directly from a Bob Dylan song, is a something that I live by on a daily basis. The opportunities to learn from another person is in abundance. The opportunities to teach another person flourishes equally. The willingness to accept that failure will occur leads to the elevation of experience that draws nearer to expertise. Identifying opportunities to improve existing skill sets is critical for growth. It is when the belief that there is nothing more the learn is when complacency begins to rear its ugly face.

Support Your Local User Group and Events
User groups and community organized events such as Tech Fests, SQL Saturdays, and code camps offer great learning opportunities for free, or at nominal cost. These events also provide the opportunity to network with your peers which is priceless. In addition, the opportunity to explore your speaking skills are provided through these venues.

At The Beginning of the Day Make A Top 5 List
At one of my previous employers the practice of making a daily top five list of tasks, in order of priority, was implemented by the CIO. This list is intended be the focus of the day. At first the idea was met with some resistance by the entire department; but I found it very valuable in organizing my day. Limiting the list to five makes it very manageable. I knew that if I go home with all five items crossed off my list it was a productive one.

My offering is rather short compared to others that have participated in this chain-of-blogs. As the trend continues providing something unique becomes more challenging. There have been many great pieces of advice passed along. Hopefully they fall into the hands of a very fortunate SQL Server beginner.

I will do my part and entrust (tag) Brad McGehee, Jimmy May, and Arie Jones to be the carriers of this vessel of good advice. Blog on my friends!