'Server is configured for windows authentication only.' Error:18456 Severity:14 State:58

Question

Hi all,

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,

Ben

Answer 1

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

Answer 2

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.

Answer 3

Thanks Rick,

I found the solution from a link off the page you sent me.

http://msdn.microsoft.com/en-us/library/ms188670.aspx

Answer 4

Thanks all,

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 ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enter password here>' ;
GO

3.Execute the query and reconnect to the server using the login sa and password you have just entered.

Regards,

Ben