Knowledge Base

Search Knowledge Base

KB #240088: Securing against the Sysadmin

Type:

Tip

Summary:

SQL Server automatically allows members of the Sysadmin Role to view any and all data. However there are situations where you do not want a Sysadmin to view certain data, but you do want to enable them to maintain and administer the server.

Additional Information:

SQL Server automatically allows members of the Sysadmin Role to view any and all data. However there are situations where you do not want a Sysadmin to view certain data, but you do want to enable them to maintain and administer the server. The fact is, that you do not really need anyone in the Sysadmin role: you can assign lesser roles to logins to allow them to perform necessary operations without giving them access to databases. For example, you need someone in the Backup Admin Role to create / restore backups, someone in the Security Admin Role to add/remove logins, etc.This is particularly valuable if you are distributing a SQL-based application and wish to prevent the customer from ad-hoc access of your database through Management Studio or Enterprise Manager.

This procedure guides you through the steps of removing Logins from the Sysadmin Role and assigning them other, lesser Roles.

Definition

Foreign Admin is the Administrator you are attempting to lock out of your application database, but who may need to perform administrative tasks such as Backup and Restore.

Prerequisites

The only prerequisites for this technique are:

  • You must use SQL Authentication Mode or Mixed Mode (SQL and Windows).
  • You must assign an SA Password to the instance. In addition, the SA Password should be unknown to anyone who is not supposed to have access to the database.

Overview of Installation Process

  • Install Encryptionizer.
  • Install instance of SQL or SQL Express if not already present.
  • Use build3(secbuild.dll) or bldcmd.exe to secure instance of SQL.
  • Limit Sysadmin access using procedure outlined below.
  • Copy in encrypted database, or encrypt database on-the-fly using N_ENCODEFILE, N_ENCODEFILEEX or SECTOOL.EXE.

Procedure

  • The “SA” login password must be changed to something unknown to the Foreign Admin.
  • The Builtin\Administrators login must be “demoted” from the sysadmin role. However, it can be given any necessary permissions required to handle administrative tasks, such as backups. More on this in section Limiting Sysadmin Access.

This procedure “demotes” members of the sysadmin role so they no longer have sysadmin rights. In addition, it changes the password for SA, since it cannot be demoted.The following procedure can be performed from Enterprise Manager, or from Query Analyzer. In either case, it requires SA access. If you are using Query Analyzer, we list the procedures you will need for each step.

1. If you are currently using only Windows Authentication Mode, you must switch to Mixed or SQL Authentication Mode.

From Managerment Studio or Enterprise Manager:

  • Right click on instance
  • Select Properties (not Registration Properties)
  • Under Authentication, select:
    – SQL 2005/2008: SQL Server Authentication (which is really “mixed”)
    – SQL 2000: SQL Server and Windows

1a. If you will be doing an unattended installation of SQL, make sure to specify the /SECURITYMODE and /SAPWD parameters in the installation command line. For more information search for “sql server unattended installation” in msdn.microsoft.com.

2. Change SA password to a value unknown to the Foreign Admin. If doing this programmatically you can use the system stored procedure: sp_password.

3. “Demote” Builtin\Administrators from the Sysadmin server role.

If you wish, you can add the Foreign Admin to any other Server Roles needed to accomplish permitted administrative tasks. Typically these might be:

  • securityadmin: to allow them to add logins to the server
  • dbcreator: to allow them to restore databases
  • bulkadmin: to allow them to perform bulk insert and bcc.

If you wish to do this programmatically, you can use the system stored proceduressp_dropsrvrolemember and sp_addsrvrolemember.See MSDN Article http://msdn.microsoft.com/en-us/library/ms188659.aspx for a list of Fixed Server Roles.

3a. If there are other logins in the SysAdmin role, they should also be “demoted”. If this is a new installation, it is likely that only Builtin\Administrators is in the SysAdmin Role.

To see what other logins are in the SysAdmin role use the system stored proceduresp_helpsrvrolemember 'sysadmin'.

4. Revoke the Server Permissions listed below from all logins where they are granted. These Permissions may allow a Foreign Admin “back door” access to the encrypted database(s):

  • CONTROL SERVER
  • VIEW ANY DATABASE
  • ALTER ANY DATABASE
  • CREATE ANY DATABASE

See MSDN Article http://msdn.microsoft.com/en-us/library/ms191291.aspx for a list of Server Permissions.

5. Remove the Guest user from Application database (e.g., Northwind). This should always be done, whether you are encrypting a database or not, unless you have a specific reason to keep the Guest user. (Note 1)

To do this programmatically use the system stored procedure sp_dropuser.

5a. We strongly recommend dropping Guest as a server login, as well. You can do this programmatically using the system stored procedure sp_droplogin.

6. In order to permit Builtin\Administrators (or any other login) to backup the database being protected, add them to the fixed database role db_backupoperator.

We also recommend that you add them to the following fixed database roles to ensure they are restricted from accessing the data:

  • db_denydatareader: to deny select
  • db_denydatawriter: to deny insert, update, delete

To do this programmatically use the system stored procedures sp_adduser andsp_addrolemember.7. Add a new login (Windows or SQL Authentication), e.g., ‘appuser’, which will be used by the application to connect to SQL. The login password should be unknown to the Foreign Admin. Then add the new login as a user of the database. No specific database roles are required as long as the Public role has not been changed from the default. (Note 1)

To do this programmatically use the system stored procedures sp_addlogin andsp_adduser.

Note 1: If you are distributing a SQL-based application, you can usually pre-configure your database with these steps before including it in your installation package.

Tip: We recommend encrypting the Master Database and specifying “Master Must Be Encrypted” in the SECADMIN Additional Options Screen, otherwise the Foreign Admin can replace the Master database and gain back the SysAdmin role.

Useful Procedures

Steps 3, 3a: The following routine will locate all logins that are in the sysadmin role, remove the user from the role, and then add the user to some other useful roles.

DECLARE @errnum integer, @loginname varchar(128)
Create Table #members(ServerRole varchar(128), Membername varchar(128), MemberSID varbinary(128));
Insert #members EXEC sp_helpsrvrolemember 'sysadmin'
DECLARE temp_cursor CURSOR FOR 
select Membername from #members where MemberSID != 0x01 and MemberName not like '##%';
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @loginname
WHILE (@@FETCH_STATUS != -1) BEGIN
IF (@@FETCH_STATUS != -2) BEGIN   
SET @loginname = RTRIM(@loginname) 
exec @errnum = sp_dropsrvrolemember @loginname , 'sysadmin'
/* optional - add to securityadmin, dbcreator, and bulkadmin roles */
exec @errnum = sp_addsrvrolemember @loginname, 'securityadmin'
exec @errnum = sp_addsrvrolemember @loginname, 'dbcreator'
exec @errnum = sp_addsrvrolemember @loginname, 'bulkadmin'
END
FETCH NEXT FROM temp_cursor INTO @loginname
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
Drop Table #members

Step 4: The following procedure will revoke the permissions mentioned in Step 4 above

DECLARE @errnum integer, @loginname varchar(128), @sql varchar(1024), @cPermission varchar(128)
DECLARE temp_cursor CURSOR FOR
select Name, permission_name from sys.server_permissions 
INNER JOIN sys.server_principals ON
sys.server_permissions.grantee_principal_id = 
sys.server_principals.principal_id
WHERE ([permission_name]='CONTROL SERVER' OR
[permission_name]='VIEW ANY DATABASE' OR
[permission_name]='ALTER ANY DATABASE' OR
[permission_name]='CREATE ANY DATABASE') AND
[state] != 'D' AND [state] != 'R' AND
sys.server_principals.type != 'C'
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @loginname, @cPermission
WHILE (@@FETCH_STATUS != -1) BEGIN
IF (@@FETCH_STATUS != -2) BEGIN
Set @loginname = RTRIM(@loginname)
set @sql = 'Use master; REVOKE '+@cPermission+' TO ['+@loginname+'] CASCADE;'
execute(@sql)
if @errnum <> 0
break
END
FETCH NEXT FROM temp_cursor INTO @loginname, @cPermission
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
Top