SQL Server Security: Server authentication mode

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Published:
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
3,561 Views
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT

Comments (1)

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Author

Commented:
Submitted

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.