2

I need to implement this: https://www.stigviewer.com/stig/ms_sql_server_2016_instance/2020-12-16/finding/V-213934

In our instance IsClustered and IsHadrEnabled are both 0.

Using an automated tool it seems the NT AUTHORITY\SYSTEM account gets flagged for having these permissions:

    CONNECT SQL 
    
    SHUTDOWN 
    
    CREATE ENDPOINT 
    
    CREATE ANY DATABASE 
    
    CREATE AVAILABILITY GROUP 
    
    ALTER ANY LOGIN 
    
    ALTER ANY CREDENTIAL 
    
    ALTER ANY ENDPOINT 
    
    ALTER ANY LINKED SERVER 
    
    ALTER ANY CONNECTION 
    
    ALTER ANY DATABASE 
    
    ALTER RESOURCES 
    
    ALTER SETTINGS 
    
    ALTER TRACE 
    
    ALTER ANY AVAILABILITY GROUP 
    
    ADMINISTER BULK OPERATIONS 
    
    AUTHENTICATE SERVER 
    
    EXTERNAL ACCESS ASSEMBLY 
    
    VIEW ANY DATABASE 
    
    VIEW ANY DEFINITION 
    
    VIEW SERVER STATE 
    
    CREATE DDL EVENT NOTIFICATION 
    
    CREATE TRACE EVENT NOTIFICATION 
    
    ALTER ANY EVENT NOTIFICATION 
    
    ALTER SERVER STATE 
    
    UNSAFE ASSEMBLY 
    
    ALTER ANY SERVER AUDIT 
    
    CREATE SERVER ROLE 
    
    ALTER ANY SERVER ROLE 
    
    ALTER ANY EVENT SESSION 
    
    CONNECT ANY DATABASE 
    
    IMPERSONATE ANY LOGIN 
    
    SELECT ALL USER SECURABLES 
    
    CONTROL SERVER

Manual check confirms this. It is not supposed to have anything more than CONNECT SQL and VIEW ANY DATABASE.

The fix text in that STIG has the query for revoking the permissions, but I discovered that you cannot revoke these without taking off the SysAdmin role inside SQL Server Management Studio. Once I did this, I was able to remove all the permissions. This is where I got that idea: Unable to revoke permissions for 'NT AUTHORITY\SYSTEM' on SQL Server 2016

This causes a problem: Without the SysAdmin Role for the NT AUTHORITY\SYSTEM account, our instance of Microsoft DPM throws an error when backing up the databases I configured the instance on: "Error: Unable to Configure Protection". According to Microsoft, there's a requirement to have the SysAdmin role enabled: https://learn.microsoft.com/en-us/azure/backup/backup-azure-scdpm-troubleshooting

Unfortunately, it seems like once I check "SysAdmin" again for the NT AUTHORITY\System account, all those permissions immediately re-enable.

Is there a way to have both the SysAdmin role and also remove extraneous permissions on this account? Alternatively does anyone have some experience with getting DPM to work right with backing up databases without having to have the SysAdmin Role?

This is a pretty severe finding; ultimately the weak link is DPM since the function of the application this SQL instance supports isn't impacted if we turn SysAdmin off, but I am rather wanting to keep DPM functional as well.

0

1 Answer 1

1

Is there a way to have both the SysAdmin role and also remove extraneous permissions on this account?

The immediate and straight forward answer is, "no". It is possible to allow logins that don't have sysadmin permissions to obtain them via other methods and not have it directly granted to it, however that's very targeted and beyond the scope of this forum in terms of implementation, let alone outside the support scope of 3rd party applications that haven't been built to utilize those methods.

According to Microsoft, there's a requirement to have the SysAdmin role enabled [....]

Then sysadmin is needed for that login.

Alternatively does anyone have some experience with getting DPM to work right with backing up databases without having to have the SysAdmin Role?

This seems that it would void the support policy for DPM. Much as Charlieface has pointed out, create a local or domain account for DPM to use and apply the permissions it needs to that. This removes the need for those permissions on local system. Personally, this "finding" is pretty idiotic as someone who is able to interact with local system already has access over the entire server, which means they have full control over SQL Server. It's moot and whomever wrote this STIG should reevaluate (they won't) how they believe Windows security works.

In our instance IsClustered and IsHadrEnabled are both 0.

If you ever choose to use AGs or the such, it'll fail until more permissions are given. Again, I have personal issues with this as a "finding".

1
  • "local or domain account" specifically a MSA or gMSA (respectively) service account Commented Apr 18, 2023 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.