none
'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



    • Changed type SQL2008SETUP Thursday, February 23, 2012 3:46 PM
    • Edited by SQL2008SETUP Thursday, February 23, 2012 3:49 PM
    Thursday, February 23, 2012 3:24 PM

Answers

  • 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

    • Marked as answer by SQL2008SETUP Friday, February 24, 2012 9:11 AM
    Friday, February 24, 2012 9:10 AM

All replies

  • 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 4:45 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.

    Thursday, February 23, 2012 8:03 PM
  • Thanks Rick,

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

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

    Friday, February 24, 2012 8:48 AM
  • 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

    • Marked as answer by SQL2008SETUP Friday, February 24, 2012 9:11 AM
    Friday, February 24, 2012 9:10 AM