Retired Content This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Improving Web Application Security: Threats and Countermeasures J.D. Meier, Alex Mackman, Michael Dunner, Srinath Vasireddy, Ray Escamilla and Anandha Murukan Microsoft Corporation Published: June 2003 Last Revised: January 2006 Applies to: •.NET Framework version 1.1 • Microsoft® SQL Server™ 2000 • Microsoft Windows® 2000 ™ operating systems See the ' for links to additional security resources. See the for the starting point and a complete overview of Improving Web Application Security: Threats and Countermeasures.

Summary: This chapter provides a proven methodology for securing database servers. The chapter focuses on SQL Server database servers. It begins by reviewing the most common threats that affect database servers and it then takes a step-by-step approach that shows you how to improve your database server's security by applying secure configuration.

I have the following class that defines an entry in my table (I have remove the constructor for brewity, it simple sets that data): class FilterResult { public bool.

Contents In This Chapter • A proven methodology for securing database servers • An explanation of the most common database server threats • Steps to secure your server • A reference table that illustrates a secure database server Overview There are many ways to attack a database. External attacks may exploit configuration weaknesses that expose the database server. An insecure Web application may also be used to exploit the database. For example, an application that is granted too much privilege in the database or one that does not validate its input can put your database at risk.

Internal threats should not be overlooked. Have you considered the rogue administrator with network access? What about the database user tricked into running malicious code? For that matter, could any malicious code on the network compromise your database? This chapter begins by reviewing the most common threats that affect database servers. It then uses this perspective to create a methodology. This chapter then puts the methodology into practice and takes a step-by-step approach that shows you how to improve your database server's security.

How to Use This Chapter This chapter provides a methodology and steps for securing a database server. The methodology can be adapted for your own scenario. The steps put the methodology into practice.

To gain the most from this chapter: • Read Chapter 2, 'Threats and Countermeasures.' This chapter provides an explanation of potential threats faced by Web applications and downstream database servers.

• Use the snapshot. The section, 'Snapshot of a Secure Database Server,' later in this chapter lists the attributes of a secure database server. It reflects distilled input from a variety of sources including customers, industry experts, and internal Microsoft development and support teams. Use the snapshot table as a reference when configuring your database server. • Use the checklist. The 'Checklist: Securing Your Database Server' in the 'Checklist' section of this guide provides a quick reference.

Use the checklist to quickly evaluate the scope of the required steps and to help you work through the individual steps. • Use the 'How To' section. The 'How To' section in this guide includes the following instructional articles that help you implement the guidance in this chapter: • 'How To: Use Microsoft Security Baseline Analyzer' • 'How To: Use IPSec' • 'How To: Implement Patch Management' Threats and Countermeasures An attacker can target and compromise a database server in a number of ways by exploiting a variety of configuration and application level vulnerabilities. The main threats to a database server are: • SQL injection • Network eavesdropping • Unauthorized server access • Password cracking Figure 18.1 shows the major threats and vulnerabilities that can result in a compromised database server and the potential destruction or theft of sensitive data. Figure 18.1 Top database server threats and vulnerabilities The next sections describe each of these threats and vulnerabilities.

SQL Injection With a SQL injection attack, the attacker exploits vulnerabilities in your application's input validation and data access code to run arbitrary commands in the database using the security context of the Web application. Vulnerabilities Vulnerabilities exploited by SQL injection include: • Poor input validation in your Web applications • Unsafe, dynamically constructed SQL commands • Over-privileged application logins to the database • Weak permissions that fail to restrict the application's login to the database Countermeasures To counter SQL injection attacks: • Your application should constrain and sanitize input data before using it in SQL queries. • Use type safe SQL parameters for data access. These can be used with stored procedures or dynamically constructed SQL command strings. Using SQL parameters ensures that input data is subject to type and length checks and also that injected code is treated as literal data, not as executable statements in the database. • Use a SQL Server login that has restricted permissions in the database.

Ideally, you should grant execute permissions only to selected stored procedures in the database and provide no direct table access. For more information about application-level countermeasures to SQL injection attacks, see Chapter 14, '.' Network Eavesdropping The deployment architecture of most applications includes a physical separation of the data access code from the database server. As a result, sensitive data, such as application-specific data or database login credentials, must be protected from network eavesdroppers.

Vulnerabilities Vulnerabilities that increase the likelihood of network eavesdropping include: • Insecure communication channels • Passing credentials in clear text to the database; for example: • Using SQL authentication instead of Windows authentication • Using SQL authentication without a server certificate Countermeasures To counter network eavesdropping: • Use Windows authentication to connect to the database server to avoid sending credentials over the network. • Install a server certificate on the database server. This results in the automatic encryption of SQL credentials over the network. • Use an SSL connection between the Web server and database server to protect sensitive application data. This requires a database server certificate.

• Use an IPSec encrypted channel between Web and database server. Unauthorized Server Access Direct access to your database server should be restricted to specific client computers to prevent unauthorized server access. Vulnerabilities Vulnerabilities that make your database server susceptible to unauthorized server access include: • Failure to block the SQL Server port at the perimeter firewall • Lack of IPSec or TCP/IP filtering policies Attacks Direct connection attacks exist for both authenticated users and those without a user name and password; for example: • Tools such as Query Analyzer (Isqlw.exe) or the command line equivalent (Osql.exe) are used to establish a direct connection to SQL Server and issue commands.

• Server information, such as software version, is revealed to an attacker who sends carefully constructed packets to listening ports. Countermeasures To counter these attacks: • Make sure that SQL Server ports are not visible from outside of the perimeter network. • Within the perimeter, restrict direct access by unauthorized hosts, for example, by using IPSec or TCP/IP filters. Password Cracking A common first line of attack is to try to crack the passwords of well known account names, such as sa (the SQL Server administrator account).

Vulnerabilities Common vulnerabilities that lead to password cracking are: • Weak or blank passwords • Passwords that contain everyday words Attacks Common password cracking attacks include: • Dictionary attacks • Manual password guessing Countermeasures To counter these attacks: • Create passwords for SQL Server login accounts that meet complexity requirements. • Avoid passwords that contain common words found in the dictionary.

Note If you use Windows authentication, password complexity can be enforced by Windows security policy. Methodology for Securing Your Server Securing SQL Server and Windows 2000 or Windows Server 2003 involves many configuration changes. The best approach is to separate the changes that must be made into specific configuration categories. Using categories allows you to systematically walk through the securing process from top to bottom or pick a particular category and apply specific steps. Configuration Categories The securing methodology has been organized into the categories shown in Figure 18.2.

Figure 18.2 Database server security categories The configuration categories shown in Figure 18.2 are based on best practices obtained from field experience, customer validation, and the study of secure deployments. The rationale behind the categories is as follows: • Patches and Updates Many security threats exist because of vulnerabilities in operating systems, services, and applications that are widely published and well known. When new vulnerabilities are discovered, attack code is frequently posted on Internet bulletin boards within hours of the first successful attack. Patching and updating your server's software is the first step toward securing your database server. There may be cases where a vulnerability exists and no patch is available. In these cases, be aware of the details of the vulnerability to assess the risk of attack and take measures accordingly. • Services Services are prime vulnerability points for attackers who can exploit the privileges and capabilities of the service to access the server and potentially other computers.

Some services are designed to run with privileged accounts. If these services are compromised, the attacker can perform privileged operations. By default, database servers generally do not need all services enabled. By disabling unnecessary and unused services, you quickly and easily reduce the attack surface area. • Protocols Limit the range of protocols that client computers can use to connect to the database server and make sure you can secure those protocols. • Accounts Restrict the number of Windows accounts accessible from the database server to the necessary set of service and user accounts.

Use least privileged accounts with strong passwords in all cases. A least privileged account used to run SQL Server limits the capabilities of an attacker who compromises SQL Server and manages to execute operating system commands.

• Files and Directories Use NTFS file system permissions to protect program, database, and log files from unauthorized access. When you use access control lists (ACLs) in conjunction with Windows auditing, you can detect when suspicious or unauthorized activity occurs. • Shares Remove all unnecessary file shares, including the default administration shares if they are not required. Secure any remaining shares with restricted NTFS permissions. Although shares may not be directly exposed to the Internet, a defense in depth strategy with limited and secured shares reduces risk if a server is compromised.

• Ports Unused ports are closed at the firewall, but it is required that servers behind the firewall also block or restrict ports based on their usage. For a dedicated SQL Server, block all ports except for the necessary SQL Server port and the ports required for authentication. • Registry SQL Server maintains a number of security-related settings, including the configured authentication mode in the registry. Restrict and control access to the registry to prevent the unauthorized update of configuration settings, for example, to loosen security on the database server. • Auditing and Logging Auditing is a vital aid in identifying intruders, attacks in progress, and to diagnose attack footprints. Configure a minimum level of auditing for the database server using a combination of Windows and SQL Server auditing features.

• SQL Server Security A number of SQL Server security settings can be controlled through Enterprise Manager. These include the authentication mode, auditing level, and the accounts used to run the SQL Server service. For improved security, you should use Windows authentication. You should also enable SQL Server logon auditing and ensure that the SQL Server service runs using a least privileged account. • SQL Server Logins, Users, and Roles SQL Server 2000 manages access control using logins, databases, users, and roles.

Users (and applications) are granted access to SQL Server by way of a SQL server login. The login is associated with a database user and the database user is placed in one or more roles. The permissions granted to the role determine the tables the login can access and the types of operations the login can perform. This approach is used to create least privileged database accounts that have the minimum set of permissions necessary to allow them to perform their legitimate functionality. • SQL Server Database Objects The ability to access SQL Server database objects, such as built-in stored procedures, extended stored procedures and cmdExec jobs, should be reviewed. Also, any sample databases should be deleted.

SQL Server Installation Considerations Before taking steps to secure your database server, know the additional components that are present on a Windows 2000 or Windows Server 2003 server after SQL Server is installed. What Does SQL Server Install?

When you install SQL Server, a number of Windows services are installed in addition to program and data files. By default, program and data files are located in the Program Files Microsoft SQL Server directory. Table 18.1 shows the services and folders that are created. Table 18.1 SQL Server Installation Defaults. Tool Purpose Upgrade tools Used to upgrade SQL Server 6.5 databases Replication support Script and binary files used for replication.

(Do not install unless you need replication.) Full text search Full text search engine (Microsoft Search service). Do not install unless you require full text search. Books online SQL Server documentation Development tools Headers and library files used by C developers and Microsoft Data Access (MDAC), and XML software development kits (SDKs), and an interface for stored procedure debugging. Code samples Sample code used to educate developers.

Also, select Windows authentication mode unless SQL Server authentication is specifically required. Windows authentication offers the following advantages: • Existing domain and local security policies can be used to enforce strong passwords and account management best practices.

• Credentials are not passed over the network. • Application database connection strings do not require credentials. If you select Mixed Mode, create a strong password for the sa account. The sa account is a prime target for password guessing and dictionary attacks.

Steps for Securing Your Database Server This section guides you through the process of securing your database server using the configuration categories introduced earlier. The steps cover Windows 2000 and Windows Server 2003 and SQL Server 2000. Each step may contain one or more actions to secure a particular area or feature. Step 1 Step 2 Step 3 Step 4 Step 5 Step 6 Patches and Updates Services Protocols Accounts Files and Directories Shares Step 7 Step 8 Step 9 Step 10 Step 11 Step 12 Ports Registry Auditing and Logging SQL Server Security SQL Server Logins, Users, and Roles SQL Server Database Objects Step 1. Patches and Updates Failure to apply the latest patches and updates in a timely manner means that you are providing opportunities for attackers to exploit known vulnerabilities. You should verify that your database server is updated with the latest Windows 2000 / Windows Server 2003 and SQL Server service packs and updates. Important Make sure to test patches and updates on test systems that mirror your production servers as closely as possible before applying them on production servers.

Detect Missing Service Packs and Updates Use the Microsoft Baseline Security Analyzer (MBSA) to detect the necessary Windows and SQL Server updates that may be missing. MBSA uses an XML file as the reference of existing updates. This XML file is either downloaded by MBSA when a scan runs, or the file can be downloaded on the local server or from a network server. To detect and install patches and updates • Download and install MBSA. You can do this from the MBSA home page. If you do not have Internet access when you run MBSA, it will not be able to retrieve the XML file containing the latest security settings from Microsoft.

In this event, download the XML file manually and put it in the MBSA program directory. The XML file is available from.

• Run MBSA by double-clicking the desktop icon or selecting it from the Programs menu. • Click Scan a computer. MBSA defaults to the local computer.

• Clear all check boxes apart from Check for security updates. This option detects which patches and updates are missing. • Click Start scan. Your server is now analyzed. When the scan is complete, MBSA displays a security report, which it also writes to the%userprofile% SecurityScans directory.

• Download and install the missing updates. Click the Result details link next to each failed check to view the list of security updates that are missing.

The resulting dialog box displays the Microsoft security bulletin reference number. Click the reference to find out more about the bulletin and to download the update. For more information about using MBSA, see ' in the 'How To' section of this guide. For more information about applying service packs, hot fixes, and security patches, see. Patching MSDE The Microsoft Desktop Edition (MSDE) of SQL Server must be patched differently than the full version of SQL Server. For details about patching MSDE, see ' in the 'How To' section of this guide.

Services To reduce the attack surface area and to make sure you are not affected by undiscovered service vulnerabilities, disable any service that is not required. Run those services that remain using least privileged accounts. In this step, you: • Disable unused SQL Server services. • Disable the Microsoft DTC (if not required). Note To disable a service, set its startup type to Disabled using the Services MMC snap-in in the Computer Management tool. Disable Unused SQL Server Services During a SQL Service installation, the following four Windows services are installed: • MSSQLSERVER (or MSSQL$InstanceName for a named instance). This is the SQL Server database engine and is the only mandatory service.

• SQLSERVERAGENT (or SQLAgent$InstanceName for a named instance). With this support service, you can schedule commands and notify operators when errors occur. • MSSQLServerADHelper. This provides Active Directory integration services, including database instance registration. • Microsoft Search.

This provides full text search capabilities. This service must always run under the local system account. Only the MSSQLSERVER database engine is required. The remaining services provide additional functionality and are required only in specific scenarios. Disable these services if they are not required.

Note SQL Server should not be configured to run as the local System account or any account that is a member of the local Administrators group. For details about configuring the service account used to run MSSQLSERVER, see '.' Disable the Microsoft DTC (if not required) If you do not use distributed transactions through the Microsoft DTC, disable the service. Protocols By preventing the use of unnecessary protocols, you reduce the surface area of attack. Configure SQL Server to support only clients that connect using the TCP/IP protocol.

Disable all other protocols, unless required. In this step, you: • Restrict SQL Server to TCP/IP. • Harden the TCP/IP Stack. Restrict SQL Server to TCP/IP By enforcing the use of TCP/IP you can control who connects to the server on specific ports using IPSec policies or TCP/IP filtering. To support IPSec or TCP/IP filtering, your SQL Server should support client connections over TCP/IP only. To configure SQL Server network protocol support • In the Microsoft SQL Server programs group, start the Server Network Utility. • Make sure that TCP/IP is the only SQL Server protocol that is enabled as shown in Figure 18.3.

Disable all other protocols. Figure 18.3 Disabling all protocols except TCP/IP in the SQL Server Network Utility Harden the TCP/IP Stack Windows 2000 and Windows Server 2003 allow you to control many parameters to configure TCP/IP implementation. Some of the defaults are geared toward server availability and specific features. For information about how to harden the TCP/IP stack, see ' in the 'How To' section of this guide. Additional Considerations To further improve your database server security, disable NetBIOS and SMB. Both protocols can be used to glean host configuration information, so you should remove them when possible.

For more information about removing NetBIOS and SMB, see ' Step 4. Protocols' in Chapter 16, 'Securing Your Web Server.' Also consider using IPSec to restrict the ports on which your database server accepts incoming connections. For more information about how to do this, see ' in the 'How To' section of this guide. Accounts Follow the principle of least privilege for the accounts used to run and connect to SQL Server to restrict the capabilities of an attacker who manages to execute SQL commands on the database server. Also apply strong password policies to counter the threat of dictionary attacks. Slow Pcfighter Product Key Free Download.

In this step, you: • Secure the SQL Server service account. • Delete or disable unused accounts.

• Disable the Windows guest account. • Rename the administrator account. • Enforce strong password policy. • Restrict remote logins.

• Disable null sessions (anonymous logons). Secure the SQL Server Service Account Run the SQL Server service using a least privileged account to minimize the damage that can be done by an attacker who manages to execute operating system commands from SQL Server. The SQL Server service account should not be granted elevated privileges such as membership to the Administrators group. To create a new account to run the SQL Server service • Start the Computer Management tool, and then expand Local Users and Groups. • Right-click the Users folder, and then click New User. • Create a new user making sure you use a strong password.

In the New User dialog box, clear the User must change password at next logon check box, and then select the User cannot change password and Password never expires check boxes. • Remove the new account from the Users group because this group is granted liberal access across the computer.

You can now configure SQL Server to run using this new account. For more information, see '.' Accessing the Network from SQL Server If you need to access network resources from SQL Server, for example to perform network backups, for replication or log shipping, the SQL Server service account must be capable of being authenticated across the network. You have two choices. Either create a duplicate local account with the same name and password on the remote server, or use a least privileged domain account. Delete or Disable Unused Accounts Unused accounts and their privileges may be a haven for an attacker who has gained access to a server.

Audit local accounts on the server and delete those that are unused. The recommendation is to first disable an account to see if this causes any problems before deleting the account, because deleted accounts cannot be recovered. Note that the administrator account and guest account cannot be deleted. Note During SQL Server 200 SP3 installation, Sqldbreg2.exe creates the SQL Debugger account. Visual Studio.NET uses this account when debugging stored procedures from managed.NET code. Because this account is only used to support debugging, you can delete it from production database servers.

Disable the Windows Guest Account The Windows guest account is the account used when an anonymous connection is made to the computer. To restrict anonymous connections to your database server, keep this account disabled.

By default, the guest account in Windows 2000 and Windows Server 2003 is disabled. To see if it is enabled, display the Users folder in the Computer Management tool.

It is represented by a cross icon. If it isn't disabled, display its Properties dialog box and select the Account is disabled check box. Rename the Administrator Account The default local administrator account is a target for malicious use because of its high privileges on the computer. To improve security, rename the default administrator account and assign it a strong password.

Enforce Strong Password Policy To counter password guessing and brute force dictionary attacks, apply strong password policies by configuring security policy. The keys to strong account and password policies are: • Set password length and complexity. Enforcing strong passwords reduces the chance of successful password guessing or dictionary attacks. • Set password expiration.

Regularly expiring passwords reduces the chance that an old password will be used for unauthorized access. The expiration period is typically guided by a company's security policy. Table 18.3 shows the default and recommended password policy settings. Table 18.3 Password Policy Default and Recommended Settings. Password Policy Default Setting Recommended Minimum Setting Enforce password history 1 password remembered 24 passwords remembered Maximum password age 42 days 42 days Minimum password age 0 days 2 days Minimum password length 0 characters 8 characters Passwords must meet complexity requirement Disabled Enabled Strong password using reversible encryption for all users in the domain Disabled Disabled Additionally, log failed login attempts to detect and trace malicious behavior. For more information, see '.' For more information about password policies, see password 'Best Practices' on.

Restrict Remote Logons Use the Local Security Policy tool to remove the 'Access this computer from the network' user right from the Everyone group to restrict who can log on to the server remotely. Disable Null Sessions (Anonymous Logons) To prevent anonymous access, disable null sessions. These are unauthenticated or anonymous sessions established between two computers. Unless null sessions are disabled, an attacker can connect to your server anonymously, that is, without requiring authentication. As soon as an attacker establishes a null session, a variety of attacks can be performed, including enumeration used to obtain system-related information from the target computer.

The type of information that can be returned over a null session includes domain and trust details, shares, user information including groups and user rights, registry keys, and more. Disable them because they represent a significant security threat.

Restrict null sessions by setting RestrictAnonymous=1 in the registry at the following location. HKLM System CurrentControlSet Control LSA RestrictAnonymous=1 For more information, see Microsoft Knowledge Base article 246261, 'How To: Use the RestrictAnonymous Registry Value in Windows 2000.' Additional Considerations Consider the following steps to improve security for your database server: • Require approval for account delegation. Do not mark domain accounts as trusted for delegation in Active Directory without special approval. • Do not use shared accounts.

Do not create shared account for use by multiple individuals. Give authorized individuals their own accounts. The activities of individuals can be audited separately and group membership and privileges appropriately assigned. • Restrict the local Administrators group membership. Ideally, have no more than two administration accounts.

This helps provide accountability. Also, do not share passwords, again to provide accountability. • Limit the administrator account to interactive logins.

If you perform only local administration, you can restrict your administrator account to interactive logons by removing the 'Access this computer from the network' user right to deny network logon rights. This prevents users (well intentioned or otherwise) from remotely logging on to the server using the administrator account. If a policy of local administration is too inflexible, implement secure remote administration. For more information about remote administration, see ' later in this chapter. • Enable NTLMv2 authentication. If client computers connect to your database server by using Windows authentication, you should configure your database server to use the strongest version of Windows authentication, which is NTLMv2.

Note To support NTLMV2, clients must be running Windows 2000, Windows Server 2003, or Windows NT® operating system version 4.0 with Service Pack 4. To enable NTLMv2 authentication from the Local Security Policy Tool • Expand Local Policies, select Security Options, and then double-click LAN Manager Authentication Level. • Select Send NTLMv2 response only refuse LM & NTLM. This is the most secure setting. Note This is equivalent to setting the HKLM System CurrentControlSet Control Lsa LMCompatibilityLevel DWORD value to 5.

Files and Directories In addition to securing operating system files using ACLs, harden NTFS permissions to restrict access to SQL Server program files, data files, and log files together with system level tools. Additionally, the SQL Server service account should have access only to what it needs.

In this step, you: • Verify permissions on SQL Server install directories. • Verify Everyone group does not have permissions to SQL Server files. • Secure setup log files. • Secure or remove tools, utilities, and SDKs. Verify Permissions on SQL Server Install Directories Verify the permissions listed in Table 18.4 to the account the SQL Server service is running under. The location specified in parentheses is the default install location.

This may vary for your installation. Table 18.4 NTFS Permissions for SQL Server Service Account. Component Characteristics Patches and Updates The latest service packs and patches are applied for Windows 2000 / Windows Server 2003 and SQL Server. Services Nonessential services are disabled. The MSDTC is disabled if not used. The MSSearch service is disabled if not required.

The SQLServerAgent service is disabled if not required. The MSSQLServerADHelper service is disabled if not required. Protocols Unnecessary protocols are removed or disabled. The following protocols are not enabled on the server: NetBIOS and SMB. The TCP/IP stack is hardened. Accounts SQL Server service account is secured (least privileged).

Unnecessary Windows accounts are deleted or disabled. The Windows guest account is disabled. A new administrator account is created. Strong password policy is enforced. Remote logons are restricted. Null sessions (anonymous logons) are disabled.

Approval is required for account delegation. Shared accounts are not used. Membership of the local Administrators group is limited (ideally, no more than two members).

The administrator account is limited to interactive logins (or a secure remote administration solution is provided). NTLMv2 authentication is enabled and enforced (LMCompatibilityLevel is set to 5). Files and Directories Volumes are formatted with NTFS. Everyone group has no rights to system or tools directories. Samples directories, Help directories, and unused admin directories are removed from the server. Permissions are hardened on SQL Server installation folder. Passwords removed from Service Pack 1 and Service Pack 2 setup log files.

Tools, utilities and SDKs are removed. Unused applications are removed. Sensitive data files are encrypted using EFS.

(This is optional for database files (.mdf), but not for log files (.ldf)). Shares Unnecessary shares are removed from the server. Access is restricted to required shares. Shares are not accessible by Everyone, unless necessary. Administration shares (C$, Admin$) are removed if they are not required. Ports All ports except SQL Server listening port [Default 1433] are blocked Named instances are configured to listen on the same port.

A non-standard SQL Server port (not TCP 1443) is used as an additional layer of defense. The hide server option is used as an additional layer of defense (optional). The firewall is configured to support DTC traffic (if necessary).

A firewall is used to separate users from the SQL TCP/IP port. Registry Everyone group is removed from SQL Server registry keys.

SAM is secured (stand-alone servers only). Auditing and Logging Failed Windows logon attempts are logged. Failed actions across the file system are logged. SQL Server login auditing is enabled.

SQL Server Settings SQL Server Security Authentication setting for SQL Server is Windows Only if possible. SQL Server audit level set to Failure or All. The SQL Server Startup Service account is a least privileged account.

SQL Server Logins, Users and Roles The sa account has a strong password. SQL Server guest accounts are removed from non-system databases. The BUILTIN Administrators group is removed from the SQL Server logins.

The sysadmin role does not contain the BUILTIN Administrators group. Permissions are not granted for the public role. The sysadmin role contains no more than two users. Restricted (granular) database permissions are granted (Built-in, non-granular roles such as db_datareader and db_datawriter are avoided) Default permissions for SQL Server objects are not changed. SQL Server Database Objects All sample databases are removed from the server. Stored procedures are secured. Extended stored procedures are secured.

CmdExec is restricted to the sysadmin role only. Additional Considerations In addition to the steps described in this chapter, consider the following guidelines: • Install a certificate on the database server.

If you use Windows authentication (NTLM or Kerberos), logon credentials are not passed over the network to SQL Server. If you use SQL authentication, it is a good idea to secure the credentials because they are passed to SQL Server in unencrypted format. Do this by installing a certificate on the database server. This automatically results in the encryption of SQL credentials over the wire.

It is also a good idea to make sure that your application securely stores database connection strings. For more information, see Chapter 14, '.' • Restrict access to sensitive commands and stored procedures.

SQL Server provides powerful hooks into the operating system. For example, you can use the xp_cmdshell extended stored procedure to run any operating system command. If an attacker manages to run arbitrary commands in the database, for example through a SQL injection vulnerability, the ability to execute operating system commands is limited only by the security credentials of the account used to run SQL Server.

This is the primary reason for running SQL Server with a least privileged local account. • Use a dedicated computer as a database server. Also cluster it for failover. • Physically protect the database server.

Locate the server in a secure computer room. • Restrict local logons. Do not allow anyone to locally log on to the server, apart from the administrator. Staying Secure You need to regularly monitor the security state of your database server and update it regularly to help prevent newly discovered vulnerabilities from being exploited. To help keep your database server secure: • Perform regular backups.

• Audit group membership. • Monitor audit logs. • Stay current with service packs and patches. • Perform security assessments.

• Use security notification services. Perform Regular Backups You must be able to restore data in the event of a compromise. If you have a recovery system in place, test it before you actually need it.

The first time you need to recover data should not be the first time you test your backup and restore process. For more information on backing up and restoring SQL Server, see the following resources: • SQL Server 2000 documentation, 'Backing Up and Restoring Databases' • 'Backup and Restore Strategies with SQL Server 2000,' by Rudy Lee Martinez, Audit Group Membership Keep track of user group membership, particularly for privileged groups such as Administrators. The following command lists the members of the Administrators group: net localgroup administrators Monitor Audit Logs Monitor audit logs regularly and analyze the log files by manually viewing them or use the technique described in Microsoft Knowledge Base article 296085, 'How To: Use SQL Server to Analyze Web Logs.' Stay Current with Service Packs and Patches Set up a schedule to analyze your server's software and subscribe to security alerts. Use MBSA to regularly scan your server for missing patches. The following links provide the latest updates: • Windows 2000 service packs.

The latest service packs are listed. • Windows 2003 service packs. The latest service packs are listed. • Critical updates.

These updates help to resolve known issues and help protect your computer from known security vulnerabilities. For the latest critical updates, see. • Advanced security updates. Also monitor the advanced security updates or Windows 2000. These also help protect your computer from known security vulnerabilities.

Perform Security Assessments Use MBSA to regularly check for security vulnerabilities and to identify missing patches and updates. Schedule MBSA to run daily and analyze the results to take action as needed. For more information about automating MBSA, see ' in the 'How To' section of this guide.

Use Security Notification Services Use the Microsoft services listed in Table 18.6 to obtain security bulletins with notifications of possible system vulnerabilities. Table 18.6 Security Notification Services.

Service Location TechNet security Web site Use this Web page to view the security bulletins that are available for your system. Microsoft Security Notification Service Use this service to register for regular email bulletins that notify you of the availability of new fixes and updates Additionally, subscribe to the industry security alert services shown in Table 18.7.

This allows you to assess the threat of a vulnerability where a patch is not yet available. Table 18. Encyclopedia Didattica Della Chitarra Installazione Skype. 7 Industry Security Notification Services.

Service Location CERT Advisory Mailing List Informative advisories are sent when vulnerabilities are reported. Windows and.NET Magazine Security UPDATE [Content link no longer available, original URL:'Announces the latest security breaches and identifies fixes. NTBugtraq This is an open discussion of Windows security vulnerabilities and attacks.

Vulnerabilities which currently have no patch are discussed. Remote Administration Administrators often need to be able to administer multiple servers. Make sure the requirements of your remote administration solution do not compromise security.

If you need remote administration capabilities, the following recommendations help improve security: • Restrict the number of administration accounts. This includes restricting the number of administration accounts as well as restricting which accounts are allowed to logon remotely. • Restrict the tools. The main options include SQL Enterprise Manager and Terminal Services. Both SQL Enterprise Manager and Terminal Services use Windows security. As such, the main considerations here are restricting the Windows accounts and the ports you use.

• Restrict the computers that are allowed to administer the server. IPSec can be used to restrict which computers can connect to your SQL Server. Securing Terminal Services It is possible to use Microsoft Terminal Services securely to remotely administer your database server. Terminal Services is based on Microsoft's proprietary protocol known as Remote Desktop Protocol (RDP). RDP uses the TCP 3389 port and supports two concurrent users. The following sections describe how to install and configure Terminal Services for secure administration: • Install Terminal Services. • Configure Terminal Services.

Install Terminal Services To install terminal services, do the following • Install Terminal Services by using Add/Remove Programs from the Control Panel. Use the Add/Remove Windows Components option. You do not need to install the Terminal Services Licensing service for remote administration. • Configure Terminal Services for remote administration mode. • Remove the TsInternetUser user account from the system, which is created during Terminal Services installation. This account is used to support anonymous Internet access to Terminal Services, which should not be enabled on the server.

Configure Terminal Services Use the Terminal Services configuration MMC snap-in available from the Administrative Tools program group to configure the following • There are three levels (Low, Medium, and High) of encryption available for connections to Terminal Services. Set the encryption to 128-bit key. Note that the Windows high encryption pack should be installed on both the server and the client. • Configure the Terminal Services session to disconnect after idle connection time limit. Set it to end a disconnected session. A session is considered to be disconnected if the user closes the Terminal Services client application without logging off in a period of 10 minutes. • Finally, restrict permissions to access Terminal Services.

Use the RDP permissions tab in the RDP dialog box. By default, all members of the Administrators group are allowed to access Terminal Services. If you do not want all members of the Administrators group to access Terminal Services, remove the group and add individual accounts that need access. Note that the SYSTEM account must be in the list. Use a secure VPN connection between the client and the server or an IPSec tunnel for enhanced security. This approach provides mutual authentication and the RDS payload is encrypted. Copying Files over RDP Terminal Services does not provide built-in support for file transfer.

However, you can install the File Copy utility from the Windows 2000 Server Resource Kit to add file transfer functionality to the clipboard redirection feature in Terminal Services. For more information about the utility and installation instructions, see Microsoft Knowledge Base article 244732, 'How To: Install the File Copy Tool Included with the Windows 2000 Resource Kit.' Summary Database servers are a prime target for attackers. The database server must be secured against internal, external, network level, and application level attacks.

A secure database server includes a hardened SQL Server 2000 installation on top of a hardened Windows 2000 / Windows Server 2003 installation, coupled with secure network defenses provided by routers and firewalls. For a quick reference checklist, see ' in the 'Checklists' section of this guide. Additional Resources For more information about SQL Server security, see the following resources: • Microsoft SQL Server Security home page: [Content link no longer available, original URL:'• SQL Server 2000 Resource Kit CD, Chapter 10 — Implementing Security:. • 'SQL Server 2000 Security Model,' by Richard Waymire and Ben Thomas:. • For information about changing the SQL Server service account, see Microsoft Knowledge Base article 283811, 'How To: Change the SQL Server Service Account Without Using SQL Enterprise Manager in SQL Server 2000.'

• For information about SQL Server auditing, see the TechNet article, 'SQL Server 2000 Auditing,' by John Howie,.