 | |||
|
|
||
| |||
| |||
| ||||||||
|
|
|||||||
|
|
|
|
| SQL Server News: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
|||||||||||||||||||||||||||||||||||||||||
SQL Server password policies and credentials In addition to new security features related to .NET managed code, other security features are intended to tighten authentication through SQL Server logins when SQL Server runs under Windows Server 2003. As we mentioned at the beginning of this chapter, users can use Windows authentication or SQL Server authentication to log in to SQL Server. Windows authentication is secure. A user's password is never sent across the network, and the system administrator can enforce password policy. The password policy can require that users change their password at the first login to the NT domain or machine. The policy can require users to use strong passwords -- for example, at least eight characters including at least one number, letter, and special character. The policy can also require users to change their password every so often. The policy can specify that a login will be locked out after a certain number of bad password attempts. When a database administrator switches all SQL Server logins to Windows authentication, SQL Server inherits this level of enforceable security. Until SQL Server 2005, SQL Server logins had none of these necessary security characteristics. And weak passwords are acknowledged to be the weakest link in most security systems. With the new SQL Server 2005 security features, SQL Server logins will have all the same security policy features available. Both SQL Server users and application roles will use the policy. With Windows Server 2003 or later, the policy will be implemented via an OS-level call, Net ValidatePasswordPolicy, so that the administrator can use the same policy for both Windows integrated and SQL Server logins. To give companies that convert to SQL Server 2005 time to analyze how the policy will affect existing applications, the policy can be turned off on a per-login basis. Obviously, this is not recommended. As Windows provides users with the ability to change their password at login time (or while logged on to Windows), so SQL Server users will have the ability to change their password during login. Both the client APIs, like OLE DB and ADO.NET, and the client tools, like SQL Server Management Studio, will support this. Password policy is set by using the Active Directory Users and Computers tool if you're using Active Directory, or by using the Local Security Settings administrator tool if you're administering a nondomain computer. Table 6-1 shows the settings that are exposed using Local Security Settings. Note that Account Lockout Duration (the amount of time accounts are locked out when you reach the Account Lockout Threshold) and Reset
Lockout Counter After (the amount of time after which the invalid login attempts revert to zero, if you haven't exceeded them) are not applicable until you set Account Lockout Threshold to something other than zero. There are two password options for SQL Server logins: CHECK_EXPIRATION and CHECK_POLICY. CHECK_EXPIRATION encompasses minimum and maximum password age, and CHECK_POLICY encompasses all the other policies. When you run afoul of either policy, the SQL Server login must be unlocked by the DBA, as shown shortly in an example. An administrator can add a new login through SQL Server Management Studio or by using the Transact-SQL statement CREATE LOGIN. The legacy stored procedure sp_addlogin will be supported for backward compatibility but will not expose the new features. As shown in the following example, you can create a new SQL Server login that requires the password to be changed on the user's first login attempt by using the MUST_CHANGE keyword. Attempting to access the SQL Server instance without changing the password will result in an error. CREATE LOGIN fred WITH PASSWORD = hy!at54Cq MUST_CHANGE, DEFAULT_DATABASE = pubs, CHECK_EXPIRATION = ON, CHECK_POLICY = ON go SETUSER fred produces: "There is no such user or group as fred" If a user has been locked out, the database administrator can unlock the login by using the following code. ALTER LOGIN fred WITH PASSWORD = 'hy!at54Cq' UNLOCK go In those rare cases where the database administrator wants to turn off the password expiration enforcement or security policy enforcement, ALTER LOGIN can accomplish this. Neither of these statements will work when the MUST_CHANGE flag is set and the user has not yet changed his password. ALTER LOGIN fred WITH CHECK_EXPIRATION = OFF go ALTER LOGIN fred WITH CHECK_POLICY = OFF go Credentials SQL Server 2005 introduces .NET procedural code that makes it easier to access resources outside SQL Server. This access is controlled by security levels on assemblies, as will be discussed later in the chapter. When accessing resources outside the database, SQL Server logins have no specific privileges -- only Windows principals defined to SQL Server are "known" to the underlying operating system. In previous versions of SQL Server, there were two choices for using external resources and SQL Server logins: Use the service account (the account of the service that runs sqlserver.exe) or use the guest account. The guest account is almost always disabled on Windows operating systems when hosting SQL Server. In SQL Server 2005, you can assign Windows credentials to SQL Server logins by first cataloging them with SQL Server. The same credentials can be assigned to multiple SQL Server logins. It looks something like this. CREATE CREDENTIAL sqlusers WITH IDENTITY = 'machinesqlusers', SECRET = '*Y6fy)' go CREATE LOGIN mary WITH PASSWORD = 'mary' GO ALTER LOGIN mary WITH CREDENTIAL = sqlusers GO Note that the Windows principal (machinesqlusers, in this case) must already be defined to the Windows security system. Click for the next excerpt in this series: Separation of users and schemas Click for the book excerpt series or visit here to obtain the complete book.
|
|
|
|
|||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||
The following excerpt, courtesy of Addison-Wesley, is from Chapter 6 of the book "A First Look at SQL Server 2005 for Developers" written by Bob Beauchemin, Niels Berglund and Dan Sullivan. Click for the complete 
|
|
 |  |  | |  | |  | |  | |  | |  | |  | |  | |
|
|
About Us | Contact Us | For Advertisers | For Business Partners | Site Index | RSS |
|
|
||
|
|||||||
