SqlServerDsc SqlSetup "Access Denied" on install Domain Admin needed.

[dawsonar802]

Details of the scenario you tried and the problem that is occurring

I have a DSC config setup with multiple compilations for various roles (web server, SQL server, etc...). This is using an Azure Automation account for DSC. Everything was working great until the other day i added a new VM, attached it to the SQL DSC config, and the SQL install fails. The config is using the SqlServerDsc module and initially i was not specifying PsDscRunAsCredential so System was being used. I found a this post, which mentioned using the PsDscRunAsCredential which i tried using my domain account which is a domain admin and it worked fine, SQL installed, no errors. I did not want to use my account long term, so i created a new domain user service account, which is also a local admin on the servers. I removed the VM, re provisioned, and re-attached to the SQL Dsc config, but it still seems to fail on the install. The failure appears to be when the SQL install is trying to confirm the credential for the SQL Service account from AD. I tried various iterations of making the account an Account operator, Server Operator, Delegating read permissions to the account, etc..., but nothing seems to work. I also found this article, which mentioned a few things, but still no luck. Also this one, which basically says to make the account one with higher permissions, so my question is, what is the least privileged that I could make the account?

I also found this post recently but the RunAs credential is a local admin on the target server and the install location is C:\windows\Temp which the account should also have access to being a local admin.

The DSC SQL install fails with Access Denied when the account used to start the install is not a Domain Admin, once i make the account a Domain Admin it works fine.

Verbose logs showing the problem

(01) 2020-05-22 11:12:13 Slp: Sco: Attempting to see if user domain\sql_svc exists
(01) 2020-05-22 11:12:13 Slp: Sco.User.OpenRoot - root DirectoryEntry object already opened for this computer for this object
(01) 2020-05-22 11:12:13 Slp: Sco: Attempting to check if user account domain\sql_svc exists
(01) 2020-05-22 11:12:13 Slp: Sco: Attempting to look up AD entry for user domain\sql_svc
(01) 2020-05-22 11:12:13 Slp: Sco.User.OpenRoot - root DirectoryEntry object already opened for this computer for this object
(01) 2020-05-22 11:12:13 Slp: Sco.User.LookupADEntry - Attempting to find user account domain\sql_svc
(01) 2020-05-22 11:12:13 Slp: Sco: Attempting to check if container 'WinNT://domain' of user account exists
(01) 2020-05-22 11:12:13 SQLEngine: --InputValidator: Engine: Validation for account domain\sql_svc failed. Access is denied.

Suggested solution to the issue

The DSC configuration that is used to reproduce the issue (as detailed as possible)

                    SqlSetup InstallSql2016
                        {
                            InstanceName = $SqlinstallCommon.InstanceName
                            SourcePath = ($InstallMedia.StorageAccountBase + $SqlConfig2016.SourcePath)
                            SourceCredential = $storageCredential #used to access the storage location(install media location)
                            Features = $SqlinstallCommon.Features
                            SQLUserDBDir = $SqlConfig2016.SQLUserDBDir
                            SQLUserDBLogDir = $SqlConfig2016.SQLUserDBLogDir
                            SQLBackupDir = $SqlConfig2016.SQLBackupDir
                            InstanceDir = $SqlinstallCommon.InstanceDir
                            InstallSharedDir = $SqlinstallCommon.InstallSharedDir
                            InstallSharedWOWDir = $SqlinstallCommon.InstallSharedWOWDir
                            SecurityMode = $SqlinstallCommon.SecurityMode
                            #AgtSvcAccount = $SqlAgtSvc
                            AgtSvcAccount = $SqlSvrSvc
                            SQLSvcAccount = $SqlSvrSvc
                            #IsSvcAccount = $SqlinstallCommon.IsSvcAccount
                            SQLSysAdminAccounts = $SqlinstallCommon.SQLSysAdminAccounts
                            SAPwd = $saCred
                            PsDscRunAsCredential = $SqlInstallAct
                            DependsOn = '[File]CreateInstallSharedDir','[File]CreateInstallSharedWOWDir','[File]CreateSQLBackupDir2016','[File]CreateSQLUserDBLogDir2016','[File]CreateSQLUserDBDir2016'
            
                        }

SQL Server edition and version the target node is running

SQL Server 2016 (13.2.5102.14)

SQL Server PowerShell modules present on the target node

Name Version

---

SQLPS 1.0

The operating system the target node is running

OsName : Microsoft Windows Server 2016 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture : 64-bit
WindowsBuildLabEx : 14393.3686.amd64fre.rs1_release.200504-1524
OsLanguage : en-US
OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

Name Value

---

PSVersion 5.1.14393.3471
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.3471
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Version of the DSC module that was used

Name Version

---

SqlServerDsc 13.0.0.0

[dawsonar802]

After several hours of troubleshooting last week, i then posted her today for some assistance. In looking into the issue more today i think i may have found the cause. I stumbled on this security policy Network access: Restrict clients allowed to make remote calls to SAM.

Since this was in a testing domain, I removed the registry key on the Domain Controllers:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\RestrictRemoteSam

The very next time the DSC config was attempted, SQL installed fine with no issues. I am now looking at adding a group to this policy since we have it as a GPO that we can allow certain users to make calls to SAM.

[johlju]

I did not know about this! Thank you for posting you findings. Much appreciated. I think we should document this so labeling this so that someone in the community can work on that.