Yukon Passwords

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