Section 14 – Secure data and applications – Configure security for databases

In this section I will cover the following:

• Enable database authentication by using Azure AD
• Enable database auditing
• Configure dynamic masking on SQL workloads
• Implement database encryption for Azure SQL Database
• Implement network isolation for data solutions, including Azure Synapse Analytics and Azure Cosmos DB

Different databases in the cloud

Inside Azure you these options https://azure.microsoft.com/en-us/product-categories/databases/

And I will be concentrating in the marked ones as options.

What you get with SQL Server on Azure virtual machines:

So if you just want to lift you DB’s to the cloud as-is then it will be SQL Server on Azure Virtual Machines, then you have to maintain the server like before, you have to get more capacity to the virtual server underneath, so not the best option, but sometimes it has to be used example for compatibility reasons.

But you will get automated backup, Disaster recovery and many other features when you install SQL Server IaaS Agent extension https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management?tabs=azure-powershell#feature-benefits

What you get with Azure SQL Managed Instance:

You will get Azure Active Directory authentication inside the DB, how cool is that. Not anymore the manually created users inside the database. So you can allow users created in your Azure to access to the database and maintain only one identity with the security and identity protection features inside Azure Identity.

You will also get all the security benefits.

Isolated environment (VNet integration, single tenant service, dedicated compute and storage)
Transparent data encryption – TDE performs real-time I/O encryption and decryption of the data at the page level.
Azure AD server principals (logins) – https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#provision-azure-ad-admin-sql-managed-instance
SQL auditing – https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure
Advanced Threat Protection – https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure

Then you will get almost the same compatibility as with old-school SQL instance, but you don’t have to update anything. All the software and hardware under you DB engine has been taken care-off.

Overview of the service https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview

What you get with Azure SQL Database:

All the the features mentioned above, but also Elastic Pools. So the deployment options are:

• As a single database with its own set of resources managed via a logical SQL server. A single database is similar to a contained database in SQL Server. This option is optimized for modern application development of new cloud-born applications. Hyperscale and serverless options are available.
• An elastic pool, which is a collection of databases with a shared set of resources managed via a logical SQL server. Single databases can be moved into and out of an elastic pool. This option is optimized for modern application development of new cloud-born applications using the multi-tenant SaaS application pattern. Elastic pools provide a cost-effective solution for managing the performance of multiple databases that have variable usage patterns.

Elastic databases was release in 2016 and it was an really nice addition for Azure SQL services. Serverless db cluster that is expanding as your usage goes up.

With Elastic Pool you don’t have to over-allocate the service, it will find the resources inside the pool and distribute them to the DB that doesn’t have too much use.

Enable database authentication by using Azure AD

Trust architecture

• Only the cloud portion of Azure AD, SQL Database, SQL Managed Instance, and Azure Synapse is considered to support Azure AD native user passwords.
• To support Windows single sign-on credentials (or user/password for Windows credential), use Azure Active Directory credentials from a federated or managed domain that is configured for seamless single sign-on for pass-through and password hash authentication. For more information, see Azure Active Directory Seamless Single Sign-On.
• To support Federated authentication (or user/password for Windows credentials), the communication with ADFS block is required.

Azure AD features and limitations

• The following members of Azure AD can be provisioned for Azure SQL Database:
  • Native members: A member created in Azure AD in the managed domain or in a customer domain. For more information, see Add your own domain name to Azure AD.
  • Members of an Active Directory domain federated with Azure Active Directory on a managed domain configured for seamless single sign-on with pass-through or password hash authentication. For more information, see Microsoft Azure now supports federation with Windows Server Active Directory and Azure Active Directory Seamless Single Sign-On.
  • Imported members from other Azure AD’s who are native or federated domain members.
  • Active Directory groups created as security groups.
• Azure AD users that are part of a group that has db_owner server role cannot use the CREATE DATABASE SCOPED CREDENTIAL syntax against Azure SQL Database and Azure Synapse. You will see the following error:SQL Error [2760] [S0001]: The specified schema name 'user@mydomain.com' either does not exist or you do not have permission to use it.Grant the db_owner role directly to the individual Azure AD user to mitigate the CREATE DATABASE SCOPED CREDENTIAL issue.
• These system functions return NULL values when executed under Azure AD principals:
  • SUSER_ID()
  • SUSER_NAME(<admin ID>)
  • SUSER_SNAME(<admin SID>)
  • SUSER_ID(<admin name>)
  • SUSER_SID(<admin name>)

Connect by using Azure AD identities

Azure Active Directory authentication supports the following methods of connecting to a database using Azure AD identities:

• Azure Active Directory Password
• Azure Active Directory Integrated
• Azure Active Directory Universal with Multi-Factor Authentication
• Using Application token authentication

The following authentication methods are supported for Azure AD server principals (logins):

• Azure Active Directory Password
• Azure Active Directory Integrated
• Azure Active Directory Universal with Multi-Factor Authentication

SQL Managed Instance

• Azure AD server principals (logins) and users are supported for SQL Managed Instance.
• Setting Azure AD server principals (logins) mapped to an Azure AD group as database owner is not supported in SQL Managed Instance.
  • An extension of this is that when a group is added as part of the dbcreator server role, users from this group can connect to the SQL Managed Instance and create new databases, but will not be able to access the database. This is because the new database owner is SA, and not the Azure AD user. This issue does not manifest if the individual user is added to the dbcreator server role.
• SQL Agent management and jobs execution are supported for Azure AD server principals (logins).
• Database backup and restore operations can be executed by Azure AD server principals (logins).
• Auditing of all statements related to Azure AD server principals (logins) and authentication events is supported.
• Dedicated administrator connection for Azure AD server principals (logins) which are members of sysadmin server role is supported.
  • Supported through SQLCMD Utility and SQL Server Management Studio.
• Logon triggers are supported for logon events coming from Azure AD server principals (logins).
• Service Broker and DB mail can be setup using an Azure AD server principal (login).

How to enable?

Add Azure AD admin

Open your SQL servers and Azure Active Directory page, from there you will choose Set admin

Choose your admin and hit save

You can also disable SQL authentication and enable Only Azure AD authentication.

Azure AD-only authentication with Azure SQL

Azure AD-only authentication is a feature within Azure SQL that allows the service to only support Azure AD authentication, and is supported for Azure SQL Database and Azure SQL Managed Instance.

Azure AD-only authentication is also available for dedicated SQL pools (formerly SQL DW) in standalone servers. Azure AD-only authentication can be enabled for the Azure Synapse workspace. For more information, see Azure AD-only authentication with Azure Synapse workspaces.

SQL authentication is disabled when enabling Azure AD-only authentication in the Azure SQL environment, including connections from SQL server administrators, logins, and users. Only users using Azure AD authentication are authorized to connect to the server or database.

Azure AD-only authentication can be enabled or disabled using the Azure portal, Azure CLI, PowerShell, or REST API. Azure AD-only authentication can also be configured during server creation with an Azure Resource Manager (ARM) template.

Permissions

Azure AD-only authentication can be enabled or disabled by Azure AD users who are members of high privileged Azure AD built-in roles, such as Azure subscription Owners, Contributors, and Global Administrators. Additionally, the role SQL Security Manager can also enable or disable the Azure AD-only authentication feature.

The SQL Server Contributor and SQL Managed Instance Contributor roles won’t have permissions to enable or disable the Azure AD-only authentication feature. This is consistent with the Separation of Duties approach, where users who can create an Azure SQL server or create an Azure AD admin, can’t enable or disable security features.

Enable database auditing

Auditing for Azure SQL Database and Azure Synapse Analytics tracks database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.

Auditing limitations

• Premium storage is currently not supported.
• Hierarchical namespace for Azure Data Lake Storage Gen2 storage account is currently not supported.
• Enabling auditing on a paused Azure Synapse is not supported. To enable auditing, resume Azure Synapse.
• Auditing for Azure Synapse SQL pools supports default audit action groups only.
• When you configure the auditing in Azure SQL Server or Azure SQL Database with log destination as the storage account, the target storage account must be enabled with access to storage account keys. If the storage account is configured to use Azure AD authentication only and not configured for access key usage, the auditing cannot be configured.

When you enable the auditing with storage accounts, you will see the existing Storage account or you can create a new one.

After you enable Managed identity you will get a warning but it’s a lot safer than using Storage Access keys.

Auditing of Microsoft Support operations

Auditing of Microsoft Support operations for Azure SQL Server allows you to audit Microsoft support engineers’ operations when they need to access your server during a support request. The use of this capability, along with your auditing, enables more transparency into your workforce and allows for anomaly detection, trend visualization, and data loss prevention.

You can use the same location for Microsoft support auditing or define a other location

When Azure SQL Auditing of Microsoft support operations is configured to a Log Analytics Workspace or an Event Hub destination, the audit logs will be audited under a new category called “DevOpsOperationsAudit“.

Configure dynamic masking on SQL workloads

Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking automatically discovers potentially sensitive data in Azure SQL Database and SQL Managed Instance and provides actionable recommendations to mask these fields, with minimal impact to the application layer. It works by obfuscating the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.

Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics support dynamic data masking. Dynamic data masking limits sensitive data exposure by masking it to non-privileged users.

Dynamic data masking helps prevent 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.

Masking function	Masking logic
Default	Full masking according to the data types of the designated fields • Use XXXX or fewer Xs if the size of the field is less than 4 characters for string data types (nchar, ntext, nvarchar). • Use a zero value for numeric data types (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real). • Use 01-01-1900 for date/time data types (date, datetime2, datetime, datetimeoffset, smalldatetime, time). • For SQL variant, the default value of the current type is used. • For XML the document <masked/> is used. • Use an empty value for special data types (timestamp table, hierarchyid, GUID, binary, image, varbinary spatial types).
Credit card	Masking method, which exposes the last four digits of the designated fields and adds a constant string as a prefix in the form of a credit card. XXXX-XXXX-XXXX-1234
Email	Masking method, which exposes the first letter and replaces the domain with XXX.com using a constant string prefix in the form of an email address. aXX@XXXX.com
Random number	Masking method, which generates a random number according to the selected boundaries and actual data types. If the designated boundaries are equal, then the masking function is a constant number.
Custom text	Masking method, which exposes the first and last characters and adds a custom padding string in the middle. If the original string is shorter than the exposed prefix and suffix, only the padding string is used.

Permissions

These are the built-in roles to configure dynamic data masking is:

• SQL Security Manager
• SQL DB Contributor
• SQL Server Contributor

These are the required actions to use dynamic data masking:

Read/Write:

• Microsoft.Sql/servers/databases/dataMaskingPolicies/* Read:
• Microsoft.Sql/servers/databases/dataMaskingPolicies/read Write:
• Microsoft.Sql/servers/databases/dataMaskingPolicies/write

Set up dynamic data masking for your database using PowerShell cmdlets

Data masking policies

• Get-AzSqlDatabaseDataMaskingPolicy
• Set-AzSqlDatabaseDataMaskingPolicy

Data masking rules

• Get-AzSqlDatabaseDataMaskingRule
• New-AzSqlDatabaseDataMaskingRule
• Remove-AzSqlDatabaseDataMaskingRule
• Set-AzSqlDatabaseDataMaskingRule

Implement database encryption for Azure SQL Database

Always encrypted

In SQL Server 2019 (15.x), Always Encrypted with secure enclaves uses Virtualization-based Security (VBS) secure memory enclaves (also known as Virtual Secure Mode, or VSM enclaves) in Windows.

Azure SQL Database, Always Encrypted with secure enclaves uses Intel Software Guard Extensions (Intel SGX) enclaves. Intel SGX is a hardware-based trusted execution environment technology supported in databases that use the DC-series hardware configuration.

We use the terms column encryption key and column master key to refer to the actual cryptographic keys, and we use column encryption key metadata and column master key metadata to refer to the Always Encrypted key descriptions in the database.

• Column encryption keys are content-encryption keys used to encrypt data. As the name implies, you use column encryption keys to encrypt data in database columns. You can encrypt 1 or more columns with the same column encryption key, or you can use multiple column encryption keys depending on your application requirements. The column encryption keys are themselves encrypted, and only the encrypted values of the column encryption keys are stored in the database (as part of the column encryption key metadata). The column encryption key metadata is stored in the sys.column_encryption_keys (Transact-SQL) and sys.column_encryption_key_values (Transact-SQL) catalog views. Column encryption keys used with the AES-256 algorithm are 256-bit long.
• Column master keys are key-protecting keys used to encrypt column encryption keys. Column master keys must be stored in a trusted key store, such as Windows Certificate Store, Azure Key Vault, or a hardware security module. The database only contains metadata about column master keys (the type of key store and location). The column master key metadata is stored in the sys.column_master_keys (Transact-SQL) catalog view.

Data Encryption Algorithm

Always Encrypted uses the AEAD_AES_256_CBC_HMAC_SHA_256 algorithm to encrypt data in the database.

AEAD_AES_256_CBC_HMAC_SHA_256 is derived from the specification draft at https://tools.ietf.org/html/draft-mcgrew-aead-aes-cbc-hmac-sha2-05. It uses an Authenticated Encryption scheme with Associated Data, following an Encrypt-then-MAC approach. That is, the plaintext is first encrypted, and the MAC is produced based on the resulting ciphertext.

In order to conceal patterns, AEAD_AES_256_CBC_HMAC_SHA_256 uses the Cipher Block Chaining (CBC) mode of operation, where an initial value is fed into the system named the initialization vector (IV). The full description of the CBC mode can be found at https://csrc.nist.gov/publications/nistpubs/800-38a/sp800-38a.pdf.

Transport Layer Security (Encryption-in-transit)

SQL Database, SQL Managed Instance, and Azure Synapse Analytics secure customer data by encrypting data in motion with Transport Layer Security (TLS).

SQL Database, SQL Managed Instance, and Azure Synapse Analytics enforce encryption (SSL/TLS) at all times for all connections. This ensures all data is encrypted “in transit” between the client and server irrespective of the setting of Encrypt or TrustServerCertificate in the connection string.

Transparent Data Encryption (Encryption-at-rest)

Transparent data encryption (TDE) for SQL Database, SQL Managed Instance, and Azure Synapse Analytics adds a layer of security to help protect data at rest from unauthorized or offline access to raw files or backups. Common scenarios include data center theft or unsecured disposal of hardware or media such as disk drives and backup tapes. TDE encrypts the entire database using an AES encryption algorithm, which doesn’t require application developers to make any changes to existing applications.

In Azure, all newly created databases are encrypted by default and the database encryption key is protected by a built-in server certificate. Certificate maintenance and rotation are managed by the service and require no input from the user. Customers who prefer to take control of the encryption keys can manage the keys in Azure Key Vault.

Transparent Data Encryption

With TDE you will bring your own certificates to encrypt the data just like in Azure Information Protection.

You will have keys to access your data not Microsoft. So you have to keep them safe.

Benefits of TDE

Customer-managed TDE provides the following benefits to the customer:

• Full and granular control over usage and management of the TDE protector;
• Transparency of the TDE protector usage;
• Ability to implement separation of duties in the management of keys and data within the organization;
• Key Vault administrator can revoke key access permissions to make encrypted database inaccessible;
• Central management of keys in AKV;
• Greater trust from your end customers, since AKV is designed such that Microsoft cannot see nor extract encryption keys;

How customer-managed TDE works?

For server to be able to use TDE protector stored in AKV for encryption of the DEK, key vault administrator needs to give the following access rights to the server using its unique Azure Active Directory (Azure AD) identity:

• get – for retrieving the public part and properties of the key in the Key Vault
• wrapKey – to be able to protect (encrypt) DEK
• unwrapKey – to be able to unprotect (decrypt) DEK

Requirements for configuring Azure Key Vault

• Key vault and SQL Database/managed instance must belong to the same Azure Active Directory tenant. Cross-tenant key vault and server interactions are not supported. To move resources afterwards, TDE with AKV will have to be reconfigured. Learn more about moving resources.
• Soft-delete and Purge protection features must be enabled on the key vault to protect from data loss due to accidental key (or key vault) deletion.
  • Soft-deleted resources are retained for 90 days, unless recovered or purged by the customer. The recover and purge actions have their own permissions associated in a key vault access policy. The Soft-delete feature can be enabled using the Azure portal, PowerShell or Azure CLI.
  • Purge protection can be turned on using Azure CLI or PowerShell. When purge protection is enabled, a vault or an object in the deleted state cannot be purged until the retention period has passed. The default retention period is 90 days, but is configurable from 7 to 90 days through the Azure portal.
• Grant the server or managed instance access to the key vault (get, wrapKey, unwrapKey) using its Azure Active Directory identity. When using the Azure portal, the Azure AD identity gets automatically created when the server is created. When using PowerShell or Azure CLI, the Azure AD identity must be explicitly created and should be verified. See Configure TDE with BYOK and Configure TDE with BYOK for SQL Managed Instance for detailed step-by-step instructions when using PowerShell.
  • Depending on the permission model of the key vault (access policy or Azure RBAC), key vault access can be granted either by creating an access policy on the key vault, or by creating a new Azure RBAC role assignment with the role Key Vault Crypto Service Encryption User.
• When using firewall with AKV, you must enable option Allow trusted Microsoft services to bypass the firewall.

Implement network isolation for data solutions, including Azure Synapse Analytics and Azure Cosmos DB

Azure Synapse Link

Benefits

To analyze large operational datasets while minimizing the impact on the performance of mission-critical transactional workloads, traditionally, the operational data in Azure Cosmos DB is extracted and processed by Extract-Transform-Load (ETL) pipelines. ETL pipelines require many layers of data movement resulting in much operational complexity, and performance impact on your transactional workloads. It also increases the latency to analyze the operational data from the time of origin.

Security

Synapse Link enables you to run near real-time analytics over your mission-critical data in Azure Cosmos DB. It is vital to make sure that critical business data is stored securely across both transactional and analytical stores.

• Network isolation using private endpoints – You can control network access to the data in the transactional and analytical stores independently. Network isolation is done using separate managed private endpoints for each store, within managed virtual networks in Azure Synapse workspace
• Data encryption with customer-managed keys – You can seamlessly encrypt the data across transactional and analytical stores using the same customer-managed keys in an automatic and transparent manner. Azure Synapse Link only supports configuring customer-managed keys using your Azure Cosmos DB account’s managed identity. You must configure your account’s managed identity in your Azure Key Vault access policy before enabling Azure Synapse Link on your account.
• Secure key management – Accessing the data in analytical store from Synapse Spark and Synapse serverless SQL pools requires managing Azure Cosmos DB keys within Synapse Analytics workspaces. Instead of using the Azure Cosmos DB account keys inline in Spark jobs or SQL scripts, Azure Synapse Link provides more secure capabilities:
  • When using Synapse serverless SQL pools, you can query the Azure Cosmos DB analytical store by pre-creating SQL credentials storing the account keys and referencing these in the OPENROWSET function.
  • When using Synapse Spark, you can store the account keys in linked service objects pointing to an Azure Cosmos DB database and reference this in the Spark configuration at runtime.

Azure Cosmos keys

Put your secrets to a Key vault

Customer-managed keys

Encrypt your data with your own keys and with key vault

Managed identities

To set up managed identities, your account needs to have the DocumentDB Account Contributor role.

System managed

User assigned

Defender for cloud

Or you can enable Defender for cloud integration

And follow the recommendations it gives

And go ahead and enable firewall

Managed private endpoints

Managed private endpoints are private endpoints created in a Managed Virtual Network associated with your Azure Synapse workspace. Managed private endpoints establish a private link to Azure resources. Azure Synapse manages these private endpoints on your behalf.

A private endpoint connection is created in a “Pending” state when you create a Managed private endpoint in Azure Synapse. An approval workflow is started. The private link resource owner is responsible to approve or reject the connection. If the owner approves the connection, the private link is established. 

How to?

A managed private endpoint connection request is sent to the workspace’s primary Data Lake Storage Gen2 account for Spark pools to access data. This must be approved by an owner of the storage account.

All outbound traffic will go through private endpoints that connect to Azure resources in approved Azure AD tenants.

Authentication

Use RBAC

And managed identities

Workspace will have network access to your Data Lake Storage Gen2 account using the workspace’s system assigned identity.

And Customer managed keys

Azure Synapse Analytics (private link hubs)

Azure Synapse Analytics Private link hubs allow you to connect to Azure Synapse Studio using a private endpoint. Network traffic between clients in your Azure VNet and Synapse Studio traverses over the private link on the Microsoft backbone network, eliminating exposure from the public Internet.

How to?

And once registered, you are free to go

On the next page you will choose the Synapse workspace you created

And on the Virtual Network page you can create an Vnet and Application Security Group

On the DNS page you will create a private DNS entry

Things to remember

As data platforms are used more and more, it will be crucial to secure them. You don’t need the services to be exposed to the internet the is many choices you can make to scale up the security posture.

Understanding the fundamental differences with different SQL database types inside Azure

Azure AD features and limitation for SQL

Supported Azure AD identities

• Azure Active Directory Password
• Azure Active Directory Integrated
• Azure Active Directory Universal with Multi-Factor Authentication
• Using Application token authentication

And authentication methods

• Azure Active Directory Password
• Azure Active Directory Integrated
• Azure Active Directory Universal with Multi-Factor Authentication

What happens when you enable Azure AD as only identity provider for SQL.

What is dynamic masking and how it works?

How Always encrypted and TDE works in SQL, what is the difference?

Azure Cosmos DB Account security features and how to integrate them with Key vault.

How to use Managed identities and why?

Using Private links and endpoints inside Data platform and how Private link hubs work and why to use it?

Link to main post