<

Go Premium for a chance to win a PS4. Enter to Win

x

SQL Server Security: Server authentication mode

Published on
10,102 Points
2,001 Views
6 Endorsements
Last Modified:
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties screen, Security tab:
SQLAuthenticationMode.PNGBy default Windows Authentication mode is selected since is the one recommended by Microsoft and also the one that is more secure because it uses Windows Security tokens so no passwords will be passed through the network when connecting to the SQL Server instance. It is also the one that is more easy to use since the user doesn't need to remember one more login name and password and it is more easy for maintenance since it will be managed by the Active Directory (AD) so it will respect the user AD policies.
The creation of a Windows authentication login is as simple as providing the domain and user name as the Login name:
DomainLogin.PNGThe same can me performed using the following T-SQL command:
CREATE LOGIN [MyDomain\DomainUserName] FROM WINDOWS

Open in new window

In Windows authentication mode the sa login is disabled so ensure that someone else has the sysadmin role.


SQL Server logins are managed inside the SQL Server engine. The passwords are stored in the SQL Server master database and when connecting to the SQL Server instance the user credential is passing through the network. So, when creating a SQL Server authentication login a login name and password need to be provided:
MSSQLLogin.PNGThe same can me performed using the following T-SQL command:

CREATE LOGIN [SQLLoginName] WITH PASSWORD=N'Pwd' MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON

Open in new window

By default a SQL Server Login will use the local password policy. If you do not want to enforce a password policy then you just need to clear the "Enforce password policy" checkbox or use the following T-SQL command:

CREATE LOGIN [SQLLoginName] WITH PASSWORD=N'Pwd' CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Open in new window

NOTES:

  1. I encourage you to enforce an account policy to guarantee strong passwords and also set a policy to enforce the password to be changed regularly. It is also a good policy to lock the account if some attempts with wrong password happens since it can be someone trying to hack an account.
  2. When enforcing a policy remember that previous logins does not have the new policy enforced so inform those users that they need to change their passwords. You can set the "User must change password at next login" to all those users so it will assure that they will change their passwords next time they log in.

The Windows authentication mode respects the Domain Password Policy so everything will be managed by the AD administrators.
The SQL Server authentication mode respects the Local Password Policy. For verify or change the local policies open the Local Security Policy utility in the Administration Tools and go to Account Policies where the Password and Account Lockout policies can be found:
LocalPasswordPolicy.PNGWindows authentication logins can not be disable in SQL Server as also their passwords can not be changed in SQL Server. Those are tasks that need to be performed in the Active Directory by the respective administrator.
On the other way, SQL Server authentication logins are managed in SQL Server and enabling/disabling logins and changing their passwords are tasks that need to be done with someone with administrator role. Follow are respectively the T-SQL command to enable, disable and change a SQL Server login:
ALTER LOGIN [SQLLoginName] ENABLE;
ALTER LOGIN [SQLLoginName] DISABLE;
ALTER LOGIN [SQLLoginName] WITH PASSWORD = 'New Password';

Open in new window

NOTES:

  1. All this can be performed in SQL Server Management Studio in the Login Properties screen.
  2. Only an user with sysadmin or securityadmin roles can change other users passwords. An user without those permissions can change only his own password but then he needs to provide the old password as well:
ALTER LOGIN [SQLLoginName]
WITH PASSWORD = 'New Password'
OLD_PASSWORD = 'Old Password';

Open in new window

If the password policy is enforced and the new password does not match the policy then the following error is returned:

Msg 15116, Level 16, State 1, Line 2

Password validation failed. The password does not meet Windows policy requirements because it is too short.

As you can see, using a SQL Server authentication mode let a lot of tasks to be managed by a Database Administrator (DBA) so whenever possible use Windows authentication mode and let these tasks to be performed by AD administrators as it is more secure and give the DBAs time to perform other important tasks.
But if is not possible and you will need to use SQL Server authentication then do not forget the Security part: Enforce strong account policies.
6
Comment
1 Comment
 
LVL 52

Author Comment

by:Vitor Montalvão
Submitted
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Join & Write a Comment

Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month