© Peter A. Carter 2018
Peter A. CarterSecuring SQL Serverhttps://doi.org/10.1007/978-1-4842-4161-5_11

11. Hijacking an Instance

Peter A. Carter1 
(1)
London, UK
 

In earlier versions of SQL Server, the BUILTINADMINISTRATORS group was added to the sysadmin role by default. This meant that the default behavior of SQL Server was to allow anybody with local administrator permissions to the server on which SQL Server was installed the ability to do anything within the instance. This posed two issues: first, the lack of separation of duties. Skilled Windows administrators are not necessarily experienced DBAs, and even if they are, they should not have administrative control of SQL Server, unless they have the responsibility of managing SQL Server instances. With automatic administrator rights, however, they could do whatever they wanted with the instance. Second, if an attacker were to gain control of a server, they would also gain control of the SQL Server instance.

This meant that it was a good practice to remove the sysadmin permission from the BUILTINADMINISTRATORS group as soon as the instance was built. There were many environments where this was overlooked, however. In SQL Server 2008, Microsoft changed the default behavior so that local administrators were no longer SQL Server administrators by default and needed to be added manually.

There is still a way for local administrators to gain administrator control over a SQL instance, however, which can potentially be used as a vulnerability by attackers. The following sections will explore this potential vulnerability and how to reduce the risk of attack.

Hijacking an Instance

There are various scenarios that can occur where administrators can become “locked out” of an SQL Server instance. For example, imagine that an instance was configured to use Windows Authentication, only, and the DBA who built the instance added only his own Windows account as an administrator. If he were to leave the company, and his domain account was deleted, then nobody could regain administrative control of the instance.

Imagine another example, where a DBA has configured an instance with mixed mode authentication but have not added any Windows Users to the sysadmin role. Instead, the DBA relies on the use of the sa account for administrative activity. If the password to the sa account was lost and forgotten, then the DBA would not be able to regain administrative control of the instance.

For these reasons, Microsoft has built in a “back-door” method for Windows administrators to regain control of an instance where the DBAs have been “locked out.” The method is a documented and supported procedure. Therefore, if an attacker were to gain local administrative control of a server hosting SQL Server, he would not need to look far to work out how to take control.

While this method has been set up for restoring proper access to a server, it has a downside. Any Windows user with administrative access to the server can do this for improper reasons.

To take administrative control of an instance, open SQL Server Configuration Manager, navigate to SQL Server Services in the left-hand pane, and then select Properties from the context menu of the SQL Server service in the right-hand pane. This will cause the Properties window for the SQL Server service to be invoked, as shown in Figure 11-1.
../images/395795_2_En_11_Chapter/395795_2_En_11_Fig1_HTML.jpg
Figure 11-1

Service properties–log-on tab

On this tab, use the Stop button to stop the database engine service. If the SQL Server Agent service is running, you will be prompted to stop the service, as shown in Figure 11-2. This is because the SQL Server Agent service is dependent on the database engine service and cannot run without it.
../images/395795_2_En_11_Chapter/395795_2_En_11_Fig2_HTML.jpg
Figure 11-2

Stop SQL Server agent prompt

You should select Yes to continue. Once the services are stopped, navigate to the Startup Parameters tab of the Properties dialog box, as illustrated in Figure 11-3.
../images/395795_2_En_11_Chapter/395795_2_En_11_Fig3_HTML.jpg
Figure 11-3

Service properties-startup parameters tab

Table 11-1

Database Engine Startup Parameters

Startup Parameter

Description

-d

Specifies the path to the master database .mdf file

-e

Specifies the path to the error log file

-E

Increases the number of extents allocated to each data file during the round-robin process

-l

Specifies the path to the master database log file. Usually  .ldf

-c

Prevents the call to Service Control Manager when the instance is started from the command line, rather than as a service

-f

Starts the instance in minimal configuration mode. This allows DBAs to troubleshoot an instance that cannot start due to a misconfiguration.

-g

Specifies the amount of memory, in megabytes, that should be allocated to the instance but not allocated to the buffer pool

-m

Starts the instance in single-user mode

-mClientApplicationName

Starts the instance in what is essentially a “single application mode.” Only connections from the specified application are permitted.

-n

Prevents events generated by the instance being written to the Windows Application Log

-s

Allows the instance to start using the binaries of a different instance

-T

Specifies a global trace flag, which should be turned on when the instance starts. Multiple -T parameters can be specified.

-x

Disables some performance monitoring features. Specifically:

• SQL Server-related performance monitor counters

• CPU time statistics

• Buffer cache hit ratio statistics

• Information collection for DBCC SQLPERF

• Information collection for a range of dynamic management objects

• A range of extended events event points

On this tab, we will configure the instance to start in single user mode. To do this, we will add the -m option as a startup parameter. A complete list of documented startup parameters for the database engine service can be found in Table 11-1.

Tip

Startup parameters are case-sensitive. For example, -e has a different usage to –E.

You will now navigate back to the Log On tab of the service Properties dialog box and use the Start button to start the Database Engine service in single-user mode.

Caution

Because you are starting the instance in single-user mode, it is important that you do not start the SQL Server Agent service, otherwise this service will take the only available connection to the instance, and you will be unable to log in.

You will now need to open SQL Server Management Studio. In this scenario, however, you will need to run the application As Administrator. To do this, select Run As Administrator, from the context menu of the application shortcut, as shown in Figure 11-4.
../images/395795_2_En_11_Chapter/395795_2_En_11_Fig4_HTML.jpg
Figure 11-4

Run SSMS As Administrator

Once you have opened SSMS, you should not connect to Object Explorer, as some usages of Object Explorer will cause multiple connections to be established, which will not work. Instead, you should choose Cancel in the Connect to Server dialog box. You can then use the New Query button to open a new query window.

The next step in hijacking the instance is to create an account with administrative permissions. This can be achieved using the script in Listing 11-1.

Note

Full details of managing logins and server roles can be found in Chapter 2.

CREATE LOGIN Attacker
WITH PASSWORD = 'Pa$$w0rd12345678' ;
GO
ALTER SERVER ROLE sysadmin
ADD MEMBER Attacker ;
GO
Listing 11-1

Create a Login With Administrative Permissions

Once this script has been run, the attacker will can put the instance back into a normal operating mode by removing the -m startup parameter and restarting the instance. He will now have full administrative control of the instance by using the attacker login.

Tip

While the example above uses a SQL Login, the attacker could equally add their Windows credentials to the sysadmin server role.

Protecting Against Hijacking

The nature of instance hijacking makes it a difficult hack to protect against, but not impossible. The method that I adopt is to use Logon Triggers. The following section will introduce the concept of Logon Triggers and discuss how they can be used to prevent an instance hijacking.

Understanding Logon Triggers

Triggers are code routines, which are very similar in nature to stored procedures. The difference between them is that a stored procedure needs to be manually executed, whereas a trigger will fire automatically when an event is triggered. SQL Server supports three types of triggers. A DML trigger will fire in response to an INSERT, UPDATE, or DELETE event occurring in a database table. A DDL trigger will fire in response to a DDL event occurring at a database or instance level. This includes statements such as CREATE, ALTER, or DROP but can also respond to other events, such as UPDATE STATISTICS. A Logon Trigger will fire every time a successful logon event occurs against the instance. This corresponds to the AUDIT_LOGON extended event.

Logon Triggers can be used for many purposes. For example, they can be used as a method for auditing logons, as the trigger could write to a table each time a logon occurs. The advantage of using Logon Triggers for this purpose is that they will fire synchronously, as opposed to asynchronously, meaning that it needs to complete successfully for the logon to occur. They can also be used for purposes as disparate as limiting the number of concurrent connections to an instance or to ensure that no users can log in to the instance during out-of-hours maintenance windows.

Inside the Logon Trigger, you will have access to useful system functions, which will help you build an algorithm to determine if the logon should be allowed or if it should be terminated. For example, the ORIGINAL_NAME() function will allow you to determine the name of the SQL Login that is attempting to authenticate, while functions such as IS_SRVROLEMEMBER() will help you to determine a login’s permissions.

Table 11-2 details the syntax components of the CREATE TRIGGER statement when being used to create a Logon Trigger.
Table 11-2

CREATE TRIGGER Syntax Components

Component

Description

ON

For Logon Triggers, the value supplied will always be ALL SERVER. DDL triggers may use ALL SERVER or DATABASE.

WITH

Allows for Logon Trigger options to be passed. These options are detailed in Table 11-3.

FOR | AFTER

FOR and ALL are interchangeable, and imply that the logon event will occur before the trigger is fired. The code within the trigger will occur within the same transaction, however, so ROLLBACK can be used to prevent the logon from occurring. DML triggers can use INSTEAD OF, which means that the original statement is never fired, but this option is not applicable to Logon Triggers or DDL triggers.

Table 11-3 details the WITH options that are available when creating a Logon Trigger.
Table 11-3

Logon Trigger WITH Options

Option

Description

ENCRYPTION

Obfuscates the definition of the Logon Trigger.

EXECUTE AS

Causes the code within the Logon Trigger to be executed under the security context of a different user. Please see Chapter 13 for a broader discussion of the EXECUTE AS clause.

Using Logon Triggers to Prevent Instance Hijacking

For our purpose, we need to create a Logon Trigger that will prevent users from logging in when the instance is in single-user mode, unless they are instance administrators. The obvious solution to this would, at first glance, appear to be to use the sys.dm_server_registry dynamic management view to check if the instance is in single-user mode, and if it is, then use the IS_SRVROLEMEMBER() function to determine if a login is part of the sysadmin server role.

The columns returned by the sys.dm_server_registry dynamic management view are detailed in Table 11-4.
Table 11-4

Columns Returned by sys.dm_server_registry

Column

Description

registry_key

The name of the registry key

value_name

The name of the registry key value

value_data

The value of the registry key

To use the sys.dm_server_registry DMV to check if the instance is in single-user mode, you can use the query in Listing 11-2.
SELECT COUNT(*)
FROM sys.dm_server_registry
WHERE value_name LIKE 'SQLArg%' AND value_data = '-m' ;
Listing 11-2

Use the sys.dm_server_registry DMV to Check for Single-User Mode

If the query in Listing 11-2 returns a value of 1, then the instance is in single-user mode. If it returns a value of 0, then the instance is not in single-user mode.

The IS_SRVROLEMEMBER() function accepts the parameters detailed in Table 11-5.
Table 11-5

Parameters Accepted by IS_SRVROLEMEMBER()

Parameter

Description

Role

The name of the server role for which you wish to check membership

Login

The name of the login for which you wish to check membership

If the function returns a value of 1, then the Login is a member of the role. If the function returns a result of 0, then the login is not part of the role.

The script in Listing 11-3 demonstrates how a Logon Trigger could be created using this approach.
CREATE TRIGGER PreventHijack
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
       DECLARE @SingleUser INT ;
       SET @SingleUser =
       (
             SELECT COUNT(*)
         FROM sys.dm_server_registry
         WHERE value_name LIKE 'SQLArg%' AND value_data = '-m'
       ) ;
       IF @SingleUser <> 0
       BEGIN
             IF IS_SRVROLEMEMBER('sysadmin', ORIGINAL_LOGIN()) <> 1
             BEGIN
                    ROLLBACK ;
             END
       END
END
Listing 11-3

Create a Logon Trigger

The trouble with this approach is how the instance hijack attack works. When SQL Server is in single-user mode, when a user that is an administrator of the local server attempts to login, SQL Server will add them the sysadmin server role and this will occur before the Logon Trigger fires. This means that the IS_SRVROLEMEMBER() evaluation will always resolve as true, making the trigger useless.

So, what if we were to remove the IS_SRVROLEMEMBER() evaluation entirely and simply stop the instance being accessed if it is in single user mode? Well, this would work, but it would pose a significant risk. For example, if the system databases needed to be rebuilt, then this could only happen in single-user mode and would no longer be possible.

Instead, the full solution is to create a stale cache of the members of the sysadmin server role. We can then make the trigger evaluate the login against the stale cache, as opposed to the current role members. To do this, we should create a table in the master database, called Administrators. This can be achieved using the command in Listing 11-4.
USE MASTER
GO
CREATE TABLE dbo.SysadminMembers
(
     ID     INT     IDENTITY     PRIMARY KEY     NOT NULL,
     LoginName      SYSNAME      NOT NULL
) ;
Listing 11-4

Create the Administrators Stale Cache

Tip

The SYSNAME data type is a synonym for NVARCHAR(128). SQL Server uses this type to store identifiers.

We can identify the logins that should be in the SysadminMembers table by using the sys.server_role_members and sys.server_principals catalog views. The columns returned by the sys.server_role_members catalog view are described in Table 11-6.
Table 11-6

Columns Returned by sys.server_role_members

Column

Description

role_principal_id

The internal ID of the server role

member_principal_id

The internal ID of the login

The columns returned by the sys.server_principals catalog view can be found in Table 11-7.
Table 11-7

Columns Returned by sys.server_principals

Column

Description

name

The name of the security principal

principal_id

The internal ID of the security principal

sid

The security identifier of the principal

type

A single character code, denoting the type of the principal. Possible values are:

• S - Indicates an SQL login

• U - Indicates a Windows login

• G - Indicates a Windows group

• R - Indicates a Server role

• C - Indicates a login that is mapped to a certificate

• K - Indicates a login that is mapped to an Asymmetric key

type_desc

A textual description of the principal’s type

is_disabled

Indicates if the principal’s disabled state.

• 0 - Indicates that the principal is enabled

• 1 - Indicates that the principal is disabled

create_date

The date and time that the principal was created

modify_date

The date and time that the principal was last modified

default_database_name

The name of the principal’s default database

default_language_name

The default language configured for the principal

credential_id

If the principal has a credential associated with it, then this column returns the ID of the credential. A credential is a mechanism used by SQL Server to allow a login to interact with the operating system.

owning_principal_id

If the principal is a server role, the owning_principal_id column returns the ID of the principal who owns the server role.

is_fixed_role

If the principal’s type is R, indicates if the server role is fixed, or user-defined. For principal’s that are not server roles, this column always returns 0. For server roles, possible values are:

• 0 - Indicates a user-defined server role

• 1 - Indicates a fixed server role

The sys.server_role_members and sys.server_principals catalog views can be joined together, to retrieve a lists of server roles, with all associated members. These results can then be filtered on the sysadmin server role. Listing 11-5 demonstrates how to do this and then merge the results into the SysadminMembers table. The MERGE statement will delete any members from the table who are no longer associated with the sysadmin server role and insert any new members.
USE master ;
GO
MERGE INTO dbo.sysadminMembers AS Target
USING (
      SELECT
               Roles.name AS RoleName
             , Members.name AS MemberName
       FROM sys.server_role_members RoleMembers
       INNER JOIN sys.server_principals Roles
             ON RoleMembers.role_principal_id = Roles.principal_id
       INNER JOIN sys.server_principals AS Members
             ON RoleMembers.member_principal_id = Members.principal_id
       WHERE Roles.name = 'sysadmin'
) AS Source
ON (Source.MemberName = Target.LoginName)
WHEN NOT MATCHED BY TARGET THEN
       INSERT (LoginName)
       VALUES (MemberName)
WHEN NOT MATCHED BY SOURCE THEN
       DELETE ;
Listing 11-5

Update SysadminMembers Table

Now that we know how to populate our stale cache, we will need to schedule the population to occur on a daily basis. We would not want to run the population on an ad-hoc basis, because that would risk the cache becoming “too stale” and risk being unable to access the instance in single-user mode.

The scheduling tool within SQL Server is SQL Server Agent. The following sections will provide a brief overview of SQL Server Agent functionality before discussing how to schedule our table update query to run.

Understanding Server Agent

Server Agent is a service that provides the ability to create automated routines, called Jobs, with decision-based logic and schedule them to run one time only, on a re-occurring basis, when the Server Agent service starts or when a CPU idle condition occurs.

It also implements Alerts that will allow you to respond to a wide range of conditions, including errors, performance conditions, or WMI (Windows Management Instrumentation) events. Responses can include notifying Operators and executing Jobs.

After introducing you to the concepts surrounding Server Agent, the following sections will discuss the Server Agent security model, how to create and manage Jobs, and how to create Alerts.

Server Agent Concepts

Server Agent is implemented using Jobs, Schedules, Alerts, and Operators. The following sections will introduce you to each of these concepts.

Schedules
A schedule defines the time or condition that will trigger a Job to start running. A schedule can be defined as:

  • One time-which allows you to specify a specific date and time

  • Start automatically when Server Agent starts-which is useful if there is a set of tasks that should run when the instance starts, assuming that the Server Agent service is configured to start automatically. This can be troublesome, however, if you need to restart the Server Agent service without restarting the Database Engine.

  • Start when CPU becomes idle-which is useful if you have resource-intensive jobs that you do not wish to impact user activity. This should only be used for short-running tasks, however, because the CPU may not be idle for long…

  • Recurring-which allows you to define a complex schedule, with start and end dates, which can reoccur daily, weekly, or monthly. If you schedule a job to run weekly, then you can also define multiple days on which it should run. If you define the schedule as daily, you can opt to have the trigger occur once daily, on an hourly basis, every minute or even as frequently as every 10 seconds. If reoccurring based on second, hour, or minute, then it is possible to define start and stop times within a day. This means that you could schedule a job to run every 1 minute— for example, between 18:00 and 20:00.

Individual schedules can be created for each job, or you can choose to define a schedule and use this to trigger multiple jobs that need to run at the same times. A job can have zero or more schedules.

Operators

An Operator is an individual or team that is configured to receive a notification of job status, or in the event that an Alert is triggered. Operators can be configured to be notified through e-mail, NET SEND, or Pager.

If Operators are configured to be notified through e-mail, then Database Mail must also be configured, specifying the address and port of the SMTP Replay server that will deliver the messages. If Operators are configured to be notified via NET SEND, then the Server Agent Windows service will be dependent on the NET SEND service, as well as the SQL Server service, in order to start. If Operators are configured to be notified by Pager, then Database Mail must be used to relay the messages to the e-mail to Pager service. It is worthy noting, however, that the Pager and NET SEND options are deprecated and you should avoid using them.

Caution

Introducing reliance on the NET SEND service can increase operational risk.

When using Pager alerts, each Operator can be configured with days and times that they are on duty. This is very useful in 24/7 organizations, who run either support shifts or follow the sun support models for operational support. This functionality also allows you to configure each Operator with different shift patterns on weekdays, Saturdays, and Sundays.

Jobs
A job is comprised of a series of actions that should be performed. Each action is known as a job step. Each job step can be configured to perform an action within one of the following categories:

  • ActiveX scripts

  • Operating system commands

  • PowerShell scripts

  • Replication Distributor tasks

  • Replication Merge Agent tasks

  • Replication Queue Reader Agent tasks

  • Replication Snapshot Agent tasks

  • Replication Transaction Log Reader tasks

  • Analysis Services commands

  • Analysis Services queries

  • SSIS packages

  • T-SQL commands

Each job step can be configured to run under the context of the service account running the Server Agent service or can be run under a Proxy account, which runs under the context of a credential. Each step can also be configured to retry a specific number of times, with an interval between each retry.

Additionally, On Success and On Failure actions can be configured individually for each job step. This allows DBAs to implement decision-based logic and error handling, as outlined in Figure 11-5.
../images/395795_2_En_11_Chapter/395795_2_En_11_Fig5_HTML.jpg
Figure 11-5

Descision Tree logic

Each Job can be run on a schedule, which can be created specifically for the Job that you are configuring or shared between multiple Jobs, which should all run on the same schedule.

Notifications can also be configured for each Job. A notification alerts an Operator to the success or failure of a Job and can also be configured to write entities to the Windows Application Event Log or even delete the Job.

Alerts
Alerts respond to events that occur in SQL Server and have been written to the Windows Application Event Log. Alerts can respond to the following categories of activity:

  • SQL Server events

  • SQL Server performance conditions

  • WMI events

When created against SQL Server events category, then an Alert can be configured to respond to a specific error message or to a specific error severity level that occurs. Alerts can also be filtered so that they will only fire if the error or warning contains specific text. They can also be filtered by the specific database in which they occur.

When created against the SQL Server performance conditions category, then Alerts are configured to be triggered if a counter falls below, becomes equal to, or rises above a specified value. When configuring such an alert, you will need to select the performance object, which is essentially the category of performance condition, the counter within that performance object, and the instance of the counter against which you wish to alert. So, for example, to trigger an Alert in the event that the Percentage Log Used, for the WideWorldImporters database rises above 70%, you would select the Databases object, the Percent Log Used Counter, and the WideWorldImporters instance and configure the Alert to be triggered if this counter rises above 70. A complete list of performance objects available to Alerts is detailed below:

  • Access Methods

  • Availability Replica

  • Batch Resp Statistics

  • Broker Activation

  • Broker Statistics

  • Broker TO Statistics

  • Broker/DBM Transport

  • Buffer Manager

  • Buffer Node

  • Catalog Metadata

  • CLR

  • Cursor Manager by Type

  • Cursor Manager Total

  • Database Replica

  • Databases

  • Deprecated Features

  • Exec Statistics

  • FileTable

  • General Statistics

  • HTTP Storage

  • Latches

  • Locks

  • Memory Broker Clerks

  • Memory Manager

  • Memory Node

  • Plan Cache

  • Resource Pool Stats

  • SQL Errors

  • SQL Statistics

  • Transactions

  • User Settable

  • Wait Statistics

  • Workload Group Stats

  • XTP Cursors

  • XTP Garbage Collection

  • XTP Phantom Processor

  • XTP Storage

  • XTP Transaction Log

  • XTP Transactions

Server Agent Security

Access to Server Agent is controlled via Database Roles, and job steps can run under the context of either the Server Agent service account, or using separate proxy accounts that map to credentials. Both of these concepts will be explored in the following sections.

Server Agent Database Roles
Other than members of the sysadmin server role, who have full access to Server Agent, access is granted to Server Agent using fixed Database Roles within the MSDB Database. The following roles are provided:

  • SQLAgentUserRole

  • SQLAgentReaderRole

  • SQLAgentOperatorRole

The permissions provided by the roles are detailed in Table 11-8.
Table 11-8

Server Agent Permissions Matrix

Permission

SQLAgentUserRole

SQLAgentReaderRole

SQLAgentOperatorRole

CREATE/ALTER/DROP Operator

No

No

No

CREATE/ALTER/DROP Local Job

Yes (Owned only)

Yes (Owned only)

Yes (Owned only)

CREATE/ALTER/DROP multiserver Job

No

No

No

CREATE/ALTER/DROP Schedule

Yes (Owned only)

Yes (Owned only)

Yes (Owned only)

CREATE/ALTER/DROP Proxy

No

No

No

CREATE/ALTER/DROP Alerts

No

No

No

View list of Operators

Yes

Yes

Yes

View list of local Jobs

Yes

Yes

Yes

View list of multiserver Jobs

No

Yes

Yes

View list of Schedules

Yes (Owned only)

Yes

Yes

View list of Proxies

Yes

Yes

Yes

View list of Alerts

No

No

No

Enable/disable Operators

No

No

No

Enable/disable local Jobs

Yes (Owned only)

Yes (Owned only)

Yes

Enable/disable multiserver Jobs

No

No

No

Enable/disable Schedules

Yes (Owned only)

Yes (Owned only)

Yes

Enable/disable Alerts

No

No

No

View Operator properties

No

No

Yes

View local Job properties

Yes (Owned only)

Yes

Yes

View multiserver Job properties

No

Yes

Yes

View Schedule properties

Yes (Owned only)

Yes

Yes

View Proxy properties

No

No

Yes

View Alert properties

No

No

Yes

Edit Operator properties

No

No

No

Edit local Job properties

No

Yes (Owned only)

Yes (Owned only)

Edit multiserver job properties

No

No

No

Edit Schedule properties

No

Yes (Owned only)

Yes (Owned only)

Edit Proxy properties

No

No

No

Edit Alert properties

No

No

No

Start/stop local Jobs

Yes (Owned only)

Yes (Owned only)

Yes

Start/stop multiserver Jobs

No

No

No

View local Job history

Yes (Owned only)

Yes

Yes

View multiserver Job history

No

Yes

Yes

Delete local Job history

No

No

Yes

Delete multiserver Job history

No

No

No

Attach/detach Schedules

Yes (Owned only)

Yes (Owned only)

Yes (Owned only)

Server Agent Proxy Accounts

By default, all job steps will run under the context of the Server Agent service account. Adopting this approach, however, can be a security risk, as it may potentially need to be granted a large number of permissions to the instance and objects within the Operating System.

To mitigate this risk and follow the principle of least privilege, you should instead consider using Proxy accounts. Proxies are mapped to Credentials within the instance level and can be configured to run only a subset of step types. For example, you could configure one Proxy to be able to run Operating System commands, while configuring another Proxy to be able to run only PowerShell scripts. This means that you can minimize the permissions that each Proxy requires.

For job steps with the Transact-SQL script step type, it is not possible to select a Proxy account. Instead, there is a Run as user option, which will allow you to select a Database User to use as the security context to run the script. This option uses the EXECUTE AS functionality in T-SQL to change the security context.

Creating A SQL Server Agent Job

We will now create an SQL Server Agent job, which will run each morning to synchronize the SysadminMembers table. To do this, drill through SQL Server Agent in Object Explorer and select New Job from the context menu of Jobs. This will invoke the New Job dialog box. The General page of the New Job dialog box is illustrated in Figure 11-6.
../images/395795_2_En_11_Chapter/395795_2_En_11_Fig6_HTML.jpg
Figure 11-6

New Job dialog box-general page

On this page of the dialog box, we have given the job a meaningful name, specified that the job will be owned by the sa account and added a description for the job. Also, ensure that the Enabled check box is ticked. This means that after the job is created, the schedule will cause the job to fire. The job can still be run manually.

On the Steps page of the New Job dialog box, we will use the New button to invoke the New Job Step dialog box. The general page of the New Job Step dialog box can be seen in Figure 11-7.
../images/395795_2_En_11_Chapter/395795_2_En_11_Fig7_HTML.jpg
Figure 11-7

New Job Step dialog box-general page

Here, we have given the job step a meaningful name and specified its type as T-SQL. This means that the step will run a T-SQL script. The Run as drop-down does not apply to T-SQL steps, as proxy accounts are not supported. In the Database drop-down, we have selected the master database, as this is where the table is stored. In the command area of the screen, we have the script from Listing 11-5, which will synchronize the table. We can now use the OK button to return to the New Job dialog box’s steps page, which is illustrated in Figure 11-8.
../images/395795_2_En_11_Chapter/395795_2_En_11_Fig8_HTML.jpg
Figure 11-8

New Job dialog box-steps page

On the schedules page of the New Job dialog box, we will use the New button to invoke the New Job Schedule dialog box, which is illustrated in Figure 11-9.
../images/395795_2_En_11_Chapter/395795_2_En_11_Fig9_HTML.jpg
Figure 11-9

New Job Schedule dialog box

In this dialog box, we have given the schedule a meaningful name and selected a frequency type of Daily. This automatically updates the options available in the dialog box so that they are relevant to a daily schedule. We have configured the schedule so that it will run every day, at midnight. We can now use the OK button to return to the New Job dialog box. There are no other options that we need to configure in the New Job dialog box, so we can now use the OK button in the New Job dialog box to create the job.

Putting it all Together

Now that we have a stale cache of sysadmin role members, which is automatically synchronized each day, we can create the final Logon Trigger. The trigger will check to see if the instance is in single-user mode. If it is, then it will check to see if the user attempting to access the instance is in the SyadminMembers table. If the user is not in the table, then it will use the ROLLBACK statement to terminate the login.

The script in Listing 11-6 demonstrates how to create the Logon Trigger.
CREATE TRIGGER PreventHijack
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
       DECLARE @SingleUser INT ;
       SET @SingleUser =
       (
             SELECT COUNT(*) FROM sys.dm_server_registry WHERE value_data = '-m'
       ) ;
       IF @SingleUser <> 0
       BEGIN
             IF (
                    SELECT COUNT(*)
                    FROM dbo.sysadminmembers
                    WHERE LoginName = ORIGINAL_LOGIN()
                    ) = 0
             BEGIN
                    ROLLBACK ;
             END
       END
END
Listing 11-6

Create the Final Logon Trigger

If an attacker were now attempting to hijack the instance, then they would receive an error stating that the login failed due to trigger execution.

Summary

Hijacking an instance involves using a loop hole designed to avoid SQL Server administrators becoming locked out of an instance due to forgetting the sa password or administrators leaving the organization. An attacker who has obtained administrator permissions to the local server can start the instance in single-user mode and be given sysadmin rights to the instance. Once they have accessed the instance in this way, they can either steal the data that they require, or they can grant themselves permanent elevated permissions before restarting the instance in standard, multi-user mode.

The nature of an instance hijack attack makes it difficult, but not impossible, to protect against. A Logon Trigger can be used to prevent such attacks from taking place. The Logon Trigger will check if the instance is in single-user mode, and if it is, it will interrogate a stale cache of sysadmin role members to ensure that the user is a genuine, previously existing member of the sysadmin role. If the user is a genuine sysadmin member, it will allow the login to continue. If not, however, it will use ROLLBACK to terminate the login, and an error will be thrown to the attacker.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset