Customers who need to encrypt data in Microsoft SQL Server databases know that they must protect the encryption key with appropriate controls to meet compliance regulations and to achieve safe harbor in the event of a data breach. Townsend Security's Alliance Key Manager solution provides the Extensible Key Management (EKM) software to make proper key management a breeze. Called Key Connection for SQL Server, this EKM Provider software is installed on the server hosting the SQL Server database and it talks seamlessly to one or more Alliance Key Manager servers running in a separate server instance. Customers get proper key management that meets compliance regulations such as PCI-DSS in an easy-to-deploy solution.
Performance is always a consideration when it comes to enabling encryption, so customers naturally ask us about key caching. Does Key Connection for SQL Server cache the encryption keys to enable better performance?
The short answer is Yes, it does.
How it does key caching depends on whether you use Transparent Data Encryption (TDE) or Cell Level Encryption (CLE). Let’s drill into each of these cases.
Transparent Data Encryption (TDE)
The implementation of TDE by Microsoft involves encrypting the entire table space and the database logs. It is the easiest type of encryption to deploy as it requires no changes to the actual application that uses the SQL Server database. You can implement TDE encryption by installing the Key Connection For SQL Server software and issuing four commands through the SQL Server management console. Restart logging to insure that it is encrypted and you are done.
So with TDE, how are keys managed? The TDE architecture involves SQL Server generating a symmetric key (usually a 256-bit AES key) and then asking Alliance Key Manager to encrypt it with an RSA key. This encrypted symmetric key is then stored on the server that hosts the SQL Server database. When you start SQL Server (or restart it, as the case may be) the SQL Server instance asks Alliance Key Manager to use RSA decryption to decrypt the symmetric key. Once that is complete the SQL Server instance has the key it needs and no longer needs to communicate with Alliance Key Manager. There is no need for key caching and the key will be decrypted the next time that SQL Server starts.
Cell Level Encryption (CLE)
The implementation of CLE by Microsoft SQL Server is quite different than for TDE. The EKM Provider software is still responsible for managing the symmetric encryption key, but it is accomplished in a different way. You must make small changes to your application SQL statements to request encryption and decryption of the cell contents. When CLE is activated the Key Connection for SQL Server software is called for each column and row that needs to be encrypted or decrypted. This means a lot more calls to the EKM Provider software and this is where key caching is very important.
The Key Connection for SQL Server software in this case does cache the symmetric encryption key (usually a 256-bit AES key) in order to improve performance. The key is cached using an equally strong RSA key to prevent key capture by malware. When SQL Server calls the Townsend Security EKM provider the software retrieves the key from the key server and will cache it locally for a 24 hour period. For the next 24 hours all subsequent requests for encryption or decryption are satisfied locally without the need to retrieve the key again. After 24 hours, the key is discarded and a fresh key is retrieved from the key server. If the connection to the key server is not available error messages are written to the Windows Event Log, but encryption processes will continue using the locally cached key, once the 24 hour period expires, network connectivity will need to be restored for a fresh key to be retrieved and operations restored. With key caching database encryption, performance is much better.
The architecture of the Alliance Key Manager EKM provider implements other core features needed to help protect your database. These include:
- Separation of Duties between Key Administrators and Database Administrators
- Dual Control for key management operations
- Built-in logging to the Windows Event Manager
- High availability failover to one or more secondary key servers
- Automatic recovery of failed EKM Provider services
- Security of credentials through Windows Certificate Store
- Easy key rollover using native SQL Server commands
Key caching is important for performance, but this is just one part of an overall key management strategy for Microsoft SQL Server.
As customers move to virtualized and cloud environments, Alliance Key Manager and the Key Connection for SQL Server EKM Provider software will move with you. In addition to traditional IT data centers, all Townsend Security encryption and key management solutions run in VMware (vSphere, ESXi, etc.), Microsoft Azure, Amazon Web Services, and in any cloud service provider vCloud environment.
This is a guest blog by Nick Trenc, CISSP, QSA, PA-QSA, VCP. Nick is an IT Security Architect at Coalfire Labs.
In any environment where potentially sensitive data is stored using Microsoft’s SQL Server, one of the key issues is how to best protect that data. Microsoft SQL Server does offer several security controls natively, but almost all of them require some sort of extensive configuration and management in order to be done according to security best practices. Additionally, SQL Server’s own security controls do face some shortcomings.
If using SQL Server’s own encryption tools, database encryption keys are stored right next to the data they are used to protect. This makes it easier for would be malicious users to capture both the protected data and the keys used to protect that data.
This is where Townsend Security’s Alliance Key Manager (AKM) comes in to play. Utilizing the built-in SQL support, IT administrators can generate, store, and manage keys within AKM away from the data those keys are used to protect. This enables separation of duties and dual control which are both best practices and requirements of several compliance frameworks.
Alliance Key Manager utilizes the Extensible Key Management (EKM) functionality of SQL Server (Enterprise Edition 2008 and newer) to centrally manage encryption keys. In addition, AKM also includes native support for SQL Server Transparent Data Encryption (TDE) which can be used to encrypt all of the tables within SQL Server. Finally, AKM includes support for SQL Server Cell Level Encryption (sometimes called Column Level Encryption), integrates directly with the Windows Certificate store, and includes features for key caching and mirroring for high availability.
For more information on using AKM to specifically meet PCI DSS compliance within a virtual environment (but also applicable to most environments), please see the VMware Product Applicability Guide for PCI 3.0 published by Coalfire Systems with collaboration with Townsend Security and VMware.
Organizations running SQL Server Enterprise edition gain the added benefit of SQL Server transparent data encryption (TDE) and extensible key management (EKM). The encryption capabilities of Enterprise edition enable users to easily encrypt data at the column level of a database, and EKM allows users to store encryption keys using a third-party encryption key management solution. These streamlined capabilities of SQL Server Enterprise Edition have made SQL Server one of the easiest databases to encrypt, and therefore it’s popularity hasn’t waned.
One of the biggest issues facing SQL Server users today is maintaining security as users move their SQL databases to the cloud. While Microsoft Azure remains a popular cloud service provider (CSP) for SQL users, Amazon Web Services (AWS) and VMware are also common amongst organizations moving to the cloud, especially those migrating a multi-platform environment. Each of these top-tier CSPs offer security solutions to help you protect your cloud environment; however, when considering security in the cloud there are two important things to remember: The security offered by your CSP won’t provide you with a complete security solution, and the security solutions you bring to protect your data in the cloud can fail if not implemented correctly.
Don’t rely on the cloud for complete security!
Your CSP should provide your business with some security, but their solutions are likely limited. Most CSPs will offer firewall protection, for example. Top-tier CSPs have also undergone some certifications such as Payment Card Industry (PCI) and FedRAMP compliance. It is important to remember, however, that relying on firewalls alone is not enough to prevent intruders, and cloud certifications never mean that your company will automatically meet these compliance regulations as well. A comprehensive data security plan is required for any business operating in the cloud, and this typically requires using third-party security solutions to ensure your business meets compliance and is adequately protecting data.
Remember these two things when protecting data in the cloud:
- The security solutions offered by your cloud vendor are rarely enough to prevent a data breach.
- Just because your cloud service provider is compliant, doesn’t mean you are.
Storing data in SQL Server in the cloud presents new security challenges. Hackers or malicious users can gain access to sensitive data easily through common hacks. Easy hacking of SQL Server is a result from:
- Incorrect configuration of cloud provider’s firewall
- Attacks through weaknesses that could have been addressed by updating and patching SQL Server
- Missing or weak passwords
- social engineering and account hacking
- Lax administrative access
When it comes to securing SQL Server in the cloud, you should also always consult your legal and auditing team (or consultants) before assuming that your data is safe and you are compliant with any industry security regulations. On a general level, it’s important to include these security measures in your holistic security plan:
- Intrusion prevention
- System logging and monitoring
- Encryption & key management
- SSH in place of passwords
- Limited access to sensitive data
- Separation of duties and split knowledge when accessing encryption keys and sensitive data.
It’s important to remember that your business continuity relies on your own security plan. Regardless of the environment, when your organization experience a data breach, ultimately the responsibility is yours. Your customers, as well as your employees, rely on you to protect their data, and if you fail to do so, the consequences may include loss of customer loyalty and a severely damaged brand. The ultimate way to prevent access to sensitive data is using encryption and encryption key management.
To learn more about how Microsoft SQL Server Enterprise Edition can easily be secured in the cloud, download:
Questions and Answers on Encryption and Key Management Projects
VMware® is hands-down the virtualization choice of large and small organizations, and it is easy to see why. Not only is it a highly reliable and scalable platform, VMware also provides a complete set of tools you need to deploy, manage, monitor, and protect virtual machines.
Earlier this month, Paul Taylor with Security Insider - Podcast Edition spoke with our founder, Patrick Townsend about encrypting data on Microsoft SQL Server in VMware environments, steps to encrypting data on SQL Server (with and without TDE), as well as talk about Townsend Security’s Alliance Key Manager for VMware. Here are a few highlights (download the podcast for the whole conversation):
Paul Taylor: We’ve talked about the Townsend Security encryption and key management solutions for VMware. Today let’s put the focus on Microsoft SQL Server and encryption in the VMware customer environment. Can you give us an overview of how VMware customers can protect data in SQL Server databases?
Patrick Townsend: Just to recap, we really need two things to get encryption right: A key management solution to protect the critical encryption keys, and an encryption solution for the SQL Server database. And they have to talk to each other.
For the first part, our Alliance Key Manager for VMware solution provides a fully functional, enterprise key management solution that protects SQL Server databases as well as other databases and other operating systems.
For encrypting SQL Server, our Alliance Key Manager solution comes with a full Microsoft SQL Server Extensible Key Management Provider. We call this Key Connection for SQL Server and it is one of the modules that our key management customers receive without paying additional license fees. Key Connection for SQL Server provides the encryption and integration with our key server to provide a complete, end-to-end solution for encrypting data in the SQL Server database.
Paul Taylor: Can you talk a little about how Microsoft enables encryption in SQL Server?
Patrick Townsend: If you are running SQL Server Enterprise Edition or higher, you have access to Microsoft’s automatic, full database encryption facility called Transparent Data Encryption, or TDE. TDE is very easy to implement. It doesn’t require any changes to your existing applications, and using TDE with Alliance Key Manager, our encryption key management solution, is very straight-forward. It typically only takes a few minutes to get up and running with our encryption key manager and TDE. Cell level encryption, on the other hand, will take at least some changes to your SQL statements or .NET application code. These changes aren’t difficult at all, but you still need to make them. For some of our customers who don’t have the source code for the application, or who don’t have IT resources available, this can be a significant barrier.
Paul Taylor: What about Microsoft customers who aren’t using the Enterprise Edition of SQL Server? Can they encrypt their data with the Townsend Security solution?
Patrick Townsend: With SQL Server Standard and Web Editions we provide two paths to encrypt data. The first is to use SQL Views and Triggers along with our .NET DLL to provide automatic encryption without any changes to applications. And the second path is to modify your C# or Java applications to use our .NET DLL to perform encryption at the application level.
Both approaches leverage our Microsoft .NET DLLs to perform encryption with integrated key management. Both are very simple to implement. And there are no additional license fees to deploy and use our Microsoft .NET DLLs to accomplish this.
Paul Taylor: So, walk me through the steps for encrypting data in my SQL Server Enterprise Edition database. How difficult is it?
Patrick Townsend: Encrypting data in Enterprise SQL Server is really very easy. The first step is to install our Alliance Key Manager for VMware solution. It launches like any other virtual machine using the normal VMware applications and you can have a key management solution up and running very quickly.
The second step is to install the Key Connection for SQL Server application on the virtual machine running SQL Server in Windows. This is a normal install process with an MSI file. You answer some questions, install a certificate and private key in the Windows Certificate Store, and run a handful of commands to start SQL Server TDE encryption or Cell Level Encryption. You also restart the log file to be sure that it is encrypted as well. That’s about it.
Of course, you will want to follow the instructions on how to set up a high availability key server, and point your Key Connection for SQL Server configuration to it as failover. That is a normal configuration process and also very easy to do. We find that VMware customers can deploy SQL Server encryption very quickly.
Paul and Patrick also cover which versions of SQL Server are supported, the availability of Alliance Key Manager in other platforms (hint: it’s quite versatile), and our 30-day evaluation program (you can do a full proof-of-concept in your own environment at no charge). Be sure to download the podcast to hear the rest of their conversation:
VMware is hands-down the virtualization choice of large and small organizations. And it is easy to see why. Not only is it a highly reliable and scalable platform, but VMware provides a complete set of tools you need to deploy, manage, monitor, and protect virtual machines. And did I mention that it totally rocks the scalability challenge?
Let’s look at how VMware customers who run Microsoft SQL Server applications can enable encryption and key management to protect sensitive data and meet compliance regulations.
We have to solve the encryption key management challenge. As we like to say around here, the hardest part of security is encryption, and the hardest part of encryption is key management. We have to store the encryption keys separate from the protected data, and use industry standard practices to protect them. With our Alliance Key Manager for VMware solution we make this problem easy to solve. Our key manager comes in a ready-to-deploy OVA format and VMware customers can just launch the key manager with standard VMware tools. Of course, there are some security best practices on how to properly deploy a security application like a key manager in VMware (see the resources section below). With Alliance Key Manager’s Ready-To-Use options you can have your VMware key management problem solved in just SECONDS.
Of course, some of our VMware customers want to protect encryption keys in traditional Hardware Security Modules (HSMs). No problem, Alliance Key Manager can be deployed as a rack-mounted HSM or as a vCloud instance.
The Second Step:
Now we want to enable encryption in SQL Server and protect the encryption keys with Alliance Key Manager. Thanks to Microsoft’s Extensible Key Management (EKM) interface, this is incredibly easy. Alliance Key Manager comes with EKM Provider software that plugs right into SQL Server to enable encryption and protect your encryption keys. We call this our Key Connection for SQL Server application and it installs on your SQL Server VMware instance using a standard MSI install process. Key Connection for SQL Server runs in all SQL Server environments including VMware, hardware, vCloud, and cloud platforms so hybrid environments are fully supported. Install the credentials, select the SQL Server instances you want to protect, answer some questions, type a few commands and you have a fully protected SQL Server database using Transparent Data Encryption (TDE). Again, this takes just minutes to accomplish.
SQL Server also supports column level encryption, which Microsoft calls Cell Level Encryption. It can provide better performance for some SQL Server databases. Yes, that’s also supported through the same Key Connection for SQL Server software.
The beauty of the Microsoft EKM architecture is that you don’t need to modify your SQL Server applications to deploy encryption. Your DBA and security team can get your data protected very quickly without a development project. Anybody got budget for that these days?
Already encrypting SQL Server but aren’t protecting your encryption key? That’s easy – you can install Key Connection for SQL Server, issue a few commands, and the problem is solved!
The Third Step:
What about high availability, business recovery, clustered configurations, and system logs? We’ve got all of that covered, too. Using the same Key Connection for SQL Server EKM Provider (did I mention that it’s free?) you can configure one or more secondary key servers that function as high availability failover servers for business recovery? Key Connection for SQL Server will automatically failover to secondary key servers if the primary key server is unavailable.
Alliance Key Manager also fits nicely into your active monitoring strategy. You can easily enable forwarding of all key access, key management, encryption, and system activity logs to your log collection server or SIEM solution.
Celebrate Victory and Do It Again!
Alliance Key Manager protects Oracle, IBM, MySQL and other databases as well as web applications and unstructured data. You get to deploy one key management solution to protect everything. And do you know how much it will cost you to do your next project? Nothing, zilch, zed, nada! Alliance Key Manager does not force you to license and pay for client-side applications.
I’ll talk more in future posts about how to protect other databases and applications in VMware environments. Stay tuned if you run SharePoint, Microsoft CRM or ERP applications, Oracle, or open source databases like MySQL and SQLite.
How Much Better Can This Get?
You can evaluate Alliance Key Manager and Key Connection for SQL Server in your own VMware environment free of charge. Just visit our Alliance Key Manager for SQL Server page and request a free 30-day evaluation.
Encryption and key management? We can get this done right!
PCI SSC Virtualization Guidelines
VMware Solution Guide for Payment Card Industry (PCI)
Securing Alliance Key Manager for VMwar
Alliance Key Manager for VMware Solution Brief
Beyond meeting compliance regulations, it is the right thing to do!
In the past, encryption has had a reputation for being difficult to do, complex, and time consuming, we hope to show you how that has changed. If you are new at protecting data in Microsoft SQL Server environments, generally compliance regulations are what drive an encryption project.
Since it wasn’t thought of as something that improved the “Bottom line” by increasing revenue or decreasing expenses, encryption has historically been a project solely driven by the need to meet compliance regulations. There are a large variety of compliance regulations that most, if not all, businesses fall under. One common misconception about compliance regulations is that they don’t equally apply to both private and public companies. To clarify, these regulations apply to all companies, of all sizes, whether they are privately-held or publicly-owned.
For example, if you take credit cards for any reason, you fall under Payment Card Industry - Data Security Standards (PCI-DSS). Other common regulations are:
- HIPAA Data Security & HITECH Act of 2009 which applies to Medical Providers and the healthcare industry.
- GLBA/FFIEC apply to banks, credit unions, credit reporting agencies, and anyone in the financial industry.
- FISMA is for Federal US Government Agencies.
- The Federal Trade Commission (FTC) also gets involved with anyone who issues a privacy statement.
More than 45 states also have their own privacy rules, in addition to the ones listed above, that strongly recommend encryption of any personally identifiable information (PII).
So, beyond compliance with regulations, why should you care about encryption… and what is it anyways? First of all, your customers, clients, and suppliers all expect you to protect their sensitive data. Hackers and data thieves are targeting mid-sized companies because, as larger companies get better at securing sensitive information, the hackers see smaller companies as better targets. Financial fraud and data breaches become more common in those businesses that might not be as prepared without the resources to have an internal security team. Data loss can have a big impact on a company's reputation as well as their financial health.
AES encryption is a mathematical formula for protecting data. It is based on a proven, well-known algorithm and standards published by NIST. Since that formula is a open and vetted standard use, it is not the mathematical algorithm that is the big secret. It is what happens with the “Key” that locks and unlocks the data that all the fuss is about.
Key management is so important because the encryption keys are THE secret that must be protected. Without access to the key, a hacker that accesses encrypted data has no way to read it. Industry standards and best practices for encryption key management, as well as compliance regulations that require proper encryption key management, all state that storing encryption keys on the server with the protected data is a poor security practice. Encryption keys are unique and cryptographically secure, and once created, protecting the key is the core practice that will protect the sensitive data. It will not be defensible in the event of a data breach if the keys were stored in the same server as the data. This would be like leaving the key to your house in the door lock and being surprised that someone entered uninvited!
Our solutions help Microsoft SQL Server customers really protect their data. Alliance Key Manager, our encryption key management solution, is NIST FIPS 140-2 compliant. This means it meets Federal standards that private enterprises expect around key management. We provide encryption key management solutions for every version and edition of SQL Server starting with SQL Server 2005. In addition, you can choose between a hardware security module (HSM), Cloud HSM, VMware virtual appliance, or a cloud instance in AWS or Azure. Easy. Efficient. Cost-Effective.
Please join our founder and data security expert, Patrick Townsend, in this 30-minute webinar that will cover encryption and key management best practices with Microsoft SQL Server!
As always, your comments and feedback are appreciated!
From the PASS Summit to the Worldwide User Group (SSWUG)
From Developers to Database Administrators, we have met another amazing group of people at the PASS Summit 2014. Over 5,000 members of the Professional Association for SQL Server converged on Seattle, WA and we got to talk about security with people from all over North America and from as far away as Norway, Spain, Australia, Colombia, Germany, and even Iceland.
We spent most of our time talking about the importance of encrypting sensitive data, and about using an encryption key management solution to protect encryption keys away from the database. There is a huge need to meet compliance regulations, and with all the options now available (Hardware appliance, Cloud HSM, VMware virtual environment, and cloud instances in AWS or Azure) there is a solution for each scenario we encountered.
If you are working with SQL Server, we hope you are familiar with the SQL Server Worldwide User Group (SSWUG). If you don’t know about them, please allow me a moment to introduce you to Stephen Wynkoop who is the founder and editor for SSWUG.org. This website is a wealth of information about everything you would want to know about SQL Server (and they are even branching out to other database systems like Oracle and IBM DB2). The emphasis at SSWUG has been on SQL Server and you will find a large number of articles, blogs, videos and other content on wide variety of topics related to it. Stephen features weekly video programs about the database and IT world, webcasts, articles, online virtual community events and virtual conferences several times a year. He is a Microsoft SQL Server MVP and the author of more than 10 books, translated into at least 7 languages. Stephen has been working with SQL Server since the very first version, with a prior experience in database platforms that included dBase and Btrieve.
SSWUG has dedicated a section of their web site to the SSWUGtv Security Edition “Townsend Security Series” where they present videos of Stephen and our own industry expert, Patrick Townsend, discussing security topics ranging from securing data with encryption and key management on SQL Server (not just with EKM) to protecting data in the cloud. Additionally, they post a new security segment just about every week on their homepage, so there is always something fresh. A few of the sessions include topics such as What top industries do Hackers focus on and why? and Cross-platform security: How do you have a hybrid environment and keep it secure?
Check out this one on: PCI Compliance and Security in the Cloud - (11 minutes)
Stephen and Patrick have a great time recording these videos, and if you haven’t seen any yet, I urge you to check them out. In addition to all the great content on the SSWUG website, SSWUG also holds virtual conferences and Summer Camps that are great online resources for developers.
You are also invited to download this latest white paper, authored by Stephen Wynkoop, on understanding options and responsibilities for managing encryption in the Microsoft Azure Cloud.
Encrypting data in Microsoft SQL Server is easy to do, yet often difficult to understand because of the different encryption options offered in various versions.
It used to be said that “encryption is the hardest part of data security, and key management is the hardest part of encryption”. While that may have been true a few years ago, we are constantly working to make affordable, easy-to-use, defensible solutions that meet security best practices and industry compliance regulations. Separating and managing the encryption keys from the data they protect is still one of the biggest challenges in terms of doing an encryption project right, so let’s take a look at what encryption & key management options are available for SQL Server users.
If you are running the Enterprise Edition of SQL Server, version 2008 or newer, you have access to Microsoft’s architecture for encryption called Extensible Key Management (EKM). This provider interface allows for third-party key management systems to be easily incorporated in order to separate encryption keys from the encrypted data they protect. A key management solution should provide Windows client libraries, guidance, and sample code within the solution.
The SQL Server EKM architecture supports:
Transparent Data Encryption (TDE)
With TDE, the entire database table (including the logs you are collecting) is encrypted. It is a very easy mechanism to use for encryption and since it is transparent, no application level changes are needed, it only takes a few commands to implement. TDE protects data at rest, including backups and log files.
Cell Level Encryption
Also known as column-level encryption, this allows for you to selectively encrypt certain columns of information in your database. This option makes sense if you have large databases of information, and only access encrypted columns periodically.
If you are running older versions of SQL Server (pre-2008), or using non-enterprise editions such as standard, web, or express; you do not have access to TDE or EKM. You still have good options for protecting your data with encryption, just remember the encryption key needs to be separated from the encrypted data it protects.
When you don’t have the EKM architecture, another option for encrypting data in your SQL Server database is to perform encryption and decryption at the application layer using .NET-based encryption. All editions of SQL Server support the ability to perform encryption from within the .NET framework with very straightforward code functions.
C# and VB.NET Application Encryption
If you are developing in .NET you only need to plug in the client side application and implement a few lines of code for your encryption and decryption calls.
Column Level Encryption
Another approach would be to combine User Described Functions (UDFs) with triggers and views to help automate the encryption and decryption at the column level.
Moving SQL Server Data to the Cloud
As more companies migrate their applications and data to the cloud, there are security issues to consider before making that move. Microsoft Azure SQL Database (MASD) -which has also been called SQL Azure, SQL Server Data Services, SQL Services, Windows Azure SQL Database- is a cloud-based service from Microsoft offering database capabilities as a part of the Azure Services Platform. The service is easy to use and readily available, just know that there are some constraints and some features of EKM that are not available when using MASD.
Most businesses migrating to the cloud will choose to run virtual machines that contain the Windows OS and a full implementation of the SQL Server database. By using a virtual machine, encryption and key management, including EKM with TDE, can be fully supported and provide the level of security you expect and compliance regulations require!
You have many options still available for your key management solution when your data has been moved to the cloud. Our NIST validated, FIPS 140-2 compliant Alliance Key Manager solutions are available as:
- Hardware Security Module (HSM) - a hardened appliance that you can rack up in your own data center
- Cloud HSM - dedicated hardware device in our hosted cloud environment
- VMware - deploy as a virtual appliance
- Cloud - deploy in Windows Azure, Amazon Web Services, or IBM Cloud as a standard cloud instance or virtual private cloud
To learn more about encrypting data on SQL Server, managing encryption keys, and how we are helping companies protect their data with Alliance Key Manager, download the podcast on Encryption Options on SQL Server.
Understanding Options and Responsibilities for Managing Encryption in the Microsoft Azure Cloud
In this latest white paper, authored by Stephen Wynkoop (SQL Server MVP, Founder & Editor at SSWUG.ORG), Stephen will address how “data at rest is data at risk”, specifically looking at the Microsoft Azure Cloud as a selected platform. The author covers a wide array of information, and discusses in detail how critical it is to do the important work of protecting information in a way that works both with your applications and with the compliance regulations & requirements that impact your company and industry.
Each of the key topic areas below are addressed in detail in the white paper:
Architecture Decisions Drive Technology Approach
The options range from fully-managed data storage and access (Windows Azure SQL Database, WASD) to setting up a SQL Server with a Virtual Machine instance. Each certainly has its place, but there are big differences in options they support.
- Virtual Machines
- Key Decision Points, VMs
- Windows Azure SQL Database (WASD)
- SQL Server and Data Encryption Choices
Impact of Encryption
Encryption, and the impact of encryption on your systems, is a big area of concern for those deploying it. Three different areas are important to consider when impact on systems is considered.
- Backup and Restore Operations
- High Availability
Key Management Fundamentals
There are core best practices to consider while you’re deploying your selected solution. Some are procedural while others are software/hardware implementations. Keep in mind that the key is to protect your most important secret: the encryption keys. You must provide for protection of the encryption keys, while still providing for access, updates and rotation (key management) of those encryption keys throughout their lifecycle.
- Segregation of Duties
- Dual Control & Split Knowledge
- Key Rotation
- Protection of Keys
- Access Controls and Audits, Logging
The author also covers how Townsend Security’s Alliance Key Manager provides answers to these challenges of working with the Microsoft Azure Cloud, securing information with encryption, and the critical need to manage the keys. For more information on Alliance Key Manager for Windows Azure, download our solution brief or get started with a complimentary 30-day evaluation
Author Bio: Stephen Wynkoop
Stephen Wynkoop is the founder and editor for SSWUG. ORG – the SQL Server Worldwide User’s Group where he writes a column and maintains the site overall. SSWUG features a weekly video programs about the database and IT world, webcasts, articles, online virtual community events and virtual conferences several times a year. Stephen is a Microsoft SQL Server MVP and the author of more than 10 books, translated into at least 7 languages. Stephen has been working with SQL Server since the very first version, with a prior experience in database platforms that included dBase and Btrieve. Stephen can be contacted at email@example.com.
After our latest webinar “Encryption & Key Management with Microsoft SQL Server” there were a number of great questions asked by attendees and answered by security expert Patrick Townsend.
Here is an informative recap of that Q&A session:
Q: Are there any special considerations when deploying an encryption key manager in the cloud?
A: The cloud always presents some additional security challenges related to encryption and security in general and has the impression of being less secure and having some new challenges around security. In the cloud, the encryption key manager itself is only one component to consider, and you need a good FIPS 140-2 compliant solution like our Alliance Key Manager for SQL Server. You also need client side applications and libraries, so when you're thinking about moving to the cloud, paying attention to that particular issue is very important. Also know that not all libraries can easily migrate to cloud. We develop ours from the ground up with the cloud in mind, so all of our components that talk back to our key manager for encryption keys or encryption services are cloud-enabled and can be deployed there.
From a compliance point of view, it is very important to take a look at the Cloud Security Alliance (CSA.org) document on cloud security - version 3.
We also provide a compliance brief about domain 11 which talks about encryption key management and issues around the security in the cloud.
Q: Can you go a little more in-depth about what gets installed on SQL Server?
A: For the SQL Server platform (the client side software) Microsoft allows for Extensible Key Management (EKM) which allows vendors like Townsend Security to plug into their environment. Our Key Connection for SQL Server is an EKM provider and it is a GUI (Graphical User Interface) install, so you load it on your own SQL Server platform and it walks you through some questions:
- It will ask what SQL Server instances you want to protect
- It will ask for your authentication credentials in order to execute the necessary commands
- It will allow you to install certificates into the Windows certificate store that are used to communicate with the key manager HSM
- It allows you to define the location of your production and multiple high-availability failover key servers (most companies deploy one production and one HA key server. However, you can actually identify a more complex environment if needed)
- Then it allows you to actually test, right there in the install dialog, your connection to your key manager to confirm it is working the way it is supposed to
Side Note: We do not charge based on the number of endpoints that talk to our Alliance Key Manager. This is something that is unique to us as a vendor. We believe the encryption should be easy to do and affordable, so no additional license fees are required to actually use it. We want our customers to deploy encryption and use it to protect data.
Q: What are the minimum requirements for the key server?
A: The Alliance Key Manager product is available as either a hardware security module (HSM) device or virtual appliance. As an HSM it has a 1U server footprint, so it looks like any normal 1U server in your data center. However if you use our Alliance Key Manager Cloud HSM implementation, the encryption key manager is installed for you in a secure data center. It is also our philosophy that these are customer install processes, so we don't have consulting fees because it is a user deployed device. The server administration is done through a secure web browser session with our Townsend Security technical experts. The encryption key management security functions are done through a specific Windows application that talks to one or more key servers to actually create and deploy encryption keys whether they’re for Oracle or SQL Server EKM.
Also, we do provide our encryption key manager as a VMware virtual appliance, which allows you to deploy a key manager within your VMware infrastructure and we give you guidance on that process. With this option you don't have to purchase a hardware appliance, you can run it in your VM infrastructure or within a vCloud architecture. We strongly recommend that a review of the PCI Security Council's - Cloud Computing Guidelines as well as their guidance around virtualization when deploying a virtual encryption key manager.
Q: Does your key manager handle encryption and decryption or just key management?
A: Our encryption key management appliance itself does support on-board encryption and decryption.
Q: Can the same EKM module be used to encrypt servers in both data centers and cloud environments?
A: Yes. You can mix and match these anyway you want. You can use the same encryption key management solution for applications running in either environment, and they can talk to each other. You should be aware of a good security practice guidance around using different encryption keys for different kinds of applications, or different user communities, even in a high-availability data center or disaster recovery centers.
Q: What are the performance impacts on encryption?
A: Encryption always has performance impacts. Generally it can impose a penalty somewhere between 2% and 4% in terms of computing resources. Guidance from Microsoft regarding very large SQL Server databases show that performance can become an issue with certain operations. For example, encrypted indexes may require the entire index to be decrypted in order to be processed. Very large SQL Server databases can impose a bigger performance penalty than 4%. Sometimes, cell level encryption has been a better performing implementation than transparent data encryption. We support both TDE and cell level encryption, allowing our customers to use our product as needed.
We strongly recommend to our customers, especially those with larger more complex SQL Server applications, that they contact us and ask for a complimentary evaluation of our encryption key manager. The complimentary product trial is fully functional and allows an opportunity to do analysis of the performance impacts. We want you to give it a try and make sure you understand the impacts personally.
Q: Is there any limit to the number of servers that you can hook up to the key manager?
A: No. There's no license limit. If you're considering putting up multiple servers we recommend you engage our pre-sales support team and get some guidance on your project. You will never come to us for additional licensing fees around adding a new platform, new SQL Server, or any other application that talks to the encryption key management server. We are unique in the industry that way and is part of our philosophy; we believe encryption needs to go everywhere, data needs protection wherever it lives, and we should lower the barriers -not raise them- when it comes to getting data protection in place. You can connect as many client-side applications to the key server as you wish.
Q: How do you keep system administrators from getting at the data and the keys at the same time.
A: Tasks such as the management of the server, putting it on the network, establishing system logging options, setting the timeservers - all network administration processes - are segmented from the actual management of the encryption keys. Good security practice says that those should be different people engaging in those activities. We provide completely different interfaces to simplify separation of duties.
If you are using our Cloud HSM environment, it is not administered, managed, or accessed by the cloud provider nor by Townsend Security. You have exclusive access and control over your encryption key managers. We even provide a path if you wish to take the encryption key manager out of the cloud environment and install it in your own data center. We believe strongly that a security device should be exclusively under your control, not under the control or management of the cloud provider.
I encourage you to download the recording of the entire webinar and Q&A session: