Worlds Largest Database
Ever wondered what the worlds largest database sits on ???
MSSQL – Data Cache
The data cache is usually the largest part of the buffer pool; therefore, it’s the largest memory consumer within SQL Server. It is here that every data page that is read from disk is written to before being used.
The sys.dm_os_buffer_descriptors DMV contains one row for every data page currently held in cache. You can use this script to see how much space each database is using in the data cache:
SELECT count(*)*8/1024 AS ‘Cached Size (MB)’
,CASE database_id
WHEN 32767 THEN ‘ResourceDb’ ELSE db_name(database_id)
END AS ‘Database’
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY ‘Cached Size (MB)’ DESC
The output will look something like this (with your own databases obviously):
Cached Size (MB) Database
3287 People
34 tempdb
12 ResourceDb
4 msdb
In this example, the People database has 3,287MB of data pages in the data cache.
The amount of time that pages stay in cache is determined by a least recently used (LRU) policy.
The header of each page in cache stores details about the last two times it was accessed, and a periodic scan through the cache examines these values. A counter is maintained that is decremented if
the page hasn’t been accessed for a while; and when SQL Server needs to free up some cache, the pages with the lowest counter are flushed first.
The process of “aging out” pages from cache and maintaining an available amount of free cache pages for subsequent use can be done by any worker thread after scheduling its own I/O or by the lazywriter process, covered later in the section “Lazywriter.”
You can view how long SQL Server expects to be able to keep a page in cache by looking at the MSSQL$<instance>:Buffer Manager\Page Life Expectancy counter in Performance Monitor. Page life expectancy (PLE) is the amount of time, in seconds, that SQL Server expects to be able to keep a page in cache.
Under memory pressure, data pages are flushed from cache far more frequently. Microsoft recommends a minimum of 300 seconds for a good PLE, but for systems with plenty of physical memory this will easily reach thousands of seconds.
The database page read to serve the result set for our SELECT query is now in the data cache in the buffer pool and will have an entry in the sys.dm_os_buffer_descriptors DMV. Now that the Buffer Manager has the result set, it’s passed back to the Access Methods to make its way to the client.
Optimising your SQL !!
When an application submits a SQL query to the database server, the server first parses the SQL. It checks the SQL syntax, for security access and prepares the query for execution. Query optimization involves determining the optimal path for executing the query. Each database comes with built-in intelligent algorithms to figure out the best possible way to execute the query. For complex queries involving joins between eight different tables, the optimizer could spend as much as 30 minutes to find an effective execution path before the server actually executes the query. The server uses either a cost-based optimizer or a rule-based optimizer to figure out the best execution path for the query. This article details more about the cost-based optimizer, as rule-based optimizers are becoming deprecated.
A cost-based optimizer tries to estimate the cost of satisfying each execution plan. The cost is based on the estimated number of database reads required as well as on the requirements for sorting. The lower the cost means the better use of system resources. However, lower-cost queries do not guarantee faster execution.
Once the server parses a query, it keeps it in the shared pool. That way, when the same query is submitted again, the server need not parse it. The best use of this feature is using the bind variables for frequently run queries wherever applicable.
Hints for Your SQL Statement
Hints are instructions that you include in your SQL statement for the optimizer. Using hints, you can specify join orders, types of access path, indexes to be used, and the intended optimization goals. You must place the hints within /*+ */, and you should place them after the SELECT key word.
Note: If a user doesn’t follow the syntax, the optimizer will not prompt the user with a syntax error. Instead, it will treat it as no hint.
Optimizer Modes
The optimizer goal determines the overall approach the optimizer takes in determining an execution plan. The following statement returns the rows as soon as it finds a few:
SELECT /*+ FIRST_ROWS */ distinct customer_name FROM
customer
However, the following query waits until all the rows are retrieved and sorted before returning them to the client:
SELECT /*+ ALL_ROWS */ distinct customer_name FROM
customer ORDER BY customer_name
You can set the optimizer modes at the session level or at the query level. PL/SQL procedures that run multiple queries would need the session-level setting.
Index Hint
Indexes play a very important role in SQL tuning. Indexes allow the table data to be indexed and organized, which in turn enables faster retrieval. Merely creating an index does not speed up the query execution. You must make sure that the query’s execution plan uses the hinted index. In the following query, when the optimizer uses the index hint, it will be forced to use the specified index for the search on last_name:
SELECT /*+ index(cust_table_last_name_indx) */
distinct author_names FROM devx_author_names WHERE
author_last_name =’DON%’
When you do a explain plan on this query, you will see the optimizer using this index. You can also instruct the optimizer to choose between a subset of indexes using /*+ index( indx1, indx2) */.
Note: Creating the index does not speed up the query execution. The index needs to be analyzed. The syntax for analyzing the index is:
analyze index compute statistics;
Join Queries
If the query involves joining two or more tables, the database server provides various hints to speed up the queries. A typical join query involves performing a search of the inner table for each row found in the outer table.
For example, suppose table A has 100 rows and table B has 1,000 rows. Logically, the query would run faster if for each row from table B it did a lookup for a matching row in table A. The opposite join could take as much as 10 times longer to execute.
SQL Server 2008 Build List
Below shows the SQL Server 2008 build list compiled by SQLServerCentral !!!
—————————————————————————————————————–
All builds are listed in reverse order, so the newest are at the top and the earliest at the bottom. You can find your build number with:
select @@Version
in a query window. This gives you the current version you are running and it should match up with one of the builds listed below.
Major Builds
| Version | Build | KB Article |
| Service Pack 2 for SQL Server 2008 | 10.0.4000.00 | 968369 |
| Service Pack 1 for SQL Server 2008 | 10.0.2531.00 | 968369 |
| RTM | 10.0.1600.22 | N/A |
Note that Service Packs include all builds below that Service Pack level. So any builds listed below Service Pack 1 are included in Service Pack 1. Those below Service Pack 2 (including Service Pack 1), are included in SP2, etc. Hotfixes may or may not include updates below them because there are two trees of code. The cumulative updates do include all hotfixes before them.
Build
| KB Article | Title | Description | |
| 10.0.4279.00 | 2498535 | SQL Server 2008 SP2 CU 3 | CU#3 for SQL Server 2008 Service Pack 2. Fixes include issues with Merge replication, mirroring suspension issues, various T-SQL issues among others. |
| 10.0.4272.00 | 2467239 | SQL Server 2008 SP2 CU 2 | CU#2 for SQL Server 2008 Service Pack 2. Fixes include recovering LOB pages from failed inserts, custom resolver issues in replication, and issues with online index rebuilds among others. |
| 10.0.4266.00 | 2289254 | SQL Server 2008 SP2 CU 1 | The first CU after SQL Server 2008 Service Pack 2. |
| 10.0.4000.00 | 2285068 | SQL Server 2008 SP2 | The second service pack for SQL Server 2008. |
| 10.0.2816.00 | 2497673 | Cumulative Update 13 for SQL Server 2008 SP1 | The 13th rollup of patches for Service Pack 1. Fixes include mirroring issues, Agent scheduling issues, and various T-SQL fixes. |
| 10.0.2808.00 | 2467236 | Cumulative Update 12 for SQL Server 2008 SP1 | The 12th rollup of patches for Service Pack 1. Fixes include clustering IPv6 problems, join performance fixes, and SSAS crashes. |
| 10.0.2804.00 | 2413738 | Cumulative Update 11 for SQL Server 2008 SP1 | The 11th rollup of patches for Service Pack 1. Fixed include fixes for too many VLFs during recovery, MDX issues in BIDS, and a few analysis services fixes. |
| 10.0.2799.00 | 2279604 | Cumulative Update 10 for SQL Server 2008 SP1 | The tenth rollup of patches for Service Pack 1. |
| 10.0.2789.00 | 2083921 | Cumulative Update 9 for SQL Server 2008 SP1 | The ninth rollup of patches for Service Pack 1. |
| 10.0.2775.00 | 981702 | Cumulative Update 8 for SQL Server 2008 SP1 | The eighth rollup of patches for Service Pack 1. |
| 10.0.2766.00 | 979065 | Cumulative Update 7 for SQL Server 2008 SP1 | The sixth rollup of patches for Service Pack 1. |
| 10.0.2757.00 | 977443 | Cumulative Update 6 for SQL Server 2008 SP1 | The sixth rollup of patches for Service Pack 1. |
| 10.0.2746.00 | 975977 | Cumulative Update 5 for SQL Server 2008 SP1 | The fifth rollup of patches for Service Pack 1. |
| 10.0.2734.00 | 973602 | Cumulative Update 4 for SQL Server 2008 SP1 | The fourth rollup of patches for Service Pack 1. |
| 10.0.2723.00 | 971491 | Cumulative Update 3 for SQL Server 2008 SP1 | The third rollup of patches for Service Pack 1. |
| 10.0.2714.00 | 970315 | Cumulative Update 2 for SQL Server 2008 SP1 | The second roll-up of patches for Service Pack 1. |
| 10.0.2710.00 | 969099 | Cumulative Update 1 for SQL Server 2008 SP1 | The first roll-up of patches for Service Pack 1. Includes CU4 patches for RTM that didn’t make it in SP1. |
| 10.0.2531.00 | 968369 | Service Pack 1 for SQL Server 2008 | SP1 |
| 10.0.1835.00 | 979064 | Cumulative Update 10 for SQL Server 2008 SP1 | The tenth rollup of patches for SQL Server 2008 RTM. |
| 10.0.1828.00 | 977444 | Cumulative Update 9 for SQL Server 2008 | The 9th roll-up of patches for SQL Server 2008 RTM. |
| 10.0.1823.00 | 975976 | Cumulative Update 8 for SQL Server 2008 | The 8th roll-up of patches for SQL Server 2008 RTM. |
| 10.0.1818.00 | 973601 | Cumulative Update 7 for SQL Server 2008 | The 7th roll-up of patches for SQL Server 2008 RTM. |
| 10.0.1812.00 | 971490 | Cumulative Update 6 for SQL Server 2008 | The 6th roll-up of patches for SQL Server 2008 RTM. |
| 10.0.1806.00 | 969531 | Cumulative Update 5 for SQL Server 2008 | The 5th roll-up of patches for SQL Server 2008 RTM. |
| 10.0.1798.00 | 963036 | Cumulative Update 4 for SQL Server 2008 | The fourth roll-up of patches since the RTM version. |
| 10.0.1787.00 | 960484 | Cumulative update package 3 for SQL Server 2008 | The third roll-up of patches since the RTM version. |
| 10.0.1779.00 | 958186 | Cumulative update package 2 for SQL Server 2008 | The second roll-up of patches since the RTM version. |
| 10.0.1763.00 | 956717 | Cumulative update package 1 for SQL Server 2008 | The first roll-up of patches since the RTM version. |
| 10.0.1755.00 | 957387 | FIX: No records may be returned when you call the SQLExecute function to execute a prepared statement and you use the SQL Native Client ODBC Driver in SQL Server 2008 | |
| 10.0.1600.22 | RTM | The first public, supported version of SQL Server 2008 |
Coders Poem
I thought I’d share a poem somone mailed to me this morning ….
“I start my day by sitting on a chair,
Giving my monitor a hard, cold stare,
By evening I’m done with another coding.
Oh! This has become a routine so boring.
Like all, I entered this field with great hope,
Jobs were many and there was plenty of scope,
Dreams of joining the likes of Gates,
And a chance to make money in the States.
This, I entered the world of bytes,
Only to realize that reality bites.
‘Coz a programmer’s life, isn’t all that cozy.
The bed of software isn’t all that rosy.
Seeing the monitor all day n night,
Have taken the power off my eyesight,
Late to bed n late to rise,
Has made me wealthy, but not healthy n wise.
Working holidays, busy weekends.
No time for family, no time for friends,
My job steals most of my time,
Helplessly, I watch this crime.
Just for few bits of money,
I forego those moments with my Honey,
When I should be out – having fun,
I’m telling a comp, what’s to be done.
I hate u, yet I can’t get away,
‘Coz, I need the money u pay.
God, to thee I pray,
If there be one – show me the way.”
Fundamentals of SQL Server
A fundamental skill for DBAs is tohave a firm understanding of the SQL Server databaseengine’s system databases. It’s also useful for database developers to be upon system databases packaged with SQL Server. Here’s a rundown of the systemdatabases. (Note: If you decide toexplore these system databases, make sure you do so ona development server.)
Safeguard your database
Unfortunately this is one of the most neglected elements of many commercial databases. As I explained earlier, every database has defined groups. Apart from the special ‘public’ group, the db_datareader and the db_datawriter are also particularly interesting as regards data security. Both of these groups have built-in rights to enter, then read and write all the database tables. None of the special groups is allowed to perform all of the procedures in the database. I should also remind you that the ‘dbo’ user has full rights to all objects (tables, procedures, definitions, data integrity, etc.) in the database. All users of the db_owner role have the same rights as the ‘dbo’ user. The reason for data’s vulnerability to unauthorized access is usually an incomplete implementation of the database. The programmers who prepare a database in their environment often forget to plan its safeguards, which means that when the base is being implemented, there are no plans for security. Completing an implementation requires that such a plan is created quickly, (which is often impossible) or else giving all users full access to all tables by placing them in db_datareader and db_datawriter groups, granting them the right to perform all procedures. In most cases this is done manually or with the use of a simple script, which derives the name of each procedure from the system tables and gives the ‘public’ group authorization to use it. You could place all users in the db_owner group instead of carrying out such a complicated job but this is a fatal error in the context of data security. A plan of an application’s safeguards should be created while the application is being created. By the way, the SQL server’s security system’s specifics should then be used as it allows the definition of authorizations at the level of the application, incredibly efficiently. Some objects of the database, like procedures, functions and views, may use other ones, e.g. a view may use tables, another view or a function, whereas a procedure may use views, functions, tables and other procedures. A user who gained access to a procedure, a view or a function may use it even if he has no access to objects used by this procedure on condition that the owner of the procedure, view or function is at the same moment the owner of these objects. This is a simple way of implementing conditions, which limit access to data in the objects. You could, for example, create a ‘my_data’ view containing the “WHERE UID = USER_ID() WITH CHECK OPTION” condition which uses a ‘personal_data’ table. No user has direct access to ‘personal data’, so they have to use ‘my_data’ view. This view has a rigidly fixed condition to render only those lines that suit the user’s ID accessible. This condition is an example of implementing the security in the application, so that it cannot be passed round by the user. A similar situation would occur if the ‘A’ user created a ‘ca’ table, a ‘pa’ procedure for manipulating the data in the table and if he gave rights for the ‘pa’ procedure to the ‘B’ user. ‘B’ could use the data included in the ‘ca’ table with the use of the ‘pa’ procedure.
At first glance, this seems complicated. However, three simple principles will allow you to build an efficient mechanism to safeguard the data:
- The database users should have no direct access to tables
- The users may access data only with the use of functions, procedures or views
- The ‘dbo’ is the owner of all objects in the data (to avoid the ‘broken ownership chain’ problem)
Two additional principles concerning user groups make it easier to manage the authorizations inside the database:
- Authorizations for views, procedures and functions are granted to groups (or roles) of users depending on the tasks they perform and the required level of data access they need.
- The authorizations of specific users are regulated by their membership in particular groups or roles.
Choosing some principles at the start of the process to create an application will let you reach a high level of data security, so it is important to consider this seriously. You would never reach such a high level of security if you added the safeguards later. In addition, such a process would be very expensive because of the required programming “overhead”, tests and possibly (or even probably) complications.
Safeguard your SQL Server
The SQLServer service is equipped with advanced security management devices, which have been partly explained in the article that described the authentication mechanisms. A quick reminder: there are two ways of logging on to an SQL server:
- The one based on an own mechanism of SQLServer accounts, possible only in the mixed mode,
- Authentication based on the operating system (i.e. Windows NT Authentication), also called the trusted authentication.
The SQL service’s most privileged account is the ‘sa’ or System Administrator account. It is always created during the installation and it uses its own SQLServer authenticating mechanism, which is available only in the mixed authentication mode. This means that setting the authentication mode to ‘trusted’ is a good way of blocking access to the ‘sa’ account. The ‘sa’ account has full control of the SQL service. You could say that the SQLSERVR.EXE process has the same privileges as the ‘sa’ account and vice versa: the ‘sa’ account may run any code with the privileges of the account used by the service. The server users who belong to the ‘sysadmin’ group have the same privileges as the ‘sa’ users do. The BUILTIN/Administrators account, i.e. the local group of the administrators of the system on which the service has been installed, belongs to that group by default. This account uses no password; it authenticates users using the trusted connection. From now on let us say that the ‘sa’ abbreviation will stand for everyone from the ‘sysadmin’ group. On account of such a configuration, the administrators of the server on which the SQLServer is installed, have the possibility of logging onto the ‘sa’ account and gaining unlimited access to the data and to the service management. A contradictory relation does not occur; the ‘sa’ account has the same privileges as the SQLSERVR.EXE has.
The account itself is the only one authorized to manage the ‘master’ database which is the main database, containing information on other databases, user accounts, files containing data, etc. An SQL server cannot be run without this database. An empty one may be created with the use of the ‘rebuild master database’ tool if such a necessity occurs, and subsequently the right database from the security backup should be recreated in its place. Every database has a list of local users. On this list the ‘dbo’ (which stands for ‘Database Owner’) account is fully authorized to manage the database, whereas ‘guest’ stands for every user of the SQL server. If you create a ‘guest’ account in any of the databases, it will be available from any account, which has access to the SQLServer service. The databases also have roles (i.e. user groups). A special ‘public’ group defined in each of the databases signifies all the users of this database. The ‘master’ database has only two users and that must not be changed. These are:
- The ‘dbo’ which is a local name of the ‘sa’ account in the ‘master’ database,
- The ‘guest’ which allows every user to perform basic operations in the ‘master’ database after having logged onto the SQL server.
The ‘public’ groups’ and the ‘guest’ users’ rights in the ‘master’ database are very limited as they are not allowed to establish databases or accounts, read passwords etc. The access rights of an ordinary user to other databases as well as the rights of access to the tables and procedures they contain have to be configured in an unclassified way. This will be explained later on in the article. Now, let us focus on securing access to the ‘sa’ account.
The main safeguard of the ‘sa’ account is the password, which must not be null. It is, however, set up like that by default after the MSDE installation is finished. This may occur no matter what the installation options were in the SQLServer’s case. Just after having finished the installation you have to log onto the SQL server by running the “osql.exe.-E” command from the administrator’s account. Subsequently, use the “sp_password @new=’this-is-the-‘sa’-password’,loginame=’sa'” procedure but not the mentioned password. If the server were not running the mixed mode, that would be the only password in the ‘master’ database and it would remain unused. A server not running in the mixed mode won’t allow logging on the ‘sa’ account with the use of the password even if it was given correctly. However, the password has to be set up because a change in the mode of logging on isn’t usually subject to security procedures identical with the surveillance of administrating passwords. A careless change of the authentication mode before setting up the ‘sa’ password is a frequently committed and serious mistake often used by worms. You probably already noticed that authentication based on passwords saved in the ‘master’ database is much more hazardous than Windows authentication. This is why applying the SQL server should be designed in a way, which would allow every application to establish a trusted connection with it. That would allow turning on SQLServer-based authentication mechanisms and using Windows-built-in account-securing mechanisms such as the account lockout, which helps to prevent brute force attack attempts. Moreover, the server’s configuration should include a command to create a log of logon attempts so that it is possible to trace attack attempts. A considered restriction of access to the SQLServer’s extended procedures is another safeguard. These procedures allow an ordinary user to take advantage of the programmer’s trivial mistakes to gain ‘sa’ authorizations because these functions come from external DLL files and are run internally in the process of the MSSQLServer service. They run on the same rights as the ‘sa’ accounts do, so a simple fault, like a buffer overrun or an erroneous interpretation of the user’s arguments may make the ‘sa’ account unusable. The faults in security bulletins no. MS00-092, MS01-060, MS02-020 are examples. Unfortunately, disallowing users from accessing extended procedures is not a warranty of full security. You should additionally ensure that the OLEDB libraries available from the level of the SQLServer service, i.e. the MS02-040 security bulletins are deprived of such errors. Apart from that, the MS02-007, MS02-038 and the MS02-039 functions contain internal faults. The most secure way is to install Service Pack 3 [4] just after the SQLServer service has been installed.
Safeguard your OS
The fact that an SQL server needs to be safeguarded comes as a surprise to many administrators because the service is believed to be unable to cause any harm. This is however a fallacious argument. The SQL server administrator is authorized to run any code chosen by himself inside the SQLSERVR.EXE with the use of the xp_cmdshell, the xp_regwrite or self-created procedures. This allows him to communicate with the server’s operating system without any limits. This possibility is built into the SQL Server service and it is not possible to take it off away from the ‘sa’ user, i.e. the server administrator. However, at any time the privileges of the ‘sa’ are limited by the SQL service policies. This implies that the lower the level of the privileges granted by the SQL server service, the less risky is its interaction with the operating system. The service’s documentation informs you that the service does not need the administrator’s authorization to perform ordinary tasks. A user, allowed to ‘logon as service’ is enough. However, some tasks require special allowances. These are, for example:
- Making the xp_cmdshell available to ordinary SQL server users. If the ordinary users of the SQL server service are to be allowed to run the commands of the operating system, then the xp_cmdshell extended procedure has to change the security token for one which is less privileged than the one inherited from the SQLSERVR.EXE service’s process. This requires high authorization. Put differently, should you wish to make the xp_cmdshell available to ordinary users, you would have to increase the authorizations of the SQL server service in the operating system to the LocalSystem level. To be more precise, you would have to allow it to ‘act as a part of an operating system’ and to ‘replace process level token’. I strongly dissuade you from using such a configuration for obvious reasons;
- Communicating with other servers via the network. A very simple rule will suffice in this case: the authorizations of the service’s access via the network are equal to those of its account. The LocalSystem account has no such authorizations (there is one exception which won’t be described here, i.e. the null session, because it violates basic security rules). This is why an account of a ordinary user will be enough for the SQL service to e.g. replicate data with other servers or create backups of the databases on network resources, etc.
- An SQL server working in a fail-over cluster, i.e. Microsoft Cluster Server. In such a situation the service has to have administrative rights on both cluster nodes. This is what Windows cluster services require.
Microsoft SQL Server is not just one service, the MSSQLServer. It is accompanied by the SQLServerAgent, used for performing tasks, which could be carried out by the administrator or by the users. This service may require administrative rights in the following situations:
- If you want to allow the ordinary users to run operating system tasks or scripts,
- To allow automatic restarts of the SQL Server in case of a breakdown,
- To allow for detection of the periods of low activity of the system in order to perform earlier-planned tasks in these periods.
If there is no need for these possibilities, the SQLServerAgent may be running on a common user account that has the right to be running as a service. Moreover, it is quite comfortable if both services (the SQLServer and the SQLServerAgent) use the same account. That simplifies administration without increasing the risks. The aim of this article is not to explain if or in what circumstances there is any reasonable use for those possibilities, which demand higher authorizations. This should be settled before the SQLServer is installed. In most cases the following principles may be a starting point:
- The MSSQLServer service is not run in the MSCS cluster,
- The users have no rights to perform the operating system commands, the xp_cmdshell as well as the SQLServerAgent-defined tasks,
- The SQLServerAgent service doesn’t have to restart the SQLServer in case of a breakdown. Such a situation should cause the administrators’ reaction and a manual intervention, not an automatic restart of the service. Monitoring the SQL server with the use of special tools would obviously make it easier to react in such situations,
- The SQLServerAgent service doesn’t have to detect periods of low server activity,
- The SQLServer service may communicate with other servers in the local area network if it is overtly given the required rights.
When this occurs, the account on which the MSSQLServer and the SQLServerAgent services are to be run should be defined as a domain user with no special rights in the domain. Also, it should not have any rights to local logging in as a ‘batch job’ or a ‘service’ on other computers, and not even with the right of network communication with the servers when the GPO is defined inside the domain. Obviously, these authorizations are not enough for the service to run. An Organizational Unit (OU) should be prepared in the system and separated from it. The machine on which the SQL server is to be run should be placed in this OU, as well as other servers with which the service should be communicating within the use of the network. The GPO should be defined in the Organizational Unit. The chosen account should be allowed to log on as a service in the OU and may be accessed from the network. The question of the vulnerability of the password to attacks should also be considered. The password won’t be used by a human, so it may reach any level of complexity. On the other hand, it should be subject to the rules that apply to password management within the organization. This means that the password has to be changed regularly, which involves the necessity of updating the services, which use the account. Another way of solving this problem is to create a password, which would be so complicated that it would make brute-force attacks impossible for long. Such a password would consist of at least 30 characters, half of which are special ones. Moreover, the SQLServer service should have at least read-only access to the directory in which it has been installed, e.g. C:\Program Files\Microsoft SQL Server\MSSQL, as well as full access to the data directories, to the event log and the authorization to read the whole root directory of the specified disk, which is often dedicated to data storage. The service should also be allowed to access the directory where the configuration information is stored:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer. The SQL Server service should not be allowed to modify the data in the directory in which the data used for running the service is stored, i.e. HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLServer. Otherwise, the ‘sa’ user will have the possibility of adding the “ObjectName=LocalSystem” value to this entry so that the service grants him LocalSystem authorizations as soon as it is run the next time.
This problem has been described in the MS02-034 security bulletin [3]. When the process of configuring the systems comes to an end, one should ensure that there are no basic security bugs, like an “Everyone: Full Control” authorization in the C:/ root directory on the server. Another very important element is the plan of how the server should be communicating with the clients. Any communication from untrusted computers towards the SQLServer service and vice versa should be disallowed. The TCP 1433 (the service’s default port) and the UDP 1434 (the identification of the SQL server, unnecessary if the user knows the service’s TCP port number) ports should be blocked by the firewall. If users from remote networks are meant to use the SQL server, you should watch out for the security of the connection between them, for example with the use of the VPN technology. Setting up the SQLServer service on an unusual port is a totally inefficient safeguard. Any tool suitable for scanning ports would find your ‘concealed’ server in a limited (and usually short) amount of time.
The system administrators often delete the xp_cmdshell or other procedures, which allow use of the operating system, e.g. xp_regwrite, in order to secure their servers. Unfortunately such actions are completely futile. None of these procedures allows for a magical way of increasing the rights of the service to a level higher than the level configured by the system administrator, i.e. the service will have the same authorizations as the account it is being run on. The only efficient way of preventing the operating system from the SQLServer service are the system’s safeguards such as registry and file system authorizations, required patches, hard-to-guess passwords, etc. If the mentioned procedures were somehow deleted, the ‘sa’ user may retrieve them with the use of the ‘sp_addextendedproc’ or by creating entries to the system tables of the ‘master’ database.
If there are multiple instances of the SQLServer service to work on one server, you have to bear in mind the fact that each one of them must not have any access to executable or data files and to registry entries used by other instances. This is how you’ll make the instances’ data and authorizations isolated. This type of isolation is obviously out of the question if all SQL instances had administrating rights on the server. If you install multiple instances of the SQLServer service on one computer, keep in mind its capacities and suitably share its resources – allocate dedicated processors and physical drives or RAID storage tables for each instance, also remember to limit the RAM usage. Another thing to remember is that whatever steps are taken, a huge burden to the server caused by one of the instances will slow down the others. This task of steps, which need to be taken, is to minimize the negative influence, as it is impossible to fully neutralize it. This is why running multiple SQLServer instances on one server is not a good idea.
Database Jedi 