Azure SQL 数据库和 SQL 数据仓库访问控制Azure SQL Database and SQL Data Warehouse access control

为了确保安全性,Azure SQL 数据库SQL 数据仓库会进行访问控制,即:使用防火墙规则来限制通过 IP 地址进行的连接,使用身份验证机制来要求用户证明其身份,并使用授权机制来限制用户执行特定操作和访问特定数据。To provide security, Azure SQL Database and SQL Data Warehouse control access with firewall rules limiting connectivity by IP address, authentication mechanisms requiring users to prove their identity, and authorization mechanisms limiting users to specific actions and data.

Important

有关 SQL 数据库安全功能的概述,请参阅 SQL 安全概述For an overview of the SQL Database security features, see SQL security overview. 有关教程,请参阅保护 Azure SQL 数据库For a tutorial, see Secure your Azure SQL Database. 有关 SQL 数据仓库安全功能的概述,请参阅 SQL 数据仓库安全概述For an overview of SQL Data Warehouse security features, see SQL Data Warehouse security overview

防火墙和防火墙规则Firewall and firewall rules

Azure SQL 数据库为 Azure 和其他基于 Internet 的应用程序提供关系型数据库服务。Azure SQL Database provides a relational database service for Azure and other Internet-based applications. 为了保护你的数据,在你指定哪些计算机具有访问权限之前,防火墙将禁止所有对数据库服务器的访问。To help protect your data, firewalls prevent all access to your database server until you specify which computers have permission. 防火墙基于每个请求的起始 IP 地址授予数据库访问权限。The firewall grants access to databases based on the originating IP address of each request. 有关详细信息,请参阅 Azure SQL 数据库防火墙规则概述For more information, see Overview of Azure SQL Database firewall rules

只能通过 TCP 端口 1433 使用 Azure SQL 数据库服务。The Azure SQL Database service is only available through TCP port 1433. 若要从计算机访问 SQL 数据库,请确保客户端计算机防火墙允许 TCP 端口 1433 上的传出 TCP 通信。To access a SQL Database from your computer, ensure that your client computer firewall allows outgoing TCP communication on TCP port 1433. 如果其他应用程序不需要,则阻止 TCP 端口 1433 上的入站连接。If not needed for other applications, block inbound connections on TCP port 1433.

在连接过程中,来自 Azure 虚拟机的连接将重定向到每个辅助角色特有的不同 IP 地址和端口。As part of the connection process, connections from Azure virtual machines are redirected to a different IP address and port, unique for each worker role. 该端口号在 11000 到 11999 的范围内。The port number is in the range from 11000 to 11999. 有关 TCP 端口的详细信息,请参阅用于 ADO.NET 4.5 和 SQL Database2 的非 1433 端口For more information about TCP ports, see Ports beyond 1433 for ADO.NET 4.5 and SQL Database2.

身份验证Authentication

SQL 数据库支持两种类型的身份验证:SQL Database supports two types of authentication:

  • SQL 身份验证SQL Authentication:

    此身份验证方法使用用户名和密码。This authentication method uses a username and password. 在为数据库创建 SQL 数据库服务器时,已指定一个包含用户名和密码的“服务器管理员”登录名。When you created the SQL Database server for your database, you specified a "server admin" login with a username and password. 通过这些凭据,可以使用数据库所有者(即“dbo”)的身份通过服务器上任何数据库的身份验证。Using these credentials, you can authenticate to any database on that server as the database owner, or "dbo."

  • Azure Active Directory 身份验证Azure Active Directory Authentication:

    此身份验证方法使用由 Azure Active Directory 托管的标识,并且受托管域和集成域支持。This authentication method uses identities managed by Azure Active Directory and is supported for managed and integrated domains. 如果想要使用 Azure Active Directory 身份验证,则必须创建名为“Azure AD 管理员”的另一个服务器管理员,用于管理 Azure AD 用户和组。If you want to use Azure Active Directory Authentication, you must create another server admin called the "Azure AD admin," which is allowed to administer Azure AD users and groups. 此管理员还能执行普通服务器管理员可以执行的所有操作。This admin can also perform all operations that a regular server admin can. 有关如何创建 Azure AD 管理员以启用 Azure Active Directory 身份验证的演练,请参阅通过使用 Azure Active Directory 身份验证连接到 SQL 数据库See Connecting to SQL Database By Using Azure Active Directory Authentication for a walkthrough of how to create an Azure AD admin to enable Azure Active Directory Authentication.

数据库引擎将关闭空闲超过 30 分钟的连接。The Database Engine closes connections that remain idle for more than 30 minutes. 该连接必须重新登录才可供使用。The connection must login again before it can be used. 连续与 SQL 数据库建立活动连接需要至少每隔 10 小时重新授权一次(由数据库引擎执行授权)。Continuously active connections to SQL Database require reauthorization (performed by the database engine) at least every 10 hours. 数据库引擎将尝试使用最初提交的密码重新授权,且不需要用户输入。The database engine attempts reauthorization using the originally submitted password and no user input is required. 出于性能原因,在 SQL 数据库中重置密码时,不会对连接重新进行身份验证,即使该连接由于连接池而重置。For performance reasons, when a password is reset in SQL Database, the connection is not reauthenticated, even if the connection is reset due to connection pooling. 这一点与本地 SQL Server 的行为不同。This is different from the behavior of on-premises SQL Server. 如果在最初授权连接后密码发生更改,则必须终止该连接,并使用新密码建立新连接。If the password has been changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. 具有 KILL DATABASE CONNECTION 权限的用户可以使用 KILL 命令显式终止与 SQL 数据库的连接。A user with the KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command.

可在 master 数据库中创建用户帐户并向其授予服务器上所有数据库中的权限,或者在数据库本身内部创建用户帐户(称为包含的用户)。User accounts can be created in the master database and can be granted permissions in all databases on the server, or they can be created in the database itself (called contained users). 有关创建和管理登录名的信息,请参阅管理登录名For information on creating and managing logins, see Manage logins. 若要增强可移植性和可伸缩性,请使用包含的数据库。Use contained databases to enhance portability and scalability. 有关包含的用户的详细信息,请参阅包含的数据库用户 - 使数据库可移植CREATE USER (Transact-SQL)包含的数据库For more information on contained users, see Contained Database Users - Making Your Database Portable, CREATE USER (Transact-SQL), and Contained Databases.

最佳做法是,应用程序应使用专用帐户进行身份验证 – 这样,就可以限制授予应用程序的权限,并在应用程序代码容易受到 SQL 注入攻击的情况下降低恶意活动的风险。As a best practice your application should use a dedicated account to authenticate -- this way you can limit the permissions granted to the application and reduce the risks of malicious activity in case your application code is vulnerable to a SQL injection attack. 建议的方法是创建 包含数据库用户,使应用程序能够直接向数据库进行身份验证。The recommended approach is to create a contained database user, which allows your app to authenticate directly to the database.

授权Authorization

授权是指用户可以在 Azure SQL 数据库中执行哪些操作,这由用户帐户的数据库角色成员身份对象级权限来控制。Authorization refers to what a user can do within an Azure SQL Database, and this is controlled by your user account's database role memberships and object-level permissions. 作为最佳实践,应向用户授予所需的最低权限。As a best practice, you should grant users the least privileges necessary. 用于连接的服务器管理员帐户是 db_owner 所有者的成员,该帐户有权在数据库中执行任何操作。The server admin account you are connecting with is a member of db_owner, which has authority to do anything within the database. 请保存此帐户,以便部署架构升级并执行其他管理操作。Save this account for deploying schema upgrades and other management operations. 权限受到更多限制的“ApplicationUser”帐户可让用户使用应用程序所需的最低权限从应用程序连接到数据库。Use the "ApplicationUser" account with more limited permissions to connect from your application to the database with the least privileges needed by your application. 有关详细信息,请参阅管理登录名For more information, see Manage logins.

通常,只有管理员需要 master 数据库的访问权限。Typically, only administrators need access to the master database. 对每个用户数据库的例程访问应通过数据库用户进行,这些用户创建于每个数据库且不包含管理员。Routine access to each user database should be through non-administrator contained database users created in each database. 使用包含的数据库用户时,不需要在 master 数据库中创建登录名。When you use contained database users, you do not need to create logins in the master database. 有关详细信息,请参阅包含的数据库用户 - 使数据库可移植For more information, see Contained Database Users - Making Your Database Portable.

用户应熟悉以下功能,以便限制或提升权限:You should familiarize yourself with the following features that can be used to limit or elevate permissions:

后续步骤Next steps