Users and Groups

Privilege management in SQL Server is simplified by the use of a number of built-in security roles detailed in this section. It also covers the storage location of database users, and the method used to encrypt users' passwords.

Account Information

All user account information in SQL Server is stored in the sysxlogins table in the master database. The schema used is detailed here (byte sizes are shown in brackets):

 srvid smallint(2)       sid varbinary(85)       xstatus smallint(2)       xdate1 datetime(8)       xdate2 datetime(8)       name sysname(128)       password varbinary(256)       dbid smallint(2)       language sysname(128)       isrpcinmap smallint(2)       ishqoutmap smallint(2)       selfoutmap smallint(2) 

The accounts created initially during installation are sa (system administrator) and BUILTIN\Administrators, an account that grants system administrator privileges to any Windows account in the Local Administrators group .

Common Accounts

If the server is using native authentication, for convenience or to simplify interaction with other machines in the network, the sa account often has a blank password. In SQL Server 2000 it is somewhat more difficult to set a blank password during the install process than earlier versions, but it is still possible. The sa account usually has no password so that other applications on the network can easily integrate with the SQL Server ”powerful logins like sa should always have a complex password set. The SQL Server worm Spida ( http://xforce.iss.net/xforce/xfdb/9124 ), first noticed in May 2002, propagated via servers with no sa password set and attempted to export an infected machine's Windows SAM password file.

SQL Server versions 6 and 6.5 create a user named "probe" used by the SQL Server Performance Monitor. This login often also has a blank password, and is most commonly found in environments where SQL Server 2000 is required to interoperate with earlier versions of SQL Server.

A SQL Server distributor is an instance that manages replication of data from the source instance (publisher) to the target instance (subscriber). The account it uses to connect to both, distributor_admin, has a default password obtained by a call to CreateGuid(). Frequently this is removed or changed to something easier to remember, and easier to guess.

Roles

SQL Server's built-in server roles allow an administrator to grant subsets of administrative privileges to other users, such as the ability to create and edit databases. The server roles are

• bulkadmin: Allows execution of the BULK INSERT statement, used to stream files into database tables and views

• dbcreator: Allows creation and management of databases

• diskadmin: Allows management of physical storage such as data and log files

• processadmin: Allows management of the SQL Server processes

• securityadmin: Allows the creation and deletion of users, audit management, and reading error logs

• serveradmin: Can change configuration settings and shut down the server

• setupadmin: Can add and remove linked servers, manage replication, manage extended stored procedures, and execute some system stored procedures

• sysadmin: Has full administrative control over the SQL Server

Server roles are fixed and cannot be created or deleted. The procedures used to add and remove members are add_srvrolemember and drop_srvrolemember.

Fixed database roles are similar to server roles in that they are preset and cannot be changed. The defined SQL Server fixed database roles are

• db_accessadmin: Allows members to add and remove users in the database

• db_backupoperator: Users can back up databases and logs

• db_datareader: Grants SELECT permission on all objects in the database

• db_datawriter: Grants DELETE, INSERT, and UPDATE permissions on all objects in the database

• db_ddladmin: Allows execution of all data-definition language (DDL) statements except those that change object permissions

• db_denydatareader: Removes SELECT permissions within the database from its members

• db_denydatawriter: Removes DELETE, INSERT, and UPDATE permissions within the database from its members

• db_owner: Members can perform any action within the database

• db_securityadmin: Allows management of the database's roles and object permissions

The PUBLIC role is created in every SQL Server database, and contains every database user. It is good security practice not to grant any privileges to PUBLIC in excess of SELECT permissions on unrestricted information.

SQL Server also allows the creation of User-Defined Roles; these sim-plify permissions management by grouping users according to the privileges they require. Object permissions are then assigned to the role itself, and so are granted to all members of the role. The procedures sp_addrole, sp_addrolemember, sp_spdroprole, and sp_droprolemember are used to manage User-Defined Roles. Additionally it is possible to nest roles by making one role a member of another.

Application roles are used to control the permissions of applications that access the database. This allows extended privileges to be granted to users only when they are using this application. This prevents users from accessing the database server using alternative clients such as Osql or Query Analyzer to bypass the restrictions. The procedure sp_addapprole creates the role, and the application uses sp_setapprole to switch its security context.

Password Encryption

SQL Server offers a self-contained authentication mechanism known as native authentication, which uses stored username and password pairs to grant access. The passwords are encrypted using a proprietary hashing algorithm, which is accessed using the inbuilt function pwdencrypt. The password hashes themselves are stored together with the usernames in the master database's sysxlogins table. The sa user's password hash, for example, can be viewed using

 SELECT password FROM master.dbo.sysxlogins WHERE name = 'sa'; 

This will return a hash of a similar length and format to the following:

 0x0100552B2146825C68C3F67F92930D7D037C3C5A724FE8CD8BAF825C68C3F67F92930D7D037C3C5A724FE8CD8BAF 

When the SQL Server password function is fed the current sa password, however, a completely different hash is produced, as shown here:

 SELECT pwdencrypt('[sa password]'); 0x0100112B6C5474911C3A5BCD37F3EB4F3D9BB872910910041FD174911C3A5BCD37F3EB4F3D9BB872910910041FD1 

Running the same query moments later produces yet another, different hash. This suggests some type of time-based salting, a technique intended to foil hash pre-computation. A salt is a value that is generated when the hash of a password is needed. This salt is then concatenated with the password before being passed to the hashing function. The salt can then be stored in plaintext together with the resultant hash, and will be combined in the future with supplied passwords before hashing and comparing to the stored hash value for user authentication. The advantage of this method is that an attacker cannot simply pre-generate a massive database of hashes, and then rapidly compare a stored hash against them.

The first SQL Server hash shown above can be broken down as follows :

 0x0100 552B2146 825C68C3F67F92930D7D037C3C5A724FE8CD8BAF 825C68C3F67F92930D7D037C3C5A724FE8CD8BAF 

The first line is a constant hash header, the second is the time-dependent hash, and the third and fourth hold the hash of the normal case-sensitive password and the password converted to uppercase, respectively. Because in this case, the third and fourth lines are identical, it can be inferred that the password that this hash represents is entirely uppercase. The storage of an uppercase representation of the password effectively removes all benefit gained by selecting a mixed-case password; however, access to password hashes is limited to the database administrator by default so this does not provide any great advantage to an attacker.

The time-based salt is created using a number of C function calls. Initially the result of the time() function is selected as a random number generation seed by passing to the srand () function. Two calls to rand() then produce two pseudo-random integers, which are converted by SQL Server to short data types and then put together to give a single integer value. This final value is used by SQL Server as the salt, which is added to the password before hashing and then prefixes the hash in plaintext in the sysxlogins table. In his paper, "Microsoft SQL Server Passwords" ( http://www.ngssoftware.com/papers/cracking-sql-passwords.pdf ), David Litchfield provides code for a simple command-line dictionary password audit tool. A commercial audit tool, NGSSQLCrack, is also available from NGSSoftware ( http://www.ngssoftware.com/sqlcrack.htm ). It is strongly recommended that the more secure Windows authentication is used with SQL Server whenever possible.

SQL Server Agent Password

The SQL Server Agent is a service used to automate scheduling and alerting within SQL Server. It can use either Windows authentication or a supplied username and password pair for the SQL Server in order to allow it to login and interact with the database. Monitoring registry access using RegMon ( http://www.sysinternals.com/ntw2k/source/regmon.shtml ) when the account information is set the Windows Local Security Authority Service (lsass.exe) can be seen to write under the following key:

 HKLM\SECURITY\Policy\Secrets\SQLSERVERAGENT_HostPassword\CurrVal 

Keys below the Security key are inaccessible to any user except the Windows LocalSystem account. The stored procedure sp_get_SQLagent_properties is used to retrieve information about the agent:

 exec msdb..sp_get_SQLAgent_properties 

This returns a great deal of information, including a hash of the password used by the agent under the column host_login_password:

 0x69879785A9AA092107A72D07F847753AC3D3B40CBE668B64338DF4A11E31676A 

The security researcher Jimmers (Martin Rakhmanoff) determined that the encryption used is a simple XOR operation using a key based on the previous character in the string. The decryption function is exported from semcomn.dll in the SQL Server binaries directory; a small application is available to decrypt SQL Server Agent password hashes ( http://jimmers.narod.ru/agent_pwd.c ).

Checking the default privileges on sp_get_SQLagent_properties:

 use msdb exec sp_helprotect sp_get_SQLagent_properties 

returns

 Owner      Object                        Grantee      Grantor      ProtectType      Action dbo      sp_get_sqlagent_properties      public      dbo      Grant           Execute 

By default the public role has execute permissions on sp_get_SQLagent_properties. This means that if an agent password is set in the default configuration, any user in the database will have access to it.

Role Passwords

Application roles are activated using an obfuscated password as an added measure of security; this algorithm is part of the client's ODBC (Open Database Connectivity) driver. The password is set on creation of the role:

 exec sp_addapprole '[rolename]', '[password]' 

The role is then activated using

 exec sp_setapprole '[rolename]', {Encrypt N '[password]'}, 'odbc' 

The algorithm used to hide the password before transfer across the network is the same as that used to obfuscate native authentication credentials, as described in the earlier section "Authentication and Authorization." The password is converted to the Unicode character set, effectively alternating null bytes throughout the string, and then it is XORed with the constant value 0xA5 before it is transmitted. This is not encryption because it can easily be reversed without knowledge of a key. A stored procedure (decrypt_odbc_sql.txt) to convert obfuscated data back to plaintext can be downloaded from SQLSecurity.com ( http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26 ).

DTS Package Passwords

DTS (Data Transformation Services) are a feature of SQL Server that enable data from multiple different sources to be manipulated and consolidated easily. A DTS package is a set of tasks that are executed either sequentially or in parallel and connect to data sources, retrieve data, perform transformations, or export data. DTS packages contain access credentials for all data sources that they need to connect to.

Two stored procedures exist in the msdb database, sp_enum_dtspackages and sp_get_dtspackage, which can be executed by the public role by default. The first allows listing of all defined DTS packages and the second can be used to return the entire package. Sp_get_dtspackage takes three arguments ”the package name, the package id, and the package version ”all of which are returned by sp_enum_dtspackages. The DTS package itself is returned in the column packagedata:

 exec sp_get_dtspackage 'my_package', '{22BCCAE4-8B40-4854-825D-A0BD9EBA4DDC}',  '{A1657EE1-5E40-4DFB-89A5-7ED3B2F5CCB2}' 

An attacker could then insert the retrieved package data into his local SQL Server, and attempt to obtain the access credentials used by capturing the network traffic generated when the package is executed. To protect against this type of attack, permissions on the DTS package stored procedures should be restricted to database administrators.

The DTS Designer allows package metadata to be saved to SQL Server's Meta Data Services. If native authentication is used, credentials are saved in plaintext to the table RTblDBMProps in the msdb database. The column Col11120 contains the password:

 select Col11120 from msdb..RTblDBMProps where Col11119 = 'sa' 

Permissions on this table should be restricted to database administrators to prevent password disclosure. Prevention of all password disclosure issues can be achieved by using Windows-only authentication whenever possible.

Replication Passwords

SQL Server's replication features allow data to be distributed easily from one database server to another, and simplify its synchronization. They can allow for load balancing in high-traffic environments, where multiple servers share incoming connection requests and provide the same data. Replication features also provide the ability to easily keep a fail-over server up to date, which can be used to take over if a main server is unavailable for any reason. SQL Server replication systems involve one or more publisher servers that offer data to one or more subscriber servers.

In pre-Service Pack 3 installs of SQL Server 2000, creating a natively authenticating subscription to a publication on a SQL Server using the Enterprise Manager will write the encrypted password to a registry value. The password can be found in the string value SubscriberEncryptedPasswordBinary under the key:

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ Replication\Subscriptions\[PublisherServerName]:[PublisherDatabaseName]: [PublicationName]:[SubscriberServerName]:[SubscriberDatabaseName] 

The undocumented extended stored procedure xp_repl_help_connect, used to test connections to replication servers, can also be used to decrypt the password:

 declare @password nvarchar(256) set @password = '[encrypted password]'  exec xp_repl_help_connect @password OUTPUT select @password 

By default all users have read access to the encrypted password in the registry. It is strongly recommended that a SQL Server that is subscribed to a publisher using native authentication be upgraded to Service Pack 3 to avoid saving sensitive information in the registry. If this is not possible, permissions on all registry stored procedures, especially xp_regread and xp_instance_regread, should be tightly locked down.