3

I have SQL Server 2014 on my Windows Server 2012 R2. I can connect locally to SQL Server from Management Studio using Windows Authentication.

I decided to switch to SQL Server Authentication (because I need access from other computer). I created new Login using Management Studio, but get error: Login failed when trying to connect.

Then I changed password for sa and tried login as sa, but again get the same error:

enter image description here

I restarted SQL Server, but it didn't help. What I do wrong?

From error log: Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

0

2 Answers 2

10

Connect to SQL Server using Windows authentication. Run sp_readerrorlog and see the complete message which would have come in errorlog when you tried connecting with SQL Authentication. This message is not of much help. You can update the question with message

From error log: Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

What is output of this query.

select serverproperty('IsIntegratedSecurityOnly')

It seems like authentication mode is Windows only. If above query returns 1. It means authentication mode is windows go to server . Right click on SQL Server select property and then click on security. Change authentication it to mixed mode by selecting radio button SQL Server and windows authentication mode and restart SQL Server service again.

0
  1. Right-click on the instance
  2. Click on properties
  3. Click on Security
  4. Click "Sql Server and Windows Authentication mode"
  5. Now click on the Security Tab Under the Database and expand on logins
  6. right-click on the sa and go to properties
  7. provide the password
  8. Endable Enforce Password Policy
  9. Click on status and click on login enabled
  10. MOST IMPORTANTLY RESTART THE SERVER NOT ONLY MANAGEMENT STUDIO

Mustrestart the server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.