none
Add permissions to user on a MSSQL Server via ARM Templates extension

    Întrebare

  • Good day.

    My work flow:
    I has deploy a several VMs with MSSQL(2012, 2014, 2016, 2017) via ARM Templates.

    I have a extension in the template that provides add user to sysadmin role(the simple PowerShell script):

    {
            "type": "Microsoft.Compute/virtualMachines/extensions",
            "name": "[concat(variables('sqlName'),'/adduserassqladmin')]",
            "location": "[resourceGroup().location]",
            "apiVersion": "2015-06-15",
            "dependsOn": [
              "[resourceId('Microsoft.Compute/virtualMachines/extensions/',variables('sqlName'),'joindomain') ]"
            ],
            "properties": {
              "autoUpgradeMinorVersion": true,
              "protectedSettings": {
                "commandToExecute": "[concat('powershell.exe .\\', parameters('addUserAsSQLAdminScriptName'), ' ', parameters('adminUsername'))]"
              },
              "publisher": "Microsoft.Compute",
              "settings": {
                "fileUris": [
                  "[concat(parameters('scriptLocation'), parameters('addUserAsSQLAdminScriptName'))]"
                ]
              },
              "type": "CustomScriptExtension",
              "typeHandlerVersion": "1.9"
            }
        }
    The extension works fine for any user except "NT AUTHORITY\SYSTEM".

    I has try a lot of various of this script. For example:

    param(
    	$AdminUsername
    )
    
    $user = "$env:USERDOMAIN\$AdminUsername"
    $ntsystem = "NT AUTHORITY\SYSTEM"
    
    net stop MSSQLSERVER
    net start MSSQLSERVER /m
    SQLCMD -E -Q "sp_addsrvrolemember '$ntsystem', 'sysadmin';"
    net stop MSSQLSERVER
    net start MSSQLSERVER
    
    net stop MSSQLSERVER
    net start MSSQLSERVER /m
    SQLCMD -E -Q "sp_addsrvrolemember '$user', 'sysadmin';"
    net stop MSSQLSERVER
    net start MSSQLSERVER

    or:

    param(
    	$AdminUsername
    )
    
    $user = "$env:USERDOMAIN\$AdminUsername"
    $ntsystem = "NT AUTHORITY\SYSTEM"
    
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    ((New-Object ('Microsoft.SqlServer.Management.Smo.Server') "$env:COMPUTERNAME").Roles | where {$_.Name -eq 'sysadmin'}).AddMember($user)
    
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    ((New-Object ('Microsoft.SqlServer.Management.Smo.Server') "$env:COMPUTERNAME").Roles | where {$_.Name -eq 'sysadmin'}).AddMember($ntsystem)

    the $user added to sysadmin role successfully. But "NT AUTHORITY\SYSTEM" does not added to the role.
    What I'm doing wrong? Is it a bug?



    • Editat de compasses miercuri, 4 iulie 2018 17:39
    • Mutat de VeeraGiri Babu miercuri, 11 iulie 2018 06:50 Better suited here than Automation
    miercuri, 4 iulie 2018 17:26

Toate mesajele

  • suggest you to refer this similar discussion over the stack over flow 

    vineri, 6 iulie 2018 10:47
  • Checking in to see if the above answer helped. Let me know if there are still any additional issues I can help with.

    marți, 10 iulie 2018 09:44
  • Hi!
    The link does not helped me.
    I have tried the idea from the discussion:

    param(
        $AdminUsername,
        $Password
    )
    
    $user = "$env:USERDOMAIN\$AdminUsername"
    $admPassword = convertto-securestring $Password -asplaintext -force
    
    net stop MSSQLSERVER
    net start MSSQLSERVER /m
    SQLCMD -E -Q "sp_addsrvrolemember '$user', 'sysadmin';"
    net stop MSSQLSERVER
    net start MSSQLSERVER
    
    $credential = New-Object System.Management.Automation.PSCredential($user, $admPassword)
    Enable-PSRemoting -Force
    Invoke-Command -ScriptBlock {SQLCMD -E -Q "sp_addsrvrolemember 'NT AUTHORITY\SYSTEM', 'sysadmin'"} -Credential $credential -ComputerName $env:COMPUTERNAME
    Disable-PSRemoting -Force
    
    net stop SQLSERVERAGENT
    net start SQLSERVERAGENT

    But user 'NT AUTHORITY\SYSTEM' still not in the sysadmin role.

    And, when I run this script manually on the target VM, the 'NT AUTHORITY\SYSTEM' get sysadmin role. 
    I'm at a loss

    UPD: I've checked 12,14,16,17 MSSQLs. This trick works only for 14 SQL. ON VMs with 12,16,17 SQLs, the user "NT AUTHORITY\SYSTEM" not in the sysadmin role
    • Editat de compasses marți, 10 iulie 2018 16:46
    marți, 10 iulie 2018 16:32