教程:保护单一数据库或共用数据库Tutorial: Secure a single or pooled database

本教程介绍如何执行下列操作:In this tutorial you learn how to:

  • 创建服务器级和数据库级防火墙规则Create server-level and database-level firewall rules
  • 配置 Azure Active Directory (AD) 管理员Configure an Azure Active Directory (AD) administrator
  • 通过 SQL 身份验证、Azure AD 身份验证和安全的连接字符串管理用户访问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 数据库允许你通过以下方式确保单一数据库或共用数据库中数据的安全: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

若要了解详细信息,请参阅 Azure SQL 数据库安全概述功能这两篇文章。To learn more, see the Azure SQL Database security overview and capabilities articles.

先决条件Prerequisites

若要完成本教程,请确保具备以下先决条件:To complete the tutorial, make sure you have the following prerequisites:

如果没有 Azure 订阅,可在开始前创建一个 1 元人民币试用帐户If you don't have an Azure subscription, create a 1rmb trial account before you begin.

登录到 Azure 门户Sign in to the Azure portal

若要完成本教程中的所有步骤,请登录到 Azure 门户For all steps in the tutorial, sign in to Azure portal

创建防火墙规则Create firewall rules

SQL 数据库受 Azure 中的防火墙保护。SQL databases are protected by firewalls in Azure. 默认情况下,将拒绝与服务器和数据库的所有连接,来自其他 Azure 服务的连接除外。By default, all connections to the server and database are rejected, except for connections from other Azure services. 若要了解详细信息,请参阅 Azure SQL 数据库服务器级和数据库级防火墙规则To learn more, see Azure SQL Database server-level and database-level firewall rules.

将“允许访问 Azure 服务”设置为“关闭”即可启用最安全的配置。Set Allow access to Azure services to OFF for the most secure configuration. 然后,为需要连接的资源(例如 Azure VM 或云服务)创建一个保留 IP(经典部署),仅允许该 IP 地址通过防火墙进行访问。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. 如果使用资源管理器部署模型,则每个资源都需要一个专用的公共 IP 地址。If you're using the resource manager deployment model, a dedicated public IP address is required for each resource.

Note

通过端口 1433 进行的 SQL 数据库通信。SQL Database communicates over port 1433. 如果尝试从企业网络内部进行连接,则该网络的防火墙可能不允许经端口 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. 如果是这样,则无法连接到 Azure SQL 数据库服务器,除非管理员打开端口 1433。If so, you can't connect to the Azure SQL Database server unless your administrator opens port 1433.

设置 SQL 数据库服务器防火墙规则Set up SQL Database server firewall rules

服务器级 IP 防火墙规则适用于同一 SQL 数据库服务器中的所有数据库。Server-level IP firewall rules apply to all databases within the same SQL Database server.

若要设置服务器级防火墙规则,请执行以下操作:To set up a server-level firewall rule:

  1. 在 Azure 门户中,选择左侧菜单中的“SQL 数据库”,然后在“SQL 数据库”页面上选择数据库。In Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.

    服务器防火墙规则

    Note

    确保复制完全限定的服务器名称(例如 yourserver.database.chinacloudapi.cn),以便稍后在本教程中使用。Be sure to copy your fully qualified server name (such as yourserver.database.chinacloudapi.cn) 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. 在工具栏上选择“添加客户端 IP”,将当前的 IP 地址添加到新的防火墙规则。Select Add client IP on the toolbar to add your current IP address to a new firewall rule. 此规则可以针对单个 IP 地址或一系列 IP 地址打开端口 1433。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.

现可使用指定的 IP 地址或 IP 地址范围连接到服务器中的任何数据库。You can now connect to any database in the server with the specified IP address or IP address range.

Important

默认情况下,已在“允许访问 Azure 服务”下为所有 Azure 服务启用通过 SQL 数据库防火墙进行访问的功能。By default, access through the SQL Database firewall is enabled for all Azure services, under Allow access to Azure services. 选择“关闭”即可禁止访问所有 Azure 服务。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. 数据库级防火墙规则只能使用 Transact-SQL (T-SQL) 语句进行配置,而且只能在配置了服务器级防火墙规则后进行配置。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. 例如,使用 SQL Server Management Studio 连接到数据库。Connect to the database, for example using SQL Server Management Studio.

  2. 在“对象资源管理器”中,右键单击数据库,并选择“新建查询”。In Object Explorer, right-click the database and select New Query.

  3. 在查询窗口中添加此语句,将 IP 地址修改为公共 IP 地址:In the query window, add this statement and modify the IP address to your public IP address:

    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

也可使用 sp_set_firewall_rule 命令在 SSMS 中创建服务器级防火墙规则,不过必须连接到 master 数据库。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.

创建 Azure AD 管理员Create an Azure AD admin

确保使用相应的 Azure Active Directory (AD) 托管域。Make sure you're using the appropriate Azure Active Directory (AD) managed domain. 若要选择 AD 域,请在 Azure 门户的右上角操作。To select the AD domain, use the upper-right corner of the Azure portal. 此过程确认是否对 Azure AD 和托管 Azure SQL 数据库或数据仓库的 SQL Server 使用了同一订阅。This process confirms the same subscription is used for both Azure AD and the SQL Server hosting your Azure SQL database or data warehouse.

选择-AD

若要设置 Azure AD 管理员,请执行以下操作:To set the Azure AD administrator:

  1. 在 Azure 门户的“SQL Server”页上,选择“Active Directory 管理员”。接下来选择“设置管理员”。In Azure portal, on the SQL server page, select Active Directory admin. Next select Set admin.

    选择 active directory

    Important

    需要是“公司管理员”或“全局管理员”才能执行此任务。You need to be either a "Company Administrator" or "Global Administrator" to perform this task.

  2. 在“添加管理员”页上搜索并选择 AD 用户或组,然后选择“选择”。On the Add admin page, search and select the AD user or group and choose Select. 将会列出 Active Directory 的所有成员和组,灰显的条目不能用作 Azure AD 管理员。All members and groups of your Active Directory are listed, and entries grayed out are not supported as Azure AD administrators. 请参阅 Azure AD 功能和限制See Azure AD features and limitations.

    选择管理员

    Important

    基于角色的访问控制 (RBAC) 仅适用于门户,不会传播到 SQL Server。Role-based access control (RBAC) only applies to the portal and isn't propagated to SQL Server.

  3. 在“Active Directory 管理员”页顶部,选择“保存”。At the top of the Active Directory admin page, select Save.

    更改管理员的过程可能需要几分钟时间。The process of changing an administrator may take several minutes. 新管理员会出现在“Active Directory 管理员”框中。The new administrator will appear in the Active Directory admin box.

Note

设置 Azure AD 管理员时,此新的管理员名称(用户或组)不能作为 SQL Server 身份验证用户存在于 master 数据库中。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. 由于此 SQL Server 身份验证用户不是 Azure AD 的一部分,因此使用 Azure AD 身份验证连接用户的任何尝试都会失败。Since the SQL Server authentication user is not part of Azure AD, any effort to connect the user using Azure AD authentication fails.

若要了解如何配置 Azure AD,请参阅:For information about configuring Azure AD, see:

管理数据库访问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. 若要了解详细信息,请参阅 AD 身份验证To learn more, see AD authentication.

若要添加用户,请选择数据库身份验证类型:To add users, choose the database authentication type:

  • SQL 身份验证,使用用户名和密码登录,仅适用于服务器中存在特定数据库的情况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 身份验证,使用 Azure AD 托管的标识Azure AD authentication, use identities managed by Azure AD

SQL 身份验证SQL authentication

若要通过 SQL 身份验证来添加用户,请执行以下操作:To add a user with SQL authentication:

  1. 例如,使用 SQL Server Management Studio 连接到数据库。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:

    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:

    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 身份验证Azure AD authentication

Azure Active Directory 身份验证要求将数据库用户作为包含的用户来创建。Azure Active Directory authentication requires that database users are created as contained. 包含的数据库用户映射到与数据库关联的 Azure AD 目录中的标识,且在 master 数据库中没有登录名。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. Azure AD 标识可以用于单个用户,也可以用于一个组。The Azure AD identity can either be for an individual user or a group. 有关详细信息,请参阅包含的数据库用户 - 使你的数据库可移植,并查看 Azure AD 教程,了解如何使用 Azure AD 进行身份验证。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

不能使用 Azure 门户创建数据库用户(管理员除外)。Database users (excluding administrators) cannot be created using the Azure portal. Azure RBAC 角色不会传播到 SQL Server、数据库或数据仓库。Azure RBAC roles do not propagate to SQL servers, databases, or data warehouses. 它们只能用于管理 Azure 资源,不会应用于数据库权限。They are only used to manage Azure resources and do not apply to database permissions.

例如,“SQL Server 参与者”角色不会授予连接到数据库或数据仓库的访问权限。For example, the SQL Server Contributor role does not grant access to connect to a database or data warehouse. 必须使用 T-SQL 语句在数据库中授予此权限。This permission must be granted within the database using T-SQL statements.

Important

不支持在 T-SQL 的 CREATE LOGINCREATE USER 语句中将特殊字符(例如冒号 : 或与号 &)用作用户名的一部分。Special characters like colon : or ampersand & are not supported in user names in the T-SQL CREATE LOGIN and CREATE USER statements.

若要通过 Azure AD 身份验证来添加用户,请执行以下操作:To add a user with Azure AD authentication:

  1. 使用至少带 ALTER ANY USER 权限的 Azure AD 帐户连接到 Azure SQL Server。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. 在查询窗口中输入以下命令,并将 <Azure_AD_principal_name> 修改为 Azure AD 用户的主体名称,或者修改为 Azure AD 组的显示名称: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:

    CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
    

Note

Azure AD 用户在数据库元数据中标记为类型 E (EXTERNAL_USER),而组则标记为类型 X (EXTERNAL_GROUPS)Azure AD users are marked in the database metadata with type E (EXTERNAL_USER) and type X (EXTERNAL_GROUPS) for groups. 有关详细信息,请参阅 sys.database_principalsFor more information, see sys.database_principals.

安全的连接字符串Secure connection strings

若要确保客户端应用程序与 SQL 数据库的连接安全且已加密,必须将连接字符串配置为: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

使用传输层安全性 (TLS) 建立连接,减少中间人攻击的风险。The connection is established using Transport Layer Security (TLS) and reduces the risk of a man-in-the-middle attack. 连接字符串按数据库提供,并已预先配置为支持客户端驱动程序,例如 ADO.NET、JDBC、ODBC、PHP。Connection strings are available per database and are pre-configured to support client drivers such as ADO.NET, JDBC, ODBC, and PHP. 有关 TLS 和连接的信息,请参阅 TLS 注意事项For information about TLS and connectivity, see TLS considerations.

若要复制安全的连接字符串,请执行以下操作:To copy a secure connection string:

  1. 在 Azure 门户中,选择左侧菜单中的“SQL 数据库”,然后在“SQL 数据库”页面上选择数据库。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.

    ADO.NET 连接字符串

启用安全功能Enable security features

Azure SQL 数据库提供可以通过 Azure 门户访问的安全功能。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

例如,SQL 注入就是一种威胁,攻击者会在应用程序输入中注入恶意 SQL。An example threat is SQL injection, a process where attackers inject malicious SQL into application inputs. 应用程序随后就会在不知情的情况下执行恶意 SQL,允许攻击者访问,导致数据库中的数据被破坏或修改。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. 在 Azure 门户中,选择左侧菜单中的“SQL 数据库”,然后在“SQL 数据库”页面上选择数据库。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. 在“SQL Server”页上找到“安全性”部分,然后选择“高级数据安全”。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. 返回到数据库的“SQL 数据库”页,在“安全性”部分选择“高级数据安全”。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. 如果收到此类电子邮件,请选择“Azure SQL 审核日志”链接,以便启动 Azure 门户并查看发生事件时的相关审核记录。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

此审核功能跟踪数据库事件并将事件写入到 Azure 存储中的审核日志。The auditing feature tracks database events and writes events to an audit log in Azure storage. 审核可确保一切符合法规、了解数据库活动,以及深入了解那些可能表明存在潜在安全违规的偏差和异常。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. 在 Azure 门户中,选择左侧菜单中的“SQL 数据库”,然后在“SQL 数据库”页面上选择数据库。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:

      • 存储,一个 Azure 存储帐户,可以在其中保存事件日志,并可将其作为 .xel 文件下载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.

    3. 选择“其他安全性验证” 。Select Save.

      审核设置

  4. 现在可以选择“查看审核日志”,以便查看数据库事件数据。Now you can select View audit logs to view database events data.

    审核记录

Important

请参阅 SQL 数据库审核,了解如何使用 PowerShell 或 REST API 来进一步自定义审核事件。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. 在 Azure 门户中,选择左侧菜单中的“SQL 数据库”,然后在“SQL 数据库”页面上选择数据库。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 会自动填充可供选择的数据库架构、表和列。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. 也可使用 SSMS 和 Always Encrypted 功能来加密数据。You can also encrypt data using SSMS and the Always encrypted feature.

若要启用或验证加密,请执行以下操作:To enable or verify encryption:

  1. 在 Azure 门户中,选择左侧菜单中的“SQL 数据库”,然后在“SQL 数据库”页面上选择数据库。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

若要查看加密状态,请使用 SSMS 连接到数据库,然后查询 sys.dm_database_encryption_keys 视图的 encryption_state 列。To view encryption status, connect to the database using SSMS and query the encryption_state column of the sys.dm_database_encryption_keys view. 状态为 3 指示数据库已加密。A state of 3 indicates the database is encrypted.

后续步骤Next steps

本教程介绍了如何通过简单的几个步骤增强数据库的安全性。In this tutorial, you've learned to improve the security of your database with just a few simple steps. 你已了解如何:You learned how to:

  • 创建服务器级和数据库级防火墙规则Create server-level and database-level firewall rules
  • 配置 Azure Active Directory (AD) 管理员Configure an Azure Active Directory (AD) administrator
  • 通过 SQL 身份验证、Azure AD 身份验证和安全的连接字符串管理用户访问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

请转到下一教程,了解如何进行地理分布。Advance to the next tutorial to learn how to implement geo-distribution.