适用于:yesAzure SQL 托管实例
Azure SQL 托管实例几乎提供最新 SQL Server(企业版)数据库引擎具备的所有安全功能:
- 在隔离的环境中限制访问
- 使用需要标识的身份验证机制:Azure Active Directory (Azure AD) 和 SQL 身份验证
- 将授权与基于角色的成员身份和权限配合使用
- 启用安全功能
在本教程中,你将了解如何执行以下操作:
- 为托管实例创建 Azure AD 服务器主体(登录名)
- 将权限授予托管实例中的 Azure AD 服务器主体(登录名)
- 基于 Azure AD 服务器主体(登录名)创建 Azure AD 用户
- 向 Azure AD 用户分配权限并管理数据库安全性
- 以 Azure AD 用户身份使用模拟
- 以 Azure AD 用户身份使用跨数据库查询
- 了解安全功能,例如威胁防护、审核、数据掩码和加密
若要了解详细信息,请参阅 Azure SQL 托管实例概述。
先决条件
若要完成本教程,请确保具备以下先决条件:
- SQL Server Management Studio (SSMS)
- 托管实例
- 遵循以下文章:快速入门:创建托管实例
- 能够访问托管实例,并且为托管实例预配了 Azure AD 管理员。 若要了解更多信息,请参阅以下文章:
限制访问
可以通过专用 IP 地址访问托管实例。 与在隔离的 SQL Server 环境中非常类似,应用程序或用户需要访问 SQL 托管实例网络 (VNet) 才能建立连接。 有关详细信息,请参阅将应用程序连接到 SQL 托管实例。
还可以在托管实例上配置服务终结点,该终结点允许使用与 Azure SQL 数据库相同的方式进行公共连接。 有关详细信息,请参阅在 Azure SQL 托管实例中配置公共终结点。
Note: 即使启用了服务终结点,Azure SQL 数据库防火墙规则也不适用。 Azure SQL 托管实例使用自己的内置防火墙来管理连接。
使用 SSMS 创建 Azure AD 服务器主体(登录名)
第一个 Azure AD 服务器主体(登录名)可以由标准 SQL 管理员帐户(非 Azure AD)创建,该帐户是 sysadmin,或者是在设置过程中创建的托管实例的 Azure AD 管理员。 有关详细信息,请参阅为 SQL 托管实例预配 Azure Active Directory 管理员。
有关如何连接到 SQL 托管实例的示例,请参阅以下文章:
-
在 SQL Server Management Studio 中使用充当
sysadmin或 SQL 托管实例的 Azure AD 管理员的标准 SQL 登录帐户(非 Azure AD)登录到托管实例。 -
在“对象资源管理器”中右键服务器,然后选择“新建查询”。
-
在查询窗口中,使用以下语法为本地 Azure AD 帐户创建登录名:
USE master GO CREATE LOGIN login_name FROM EXTERNAL PROVIDER GO此示例为帐户 nativeuser@aadsqlmi.onmicrosoft.com 创建登录名。
USE master GO CREATE LOGIN [nativeuser@aadsqlmi.onmicrosoft.com] FROM EXTERNAL PROVIDER GO -
在工具栏上,选择“执行”以创建登录名。
-
执行以下 T-SQL 命令检查新添加的登录名:
SELECT * FROM sys.server_principals; GOSSMS 对象资源管理器中“结果”选项卡的屏幕截图,其中显示了新添加的登录名的名称、principal_id、sid、类型和 type_desc。
有关详细信息,请参阅 CREATE LOGIN。
授予用于创建登录名的权限
若要创建其他 Azure AD 服务器主体(登录名),必须向主体(SQL 或 Azure AD)授予 SQL Server 角色或权限。
SQL 身份验证
- 如果登录名是 SQL 主体,则只有属于
sysadmin角色的登录名才能使用 create 命令来为 Azure AD 帐户创建登录名。
Azure AD 身份验证
- 要使新建的 Azure AD 服务器主体(登录名)能够为其他 Azure AD 用户、组或应用程序创建其他登录名,请向新建的登录名授予
sysadmin或securityadmin服务器角色。 - 最起码需要向 Azure AD 服务器主体(登录名)授予 ALTER ANY LOGIN 权限才能让其创建其他 Azure AD 服务器主体(登录名)。
- 默认情况下,向 master 数据库中新建的 Azure AD 服务器主体(登录名)授予的标准权限为: CONNECT SQL 和 VIEW ANY DATABASE 。
- 可向托管实例中的多个 Azure AD 服务器主体(登录名)授予
sysadmin服务器角色。
将登录名添加到 sysadmin 服务器角色:
-
再次登录到托管实例,或通过充当
sysadmin的 Azure AD 管理员或 SQL 主体使用现有连接。 -
在“对象资源管理器”中右键服务器,然后选择“新建查询”。
-
使用以下 T-SQL 语法向 Azure AD 服务器主体(登录名)授予
sysadmin服务器角色:ALTER SERVER ROLE sysadmin ADD MEMBER login_name GO以下示例向登录名 nativeuser@aadsqlmi.onmicrosoft.com 授予
sysadmin服务器角色ALTER SERVER ROLE sysadmin ADD MEMBER [nativeuser@aadsqlmi.onmicrosoft.com] GO
使用 SSMS 创建其他 Azure AD 服务器主体(登录名)
创建 Azure AD 服务器主体(登录名)并向其提供 sysadmin 特权后,该登录名可以结合 CREATE LOGIN 使用 FROM EXTERNAL PROVIDER 子句创建其他登录名。
-
在 SQL Server Management Studio 中使用 Azure AD 服务器主体(登录名)连接到托管实例。 输入 SQL 托管实例主机名。 若要在 SSMS 中进行身份验证,可在使用 Azure AD 帐户登录时从三个选项中选择:
-
Active Directory - 支持 MFA 的通用方法
-
Active Directory - 密码
-
Active Directory - 集成
SSMS 中“连接到服务器”对话框的屏幕截图,显示在“身份验证”下拉列表中选择了“Active Directory - 通用且具有 MFA 支持”。
有关详细信息,请参阅通用身份验证(SSMS 支持多重身份验证)。
-
-
选择“Active Directory - 支持 MFA 的通用方法”。 此时会打开“多重身份验证登录”窗口。 使用 Azure AD 密码登录。
“多重身份验证登录”窗口的屏幕截图,其中光标位于“输入密码”字段中。
-
在 SSMS 的“对象资源管理器”中右键服务器,然后选择“新建查询”。
-
在查询窗口中,使用以下语法为另一个 Azure AD 帐户创建登录名:
USE master GO CREATE LOGIN login_name FROM EXTERNAL PROVIDER GO此示例为 Azure AD 用户 bob@aadsqlmi.net 创建登录名。该用户的 aadsqlmi.net 域已与 Azure AD aadsqlmi.onmicrosoft.com 域相联合。
执行以下 T-SQL 命令。 联合的 Azure AD 帐户是本地 Windows 登录名和用户的 SQL 托管实例替代项。
USE master GO CREATE LOGIN [bob@aadsqlmi.net] FROM EXTERNAL PROVIDER GO -
使用 CREATE DATABASE 语法在托管实例中创建数据库。 在下一部分,此数据库将用于测试用户登录名。
-
在“对象资源管理器”中右键服务器,然后选择“新建查询”。
-
在查询窗口中,使用以下语法创建名为 MyMITestDB 的数据库。
CREATE DATABASE MyMITestDB; GO
-
-
为 Azure AD 中的某个组创建 SQL 托管实例登录名。 Azure AD 中必须存在该组,才能将该登录名添加到 SQL 托管实例。 请参阅使用 Azure Active Directory 创建基本组并添加成员。 创建组 mygroup 并在其中添加成员。
-
在 SQL Server Management Studio 中打开新的查询窗口。
此示例假设 Azure AD 中存在名为 mygroup 的组。 执行以下命令:
USE master GO CREATE LOGIN [mygroup] FROM EXTERNAL PROVIDER GO -
为了进行测试,请使用新建的登录名或组登录到托管实例。 与托管实例建立新的连接,并在身份验证时使用新登录名。
-
在“对象资源管理器”中右键服务器,然后选择新连接对应的“新建查询”。
-
执行以下命令,检查新建的 Azure AD 服务器主体(登录名)的服务器权限:
SELECT * FROM sys.fn_my_permissions (NULL, 'DATABASE') GO
Note: 对于 Azure AD 来宾用户,仅当已将其添加为 Azure AD 组的一部分时,才支持将其用于 SQL 托管实例登录名。 Azure AD 来宾用户是指在另一个 Azure AD 实例中邀请其加入托管实例所属 Azure AD 实例的帐户。 例如,可将 joe@contoso.com(Azure AD 帐户)或 steve@outlook.com(Microsoft 帐户)添加到 Azure AD aadsqlmi 实例中的组。 将用户添加到组后,可以使用 CREATE LOGIN 语法在 SQL 托管实例 master 数据库中为该组创建登录名 。 属于此组的来宾用户可以使用其当前登录名(例如 joe@contoso.com 或 steve@outlook.com)连接到托管实例。
基于 Azure AD 服务器主体(登录名)创建 Azure AD 用户
在 SQL 托管实例中对单个数据库授权的方式非常类似于 SQL Server 中数据库的授权。 可以基于数据库中的现有登录名创建用户并为其提供对该数据库的权限,或者将该用户添加到数据库角色。
创建名为 MyMITestDB 的数据库以及一个只有默认权限的登录名后,下一步是基于该登录名创建用户。 目前,该登录名可以连接到托管实例和查看所有数据库,但无法与数据库交互。 如果使用具有默认权限的 Azure AD 帐户登录并尝试展开新建的数据库,将会看到以下错误:
来自 SSMS 对象资源管理器的错误消息的屏幕截图,该消息显示“数据库 MyMITestDB 不可访问。 (ObjectExplorer)”。
有关授予数据库权限的详细信息,请参阅数据库引擎权限入门。
创建 Azure AD 用户并创建示例表
-
在 SQL Server Management Studio 中使用
sysadmin帐户登录到托管实例。 -
在“对象资源管理器”中右键服务器,然后选择“新建查询”。
-
在查询窗口中,使用以下语法基于 Azure AD 服务器主体(登录名)创建 Azure AD 用户:
USE <Database Name> -- provide your database name GO CREATE USER user_name FROM LOGIN login_name GO以下示例基于登录名 bob@aadsqlmi.net: 创建用户 bob@aadsqlmi.net。
USE MyMITestDB GO CREATE USER [bob@aadsqlmi.net] FROM LOGIN [bob@aadsqlmi.net] GO -
也支持基于组的形式的 Azure AD 服务器主体(登录名)创建 Azure AD 用户。
以下示例为 Azure AD 实例中的 Azure AD 组 mygroup 创建登录名。
USE MyMITestDB GO CREATE USER [mygroup] FROM LOGIN [mygroup] GO属于 mygroup 的所有用户都可以访问 MyMITestDB 数据库。
IMPORTANT 基于 Azure AD 服务器主体(登录名)创建 USER 时,请指定与 LOGIN 中的 login_name 相同的 user_name。
有关详细信息,请参阅 CREATE USER。
-
在新查询窗口中,使用以下 T-SQL 命令创建测试表:
USE MyMITestDB GO CREATE TABLE TestTable ( AccountNum varchar(10), City varchar(255), Name varchar(255), State varchar(2) ); -
使用创建的用户在 SSMS 中创建连接。 你会注意到,无法看到
sysadmin在以前创建的表 TestTable 。 我们需要向该用户提供读取数据库中的数据的权限。 -
可以执行以下命令来检查用户当前拥有的权限:
SELECT * FROM sys.fn_my_permissions('MyMITestDB','DATABASE') GO
将用户添加到数据库级角色
要使用户能够查看数据库中的数据,我们可以向该用户提供数据库级角色。
-
在 SQL Server Management Studio 中使用
sysadmin帐户登录到托管实例。 -
在“对象资源管理器”中右键服务器,然后选择“新建查询”。
-
使用以下 T-SQL 语法向 Azure AD 用户授予
db_datareader数据库角色:Use <Database Name> -- provide your database name ALTER ROLE db_datareader ADD MEMBER user_name GO以下示例为用户 bob@aadsqlmi.net 和组 mygroup 提供对 MyMITestDB 数据库的
db_datareader权限:USE MyMITestDB GO ALTER ROLE db_datareader ADD MEMBER [bob@aadsqlmi.net] GO ALTER ROLE db_datareader ADD MEMBER [mygroup] GO -
执行以下命令,检查在数据库中创建的 Azure AD 用户是否存在:
SELECT * FROM sys.database_principals GO -
使用已添加到
db_datareader角色的用户身份与托管实例建立新的连接。 -
在“对象资源管理器”中展开数据库,以查看表。
SSMS 中对象资源管理器的屏幕截图,显示了 MyMITestDB 中表的文件夹结构。 dbo.TestTable 文件夹突出显示。
-
打开新查询窗口并执行以下 SELECT 语句:
SELECT * FROM TestTable是否能够看到表中的数据? 应会看到返回的列。
SSMS 对象资源管理器中“结果”选项卡的屏幕截图,显示了表的列标题 AccountNum、City、Name 和 State。
模拟 Azure AD 服务器级主体(登录名)
SQL 托管实例支持模拟 Azure AD 服务器级主体(登录名)。
测试模拟
-
在 SQL Server Management Studio 中使用
sysadmin帐户登录到托管实例。 -
在“对象资源管理器”中右键服务器,然后选择“新建查询”。
-
在查询窗口中,使用以下命令创建新的存储过程:
USE MyMITestDB GO CREATE PROCEDURE dbo.usp_Demo WITH EXECUTE AS 'bob@aadsqlmi.net' AS SELECT user_name(); GO -
使用以下命令来查看执行该存储过程时模拟的用户是否为 bob@aadsqlmi.net。
Exec dbo.usp_Demo -
使用 EXECUTE AS LOGIN 语句测试模拟:
EXECUTE AS LOGIN = 'bob@aadsqlmi.net' GO SELECT SUSER_SNAME() REVERT GO
Note: 只有属于
sysadmin角色的 SQL 服务器级主体(登录名)可以针对 Azure AD 主体执行以下操作:
- EXECUTE AS USER
- EXECUTE AS LOGIN
使用跨数据库查询
使用 Azure AD 服务器主体(登录名)的 Azure AD 帐户支持跨数据库查询。 若要使用 Azure AD 组测试跨数据库查询,需要创建另一个数据库和表。 如果已存在一个数据库和表,则无需额外创建。
-
在 SQL Server Management Studio 中使用
sysadmin帐户登录到托管实例。 -
在“对象资源管理器”中右键服务器,然后选择“新建查询”。
-
在查询窗口中,使用以下命令创建名为 MyMITestDB2 的数据库和名为 TestTable2 的表。
CREATE DATABASE MyMITestDB2; GO USE MyMITestDB2 GO CREATE TABLE TestTable2 ( EmpId varchar(10), FirstName varchar(255), LastName varchar(255), Status varchar(10) ); -
在新查询窗口中执行以下命令,在新数据库 MyMITestDB2 中创建用户 mygroup ,并向 mygroup 授予对该数据库的 SELECT 权限:
USE MyMITestDB2 GO CREATE USER [mygroup] FROM LOGIN [mygroup] GO GRANT SELECT TO [mygroup] GO -
使用 SQL Server Management Studio 以 Azure AD 组 mygroup 的成员身份登录到托管实例。 打开新查询窗口并执行跨数据库 SELECT 语句:
USE MyMITestDB SELECT * FROM MyMITestDB2..TestTable2 GO应会看到 TestTable2 的表结果。
其他受支持方案
- Azure AD 服务器主体(登录名)支持 SQL 代理管理和作业执行。
- Azure AD 服务器主体(登录名)可以执行数据库备份和还原操作。
- 审核与 Azure AD 服务器主体(登录名)和身份验证事件相关的所有语句。
- 属于
sysadmin服务器角色成员的 Azure AD 服务器主体(登录名)可以建立专用管理员连接。 - 支持 Azure AD 服务器主体(登录名)使用 sqlcmd 实用工具和 SQL Server Management Studio 工具。
- 来自 Azure AD 服务器主体(登录名)的登录事件支持登录触发器。
- 可以使用 Azure AD 服务器主体(登录名)设置 Service Broker 和数据库邮件。
后续步骤
启用安全功能
请参阅 SQL 托管实例安全功能一文,全面了解保护数据库的各种方式。 此文介绍了以下安全功能:
SQL 托管实例功能
有关 SQL 托管实例功能的完整概述,请参阅:
历史资料。此文对应 2022-08-12 的中文文档快照,适用产品版本和命令行为以原文为准。
来源与署名:Microsoft 和文档贡献者(原始作者元数据 GitHubMirek)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。
