This blog will go through the concept related to implement a secure environment that how we can provide the robust environment to the Azure SQL Databases , with advanced threat protections, permission chains, different firewall settings, and multi-factor authentication using the azure active directory that we have covered in the Azure Database Administrator Day 3 Live Session which will help you gain a better understanding and make it easier for you to learn the Azure Database Administrator Training Program clear the [DP-300] Certification & get a better-paid job.
On our Day 3 Live Session of the [DP-300] Microsoft Database Administrator Training Program, we covered the concepts of Implement A Secure Environment and learned and Learn how to Implement A Secure Environment.
Implement A Secure Environment
Azure SQL Database has several authentication and authorization options that are different from the options in SQL Server. This is because Azure SQL Database and Azure SQL Managed Instance rely on Azure Active Directory instead of Windows Server Active Directory. In Azure, while implementing a secure environment we explore encryptions, firewalls, advanced threat protection, and how to achieve the policy of least privileges.
Check out our related blog here: Implement A Secure Environment For A Database Service | Authentication | Authorization
Q.1 Name the SQL Server Authentication options.
- Windows Authentication: User login information is stored in Active Directory.
- SQL Server Authentication: User login information is stored in the master or user database.
Q.2 Name the Azure SQL Database and Managed Instance Authentication Options.
- Azure Active Directory Authentication: User login information is stored in Active Directory.
- SQL Server Authentication: User login information is stored in the master or user database.
Q.3 What is Azure Active Directory Authentication?
- Azure Active Directory can be synced to on-premises Active Directory. Azure Active Directory authentication can allow Windows Style Authentication into cloud-hosted databases. Multi-factor authentication is fully supported.
- The Active Directory Admin will have full rights to the SQL Database server, similar to being a member of the sysadmin fixed server role in an on-premises install of SQL Server.
- In order to add Users with Azure Active Directory Authentication, you must first configure the AAD admin.
Q.4 What are the Security Principals?
Security Principals are entities that can request SQL Server resources and to which you can (usually) grant permissions. There are several sets of security principles in SQL Server. Security principals exist at either the server level or the database level and can be either individuals or collections.
Q.5 How many types of permissions are available on tables or views?
- SELECT statement is used to fetch rows from a database table.
- INSERT statement is used to add new rows to a table.
- UPDATE statement is used to edit and update the values of an existing record.
- DELETE statement is used to delete records from a database table.
Q.6 What are permission chains?
Permission chains are used when access to an object is needed to complete a task against another object. This allows a user to execute a stored procedure without the user needing the right to the tables which the stored procedure uses.
Q.7 What is the concept of least privileges?
The concept of “Least Privilege” means that users never have more permissions than they need to get the task done. If the user runs an application and the user only needs to use stored procedures, then the user should have the EXECUTE permission for the stored procedure, and they should have no permissions on the underlying tables.
Q.8 What is Encryption at Rest?
Encryption at rest protects data files, transaction log files, and backup files by requiring a certificate to bring them online. SQL Server and Azure SQL Database implement this through a feature called Transparent Data Encryption. Encryption at rest can also be provided by the underlying storage layer, which is how Azure Database for MySQL and Azure SQL Database for Postgres support it.
Q.9 How Transparent Data Encryption Works?
Transparent Data Encryption (TDE) works by encrypting data in SQL Server and Azure SQL Database on a page level as the page is written to the disk. As data is read from the disk to the buffer pool, it is still encrypted as it sits in the buffer pool.
Q.10 Write the difference between Deterministic and Randomized encrypted encryption types.
Deterministic | Randomized |
Can perform equality operations | Most secure |
Can perform grouping operations | Does not allow grouping |
Allows indexing on encrypted columns | Does not allow equality operations on encrypted columns |
Should be used for data with the large domain of distinct values | Good for columns with the small domain of distinct values |
Q.11 What is Dynamic Data Masking used for?
Dynamic Data Masking provides you support for real-time obfuscation of data so that the data requesters do not get access to unauthorized data. This helps protect sensitive data even when it is not encrypted, and shows obfuscated data at the presentation layer without changing anything at the database level.
Q.12 What are Server-level firewalls?
Server-level firewalls are used to prevent all users from accessing the Azure SQL DB server name.
Q.13 What are Database-level firewalls?
Database level firewalls are used when a server firewall has been opened but only some of those users should have connectivity to a database.
Q.14 What is Azure Private link?
The Private link allows you to connect various Azure services (including Azure SQL DB) to a private endpoint. A private endpoint is a private IP address within a specific virtual network subnet this allows for you to use network rules to prevent data exfiltration.
Q.15 What is Advanced Threat Protection?
Advanced Threat Protection monitors the connections and queries which are being executed against your Azure SQL DB Database and Managed Instance.
Advanced Threat Protections watches for:
- Suspicious database activities
- Potential database vulnerabilities
- SQL Injection Attacks
- Anomalous database access and query patterns
Check out our related blog here: Implements Data Security And Data Classification
Q.16 How To Check For Vulnerabilities in Azure SQL Database?
The Issues that Advanced Threat Protection identifies can be found in the “Advanced Data Security” Click on the Vulnerability Assessment in order to see the current vulnerability assessment for the database If there is no current assessment, click “Scan” on the Vulnerability Assessment page to scan the server.
>Lab: Implement A Secure Environment
In this lab, we will learn to configure and subsequently implement security in the Azure Portal and within the AdventureWorks database. We will configure an Azure SQL Database Firewall, Authorize Access to Azure SQL Database with Azure Active Directory and Manage access to database objects.
Feedback Received…
Here is some positive feedback from our trainees who attended the session:
Read more about the DP-300 Certification and whether it is the right certification for you, from our blog on Exam DP-300: Microsoft Azure Database Administrator Associate
Quiz Time (Sample Exam Questions)!
With our Azure Database Administrator Training Program, we cover 200+ sample exam questions to help you prepare for the DP-300 Certification.
Ques: Your company wants to create an Azure SQL Database. The database will contain a table that would have Employee information. There are columns that contain sensitive information. You have to enable Always Encryption for the data stored in the Employee table.
Which of the following could be used to store the encryption keys required for enabling Always Encrypted for the table?
Comment with your answer & we will tell you if you are correct or not!
Next Task For You
Begin your journey toward Mastering Azure Cloud and landing high-paying jobs. Just click on the register now button on the below image to register for a Free Class on Mastering Azure Cloud: How to Build In-Demand Skills and Land High-Paying Jobs. This class will help you understand better, so you can choose the right career path and get a higher paying job.
Leave a Reply