如何不使用NTLM连接采用gMSA账户的SQL Server实例？

针对你使用gMSA账户运行的SQL Server实例，除了默认的集成安全（可能自动使用NTLM），还有以下几种可行的连接方式：

1. 强制使用Kerberos身份验证 #

gMSA作为域托管服务账户，SQL Server实例通常会自动注册对应的服务主体名称（SPN）。你可以在连接字符串中明确指定强制使用Kerberos，避免回退到NTLM：

修改连接字符串，添加Authentication=ActiveDirectoryIntegrated（优先Kerberos），或手动指定SPN（自动注册有问题时用）：

$instance = "SERVER.DOMAIN.LOCAL\INSTANCE"
$database = "DATABASE"
# 方式1：强制AD集成身份验证
$connectionString = "Server = $instance; Database = $database; Integrated Security = True; Authentication=ActiveDirectoryIntegrated;"
# 方式2：手动指定SPN
# $connectionString = "Server = $instance; Database = $database; Integrated Security = True; ServerSPN=MSSQLSvc/SERVER.DOMAIN.LOCAL:INSTANCE;"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

对应修改你的Execute-SQL函数：

function Execute-SQL($query, [switch]$read, [switch]$write){
    $instance                    = "SERVER.DOMAIN.LOCAL\INSTANCE"
    $database                    = "DATABASE"
    # 使用Kerberos强制连接字符串
    $connectionString            = "Server = $instance; Database = $database; Integrated Security = True; Authentication=ActiveDirectoryIntegrated;"
    $connection                  = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()
    $cmd                         = New-Object System.Data.SqlClient.SqlCommand
    $cmd.Connection              = $connection
    $cmd.CommandText             = $query
    $cmd.CommandTimeout          = 0

    if($write){
        try{
            $cmd.ExecuteNonQuery() | Out-Null
        }catch{
            Write-Host $Error[0] -ForegroundColor DarkRed
            Write-Host $cmd.CommandText -ForegroundColor DarkGray
        }
    }elseif($read){
        try{
            $output                = ""
            $adapter               = New-Object System.Data.SqlClient.SqlDataAdapter
            $adapter.SelectCommand = $cmd
            $DataSet               = New-Object System.Data.DataSet
            $adapter.Fill($DataSet) | Out-Null
            $output                = $DataSet.Tables[0] | ?{$_}
            return $output
        }catch{
            Write-Host $Error[0] -ForegroundColor DarkRed
            Write-Host $query -ForegroundColor DarkCyan
        }
    }else{
        Write-Host "READ/WRITE NOT SPECIFIED" -ForegroundColor DarkCyan
    }
    $connection.Close()
}

2. 使用Azure AD身份验证（混合云/云环境适用） #

如果你的SQL Server实例配置了Azure AD集成，可使用Azure AD身份验证方式，比如：

Azure AD用户密码登录 #

$connectionString = "Server = $instance; Database = $database; Authentication=ActiveDirectoryPassword; User ID=user@domain.com; Password=yourpassword;"

Azure AD服务主体登录 #

$clientId = "your-service-principal-client-id"
$clientSecret = "your-service-principal-secret"
$tenantId = "your-tenant-id"
$connectionString = "Server = $instance; Database = $database; Authentication=ActiveDirectoryServicePrincipal; User ID=$clientId; Password=$clientSecret;"

注意：推荐使用Microsoft.Data.SqlClient驱动（替代旧的System.Data.SqlClient）以获得完整的Azure AD支持。

3. 使用SQL Server模拟（需SQL端配置） #

如果SQL Server实例允许，可通过EXECUTE AS语句切换到有权限的账户，前提是已在SQL端配置对应权限：

EXECUTE AS LOGIN = 'DOMAIN\AuthorizedUser'
-- 执行你的查询逻辑
REVERT

可将这段逻辑嵌入到PowerShell的查询语句中，实现权限切换后的连接操作。

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