I'm getting error 18456 from SQL Server Management Studio when I try to connect Windows Authentication or SQL Server Authentication as in the pictures below.
Windows Authentication
SQL Server Authentication
I want to implement the solution in this link: Login to Microsoft SQL Server Error: 18456 but I can't start the engine in no way. Also, I don't know my password for SQL Server Authentication.
What can I do?
I have seen this problem very often with SQL Server 2012 and later versions (usually with new installations) the reason is SQL Server 2012 and later version do not give window Administrators automatically the admin rights in SQL Server.
This will leave you with no access to the SQL Server at all, in this case I use the following approach:
Run SQL Server configuration Manager.
Location for SQL Server configuration Manager:
SQL Server 2019 C:\Windows\SysWOW64\SQLServerManager15.msc SQL Server 2017 C:\Windows\SysWOW64\SQLServerManager14.msc SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc SQL Server 2014 C:\Windows\SysWOW64\SQLServerManager12.msc SQL Server 2012 C:\Windows\SysWOW64\SQLServerManager11.msc SQL Server 2008 C:\Windows\SysWOW64\SQLServerManager10.msc
Right-Click the SQL Server Service and go to Properties.Startup Parameters tab and add -m parameter there. This will cause the SQL-Server to run in Single-User mode.Restart the SQL Server service, make sure the SQL Server agent doesn't start, if it does start it may obtain the single available connection to the SQL Server.Run as Administrator.SysAdmin role.-m from the startup parameters. Restart the SQL Server service and tadaaa.Also do not do this on a server that is being used by others.
I got the same error:
When logging in with the default user "sa" and the wrong password.
When logging in with the default user "sa" without restarting MSSQL(SQL Server) after changing "Server authentication" from "Windows Authentication mode" to "SQL Server and Windows Authentication mode".
The solution for "1" is changing the password for "sa" whether or not you forgot the password or don't know the default password for "sa". *You can log in with "sa" and Empty password if you keep "Password:" and "Confirm password:" blank, then apply:
The solution for "2" is when changing "Server authentication" from "Windows Authentication mode" to "SQL Server and Windows Authentication mode":
You will get this message below. So, as the message says, "Server authentication" is still "Windows Authentication mode" until restarting MSSQL with SQL Server Configuration Manager(SSCM):
Some of your configuration changes will not take effect until SQL Server is restarted.
So, you need to restart MSSQL with SQL Server Configuration Manager(SSCM) as shown below. This is How to find SQL Server Configuration Manager in your Windows Machine:
If you use these 2 solutions above, you will log in with the default user "sa" and the password.
One option is to install a new instance of SQL Server and attach any databases to the new instance, but this can be tricky for a variety of reasons, especially if you do not have a recent backup of the master database.
Therefore, I'd suggest following the advice given by Microsoft here and start the instance in single-user mode, which will then allow any local administrator on the machine where the instance is installed to gain access and correct the authentication issues (resetting passwords etc.).
Of course, this presumes that you have access to a administrator account on the machine, which hopefully you do.
Similar issue I faced and the solution mentioned on this post worked for me : https://stackoverflow.com/a/23395581/4409488
What it basically says is that you have to change default login mode which is set to Windows authentication only to SQL Server and Windows Authentication mode.
Right click on the server => SelectProperties => Security page => Server authentication => Select SQL Server and Windows Authentication mode radio button
Restart the SQL server from Services. And you should be good to go.
Regarding a specific case of
Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ##.##.##.##]
and its companion message
Error: 18456, Severity: 14, State: 8.
with respect to SQL Server 2014, Windows Server 2019, and SOLIDWORKS professional document management.
Summary
Test a different and less complex password.
Detail
My first successfully functioning credential contained 14 characters: letters upper & lower case, numbers, and symbols from this set "$!%^(){}[];:<>?". This enabled a SOLIDWORKS professional document management client stack to communicate with a remote SQL Server Express 2014 stack via the SA database account without the above error.
Searching on "SQL Server 2014 password restrictions" led to this helpful post https://social.msdn.microsoft.com/Forums/sqlserver/en-US/52eda46d-5814-45a0-b676-4676f0853474/invalid-password-symbols-for-sql-server-2014-login?forum=sqlsecurity
These links in the accepted answer--while not definitive for SQL Server 2014 nor for Windows Server 2019 credentials--did jog me out of my specific rut:
reduce complexity: try a 14 character instead of 48 character password; apply a more constrained symbol set
http://technet.microsoft.com/en-us/library/cc786468%28v=ws.10%29.aspx http://technet.microsoft.com/en-us/library/cc756109(v=ws.10).aspx http://msdn.microsoft.com/en-us/library/ms722458(v=vs.85).aspx
TBD - replace above links with definitive links for
Server-side and client-side authorization stacks may not adhere to the same specifications and limitations. Detailed knowledge of both may be required to achieve functionality first, then some desired level of security.
May your enlightenment be swifter than mine, with fewer bruises.
Check the sql server log file first to know the state number. Then, find base on that state number. As for me, it's faster to find out after I know state number. I started this useful link https://sqlblog.org/2020/07/28/troubleshooting-error-18456
Above marked answer looks good but also for Troubleshooting Make sure username, password are correct. Try running SSMS as administrator and logging in. If using AD username with windows authentication then make sure account is not locked out.
If recently changed AD username password then try log off and log back in.
Please refer full article on sqlserver-dba.co.uk
