Thursday, February 23, 2012 3:24 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:45 PM
Troubleshooting that error http://msdn.microsoft.com/en-us/library/cc645917.aspx
In my experience, if you have correctly configured SQL Server Authentication, you might have more than one instance of the Database Engine installed and are trying to connect to the wrong instance. Checking the Database Engine error log can provide more information.
Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
Thursday, February 23, 2012 8:03 PM
This may sound strange, but as test, see if named pipes works e.g. use odbcad32.exe, create and test a connection the SQL instance.
Named pipes will need to be enabled in SQL Server Configuration Manger -> SQL Server Network Configuration.
Friday, February 24, 2012 8:48 AM
I found the solution from a link off the page you sent me.
Friday, February 24, 2012 9:10 AM
I found the answer from this link: http://msdn.microsoft.com/en-us/library/ms188670.asp
For some reason when you change the authentication mode to allow Windows and SQL Server the login 'sa' is automatically disabled.
1.Connect to server via 'Windows Authentication'.
2.Open a new Query and copy and paste the following (making sure you change the password):
ALTER LOGIN sa ENABLE ;
ALTER LOGIN sa WITH PASSWORD = '<enter password here>' ;
3.Execute the query and reconnect to the server using the login sa and password you have just entered.
- Marked As Answer by SQL2008SETUP Friday, February 24, 2012 9:11 AM