Authentication Modes in Microsoft SQLServer

Microsoft SQLServer has two authentication modes
1. SQLServer authentication – The logins are created in SQLServer which are not available at Windows level.
2. Windows authentication – The windows user accounts validated by SQLServer at the time of log using Windows Active Directory.

The sa account connects by using SQL Server Authentication. For SQLServer accounts, three option password policies available.
• User must change password at next login
• Enforce password expiration
• Enforce password policy

Users will receive below errors, if only Windows authentication mode enabled.

2012-03-09 15:41:06.44 Logon Login failed for user 'App_user'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 172.20.251.53]

To change the authentication mode follow the below steps.
1. Launch SQLServer Management Studio, right click on Server and select properties
2. Go to Security page, Under “Server Authentication” select the authentication mode
3. Restart the SQLServer

To enable the SA Login execute the following command.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'Passowrd';
GO

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com