Hi there. Welcome to Database Security. In this lecture, we're going to review the different database security options and services that are available. We'll look at Azure SQL Firewall Rules, Azure SQL Always Encrypted, Azure SQL Transparent Data Encryption, and Azure SQL Database Auditing.

Let’s talk a little bit about Azure SQL firewall rules. When you create a new Azure SQL server, the SQL Database firewall will automatically block all access to the public endpoint for the server. Connection attempts that originate from the internet will have to pass through the firewall before hitting the SQL server or database. The diagram on your screen shows the flow of traffic through the firewall.

As you can see in the diagram, you can define server-level IP firewall rules and database-level IP firewall rules.

Server-level IP firewall rules allow clients to access the entire Azure SQL server, which includes all databases hosted on it. The master database holds these rules. I should mention that you can configure a maximum of 128 server-level IP firewall rules for an Azure SQL Server.

Database-level IP firewall rules are used to allow access to specific databases on a SQL Database server. You create database-level IP firewall rules each database (including the master database), rather than for the entire server. These rules are stored in the database that they are created for.

As is the case for server-level IP firewall rules, you can define a maximum of 128 database-level IP firewall rules for a database. 

For more information about configuring SQL firewall rules, visit the URL that you see on your screen:

https://docs.microsoft.com/azure/sql-database/sql-database-firewall-configure

The Always Encrypted feature is designed to protect sensitive data like credit card numbers or social security numbers, that are stored in an Azure SQL Database or even in a traditional SQL Server database.

With Always Encrypted, clients can encrypt sensitive data inside the client applications without revealing the encryption keys to the SQL Server, ensuring separation between the owners of the data and those who manage it. This is important because those who manage this sensitive data shouldn’t be able to see the data. 

Always Encrypted ensures unauthorized users, including database admins, can't access sensitive data. This means customers can confidently store sensitive data, even when it’s outside of their direct control. 

To ensure encryption is transparent to applications, an Always Encrypted-enabled driver has to be installed on the client computer. This driver automatically encrypts and decrypts sensitive data in the client application before sending the data off to the SQL Server. In a similar fashion, the driver also transparently decrypts the data contained in query results.

I should mention that Always Encrypted is available in all editions of Azure SQL Database, starting with SQL Server 2016. It’s also available in all service tiers of SQL Database. 

To learn more about Always Encrypted, visit the URL that you see on your screen:

https://docs.microsoft.com/sql/relational-databases/security/encryption/always-encrypted-database-engine

Transparent data encryption, or TDE, is used to protect Azure SQL Databases, Azure SQL Managed Instances, and even Azure Synapse from malicious offline activities. It provides this protection by encrypting the data at rest. TDE performs real-time encryption and decryption of the database, associated backups, and the transaction log files at rest – and it does so without the need for any changes to the application. 

I should point out though, that while TDE, by default, is enabled for all newly deployed Azure SQL databases, it cannot be used to encrypt the logical master database. This is because the master database contains objects that TDE needs to perform its encryption operations on the user databases.

It’s also important to note that you’ll need to manually enable TDE for older databases of Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse. 

To learn how Transparent Data Encryption works under the hood, visit the URL that you see on your screen:

https://docs.microsoft.com/azure/sql-database/transparent-data-encryption-azure-sql

Auditing is a core part of security in any environment. Auditing for Azure SQL Database, along with Azure Synapse Analytics, will track database events and writes them to an audit log that you can host in an Azure storage account, a Log Analytics workspace, or in Event Hubs. 

Auditing your SQL databases helps organizations to not only maintain regulatory compliance, but to also understand what’s going on within the database. It also helps track down information on anomalies that may or may not be related to suspected security violations or business concerns. By analyzing audit reports on a regular basis, it becomes easier to identify unusual activity and suspicious events.

It’s important to understand that, while audit policies can be defined for a specific database, they can also be defined in a default server policy that applies to all new and existing databases on the server.

If you enable server blob auditing, the auditing will always apply to the databases on the server, meaning the database will always be audited, regardless of the database auditing settings. In situations where you enable both blob auditing on the database AND on the server, both audits will exist separately. You will essentially be auditing the databases twice at the same time - once by the server policy and once by the database policy.

That said, Microsoft recommends that you avoid simultaneously enabling both server blob auditing and database blob auditing unless you plan to use a different storage account for each, or if you plan to define a different retention period for a specific database. The only other time you might want to enable both is if you need to audit certain event types for a specific database that differ from the other databases hosted on the server. 

Your best bet, in most cases, is to enable server-level blob auditing only. Leave database-level auditing disabled for all databases, unless there is a specific need for it.