Surreptitiously Restrict Access

Business Case: As happens significantly more often than it should, the credentials to a privileged system account have slipped into broad use. In this instance, however,  security is not a major concern; the issue is that users have access to query databases directly through SQL Server Management Studio (SSMS) rather than through the custom application interface developed in order to control the scope of runnable queries.

Environment: The compromised credentials cannot easily be re-secured because they are used pervasively by application program interfaces (APIs) across the enterprise. An uncoordinated change of password could cause havoc, but system performance is flagging due to ad hoc, non-optimized query loading.

Approach: Unsecured credentials must be addressed and coordination / planning to do so should begin immediately. However, we can also take a few quick and easy steps to mitigate the likelihood of a significant performance hit. In this case, SQL’s resource governor should do the trick. We will configure the resource governor to look for logins using the affected credential and SSMS as an interface program. Once SQL connections have been classified we will limit the CPU and memory that our rogue connections have access to.

Demonstration Preparation: No preparation is necessary for demonstration purposes. However, you should confirm whether your resource governor is already in use before making any modifications to it. The following scripts will provide high-level information about your resource governor configuration. If your resource governor is in use, or has a configuration pending, take extra care to ensure any changes you make are in sync with your overall environment.

-- See if the resource governor is enabled and which user-defined
--   function (UDF) is being used for classifying connections
SELECT
    is_enabled
    , 'ClassifierUDF' =
        object_schema_name(classifier_function_id) + '.'
        + object_name(classifier_function_id)
FROM sys.resource_governor_configuration
;

-- See if the resource governor is in the process of being reconfigured
--   and which UDF is set to be used for classifying connections
SELECT
    is_reconfiguration_pending
    , 'PendingClassifierUDF' =
        object_schema_name(classifier_function_id) + '.'
        + object_name(classifier_function_id)
FROM sys.dm_resource_governor_configuration
;

Solution: In order to prevent rogue logins from overloading our SQL server, we will put them into a workload group that is assigned to a resource pool with limited resources.

-- Switch connection to the master database
USE master;
GO

-- If the workload group we intend to create already exists, drop it
IF EXISTS(
    SELECT name
    FROM sys.resource_governor_workload_groups
    WHERE name = N'wrkgrpLimited')
DROP WORKLOAD GROUP wrkgrpLimited
;
GO

-- If the resource pool we intend to create already exists, drop it
IF EXISTS (
    SELECT name
    FROM sys.resource_governor_resource_pools
    WHERE name = N'poolLimited')
DROP RESOURCE POOL poolLimited
;
GO

-- Create a resource pool to restrict server resource availability
CREATE RESOURCE POOL poolLimited
WITH (
    -- Adjust the following options as you desire
    MAX_CPU_PERCENT = 25
    , MAX_MEMORY_PERCENT = 25)
;
GO

-- Create a workload group for connections to be assigned
--   to the resource pool

CREATE WORKLOAD GROUP wrkgrpLimited
WITH (
    IMPORTANCE = MEDIUM)
USING poolLimited
;
GO

-- Modify the resource governor to remove the current classifer function
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = NULL)
;
GO

-- Apply the configuration changes
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- If the classifier function we intend to create already exists, drop it
IF EXISTS (
    SELECT *
    FROM sys.objects
    WHERE
        object_id = OBJECT_ID(N'dbo.ufnResourceGovernorClassifier')
        AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.ufnResourceGovernorClassifier
;
GO

-- Create the classifier function to assign connections to a workload group
CREATE FUNCTION ufnResourceGovernorClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    -- Add criteria appropriate to your situation
    IF SUSER_SNAME() = N'Compromised Login'
        AND PROGRAM_NAME
() LIKE 'Microsoft SQL Server Management Studio%'
    RETURN N'wrkgrpLimited'
    ;
    RETURN N'default';
END
;
GO

-- Modify the resource governor to assign a classifer function
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.ufnResourceGovernorClassifier)
;
GO

-- Apply the configuration changes
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Note that if you’d like to effectively eliminate this access path you can set the resource pool to use a maximum of 1% of CPU and a maximum of 1% of memory. While users could still connect (in other words, they would not get an error message indicating their access was denied), the performance of their connection would be so poor as to highly discourage its use. Also note that we could actively deny connections based on any number of parameters, including IP address, connection method, domain, or other factors. However, that cannot be done through the resource governor as user defined functions such as our classifier function cannot effect action beyond the scope of the function (such as killing a session, writing data, etc.).

Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: