Database Security (part 4 of 4)

By Ashwin Venugopal -

 


To read part 1 please click here
To read part 2 please click here
To read part 3 please click here



Dynamic Data Masking

SQL Database Dynamic Data Masking (DDM) can limit the sensitive data exposure by masking it to non-privileged users which also helps in preventing an unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It's a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.

You set up a DDM policy in the Azure portal by selecting the DDM operation in your SQL Database configuration blade or settings blade. This feature cannot be set by using portal for Azure Synapse.

Dynamic data masking policy

  • SQL users excluded from masking- A set of SQL users or AAD identities that get unmasked data in the SQL query results. Users with the administrators privileges are always excluded from masking, and view the original data without any mask.

  • Masking rules- A set of rules that define the designated fields to be masked and the masking function that is used. The designated fields can be defined using a database schema name, table name, and column name.

  • Masking functions- A set of methods that controls the exposure of data for different scenarios. 

In the DDM blade in the portal, you can review the recommended columns for your database. All you need to do is click on Add Mask for one or more columns and then Save to apply a mask for these fields.

Transparent Data Encryption

Transparent Data Encryption (TDE) helps you to protect Azure SQL Database, Azure SQL Managed Instance, and Synapse SQL in Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest. By default, TDE is enabled for all newly deployed Azure SQL databases and needs to be manually enabled for older databases of Azure SQL Databases, Azure SQL Managed Instance, or Azure Synapse.

TDE performs real time I/O encryption and decryption of the data at the page level and on the database startup, the encrypted Database Encryption Key (DEK) is decrypted and then used for decrytion and re-encryption of the database files in the SQL Server Database Engine process. 

For Azure SQL Database and Azure Synapse, the TDE protector is set at the logical SQL server level and is inherited by all databases associated with that server. The term server always refers both to server and instance, unless stated differently. 

Service-managed transparent data encryption

In Azure, the default setting for TDE is that the DEK is protected by a built-in server certificate. If the two databases are connected to the same server, they also share the same built-in certificate and the Microsoft can automatically rotate these certificates in compliance with the internal security policy and the root key is protected by a Microsoft internal secret store. It can also seamlessly move and manage the keys as needed for geo-replication and restores. 

Customer-managed transparent data encryption- bring your own key

In this scenario, the TDE Protector that encrypts the DEK is a customer-managed asymmetric key, which is stored in a customer-owned as well as managed Azure Key Vault and never leaves the key vault. TDE and Azure Key Vault integration, allows users to control key management tasks including key rotations, key vault permissions, key backups, and enable auditing/reporting on all TDE protectors using Azure Key Vault functionality. 

Always Encrypted

SQL Database Always Encrypted

Always encrypted is a feature designed to secure sensitive data, like credit numbers, or national identification numbers, stored in the Azure SQL database or SQL server databases which allows the clients to encrypt sensitive data inside client applications and never reveal encryption keys to the Database Engine. 

It also makes the encryption transparent to applications and the driver encrypts the data in sensitive columns before passing the data to the Database Engine and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.

Always Encrypted Features

Always Encrypted supports two types of encryption:

  • Deterministic encryption- It always generates the same encrypted value for any given plain text value and allows point lookups, equality joins, grouping, as well as indexing on encrypted columns. Deterministic encryption must use a column collation using binary2 sort order for character columns.

  • Randomized encryption- It uses a method that encrypts the data in a less predictable manner. It is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.       

You can use deterministic encryption for the columns that will be used as search and grouping parameters such as government ID number, while randomized encryption for the data such as confidential investigation comments, which are not grouped with other records and are not used to join tables. 

Always Encrypted Implementation

Configuring Always Encrypted

The initial setup of Always Encrypted in a database generally involves generating Always Encrypted Keys, creating key metadata, configuring encryption properties of selected database columns, and/or encrypting data that may already exist in the columns that need to be encrypted. It is to be noted that some of these tasks are not supported in Transact-SQL and require the use of client-side tools. You can use SQL Server Management Studio (SSMS) or PowerShell to accomplish such tasks.   

Whenever you set up an encryption for a column, you can specify the information about the encryption algorithm and cryptographic keys used to protect the data in the column. A column encryption key is used to encrypt data in an encrypted column while a column master key is a key-protecting key that encrypts one or more column encryption keys. 

The Database Engine stores encryption configuration for each column in database metadata and if you want to access the data stored in an encrypted column in plaintext, an application must use an Always Encrypted enabled client driver. Next, the driver contacts the key store, containing the column master key, in order to decrypt the encrypted column encryption key value and then, it uses the plaintext column encryption key to encrypt the parameter.

The driver substitutes the plaintext values of the parameters targeting encrypted columns with their encrypted values, and it sends the query to the server for processing. The server computes the result set, and for any encrypted columns included in the result set, the driver attaches the encryption metadata for the column, including the information about the encryption algorithm and the corresponding keys. Next, the driver decrypts the results and returns plaintext values to the application. 

A client driver interacts with a key store, containing a column master key, using a column master key store provider, which is a client-side software component that encapsulates a key store containing the column master key. Always Encrypted capabilities, including built-in column master key store providers vary by a driver library and its version.   


To read part 1 please click here
To read part 2 please click here
To read part 3 please click here




Comments

Post a Comment

Popular posts from this blog

Query, Visualize, & Monitor Data in Azure Sentinel

By Ashwin Venugopal -
Image
  Azure Sentinel Workbooks Several ready for use templates are provided by the Azure Sentinel that can be used to create your own workbook and then modify them as needed for Contoso. Most of the data connectors it uses to ingest data come with their own workbooks, but better insight can be obtained by simply looking into the data that is being ingested by using tables and visualizations, including bar and pie charts. You can also make your own workbook easily by using this data from the beginning instead of using the predefined templates. Workbook Page You can easily access the workbook page from the Azure Sentinel from the navigation pane which consists of the: Workbook header- You can add a new workbook and review the saved workbooks as well as templates that are available on the workbook page. Templates section- You can access existing workbook templates on the Templates tab. You can save some of the workbooks for quick access and they will appear on the My Workbooks tab.  From the
Read more

Planning for Implementing SAP Solutions on Azure (Part 2 of 5)

By Ashwin Venugopal -
Image
  To read part 1 please click  here To read part 3 please click  here To read part 4 please click  here To read part 5 please click  here Load Balancing The load balancer uses probe ports to determine as well as route the traffic to an active DBMS node, but of there's any failover then the most common SAP application architecture can automatically reconnect against the private virtual IP address without the need for the SAP application to reconfigure it, while the load balancer redirect the traffic against the private virtual IP address without any need for the SAP application to reconfigure. The loaf balancer also offers an option of DirectServerReturn which if configured, can help in directing the traffic from the DBMS VM to the SAP application layer directly to the application layer without routing through the load balancer. But, if it isn't configured, then the return traffic to the SAP application layer is routed through the load balancer. You can also use Azure Load Balan
Read more

Work with String Data Using KQL Statements

By Ashwin Venugopal -
Image
  Extract Data from Unstructured String Fields Unstructured string fields often contains the Security log data and requires parsing to extract data. There are multiple ways of pulling information from string fields in KQL and the two primary operators used are extract and parse.  Extract It gets a match for a regular expression from a text string. You can convert the extracted substring to the indicated type.  Arguments regex- A regular expression. captureGroup- A positive int constant indicating the capture group to extract. 0 stands for the entire match, 1 for the value matched by the first '('parenthesis')' in the regular expression, 2 or more for subsequent parenthesis.  text- A string to search. typeLiteral- An optional type literal. If provided, the extracted substring is converted to this type.  Returns If regex finds a match in text- the substring matched against the indicated capture group captureGroup, optionally converted to typeLiteral. If there's no mat
Read more