sql server authentication login failed

Question

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.

Answer 1

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.

Answer 2

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