Thursday, February 23, 2012 3:52 PM
After the struggle of installing SQL I am now having problems conecting to the server.
I can connect to the server through 'Windows Authentication' (as you would expect looking at the error message) but I cannot connect to the sever using 'SQL Server Authentication'.
I right clicked on the server name in 'Object Explorer' and made sure that 'SQL Server and Windows Authentication mode' was selected in properties. I restarted SQL and I'm still having the same error message appear:
'An attempt to login using SQL authentication failed. Server is configured for windows authentication only.'
Error:18456. Severity:14. State:58
I've had a look around and can't find any other solutions.
Thank you for your time,
Thursday, February 23, 2012 4:25 PMAnswerer
The error as you already pointed out is self explanatory. State 58 does mean that the instance is not setup to allow SQL Auth logins.
Are you sure that this is the only instance on the server? Is the application pointing to the correct instance?
Again, as already pointed out you'd need to change the security to mixed mode and then restart the services. Could you check the registry and see what the value of the following key is?
HKEY_Local_Machine\Software\Microsoft\Microsoft SQL Server\MSSQL.Instance\MSSQLSERVER
Friday, February 24, 2012 9:30 AM
I do not have a folder called 'MSSQL.Instance' in the registry so i assume there is a difference between our OS's.
I found out the solution now:
When you change the Authentication mode, the user 'sa' automatically sets to disabled for some reason.
I just copied the following into a new query, ran it and now it's working.
ALTER LOGIN sa ENABLE ; GO ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ; GO
Thanks for your time, much appreciated
- Marked As Answer by SQL2008SETUP Friday, February 24, 2012 9:30 AM