Cannot log in using SA with SQL Authentication

Question

I can not log in to SQL Server 2008 using SQL Server authentication.

I can only log in using Windows Authentication Mode.

I chose only defaults while installing SQL Server 2008 R2.

Answer 1

Did you look anywhere? Right-click the server and choose Properties, then select the Security screen.

Then, log in using your Windows auth admin account, and set the password for the sa SQL auth account.

ALTER LOGIN sa WITH PASSWORD = 'strong password here';

Answer 2

Which Authentication Mode?

Your server might have been configured to run in Windows Authentication mode only. This can be checked by opening up the Properties of your SQL Server instance in SQL Server Management Studio and looking at the Security section:

Or you can run the following SQL Server statement after logging in with a Windows Account:

EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode'

If the value returned is LoginMode 2 then SQL Server and Windows Authentication mode has been activated. (See Step 2. for details on how to change this.)

If the value returned is LoginMode 1 then Windows Authentication mode has been activated. (See Step 1. for further details on how to change this setting) Logging in with pure SQL Server logins is not possible.

---

Step 1. - Changing Authenticate Mode

If your instance has been configured to use only Windows Authenticated mode then you will have to turn on SQL Server and Windows Authentication mode and restart your server. Follow these easy steps:

1. Change Authentication Mode

SQL Script

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

SQL Server Management Studio (SSMS)

Change the setting in the security section of the properties window to SQL Server and Windows Authentication mode.

2. Restart SQL Server Instance

SQL Script

(can be achieved via SQL Script in SQLCMD Mode)

-- Connect to your instance
-- (Replace <instancename> with your instance's name
:connect <instancename>

-- Stop your SQL Server instance 

-- (Replace SQLSERVERAGENT with the name of your service)
!! net stop SQLSERVERAGENT
-- (Replace MSSQLServer with the name of your service)
!! net stop MSSQLServer

-- Start your SQL Server instance 

-- (Replace SQLSERVERAGENT with the name of your service)
!! net start MSSQLServer
-- (Replace SQLSERVERAGENT with the name of your service)   
!! net start SQLSERVERAGENT

:connect localhost

SQL Server Management Studio (SSMS)

Right-click on your SQL Server instance and select Restart from the context menu:

3. Verify the setting

SQL Script

Verify that your instance is running in SQL Server and Windows Authentication mode by issuing the following command:

EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode'

You should receive a LoginMode 2.

SQL Server Management Studio (SSMS)

Verify that the radio button is selected for SQL Server and Windows Authentication mode.

If your sa account has been disabled then please also follow Step 2.. Otherwise you are finished.

---

Step 2. Enabling sa Account

In the case of a deactivated sa account, just go ahead and enable the SQL Server account again. This can be achieved with the following script:

USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'<your password>'
GO
ALTER LOGIN [sa] ENABLE
GO

...or via SSMS.

Answer 3

Sometimes login is disabled due to many incorrect passwords or any policy violation. So what we can do is just log in with window authentication, change the password and enable login again.

Log into database with Windows Authentication

Run Query:

ALTER LOGIN [sa] WITH PASSWORD='newpassword', CHECK_POLICY=OFF
GO
ALTER LOGIN [sa] ENABLE
GO

Use of CHECK_POLICY is required as it specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default is ON when we make OFF it will change the password without any difficulties. Later you can ON

NOTE: What I have observed that when CHECK_POLICY is on the GUI SSME solutions for changing password won't work every time. I don't the reason behind but that I have observed.