dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted June 7, 2018

WEBINAR:
On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


Azure SQL Database and GDPR Compliance

By Marcin Policht

In recent years, technology landscape has undergone dramatic changes, driven primarily by cloud computing and a continuously increasing level of attention dedicated to security, privacy, and compliance. As recent revelations related to improper use of consumer data by social network providers and research firms have shown, this attention is well warranted. Similarly, surging numbers of increasingly sophisticated cyber attacks further underscore the importance of proper protection and detection mechanisms guarding individuals and organizations alike. One of the more significant initiatives that attempts to address these challenges is General Data Protection Regulation (GDPR), which is the regulation imposed by the European Union (EU) on May 25, 2018. While its primary purpose is to legislate data protection and privacy within the boundaries of EU, it applies to any business or organization (regardless of their location) that conducts business with the European Economic Area. In this article, we will explore how Azure SQL Database could help with addressing the GDPR requirements. Note however, that you should always consult with a legal professional in order to ensure full compliance.

One of the crucial aspects of GDPR regulations deals with measures that must be implemented in order to facilitate control and protection of personal data. These measures are described in the following sections of the GDPR legislation:

  • GDPR article 25 stipulates that data must be protected by design and by default. This includes implementing controls in regard to data access, data collection scope, and data retention.
  • GDPR article 30 dictates that all data processing operations must be logged and monitored.
  • GDPR article 32 references security (including pseudo-randomization and encryption), resiliency (including recoverability, high availability, and disaster recovery), and fidelity (including regular testing) mechanisms that data storage and processing involve.
  • GDPR article 35 describes the requirements regarding performing and documenting risk assessment.

Complying with these measures typically requires significant effort, which translates into additional cost and management overhead. In order to minimize this effort, it is worthwhile considering the use of technologies that incorporate mechanisms that GDPR demands. Azure SQL Database is an example of such technology. Let's review some of its features that illustrate this point.

To start, in order to properly control and protect customer data, it is critical to be able to properly identify it. The table-based structure of Azure SQL Database yields itself to cursory examination of the data content based on column names. An in-depth analysis can be performed by relying on the Full-Text Search capability of the database engine, which has been part of the Azure SQL Database offering since April 2015.

The first line of defense when controlling access to Azure SQL Databases involves IP address and port filtering on the Azure SQL Server instance level, implemented as firewall rules defined in its master database. The firewall allows inbound traffic from designated IP addresses or address ranges on TCP port 1433 only. By default, all external connections are blocked, so you need to explicitly enable them by specifying the public IP address (or IP address range) assigned to your Internet entry point. You also have the option of controlling network-level access by using firewall rules applied to individual databases (for more information, refer to Microsoft Azure SQL Database Security - Firewall Configuration). In addition, Virtual Network Service Endpoints allow you to restrict inbound traffic to an Azure SQL Database server to specific Azure virtual network subnets that you designate. Even though the server remains associated with a public IP address, traffic from private IP addresses of Azure virtual machines on the designated subnets is routed via the Microsoft backbone network, rather than via public Internet (for more information, refer to Azure SQL Database Virtual Network Service Endpoints and Rules).

The subsequent levels of defenses are facilitated by features in other, security-related areas, such as authentication and authorization, encryption in transit and at rest, as well as data integrity and confidentiality. Azure SQL Database supports two modes of authentication. The traditional one relies on server-level logins with the corresponding user accounts in individual user databases. The recommended approach takes advantage of the newer authentication method, which integrates with Azure Active Directory (Azure AD). With Azure AD authentication, each server instance includes an Azure AD-based administrative account, which corresponds to either an Azure AD user or an Azure AD group. You can use this administrative account to create database-level users. This new approach to authentication provides a number of benefits. The ability to use the same user name and password to obtain access to multiple Azure SQL Database server and database instances eliminates the credential sprawl, automatically improving security and lowering the management overhead associated with user account management. By virtue of integration with Azure AD, you also can implement centralized management of these credentials, either in the cloud (for Azure AD managed accounts) or on-premises (for synchronized and federated accounts originating from on-premises Active Directory). For more information, regarding this topic, refer to Azure SQL Database - Azure AD Authentication

From the authorization standpoint, Azure SQL Database implements granular object model and role-based security, which facilitate implementing the principle of least privilege. This not only helps limit the risk of unintended data exposure but also minimizes the impact of a potential exploit. Data protection is further enhanced by leveraging such features as:

  • Transport Layer Security encrypts all network connections to Azure SQL Database. This is a default, enforced functionality, which provides automatic protection of data in transit.
  • Dynamic Data Masking limits the visibility of sensitive data stored in relational database tables accessible to non-privileged users. This functionality, supported starting with SQL Server 2016 and present in the current version of Azure SQL Database relies on a custom policy defined on the database level. The policy specifies one or more filters that should be applied to result sets returned in response to user or application initiated queries. This facilitates scenarios where database-resident content should not be fully exposed to database users. This is commonly required when referencing data containing Personally Identifiable Information (PII), such as credit card numbers, or email addresses (for more information, refer to Azure SQL Database - Dynamic Data Masking).
  • Row-Level Security implements a predicate-based access control. This means that its underlying mechanism relies on an arbitrary condition to evaluate whether requests to perform specific actions on individual rows of a database table should be granted or denied. These actions are determined based on the type of predicate (for more information, refer to Azure SQL Database - Row-Level Security).
  • Transparent Data Encryption encrypts content of the database, its transaction logs, as well as backups while at rest. Encryption and decryption are performed in real-time, at the individual page level, as the database content is being written to and read from storage, without necessitating changes to applications accessing their data. In the case of Azure SQL Database, its performance impact is minimized by relying on the Intel AES-NI hardware acceleration, included as an inherent part of the service (for more information, refer to Azure SQL Database - Transparent Data Encryption).
  • Always Encrypted offers end-to-end encryption of customer data by handing control over the encryption process to that customer. The encryption is implemented by a client-side driver, with encryption keys never revealed to the management layer on the Azure SQL Database side. This delivers the highest assurance of data confidentiality, without the need for changes to application code.
  • Azure SQL Database auditing tracks database-level and server-level security-related events, allowing you to identify instances of unauthorized changes and access attempts, addressing at the same time regulatory compliance requirements. Logging can be selectively turned on or off based on the event types, which include Data Access, Schema Changes, Data Changes, Security Failures, and Grant/Revoke Permissions (for more information, refer to Azure SQL Database Auditing). In order to track all data changes, you can take advantage of Temporal Tables. Their primary purpose is to provide the full visibility of historical changes of content stored in Azure SQL Database tables. Effectively, you have the ability to identify the state of your data at an arbitrary point in time. This comes in handy in a variety of scenarios, including performing analysis of usage trends of database-bound applications, identifying data lineage, or carrying compliance and regulatory audits. In addition, you have a rather unique ability of granular recovery of individual table entries, without resorting to traditional restore methods. All of these benefits can be realized without the need for programming or database design changes. Existing applications interact with their data sources in the same manner as they would without having temporal tables in place (for more information, refer to Introduction to Azure SQL Database Temporal Tables).
  • Azure SQL Database Threat Detection automatically detects and alerts on suspicious database activities, which might be a sign of a cyber attack or a security breach. This functionality relies on machine learning and behavioral analytics technologies. It integrates with Azure Security Center, which additionally provides guidance regarding threat remediation.
  • Point-In Time Restore relies on automatic backups to facilitate database restore to an arbitrary point in time (for more information, refer to Azure SQL Database Resiliency - Point-in-Time Restore).
  • Active Geo-Replication provides disaster recovery capabilities. It supports up to four read-only secondaries located in arbitrarily chosen Azure regions, which allows you to fail over to any of the four locations. In addition, since each of the replicas operates in read-only mode, you not only can mitigate the impact of datacenter-wide disasters, but you also gain the ability to facilitate online application upgrades or migrations as well as offloading data analysis or reporting tasks from your production instance. (for more information, refer to Azure SQL Database Resiliency - Business Continuity).

    This concludes our overview of Azure SQL Database features that could help you with addressing GDRP requirements.

    See all articles by Marcin Policht



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM