4

I can access my databases through SQL Server Management Studio (SSMS) by using my Windows account no problem.

I have two issues though:

  1. I want to enable sa and then try to log in using sa and the password. I get this error:

    Enter image description here

    I enabled the SQL Server authentication:

    Enter image description here

    Enter image description here

    Enter image description here

    Enter image description here

  2. I tried to create a new user, but that user cannot connect either:

    Enter image description here

How can I fix this?

Improve this question
0

3 Answers 3

Reset to default
15

In SSMS, right-click on the server and go to Properties and under the security page, check what Server authentication looks like. Odds are it's set to Windows Authentication only and needs to change if you want to use SQL Server users: enter image description here

Improve this answer
0
3

Alternative ways to change the Server authentication mode

From Windows registry:

Step 1: Open the registry editor.

Step 2: Navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.10.SQLEXPRESS\MSSQLServer

Step 3: Change the value of LoginMode from 1 to 2.

From query:

Just run

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

Note: changing the SQL Server authentication mode requires the service to be restarted to take effect.

Source:
4 Ways to Enable Mixed Mode Authentication for SQL Server
Change authentication mode with SSMS

Improve this answer
1

Right-click on the server name in SSMS and go to properties and under the security page.

Change the authentication from windows authentication mode to sql server and windows authentication mode .

Don't forget to restart the SQL Server Instance, as it will restart the windows services. enter image description here

Improve this answer
2
  • Welcome to DBA SE. It seems your answer does not add any new info other than the correct answer already provides.
    – Ronaldo
    Dec 22, 2021 at 17:09
  • 1
    Thanks Ronaldo, I have faced similar issue but did not work until I restarted the connection.
    – Aman singh Parihar
    Dec 23, 2021 at 6:03

Your Answer

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

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