In this article I will explain the options available on Azure we can use it for securing Azure SQL Database, as we know that we have azure deployment model options (Single Database, Elastic Pool, Managed instance, SQL on Azure VM) our article today focused on Azure Paas Database deployment option (Single Database, Elastic Pool, Managed Instance) for more information about Database Deployment option and more details Azure SQL Database deployment models check below articles and if you are interested in more Azure Articles in Database and other services check this Link, also IF you are preparing for Azure AZ-103 so this Post for you check it now.
- Azure Database Deployment model options
- Azure Single Database Fundamental
- Azure SQL Database Elastic Pool Fundamental
- Azure SQL Database Managed Instance Fundamental
Azure Single Database and Elastic Pool Securing Options
We can secure our database using anyone from these options (Network Security, Access Management, Threat Protection, information protection, and encryption)
- Network Security Azure SQL DB limited the access control on DB level or server level using Azure server firewall and Azure Database Firewall and this can be implemented from Azure portal or using T-SQL, And it is the first layer for accessing the DB on Azure, and the Firewall allows an IP address or range of IP addresses and this is the first configuration you should do it after provisioning new DB on Azure because it is by default not enabled. When the APP connected to Azure DB first validation is Firewall configuration that’s why it is the first layer for accessing the DB on Azure For more information Check this Post
- Create a server-level firewall rule: using this Server option we can allow any IP to get access on Azure SQL logical instance for More information Check this Post
- Configuring the Azure SQL Database Firewall: using this option we will allow any Client IP to get access only on the certain database not like the server level getting aces on all databases hosted on the logical server for More information Check this Post
- sp_set_database_firewall_rule and sp_delete_database_firewall_rule system stored procedures in the database to which these firewall rules apply. You can use Azure REST API or Windows PowerShell to implement the same functionality. To view database firewall rules in a specific database, you can query its sys.database_firewall_rules system view
Access management is one of the most important options that we can use it to secure our Azure SQL Database, Azure SQL Database supported
- SQL Authentication User name and password
- Supported Azure AD Authentication using identities in Azure Active directory
- Supported Row-level Security to able to control the access per row in tables
- There are 3 different ways to authenticate Active directory login
- Azure active directory password: User name and password created and managed by azure active directory
- Azure active directory integrated: User name managed by on-premises active directory and integrated with azure active directory
- Azure active directory universal with MFA: User name managed by Azure active directory and used Multi-factor authentication and this will code received by SMS or by calling
- Azure Server level administration roles: Database Creator, Login Managers
- You can integrate your on-premises Windows AD to Azure AD using Azure AD Connect.
- There is can be only one server admin account on azure SQL DB.
Threat Protection Can be done using (SQL Auditing in Azure monitoring logs and Event Hub or advanced threat Protection)
- SQL Auditing in Azure monitoring logs and Event Hub using this feature will be able to tracks the activities of the database to help you in maintenance compliance For more information Check this Post
- Advanced threat Protection using this feature you can analyze the Azure SQL Server database logs to detect the unusual behavior For more information Check this Post
Information Protection, and encryption
Connection in Azure is secured by TLS Transparent Layer security, TDE is Available on Azure SQL Database, Dynamic Data Masking Available on Azure SQL Database, Always Encrypted Available on Azure SQL Database and All of the Encryption Keys and stored in Azure Key Vault.
Azure SQL Managed instance Security
Azure Managed instance supporting all of the security features supported In Azure single database and Azur SQL Database elastic pool (TDE, Threat Protection, RLS Row-Level Security, Dynamic data masking, Managed instance auditing, Azure AD integration) Plus other features supported only for Azure managed instance (Managed instance Security advanced options)
- A managed instance using native Virtual network that is allowed the connection from on-premises to use this network to connect using Azure express route or VPN Gateway
- In Azure Managed instance by default, it is allowed the SQL Endpoint in only exposed through Private IP and this allowing safe connectivity
For More information about Azure SQL Database managed instance security check this Microsoft Document