Executive Summary

Microsoft, like many large corporations, carefully analyzes existing database security frameworks to make sure that the security frameworks comply with recent government regulatory requirements, such as the Sarbanes-Oxley Act of 2002. These regulatory requirements specify conditions for the storage of personally identifiable information. These requirements do not only affect data when it is stored in a database. They also affect data transfer mechanisms, database authorization and access controls, and database auditing.

By using this database analysis, Microsoft Information Technology (Microsoft IT) determined that sensitive data was duplicated throughout the Microsoft IT line-of-business (LOB) application space. This data was duplicated when data was transferred and replicated during the day-to-day operations of the company.

In response to this analysis, Microsoft IT developed strategies to reduce the duplication of sensitive data and improve the security of personally identifiable information in the Microsoft IT LOB application space. These strategies are based on the new security features and functionalities that Microsoft® SQL Server™ 2005 includes.

The Enterprise Data Services group within Microsoft IT created a 2-terabyte central information repository that is named FeedStore. The group developed a pilot project to enhance the security of personally identifiable information that is passed through FeedStore. This project involved creating a centralized encrypted store that is named the Digital Asset Store to house highly sensitive personally identifiable information. Enterprise Data Services designed the Digital Asset Store to use the key management features and column-level encryption functionality in SQL Server 2005 to encrypt sensitive data in a central location. This pilot project had clear business goals and functional goals to help remove or reduce data duplication in Microsoft IT LOB applications.

The Financial IT department within Microsoft IT created the Payroll Controls Reporting System (PCRS) application. This department developed a security framework to improve data security by encrypting sensitive data that would be stored in the PCRS data warehouse. Additionally, the Services IT department within Microsoft IT used the SQL Server 2005 key management functionality and column-level encryption capabilities to create a robust (reliable and strong) encryption mechanism to encrypt data in the Metropolis LOB application.

This document shares Microsoft IT experiences with these security strategies and with SQL Server 2005 encryption capabilities. Because many SQL Server 2005 pilot projects are currently in progress, Microsoft IT has learned valuable lessons and best practices that relate to data consolidation and encryption in the Microsoft IT LOB application space. Because Microsoft IT requirements are among the most challenging in the world, the strategies that Microsoft IT develops and the lessons that Microsoft IT learns through the deployment of SQL Server 2005 should provide meaningful guidance to corporations that want to deploy a SQL Server 2005–based encryption and key management framework.

This document is intended for enterprise business decision makers, technical decision makers, IT architects, database developers, and deployment managers. Although this document provides recommendations based on Microsoft IT early-adopter experiences, it is not intended to serve as a procedural guide. Each enterprise environment has unique circumstances. Therefore, each organization should adapt this information to meet its specific requirements.

Note: For security reasons, the sample names of internal resources, organizations, and internally developed security file names used in this paper do not represent real resource names used within Microsoft and are for illustration purposes only.

Introduction

Corporate decision makers frequently request information about experiences with using Microsoft products and technologies within Microsoft. IT departments within Microsoft do not only provide IT services. These departments also act as the first customer for each new release of software for servers and business productivity. Because Microsoft IT requirements are among the most technically challenging in the world, the methods that Microsoft IT uses to deploy these technologies and the experience that Microsoft IT gains from these deployments frequently provide meaningful deployment and operational guidelines for other corporations that want to deploy Microsoft products.

Additionally, because Microsoft IT works with new Microsoft products from the prerelease editions to the Release to Manufacturing (RTM) editions, Microsoft IT provides Microsoft with valuable feedback about features and functionalities. This feedback improves the software products. This feedback also helps Microsoft customers and partners successfully deploy these products and technologies.

Overview of Regulatory Requirements

Like other corporations, Microsoft has been reevaluating current security frameworks to make sure that the security frameworks comply with recent federal, state, and international laws that define regulatory compliance obligations for personal information. In the United States, these regulations include the following federal and state laws:

• Sarbanes-Oxley Act of 2002

• Gramm-Leach-Bliley Act (GLBA) of 1999

• Health Insurance Portability and Accountability Act (HIPAA) of 1996

• Family Educational Rights and Privacy Act (FERPA)

• FDA Title 21 CFR Part 11

• California Senate Bill 1386

• Washington Senate Bill 6043

Additionally, international regulations define regulatory compliance obligations for companies that store personally identifiable information. These regulations include:

• Canadian Personal Information Protection and Electronic Documents Act (PIPEDA)

• European Union Data Protection Directive

• Basel Capital Accord, also known as Basel II

Organizations that store consumer personal information must carefully consider the implications of these new regulatory requirements. These requirements affect all the following database operations:

• Database authentication, including password policies and authentication protocols

• Database authorization and access controls

• Data protection for sensitive data that is stored in a database

• Data protection for sensitive data that is transferred to a database or from a database

• Audits of database transactions to help guarantee confidentiality and data integrity

Corporations must adhere to regulatory compliance obligations regarding personally identifiable information. To provide efficient and cost-effective data protection, corporate IT departments may have to reconsider how their organizations store and manage sensitive data.

Overview of Data Encryption

During the evaluation of a security framework, corporate IT departments may have to reevaluate security throughout their organizations. These security precautions may include password policies, audit policies, isolation of database servers, and application authentication and authorization controls. However, the final security barrier to help protect sensitive data is typically data encryption.

Encryption is a mechanism to help protect data. Encryption helps provide data confidentiality by obfuscating the data so that only authorized people can access and read the data. Data is encrypted when the original data, known as plaintext, together with a value that is known as a key, is passed through one or more mathematical formulas. This procedure makes the original data unreadable. The resultant encrypted data is known as ciphertext. To make this data readable again, the recipient decrypts the data by reversing the mathematical process together with the correct key.

This kind of data protection, however, has a cost in both computer processor time and storage requirements. A longer encryption key helps make the ciphertext more secure than if an organization uses a shorter encryption key. However, this more complex encryption/decryption operation costs more in processor time than encryption that uses a shorter encryption key. Additionally, encryption increases the size of the target (encrypted) data.

The following two main types of encryption exist:

• Symmetric encryption. This kind of encryption is also known as shared-key encryption.

• Asymmetric encryption. This kind of encryption is also known as two-part encryption or public key encryption.

"With SQL Server 2005, we'll be able to take security to the next level and encrypt attributes that need to be protected... such as social security numbers and other sensitive information."

David Fahey

Symmetric Encryption

Symmetric encryption uses the same key both to encrypt and to decrypt data. The algorithms that are used for symmetric encryption are simpler than the algorithms that are used for asymmetric encryption. Because of these simpler algorithms, and because the same key is used both to encrypt and to decrypt the data, symmetric encryption is much faster than asymmetric encryption. Therefore, symmetric encryption is suited to encrypting and decrypting a large amount of data. Figure 1 shows the symmetric encryption process.

Figure 1. Symmetric encryption process

One of the main disadvantages of symmetric encryption is that it uses the same key both to encrypt and to decrypt the data. Therefore, all the parties that send and receive the data must know or have access to the encryption key. This requirement creates a security management issue and key management issues that an organization must consider in its environment. A security management issue exists because the organization must send this encryption key to any party that requires access to the encrypted data. Key management issues that an organization must consider include key generation, distribution, backup, regeneration, and life cycle.

Symmetric encryption provides authorization for encrypted data. For example, by using symmetric encryption, an organization can be reasonably certain that only authorized parties that can access the shared encryption key can decrypt the ciphertext. However, symmetric encryption does not provide nonrepudiation. For example, in a scenario in which many parties can access the shared encryption key, symmetric encryption cannot confirm the particular party that sends the data. Encryption algorithms that are used for symmetric encryption include the following:

• RC2 (128 bit)

• Triple Data Encryption Standard (3DES)

• Advanced Encryption Standard (AES)

Asymmetric Encryption

Asymmetric encryption uses two different but mathematically related encryption keys to encrypt and to decrypt data. These keys are known as the private key and the public key. Together, these keys are known as a key pair. Asymmetric encryption is considered to be more secure than symmetric encryption because a different key is used to encrypt the data than the key that is used to decrypt the data. However, because asymmetric encryption uses more complex algorithms than symmetric encryption uses, and because asymmetric encryption uses a key pair, the encryption process is much slower when an organization uses asymmetric encryption than when it uses symmetric encryption. Figure 2 shows the asymmetric encryption process.

Figure 2. Asymmetric encryption process

With asymmetric encryption, only one party holds the private key. This party is known as the subject. All other parties can access the public key. Data that is encrypted by means of the public key can be decrypted only by means of the private key. Conversely, data that is encrypted by means of the private key can be decrypted only by means of the public key. Therefore, this kind of encryption provides both confidentiality and nonrepudiation.

An organization can use this kind of encryption to provide authorization by using the public key to encrypt data. This key is publicly available. Therefore, anyone can encrypt the data. However, because only the subject holds the private key, the organization can be reasonably certain that only the intended recipient can decrypt and view the encrypted data.

An organization can use this kind of encryption to provide authentication by using the private key to encrypt data. Only the subject holds this key. However, anyone can decrypt the data because the public key that decrypts this data is publicly available. Therefore, if the recipient can decrypt this data by using the public key, he or she can be reasonably certain that only the subject encrypted the data. Encryption algorithms that are used for asymmetric encryption include the following:

• Diffie-Hellman key agreement

• Rivest-Shamir-Adleman (RSA)

• Digital Signature Algorithm (DSA)

Hybrid Encryption

Hybrid encryption is an encryption scheme in which data encryption is performed through a combination of symmetric encryption and asymmetric encryption. A hybrid encryption method takes advantage of the strengths of both kinds of encryption to help make sure that only the intended recipient reads the data.

In a hybrid encryption scenario, an organization encrypts data by using symmetric encryption together with a randomly generated key. This step takes advantage of the speed of symmetric encryption. Then, the organization encrypts the symmetric encryption key by using the public key of an asymmetric key pair. This step takes advantage of the increased security of asymmetric encryption. The encrypted data together with the encrypted symmetric key is sent to the data recipient. Figure 3 shows the hybrid encryption process.

Figure 3. Hybrid encryption process

To decrypt the data, the recipient first uses the private key of the asymmetric key pair to decrypt the symmetric key. Then, the recipient uses the decrypted symmetric key to decrypt the data. Figure 4 illustrates the hybrid decryption process.

Figure 4. Hybrid decryption process

Encryption Considerations

When an organization is deciding whether to encrypt data, it must consider the increased processor load to perform encryption and decryption. However, it must also consider the increased storage space that encrypted data consumes. How much storage space the data consumes depends on the algorithm that the organization uses, the size of the key, and the size of the clear text that the organization encrypts.

Although an organization must consider both performance issues and storage issues when it implements encryption, the most important issue is key management. The encryption keys that an organization uses to encrypt and to decrypt data are a critical part of its data security framework. To make sure that only authorized users view encrypted data, the organization must implement measures to manage, to store, to help protect, and to back up the encryption keys.

Application Environment

Microsoft IT delivers global IT services for Microsoft. These services include support services for 57,000 employees, more than 200,000 personal computers, and more than 8,000 servers. These services range from server and network operations to software deployment and end-user technical support. Additionally, Microsoft IT deals with more than 100,000 security-related issues every month.

Microsoft IT has more than 300 LOB applications that handle sensitive data in the day-to-day operations of the company. The goal of Microsoft IT is to enhance data security as the data is stored, transmitted, processed, or displayed in systems or reports throughout the LOB applications. Therefore, Microsoft IT is currently upgrading all its database-related LOB applications to SQL Server 2005. This upgrade takes advantage of new management, security, and performance-related features and functionalities in SQL Server 2005.

Microsoft IT analyzed (and continues to analyze) its database storage solutions in response to the following requirements:

• Government regulatory requirements to help protect employee, customer, and partner privacy

• Microsoft corporate information security requirements regarding highly sensitive personally identifiable information, such as personal information about employees and partners

This document discusses the encryption frameworks that Microsoft IT developed and continues to develop for the following three database systems that the analysis included:

• The 2-terabyte central information repository that is named FeedStore in the Enterprise Data Services group

• The PCRS data warehouse in Financial IT

• The Metropolis service and support tool database in Services IT

Because different amounts of data are involved, different numbers of applications are involved, and the particular database environments differ, the IT departments that managed each of these database systems within Microsoft IT had to develop different encryption strategies and implementation processes during their analysis of the particular application environment that they managed. However, common to these strategies was using SQL Server 2005 to implement a key management hierarchy and column-level encryption.

Solution: SQL Server 2005 Encryption

SQL Server 2005 includes many security-related features that help protect the data in an organization. SQL Server 2005 includes password policy enforcement, a strong authentication functionality, and a granular hierarchical permissions model. SQL Server 2005 also includes a built-in data encryption capability. This column-level encryption capability is enhanced by an integrated and hierarchical infrastructure for managing encryption keys. Built-in encryption functions and application programming interfaces (APIs) make it easier for an organization to create an encryption security framework.

"With SQL Server 2005 built-in encryption, you don't have to think about how the process works in the background. You just have to call the encryption function to encrypt the data."

Devendra Tiwari

Built-in Encryption Capabilities

Key management is the single most important element in an encryption security framework. SQL Server 2005 supports three types of encryption. Each type uses a different kind of key, and each type has multiple encryption algorithms and key strengths, as follows:

• Symmetric encryption: SQL Server 2005 supports the RC4, RC2, DES, and AES families of encryption algorithms.

• Asymmetric encryption: SQL Server 2005 supports the RSA encryption algorithm together with key strengths of 512 bit, 1,024 bit, and 2,048 bit.

• Certificates: Certificate usage is another form of asymmetric encryption. However, an organization can use a certificate to associate a set of public and private keys with their owner by using a digital signature. SQL Server 2005 supports the Internet Engineering Task Force (IETF) X.509 version 3 (X.509v3) specification. An organization can use externally generated certificates with SQL Server 2005, or it can generate certificates by using SQL Server 2005.

Encryption Key Hierarchy

SQL Server 2005 implements a framework to help protect encryption keys by using an encryption key hierarchy, as shown in Figure 5. In this hierarchy, each layer encrypts the layers that are below it.

If your browser does not support inline frames, click here to view on a separate page.

Figure 5. SQL Server 2005 encryption key hierarchy

The Data Protection API (DPAPI) is at the top of this encryption key hierarchy. DPAPI is a pair of function calls that provide operating system–level data protection services to user and system processes. Because this API is part of the Microsoft Windows® operating system, applications can use DPAPI to encrypt data but do not have to use any cryptographic code other than the code that calls the DPAPI functions. DPAPI is a password-based data protection service. Therefore, DPAPI requires a password to encrypt the data.

Note: The password that is used here is that of the account that calls the encryption functionality. Therefore, a developer who implements encryption does not have to specify an additional password.

The SQL Server 2005 Service Master Key is a symmetric key that the cryptGenKey function automatically generates when an administrator installs SQL Server 2005. DPAPI uses the password of the account under which SQL Server 2005 runs to generate a key with which DPAPI encrypts the Service Master Key. Therefore, if an administrator changes the service account under which SQL Server 2005 runs, he or she must decrypt the Service Master Key by using the original credentials. Then, he or she must encrypt the Service Master Key by using the new credentials. We strongly recommend that administrators change the service account under which SQL Server 2005 runs only by using the SQL Server 2005 Computer Manager tool. This tool automatically performs the required steps to decrypt the Service Master Key, and to then re-encrypt the Service Master Key.

Note: An administrator does not have to perform these actions if he or she changes only the service account password. These actions have to be performed only if the actual service account under which SQL Server 2005 runs is changed.

The Service Master Key encrypts the Database Master Key. The Database Master Key is required in each database where an organization encrypts data. This key provides the same functionality as the Service Master Key. However, the functionality occurs at a database level instead of a SQL Server 2005 instance level.

The Database Master Key is a symmetric key. It is not automatically created when a new SQL Server 2005 database is created. Therefore, the developer must explicitly create this key to implement encryption in a database.

The Database Master Key encrypts the user keys that a developer creates. These user keys include certificates and asymmetric keys. In turn, certificates and asymmetric keys encrypt symmetric keys that the developer creates.

Note: A developer can also use certificates and asymmetric keys to encrypt data directly. However, certificates and asymmetric keys are best suited to encrypt only small amounts of data.

A developer can use symmetric keys to encrypt other symmetric keys that he or she creates or to encrypt data. This encryption key hierarchy is especially important to consider when the developer determines the type of key to use to encrypt newly created keys. The SQL Server 2005 encryption key framework gives a developer a large amount of flexibility to create a simple or complex encryption key hierarchy for each database that he or she creates. However, we recommend that a developer create as simple a key structure as his or her organization allows.

Note: A developer can also encrypt the private key of a certificate or a symmetric key by specifying a password. However, this method is generally better suited to an environment where it is acceptable for applications or end users to have knowledge of the encryption method that is used and the encryption keys that are used. This method is not good for a scenario where the encryption process is intended to be transparent to end users.

Encryption Keys

The encryption and decryption process requires the following keys.

Certificate

A certificate is a way to use asymmetric encryption. A certificate is a digitally signed security object that binds the value of the public key to the user, device, or service that holds the corresponding private key. A certification authority (CA) issues and signs certificates. The certificates that SQL Server 2005 creates comply with the IETF X.509v3 certificate standard. Generally, a developer uses a certificate to encrypt other types of encryption keys in a database.

Asymmetric Key

An asymmetric key consists of a private key and the corresponding public key. Each of these keys can decrypt data that the other key encrypts. Generally, a developer uses asymmetric encryption to encrypt a symmetric key for storage in a database. In SQL Server 2005, asymmetric keys are public and private key pairs. The public key does not have a particular format as a certificate would have, and the developer cannot export it to a file.

In SQL Server 2005, certificates and asymmetric keys have interchangeable roles. A developer can encrypt asymmetric keys by using the following two methods:

• A user key that is derived from a user-supplied password

• The Database Master Key

Symmetric Key

A symmetric key is a single key that is used for both encryption and decryption. The encryption and decryption operations perform quickly with symmetric encryption. Therefore, symmetric encryption is well suited for encrypting a large amount of data in SQL Server 2005.

In SQL Server 2005, a developer can encrypt a symmetric key by using one or more of the following methods:

• The public key of a certificate

• A user-supplied password

• Another symmetric key

• An asymmetric key

  Note: The symmetric key is not stored in the database. Only the encrypted values of the symmetric key are stored in the database. Therefore, users who can access the database cannot decrypt the data without first decrypting the symmetric key.

If a developer encrypts symmetric keys by using other symmetric keys, he or she must open the keys in the correct order. The correct order is from the upper levels in the encryption hierarchy to the lower levels, one level at a time. The developer must follow the correct order because he or she cannot open and decrypt a symmetric key without first opening and decrypting the key with which the particular key was encrypted. Developers must keep this order in mind when they design the hierarchy of encryption keys in the database.

FeedStore

FeedStore is an internal application that the Enterprise Data Services group created. This application pulls data from 39 internal sources and generates data for approximately 500 subscribing applications worldwide. FeedStore receives data by using all the following methods:

• Linked servers

• Replication partners

• Flat files

FeedStore processes this data to create standardized data sets to submit to distribution servers. This information is passed as complete or partial tables to subscribing applications by means of the following methods:

• A custom internal application in the Microsoft corporate network

• SQL Server push replication in the Microsoft extranet

Enterprise Data Services determined that sensitive data was duplicated throughout the Microsoft IT LOB application space during the course of doing business. For example, data is pushed to FeedStore. FeedStore pushes this data to approximately 500 subscribers. Then, Microsoft IT LOB applications pull data from these subscribers. Because of this process, a copy of the sensitive data resides in FeedStore, and another copy resides in the local subscriber. Figure 6 illustrates the data flow across applications in Microsoft IT.

If your browser does not support inline frames, click here to view on a separate page.

Figure 6. Data flow across applications in Microsoft IT

Additionally, Enterprise Data Services noted that data was duplicated in the following manner: Business-related data is submitted to SAP, FeedStore, and other databases. These databases consolidate the data in another data warehouse from which the data is exported to FeedStore and to other databases. Many LOB applications access this data from databases other than FeedStore. Additionally, FeedStore distributes this data to other locations. In each of these processes, data is stored in multiple locations and then copied to multiple locations. The data moves between SQL Server databases, flat file locations, and other proprietary stores. From these locations, batch processes, applications, and users may access this data.

Enterprise Data Services determined that the same piece of data may be used by a different set of users or under one or more different user accounts at any particular point in the system. For example, an application may use a trusted subsystem to access data. In this scenario, an application may authorize a user to access a particular piece of data. However, after the user has been successfully authorized, the application actually retrieves the relevant data by using the credentials of the service account under which that particular application runs. Figure 7 illustrates this authorization process.

Figure 7. Example authorization process

FeedStore Strategy

Enterprise Data Services determined that, as in many other corporate environments, sensitive data was duplicated and stored in multiple locations in the LOB application space. An initial response to this type of discovery may be to apply encryption to each location that houses this sensitive data. This action would provide a high level of security to the sensitive data. However, the cost of encrypting every bit of this data at every storage location would be enormous.

Enterprise Data Services determined that consolidating and encrypting sensitive data in a separate store would be the best and most cost-effective strategy to comply with government regulatory requirements and Microsoft information security requirements. This strategy involves data consolidation and removal.

Data Consolidation

The consolidation of sensitive data in a central store helps reduce data duplication. Additionally, having all the sensitive data in a single store makes applying an encryption framework to that data easier. Through the use of a central store for sensitive data, all the security-related issues, such as key management, authorization, authentication, and auditing, are handled in one central location. Therefore, Microsoft IT would not have to make major database changes to each LOB application that handles sensitive data to encrypt that sensitive data locally. Figure 8 shows the data flow that Enterprise Data Services proposed to help protect sensitive data throughout the Microsoft IT LOB application space.

If your browser does not support inline frames, click here to view on a separate page.

Figure 8. Proposed data flow across applications in Microsoft IT

Data Removal

Microsoft IT reviews LOB applications to identify whether those applications require access to sensitive data. For applications that do not require access to sensitive data, Microsoft IT reconfigures the feed to remove the sensitive data. This process is not only less expensive than data encryption, but it also follows the security best practice of least privilege—making sure that sensitive data is accessed on a need-to-know basis. The process to modify some or all Microsoft IT LOB applications to remove some or all personally identifiable information is already underway throughout Microsoft IT.

For applications that require access to sensitive data, Enterprise Data Services determined that encrypting this data in the local application would be more expensive than reconfiguring the application to obtain the sensitive data from a separate encrypted store. However, in some cases, the particular application cannot be reconfigured to obtain the sensitive data from a separate store. In this scenario, the LOB application must be reconfigured to encrypt the sensitive data locally.

The challenge of a data consolidation strategy is how to keep the sensitive data robustly available. Enterprise Data Services had to consider all the following challenges:

• Business unit IT (BUIT) departments within Microsoft IT have specific performance and fault tolerance requirements. A centralized encrypted store must be responsive enough and must have high availability to meet these requirements.

• The increased processor load that encryption requires may slow down processes to the extent that a centralized encrypted store becomes unusable.

• BUIT departments within Microsoft IT would have to change their applications to obtain highly sensitive personally identifiable information from the centralized encrypted store.

• It might not be feasible to re-engineer every LOB application to use a centralized encrypted store. LOB applications may run certain processes in which highly sensitive personally identifiable information is stored and copied to multiple locations for business processing.

• A centralized encrypted store must provide encryption, decryption, authentication, authorization, data retention, and data recovery mechanisms.

Digital Asset Store Pilot

As a starting point toward the goal of increased regulatory and corporate security compliance, Enterprise Data Services developed a pilot project to create a centralized encrypted store by using the new SQL Server 2005 encryption features and functionalities. Enterprise Data Services named this centralized store the Digital Asset Store. The purpose of the Digital Asset Store service is to integrate with the FeedStore application to help remove highly sensitive personally identifiable information from the FeedStore application and to enhance security for sensitive data in the Microsoft IT LOB application space.

Enterprise Data Services determined that even as a beta product, SQL Server 2005 was robust enough and had the features and the functionality that they needed to meet the requirements of the Digital Asset Store service. Enterprise Data Services found that existing custom encryption solutions were too expensive; millions of dollars were being spent on custom solutions that had limited reuse. Additionally, Enterprise Data Services determined that existing third-party encryption solutions were too expensive in terms of cost, integration, maintenance, and support issues.

Business Requirements

Before the Enterprise Data Services group implemented the Digital Asset Store pilot, the group created a series of business requirements. These requirements listed goals and objectives that Enterprise Data Services used to determine whether the Digital Asset Store pilot was successful. Enterprise Data Services had the following specific feature goals for the Digital Asset Store service:

• Provide encryption, decryption, authentication, and authorization services together with data retention and data recovery services.

• Provide a method to populate fields in business-to-business document exchanges with sensitive data at run time. This method is also known as inline translation.

• Provide a migration plan to move sensitive data to the Digital Asset Store.

Enterprise Data Services also created the following basic business objectives for the Digital Asset Store:

• Have Microsoft IT LOB application budgets include funding for migrating highly sensitive personally identifiable information to the Digital Asset Store.

• Reduce the cost of migrating highly sensitive personally identifiable information to the Digital Asset Store. Specifically, reduce this cost to less than the cost of encrypting data locally in each Microsoft IT LOB application. Therefore, Enterprise Data Services considered a target cost of approximately $10,000 U.S. or less to modify each LOB application to use the Digital Asset Store.

To determine the information to move to the Digital Asset Store, Enterprise Data Services first had to classify current information in a security-related context. Because of the costs involved in both moving and encrypting data, Microsoft and other corporations must carefully determine the information that is sensitive enough to require encryption.

Creating a separate encrypted store to house sensitive data requires careful planning and monitoring to make sure that the sensitive data remains available to requesting applications. The sensitive data must remain in the unencrypted data warehouse until the subscribing applications can be modified to obtain this data from the encrypted store.

Note: In some cases, a particular LOB application cannot be reconfigured to obtain the sensitive data from an encrypted store. In this scenario, the LOB application must be reconfigured to use features such as SQL Server 2005 column-level encryption to encrypt the sensitive data locally.

Implementation

Enterprise Data Services determined that the SQL Server 2005 security-related features would make the Digital Asset Store pilot project a success. To implement the Digital Asset Store, Enterprise Data Services determined that it would have to perform the following actions in the Microsoft IT LOB application space regarding FeedStore:

• Identify sensitive data elements that exist in the LOB application space.

• Remove sensitive data elements from LOB applications that do not require this information.

• Move highly sensitive personally identifiable information to the Digital Asset Store.

• Encrypt highly sensitive personally identifiable information in the Digital Asset Store.

• Help protect highly sensitive personally identifiable information as it is moved between applications.

To implement the Digital Asset Store service, Enterprise Data Services determined that publishers would have to be configured to submit sensitive data to the Digital Asset Store. However, publishers would continue to submit non-sensitive data to FeedStore.

FeedStore receives data feeds by using replication, SQL Server pull operations from linked servers, and pickup operations from flat file locations. The Digital Asset Store would provide encryption for data at rest (data that is not being transferred or accessed) in the Digital Asset Store. However, sensitive data would also rest in a flat file location that is currently used to submit data to the Digital Asset Store. Enterprise Data Services determined that a flat file location presents an unacceptable link in the transfer of data to the Digital Asset Store. Figure 9 shows the location of the flat file in the current FeedStore data feed structure.

If your browser does not support inline frames, click here to view on a separate page.

Figure 9. Current FeedStore data feed structure

Instead of developing a method to encrypt the data in flat file locations, Enterprise Data Services decided that it would only allow for a database-to-database data transfer method. In this scenario, the Digital Asset Store would obtain data by using a data transport mechanism to obtain smaller single points of data instead of the larger data sets that are in the transports to FeedStore. Figure 10 shows the proposed Digital Asset Store data feed structure.

If your browser does not support inline frames, click here to view on a separate page.

Figure 10. Proposed Digital Asset Store data feed structure

"SQL Server 2005 security and encryption features enable Microsoft IT to easily implement an encryption framework without having to worry about encryption key management."

Devendra Tiwari

The encryption features in SQL Server 2005 are designed to encrypt data at rest. Data that is stored in the Digital Asset Store would be encrypted. Data transfer between applications and the Digital Asset Store would be performed by the passing of decrypted (clear text) data through an encrypted tunnel. Microsoft IT determined this approach to be a recommended best practice for transferring data between databases. In this approach, encryption keys are not shared between systems. Additionally, the data at rest is encrypted by means of the encryption framework that is present in each system. Figure 11 illustrates this data transfer.

Figure 11. Recommended method to transfer sensitive data

In Figure 11, clear text data is transferred over an encrypted channel between two computers that are running SQL Server 2005. In this situation, the following actions occur:

1. Data that is stored (data at rest) in Server 1 is encrypted by means of the Key 1 encryption key.

2. This data is decrypted by means of Key 1 before the data is transferred over an encrypted channel to Server 2.

3. The data is encrypted in Server 2 by means of an encryption key (Key 2) that Server 2 generates.

To perform these actions by using a process that is running on Server 2, the particular process must decrypt the data on Server 1 by using Key 1, copy the decrypted data to Server 2, and then encrypt the data on Server 2 by using Key 2. To perform these actions, the account under which the process runs must have all the following user rights:

• View definition on the keys to encrypt and to decrypt the data

• View definition on the certificates to encrypt and to decrypt the data

• Control permissions on the certificate to decrypt the data

  Note: SQL Server 2005 encryption is designed to encrypt data at rest. Therefore, if an organization decides to transfer ciphertext between computers that are running SQL Server 2005, it should not use SQL Server 2005 encryption as the only security framework for the data when the data is in transit. The organization should also use other methods to help protect this transmitted data, such as Internet Protocol security (IPsec) or Secure Sockets Layer (SSL). Although SQL Server 2005 provides robust encryption, data in transit is subject to attacks such as statistical analysis attacks, cryptoanalysis attacks, or reply attacks. Therefore, we recommend that an organization encrypt the transport channel even if it transfers encrypted data.

FeedStore subscribers would have to be modified to obtain highly sensitive personally identifiable information from the Digital Asset Store. To reduce data duplication, and to follow the security best practice of least privilege, Enterprise Data Services determined that it would have to implement a high-performance procedure to insert sensitive data into a business-to-business data feed at run time. Enterprise Data Services therefore developed the technique that it calls inline translation. With inline translation, the following actions occur:

1. Look up data points such as social security numbers in an incoming feed based on keys such as personnel numbers.

2. Decrypt the data.

3. Insert this data into the feed as specified.

For example, an application may generate a data feed that contains social security numbers. This data feed is sent to a third-party financial institution. However, the environment that generates this data feed may not require access to the social security numbers. In this situation, the social security number does not have to exist in the environment that generates the feed if the social security number can be inserted into the feed to the third-party financial institution. Inline translation reduces the number of instances in which sensitive data may be accessed, stored, and transmitted. Additionally, inline translation uses a separate encrypted store to house the sensitive data.

Digital Asset Store Encryption Process

Enterprise Data Services determined that the Digital Asset Store would use a hybrid encryption mechanism. Therefore, data that is sent to the Digital Asset Store would be encrypted by means of SQL Server 2005 built-in encryption and a symmetric key. Then, SQL Server 2005 would encrypt this symmetric key by using certificate-based encryption. Figure 12 shows the Digital Asset Store encryption process as Enterprise Data Services intended to implement it.

If your browser does not support inline frames, click here to view on a separate page.

Figure 12. Digital Asset Store encryption process

To transfer data from the Digital Asset Store to a subscriber or to subscriber processing, the encryption process is reversed. SQL Server 2005 would decrypt the symmetric key by using certificate-based encryption. SQL Server 2005 would then decrypt the data by using the decrypted symmetric key. The decrypted data would be processed by a subscriber process, and then dissipated (deleted), or the decrypted data would be sent through an encrypted channel to a subscriber. The subscriber would then encrypt this data by using built-in SQL Server 2005 encryption. Figure 13 shows the Digital Asset Store decryption process as Enterprise Data Services intended to implement it.

If your browser does not support inline frames, click here to view on a separate page.

Figure 13. Digital Asset Store decryption process

Implementing the Digital Asset Store pilot project provided Microsoft IT with valuable implementation information for other encryption and data consolidation projects in the Microsoft IT LOB application space. SQL Server 2005 provides Microsoft IT with protocol enhancements for authentication, improved granular permissions, and a built-in encryption mechanism to help maintain confidentiality and integrity of sensitive data.

Payroll Controls Reporting System

United States–based Microsoft employees use the PCRS application to generate payroll accounting, payroll operations, and benefits-related reports.

The PCRS application is essentially a payroll data warehouse. This database holds 350 gigabytes (GB) of data and contains payroll information for all Microsoft employees who are based in the United States. The PCRS uses batch jobs to obtain information from FeedStore and SAP on an almost daily basis. The PCRS stores this information in one database, calculates values from this data, and then populates the PCRS reporting database by using these calculated values. After the calculated values are stored in the reporting database, Microsoft payroll operations and accounting can create payroll-related reports by using programs such as Microsoft Access or Microsoft Excel®. Figure 14 illustrates the flow of data through the PCRS.

Figure 14. Payroll Controls Reporting System data flow

Payroll Controls Reporting System Strategy

To ensure compliance with new government regulations regarding personally identifiable information, and to ensure compliance with Microsoft corporate security requirements, the Financial IT department determined that the PCRS application would have to be modified to use SQL Server 2005 encryption to help protect sensitive data. Additionally, because the PCRS receives sensitive data from multiple locations, and because additional processing must be performed on this sensitive data, Financial IT determined that encryption must be implemented in the local PCRS database instead of in a separate encrypted store.

Only a small number of columns in the PCRS database required encryption. However, the encryption strategy of Financial IT would also involve creating a SQL Server 2005 key management structure and modifying stored procedures to access encrypted data in the PCRS. Financial IT did not believe that modifying the PCRS application to include SQL Server 2005 column-level encryption would be a complex procedure. However, Financial IT was concerned about backing up, restoring, and performing ongoing maintenance of the SQL Server 2005 encryption key hierarchy. Therefore, before Financial IT implemented this strategy in a production environment, it implemented a database prototype to test and to guarantee the successful backup, maintenance, and restoration of the SQL Server 2005 encryption keys. Additionally, because indexing does not work on encrypted columns, Financial IT would have to locate and removed indexes from columns that required encryption.

Payroll Controls Reporting System Pilot

The Financial IT department set a target date to implement SQL Server 2005 column-level encryption in the PCRS application. However, before Financial IT implemented this encryption solution in the production environment, it created a prototype database that contained encrypted sample data to make sure that the SQL Server 2005 encryption key hierarchy could be successfully backed up, restored, and maintained in the production environment.

Financial IT created a sample database that was named PCRS_Encryption to test the encryption strategy for the PCRS application. This database contained sample sensitive data together with stored procedures to submit sensitive data to the database and to obtain sensitive data from the database. Database access was based on SQL Server 2005 role-based security. Figure 15 illustrates this database architecture.

If your browser does not support inline frames, click here to view on a separate page.

Figure 15. Payroll Controls Reporting System sample database

To implement and to test this database configuration, Financial IT used the following steps to create a simple yet robust SQL Server 2005 encryption key hierarchy:

1. Create the Database Master Key.

2. Regenerate the Service Master Key. This step was an additional security precaution to make sure that the Service Master Key had not been compromised.

3. Create a self-signed SQL Server 2005 certificate.

4. Create a symmetric key. Encrypt this key by using the SQL Server 2005 certificate.

Next, Financial IT used the following steps to modify the database schema to encrypt the sensitive data in the table:

1. Create a new table that has the same structure as the original table, except that the column that contains the sensitive data must be a varbinary data type.

2. Open the symmetric key.

3. Perform a SELECT INTO query to copy data from the existing table to the newly created table and to encrypt the sensitive data.

4. Close the opened symmetric key.

5. Drop the original table.

6. Rename the newly created table to the name of the original table.

7. Remove all indexes from the encrypted columns, if applicable.

Financial IT then updated the stored procedures to use the EncryptByKey() function and the DecryptByKey() function to access the encrypted data. The update entailed the following steps:

Note: For more information about these functions, see "Appendix: Encryption Usage Scenarios" later in this document.

1. Open the symmetric key.

2. Use the EncryptByKey() function or the DecryptByKey() function to encrypt or to decrypt data in the column that contains the sensitive data.

3. Close the opened symmetric key.

After Financial IT completed the preceding steps, it configured permissions on the SQL Server roles to grant permissions to the symmetric key for the role that required access the encrypted data and to deny permissions to the symmetric key for the role that should not have access to the encrypted data.

After Financial IT successfully configured the sample database to support the encryption of sensitive data, it backed up the Database Master Key, the SQL Server 2005 certificate, and the symmetric key with which the data was encrypted. Because encrypted data was then stored in the database, Financial IT determined that it needed to use the following procedure to back up the database:

1. Back up the SQL Server 2005 encryption keys by using the corresponding SQL Server 2005 Transact-SQL commands.

2. Back up the SQL Server 2005 database that contains the encrypted data.

3. Record the encryption key backup that corresponds to the particular database backup.

When an organization restores a database that contains encrypted data, it must have the encryption keys that were used to encrypt the stored data. If an organization regularly changes encryption keys to comply with its corporate security requirements, it must make sure that it has the encryption keys that can decrypt the data from a particular backup.

To restore a database that contains encrypted data, an organization must follow additional steps to make sure that the encrypted data can be decrypted. After the database is restored, the organization must decrypt the Database Master Key. Then, it must encrypt the Database Master Key by using the Service Master Key. To decrypt the Database Master Key, the organization must use the password with which it encrypted the Database Master Key when it used the SQL Server 2005 Transact-SQL commands to back up the key.

Although the specific goal of Financial IT was to develop and implement an encryption strategy for the PCRS application, the department also had a larger goal. This goal was to create a prescribed solution that other IT departments within Microsoft IT could use to implement encryption in local LOB applications.

Even though Financial IT used only sample data that was in the PCRS_Encryption database, the PCRS application has many similarities to other LOB applications within Microsoft IT. Therefore, Financial IT tested the PCRS_Encryption database in many real-world scenarios to determine whether the encryption backup and restoration guidelines would work for other IT departments in addition to Financial IT. Financial IT did not experience any performance issues with the encryption process or the decryption process during this testing. The encryption and decryption process was transparent to the Microsoft employees who used this sample database.

"SQL Server 2005 is a critical part of the end-to-end security of the PCRS application."

Steven Devin

Metropolis

Metropolis is a customer service and technical support application that the Services IT department created. This application consists of a front-end tool created through the Microsoft Win32® API, an Extensible Markup Language (XML)–based Web services second tier, and a SQL Server 2005–based third tier.

Microsoft support professionals use the front-end tool to help direct and support Helpdesk calls. Together with other support-related functionalities such as creating service requests and lookup operations to locate available support technicians, this tool enables support staff to access file shares and other locations that contain sensitive data such as product keys and passwords.

"The approach to data encryption in SQL Server 2005 enables our infrastructure to provide one-stop shopping for sensitive data. Storing secrets securely in a database makes working with sensitive data as simple to our application developers as dealing with any other data, yet provides state-of-the-art encryption technology to protect our most valuable assets.""

Brad Uhrich

When the Services IT department created the Metropolis application, the Services IT department, like many other corporate IT departments, had to comply with corporate security requirements that affect the storage of sensitive data such as product keys and passwords. Typically, sensitive data is stored in an encrypted file share or in a shared location where access control lists are set to limit user access to the sensitive data. The main problem with this approach is that when an organization helps protect the sensitive data by using a password or a secret, it must then protect that password or that secret. For example, it may encrypt information by using a pass phrase. The organization must then protect that pass phrase with another password or secret. This approach may lead to a cascading series of passwords or encryption mechanisms.

Microsoft corporate security requirements allow for using the DPAPI to act as the security mechanism for sensitive data. One of the reasons for this policy is that the account that uses the DPAPI must be logged on to the local computer.

Services IT determined that the SQL Server 2005 built-in key management hierarchy would not only satisfy Microsoft corporate security requirements but would also enable Services IT to create a simple mechanism to allow for access to sensitive data in the Metropolis databases. Therefore, Services IT created an administrative database to hold sensitive data. This administrative database is known as the environment database. The database holds all the sensitive data that is required to maintain the Metropolis database, such as server names, file share locations, credentials, and cryptographic keys. Services IT configured SQL Server 2005 column-level encryption to encrypt all the sensitive data in this database.

Services IT determined that the Metropolis environment database must meet the following conditions:

• Users who have low-level security permissions can encrypt data in the environment database.

• Only users who have a sufficiently high level of security permissions can decrypt data in the environment database.

The Services IT department determined that SQL Server 2005 encryption capabilities would enable it to create a simple yet robust security framework to meet the preceding two conditions. To create this encryption security framework, Services IT created the SQL Server 2005 hybrid encryption key hierarchy that Figure 16 illustrates.

Figure 16. Metropolis encryption key hierarchy

By using the DPAPI to encrypt the SQL Server 2005 Service Master Key, Services IT guaranteed that this encryption key framework would comply with Microsoft corporate security requirements. Additionally, by using a hybrid encryption scheme, Services IT followed Microsoft IT encryption best practices. A symmetric key is used to encrypt data, taking advantage of the speed of symmetric encryption. An asymmetric encryption method is used to encrypt the symmetric key, taking advantage of the increased security of asymmetric encryption over symmetric encryption.

However, the interesting part of the encryption framework for Systems IT is that the framework uses a certificate to digitally sign the stored procedure that encrypts data. By using the private key of the SQL Server 2005 certificate to digitally sign the stored procedure, a user who does not have permissions to the certificate can use the stored procedure to decrypt the symmetric key and then use the symmetric key to encrypt data in the environment database. However, a user who wants to decrypt the symmetric key to decrypt data in the environment database must have Key Master permissions.

By using a SQL Server certificate to encrypt the symmetric key, and by using this certificate to digitally sign the stored procedure that encrypts data in the environment database, Services IT was able to configure a permissions framework in which any user can encrypt data in the database. However, only certain users can then decrypt that data.

Services IT implemented the Metropolis encryption framework in a production environment with great success. The encryption process is transparent to the service professionals who use this system. Additionally, Services IT plans to expand the use of this encryption framework to include other databases in the Metropolis LOB application.

Best Practices

Microsoft IT has many SQL Server 2005 encryption-related pilot projects underway in the Microsoft IT LOB application space. Therefore, Microsoft IT has gained a large amount of experience with SQL Server 2005 database encryption techniques. This experience has led to a list of best practices. Other organizations can use these best practices to help simplify the task of enhancing database security to meet the requirements of government legislations and to meet the requirements of their corporate security departments.

Key Management Is Critical to an Encryption Framework

SQL Server 2005 includes the features and functionality to encrypt and to decrypt data without the need to deal with the minute details of encryption algorithms. However, one of the main benefits of SQL Server 2005 encryption is that SQL Server 2005 can manage the encryption keys.

The Microsoft IT recommendations for key generation are:

• Always create a backup by using a strong password for the Service Master Key.

• Use a strong password when creating the Database Master Key. This password must be subject to the local password verification policy, and SQL Server 2005 must be configured to verify password strength. Additionally, encrypt this password by using either certificates or symmetric keys. Back up the Database Master Key so that you can recover the key if you lose it.

• Use the AES encryption algorithm with 256-bit length during creation of a symmetric key. Encrypt symmetric keys by using SQL Server 2005 certificates. Grant permissions to the cryptographic objects (such as certificates or keys) to trusted principals. If a limited-trusted principal must use a key to decrypt or to encrypt data, use the EXECUTE AS clause in modules to change the key and the data instead of granting permissions directly to the limited user account.

• Create self-signed certificates. The built-in functions for encryption and signing do not verify the expiration dates of certificates. Therefore, test for certificate expiration in the application by using a stored procedure or middle-tier business logic.

• Regularly audit the database tables that contain sensitive data together with the keys and certificates catalog to determine who generates the certificates and keys. To conduct the audit, monitor these tables by using a SQL Server script and alerting mechanism.

The Microsoft IT recommendations for key usage are:

• Do not distribute encryption keys between servers. Data should be encrypted and decrypted on the same computer. Therefore, you generally do not have to transfer the encryption keys to another computer.

• When you open encryption keys during an encryption or decryption operation, always close those keys in the same session.

The Microsoft IT recommendations for key backup are:

• Back up the private key of the certificate and the password that you used to encrypt the private key of the certificate.

• Back up the Database Master Key by using the DUMP statement.

• Move the Service Master Key to a file, and then back up the file.

The Microsoft IT recommendations for key regeneration are:

• Regularly regenerate the Service Master Key because it is a symmetric key. This practice helps avoid potential key and data leakage through brute force attacks.

• Carefully consider the use of the FORCE REGENERATE option. Use the FORCE option only when it is required; that is, if regeneration regularly fails. The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key or cannot decrypt all the private keys.

Limit the Use of Encryption to Sensitive Data

Before an organization implements SQL Server 2005 encryption, it must consider the performance effect of encryption, whether an external source requires access to the encrypted data, and the increased size of ciphertext over plaintext. Microsoft IT has the following guidelines for the use of encryption in SQL Server 2005:

• Carefully classify data. Then, encrypt only the data that requires the enhanced security that encryption provides.

• If data will be encrypted only as long as it is stored in the database (at rest), and if you can both save and retrieve this data as plaintext, use symmetric keys to encrypt the data. Do not encrypt symmetric keys by using passwords unless you can carefully manage these keys and passwords. Do not transfer the symmetric keys between users and applications.

• If you want to encrypt a small amount of data, use asymmetric encryption. To encrypt a large amount of data, use a hybrid encryption approach.

Conclusion

Microsoft IT began moving its LOB applications to SQL Server 2005 to take advantage of new performance and security-related features that are available in the latest version of SQL Server. Microsoft IT had to respond to new government regulatory requirements for personally identifiable information together with Microsoft information security requirements for sensitive data. Therefore, Microsoft IT reevaluated the security framework that exists in the Microsoft IT LOB application space.

The FeedStore application within Microsoft IT serves as the central repository of data for Microsoft. Because this application deals with data that may contain personally identifiable information, a strong security scheme to help protect this data already exists. However, Enterprise Data Services decided to take the FeedStore security framework to the next level by developing an encryption framework and by implementing the Digital Asset Store pilot. This Digital Asset Store pilot would help remove personally identifiable information from FeedStore and from the Microsoft IT LOB application space.

The Financial IT department and the Services IT department faced a scenario in which they required encryption in their local LOB applications. Each department used SQL Server 2005 to develop an encryption framework that was robust yet simple to manage.

By using the built-in key management mechanisms and column-level encryption functionality in SQL Server 2005, Microsoft IT has taken the first steps toward increasing data security in the Microsoft IT LOB application space.

For More Information

For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada information Centre at (800) 563-9048. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information through the World Wide Web, go to:

http://www.microsoft.com

http://www.microsoft.com/itshowcase

http://www.microsoft.com/technet/itshowcase

For any questions, comments, or suggestions on this document, or to obtain additional information about Microsoft IT Showcase, please send e-mail to:

showcase@microsoft.com

Appendix: Encryption Usage Scenarios

Encrypting Data at Rest

To encrypt data at rest by using SQL Server 2005 symmetric encryption, use the following steps:

1. Create the Database Master Key. SQL Server 2005 uses the Database Master Key to encrypt the private key of the certificate that you create in step 2.

2. Create a certificate. SQL Server 2005 uses certificates to encrypt data or to encrypt symmetric keys.

3. Create a symmetric key to encrypt the destination data. Encrypt this symmetric key by using the certificate that you created in step 2, by using another symmetric key, or by using a user-supplied password.

4. Open the symmetric key to encrypt or to decrypt data. To open this key, use the same mechanism with which you encrypted the key.

5. Encrypt data by using the EncryptByKey() function, or decrypt data by using the DecryptByKey() function. The data is now stored as a binary large object (BLOB) in the database, or the data is now decrypted, depending on the Transact-SQL statement that you used.

6. Close all symmetric keys.

SQL Server 2005 provides an optional parameter that you can use with the EncryptByKey() function and the DecryptByKey() function to verify column integrity. Use this parameter if you want to make sure that a user who has the necessary permissions does not swap two encrypted values between rows in a database. For example, you may have encrypted salary information in a database table. Although column-level encryption prevents a user from reading the encrypted salary, column-level encryption does not prevent a user who has the necessary permissions from switching a manager's encrypted salary with the salary of another employee.

SQL Server 2005 helps protect against this scenario by using an optional parameter to specify a hash value at the time of encryption. During the encryption process, you can specify the following.

Insert into empTable values (emp1id, encryptbykey(Key1Guid, 50000, 
 emp1id))

During the decryption process, you can specify the following.

Select empID, decryptbykey(salary, empID) from empTable

In these examples, encryption is performed on the clear text value of 50000, which is concatenated with the hash of the optional third parameter. In this example, emp1id is the third parameter. At the time of decryption, SQL Server 2005 compares the hash of the third parameter with the hash that is stored in the encrypted BLOB. If the two hashes match, SQL Server 2005 determines that the encrypted value has not been moved between rows in the database.

Note: We recommend that you use the primary key as the third parameter of theEncryptByKey()function.

Accessing Encrypted Data by Using a View

Current scenarios exist within Microsoft IT where views and functions reference encrypted columns. SQL Server 2005 provides the DecryptByKeyAutoCert() function to decrypt the data inside views. In a scenario in which data is encrypted through a symmetric key and a certificate protects that symmetric key, this function works by looking up the key that is encrypted through the certificate that you specify. The DecryptByKeyAutoCert() function then automatically opens the symmetric key, decrypts the data, and then closes the key.

Note: For views and functions that reference an encrypted column, you must modify the view or the function to include the data encryption logic.

The following example script illustrates how to access encrypted data by using a view in SQL Server 2005.

Create View testview 
as 
Select Convert(varchar(100), 
  decryptbykeyautocert(cert_id('myCert1'), NULL, SSN, 0, Null)) as SSN 
From Customer

The DecryptByKeyAutoCert() function expects the following arguments:

• The certificate or the asymmetric key ID; for example, myCert1.

• The password for the specified certificate. In this example, the certificate is encrypted by means of the Database Master Key. Therefore, the password for the specified certificate is NULL.

• The stream to decrypt; for example, column SSN.

• Whether bytes for authenticity is present. This optional parameter has a value of either 0 or 1.

• Bytes for authenticity. The default value for this optional parameter is NULL.

  Note: When you use theDecryptByKeyAutoCert() function to look up a symmetric key that a certificate encrypts, and if that certificate was used to encrypt other symmetric keys, SQL Server 2005 uses the particular certificate to examine the encrypted BLOB structure to determine the key globally unique identifier (GUID) that is associated with the BLOB. SQL Server 2005 then opens only the particular symmetric key that was used to encrypt the column.

Bulk Inserting Data

The following example script illustrates how to bulk insert data from a file into a table, and then encrypt that data by using SQL Server 2005 encryption.

Note: The following code snippet has been displayed in multiple lines only for better readability. These should be entered in a single line.

Create Procedure test_sp 
as 
CREATE TABLE #InsertTemp 
( 
      PN int, 
      SSN varchar(100) 
) 
BULK INSERT #InsertTemp 
   FROM 'C:\test.txt'     
TRUNCATE TABLE BulkInsertTbl 
 
OPEN SYMMETRIC KEY key1AES DECRYPTION BY CERTIFICATE myCert1 
 
INSERT INTO BulkInsertTbl (PersonnelNumber, SSN) 
SELECT Tmp.PN, EncryptByKey(Key_GUID('key1AES'),Tmp.NID) 
FROM #InsertTemp Tmp 
 
DROP TABLE #InsertTemp 
/* Note: The following code is not required. The DecryptByKey  
    function call is not required. The function call appears here to 
test whether the inserted data is encrypted. */ 
SELECT 
      T.PersonnelNumber, 
      convert(varchar(20), decryptbykey(T.SSN)) as SSN 
 FROM BulkInsertTbl T 
CLOSE SYMMETRIC KEY key1AES 
go 
 
/* Script to create the BulkInsertTbl table */ 
CREATE TABLE BulkInsertTest 
( 
      PersonnelNumber int, 
      SSN varbinary(100)     /* Note: The encrypted column data type 
   is varbinary.) */ 
) 
go

In this example, the symmetric key that is named key1AES is encrypted by means of the certificate that is named myCert1. You must open the key1AES key before you perform data encryption. Then, you must close this key after encryption is finished. In this example, the DecryptByKey() function is not required. The function is included to verify whether the inserted data has been encrypted.

Encrypting and Decrypting Data by Using Certificates

Generally, we recommend that you encrypt data by using a symmetric key. This method takes advantage of the speed of symmetric encryption. However, you can encrypt data by using a certificate instead of a symmetric key. Because asymmetric encryption is more secure than symmetric encryption, using a certificate to encrypt data is useful in a scenario in which you want to transfer encryption keys between servers that are running SQL Server 2005. The following example script illustrates how to encrypt data by using a certificate.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Yukon900' 
go 
CREATE CERTIFICATE [cert_name] WITH SUBJECT = 'MyApp - Data 
   encryption' 
go 
INSERT INTO TABLE VALUES (encryptbycert( cert_id(cert_name), data))

To decrypt this data, use the DecryptByCert() function.