MSSQL and Security
MS SQL Server is installed as every other Windows software is - the installation software leads the administrator through the setup procedures. If the installation is to be performed more than once on multiple machines, however, it would help to create a file which would make all the required operations automatic. Choosing the Advanced Options and the Record Unattended .ISS file option on the first installation screen will allow you to save all of your subsequent choices in an ISS file. When such a file is created, all you have to do to perform a proper installation is type:
Setupsql.exe -s -f1 filePath [-SMS]
The SMS option defines that control is transferred to the command line no sooner than after the installation has been completed. This is a way of e.g. confining the whole operation to a batch file and running all the services and attaching all the databases in the very end. Patch installations may be "mechanized" this way too.
The SQL Server Desktop Engine is similar and the only difference is that instead of writing an ISS file, you have to define all the parameters in the command line.
The SQL Server and the SQL Server Agent are run as Windows services named: MSSQLserver in case of the default instance (or MSSQL$instancename in case of a named instance) and SQLServerAgent. As any other services, they have to be run under a defined Windows account. This account should be an 'ordinary' user account with interactive logon permission or even a special account with very limited authorizations. You should consider creating a proper account before installing the SQL Server so that the installation software gives the user the appropriate rights allowing him to run the service.
The account on which the SQL Server is run has to have the following rights:
- To run as a part of the OS
- To increase the shares
- To substitute the process level token
- To log on as a service
Moreover, it has to be allowed to access and modify files and directories such as:
- the SQL Server directory
- database files, i.e. .mdf .ndf and .ldf (usually ordinary mdf data-containing files are located on a different drive than the .ldf file which contains the database log).
Apart from that the rights for writing the following registry keys are necessary:
- HKEY_LOCAL_MACHINE \Software \Microsoft \MSSQLServer$istancename,
- HKEY_LOCAL_MACHINE \System \CurrentControlset \Services \MSSQLServer$instancename,
- HKEY_LOCAL_MACHINE \Software \Microsoft \Windows NT\CurrentVersion\Perflib.
After the installation, the account on which the SQL Server is being run should be added to the sysadmin role. You should consider if it was not useful to deprive the Windows administrators of logging on and instead attribute this right to a chosen 'administrating' account for the SQL Server. To take the authorizations away you have to execute the following line:
exec sp_revokelogin [BUILTIN\Administrators]
If you chose the integrated mode during the installation then the default sa account will have an empty password. The administrator, however, will be asked to set up a password for this account when installing SP3.
When choosing protocols, it would be good to turn TCP/IP off unless it's indispensable and use only the Named Pipes protocol. You have to remember that even though the Multi Protocol supports encryption, it uses Windows RPC mechanisms and, moreover, it only allows communicating with the default SQL instance.
The SQL database files may not be compressed, but it is possible to apply the Windows Encrypted File System (EFS) to them. That gives you additional, besides ACL, protection from unauthorized 'copying' of the file from the server. Microsoft claims that an ordinary encryption of the MDF and LDF files doesn't slow the database down by more than 5% so in the case of information you regard as critical you should consider using this device.
Don't forget to install all the patches, updates and additional HotFix packages (if available) once you have finished your installation of the SQL Server. At the time of writing the SP3 was the latest update.
In the SQL Server the user logs on to a server. Afterwards, a user in a particular database is attributed to a specified ID. The user may have rights on the level of the server as well as on the level of a particular database.
The server has pre-defined built-in roles which apply to the 'whole' instance of the database, i.e. the fixed server role as well as those adherent to a specified database.
The fixed roles worth mentioning are:
- sysadmin - allowed to perform any operation
- dbcreator - allowed to create databases
- bulkinsert - allowed to execute so-called inserting operations (BULK) used for adding large amounts of data when, for example, supplying a data warehouse
The database built-in roles are:
- public - the default set of authorizations
- db_owner - allowed to perform any operation of a specified database
- db_oddladmin - allowed to create or modify the new objects of the database (so-called DDL operations); you should take notice of the fact that the user doesn't have to have db_owner authorizations to do that
- db_datareader - allowed to read any table
- db_datawriter - allowed to write any table
- db_denydatareader - banned to read tables (an overt authorization takeaway)
- dDb_denydatawriter - banned to write tables
It should be mentioned that through choosing the latter four roles you may for example deny an application to access the tables directly and constrain it to perform all the read and write operations through stored procedures.
The user may be authorized to perform a certain procedure but not necessarily to perform any of the commands of which the procedure consists. This is a way of constraining extra checks before writing the database etc.
The server roles are nothing more than some kinds of patterns, which define particular sets of authorizations. Instead of using roles, you may attribute the rights you want manually.
You should bear in mind that if the user belongs to roles, which have a specified right, and to another from who the same right has been taken away, then the SQL Server would acknowledge that it couldn't perform the demanded operation. This situation is explained in figure 1.
The authorizations are set up with the use of TSQL commands such as GRANT/REVOKE or through extra stored procedures (as mentioned in the documentation in the Security Procedures section). It is possible to use the Enterprise Manager interface where the administrator has a 'table' with specified rights at his disposal.
Another important thing to remember is that if the administrator wanted to remove an SQL user immediately, he'd have to restart the proper instance of SQL Server.
By default the user is not authorized to perform UPDATE/INSERT operations i.e. to modify system tables directly. You should make sure that the allow updates option is set to 0. Moreover, remember that when allow updates is set to 1 and a storing procedure has been created, this procedure will still be allowed to update the system tables even though the possibility of direct modifications will have been blocked.
Note: changing allow updates to 1 requires performing the RECONFIGURE WITH OVERRIDE operation afterwards. This option starts working immediately.
You should also know that sometimes an administrator aware of what he's doing might like to change something in the system tables manually. Such a situation would be best coped with through running the server in such a mode in which a database is available for a single user only. To do this simply choose the appropriate option in the SQL Server Enterprise Manager or run sqlserv.exe -m.
In the 'single-user mode' only the first user will get access to the SQL Server, so before you shift the instance to this mode you should block the SQL Server Agent so that a periodic operation wouldn't accidentally block the only connection available.
Event after the most secure installation of the SQL Server the administrator should be aware of what is happening in his databases. The built-in audit device may be used, for example, to register events related to users' logons. In order to run this device you have to enter the SQL Server Properties and go to the Security tab. The default events will be saved in two places: in the SQL Server LOG and in the Windows Application log. The None level is chosen by default. That means that no information is logged. If you choose the Success option, than all the successful logons will be registered. In case you choose the Failure option, only data on logon errors will be saved. Finally the All option will trigger saving both types of events. To apply changes you'll have to restart the service.
In many cases registering events and logons/logouts is not enough. The administrator would often like to know precisely what a specified SQL Server is executing; it means he'd like to know what commands are being sent to the database.
The SQL Server may be configured in such a way that it meets the requirements of the C2 security certificate. It is however possible to turn C2 audit on independently from creating such installations. This kind of audit allows the administrator to monitor the details of any operation performed in a database.
In order to configure the C2 audit, you have to activate (in Query Analyzer or osql.exe) and run the following:
EXEC sp_configure 'show advanced option', '1'
EXEC sp_configure 'c2 audit mode','1'
The C2 audit will be run after the service is restarted.
The registered and logged events are:
- All user actions (i.e. all SQL commands and logons/logouts)
- DDL operations (i.e. defining new objects in a database, changing authorizations, configuring the server)
- Security-related events (information if the server allowed or refused to perform certain operations and adding and configuring new users' accounts)
- Administrating operations (creating/recreating a backup copy, BCP inserting operations or DBCC commands used for SQL Server troubleshooting)
- Operations performed on the SQL Server service; it should be mentioned that a halt of a service will be logged only if it is performed from the inside of the SQL Server; when the service is halted through the OS, only the system log covers this information
- Commencing/halting the audit
Information on particular records including the registered operations differs from each other. This information, however, will always be logged:
- Date and time of the beginning of the event
- The domain and username of who caused the event
- The user's SID
- The type, class and sub-class of the event
- Whether the operation was successful or not
- The SQL Server's name
- The name of the request sender's host
- The name of the application transferred in the connection chain to the database
- The SPID (Server Process ID) of the user's connection to the SQL Server
Figure 2: A log of the C2 audit.
The SQL tracing function supports 'scrolling' the file after it exceeds 200 MB. It creates a new file whose name fits the following scheme: "MSSQLinstancename\Data\audittrace_yyyyMMddhhmmss.trc". In case of a failure you won't lose more than 128kB of the audit log. If you run out of drive space, the audit stops automatically. When the service is running, the last 200-MB file is constantly blocked by the SQL Server. No sooner than after it's filled (or the service is stopped) the possibility of browsing it disappears.
The C2 audit registering every one of the operations performed in the database soon becomes expensive. It gives you an in-depth look on what is being performed in the system yet it slows it down. Another thing to do is to specify the events you're interested in and define your own audit rules instead of using the C2 audit. All you're going to need to do this is the SQL Server Profiler tool. This tool allows you to specify which events you're interested in and set up filters, for example in order to have only the databases of utmost importance monitored. To improve your proficiency with the tool you should read the "Monitoring with SQL Profiler Event Categories" chapter from the Server's documentation. This chapter contains a wide description of the elements that might be registered in a log. You also should take a glance at the sp_trace_* stored procedures which allow performing the same operations the user interface does, as audit and operation registering and which are an integral part of a database. In SQL Server you may also choose to configure certain stored procedures in such a way that they're run when the service starts up. In SQL 6.x the function responsible for that was called sp_makestartup. In SQL 2000 you may use the sp_procoption:
EXEC sp_procoption 'Procedure', 'startup', 'true'
After you have executed this command, the Procedure will be run automatically after the SQL server starts up. If the procedure code contains auditing commands, the audit will be run together with the database engine with no need of running the SQL Server Profiler GUI.
Figure 3: A list of SQL Server-monitored events
The SQL Server Profiler is also a tool of analysis of the audit results. It also allows saving the outcome of the audit loaded as a table in one of the SQL Server databases. All you have to do is choose the Save As option in the File menu tab. Those operations allow you to use the TSQL for analyzing the audit log.
It has to be emphasized that audit is a powerful tool, allowing the administrator to find numerous threats to the system, but on the other hand a complete C2 audit contains all the information written in databases. Losing them may have dangerous consequences, so the audit files should be properly secured.
It's worth paying attention to two important matters concerning restoring data from a backup file. You shouldn't allow the users to restore other databases than their own ones. If someone who's not the owner of a database opens it, it will become inaccessible to the owner. Such situations occur when user A is a database owner and user B is granted by user A an authorization to make a backup or to restore a database. If user B is allowed to perform the CREATE operation and he makes a backup of the database and then restores it as a new database, he'll become the new owner of the database, yet the tables will still belong to user A.
If the WITH DBO_ONLY option is included in the RESTORE DATABASE expression, then user A (the tables' owner) won't be allowed to access the tables in the restored database until he is publicly authorized with the use of the DAC. It should be mentioned, too, that when restoring a database on a remote server, it is handy to run the following procedure:
EXEC sp_change_users_login 'Auto_Fix'
EXEC sp_change_users_login 'Update_One', 'account', 'newAccount'
This will link the server logon passwords with the accounts, which exist in the restored database. In other words - you'll have to recreate all the user accounts. If you use the SQL Server on the Windows 2003 Server OS, then you have the Shadow Copy mechanism at your disposal. It allows you to create a copy of the whole drive, including blocked MDF/LDF files. It will work, however, only if the database restoration mode is not set to Full, whereas it is set to Simple mode in which the confirmed transactions are automatically cut off. The patch, which is available through the PSSS, removes this inconvenience.
Multiple simultaneous instances
Sometimes, on either business model (hosting with 'separate' servers) or application security reasons multiple instances of the SQL Server are run simultaneously on one physical server. In a standard installation they share the server's resources. There exist some advantages of such a situation: the systems itself keeps an eye on the instances to make sure every one of them has access to resources. Nevertheless, some hosting agreements require that the resources be precisely attributed to a specified instance. During the configuration process of an SQL Server service you may define the maximum memory use and 'bind' the database engine to whichever processing units of the system you choose. In order to configure such a mechanism you should do the following:
1. log with the use of Query Analyzer or osql.exe on an instance of your choice
2. turn the advanced options on
EXEC sp_configure 'show advanced option', '1'
3. set the maximum memory size by changing the max server memory parameter
(to be expressed in MB)
EXEC sp_configure 'max server memory', 256
4. define an affinity mask
EXEC sp_configure 'affinity mask', 12
Figure 4: Multiple simultaneous instances on Small Business Server 2003
The SQL Server sees HyperThreading CPUs as two-processor systems. In other words, if you have a 2-CPU HT-technology server, the SQL sees four CPUs. Setting up these options surely won't eliminate all the aspects of resource sharing. Various instances share the same I/O channels, the same OS instances, the same system interface etc., yet the most common conflict sources, i.e. memory, CPUs and drives, are eliminated and you may feel assured that any software running on one of the instances won't affect the functioning of other systems.
A reliable and secure application
Even the most secure server has to be functional and it has to be capable of running databases supporting the applications. Not only should the server then be secure, but also its usefulness and reliability should be singling out. What is meant through using the word 'reliability' is not the fact that the SQL Server will be working 24/7 but that a specified solution (i.e. an application which uses the infra-structure) is available at any time or is unavailable for periods short enough to be unnoticeable to users. Three factors are crucial when planning reliable applications:
- human resources
- processes (i.e. the proceeding procedures)
Let's take a closer look at the statistics, which analyze various crackups' reasons. Microsoft Corporation claims that 40% of the bugs are those residing in the application itself (bad testing, limited error reports, bad scalability). Good technology is not enough; you have to know how to apply it and how to adjust it to your needs. The subsequent 40% of bugs are the administrator's faults as the administrator is not infallible, lacks proceeding patterns for emergencies or doesn't have security backups etc. The final 20% come from the platform, i.e. the OS, routers and network connections. The user however doesn't care which of these elements fails - any failure means that some application becomes inaccessible. If you consider assuring high reliability, the first thing you're probably going to think of is the Microsoft Cluster Service. Those are so-called 'failover' clusters in which in case of a crackup, the MSCS would transfer the application and burden to another node. From the client's point of view nothing changes - the server's name and IP address remain the same. Switching, however, may last up to 30 seconds. Let's find out what does 'switching' mean exactly.
'Switching' is a transfer of the operation to another computer; the connections are interrupted and transactions are disclaimed. The data has to be resent and the transactions have to be re-executed on the new node; how much is to be done depends on the time that passed from the last of the transaction's checkpoints. If the interrupted transaction was a 'long' one, the checkpoint took time long ago so the switching may take much more than 30 seconds.
Another solution is the LogShipping mechanism, which is very often easier to implement and apply. You may create a schedule of sending the transaction log to another server with the use of the Database Maintenance Plan Wizard. Such events are scheduled for every five minutes by default but in some cases you may set it for one. It should be emphasized, however, that if you use this synchronizing device, you have to invent a way to transfer the lacking data to the server in case an accident occurred. Another way of creating a mirror server is a replication mechanism, for example a Snap Shot type mechanism. However, if that is not possible, the log file transferring mechanisms will be much more convenient.
You could ask why is the MSCS superior to Log Shipping? Firstly, the MSCS switching time is relatively short and there's no need of transmitting some unsent data manually. In other manual synchronization mechanisms it may always happen that the most convenient way of transfer is re-entering the data by the user. In addition to that the application has to 'foresee' that its address and server name are going to change. Such solutions still do have some advantages because there exist read-only servers whose information is always up to date and which may be used (if everything is working fine) as servers destined for analytical operations.
You should know that Yukon (another version of the SQL Server) would probably have a mechanism whose Log Shipping 'backup' server creation will be even more automatic. Nonetheless, no matter how secure your SQL Server and the platform are, an unskilled 'legal' user may be the most detrimental element of the system, together with a faulty application and lack of planning and procedures.