更改服务器身份验证模式Change server authentication mode

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

本主题介绍如何通过使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中更改服务器身份验证模式。This topic describes how to change the server authentication mode in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 安装过程中, SQL Server 数据库引擎SQL Server Database Engine 设置为 “Windows 身份验证模式”“SQL Server 和 Windows 身份验证模式”During installation, SQL Server 数据库引擎SQL Server Database Engine is set to either Windows Authentication mode or SQL Server and Windows Authentication mode. 安装完成后,您可以随时更改身份验证模式。After installation, you can change the authentication mode at any time.

如果在安装过程中选择了“Windows 身份验证模式”,则 sa 登录名将被禁用,安装程序会分配一个密码。If Windows Authentication mode is selected during installation, the sa login is disabled and a password is assigned by setup. 如果稍后将身份验证模式更改为“SQL Server 和 Windows 身份验证模式”,则 sa 登录名仍处于禁用状态。If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. 若要使用 sa 登录名,请使用 ALTER LOGIN 语句启用 sa 登录名并分配一个新密码。To use the sa login, use the ALTER LOGIN statement to enable the sa login and assign a new password. sa 登录名只能使用 SQL ServerSQL Server 身份验证连接到服务器。The sa login can only connect to the server by using SQL ServerSQL Server Authentication.

开始之前Before you begin

sa 帐户是一个广为人知的 SQL ServerSQL Server 帐户,并且经常成为恶意用户的攻击目标。The sa account is a well known SQL ServerSQL Server account and it is often targeted by malicious users. 除非您的应用程序需要使用 sa 帐户,否则请不要启用它。Do not enable the sa account unless your application requires it. 因此,为 sa 登录名使用强密码非常重要。It is important that you use a strong password for the sa login.

使用 SSMS 更改身份验证模式Change authentication mode with SSMS

  1. SQL Server Management StudioSQL Server Management Studio 对象资源管理器中,右键单击服务器,再单击“属性”。In SQL Server Management StudioSQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

  2. “安全性” 页上的 “服务器身份验证” 下,选择新的服务器身份验证模式,再单击 “确定”On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

  3. SQL Server Management StudioSQL Server Management Studio 对话框中,单击 “确定” 以确认需要重新启动 SQL ServerSQL ServerIn the SQL Server Management StudioSQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL ServerSQL Server.

  4. 在“对象资源管理器”中,右键单击服务器,并单击“重新启动”。In Object Explorer, right-click your server, and then click Restart. 如果运行有 SQL ServerSQL Server 代理,则也必须重新启动该代理。If SQL ServerSQL Server Agent is running, it must also be restarted.

启用 sa 登录Enable sa login

可使用 SSMS 或 T-SQL 启用 sa 登录。You can enable the sa login with SSMS or T-SQL.

使用 SSMSUse SSMS

  1. 在对象资源管理器中,依次展开“安全性”、“登录名”,右键单击“sa”,再单击“属性”。In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.

  2. 在“常规”页上,你可能需要为 sa 登录名创建密码并确认该密码 。On the General page, you might have to create and confirm a password for the sa login.

  3. “状态” 页上的 “登录” 部分,单击 “启用” ,然后单击 “确定”On the Status page, in the Login section, click Enabled, and then click OK.

“使用 Transact-SQL”Using Transact-SQL

下面的示例启用 sa 登录名并设置一个新密码。The following example enables the sa login and sets a new password. 在运行之前将 <enterStrongPasswordHere> 替换为强密码。Replace <enterStrongPasswordHere> with a strong password before you run it.

ALTER LOGIN sa ENABLE ;  
GO  
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;  
GO  

更改身份验证模式 (T-SQL)Change authentication mode (T-SQL)

以下示例将服务器身份验证从混合模式 (Windows + SQL) 更改为仅 Windows。The following example changes Server Authentication from mixed mode (Windows + SQL) to Windows only.

注意

下面的示例使用扩展存储过程来修改服务器注册表。The following example uses an extended stored procedure to modify the server registry. 如果没有正确修改注册表,可能会出现严重问题。Serious problems might occur if you modify the registry incorrectly. 这些问题可能需要你重新安装操作系统。These problems might require you to reinstall the operating system. Microsoft 无法保证可以解决这些问题。Microsoft cannot guarantee that these problems can be resolved. 修改注册表的风险自负。Modify the registry at your own risk.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'LoginMode', REG_DWORD, 1
GO

备注

更改身份验证模式所需的权限为 sysadminControl ServerThe permissions required to change the authentication mode are sysadmin or Control Server

另请参阅See also