SqlRole: NT AUTHORITY\SYSTEM can provide sysadmin to itself and other Logins when SYSTEM is only a public sql login.

[jonanoz]

Problem description

Provided that NT AUTHORITY\SYSTEM has a SQL Login on the instance, an invoke-dscresource call running as system can give itself, and any other login any SQL Role. Namely sysadmin.
This is true when:

• NT AUTHORTY\SYSTEM has a SQL Login on the instance.
• NT AUTHORITY\SYSTEM only has public role with default permissions: CONNECT SQL, VIEW ANY DATABSE

Additionally, opening SSMS as NT AUTHORITY\SYSTEM to manually test this behaves correctly. The SYSTEM account is unable to see other logins, nor elevate permissions to itself.

Verbose logs

PS C:\Windows\system32> whoami
nt authority\system
PS C:\Windows\system32> invoke-dscresource -Name 'SqlRole' -ModuleName 'SqlServerDsc' -Method set -Verbose -Property @{
>>     Ensure = 'Present'
>>     ServerName = 'servername'
>>     InstanceName = 'instancename'
>>     ServerRoleName = 'sysadmin'
>>     MembersToInclude = @('NT AUTHORITY\SYSTEM')
>> }
VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = Resourceset,'className' =
MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer servername with user sid xxx.
VERBOSE: [servername]: LCM:  [ Start  Set      ]  [[SqlRole]DirectResourceAccess]
VERBOSE: [servername]:                            [[SqlRole]DirectResourceAccess] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated
security. (xxx)
VERBOSE: [servername]:                            [[SqlRole]DirectResourceAccess] Connected to SQL instance 'servername'. (xxx)
VERBOSE: [servername]:                            [[SqlRole]DirectResourceAccess] Setting properties of the SQL Server role 'sysadmin'.
VERBOSE: [servername]:                            [[SqlRole]DirectResourceAccess] Adding login 'NT AUTHORITY\SYSTEM' to role 'sysadmin'.
VERBOSE: [servername]: LCM:  [ End    Set      ]  [[SqlRole]DirectResourceAccess]  in 0.1250 seconds.
VERBOSE: [servername]: LCM:  [ End    Set      ]    in  0.2030 seconds.
VERBOSE: Operation 'Invoke CimMethod' complete.

RebootRequired
--------------
False

DSC configuration

>>psexec -i -s powershell

>>
invoke-dscresource -Name 'SqlRole' -ModuleName 'SqlServerDsc' -Method set -Verbose -Property @{
    Ensure = 'Present'
    ServerName = 'servername'
    InstanceName = 'instancename'
    ServerRoleName = 'sysadmin'
    MembersToInclude = @('NT AUTHORITY\SYSTEM')
}

Suggested solution

Unless intended behavior, possible Bug.

SQL Server edition and version

Edition: SQL Server 2022
Version: 16.0.4135.14

SQL Server PowerShell modules

SQLPS v16.0

Operating system

win2019+

PowerShell version

Name                           Value                                                                                                                                    
----                           -----                                                                                                                                    
PSVersion                      5.1.20348.2849                                                                                                                            
PSEdition                      Desktop 

Name                           Value                                                                                                                                    
----                           -----                                                                                                                                    
PSVersion                      7.3.6                                                                                                                            
PSEdition                      Core

SqlServerDsc version

SqlServerDsc v17.0.0

[johlju]

The resource SqlRole is using Sql Server Management Objects (SMO) to connect to and instance and change roles. So if there are no permission, local security group, user right permission or anything else that gives SYSTEM access to the instance I suggest to raise this in a support case to Microsoft.

SqlServerDsc/source/DSCResources/DSC_SqlRole/DSC_SqlRole.psm1

Lines 497 to 536 in 85ea1ed

	function Add-SqlDscServerRoleMember
	{
	[CmdletBinding()]
	param
	(
	[Parameter(Mandatory = $true)]
	[ValidateNotNullOrEmpty()]
	[System.Object]
	$SqlServerObject,
	[Parameter(Mandatory = $true)]
	[ValidateNotNullOrEmpty()]
	[System.String]
	$SecurityPrincipal,
	[Parameter(Mandatory = $true)]
	[ValidateNotNullOrEmpty()]
	[System.String]
	$ServerRoleName
	)
	try
	{
	Test-SqlSecurityPrincipal -SqlServerObject $SqlServerObject -SecurityPrincipal $SecurityPrincipal
	Write-Verbose -Message (
	$script:localizedData.AddMemberToRole `
	-f $SecurityPrincipal, $ServerRoleName
	)
	$SqlServerObject.Roles[$ServerRoleName].AddMember($SecurityPrincipal)
	}
	catch
	{
	$errorMessage = $script:localizedData.AddMemberServerRoleSetError `
	-f $ServerName, $InstanceName, $ServerRoleName, $SecurityPrincipal
	New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
	}
	}