9

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.

13

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

enter image description here

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';
| improve this answer | |
  • thanks mate i choose this option and when i did to log in sql authentication mode.i enter sa in login and leave the password blank as i did not provide any password during installation – khan May 6 '14 at 1:18
  • You need to set the password for the sa account when logged in using Windows authentication. There is no such thing as a blank sa password in SQL Server anymore. – Aaron Bertrand May 6 '14 at 1:26
3

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:

SSMS Security Properties

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.

SSMS Security Properties - 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:

SSMS Instance Restart via 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.

SSMS Security Properties - 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.

| improve this answer | |
-1

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.

| improve this answer | |
  • Good Jay. Now, please edit your answer (on the left, opposite your picture) and include this additional information. Then you can delete your comment (hover over the comment, a cross in a circle appears), as I will delete mine. – Michael Green May 9 '18 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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