Secure a single or pooled database in Azure Portal

We can learn in this topics:

• Create server-level and database-level firewall rules
• Configure an Azure Active Directory (AD) administrator
• Manage user access with SQL authentication, Azure AD authentication, and secure connection strings
• Enable security features, such as advanced data security, auditing, data masking, and encryption

Azure SQL Database secures data in a single or pooled database by allowing you to:

• Limit access using firewall rules
• Use authentication mechanisms that require identity
• Use authorization with role-based memberships and permissions
• Enable security features

 Note

An Azure SQL database on a managed instance is secured using network security rules and private endpoints as described in Azure SQL database managed instance and connectivity architecture.

 Tip

The following Microsoft Learn module helps you learn for free about how to Secure your Azure SQL Database.

Prerequisites

To complete the tutorial, make sure you have the following prerequisites:

• SQL Server Management Studio
• An Azure SQL server and database
  • Create them with Azure portal, CLI, or PowerShell

If you don't have an Azure subscription, create a free account before you begin.

Sign in to the Azure portal

For all steps in the tutorial, sign in to Azure portal

Create firewall rules

SQL databases are protected by firewalls in Azure. By default, all connections to the server and database are rejected, except for connections from other Azure services. T

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. If you're using the resource manager deployment model, a dedicated public IP address is required for each resource.

 Note

SQL Database communicates over port 1433. If you're trying to connect from within a corporate network, outbound traffic over port 1433 may not be allowed by your network's firewall. If so, you can't connect to the Azure SQL Database server unless your administrator opens port 1433.

Set up SQL Database server firewall rules

Server-level IP firewall rules apply to all databases within the same SQL Database server.

To set up a server-level firewall rule:

1. In Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
   
    Note

   Be sure to copy your fully qualified server name (such as yourserver.database.windows.net) for use later in the tutorial.
2. On the Overview page, select Set server firewall. The Firewall settings page for the database server opens.
   1. 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.
      
   2. Select OK and close the Firewall settings page.

You can now connect to any database in the server with the specified IP address or IP address range.

 Important

By default, access through the SQL Database firewall is enabled for all Azure services, under Allow access to Azure services. Choose OFF to disable access for all Azure services.

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 setup a database-level firewall rule:

1. Connect to the database, for example using SQL Server Management Studio.
2. In Object Explorer, right-click the database and select New Query.
3. 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';
   

4. On the toolbar, select Execute to create the firewall rule.

 Note

You can also create a server-level firewall rule in SSMS by using the sp_set_firewall_rule command, though you must be connected to the master database.

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 SQL Server hosting your Azure SQL database or data warehouse.

To set the Azure AD administrator:

1. In Azure portal, on the SQL server page, select Active Directory admin. Next select Set admin.
   
    Important

   You need to be either a "Company Administrator" or "Global Administrator" to perform this task.
2. 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.
   
    Important

   Role-based access control (RBAC) only applies to the portal and isn't propagated to SQL Server.
3. At the top of the Active Directory admin page, select Save.
   The process of changing an administrator may take several minutes. The new administrator will appear in the Active Directory admin box.

 Note

When setting an Azure AD admin, the new admin name (user or group) cannot exist as a SQL Server authentication user in the master database. If present, the setup will fail and roll back changes, indicating that such an admin name already exists. Since the SQL Server authentication user is not part of Azure AD, any effort to connect the user using Azure AD authentication fails.

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 learn more, see Azure SQL access control and AD authentication.

To add users, choose the database authentication type:

• SQL authentication, use a username and password for logins and are only valid in the context of a specific database within the server
• Azure AD authentication, use identities managed by Azure AD

SQL authentication

To add a user with SQL authentication:

1. Connect to the database, for example using SQL Server Management Studio.
2. In Object Explorer, right-click the database and choose New Query.
3. In the query window, enter the following command:
   SQL

   CREATE USER ApplicationUser WITH PASSWORD = 'YourStrongPassword1';
   

4. On the toolbar, select Execute to create the user.
5. 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;
   

 Note

Create non-administrator accounts at the database level, unless they need to execute administrator tasks like creating new users.

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. For more information, see Contained database users, make your database portable and review the Azure AD tutorial on how to authenticate using Azure AD.

 Note

Database users (excluding administrators) cannot be created using the Azure portal. Azure RBAC roles do not propagate to SQL servers, databases, or data warehouses. They are only used to manage Azure resources and do not apply to database permissions.

For example, the SQL Server Contributor role does not grant access to connect to a database or data warehouse. This permission must be granted within the database using T-SQL statements.

 Important

Special characters like colon : or ampersand & are not supported in user names in the T-SQL CREATE LOGINand CREATE USER statements.

To add a user with Azure AD authentication:

1. Connect to your Azure SQL server using an Azure AD account with at least the ALTER ANY USER permission.
2. In Object Explorer, right-click the database and select New Query.
3. In the query window, enter the following command and modify <Azure_AD_principal_name> to the principal name of the Azure AD user or the display name of the Azure AD group:
   SQL

   CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
   

 Note

Azure AD users are marked in the database metadata with type E (EXTERNAL_USER) and type X (EXTERNAL_GROUPS) for groups. For more information, see sys.database_principals.

Secure connection strings

To ensure a secure, encrypted connection between the client application and SQL database, a connection string must be configured to:

• Request an encrypted connection
• Not trust the server certificate

The connection is established using Transport Layer Security (TLS) and reduces the risk of a man-in-the-middle attack. Connection strings are available per database and are pre-configured to support client drivers such as ADO.NET, JDBC, ODBC, and PHP. For information about TLS and connectivity, see TLS considerations.

To copy a secure connection string:

1. In Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
2. On the Overview page, select Show database connection strings.
3. Select a driver tab and copy the complete connection string.
   

Enable security features

Azure SQL Database provides security features that are accessed using the Azure portal. These features are available for both the database and server, except for data masking, which is only available on the database. To learn more, see Advanced data security, Auditing, Dynamic data masking, and Transparent data encryption.

Advanced data security

The advanced data security 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.

 Note

An example threat is SQL injection, a process where attackers inject malicious SQL into application inputs. An application can then unknowingly execute the malicious SQL and allow attackers access to breach or modify data in the database.

To enable advanced data security:

1. In Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
2. On the Overview page, select the Server name link. The database server page will open.
3. On the SQL server page, find the Security section and select Advanced Data Security.
   1. Select ON under Advanced Data Security to enable the feature. Choose a storage account for saving vulnerability assessment results. Then select Save.
      
      You can also configure emails to receive security alerts, storage details, and threat detection types.
4. Return to the SQL databases page of your database and select Advanced Data Security under the Security section. Here you'll find various security indicators available for the database.
   

If anomalous activities are detected, you receive an email with information on the event. This includes the nature of the activity, database, server, event time, possible causes, and recommended actions to investigate and mitigate the potential threat. If such an email is received, select the Azure SQL Auditing Log link to launch the Azure portal and show relevant auditing records for the time of the event.

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:

1. In Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
2. In the Security section, select Auditing.
3. Under Auditing settings, set the following values:
   1. Set Auditing to ON.
   2. 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
         Tip

        Use the same storage account for all audited databases to get the most from auditing report templates.
      • Log Analytics, which automatically stores events for query or further analysis
         Note

        A Log Analytics workspace is required to support advanced features such as analytics, custom alert rules, and Excel or Power BI exports. Without a workspace, only the query editor is available.
      • Event Hub, which allows events to be routed for use in other applications
   3. Select Save.
      
4. Now you can select View audit logs to view database events data.
   

 Important

See SQL database auditing on how to further customize audit events using PowerShell or REST API.

Dynamic data masking

The data masking feature will automatically hide sensitive data in your database.

To enable data masking:

1. In Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
2. In the Security section, select Dynamic Data Masking.
3. 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.
   
4. Select Save. The selected information is now masked for privacy.
   

Transparent data encryption

The encryption feature automatically encrypts your data at rest, and requires no changes to applications accessing the encrypted database. For new databases, encryption is on by default. You can also encrypt data using SSMS and the Always encrypted feature.

To enable or verify encryption:

1. In Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
2. In the Security section, select Transparent data encryption.
3. If necessary, set Data encryption to ON. Select Save.
   

 Note

To view encryption status, connect to the database using SSMS and query the encryption_state column of the sys.dm_database_encryption_keys view. A state of 3 indicates the database is encrypted.