Security | Microsoft SQL Server 2000 High Availability

The most basic ”and arguably the most important ”thing you can do from an administrative standpoint is to secure your Microsoft SQL Server 2000 instances. Beyond the obvious steps of putting your servers in racks, locking cases, and physically securing data centers (all of which were discussed in Chapter 2, The Basics of Achieving High Availability ), how can you specifically secure things at the operating system and SQL Server layers ? You need to address multiple threats. A few years ago it might have been good enough to throw up a firewall and make sure that your SQL Server was isolated. Unfortunately, the game has changed. Worms, viruses, and denial of service attacks ”some of which are directed at your database, others of which are not ”can compromise your data s security.

When a Web site pops up on the evening news in a story about how it was hacked, it is a glaring example of how something can be compromised. But how do you measure what was compromised ” especially sensitive data? Because many systems now access both internal and external systems, the potential for someone gaining unauthorized access increases dramatically unless you have dedicated security professionals making sure that you are protected. There have recently been cases where hackers have broken into an e- commerce site or into a bank and compromised large numbers of credit cards. Or think about the recent rash of identity theft. Do not assume it cannot happen to you or your systems. As important as your platform s availability is, it is your data s availability and security that you are really protecting. However, the reality is that sometimes security and availability do not always play nicely together in the sandbox, and you might have to make tradeoffs one way or the other.

Securing Your SQL Server Installations

You can do several things to ensure that your physical SQL Server installations are secure.

Do not use a blank or weak sa password. For obvious reasons, this can compromise your SQL Servers. Even before SQL Server 2000, it was a common practice in testing or development environments to use the SQL Server s sa account with no password in testing or development environments. The unfortunate thing is that the practice followed into production and was never altered .

Passwords for all accounts ”whether or not they are SQL Server and from system administrators on down to users ”should not be weak passwords. Since there is no direct way to enforce stronger passwords, a weak or a blank password can be a problem if you configure your instance to only use SQL Server security. The recommended approach is to set SQL Server to use Microsoft Windows Authentication and to require your applications to use it as well. This allows you control at the Windows level. For example, you can create a group in Windows and place users in it. In SQL Server, you can then assign a login to that group and add that login as a user to a database. Now, when you want to have someone gain access to the database, your administrators can do it in a standard way. In addition, you can force your Windows users to change passwords on a periodic basis, you can enforce strong passwords, and you can take other measures to ensure that you are protecting your data.

Important

Even if you use Windows Authentication, you must still set a strong password for the sa user. If you just select Windows Authentication, the sa user still exists in SQL Server, but a password is not set. Also, if you later change to Mixed, you might need to use sa.

The service accounts used in your environments, especially ones for SQL Server, should not have unneeded privileges, nor should SQL Server share the same account (with the same password) with every other application on the server. This means that the use of accounts like LocalSystem or a domain administrator is strongly discouraged. That way, if someone accesses your SQL Server, that person cannot then turn around and use something like xp_cmdshell to attack the rest of your environment.
Install SQL Server only on NTFS partitions.
Never install SQL Server 2000 instances on a domain controller unless it is absolutely necessary. Doing so might expose your data to attackers who can gain escalated user permissions through some other method.
Stay abreast of security patches, hotfixes, and service packs . As noted in Chapter 13, you should evaluate all updates to see if they need to be installed; if they enhance your security, apply them!
Where possible, set your SQL Server instances only to Windows Authentication, sometimes known as Integrated Security. Doing so permits centralized account management and the use of other protocols such as Kerberos. If you use SQL Server authentication, use Secure Sockets Layer (SSL), at a minimum, to encrypt the login packets as well as strong passwords (which you must enforce yourself). Never use blank passwords.
Turn on auditing in SQL Server. Although this might cause overhead, you can track failed login attempts and then take care of the problem. This is done by setting the Audit Level in the Security tab of your server properties, which is shown in Figure 14-1. Setting it to Success records successful logins. Failure records unsuccessful logins. Setting it to All records both.

Note
Turning on the auditing feature will cause you to cycle your SQL Server, causing an availability outage .
You are strongly advised to use Enterprise Manager to change your service accounts, as there are file system and registry dependencies that go along with a change in service accounts. You do this in two places:
- SQL Server service account You change this in the Security tab of your instance s properties.
  
  Figure 14-1: Security tab of Properties.
- SQL Server Agent service account You change this in the General tab of the SQL Server Agent (see Figure 14-2), which is in the Management folder in Enterprise Manager.
  
  Figure 14-2: The General tab of SQL Server Agent Properties.
If you do not change the passwords in the recommended way in Enterprise Manager, it might be possible to use the Services utility. The problem is that doing it there will not reset the correct registry keys or NTFS permissions in all cases. For a full list of what you would have to alter after using Services, see Knowledge Base article 283811, HOW TO: Change the SQL Server Service Account Without Using SQL Enterprise Manager in SQL Server 2000 (available at http://support.microsoft.com/ ).

Warning
You can use only the Windows-level Services utility to change password accounts on nonclustered SQL Server instances.

Important
Changing your service account passwords will require you to stop and start the associated SQL Server services. So you must plan for this availability outage.

Use Server Network Utility to change the port to a static port that is known only to you. By default, the first instance installed on your server or cluster will try to take port 1433, which is a known port for SQL Server and can potentially be exploited. To access Server Network Utility, you can find it in the Microsoft SQL Server folder of the Programs menu. Once it is started, select your instance in the drop- down list, select TCP in the Enabled protocols list, and then click Properties. You can then modify the port number (see Figure 14-3).

click to expand
Figure 14-3: Modifying your port.

Important

Two instances on the same server cannot share the same port number. If you are using a version of the Microsoft Data Access Components (MDAC) prior to MDAC 2.6, you might need to physically put this port number in the connection string, as it will not be 1433.

Avoid using User Datagram Protocol (UDP) port 1434 at a firewall if possible. This is the SQL Server listener.
Use the Microsoft Baseline Security Analyzer ( http://www.microsoft.com/technet/security/tools/Tools/mbsahome.asp ) to help you assess your environment, including SQL Server. It will scan for known things such as blank sa passwords, file and registry permissions, exposure of xp_cmdshell to users who are not system administrators, and more. Version 1.1 or later supports multiple instances of SQL Server.
Do not configure unnecessary features, tools, and utilities, and do not install more software on your SQL Server machine than you need to.
Never change the default permissions on xp_cmdshell. Restrict the people who can have access to this extended stored procedure by not configuring users as system administrators.
Never allow anyone to access your SQL Servers directly. Using a method such as Terminal Server will allow you not only to see who is logging into the machine, but also to prevent physical access to the server.
You can use SQL Profiler to audit events. For information on how to do this, see SQL Server Books Online, available with the SQL Server installation.
Scan and remove logins with NULL passwords. On a regular basis, evaluate and remove old and unused logins; this might prevent unauthorized access.
Make sure that when you are assigning roles, you trust the user that you are giving privileges to. Although this seems obvious, the point is to put thought into what roles you assign to users.
Secure your startup procedures.
Always verify your file and registry key permissions, as outlined in the earlier Knowledge Base article 283811.
After you install a service pack or an instance (or something else), passwords might be exposed in the installation or log files. To scan those files and remove any potential offending exposures, Microsoft Product Support Services (PSS) has a tool called KillPwd that you can download from http://download.microsoft.com/download/SQLSVR2000/Utility/2.0/W98NT42KMeXP/EN-US/killpwd.exe . For instructions on its use, consult Knowledge Base article 263968, FIX: Service Pack Installation May Save Standard Security Password in File.
SQL Server supports the encryption of your file system. As of Windows Server 2003, it is also supported with SQL Server 2000 failover clustering. Using it will decrease the chance of someone using a text tool to read your data or backup files, but you must account for any system overhead incurred.

You can enable C2 security with SQL Server 2000 if it is necessary.

More Info

To see how to use C2 with SQL Server 2000, see the SQL Server 2000 C2 Administrator s and User s Security Guide at http://www.microsoft.com/technet/treeview/default.asp? url=/technet/prodtechnol/sql/maintain/security/sqlc2.asp .

Securing Your SQL Server “Based Applications

As a database administrator (DBA), or as someone who will be performing database- related tasks , whether you like it or not, you are responsible for every application s database in your environment. Although you might control the back end, developers control the front end. It is in your best interests to validate custom database-specific code and ensure that packaged applications will work with your security model. Here are some tips to consider:

When coming up with the architecture for your application, how will you access the database? You have essentially three options: flowing the user to the database, using a single Windows context to the database, and using a single connection to the database using SQL Server authentication.

If you use flowing, all machines must be part of the same domain or be trusted, Kerberos and delegation must be enabled, and impersonation must be enabled in ASP.NET. This will allow you to enforce security for SQL Server and audit all user actions. If, however, you have external components (such as outside vendors accessing the application), this might not be feasible , and connection pooling is limited since you cannot share connections.

If you use Windows Authentication, run ASP.NET as a nondomain administrator. The users would authenticate at the application component, and the connection to the database is made in the context of the ASP.NET account (usually ASPNET user). This account should not have a system administrator role in SQL Server. With this method, you do not have to store passwords or pass any credentials to SQL Server, connection pooling is possible, and running ASP.NET as a low privileged account will minimize risk.

If you are using SQL Server authentication, the application is using SQL Server to authenticate your login. You now need to ensure that all logins have the correct privileges at the SQL-level and to force users to enter strong passwords. You can use secure credentials at the middle tier using data protection application programming interfaces (APIs) to encrypt the credentials, which means that only that account can decrypt. The problem with this method is that you now need to store credentials, and SQL Server authentication, by its nature, might not be as secure as Windows Authentication. But you can work across firewalls and nontrusted domains and use connection pooling. If you do not have a specific limitation in your application, it is highly recommended that you design and deploy using Windows Authentication to grant user access and privileges.
If your company is writing an application (or has hired contractors to write one), make sure that they have the skill and experience to implement your company s security policies through the use of things like correctly privileged logins and roles. No single user should be doing everything in the application. At the very least, you should have a separate user and administrator account. Since many developers install SQL Server on their machines, they incorrectly assume that all database access requires credentials equivalent to a highly privileged administrator.
Some developers might hardcode the sa user directly into the application, which is not only bad for security, but which also might hurt you if you need to use another server for a warm standby. Do not allow a developer to hardcode passwords, ever!
For a security model, Windows Authentication should be your first choice, if possible. Because the control is now at the network level, not in the application, your application is, by default, easier to secure. Passwords are stored in one place (at the domain controller), and you can combine Windows Authentication with some sort of encryption to secure your connections. Active Directory directory service can assist you in centrally managing your passwords.
If possible, use some form of encryption (Internet Protocol Security [IPSec], SSL, Kerberos, or other forms of encryption, such as data encryption with third-party tools like Protegrity) in your application or database. If you are doing things like passing credit card numbers, performing online bank transactions, sending Social Security numbers, and doing other sensitive operations across a network, you do not want to transmit these as clear text in an HTML stream that could be sniffed or found in a log somewhere.
Know your connection method. If you do your best to encrypt data, but your underlying protocol winds up sending passwords and SQL statements through text, you need to mitigate this somehow by using another form of encryption like SSL, IPSec, or Kerberos to encrypt the connection as well as the underlying data access protocol.
When designing the schema, make sure that roles, and not users, own all objects. If, for some reason, the user is dropped or renamed , you will not have any issues of access to the object.
Use ownership chaining to mask or hide the actual schema by using views and stored procedures, and not direct SQL statements, to go against base tables in your database. If the object and the user calling it have the same owner, the underlying permissions check is skipped . This not only makes your code secure, but more efficient. Consider the following example:

You have an Orders table in your sales database created by User1. To access it, User2 creates a stored procedure to retrieve data from the table, which also includes some potentially sensitive information. If User3 tries to execute User2 s stored procedure, SQL Server will check both the execute permissions for the procedure as well as the SELECT permissions on the underlying table created by User1. However, if you created the stored procedure with User1 and granted execute permissions to User3, since User1 is the owner of both the table and the stored procedure, only one permissions check is needed.
Perform code reviews to ensure that the code is safe to implement (like making sure your application is available and scalable, among other things). A code review should be a formalized , regular process. Similarly, your company should develop coding standards that can govern the creation of code.
Developers will often put debugging code used for testing or messages into the application that might be accidentally exposed or be too technically informative to an end user. You should remove the information that is presented (such as account names , passwords, Internet Protocol [IP] addresses) prior to rolling the application out in production.
The application should also disallow any ad hoc queries. A savvy person might use a text field he or she knows is issued as part of a SQL statement and use it to do something malicious. You should validate all user input and reject anything that does not meet the standards of your company s security policy. Along the same lines, if you can, attempt to avoid using dynamic SQL statements within a stored procedure.
Although focusing on code is important, developers need to also think about how the application could be compromised through something like a denial-of-service attack or exposing problems ( publicly known or not yet known) through standard interfaces. For example, if a hacker gains access to your Web server, that person might now have access to your entire enterprise. How you have your security model will determine how far the hacker will be able to get. To combat this, ensure that your operations people who are doing monitoring know how to interpret a seemingly harmless blip on the monitor.