Townsend Security Data Privacy Blog

Oracle, SQL Server, and Encryption Key Management

Posted by Paul Taylor on Aug 22, 2012 10:59:00 AM

I often speak with organizations that need to employ encryption and external key management for multiple relational databases they are using to store encrypted data.  Often this is a combination of Oracle and Microsoft SQL Server databases.   

Transparent Data Encryption (TDE) is used within both the Microsoft SQL Server and Oracle Database universes to provide encryption services at the tablespace level.  Many companies employ TDE and external encryption key management to meet the concept of "Separation of Duties" as required by PCI DSS and other compliance regulations.  Also, TDE is often easier to implement than column level encryption that may require programming changes to your application layer.  

key management sql serverIn Microsoft's SQL Server Enterprise edition 2008/2012 you have access to Extensible Key Management (EKM).  When EKM is enabled, SQL Server users can use encryption keys stored on external key managers, as opposed to accessing local key stores, which doesn't line up with compliance requirements.  Also, another benefit of using EKM is that you can easily take advantage of TDE as your database encryption approach.  

If you're running versions of Microsoft SQL server that don’t support EKM, don't worry.  You can still take advantage of the added features and security of using an external key manager with our encryption key management HSM, Alliance Key Manager (AKM).  AKM fully supports the entire Microsoft SQL Server product line.  You’ll just have make some programming changes to your application code to perform the necessary API calls to the key manager and you'll be set up to do key retrieval.   To help you with the process, we provide sample code and the .Net key retrieval assemblies to add to your project.  Additionally, we have C# and VBNET sample code that shows how to retrieve a key from the key server.

Much like Microsoft SQL Server, in the land of Oracle you need to be running Oracle Enterprise Edition with the Advanced Security option.  This can often be a pricey upgrade and I find that quite a few organizations would rather do column level encryption due to this fact.  oracle key managementAKM fully supports the path to column level encryption within the Oracle 10g and 11g environments.  Again your approach will include making coding changes to your application layer to perform key retrieval from AKM.  To help you with this on the Oracle front we provide some PL/SQL sample code for you to work from.

For more information on the importance of encryption key management, download our white paper "Key Management in the Multi-Platform Envrionment" and learn how to overcome the challenges of deploying encryption key management in business applications.

Click me

Topics: Oracle, Encryption Key Management, SQL Server

Case Study: Encryption Key Management with SQL Server and Oracle

Posted by Luke Probasco on Feb 23, 2012 10:01:00 AM

sql server oracle encryption key managementAs a company that provides NIST-certified encryption and FIPS 140-2 encryption key management, we need to secure data on a number of different platforms. Lately we have been coming into several cases where a customer needs encryption and key management on both Microsoft SQL Server and Oracle databases. Below is an email exchange with a customer who came to us “looking for a product to store, generate and manage keys that we use to encrypt/decrypt credit card information inside both SQL Server and Oracle Databases on Windows and UNIX.” We hope this discussion helps with your encryption project.

Customers Environment:

1. Encryption key is generated on the Windows platform through custom software, but if we can move away from that to an automated approach all the better. The key is moved to Oracle manually and we want to replace this with automation.

2. Credit card information is interfaced to our system then automatically encrypted using the key and stored in a SQL 2008 Enterprise Edition server table (only the one column is encrypted).

3. The SQL Server data is then sent to our Oracle database as encrypted data where it is stored in one column of a table.  It is then decrypted and sent to a payment services company who send us back a billing code, which replaces the encrypted credit card number.  So most of the encrypted credit cards are only stored for a short period of time, but some with problems are stored much longer.

Questions Addressed:

Our Alliance Key Manager (AKM) Hardware Security Module (HSM) provides full life-cycle management of encryption keys for PCI DSS and PII compliance. It works with applications and databases on a variety of platforms including Windows, Linux, UNIX, and IBM mainframes. We support the Microsoft EKM architecture for automatic encryption of SQL Server using TDE or Cell Level Encryption. Our customers also use AKM to manage keys for Oracle database applications, and support for Oracle TDE (requires Oracle Enterprise Edition and Advanced Security) is in our product roadmap.

SQL Server:

Because you are running SQL Server Enterprise edition, you have two options. The first is to deploy Extensible Key Management (EKM), which is supported by our Alliance Key Manager. EKM gives you the ability to automate encryption through Transparent Data Encryption (TDE) or Cell Level Encryption. TDE is usually the choice people make as it is the easiest to deploy and does not require any programming. Cell Level Encryption requires a bit of programming, but still fully automates the storage of keys on the key server.

The second approach is to use our Windows .NET key retrieval assembly to retrieve an encryption key from our key server and perform encryption in your application. Since you are already doing an encryption with a local key, this would probably be a pretty simple task. It appears that you might need this type of granular approach to support your current integration between SQL Server and Oracle. We have C# and VBNET sample code that shows how to retrieve a key from the key server.

Additionally, we support Windows 2003 for either approach and both of these approaches will meet PCI DSS standards for key management.


Our customers are currently using our key manager to encrypt data in Oracle databases. We provide sample code in Java, Perl, PHP, and other languages to support this. We also provide a shared library that does secure key retrieval from a variety of platforms, and also sample code that shows how use the shared library in PL/SQL.

encryption key managementKey Generation:

Encryption keys are generated on the encryption key manager using a secure administrator's console installed on a Windows PC. The interface to the key manager is a wire protocol and you can drive it from any application platform that supports SSL/TLS. All of your OS's do so. Our business users attempting to meet PCI DSS requirements for Dual Control and Separation of Duties typically stick to using our secure key management console.

Encryption on HSM:

Our Alliance Key Manager also supports encryption on the server. Rather than retrieving the key to your business application, you can send the data to the key manager with the name of the key you want to use, and it will return the encrypted or decrypted data back to your application on the secure connection. No key leaves the key server with this approach - just an alternative that is worth mentioning.

We hope that this case study can help you with your encryption project.  Listen to our podcast “Encryption Key Management with Microsoft SQL Server 2008 to learn how easy it is for your organization to start encrypting data on your SQL Server.

Click me

Topics: Alliance Key Manager, Oracle, SQL Server 2008, Encryption Key Management, Case Study

Data Privacy in a De-Perimeterized World

Posted by Patrick Townsend on Feb 25, 2011 8:33:00 AM
De-perimeterizationI just listened to a discussion of database security hosted by Oracle that was very well done. At one point the discussion turned to current threats and how the Enterprise has lost the ability to use perimeter protection for sensitive data. This has been a topic of much discussion in the security area for the last few months. Perimeter protection is based on the use of Firewall and similar technologies to keep the bad guys out, but this approach is failing with the advance of more sophisticated attacks, the use of social media by large organizations, the advance of mobile technologies, insider threats, and the migration of applications to cloud platforms. The trend is called “de-perimeterization” and represents a bit of a challenge to organizations that need to protect sensitive data.

Vipin Samir and Nishant Kaushik did a great job of describing the how the process of de-perimeterization has forced companies to fall back on user access controls to protect data. But user access controls are notoriously weak.  Weak passwords and sophisticated password cracking routines make it almost impossible to properly secure a database. So what is a security administrator to do?

Here are the suggestions from the panel that are a part of a defense-in-depth strategy:

Use Encryption to Protect Data:
Companies should use encryption at the database level or column level to protect data. This will secure data at rest on backup tapes and on disk in the event a drive is replaced. Encryption is an important part of the data protection strategy, but it needs to be combined with other techniques.

Use Good Key Management:
Protecting encryption keys is the most important part of the encryption strategy. Good key management techniques are needed, and the keys must be separated from the data they protect. Without this separation from protected data it is impossible to implement separation of duties and dual control – important parts of the key management strategy. See our Alliance Key Manager solution for more information about securing encryption keys.

Separation of Duties:
Because the threat from insiders is rising, it is important that the management of encryption keys be separate from the management of databases. Database administrators responsible for our relational databases should not have access to encryption key management, and security administrators should not manage databases. This is a core principal in data security regulations such as PCI DSS, but is often overlooked.

Context Sensitive Controls and Monitoring:
The last important step is to be sure that data access controls are sensitive to the data and its context. Bill in shipping has access to the order database, but should he really be decrypting the credit card number? Does your encryption solution detect and block this type of event? How will you monitor this security event? Or, Sally is authorized to view HR data from the accounting application, but should she really be using FTP to transfer this data? Normal encryption functions would not provide adequate protection from these types of data access. Context sensitive controls are needed to augment encryption.

When we started planning for automatic encryption in our Alliance AES/400 product two years ago, we took care to implement context sensitive controls right in the decryption APIs. That is now available in V7R1 of the IBM i operating system. We avoided the error of basing these controls on user account authorities and native OS security. Just because the operating system says you have read access rights to a database table, doesn’t mean you should be decrypting the social security number or using FTP to transfer the file. I’m happy with our implementation that is based on explicit authorization by a security administrator, and application white lists.

You can get more information and request an evaluation version of our Alliance AES/400 solution here.

You can find the Oracle presentation here. Look for “How secure is your Enterprise Application Data?”


Topics: Key Management, De-Perimeterization, Oracle, Separation of Duties, Alliance AES/400, Encryption Key Management, Defense-in-Depth, automatic encryption, AES Encryption