SQL Server 中的身份验证Authentication in SQL Server

SQL Server 支持两种身份验证模式,即 Windows 身份验证模式和混合模式。SQL Server supports two authentication modes, Windows authentication mode and mixed mode.

  • Windows 身份验证是默认模式(通常称为集成安全),因为此 SQL Server 安全模型与 Windows 紧密集成。Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. 特定的 Windows 用户和组帐户可信任,可以登录 SQL Server。Specific Windows user and group accounts are trusted to log in to SQL Server. 已经过身份验证的 Windows 用户无需提供其他凭据。Windows users who have already been authenticated do not have to present additional credentials.

  • 混合模式支持由 Windows 和 SQL Server 进行身份验证。Mixed mode supports authentication both by Windows and by SQL Server. 用户名和密码保留在 SQL Server 内。User name and password pairs are maintained within SQL Server.

重要

建议尽可能使用 Windows 身份验证。We recommend using Windows authentication wherever possible. Windows 身份验证使用一系列加密消息来验证 SQL Server 中的用户。Windows authentication uses a series of encrypted messages to authenticate users in SQL Server. 使用 SQL Server 登录时,会通过网络传递 SQL Server 登录名和加密的密码,这样会降低它们的安全性。When SQL Server logins are used, SQL Server login names and encrypted passwords are passed across the network, which makes them less secure.

使用 Windows 身份验证时,用户已登录到 Windows,无需另外登录到 SQL Server。With Windows authentication, users are already logged onto Windows and do not have to log on separately to SQL Server. 下面的 SqlConnection.ConnectionString 可指定 Windows 身份验证,而无需用户提供用户名或密码。The following SqlConnection.ConnectionString specifies Windows authentication without requiring users to provide a user name or password.

"Server=MSSQL1;Database=AdventureWorks;Integrated Security=true;"

备注

登录名不同于数据库用户。Logins are distinct from database users. 必须在单独的操作中将登录名或 Windows 组映射到数据库用户或角色。You must map logins or Windows groups to database users or roles in a separate operation. 然后,向用户或角色授予对数据库对象的访问权限。You then grant permissions to users or roles to access database objects.

身份验证方案Authentication Scenarios

在以下情况下,Windows 身份验证通常是最佳选择:Windows authentication is usually the best choice in the following situations:

  • 有域控制器。There is a domain controller.

  • 应用程序和数据库位于同一台计算机上。The application and the database are on the same computer.

  • 你正在使用 SQL Server Express 或 LocalDB 的实例。You are using an instance of SQL Server Express or LocalDB.

SQL Server 登录通常用于以下情况:SQL Server logins are often used in the following situations:

  • 有工作组。If you have a workgroup.

  • 用户从不受信任的其他域连接。Users connect from different, non-trusted domains.

  • Internet 应用程序(如 ASP.NET)。Internet applications, such as ASP.NET.

备注

指定 Windows 身份验证不会禁用 SQL Server 登录。Specifying Windows authentication does not disable SQL Server logins. 使用 ALTER LOGIN DISABLE Transact-SQL 语句会禁用具有高级权限的 SQL Server 登录。Use the ALTER LOGIN DISABLE Transact-SQL statement to disable highly-privileged SQL Server logins.

登录类型Login Types

SQL Server 支持三种登录类型:SQL Server supports three types of logins:

  • 本地 Windows 用户帐户或受信任的域帐户。A local Windows user account or trusted domain account. SQL Server 依靠 Windows 来对 Windows 用户帐户进行身份验证。SQL Server relies on Windows to authenticate the Windows user accounts.

  • Windows 组。Windows group. 向 Windows 组授予访问权限会向作为组成员的所有 Windows 用户登录名授予访问权限。Granting access to a Windows group grants access to all Windows user logins that are members of the group.

  • SQL Server 登录。SQL Server login. SQL Server 将用户名和密码的哈希都存储在 master 数据库中,使用内部身份验证方法来验证登录尝试。SQL Server stores both the username and a hash of the password in the master database, by using internal authentication methods to verify login attempts.

备注

SQL Server 提供了从证书或非对称密钥创建的登录名,仅用于代码签名。SQL Server provides logins created from certificates or asymmetric keys that are used only for code signing. 不能用于连接到 SQL Server。They cannot be used to connect to SQL Server.

混合模式身份验证Mixed Mode Authentication

如果必须使用混合模式身份验证,则必须创建 SQL Server 登录名,这些登录名存储在 SQL Server 中。If you must use mixed mode authentication, you must create SQL Server logins, which are stored in SQL Server. 然后,必须在运行时提供 SQL Server 用户名和密码。You then have to supply the SQL Server user name and password at run time.

重要

SQL Server 安装有名为 sa(“系统管理员”的首字母缩写)的 SQL Server 登录名。SQL Server installs with a SQL Server login named sa (an abbreviation of "system administrator"). 请向 sa 登录名分配强密码,并且不在应用程序中使用 sa 登录名。Assign a strong password to the sa login and do not use the sa login in your application. sa 登录名映射到 sysadmin 固定服务器角色,此角色在整个服务器上具有不可撤销的管理凭据。The sa login maps to the sysadmin fixed server role, which has irrevocable administrative credentials on the whole server. 如果攻击者以系统管理员身份获得访问权限,潜在损害是无限的。There are no limits to the potential damage if an attacker gains access as a system administrator. 默认情况下,Windows BUILTIN\Administrators 组(本地管理员组)的所有成员均为 sysadmin 角色的成员,但可以从该角色中移除这些成员。All members of the Windows BUILTIN\Administrators group (the local administrator's group) are members of the sysadmin role by default, but can be removed from that role.

SQL Server 提供 SQL Server 登录名的 Windows 密码策略机制。SQL Server provides Windows password policy mechanisms for SQL Server logins. 密码复杂性策略通过增加可能密码的数量来阻止强力攻击。Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. SQL Server 可以将相同的复杂性和过期策略应用于 SQL Server 内使用的密码。SQL Server can apply the same complexity and expiration policies to passwords used inside SQL Server.

重要

连接用户输入中的连接字符串可能会让你易受到连接字符串注入攻击。Concatenating connection strings from user input can leave you vulnerable to a connection string injection attack. 请使用 SqlConnectionStringBuilder 在运行时创建语法有效的连接字符串。Use the SqlConnectionStringBuilder to create syntactically valid connection strings at run time. 有关详细信息,请参阅连接字符串生成器For more information, see Connection String Builders.

外部资源External Resources

有关详细信息,请参阅以下资源。For more information, see the following resources.

资源Resource 说明Description
主体Principals 描述 SQL Server 中的登录名和其他安全主体。Describes logins and other security principals in SQL Server.

请参阅See also