SQL Server encryption and decryption policies

6.4 SQL server password policies and certificates

SQL Server 2005 enhances the authentication for logon to SQL server running on Windows Server 2003. SQL Server 2005 has also improved the identity authentication of SQL Server Running in other operating systems. As mentioned at the beginning of this chapter, you can use Windows authentication or SQL Server authentication to log on to SQL Server. Windows authentication is very secure, because the user's password will not be sent online, and the Administrator of the domain and machine can enhance the password policy. Password policies may require users to change their passwords when they log on to the NT domain or machine for the first time. The policy may require the user to use a strong password, for example, at least eight characters, including at least one number, letter, and special symbol. Password policies may also require users to change their passwords frequently. This policy allows you to disable logon after several attempts of wrong passwords. When a database administrator only logs on to Windows, SQL Server inherits this level to enhance security. Before SQL Server 2005, SQL Server did not have these security features. In most security systems, weak passwords are considered to be the weakest security link.

With the new security features of SQL Server 2005, SQL Server logon accounts will have all available security policy features. SQL Server users and ApplicationsProgramAll roles use this policy. In Windows Server 2003 or later versions, this policy will be implemented through system-level calls. The system-level call for this operation is netvalidatepasswordpolicy. In this way, the administrator can use the same policy in Windows integration and SQL Server login. In order for SQL Server 2005 companies to have time to analyze how policies affect existing applications, these policies have been disabled for each Logon account. Obviously, this approach is not recommended. By default, the check_expiration policy is set to off, because organizations and software vendors will have to increase the password change capability for each application. Windows provides users with the ability to change the password upon login (or after Windows), SQL Server users can change the password upon login. Client application interfaces (such as ole db, ODBC, and ADO. net) and client tools (such as SQL Server Management studio) have been enhanced to support this feature.

If you are using active directory, the password policy will be set through the Active Directory users and computers tool. If you are managing a non-domain computer, the password policy will be set through the local security settings management tool. Table 6.1 provides settings for using local security settings.

Table 6.1 security policies of Logon accounts for Windows and SQL Server 2005

Policy category	Policy Name	Default settings (local server)
Password Policy	Strong Password History	0 password memory
	Maximum Password usage time	42 days
	Minimum Password usage time	0 days
	Minimum Password Length	0 characters
	Password must match complexity requirements
	Save the password using the pass-through encryption method	Disable
Account lock Policy	Account lock Period	Disable
	Account lock threshold	0 illegal logon attempts
	After that, reset the lock counter	Not applicable

Note: Account Lockout Duration (the time when the Account Lockout Threshold (account lock limit) is reached) and Reset Lockout Counter After (when an invalid logon attempt returns 0 (if it is not extended) are not applicable until the Account lockout threshold is set to a non-0 value.

For SQL Server login accounts, there are two password options: check_expiration and check_policy. Check_expiration includes the minimum and maximum password period, while check_policy includes all other policies. When a user is running and any policy is running, the SQL Server login account must be unlocked by the database administrator, as shown in an example later in this chapter.

The administrator can use SQL Server Management studio or the t_ SQL statement create login to add a new Logon account. For backward compatibility, the traditional Stored Procedure sp_addlogin is still supported, but this stored procedure does not show new features. As in the following example, you can create a new SQL Server Logon account, which requires you to use the must_change keyword to change the password when the user attempts to log on for the first time. If you try to access the SQL server instance without changing the password, an error will occur:

Create login Fred with Password = 'hy! At54cq 'must_change,

Default_database = pubs,

Check_expiration = on,

Check_policy = on

Go

When Fred tries to log on to the database, he will get an error "password must be changed upon first Logon. The database administrator can use DDL to manage password policy issues. If the Logon account Fred is locked, for example, after three failed logon attempts, the database administrator canCodeUnlock Logon account:

Alter login Fred with Password = 'fredsnewpassword # 'unlock

Go

In rare cases, the database administrator can use alter login to disable the force password validity period or enforce security policies. When the must_change flag is set but the user has not changed the password, the following two statements cannot work:

Alter login Fred with check_expiration = off

Go

Alter login Fred with check_policy = off

go