Secure a database in Azure SQL Database
In this, we will learn secure a database in Azure SQL database with creating server-level and database-level firewall rules. And, we will learn about configuring an Azure Active Directory (Azure AD) administrator as well as managing user access with SQL authentication, Azure AD authentication, and secure connection strings.
Create firewall rules
- Firstly, databases in SQL Database are protected by firewalls in Azure. By default, all connections to the server and database are rejected.
- Secondly, set Allow access to Azure services to OFF for the most secure configuration. Then, create a reserved IP (classic deployment) for the resource that needs to connect, such as an Azure VM or cloud service, and only allow that IP address access through the firewall.
Set up server-level firewall rules
Server-level IP firewall rules apply to all databases within the same server.
To set up a server-level firewall rule:
- Firstly, in the Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
- Then, on the Overview page, select Set server firewall. The Firewall settings page for the server opens.
- Select Add client IP on the toolbar to add your current IP address to a new firewall rule. The rule can open port 1433 for a single IP address or a range of IP addresses. Select Save.
- Select OK and close the Firewall settings page.
Setup database firewall rules
Database-level firewall rules only apply to individual databases. The database will retain these rules during a server failover. Database-level firewall rules can only be configured using Transact-SQL (T-SQL) statements, and only after you’ve configured a server-level firewall rule.
To set up a database-level firewall rule:
- Firstly, Connect to the database, for example using SQL Server Management Studio.
- Secondly, In Object Explorer, right-click the database and select New Query.
- Then, In the query window, add this statement and modify the IP address to your public IP address:
SQL
EXECUTE sp_set_database_firewall_rule N’Example DB Rule’,’0.0.0.4′,’0.0.0.4′;
- Lastly, on the toolbar, select Execute to create the firewall rule.
Create an Azure AD admin
Make sure you’re using the appropriate Azure Active Directory (AD) managed domain. To select the AD domain, use the upper-right corner of the Azure portal. This process confirms the same subscription is used for both Azure AD and the logical SQL server hosting your database or data warehouse.
To set the Azure AD administrator:
- Firstly, in the Azure portal, on the SQL server page, select Active Directory admin. Next select Set admin.
- Secondly, on the Add admin page, search and select the AD user or group and choose Select. All members and groups of your Active Directory are listed, and entries grayed out are not supported as Azure AD administrators. See Azure AD features and limitations.
- Then, at the top of the Active Directory admin page, select Save.
Manage database access
Manage database access by adding users to the database, or allowing user access with secure connection strings. Connection strings are useful for external applications.
To add users, choose the database authentication type:
SQL authentication
To add a user with SQL authentication:
- Firstly, connect to the database, for example using SQL Server Management Studio.
- Secondly, in Object Explorer, right-click the database and choose New Query.
- Thirdly, in the query window, enter the following command:
SQL
CREATE USER ApplicationUser WITH PASSWORD = ‘YourStrongPassword1’;
- Next, on the toolbar, select Execute to create the user.
- However, by default, the user can connect to the database, but has no permissions to read or write data. To grant these permissions, execute the following commands in a new query window:
SQL
ALTER ROLE db_datareader ADD MEMBER ApplicationUser;
ALTER ROLE db_datawriter ADD MEMBER ApplicationUser;
Azure AD authentication
Azure Active Directory authentication requires that database users are created as contained. A contained database user maps to an identity in the Azure AD directory associated with the database and has no login in the master database. The Azure AD identity can either be for an individual user or a group.
To add a user with Azure AD authentication:
- Connect to your server in Azure using an Azure AD account with at least the ALTER ANY USER permission.
- In Object Explorer, right-click the database and select New Query.
- In the query window, enter the following command and modify to the principal name of the Azure AD user or the display name of the Azure AD group:
SQL
CREATE USER FROM EXTERNAL PROVIDER;
Azure Defender for SQL
The Azure Defender for SQL feature detects potential threats as they occur and provides security alerts on anomalous activities. Users can explore these suspicious events using the auditing feature, and determine if the event was to access, breach, or exploit data in the database. Users are also provided a security overview that includes a vulnerability assessment and the data discovery and classification tool.
To enable Azure Defender for SQL:
- Firstly, in the Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
- Secondly, on the Overview page, select the Server name link. The server page will open.
- Then, on the SQL server page, find the Security section and select Security center.
- Select ON under Azure Defender for SQL to enable the feature. Choose a storage account for saving vulnerability assessment results. Then select Save.
- Lastly, return to the SQL databases page of your database and select Security center under the Security section. Here you’ll find various security indicators available for the database.
Auditing
The auditing feature tracks database events and writes events to an audit log in either Azure storage, Azure Monitor logs, or to an event hub. Auditing helps maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate potential security violations.
To enable auditing:
- Firstly, in the Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
- Secondly, in the Security section, select Auditing.
- Then, under Auditing settings, set the following values:
- Set Auditing to ON.
- Select Audit log destination as any of the following:
- Storage, an Azure storage account where event logs are saved and can be downloaded as .xel files
- Log Analytics, which automatically stores events for query or further analysis
- Event Hub, which allows events to be routed for use in other applications
- Select Save.
- Lastly, now you can select View audit logs to view database events data.
Dynamic data masking
The data masking feature will automatically hide sensitive data in your database.
To enable data masking:
- Firstly, in the Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
- secondly, in the Security section, select Dynamic Data Masking.
- Then, under Dynamic data masking settings, select Add mask to add a masking rule. Azure will automatically populate available database schemas, tables, and columns to choose from.
- Lastly, select Save. The selected information is now masked for privacy.
Reference: Microsoft Documentation