Want to know how Microsoft does IT? IT Showcase is a preferred source of information technology expertise, straight from the top subject matter experts at Microsoft.
>
Protecting highly confidential sales data with Azure SQL Database
Protecting highly confidential sales data with Azure SQL Database
Protecting highly confidential sales data with Azure SQL Database
TechnicalCaseStudyIcon-Img  Technical White Paper
Published:
Feb 21, 2018
Star1 Star2 Star3 Star4 Star5
Enter below text to save article rating

One team’s move to the cloud came with added complexities—protecting sensitive data. The security tools in Microsoft Azure SQL Database help with authentication and authorization, database encryption, data access policies, and database monitoring. We migrated an important internal sales tool to Azure SQL Database on a PaaS cloud platform and met strict security policy requirements to protect highly confidential sales data.

Technical White Paper Blank Img
 
Print
Powered by Microsoft Translator BingLogo_Img

Protecting highly confidential sales data with Azure SQL Database

At Microsoft, Core Services Engineering (CSE, formerly Microsoft IT) is a close partner with the product teams. As a custodian of some of the company’s most sensitive data, we are held to high security standards. We use the built-in capabilities of Microsoft Azure SQL Database to meet stringent security controls for storing sensitive data in the cloud. Azure SQL Database applies our security requirements, helps strengthen our database security in the cloud, and allows us to use the Platform as a Service (PaaS) cloud model.

Azure SQL Database offers a broad suite of enterprise-grade security tools to help with authentication and authorization, database encryption, data access policies, and proactive database monitoring. We use Azure SQL Database to protect an important internal sales tool—Microsoft Enterprise Deal Discount Approval (DDA).

The DDA tool is a classic multi-tier application. DDA controls the approval process for licensing deals and discounts at our enterprise sales organization in France. The application, running on the Windows 10 Universal Windows Platform, helps simplify the discount approval workflow and offers improved discount control at the subsidiary.

The information processed by DDA, including discounts and other non-pricing concessions, is extremely sensitive. In fact, such data is labeled Highly Confidential, and “represents very sensitive business data which would certainly cause business harm if over-shared,” according to Microsoft data classification standards. We maintain security controls and requirements that any application processing Highly Confidential data needs to adhere to.

The DDA team that manages this tool needed first-class security measures as it moved this sensitive data to Azure SQL Database.

The evolution of DDA architecture

The DDA team started with simple architecture:

  • Azure SQL Database to store structured data
  • Azure Storage Account (Blob) to store files
  • Azure Storage Account (Queue) for messaging
  • App Service for the API layer—REST API
  • Mobile client

Figure 1 shows the simple architectural structure we started with.

This diagram shows the process flow in the first version of DDA.
Figure 1. DDA began with simple architecture

Over time, this architecture evolved to meet more stringent controls around network security, authentication, authorization, encryption, and security monitoring.

Network security

The threat model of the original architecture exposed risks. One risk is that both Azure SQL Database and Azure Blob Storage are accessible via public IP addresses. Both data stores contain sensitive information, so it is important to restrict access to them.

To achieve that, the DDA REST API App Service was deployed within an App Service Environment (ASE) inside a virtual network. The outbound address used by REST API to communicate with Azure Storage and Azure SQL Database is the public virtual IP (VIP) of the ASE. The VIP is a static IP address that can be found in the portal user interface for the ASE in the Properties settings. (See Network Architecture Overview of App Service Environments for details.)

Once the outbound address of the REST API is fixed, the Azure Storage and Azure SQL Database endpoints can be secured as follows:

  • Azure SQL Database firewall rules can be set up so that incoming connections to the DDA database are allowed only from the REST API’s fixed IP address. Without a fixed IP address, the firewall rules would need to allow connections from all Azure services, which would increase risk.
  • The fixed IP address of the REST API service is set inside a Security Access Signature (SAS) key that the REST API uses to connect to Azure Storage. The SAS key cannot be used from any other IP addresses. ( Using Shared Access Signatures (SAS) has more information about SAS.)

At this point, our basic DDA topology was enhanced by an ASE with a fixed IP address and the Azure SQL Database firewall, shown in Figure 2.

A diagram that shows how a firewall was added to DDA topology.
Figure 2. Enhanced DDA architecture

Since DDA users need to connect to the DDA REST API from the internet, this topology leaves the REST API App Service as a public endpoint. REST API requires very high-level privileges, including the ability to decrypt sensitive data stored in the database. If the REST API is compromised, all data could be accessed in cleartext. To remediate this risk, the network topology was further enhanced to use the Gatekeeper pattern with another ASE to isolate inbound traffic to the REST API, as shown in Figure 3.

A diagram showing the enhanced DDA topology with a second firewall.
Figure 3. Enhanced DDA architecture with Gatekeeper

Network security challenges

While this architecture meets the security requirements, it affects:

  • Performance. Adding an extra Gatekeeper layer to isolate the network increases latency for client requests. Furthermore, multiple authentication roundtrips increase the response time when new authentication is required.
  • Manageability. Both App Services may have to scale to accommodate a large volume of requests.
  • Supportability. Again, the extra Gatekeeper layer increases complexity. Instrumentation must be carefully planned to enable proper root cause diagnosis.
  • Cost. App Service Environments are a key element of securing the application; however, they can be costly for small applications. Consider using the ASE for multiple applications.

It is important to weigh the benefits against the disadvantages to balance tradeoffs that are consistent with your policies.

Authentication

DDA uses Azure Active Directory (Azure AD) to authenticate users, operators, and service-to-service authentication. The authentication flow, which is based on Authentication Scenarios for Azure AD, is shown in Figure 4.

A diagram that shows the DDA authentication flow.

Figure 4. The authentication flow

For more information about the authentication flow, see:

The REST API employs a trusted subsystem model, which means it connects to the database using a single database principal. To achieve non-repudiation, we obtain the user’s identity from the claim information in the on-behalf token and pass it in all the CRUD database operations via a stored procedure parameter.

An alternative approach is for the REST API service to impersonate users against the database. To do that, the REST API could open a token for Azure SQL Database on behalf of each application user (like what Gatekeeper does). One advantage of this approach is that the database audit log captures actions of individual application users directly, without relying on data passed as a Stored Procedure parameter. However, it also requires granting users access to the database and to keys in Azure Key Vault (see the Encryption section of this paper). We felt that this process poses a threat that we wanted to avoid.

Authorization

The REST API can connect to the Azure SQL database, because it contains a database user that is associated with the Azure AD principal representing the REST API. That database user has the CONNECT database permission, as well as other permissions required for the REST API to support its use.

The database user for the Azure AD principal has been created using this Transact-SQL statement:

CREATE USER [<username@microsoft.com>] FROM EXTERNAL PROVIDER;

The permissions configuration in the database follows the least-privilege principle. All database users, including the user representing the REST API, are granted the minimum permissions needed for their jobs.

CREATE ROLE [DDA_ProcedureExecution] AUTHORIZATION [dbo];

GO

GRANT EXECUTE ON DDA_UpdateDealDetails TO [DDA_ProcedureExecution];

GO

GRANT EXECUTE ON DDA_UpdatePricingDetails TO [DDA_ProcedureExecution];

GO

GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [DDA_ProcedureExecution];

GO

GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO [DDA_ProcedureExecution];

GO

ALTER ROLE [DDA_ProcedureExecution] ADD MEMBER [<username@microsoft.com>];

GO;

The first Two GRANT statements enable the DDA_ProcedureExecution role to execute stored procedures that are required for supported REST API uses. The role has no direct permissions on the underlying views and tables, which means the middle tier cannot execute arbitrary queries against those views and tables.

Two more GRANT statements give the role access to Always Encrypted key metadata, which is required to query encrypted data. (Always Encrypted is described in the next section.)

The database also contains a database user that maps to an Azure AD group, representing the DDA Operations team. (we are using the <DDAOperationsGroupName> placeholder for the group name in the below script).

CREATE USER [<DDAOperationsGroupName>] From External Provider;

This database user is a highly privileged principal that is permitted to directly read and write data, execute stored procedures, and alter Always Encrypted key metadata.

ALTER ROLE [DDA_ProcedureExecution] ADD MEMBER [<DDAOperationsGroupName>];

ALTER ROLE [db_datawriter] ADD MEMBER [<DDAOperationsGroupName>];

ALTER ROLE [db_datareader] ADD MEMBER [<DDAOperationsGroupName>];

GRANT ALTER ANY COLUMN MASTER KEY TO [<DDAOperationsGroupName>];

GO;

GRANT ALTER ANY COLUMN ENCRYPTION KEY TO [<DDAOperationsGroupName>];

GO;

Note that the <DDAOperationsGroupName> group in Azure AD has no permanent members. Thus, no operator has standing access to the database. To troubleshoot a production issue in the database, an operator must request temporary access via an internal Microsoft access management tool. Once a manager approves the request, the operator’s Azure AD principal is temporarily added to the <DDAOperationsGroupName> Azure AD group. The user principal is automatically removed from the group after a short time.

Encryption

Our standards specify that all data at rest must be protected from offline physical and virtual media attacks. To meet this requirement, DDA uses Azure Storage Service Encryption and SQL Transparent Data Encryption (TDE) for Azure SQL Database. TDE encrypts data at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. This protects data stored in data files, transaction logs, and in backup files.

Special requirements apply to Highly Confidential data, such as discount amounts and other sensitive sales deal information, that DDA processes. Such data must be encrypted before going to a data store, such as Azure Storage or Azure SQL Database. To meet this requirement, DDA uses Client-Side Encryption for Microsoft Azure Storage and Always Encrypted for Azure SQL Database.

Typically, implementing a client-side encryption solution for a relational database is hard and expensive. It requires a developer to identify all queries that target columns storing sensitive information and to modify the application code to inject encryption and decryption routines before and after each query. Client-side encryption makes it usually impossible for a database system to support any queries or computations on encrypted data. In addition, developers need to implement a solution for provisioning and managing the encryption keys.

Always Encrypted allows DDA developers to avoid those challenges. Always Encrypted is an industry-first technology in Azure SQL Database that transparently encrypts data on the client-side, before the data is sent to the database. At the same time, Always Encrypted ensures that point lookup queries and equality joins are supported on encrypted columns. This capability enabled DDA developers to implement keyword-based search on sales deals, while protecting sensitive deal information from malware or rogue insiders.

DDA stores column master keys in Azure Key Vault – the most convenient option for applications hosted in Azure. The master keys are regularly rotated. The DDA Operations team has automated key provisioning and key rotation by developing PowerShell scripts, following the examples in the documentation: Always Encrypted (Database Engine).

Security monitoring

Enforcing security policies and monitoring security across an organization can be a challenge. The DDA team actively monitors their environment for security events and analyzes those events to determine if they are incidents. By integrating their monitoring system with an array of Azure services, the DDA team makes it easy to respond to a potential incident. (See SQLDatabase.md for more information.)

The DDA Operations team monitors security enforcement in three ways:

  • Apply consistent security policies.
  • Actively monitor all resources.
  • Create tailored alerts to be notified incidents or policy violations.

To apply a consistent security policy to every new SQL database, the operations team uses JSON config files. This template of feature configurations helps to enforce technical control procedures that we must abide by. The JSON configurations execute the Security Development Lifecycle (SDL) Technical Control Procedures mentioned earlier, like enabling SQL Database Audit, Threat Detection, and Transparent Data Encryption.

For database monitoring, SQL Database’s built-in audit logs allow the DDA team to track database events, helping to maintain regulatory compliance, understand database activity, and find discrepancies that could indicate security issues. Additionally, with SQL Audit Logs now stored in Blob storage, the DDA team can benefit from better performance, higher granularity, and lower storage cost for SQL Database Audit.

More broadly across the architecture, logs from the following Azure services are collected and stored in a dedicated storage account:

Having all this available data is important for forensics but trying to find the right information in Blobs can be time consuming. To facilitate log analysis, most of this data is centralized in a single Operation Management Suite (OMS) workspace. This way, the team can use a unified Search Engine.

Beyond a centralized workspace for analyzing logs, the team relies extensively on automated alerts to notify them of security events. Using scripts provided by the Microsoft security team, the DDA team was able to create relevant Insights based alerts for their services. The team also relies on the built-in proactive monitoring tool, Azure SQL Threat Detection, to notify them of potential malicious activities. SQL Threat Detection runs algorithms to detect potential vulnerabilities, potential SQL injection attacks, and anomalous database access from an unusual location or principal.

For example, with SQL Threat Detection, the team can quickly learn about a suspicious login through an alert in Azure Security Center. In accordance with the team policy, when a team member picks up the investigation of a security alert, they dismiss the alert in Azure Security Center, so the rest of the team knows it's being addressed. The Threat Detection alert will show the related audit logs around the suspicious activity in OMS, and the team member can gain more insight by analyzing and querying other events that happened around the same time.

Because OMS is the team's central monitoring tool, not only are database alerts integrated there, but also other Azure service alerts, such as from Azure Key Vault. The team also set their own queries in OMS to create custom alerts. These alerts are tailored to their security policy, and capture events that are non-standard behaviors typically done by an application, not a human. Examples include someone authenticating using SQL authentication (instead of Azure AD authentication) to a SQL database, or someone trying to read data without using a stored procedure. They also have email alerts if something other than their REST Service layer tries to access keys directly in Key Vault (such as through the Azure Portal).

Besides responding to alerts, members of the DDA Ops team regularly check the subscription health and application health dashboards on OMS. These dashboards show the team how well their Azure resources are adhering to CSE policies, which they had translated into controls within OMS.

The DDA Ops team also wants to track any drift that has occurred in their security policies. OMS helps the team keep track of how well their resources are adhering to the security policies that the team defined for their databases (which were shown in JSON config in the beginning of this section).

Similarly, for broader Azure services, the DDA Ops team can use Azure Insights to create rules for Azure-wide operations that would violate their policies. Azure Insights runs on Azure Activity Log and role-based access control, generating alerts for any suspicious activity that manipulates access or important resources. Some of the actions they track are changing TDE configuration, altering firewall or audit rules, and modifying Key Vault access policies.

Securing apps in the future

We aim to eventually have all applications in PaaS. By moving to PaaS, we can unlock advantages that only the PaaS model offers, such as out-of-box scale-outs and scale-ups, better cost control, and easier support for mobile applications.

The DDA Operations team is constantly looking to improve their application security. Their security goals go beyond our requirements, as they look for more ways to secure their system. For example, one main security focus is to add Virtual Network support. They will also expand their use of Azure Key Vault and adopt Bring-Your-Own-Key support for SQL Transparent Data Encryption, so they can use their own key from Azure Key Vault for encryption-at-rest in their databases. DDA is also evaluating the new confidential computing capabilities in Always Encrypted to support even richer queries on encrypted database columns and simplify key management via in-place encryption.

Another focus is to improve the monitoring system and overall security manageability as their application and team scales. They are integrating OMS and email alerts into Microsoft Visual Studio Online (VSO), so that VSO can serve as the central monitoring and incident response destination for their Development Operations team (DevOps). They would also like to integrate email alerts into our ticketing system for faster response and better organization. Today, their development environment also receives a high volume of email alerts, so they are working to refine their alerting rules and remove false positives before deploying to production.

For incident response, they plan to improve just-in-time (JIT) access provisioning for the DevOps team by integrating Azure AD Privileged Identity Management with role-based access control. This eases the management, control, and monitoring of access within the team. The team is also continuing to invest in automation by implementing a bot for JIT access requests and working toward unsupervised key and credential rotation.

Putting it all together

To build strong network security, we enforce fixed IP addresses and only allow traffic from whitelisted addresses using SQL Firewall rules. Two-factor authentication with Azure AD controls the authentication flow through Azure Key Vault, Azure Storage, and Azure SQL Database. SQL Transparent Data Encryption encrypts our databases at rest, and SQL Always Encrypted encrypts the most sensitive sales information and stores the encryption keys on the client side.

The cloud is unfamiliar territory for companies, especially when they consider storing sensitive data. We were able to use the built-in, multi-layered security functionality in Azure SQL Database to meet our own stringent security requirements. By coupling Azure SQL Database’s security with the suite of Azure monitoring tools, other companies can employ disciplined security protocols and operational controls while benefiting from the cost savings and manageability of Azure.

Appendix

The Digital Security and Risk Engineering (DSRE) group at Microsoft is a central organization within CSE that, among other services, develops guidelines for internal adoption of technologies. DSRE automation and guidance helps us manage internal applications in two major areas: Security Development Lifecycle (SDL) and Operational Security Assurance (OSA). It also offers consulting services to help create secure service topology. SDL procedures are typically managed and implemented by development teams; OSA procedures are typically addressed by operations teams.

Tables 1 and 2 contain examples of SDL and OSA procedures that helped the DDA team achieve compliance.

Table 1. Sample DDA SDL procedures

Category

Applicable procedures

Azure services

Authentication

Implement proper authentication method

Azure Active Directory authentication for Azure SQL Database lets us rely on central identity management and central policies (password rotation frequency, password strength policy, etc.).

Authorization

Implement access control to check user authorization

Do not use high privilege accounts

Contained Database User from external provider.
SQL Firewall in combination with App Service Environment isolates access to the Azure SQL Database.

Cryptography

Create and manage keys and certificates securely

Azure Key Vault provides storage for secrets such as connection strings, Azure Storage Shared access signature tokens and encryption keys.

Data Handling

Secure sensitive data in storage and code

Always encrypted and Transparent data encryption are available for Azure SQL Database.

Secure sensitive data in transit

SSL/TLS is available for Azure SQL Database. Furthermore, encryption is enforced by default.

 

Table 2. Sample DDA OSA technical control procedures

Category

Applicable procedures

Azure service

Auditing and logging

Configure appropriate security event logging

Appropriate retention of security event logs

Subscribe to or implement appropriate security monitoring

Azure SQL Database Auditing provides the ability to log activities for the right duration in durable storage.

Auditing is also available for other services used by DDA.

Auditing data can be used for custom alerts with Operations Management Suite integration and for forensic analysis if required.

Azure SQL Database Threat Detection enables detection and response to potential threats.

Azure Security Center (ASC) provides a record of Azure SQL Database Threat Detection Alert as well as a dashboard to monitor for Security Health.

Cryptography

Use approved Secret Storage Solution

Use hardware security modules (HSM) to protect private keys

Azure Key Vault  offers hardware security modules to protect keys.

Desired State Configuration

Implement SQL Server Baseline Configuration

Implement Web Server Security Baseline Configuration

Azure Security Center provides the ability to set a number of configuration policies for some the services used by the DDA application.

Data Handling

Support Secret and Key Rotation

Always Encrypted support Column Master Key rotation

Network Isolation

Endpoints enabling access to sensitive data should not be publicly accessible

 

App Service and Azure Storage Account are public endpoints. Azure SQL Database provides a firewall but to be efficient, fixed IP addresses are needed. ISRM recommended the use of App Service Environment to isolate endpoints from public traffic. This enables configuration of tighter rules on the SQL Server firewall as well as using SignedIP parameter in Storage Shared Access Signature tokens.

For more information

Microsoft IT Showcase

microsoft.com/itshowcase

Strengthen your security posture

 

© 2018 Microsoft Corporation. This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

X
Share Widget Slider share icon
Share

Share on Facebook

New to Facebook?
Get instant updates from your friends, industry experts, favorite celebrities, and what's happening around the world.

Share a link with your followers

twitter login icon
loader
Tweets
New to Twitter?
Get instant updates from your friends, industry experts, favorite celebrities, and what's happening around the world.

Share on LinkedIn

New to LinkedIn?
Get instant updates from your friends, industry experts, favorite celebrities, and what's happening around the world.
shareicon
Share
Feedback
icon_close
How Microsoft does IT
Overall, how satisfied are you with our site?
Additional Comments:
Please enter your feedback comment of minimum 30 characters