Townsend Security Data Privacy Blog

KEY MANAGEMENT FOR SQL AZURE DATABASE

Posted by Patrick Townsend on Nov 15, 2022 4:57:20 PM

 

Our customers often ask about encryption key management for the Microsoft SQL Azure Database on the Azure cloud. SQL Azure Database is the Microsoft Azure Database-as-a-Service offering based on SQL Server. It is a natural question because SQL Server has a convenient interface for plugging in a key management solution through their Extensible Key Management (EKM) interface. And our Alliance Key Manager has supported this for more than a decade and is available in the Azure marketplace. 

Here’s the rub: 

Unlike normal SQL Server, the Azure SQL Database offering does not support the normal SQL Server key management interface. It does support encryption of the database, but only by using the Microsoft Key Vault service. So Azure customers are locked out of managing and controlling the encryption keys when using SQL Azure Database. 

This is not a problem with Azure itself! We have customers who have deployed SQL Encryption Key Management in Windows Azure Server in a virtual machine on Azure and use our Alliance Key Manager in Azure with no problems! Microsoft does not allow the use of a key manager and only allows the Azure Key Vault or a Bring Your Own Key (BYOK) option..

Is there anything you can do? 

Sure! Let me describe one approach you can use in a web application that uses SQL Azure Database that gives you exclusive control and access to your encryption keys, and supports a real time mirroring of encryption keys to a key server outside of the cloud. And a bonus is that if you are mirroring data out of the cloud to an on-premise SQL Server database, the key management synchronization and failover will be automatic.

Here is what to do in Azure:

First, deploy Alliance Key Manager right from the Azure Marketplace. It will automatically license for a 30 day no-cost evaluation period (Azure charges may apply). When you access the key manager in Azure Marketplace you will have a link to documentation, and you will be eligible for technical support. Create an AES key to use for encrypting data in SQL Azure Database. Here is the quick start guide to help you get started:

https://docs.townsendsecurity.com/akm_for_microsoft_azure_quick_start_guide

Then, modify your Windows .NET application to make a call to Alliance Key Manager to encrypt or decrypt information using the AES key you created before you insert or update data in a column. Alliance Key Manager provides a simple Windows .NET SDK to make this easy. There is no charge for the SDK and you can download it from the Townsend Security website. Here is the link to the Windows .NET SDK:

https://docs.townsendsecurity.com/akm_guide_for_windows_dot_net_developers

Backups of the Azure SQL Database and all data you copy out of Azure will now be encrypted and under your control. 

What to do in your data center:

You can easily mirror encryption keys from Azure to your own data center. Download Alliance Key Manager for VMware, launch it in your VMware environment, and set up mirroring between Alliance Key Manager in Azure and Alliance Key Manager in your data center. Keys are mirrored in real time and your on-premise applications can use the same logic as in the cloud to decrypt data as needed. Here is the VMware quick start guide:

https://docs.townsendsecurity.com/akm_for_vmware_quick_start_guide

Your applications in your on-premise deployment can now use the same Windows .NET SDK as mentioned above to do decryption when needed.

Voila!

You now have your data encrypted in SQL Azure Database, in your on-premise SQL Server database, and you have full control of your encryption keys! You also have a lot more flexibility about your choice of Cloud Service Providers. 

A few more thoughts:

Triggers, UDFs and Stored Procedures

If modifying your applications is not feasible or costly, consider adding Triggers and Stored Procedures to the database to achieve encryption and decryption tasks. This can be much easier to implement than making code changes. See the resources below to get started.

How to implement User Defined Functions and Stored Procedures in Azure SQL Database:

https://bookshelf.erwin.com/bookshelf/public_html/2020R1/Content/User%20Guides/erwin%20Help/Define_SQL_Azure_Stored_Procedures.html

And

https://www.sqlshack.com/executing-stored-procedures-from-data-pipelines-in-azure-data-factory/

And Alliance Key Manager provides guidance on Triggers and Stored Procedures:

https://docs.townsendsecurity.com/akm_guide_for_windows_dot_net_developers

Mirroring keys in the cloud

Sometimes you are not mirroring SQL Azure Database data to your on-premise database. If you have a backup strategy that involves failover to another Azure availability zone be aware that you can run a second copy of Alliance Key Manager in that zone. Alliance Key Manager will mirror encryption keys across any availability zones and regions.

Mirroring keys to AWS

If you really want to mirror your encryption keys out of the Azure cloud, but don’t want to bring the keys in-house, you can mirror them to AWS! Alliance Key Manager is also available in AWS and fully supports cross-cloud key mirroring.

Alliance Key Manager for Windows Azure - complimentary product evaluation

Topics: Encryption, Microsoft Azure, KMS

Data Protection in the Cloud & PCI DSS - Logs and Log Monitoring (Part 3)

Posted by Patrick Townsend on Mar 18, 2015 9:16:00 AM

This is the third part in our series looking at recent announcements by Amazon, Microsoft and other cloud service providers regarding new encryption and key management services. Let’s talk about log collection and active monitoring as a security best practice, and as a requirement to meet PCI DSS security requirements. Since the PCI DSS guidelines implement common security best practices, they are a good starting point for evaluating the security of any application and platform that processes sensitive data. Following the practice of the first part of this series we will use the PCI document “PCI DSS Cloud Computing Guidelines, Version 2.0” as our reference point, and add in some other sources of security best practices. Even if you don’t have to meet PCI data security requirements, this should be helpful when evaluating your security posture in the cloud.

Download Whitepaper on PCI Data Security

Collecting system logs and actively monitoring them is a core component of every cyber security recommendation. Cybercriminals often gain access to IT systems and go undetected for weeks or months. This gives them the ability to work on compromising systems and stealing data over time. Active monitoring is important in the attempt to detect and thwart this compromise.

Here is what PCI says about active monitoring in Section 10 of the PCI DSS (emphasis added):

Review logs and security events for all system components to identify anomalies or suspicious activity.

Many breaches occur over days or months before being detected. Checking logs daily minimizes the amount of time and exposure of a potential breach. Regular log reviews by personnel or automated means can identify and proactively address unauthorized access to the cardholder data environment. The log review process does not have to be manual. The use of log harvesting, parsing, and alerting tools can help facilitate the process by identifying log events that need to be reviewed.

In recognition of the importance of ongoing, active monitoring the National Institute of Standards and Technology (NIST) provides this guidance in their Special Publication 800-137 “Information Security Continuous Monitoring (ISCM)” guidance:

The Risk Management Framework (RMF) developed by NIST, describes a disciplined and structured process that integrates information security and risk management activities into the system development life cycle. Ongoing monitoring is a critical part of that risk management process. In addition, an organization’s overall security architecture and accompanying security program are monitored to ensure that organization-wide operations remain within an acceptable level of risk, despite any changes that occur. Timely, relevant, and accurate information is vital, particularly when resources are limited and agencies must prioritize their efforts.

And active monitoring is a component of the SANS Top 20 security recommendations:

Collect, manage, and analyze audit logs of events that could help detect, understand, or recover from an attack.

Deficiencies in security logging and analysis allow attackers to hide their location, malicious software, and activities on victim machines. Even if the victims know that their systems have been compromised, without protected and complete logging records they are blind to the details of the attack and to subsequent actions taken by the attackers. Without solid audit logs, an attack may go unnoticed indefinitely and the particular damages done may be irreversible.

Because of poor or nonexistent log analysis processes, attackers sometimes control victim machines for months or years without anyone in the target organization knowing, even though the evidence of the attack has been recorded in unexamined log files.

Deploy a SIEM (Security Incident and Event Management) or log analytic tools for log aggregation and consolidation from multiple machines and for log correlation and analysis.

This is why actively collecting and monitoring system and application logs is critical for your security strategy.

Implementing this critical security control in a cloud environment presents some special challenges. Here is what the PCI cloud guidance says:

Additionally, the ability to maintain an accurate and complete audit trail may require logs from all levels of the infrastructure, requiring involvement from both the CSP and the client. For example, the CSP could manage system-level, operating-system, and hypervisor logs, while the client configures logging for their own VMs and applications. In this scenario, the ability to associate various log files into meaningful events would require correlation of client-controlled logs and those controlled by the CSP.

It is not enough to collect logs from a few selected points in your cloud application environment. You need to collect all of the logs from all of the components that you deploy and use in your cloud application. This is because the effectiveness of active monitoring depends on the correlation of events across your entire application, database, and network and this includes the cloud providers systems and infrastructure. Here is what ISACA says about security event correlation:

Correlation of event data is critical to uncover security breaches because security incidents are made up of a series of events that occur at various touch points throughout a network--a many-to-one process. Unlike network management, which typically is exception-based or a one-to-one process, security management is far more complex. An attack typically touches a network at multiple points and leaves marks or breadcrumbs at each. By finding and following that breadcrumb trail, a security analyst can detect and hopefully prevent the attack.

Your encryption key management system is one of those critical system components that must be monitored and whose events should be aggregated into a unified view. Key management logs would include encryption key establishment and configuration, encryption key access and use, and operating system logs of every component of the key management service. You should be able to collect and monitor logs from all parts of your applications and cloud platform.

Unfortunately, current key management services from cloud providers only provide a very limited level of access to critical component logs. You might have access to a limited audit trail of your own access to encryption keys, but no access to the key service system logs, HSM access logs, HSM audit logs, or HSM operating system logs. Without access to the logs in these components it is not possible for you to implement an effective log collection and active monitoring strategy. You are working in the dark, and without full access to all logs on all components of your cloud key management service you can’t comply with security best practices for log collection, correlation, and active monitoring.

Since key management systems are always in scope for PCI audit and are extensions of your application environment it is difficult to see how these new cloud key management services can meet PCI DSS requirements for log collection and monitoring as currently implemented.

Does this mean you can’t implement security best practices for key management in the cloud? I don’t think so. There are multiple vendors, including us (see below), who offer cloud key management solutions that provide full access to key management, configuration, key usage, application, and operating system logs.  You can deploy a key management service that fully supports security best practices for log collection and monitoring.

In part 4 of this series we’ll look at the topic of key custody and multi-tenancy and how it affects the security of your key management solution in the cloud.

Patrick


Resources

Alliance Key Manager for AWS

Alliance Key Manager for Azure

Alliance Key Manager for VMware and vCloud

Alliance Key Manager for Drupal

 

download the Whitepaper: Meet the Challenges of PCI Compliance

Topics: PCI DSS, Amazon Web Services (AWS), logging, cloud, Microsoft Azure