We continue our three-part series on protecting your database server with this follow-up to my article "Empirical Hacker --
Toss a quarter and what do you get? Obviously, you'll have a 50% percent chance that it will be heads and a 50% chance it will be tails. Is the security of your network left to chance? Are you vulnerable to a breed of vipers who slither in and out of your network, ready to attack?
When was the last time you consistently stored and monitored your log files off-site?
Store and monitor your log files off-site
Put a mechanism in place to move, without using IPC, your production server log files off-site to a central repository location (or server). By doing this, you will limit the hacker's ability to remove any evidence (or footprints) recorded in your log files. A better solution would be to set up your events or audits to report automatically to an off-site server and then immediately rename the log files.
Failure to consistently store and monitor your log files off-site will provide the hacker unlimited time to clear his steps. This tip applies especially to your production network logs (such as those for your perimeter router, firewall, servers, load balancing and cluster devices).
SQL Profiler -- Defining your security audit trace template
Let's begin by creating a security audit trace template with a list of security events to monitor on your SQL 2000 server. In Profiler, go to File+New+Trace Template and log in. Go to the Events tab and add Security Audit, Sessions and Objects to list of event classes (in the pane on the right). Below is a sample list of the events you should consider monitoring when defining your security audit template:
Audit Add DB User Event - logs creation or deletion of database users Audit Add Login to Server Role Event - creation or deletion of log-in to server role Audit Add Member to DB Role Event - creation or deletion of member to database role Audit Change Audit Event - logs audit changes Audit Login Change Password Event - logs SQL log-in password changes Audit Login Failed - logs client log-in attempts Audit Login GDR Event - logs Grant, Deny and Revoke (GDR) Windows log-in rights Audit Object GDR Event - logs Grant, Deny and Revoke object permissions Audit Server Starts and Stops - logs start, pause and shutdown activities Audit Statement GDR Event - logs Grant, Deny and Revoke statement permissions ExistingConnections - logs users connected to server ObjectsObjects:Created - logs the object accessed (select, insert, delete) ObjectsObjects:Deleted - logs the deletion (drop index, drop table) of an object
After adding the above events, go to the Data Columns tab and add DBUserName, LoginName, Permissions, TargetLoginName and TargetUserName; then go to the General tab, click on Save As, and assign a name (for example, s3c-aud1t-t3mp) for your template and store in a custom path. Now you are ready to create a trace file with your new custom template and save it to either a file or database table.
Although my preference is to save the trace (audits) to a file and then transfer the file to an off-site server every 10 minutes, saving to a database table will work too. However, a table-based solution -- for example, populating a table, scheduling a dump and moving the dump -- will require more effort than a file-based solution, not to mention that using a file will consume less T1 bandwidth and require less time to transfer off-site.
Avoid letting database passwords become common knowledge with administrators
As the database administrator, you must restrict your database passwords from becoming common knowledge with network administrators. This task will be difficult to achieve if you decide to use Windows Authentication Mode, because the account name and password will not be stored on the database server.
Windows Authentication Mode vs. Mixed Mode (SQL authentication)
During SQL 2000 setup, Windows Authentication Mode is the default choice. This mode is recommended by Microsoft because it supports trusted (relies on authentication at the AD or Domain level) connections between the client and the database server. Mixed Mode -- AKA SQL authentication -- supports non-trusted connections; that is, the log-in name and password are stored locally on the database server and a connection from a non-trusted client will be accepted, as long as the correct account name and password is provided.
If you choose Mixed Mode, you will also notice that the password for the SA account is optional. Regardless of which mode you choose, you will need to set a unique password for the SA account. My preference is to use SQL authentication. I'll explain why:
The problem I see with using Windows Authentication Mode is that if -- or when -- a hacker obtains your domain administrator password, he or she will also have access to your database server. In the meantime, the database passwords will be common knowledge and shared among network administrators.
I suggest that this password be limited to a primary and secondary DBA. Therefore, I prefer the extra level of protection by enforcing a separate complicated password for the SA account, one that is not shared with network administrators, to avoid the "too many hands in the cookie jar" syndrome. Depending on your environment, you may choose to go with SQL authentication instead of Windows authentication.
Implement intermediary security between your clients and database server
One of the main advantages of using Windows Authentication Mode is that only authenticated clients can establish a trusted connection to your database server. But, the mode is not designed or intended to protect your database server, as would the implementation of a secondary firewall, or a router with appropriate ACL.
The absence of an intermediary security device between your clients and database server is an overlooked security area that should be addressed by your database administrator and IT staff, sooner rather than later. In the absence of security, there's an opportunity for intrusion.
Data Sources (ODBC) and Data Source Name (DSN) configuration
Creating a User or System DSN in Microsoft ODBC Administrator on your Web servers is a straightforward process. The important thing here is to configure, in Client Configuration, each DSN with TCP and the non-default port of your database server (other than port 1433) and enter the appropriate account name and password. Make sure you are using the most stable and secured version of MDAC and record the version number (see About). Select the box "Change the default database to" and enter your database name. If you want to test your access to your database server using Microsoft's NorthWind database, select it form the list of databases and enter the password for the SA account. If you are using NamedPipes, your client will require an IPC connection to the database server, which I don't recommend for security reasons. If you recall, in part one of "Protecting your database server," I covered the importance of disabling unnecessary ports, especially in your production servers.
Define a value other than the default unlimited value
One of the most overlooked settings is the Maximum Concurrent User Connections; the default value of zero represents unlimited connections. The problem is that your database server most likely can't handle unlimited user connections, and if a hacker (or erratic procedure) managed to initiate these connections, it would bypass the security of your server and render your database useless. Accepting unlimited connections also implies that your staff does not have a proper understanding or realistic estimation about the existing capacity or future expectations for user connections.
The goal is to secure your server using realistic values and not default values, which often defeat the purpose of having security in the first place. Performance benchmarks of your existing application should provide the statistics you need to determine the maximum number of user connections that your database server can reliably handle.
Enterprise Manager -- Defining your security configuration
Select Properties for your database server listed in SQL Server Group in Enterprise Manager. Go to the Security tab and set Failure on Audit level to record failed log-in attempts; then go to the Connections tab and disable Remote Server Connections, which by default allows other SQL servers to connect via Remote Procedure Call (RPC) to your database server. Enter a value in Maximum Concurrent User Connections and don't accept the default unlimited value. Go to Server Settings tab and select the appropriate behavior of your server, then go to the Active Directory tab and add the instance, if you elect to integrate SQL with AD.
Below is the second part of my SQL Security Checklist. In my third part of the "Protect your database server" mini-series, we will focus on role-based security, SID and GUID, permissions and delegation.
SQL security check list, part two
- Implement a mechanism to automatically store production log files off-site without using IPC connections.
- Monitor your log files on a daily basis for suspicious or failed attempts.
- Don't settle for unlimited default values for present and near-future connections; specify a realistic number.
- Implement intermediary security (I can't emphasize this enough) between your clients and database server.
- Carefully consider which authentication mode is appropriate for your environment. If you are the DBA or a network administrator, the decision is simple.
Remember that the purpose of my articles is to explore the various areas in your network and bring to the surface any security vulnerabilities that have been overlooked. My intention with this article is not to tell you how to set up a database server, but to point out the areas you should be aware of when configuring a SQL server.
Did you pass the test? Does your SQL server configuration already include the security tips in this article? If yes, kudos! It's been my experience that some of these basic steps have often been overlooked by experienced IT professionals. If you're serious about security, you must pay attention to details and leave no room for hackers.
Please write to me and let me know if this article has brought to light any potential weak links in your network.
Luis Medina is the author of "The Weakest Link Series," which offers network managers an opportunity to identify ongoing network security issues. Luis also answers security questions in SearchNetworkings Ask the Expert section. Submit a security question to Luis here or view his previously answered Ask the Expert questions.
This was first published in August 2002