SQL Server Login issue
- Posted in:
- SQL Server 2014
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.
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.
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
- Step 2:Open master.mdf filer under “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA” Folder, and then change password
- 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.