使用SQLSetup DSC创建SQL Server实例后无法登录问题咨询

• SQLSysAdminAccounts 配置精度不足
  你当前配置的SQLSysAdminAccounts = @("Administrators")仅简写了管理员组名，在域环境或UAC启用的场景下会出现识别异常：本地管理员组的完整标识为BUILTIN\Administrators，域账号/域组需要显式带上域名前缀（例如DOMAIN\你的用户名）才能被SQL正确识别，同时UAC会默认过滤管理员组的高权限，导致隶属于管理员组的账号登录时无法继承sysadmin权限。
• 缺失必要的参数声明
  你的配置中Registry资源引用了未定义的$Node.LoginMode和$SQLInstallCredential变量，既没有在配置的param块中声明这两个参数，也没有给SqlSetup资源指定安装运行凭据、SQL服务账号，安装过程中权限上下文异常会直接导致管理员组权限配置不生效。
• 没有显式配置SQL登录与权限映射
  仅依赖管理员组默认权限的方式兼容性差，需要通过SqlLogin和SqlServerRole资源显式将你需要使用的账号添加为SQL登录，并授予sysadmin角色，避免UAC或域权限识别问题。
• 缺少安装后的权限生效逻辑
  SQL安装完成后需要重启服务才能让账号权限配置生效，你当前的配置仅在修改TCP协议时重启服务，没有在SqlSetup执行后触发服务重启，也可能导致权限配置未即时生效。

Configuration InstallSQLServer
{
     param(
          [string[]]$NodeName = 'localhost',
          [Parameter(Mandatory)]
          [PSCredential]$SQLInstallCredential,
          [Parameter(Mandatory)]
          [string[]]$SQLAdminAccounts,
          [int]$LoginMode = 2 # 1=仅Windows身份验证 2=混合模式
     )

     Import-DscResource -ModuleName SqlServerDsc
     Import-DscResource -ModuleName 'PSDesiredStateConfiguration'
     
     node $NodeName
     {
          WindowsFeature 'NetFramework45'
          {
               Name   = 'NET-Framework-45-Core'
               Ensure = 'Present'
          }

          SqlSetup 'InstallDefaultInstance'
          {
               InstanceName        = 'MSSQLSERVER'
               Features            = 'SQLENGINE'
               SourcePath          = 'C:\SQL2019'
               SQLSysAdminAccounts = $SQLAdminAccounts
               SetupProcessCredential = $SQLInstallCredential
               SQLSvcAccount       = $SQLInstallCredential # 也可单独指定专属的SQL服务运行账号
               DependsOn           = '[WindowsFeature]NetFramework45'
          }

          # 显式添加SQL登录并授予sysadmin权限
          foreach ($adminAccount in $SQLAdminAccounts) {
               SqlLogin "AddLogin_$($adminAccount.Replace('\','_'))"
               {
                    Ensure = 'Present'
                    Name = $adminAccount
                    LoginType = 'WindowsGroup' # 单个用户改为WindowsUser
                    ServerName = 'localhost'
                    InstanceName = 'MSSQLSERVER'
                    DependsOn = '[SqlSetup]InstallDefaultInstance'
               }

               SqlServerRole "GrantSysadmin_$($adminAccount.Replace('\','_'))"
               {
                    ServerRoleName = 'sysadmin'
                    MembersToInclude = $adminAccount
                    ServerName = 'localhost'
                    InstanceName = 'MSSQLSERVER'
                    DependsOn = "[SqlLogin]AddLogin_$($adminAccount.Replace('\','_'))"
               }
          }

          Registry REG_LoginMode{
              DependsOn           = '[SqlSetup]InstallDefaultInstance'
              Key                 = 'HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer'
              ValueName           = 'LoginMode'
              ValueType           = 'DWORD'
              ValueData           = $LoginMode
              PsDscRunAsCredential = $SQLInstallCredential
          }

          SqlServerNetwork EnableTcpIp {
              DependsOn           = '[SqlSetup]InstallDefaultInstance'
              InstanceName        = 'MSSQLSERVER'
              ProtocolName        = 'Tcp'
              IsEnabled           = $true
              TCPPort             = 1433
              RestartService      = $true
          }

     }
}

# 生成MOF文件示例，替换为实际的域名、账号、凭据
$installCred = Get-Credential -Message "输入SQL安装用的管理员凭据"
InstallSQLServer -NodeName localhost `
     -SQLInstallCredential $installCred `
     -SQLAdminAccounts @("DOMAIN\你的管理员账号", "BUILTIN\Administrators") `
     -LoginMode 2

# 应用配置
Start-DscConfiguration -Path .\InstallSQLServer -Wait -Force -Verbose

内容的提问来源于stack exchange，提问作者Kundan