Ana səhifə

Sql server 2005 Security Best Practices Operational and Administrative Tasks


Yüklə 274 Kb.
səhifə4/7
tarix18.07.2016
ölçüsü274 Kb.
1   2   3   4   5   6   7

Lockdown of System Stored Procedures


SQL Server uses system stored procedures to accomplish some administrative tasks. These procedures almost always begin with the prefix xp_ or sp_. Even with the introduction of standard DDL for some tasks (for example, creating logins and users), system procedures remain the only way to accomplish tasks such as sending mail or invoking COM components. System extended stored procedures in particular are used to access resources outside the SQL Server instance. Most system stored procedures contain the relevant security checks as part of the procedure and also perform impersonation so that they run as the Windows login that invoked the procedure. An example of this is sp_reserve_http_namespace, which impersonates the current login and then attempts to reserve part of the HTTP namespace (HTTP.SYS) by using a low-level operating system function.

Because some system procedures interact with the operating system or execute code outside of the normal SQL Server permissions, they can constitute a security risk. System stored procedures such as xp_cmdshell or sp_send_dbmail are off by default and should remain disabled unless there is a reason to use them. In SQL Server 2005, you no longer need to use stored procedures that access the underlying operating system or network outside of the SQL Server permission space. SQLCLR procedures executing in EXTERNAL_ACCESS mode are subject to SQL Server permissions, and SQLCLR procedures executing in UNSAFE mode are subject to some, but not all, security checks. For example, to catalog a SQLCLR assembly categorized as EXTERNAL_ACCESS or UNSAFE, either the database must be marked as TRUSTWORTHY (see Database Ownership and Trust) or the assembly must be signed with a certificate or asymmetric key that is cataloged to the master database. SQLCLR procedures should replace user-written extended stored procedures in the future.

Some categories of system stored procedures can be managed by using SQL Server Surface Area Configuration. These include:


  • xp_cmdshell - executes a command in the underlying operating system

  • Database Mail procedures

  • SQL Mail procedures

  • COM component procedures (e.g. sp_OACreate)

Enable these procedures only if necessary.

Some system stored procedures, such as procedures that use SQLDMO and SQLSMO libraries, cannot be configured by using SQL Server Surface Area Configuration. They must be configured by using sp_configure or SSMS directly. SSMS or sp_configure can also be used to set most of the configuration feature settings that are set by using SQL Server Surface Area Configuration.

The system stored procedures should not be dropped from the database; dropping these can cause problems when applying service packs. Removing the system stored procedures results in an unsupported configuration. It is usually unnecessary to completely DENY all users access to the system stored procedures, as these stored procedures have the appropriate permission checks internal to the procedure as well as external.

Best practices for system stored procedures



  • Disable xp_cmdshell unless it is absolutely needed.

  • Disable COM components once all COM components have been converted to SQLCLR.

  • Disable both mail procedures (Database Mail and SQL Mail) unless you need to send mail from SQL Server. Prefer Database Mail as soon as you can convert to it.

  • Use SQL Server Surface Area Configuration to enforce a standard policy for extended procedure usage.

  • Document each exception to the standard policy.

  • Do not remove the system stored procedures by dropping them.

  • Do not DENY all users/administrators access to the extended procedures.

Password Policy


Windows logins abide by the login policies of the underlying operating system. These policies can be set using the Domain Security Policy or Local Security Policy administrator Control Panel applets. Login policies fall into two categories: Password policies and Account Lockout policies. Password policies include:

Account Lockout policies include:

  • Account Lockout Threshold (Number of invalid logins before lockout)

  • Account Lockout Duration (Amount of time locked out)

  • Reset Lockout Counter After n Minutes

In SQL Server 2005, SQL logins can also go by the login policies of the underlying operating system if the operating system supports it. The operating system must support the system call NetValidatePasswordPolicy. Currently, the only operating system that supports this is Windows Server 2003 and later versions. If you use SQL logins, run SQL Server 2005 on a Windows Server 2003 or later operating system. CREATE LOGIN parameters determine whether the login goes by the operating system policies. These parameters are:

  • CHECK_POLICY

  • CHECK_EXPIRATION

  • MUST_CHANGE

CHECK_POLICY specifies that the SQL login must abide by the Windows login policies and Account Lockout policies, with the exception of password expiration. This is because, if SQL logins must go by the Windows password expiration policy, underlying applications must be outfitted with a mechanism for password changing. Most applications currently do not provide a way to change SQL login passwords. In SQL Server 2005, both SSMS and SQLCMD provide a way to change SQL Server passwords for SQL logins. Consider outfitting your applications with a password-changing mechanism as soon as possible. Having built-in password changing also allows logins to be created with the MUST_CHANGE parameter; using this parameter requires the user to change the password at the time of the first login. Administrators should be aware of the fact that password length and complexity policies, but not expiration policies, apply to passwords used with encryption keys as well as to passwords used with SQL logins. For a description of encryption keys, see Encryption.

When SQL logins are used on pre-Windows 2003 operating systems, there is a series of hard-coded password policies in lieu of the domain or operating system policies if CHECK_POLICY = ON. These policies are enumerated in SQL Server Books Online.

Best practices for password policy


  • Mandate a strong password policy, including an expiration and a complexity policy for your organization.

  • If you must use SQL logins, ensure that SQL Server 2005 runs on the Windows Server 2003 operating system and use password policies.

  • Outfit your applications with a mechanism to change SQL login passwords.

  • Set MUST_CHANGE for new logins.

1   2   3   4   5   6   7


Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©atelim.com 2016
rəhbərliyinə müraciət