Microsoft SQL Server is a popular and robust environment for many applications that use databases – it features excellent multi-access capabilities, comprehensive security coverage and can easily be transported to other database platforms. Unfortunately, such potential will not be realized – notwithstanding the use of royalty-free MSDE (Microsoft Database Engine optimized for individual or small workgroup solutions) if adequate security protection to the databases is not provided as a minimum. Why is this must –have technology? Because high capabilities of the SQL Server are combined with high flexibility and too much flexibility may be detrimental if used in the wrong manner. This article aims to identify certain types of risks that might result from inappropriate management of the Microsoft SQL Server.
If properly configured, each SQL Server permits all users to access the master database, which contains all the settings for SQL Server – and all the information that SQL Server uses to open the databases. It also contains all SQL login IDs, data of connected servers etc. Of course, “normal” users are not allowed to access all information resources. Figure 1 illustrates how the server behaves if an attempt is made to access the account list – as can be seen, the server has prevented users from reading passwords. Nevertheless, account names and databases (including the information stored on them) may be accessed by unprivileged users. An example showing a piece of information taken by a user is illustrated in Figure 2 below.
FIGURE 1: A failed attempt to access the account list.
If properly configured, each SQL Server permits all users to access the master database, which contains all the settings for SQL Server – and all the information that SQL Server uses to open the databases. It also contains all SQL login IDs, data of connected servers etc. Of course, “normal” users are not allowed to access all information resources. Figure 1 illustrates how the server behaves if an attempt is made to access the account list – as can be seen, the server has prevented users from reading passwords. Nevertheless, account names and databases (including the information stored on them) may be accessed by unprivileged users. An example showing a piece of information taken by a user is illustrated in Figure 2 below.
FIGURE 1: A failed attempt to access the account list.
FIGURE 2: When a normal user
manages to access the account list.
manages to access the account list.scolling to the bottom of the screen in the screenshot above gives the final few lines of screen ouput, shown below:
(4 rows affected)
1> select name,dbid from sysdatabases
2> go
1> select name,dbid from sysdatabases
2> go
name
dbid
-------------------------------------------
----------------------
master
1
tempdb
2
model
3
msdb
4
pubs
5
Northwind
6
pages
7
(7 rows affected)
1>
dbid
-------------------------------------------
----------------------
master
1
tempdb
2
model
3
msdb
4
pubs
5
Northwind
6
pages
7
(7 rows affected)
1>
As can be seen it is difficult to keep your data secure from users’ prying eyes.
However…
Of course, the reader may now wish to remark "But after all, my users are not allowed to run ad hoc queries on the SQL Server!” However, close examination shows this to be a rather dubious claim, because the possibility or impossibility of placing deliberately created queries in an SQL Server application does not necessarily imply that the same rule is valid for the server itself! Even though a specific application is apparently secured against such a situation, I would suggest that you reconsider this idea that all scripts are protected against SQL injection attacks" [1][2] What if the user, when defining query conditions, may attach a table to the results? And what is the server-defined authentication scheme to be applied for potential users? Even presuming that the application is well protected against unauthorized access to the data, the user himself cannot run standard communication tools on his computer to connect the SQL server- osql.exe, VBScript that uses ADO, or any other program that runs ODBC (or OLEDB) directly on the database server. Knowledge of the SQL Server password is the only prerequisite. Yet, in many situations, the possibility of authentication of local network users in the Directory Services is sufficient. However don't let paranoia set in - we must simply accept that the possibility of user authentication by the SQL Server is associated with the automatic right to ask probing, or even indiscreet questions.
What are the conclusions?
Let us look once again at the above example of a query that returns the account names – some of them are defined on the SQL server and are recognizable by the 0 value in the "isntname" column. If configured to perform authentication in Mixed Authentication Mode, the SQL Server will accept attempts to login on these accounts, while not disabling the account on repeated attempts. This is the drawback of the mechanism by which the SQL Server authenticates connections in mixed mode. Fortunately, its use is optional and even when it is present, it is not recommended, because it creates opportunities for a brute force attack in which each possible key or password is attempted until the correct one is found. Of course, a hacker may do this using a suitable program to automate such work (I, will not enter into details for obvious reasons). The most “appreciated” is an "sa" (System Administrator) account, which has access to everything as created specifically for the SQL server (It is similar to root on Linux or LocalSystem in Windows NT). It has the rights to manage any database (the "master" database included), full access to perform any function on the SQL Server and even to run processes that are inherited by the server’s service, namely the SQLSERVR.EXE process. Moreover, in many installations, SQL Server runs in the security context of the machine’s administrator - thereby the "sa" user can manage a computer with the MS SQL Server service installed, apart from managing all databases. When working with MSDE, the situation is even worse - the service is installed under LocalSystem, with a default "sa" account with a BLANK password! Figure 3 illustrates two consecutive attempts to hack the "sa" account, one failed attempt and another successful.
FIGURE 3: Two attacks on an "sa" account with a blank password – the second attempt is successful.
However…
Of course, the reader may now wish to remark "But after all, my users are not allowed to run ad hoc queries on the SQL Server!” However, close examination shows this to be a rather dubious claim, because the possibility or impossibility of placing deliberately created queries in an SQL Server application does not necessarily imply that the same rule is valid for the server itself! Even though a specific application is apparently secured against such a situation, I would suggest that you reconsider this idea that all scripts are protected against SQL injection attacks" [1][2] What if the user, when defining query conditions, may attach a table to the results? And what is the server-defined authentication scheme to be applied for potential users? Even presuming that the application is well protected against unauthorized access to the data, the user himself cannot run standard communication tools on his computer to connect the SQL server- osql.exe, VBScript that uses ADO, or any other program that runs ODBC (or OLEDB) directly on the database server. Knowledge of the SQL Server password is the only prerequisite. Yet, in many situations, the possibility of authentication of local network users in the Directory Services is sufficient. However don't let paranoia set in - we must simply accept that the possibility of user authentication by the SQL Server is associated with the automatic right to ask probing, or even indiscreet questions.
What are the conclusions?
Let us look once again at the above example of a query that returns the account names – some of them are defined on the SQL server and are recognizable by the 0 value in the "isntname" column. If configured to perform authentication in Mixed Authentication Mode, the SQL Server will accept attempts to login on these accounts, while not disabling the account on repeated attempts. This is the drawback of the mechanism by which the SQL Server authenticates connections in mixed mode. Fortunately, its use is optional and even when it is present, it is not recommended, because it creates opportunities for a brute force attack in which each possible key or password is attempted until the correct one is found. Of course, a hacker may do this using a suitable program to automate such work (I, will not enter into details for obvious reasons). The most “appreciated” is an "sa" (System Administrator) account, which has access to everything as created specifically for the SQL server (It is similar to root on Linux or LocalSystem in Windows NT). It has the rights to manage any database (the "master" database included), full access to perform any function on the SQL Server and even to run processes that are inherited by the server’s service, namely the SQLSERVR.EXE process. Moreover, in many installations, SQL Server runs in the security context of the machine’s administrator - thereby the "sa" user can manage a computer with the MS SQL Server service installed, apart from managing all databases. When working with MSDE, the situation is even worse - the service is installed under LocalSystem, with a default "sa" account with a BLANK password! Figure 3 illustrates two consecutive attempts to hack the "sa" account, one failed attempt and another successful.
FIGURE 3: Two attacks on an "sa" account with a blank password – the second attempt is successful.
Using the System Administrator account
First and foremost, the "sa" user has control over all SQL server data, scheme and setup. It is not necessary here to enter into details, because the list of system administrator privileges can contain almost anything. Instead, let us focus on certain purposely-selected types of malicious attacks. The simplest one consists in creating a new account with the same privileges as the "sa" user has. The listing below is an example:
1> sp_addlogin 'hacked','h4xor'2> goNew login created.1> sp_addsrvrolemember 'hacked','sysadmin'2> go'hacked' added to role 'sysadmin'.1> quitC:\>osql -U hacked -P h4xor -Q "SELECT DB_NAME(),USER_NAME()" -------- ------- master dbo(1 row affected)C:\>
In lieu of the "sp_addlogin" procedure one may use, for example, "sp_grantlogin" – the latter will establish an account being authenticated by the operating system on which the SQL server is running. I would like to note, that it is very easy to gain "sa" privileges – just by assigning the sysadmin role to the selected user (or a group of users defined at the SQL Server setup phase) account. Apart from this role, the SQL Server service makes it possible to exploit some other feature that, however, cannot be used to fully manage the server. I’ll refer those interested to [3]. Of course, it cannot be excluded that the administrator will detect such an account (even if its name does not stand out as anything unusual). If so, a hacker has no other options but to use the existent account – the simplest way would be to modify the password employing the "sp_password" procedure. If an intruder wishes to cover his tracks, he may attempt to decipher passwords, which give users access to the server. One might wish to remember at this point that in mixed mode authentication, the SQL Server can use its own authentication mechanism which stores information not only on account names (similar to the operating system based authentication) but also on passwords. This is done in the "password" column of the sysxlogins table- it contains a hash of the user's password produced by the pwdencrypt() function. This is a fairly well known fact brilliantly described by David Litchfield in [4]. The tools that can be used to perform brute force attacks are explained in [5]. If your SQL Server is not configured for SQL Server authentication, you don't have to worry about getting hacked in the above manner. A more malicious intruder may attempt to install a backdoor. Two possibilities are presented below.
Extended Stored Procedures
SQL Server features the possibility to run user-written procedures. These procedures can both be written in the SQL language and stored in any database, or they are dynamic link libraries (DLLs) that SQL Server can dynamically load and execute if required. This latter type is called "extended stored procedures" and means procedures that have interesting characteristics: they are added directly to the SQL Server and share the security context of the SQL Server executable. In practice, extended stored procedures can be made to run with „sa” privileges on the database. They can be defined only in the "master" database by which they can be added to the database by the administrator (the user "sa") only.
The default SQL Server configuration contains a virtually huge number of these procedures, however most of them are undocumented. Extended stored procedures can be made to run under different authentication schemes – certain procedures are made accessible for all users either by default or because the server’s functioning so requires. Only the system administrator can run other extended stored procedures. This creates a number of operational difficulties for the administrator especially in detecting “special” procedures that are likely to be embedded in the server by an intruder. It is sufficient to load a previously prepared DLL file to the computer (using the Open Data Services API, which is optionally installed with the SQL Server), then call the "sp_addextendedproc" procedure with its respective arguments, followed by granting the right to execute this procedure to all users (i.e. to the public user group). Such a procedure may then be used by a hacker to execute deliberate operations with “sa" privileges from any user account level!
Why not revoke authorization?
Chris Anley in his document entitled "Violating Database - Enforced Security Mechanisms" [6] described an approach to enhancing security, which involves a modification of SQL Server functioning to ignore queries from anyone but “sa” users. His invention is based on the idea of handling authorization procedure exceptions by the SQL Server code. “Rather than attempting a static analysis of the entire SQL server codebase, a little debugging up front will probably point us in the right direction”. Of course, such a change is feasible under particular conditions – either by manipulating the executable file that contains the SQL server codebase or by making operations on the in-memory image –in both cases, the SQL Server System Administrator is the only person authorized to do this. However, this is a simplified situation. The reality is rather bleak. Before entering in further detail, let me remind you that the observance of current security bulletins is of top importance, otherwise the buffer might overflow.
Why is a buffer overrun problem of concern? As you may remember, SQL Server has many default extended stored procedures i.e. DLL functions that come with SQL Server. They are written in any high level language (for example C) and then compiled as a DLL. On starting to run, they read user’s parameters to the memory buffer. However as may happen for whatever reason, the programmer may forget to encrypt the data check being placed into variables in the program – and if an attacker sends too much data, it is possible for the data to "overflow" the predetermined size and write the data to adjacent areas of the computer buffer. This buffer overflow may allow malicious users to run arbitrary commands on the remote system. (See, for example, “Analysis of Buffer Overflow Attacks “ by Piotr Frej and Maciej Ogórkiewicz). The problem is that Microsoft SQL Server contains procedures that are vulnerable to buffer overflow attacks. These are both the previously described extended stored procedures as well as functions implemented in the Transact-SQL language (an example may be the previously mentioned "pwdencrypt" function). This may also happen with OLEDB libraries, which are COM objects loaded in the client process. Remember, that in the COM context, every component-based program becomes a client. Whenever the OPENROWSET command is activated, SQL Server becomes a client of the data provider, which makes it automatically vulnerable to errors in the code of a particular OLEDB. For those interested, you might wish to read various security bulletins [7] for details of problems and proposed solutions. If so, be aware that a successful buffer overflow attack means that every user may run arbitrary code on the SQL server in the context of a local administrator account. And as we already know, these (“sa”) privileges allow for arbitrary manipulation of SQL service, nevertheless we are still left with the following problem – how powerful are the "sa" privileges in the operating system context?
They are equally powerful to the SQL service and, reasoning strictly in security terms, these privileges correspond to those of the SQLSERVR.EXE process. By “standard” default, SQL Server includes several very powerful extended stored procedures to communicate with the operating system. Just to mention some of them:. "xp_cmdshell", "sp_OACreate", "xp_regwrite", "xp_instance_regwrite", "xp_adsirequest". Though some of them are documented (two first ones), we are still uncertain of the effects of others. Using these procedures, SQL Server administrators can interact with the operating system only limited by the authorizations for the MSSQLServer service. If it were a case of running this service in the LocalSystem account, the "sa" would have been granted top privileges to access the Desktop but not the network directories. The attacker may make use of these privileges as illustrated in Figure 4.
FIGURE 4: Using the "sa" account to upload a program from the Internet to the server.
First and foremost, the "sa" user has control over all SQL server data, scheme and setup. It is not necessary here to enter into details, because the list of system administrator privileges can contain almost anything. Instead, let us focus on certain purposely-selected types of malicious attacks. The simplest one consists in creating a new account with the same privileges as the "sa" user has. The listing below is an example:
1> sp_addlogin 'hacked','h4xor'2> goNew login created.1> sp_addsrvrolemember 'hacked','sysadmin'2> go'hacked' added to role 'sysadmin'.1> quitC:\>osql -U hacked -P h4xor -Q "SELECT DB_NAME(),USER_NAME()" -------- ------- master dbo(1 row affected)C:\>
In lieu of the "sp_addlogin" procedure one may use, for example, "sp_grantlogin" – the latter will establish an account being authenticated by the operating system on which the SQL server is running. I would like to note, that it is very easy to gain "sa" privileges – just by assigning the sysadmin role to the selected user (or a group of users defined at the SQL Server setup phase) account. Apart from this role, the SQL Server service makes it possible to exploit some other feature that, however, cannot be used to fully manage the server. I’ll refer those interested to [3]. Of course, it cannot be excluded that the administrator will detect such an account (even if its name does not stand out as anything unusual). If so, a hacker has no other options but to use the existent account – the simplest way would be to modify the password employing the "sp_password" procedure. If an intruder wishes to cover his tracks, he may attempt to decipher passwords, which give users access to the server. One might wish to remember at this point that in mixed mode authentication, the SQL Server can use its own authentication mechanism which stores information not only on account names (similar to the operating system based authentication) but also on passwords. This is done in the "password" column of the sysxlogins table- it contains a hash of the user's password produced by the pwdencrypt() function. This is a fairly well known fact brilliantly described by David Litchfield in [4]. The tools that can be used to perform brute force attacks are explained in [5]. If your SQL Server is not configured for SQL Server authentication, you don't have to worry about getting hacked in the above manner. A more malicious intruder may attempt to install a backdoor. Two possibilities are presented below.
Extended Stored Procedures
SQL Server features the possibility to run user-written procedures. These procedures can both be written in the SQL language and stored in any database, or they are dynamic link libraries (DLLs) that SQL Server can dynamically load and execute if required. This latter type is called "extended stored procedures" and means procedures that have interesting characteristics: they are added directly to the SQL Server and share the security context of the SQL Server executable. In practice, extended stored procedures can be made to run with „sa” privileges on the database. They can be defined only in the "master" database by which they can be added to the database by the administrator (the user "sa") only.
The default SQL Server configuration contains a virtually huge number of these procedures, however most of them are undocumented. Extended stored procedures can be made to run under different authentication schemes – certain procedures are made accessible for all users either by default or because the server’s functioning so requires. Only the system administrator can run other extended stored procedures. This creates a number of operational difficulties for the administrator especially in detecting “special” procedures that are likely to be embedded in the server by an intruder. It is sufficient to load a previously prepared DLL file to the computer (using the Open Data Services API, which is optionally installed with the SQL Server), then call the "sp_addextendedproc" procedure with its respective arguments, followed by granting the right to execute this procedure to all users (i.e. to the public user group). Such a procedure may then be used by a hacker to execute deliberate operations with “sa" privileges from any user account level!
Why not revoke authorization?
Chris Anley in his document entitled "Violating Database - Enforced Security Mechanisms" [6] described an approach to enhancing security, which involves a modification of SQL Server functioning to ignore queries from anyone but “sa” users. His invention is based on the idea of handling authorization procedure exceptions by the SQL Server code. “Rather than attempting a static analysis of the entire SQL server codebase, a little debugging up front will probably point us in the right direction”. Of course, such a change is feasible under particular conditions – either by manipulating the executable file that contains the SQL server codebase or by making operations on the in-memory image –in both cases, the SQL Server System Administrator is the only person authorized to do this. However, this is a simplified situation. The reality is rather bleak. Before entering in further detail, let me remind you that the observance of current security bulletins is of top importance, otherwise the buffer might overflow.
Why is a buffer overrun problem of concern? As you may remember, SQL Server has many default extended stored procedures i.e. DLL functions that come with SQL Server. They are written in any high level language (for example C) and then compiled as a DLL. On starting to run, they read user’s parameters to the memory buffer. However as may happen for whatever reason, the programmer may forget to encrypt the data check being placed into variables in the program – and if an attacker sends too much data, it is possible for the data to "overflow" the predetermined size and write the data to adjacent areas of the computer buffer. This buffer overflow may allow malicious users to run arbitrary commands on the remote system. (See, for example, “Analysis of Buffer Overflow Attacks “ by Piotr Frej and Maciej Ogórkiewicz). The problem is that Microsoft SQL Server contains procedures that are vulnerable to buffer overflow attacks. These are both the previously described extended stored procedures as well as functions implemented in the Transact-SQL language (an example may be the previously mentioned "pwdencrypt" function). This may also happen with OLEDB libraries, which are COM objects loaded in the client process. Remember, that in the COM context, every component-based program becomes a client. Whenever the OPENROWSET command is activated, SQL Server becomes a client of the data provider, which makes it automatically vulnerable to errors in the code of a particular OLEDB. For those interested, you might wish to read various security bulletins [7] for details of problems and proposed solutions. If so, be aware that a successful buffer overflow attack means that every user may run arbitrary code on the SQL server in the context of a local administrator account. And as we already know, these (“sa”) privileges allow for arbitrary manipulation of SQL service, nevertheless we are still left with the following problem – how powerful are the "sa" privileges in the operating system context?
They are equally powerful to the SQL service and, reasoning strictly in security terms, these privileges correspond to those of the SQLSERVR.EXE process. By “standard” default, SQL Server includes several very powerful extended stored procedures to communicate with the operating system. Just to mention some of them:. "xp_cmdshell", "sp_OACreate", "xp_regwrite", "xp_instance_regwrite", "xp_adsirequest". Though some of them are documented (two first ones), we are still uncertain of the effects of others. Using these procedures, SQL Server administrators can interact with the operating system only limited by the authorizations for the MSSQLServer service. If it were a case of running this service in the LocalSystem account, the "sa" would have been granted top privileges to access the Desktop but not the network directories. The attacker may make use of these privileges as illustrated in Figure 4.
FIGURE 4: Using the "sa" account to upload a program from the Internet to the server.
If, instead, we run the service with a domain account, which has access to the network, then the “sa” user might access the whole network. However, for regular user accounts this will be a surprise. The "sa" account privileges depend on how the MSSQLServer service has been configured. In the SQL Server documentation it is clearly stated that the MSSQLServer account is a user account and not a system account. Even though recently we have read in Microsoft Security Bulletins dealing with SQL Server: “SQL Server 2000 can run under a non-administrator account, however it requires full access to registry key:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLServer”
Having this access level, the SQL server process is able to modify an "ObjectName" value in the registry. It contains the name of the account that is necessary to run the service. This is enough to re-configure the service to run as LocalSystem. Hence, an attacker who is able to run code under an SQL Server account is able to re-configure a service to run under the highest possible local privileges, even if SQL Server is running as a regular user! This problem has been addressed in the third section of the Microsoft Security Bulletin MS02-034 [8]. To read about the permissions required by SQL Server, refer to the SQL Server 2000 Security document [9]
LocalSystem by default?
Microsoft SQL Server 2000 Desktop Edition (MSDE 2000), the latest version of MSDE is installed with the LocalSystem account as a default. Of course, this creates potential vulnerability to attacks as described above. Naturally, patching SQL Server to Service Pack 3 enhances security – but of the SQL Server service itself, not the machine on which it is running. Instead, having installed SP3, you can experience problems if the server is running somewhere other than the LocalHost. This is a consequence of the permissions that are set to the registry key and the file system with the installation of SP3. Of course, minimizing unwanted privileges is a positive factor but leaving this approach with insufficient documentation is bad practice. Included below is some basic information to help one complete the documentation as required.
In order to reduce MSDE –associated privileges in the operating system:
Replace both the MSSQLServer and SQLServerAgent service accounts (with LocalSystem) with a selected account
Provide this new account with the right to read in the C:\Program Files\Microsoft SQL Server\MSSQL directory, remembering to check the "Reset permissions on all child objects" option in the Advanced dialog box
Grant full privileges in DATA and LOG sub-directories for this new account
Run the registry using regedt32.exe (in such a manner so as to allow for editing of privileges - use regedit.exe if in Windows XP) and grant full permissions for the account in the registry key: HKLM\SOFTWARE\Microsoft\MSSQLServer. Re-run the "Reset permissions on all child objects ..."
Grant read permission to the account (read only!) in the registry key HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER
Run the MSSQLServer service
Add the service account to the sysadmin server role (to support the SQLServerAgent service), using the following SQL commands:
1> sp_grantlogin 'COMPUTER\account_sql'2> goGranted login access to 'COMPUTER\account_sql'.1> sp_addsrvrolemember 'COMPUTER\account_sql','sysadmin'2> go'COMPUTER\account_sql' added to role 'sysadmin'.
Run the SQLServerAgent service
SQL commands are to be run using the osql.exe program under the system administrator account specifying the E parameter. Obviously, you should specify the real name of the SQL service account that must be preceded with the machine name. If you not wish to grant access to your MSDE server from the network, disable the following registry key:
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib
For obvious reasons, it is good practice to export this registry key to the .reg file earlier on. If problems arise with starting MSDE service, use FileMon and RegMon programs available at www.sysinternals.com - these are very useful tools when analyzing and remedying difficulties resulting from over-restrictive constraints posed on registry keys or file systems. It is also worth consulting the SQL Server 2000 Documentation
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLServer”
Having this access level, the SQL server process is able to modify an "ObjectName" value in the registry. It contains the name of the account that is necessary to run the service. This is enough to re-configure the service to run as LocalSystem. Hence, an attacker who is able to run code under an SQL Server account is able to re-configure a service to run under the highest possible local privileges, even if SQL Server is running as a regular user! This problem has been addressed in the third section of the Microsoft Security Bulletin MS02-034 [8]. To read about the permissions required by SQL Server, refer to the SQL Server 2000 Security document [9]
LocalSystem by default?
Microsoft SQL Server 2000 Desktop Edition (MSDE 2000), the latest version of MSDE is installed with the LocalSystem account as a default. Of course, this creates potential vulnerability to attacks as described above. Naturally, patching SQL Server to Service Pack 3 enhances security – but of the SQL Server service itself, not the machine on which it is running. Instead, having installed SP3, you can experience problems if the server is running somewhere other than the LocalHost. This is a consequence of the permissions that are set to the registry key and the file system with the installation of SP3. Of course, minimizing unwanted privileges is a positive factor but leaving this approach with insufficient documentation is bad practice. Included below is some basic information to help one complete the documentation as required.
In order to reduce MSDE –associated privileges in the operating system:
Replace both the MSSQLServer and SQLServerAgent service accounts (with LocalSystem) with a selected account
Provide this new account with the right to read in the C:\Program Files\Microsoft SQL Server\MSSQL directory, remembering to check the "Reset permissions on all child objects" option in the Advanced dialog box
Grant full privileges in DATA and LOG sub-directories for this new account
Run the registry using regedt32.exe (in such a manner so as to allow for editing of privileges - use regedit.exe if in Windows XP) and grant full permissions for the account in the registry key: HKLM\SOFTWARE\Microsoft\MSSQLServer. Re-run the "Reset permissions on all child objects ..."
Grant read permission to the account (read only!) in the registry key HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER
Run the MSSQLServer service
Add the service account to the sysadmin server role (to support the SQLServerAgent service), using the following SQL commands:
1> sp_grantlogin 'COMPUTER\account_sql'2> goGranted login access to 'COMPUTER\account_sql'.1> sp_addsrvrolemember 'COMPUTER\account_sql','sysadmin'2> go'COMPUTER\account_sql' added to role 'sysadmin'.
Run the SQLServerAgent service
SQL commands are to be run using the osql.exe program under the system administrator account specifying the E parameter. Obviously, you should specify the real name of the SQL service account that must be preceded with the machine name. If you not wish to grant access to your MSDE server from the network, disable the following registry key:
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib
For obvious reasons, it is good practice to export this registry key to the .reg file earlier on. If problems arise with starting MSDE service, use FileMon and RegMon programs available at www.sysinternals.com - these are very useful tools when analyzing and remedying difficulties resulting from over-restrictive constraints posed on registry keys or file systems. It is also worth consulting the SQL Server 2000 Documentation
