<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

SQL Server Security: Server authentication mode

Published on
9,036 Points
1,936 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 52

Author Comment

by:Vitor Montalvão
Submitted
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Join & Write a Comment

This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month