Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Yukon Passwords

By Randy Dyess,

Among the changes brought to you by the next version of SQL Server is the ability to tie SQL Server login passwords into the Windows 2003 local policy for passwords. This ability allows SQL Server login passwords to enjoy the same complexity and expiration policies long associated with Windows login passwords.

Not to take anything away from this nice step forward by Microsoft, there are a few limitations when dealing with the new password policies. One of the limitations is that the policy enforcement can only be initiated with SQL Server installations running on Windows 2003 servers. Windows 2000 installations will ignore some of the new changes, but a weak SQL Server only policy similar to the MBSA checks will be performed. Another limitation is the granularity of the policy choices. You must either turn all three choices on, only turn on the expiration policy or only turn on the account lockout and password complexity policy. It would have been nice to be able to only enforce password complexity without having to accept the potential problems associated with account lockouts. Another limitation is the password policies must be the same as the local Windows password policy. This is a nice combination as it allows similar policies but it would also be nice to be able to set up a separate policy structure for your SQL Server application.

Password Complexity

If you set the CHECK_POLICY property of the login to ON, which it is by default, then you will have the ability to control the complexity of your SQL Server login passwords during their creation and during any password reset.

The default behavior of Windows is to have the password complexity filter, Passflt.dll, inactive by default. You can activate this filter and all passwords will be required to have at least 8 characters and be created with characters from three of the four following lists:

  • English Uppercase characters
  • English Lowercase characters
  • Numeric characters
  • Special characters

You can alter this behavior with a custom password filter, but this filter will require advance C++ and Windows API skills.

YUKON installations running on Windows 2000 installations will not utilize the Windows Security API to enforce password complexity but will use policies similar to that of the Microsoft Baseline Security Analyzer and only check for the following conditions:

  • Blank or Null passwords
  • Passwords same as the user name
  • Passwords same as the machine name
  • Passwords of "Password", "Admin", or "Administrator"

Account lockout

If you set the CHECK_POLICY property of the login to ON, which it is by default, then you will have the ability to control whether your login will be locked-out when X login attempts fail. This policy is the same as when you forget your Windows account password and only have X number of times to guess before your account is locked-out for a set number of minutes or until it is reset by an administrator. This feature was included for SQL Server logins to prevent those brute-force password attacks that are common with SQL Server installations. While this is a great idea for some of your SQL Server logins, you may want to turn this feature off for your main application logins or you will find yourself the easy victim of DOS attacks that leave all your application users sitting there as the application continually fails because of the login lockout. This feature will need to be tested over time to see if it is worth utilizing or not. My feeling is that it would be great for human logins but not for application logins.

This policy is only available for YUKON installations running on Windows 2003 and above installations.

Password Expiration

If you set the CHECK_EXPIRATION property of the login to ON, which it is by default, then you will have the ability to control whether your SQL Server login passwords expire or not. Setting this property to OFF will have the same effect as using the "Password Never Expires" option available for Windows login. Like Windows logins, when the password is about to expire the users are given a warning to change them. This policy is another useful policy to use for your human logins but not for your non-human logins

This policy is only available for YUKON installations running on Windows 2003 and above installations.

Password Enforcement

Authentication:
Checks performed during authentication include both the check for account lockout and for password age restrictions.

Password Set/Change:
Checks performed during password creation or changes include both checks for password complexity and for password history rules. These checks allow for the same rules for the complexity of the password and to make sure the same set of passwords are not used over and over.

Client Side Support:
Another nice change is the ability to force users and allow users to change their SQL Server login passwords during logging in. This ability will give administrators some relief as they no longer have to let users change their passwords with the sp_password system stored procedure nor do they have to change the password themselves if the users do not have the ability to execute the sp_password system stored procedure.

Transact-SQL Statements

CREATE LOGIN
Creates a new Microsoft Windows or SQL Server login account. Permissions default to members of the sysadmin and securityadmin fixed server roles and to logins with ALTER ANY LOGIN permission.

Syntax
CREATE LOGIN login_name
{ WITH option_list1 | FROM WINDOWS [ WITH option_list2 [,...] ] }

option_list1 ::=
PASSWORD password [ HASHED ] [ MUST CHANGE ] [ , option_list3 [,...] ]

option_list2 ::=
DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language

option_list3 ::=
| SID = sid
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| CHECK_EXPIRATION = { ON | OFF }
| CHECK_POLICY = { ON | OFF }

Arguments
login_name Specifies the name of the SQL Server or Windows login that is being created.

PASSWORD password Specifies the password for the login that is being created.

HASHED Applies to SQL Server logins only. Specifies that the password is already hashed.

MUST_CHANGE Applies to SQL Server logins only. If this option is included, SQL Server will prompt for an updated password the first time the new login is used.

SID = sid Applies to SQL Server logins only. Specifies the GUID of the new SQL login. If this option is not included, SQL Server will automatically assign a GUID.

DEFAULT_DATABASE = database Specifies the default database to be assigned to the login. If this option is not included, the default database will be set to MASTER.

DEFAULT_LANGUAGE = language Specifies the default language to be assigned to the login. If this option is not included, the default language will be set to the current default language of the server. If the default language of the server is changed in the future, the default language of the login will remain unchanged.

CHECK_EXPIRATION Applies to SQL Server logins only. Specifies that password expiration policy should be enforced on this login. The default value is ON.

CHECK_POLICY Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

Remarks
Pre-hashing of passwords is supported only when creating SQL Server logins

ALTER LOGIN
Changes the properties of a Microsoft Windows or SQL Server login account.

Syntax
ALTER LOGIN login_name WITH set_option [,...]
set_option::=
PASSWORD = password [OLD PASSWORD = oldpassword
| secadmin_pwd_option [secadmin_pwd_option] ]
| SID =sid
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| NAME = login_name
| CHECK_POLICY = {ON|OFF}
| CHECK_EXPIRATION = {ON|OFF}

secadmin_pwd_opt := MUST CHANGE | UNLOCK

Arguments
login_name Specifies the name of the SQL Server or Windows login that is being created.

PASSWORD password Specifies the password for the login that is being created.

HASHED Applies to SQL Server logins only. Specifies that the password is already hashed.

MUST_CHANGE Applies to SQL Server logins only. If this option is included, SQL Server will prompt for an updated password the first time the new login is used.

SID = sid Applies to SQL Server logins only. Specifies the GUID of the new SQL login. If this option is not included, SQL Server will automatically assign a GUID.

DEFAULT_DATABASE = database Specifies the default database to be assigned to the login. If this option is not included, the default database will be set to MASTER.

DEFAULT_LANGUAGE = language Specifies the default language to be assigned to the login. If this option is not included, the default language will be set to the current default language of the server. If the default language of the server is changed in the future, the default language of the login will remain unchanged.

CHECK_EXPIRATION Applies to SQL Server logins only. Specifies that password expiration policy should be enforced on this login. The default value is ON.

CHECK_POLICY Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

UNLOCK Specifies that a locked login should be unlocked.

LOGINPROPERTY

Syntax
LoginProperty ('login-name','property')

Arguments
login-name The name of the SQL Server login.
'property The property to be queried: .

    IsLocked If the login is currently locked out. .
    IsExpired If the login is has expired. .
    IsMustChange Checks to see if the login needs to change it's password on the next login.

sys.sql_logins

This catalog view contains a row for each security principal that is a SQL login (of type S).

name SYSNAME NOT NULL Name of principal, unique within the database.
principal_id INTEGER NOT NULL ID of principal, unique within a database.
sid VARBINARY(85) NULL SID (Security Identifier) if the principal is defined external to the database (type S, U, and G), else NULL
type CHAR(1) NOT NULL Principal Type, one of:
    S = SQL User
    U = Windows User
    G = Windows Group
    A = Application Role
    R = Database Role
create_date DATETIME NOT NULL Login create date
modify_date DATETIME NOT NULL Login modify date
default_database_name SYSNAME NULL Name of the login's default database.
default_lanugage_name SYSNAME NULL Name of the login's default language.
is_secure_access_required BIT NULL If 1, then session encryption is required.
is_basic_auth_disabled BIT NULL If 1, then basic authentication is disabled.
is_ntlm_auth_disabled BIT NULL If 1, then NTLM authentication is disabled.
is_kerberos_auth_disabled BIT NULL If 1, Kerberos authentication is disabled.
is_policy_checked BIT NULL Password policy is checked.
is_expiration_checked BIT NULL Password expiration is checked.

Conclusion

The new password policy enforcement rules are active by default in YUKON but can be turned off by the administrator if you do not want every password to be checked. It would be a better option to keep the policy checks active and configure each login as it is created to be checked or not.

As you can see, the new changes are not sweeping in scope but they do offer a great step forward in thinking for Microsoft. In the past I have often wrote that it felt like Microsoft was giving up on using SQL Server logins altogether but with these new changes I am not longer sure of this. It is nice for them to recognize that not every application will be ported to Windows Authentication and that they need to continue with the strengthening of SQL Server logins.

I would like to let you know that the YUKON Beta 1 release was used during the research for this article and the information contained within this article may change during the subsequent Beta and Market releases. I doubt this will happen but please test any policies or scripts you may create with the information contained within this article against the final release before making it actual policy.

Total article views: 8797 | Views in the last 30 days: 2
 
Related Articles
FORUM

password policies

password policies

SCRIPT

Login Password Policy

This script lists all of the SQL logins with their password policy

FORUM

password expiration and password policy

password expiration and password policy

FORUM

sa password change, is it required with CHECK_POLICY=ON

if windows policy requires change of passwords

FORUM

Passwords - case sensitive in Login using SQL Server Authentication

Passwords - case sensitive in Login using SQL Server Authentication

Tags
security    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones