在 SQL Server 2012 中更改用户的密码 [英] Changing User's Password In SQL Server 2012

问题描述

我安装了 Microsoft SQL Server 2012.我打开 Microsoft SQL Server 管理并使用 Windows 身份验证登录.我不知道sa"的默认密码.所以，我需要改变它.我按照这个问题的答案中的步骤进行操作:

2. 如您所见，密码长度为 15 个字符.

3.我将其更改为1234".(密码的长度逻辑上将等于 4 个字符)然后选择确定"

4. 我尝试使用 SQL Server 身份验证登录但是我无法登录系统.

你对这个奇怪的问题有什么想法吗?

一些不成功的尝试

• 我尝试过其他带有复杂字符的密码，例如@#FGHbnm1234567890"
• 我通过此链接更改了身份验证模式 看来我的认证方式不对:
  <块引用>

  用户sa"登录失败.原因:尝试使用 SQL 登录身份验证失败.服务器配置为 Windows 身份验证只要.[客户:]

  但我之前更改了身份验证模式:

  <块引用>

  SQL Server 和 windows 身份验证模式

  更新 3
  终于，我的问题解决了！我的问题的原因是错误地更改身份验证模式.Larnu 的回答是一个很好的解决方案.另外，我建议使用此链接作为分步指南:如何启用 SQL Server 身份验证

  解决方案

  这里真正的问题是您的服务器仅在 Windows 身份验证中配置.如果实例处于仅 Windows 身份验证模式，则您无法连接到使用 SQL 身份验证的实例，因为(显而易见)SQL 身份验证不是 Windows 身份验证.

  您需要更改服务器的认证方式:

  <块引用>

  启用 sa 登录

  您可以使用 SSMS 或 T-SQL 启用 sa 登录.

  使用 SSMS

  1. 在对象资源管理器中，展开安全性，展开登录，右键单击 sa，然后单击属性.
  2. 在常规"页面上，您可能需要为 sa 登录名创建并确认密码.
  3. 在状态"页面的登录"部分，单击已启用"，然后单击确定".

  使用 Transact-SQL

  以下示例启用 sa 登录并设置新密码.在运行之前替换为强密码.

  ALTER LOGIN sa ENABLE ;走ALTER LOGIN sa WITH PASSWORD = '';走

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

  以下示例将服务器身份验证从混合模式(Windows + SQL)更改为仅限 Windows.

  <块引用>

  注意

  以下示例使用扩展存储过程来修改服务器注册表.如果注册表修改不当，可能会出现严重问题.这些问题可能需要您重新安装操作系统.微软不能 >保证这些问题都能得到解决.自行承担修改注册表的风险.

  使用 [master]走EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'登录模式'，REG_DWORD，1走

  对于混合认证，最后一个参数是2，而不是1.

  I installed Microsoft SQL server 2012. I open Microsoft SQL server management and login with Windows Authentication. I don't know the default password of "sa". So, I need to change it. I follow steps from the answer to this question: Unable to change the password of "sa" in SQL Server 2008 but the password doesn't change!

  For better sense see these screenshots in order:
  1. I select logins. After, I select "sa". Then, select "Properties".

  
  2. As you can see, the length of the password is 15 chars.
  
  3. I change it to "1234". (the length of the password logically will equal to 4 chars) And then select "OK"
  4. I tried login with SQL Server Authentication But I cannot login to the system.

  
  Do you have any idea about this strange problem?
  

  Some Unsuccessful Tries

  • I have tried other passwords with complex char likes "@#FGHbnm1234567890"
  • I changed the mode of authentication with this link Options to Change SQL Server to Mixed Mode Authentication
  • I have reinstalled the SQL server.
  • I have created a new user. But the password of the new user isn't changeable likes "sa"

  UPDATE
  The first I said length of the password isn't correct. But I have noticed the length of the password isn't real length for the security aspect. I edit step 4 with a better reason for my claim

  UPDATE 2
  I open logs: It seems my authentication mode is wrong:

  Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. The server is configured for Windows authentication only. [CLIENT: ]

  But I change authentication mode before:

  SQL Server and windows Authentication mode

  UPDATE 3
  Finally, my problem was solved! The reason for my problem is wrong changing authentication mode. The answer of Larnu is a good solution. Also, I suggest this link for step by step guide: How To Enable SQL Server Authentication

  解决方案

  The real problem here is that you have your server configured in Windows authentication only. You cannot connect to an instance using SQL Authentication if it's in Windows Authentication only mode, because (to state the obvious) SQL Authentication is not Windows Authentication.

  You need to change the authentication mode of the server:

  Enable sa login

  You can enable the sa login with SSMS or T-SQL.

  Use SSMS

  1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
  2. 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.

  Using Transact-SQL

  The following example enables the sa login and sets a new password. Replace with a strong password before you run it.

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

  Change authentication mode (T-SQL)

  The following example changes Server Authentication from mixed mode (Windows + SQL) to Windows only.

  Caution

  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 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
  

  For mixed authentication, the final parameter would be 2, not 1.

  这篇关于在 SQL Server 2012 中更改用户的密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！