0

SQL server 2012. Windows 10 Professional.

I enabled "sa" user account, and the authentication mode is set to "windows authentication and SQL server authentication". I set the password for "sa user account". but I cannot login into it. Error: 18456. enter image description here

I can successfully login into windows authentication.

"sa" system administrator account status enabled. enter image description here

I found many duplicate questions, but none solved the issue.

squillman
  • 13,363
  • 3
  • 41
  • 60
AverageJoe
  • 13
  • 1
  • 5
  • Do you have multiple instances installed? Are you sure you're connecting to the right instance? – squillman Sep 21 '17 at 12:57
  • The only instance ID I know while installing the sql server was "MSSQLSERVER", I added my current administrator desktop accounts for authorizations. Am I connected to the right instance? Here is what I found in the error log file: Error: 18456, Severity: 14, State: 58. Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]. I have the sa account set to both windows and sql server authentication mode. – AverageJoe Sep 21 '17 at 13:16

1 Answers1

2

You need to configure the authentication mode of your SQL-Instance to "mixed" (SQL Server and Windows Authentication Mode.).

If you just changed it you need to restart the SQL-Server service.

Here's the MS-documentation and here an SO-answer.

MatSnow
  • 7,357
  • 3
  • 19
  • 31
  • its set to "sql server and windows authentication mode". – AverageJoe Sep 21 '17 at 13:21
  • @AverageJoe The error-message is telling something different. If you just changed it you need to restart the SQL-Server service. – MatSnow Sep 21 '17 at 13:22
  • thanks, I had it set to mix mode, like hours ago. but didn't know nothing about the restarting the service! it worked. – AverageJoe Sep 21 '17 at 13:28
  • Glad it works. When u change it and click on ok, a message pops up saying that you need to restart service ;-) – MatSnow Sep 21 '17 at 13:32
  • it did restarted, but now I did from the services, it worked properly. didn't work before. should I check this answer correct? – AverageJoe Sep 21 '17 at 13:43
  • The service restart should work from SSMS too. I guess there was a additional problem when you tested the first time. If you think my answer is correct, then please. :-) – MatSnow Sep 21 '17 at 13:48