0 Comments

Yesterday I got a new computer from IT, and they installed most of the essential tools/software for me which is good.  But when I tried to login into SQL Server, I had trouble with the login.  I spent an hour to fix it and I think it’s worth to share.

Background

Firstly, I tried to login with the “Windows Authentication account”,but failed shown me the error as below.  then I tried using “sa” account same error.

image

 

Troubleshooting

I think it might be because of the wrong password, then I emailed to IT to confirm the “sa” password. They sent me password but still the same password.  Just making sure it’s not a probably with the password, I found a tool (http://www.top-password.com/guide/reset-sql-server-password.html) to reset “sa” password.  SQL Server Password Changer is super easy to use.  All you need to do is only 3 steps really:

  • Step 1: Stop service via Sql Server Configuration Manager

image

  • Step 2:Open master.mdf filer under “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA” Folder, and then change password

image

  • Step 3:Restart the service that we turned off in Step 1.

Unfortunately, after resetting the password, it was still the same error.  I was convinced that it’s not caused by wrong password.  Then I think it can be because of that the mix mode hasn’t been enabled. At the end,  I found a solution to enable the max mode without login to SQL.  Thanks to stackoverflow for this solution. 

Use 2 instead of 1 for mixed authentication. You'll have to restart SQL Server after changing this setting. You can't do that from T-SQL. From the command prompt, it's something like net stop mssqlserver then net start mssqlserver.

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1

 

After enabling the mixed mode,  I can login with “SQL Server Authentication account” successfully, although I still don't know why the “windows account” doesn’t work.  But at least i can continue my work.

Post comment