Securing Your Database Server

[ivo2296]

This is taken from microsoft for those who are not lazy to read:

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, "Building Secure Data Access."
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
Item Details Services MSSQLSERVER
MSSQLServerADHelper
Microsoft Search
SQLSERVERAGENT Folders \program files\Microsoft SQL Server\mssql\binn (program files)
\program files\Microsoft SQL Server\mssql\data (data files including .mdf, .log, and .ndf)
\program files\Microsoft SQL Server\80\tools (shared tools/books online)
\program files\Microsoft SQL Server\mssql\logs (error logs)
\program files\Microsoft SQL Server\mssql\backup (backup files)
\program files\Microsoft SQL Server\mssql\jobs (temp job output files) For named instances, the instance name is used in the file path:
\program files\Microsoft SQL Server\MSSQL$InstanceName\binn
\program files\Microsoft SQL Server\MSSQL$InstanceName\data

SQL Server Installation Recommendations

If you are building a new database server from scratch, there are a number of considerations to take into account before installing SQL Server. Also, it is a good idea to perform a custom installation of SQL Server so you can select the most secure installation options.
Before Running SQL Server Setup

Before you run the SQL Server setup program, check the following items:

• Create a least privileged local account with which to run the SQL Server service. Use this account when you are prompted for service settings during setup. Do not use the local system account or an administrator account.
• Make sure you do not install SQL Server on a domain controller.
• Make sure you install SQL Server on a partition formatted with NTFS.
• Install SQL Server program and database files on a non-system volume, separate from the operating system.

Installing SQL Server

When installing SQL Server on a production server, choose the custom setup option. When you do this, you can selectively choose the items to install. You should not install the items listed in Table 18.2 on a production database server.
Table 18.2 Items Not to Install During Custom Installation
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

1. Download and install MBSA. You can do this from the MBSA home page at http://www.microsoft.com/technet/sec.../mbsahome.mspx.
   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 http://download.microsoft.com/downlo...s/mssecure.cab.
2. Run MBSA by double-clicking the desktop icon or selecting it from the Programs menu.
3. Click Scan a computer. MBSA defaults to the local computer.
4. Clear all check boxes apart from Check for security updates. This option detects which patches and updates are missing.
5. 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.
6. 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 "How To: Use the Microsoft Baseline Security Analyzer" in the "How To" section of this guide.
For more information about applying service packs, hot fixes, and security patches, see TechNet Security Center.
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 "Secure Your Developer Workstation" in the "How To" section of this guide.
Step 2. 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 "Step 4. Accounts."

Disable the Microsoft DTC (if not required)

If you do not use distributed transactions through the Microsoft DTC, disable the service.
Step 3. 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

1. In the Microsoft SQL Server programs group, start the Server Network Utility.
2. 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 "How To: Harden the TCP/IP Stack" 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 "How To: Use IPSec for Filtering Ports and Authentication" in the "How To" section of this guide.
Step 4. 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.
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

1. Start the Computer Management tool, and then expand Local Users and Groups.
2. Right-click the Users folder, and then click New User.
3. 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.
4. 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 "Step 10. SQL Server Security."
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 "Step 9. Auditing and Logging."
For more information about password policies, see password "Best Practices" on at http://www.microsoft.com/resources/d...rd_protect.asp.
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:
[LIST][*]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 "Remote Administration" 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. [INDENT] Note To support NTLMV2, clients must be running Windows 2000, Windows Server 2003, or Windows NT

[MentaL]

[RedMG]

How long does it takes you to write in guide? :P GREAT GUIDE DUDE....

[Fraps.]

I guess I'll go with the lazy way, but thanks for sharing

[ivo2296]

How long does it takes you to write in guide? :P GREAT GUIDE DUDE....

hehe it took me more time to search in forum if such a guide is already posted.

[limpamesa]

Nice guide. Very similar to Securing Your Database Server

[Ron]

.. or you can just use a firewall to block what you don't need.

[eladlevy5]

Oh My F*cking God!
who is gonna read all this lol..
hmm thnx but i will stay with the lazy people and firewall defence..

but still GREAT INVESTMENT!!!

[Fraps.]

Oh My F*cking God!
who is gonna read all this lol..
hmm thnx but i will stay with the lazy people and firewall defence..

but still GREAT INVESTMENT!!!

Welcome to the lazy guild :D

[deztroyr1]

this guide is way too long. i doubt anyone is going to read all this. i am not reading it for sure.... anyways.... you really made all this by yourself? doubt it -.-

[SHANG]

Welcome to the lazy guild :D

i also wana join </guild> :eek6:

[ivo2296]

you really made all this by yourself? doubt it -.-

You didn't ever read first sentence of the topic ?!?

[Fraps.]

i also wana join </guild> :eek6:

/Accept

[Hellvix]

Holy shit! Take a look at it. It's fucking great! Great job, man!

[razu3]

Great job... but over 60% of that information isn't related to "MuONline Help", but not bad. Ty for it :P

[ilioa]

I seriously consider of reading all that stuff, because despite I have firewall and I don't use connectserver but skyteam anticheat and I have changed connect ports:
I still have some way that outsiders are hacking my server time by time!

[Eyes]

.. or you can just use a firewall to block what you don't need.

Exactly!.

But good post!,

[loberia]

the best security is use router conection in you server, open the port necesary, is all is best than ready a looooonngggg guide

[Las3r]

Prefect copy paste from the Microsoft Page,

i think people would be better off by buying a good ifrewall and using it ;)

[ivo2296]

i think people would be better off by buying a good ifrewall and using it ;)

I don't know why you write in here - and if you read the guide, you will learn what to do with your firewall about SQL securing. The guide doesn't say people don't have to use firewall.

[HurryPoker]

greate job dude! i read this guide in 2houres!

[juliom]

very long to read.

[noobies]

Wooot nice guide dude,
Nice to see someone who takes effort making a guide ;)!