PowerShell插入AD的ServicePrincipalNames到SQL Server格式错误如何修复

问题原因 #

ServicePrincipalNames是Active Directory模块返回的ADPropertyValueCollection集合类型，直接插入字符串类型的SQL字段时，PowerShell默认输出对象的类型全名Microsoft.ActiveDirectory.Management.ADPropertyValueCollection，不会自动展开集合内的元素值。

修复方案 #

仅需要在拼接SQL参数时，将SPN集合的所有元素用|拼接为单个字符串即可，同时为了避免字段值含单引号导致SQL语法错误，新增了单引号转义逻辑，修改后的完整代码如下：

$sqlServer='SomeServer'
$catalog = 'SomeDatabase'


$insert = @"
Insert into dbo.ADServers([Name],[OperatingSystem],[OperatingSystemVersion],[ipv4Address],[Created],[Deleted],[whenChanged],[Modified],[Description],[ServicePrincipalNames],[DisplayName],[Location],[DistinguishedName],[DNSHostName])
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}', '{13}')
"@

$start = (Get-Date).ToString('MM/dd/yyyy hh:mm:ss tt')

$connectionString = "Data Source=$sqlServer;Initial Catalog=$catalog;Integrated Security=SSPI"
# 连接对象初始化
$conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)
# 打开连接
$conn.Open()
# 准备SQL命令
$cmd = $conn.CreateCommand()
# WMI输出转换到SQL表

Get-ADComputer -Filter {operatingSystem -Like 'Windows*server*2019*'} -Property * |`
        Select Name,OperatingSystem,OperatingSystemVersion,ipv4Address,Created,Deleted,whenChanged,Modified,Description,ServicePrincipalNames,DisplayName,Location,DistinguishedName,DNSHostName |`
           forEach-object{
                   $cmd.CommandText = $insert -f `
                        $($_.Name -replace "'", "''"), `
                        $($_.OperatingSystem -replace "'", "''"), `
                        $($_.OperatingSystemVersion -replace "'", "''"), `
                        $($_.ipv4Address -replace "'", "''"), `
                        $($_.Created -replace "'", "''"), `
                        $($_.Deleted -replace "'", "''"), `
                        $($_.whenChanged -replace "'", "''"), `
                        $($_.Modified -replace "'", "''"), `
                        $($_.Description -replace "'", "''"), `
                        # 核心修改：将SPN集合用|拼接为单个字符串
                        $($_.ServicePrincipalNames -join '|' -replace "'", "''"), `
                        $($_.DisplayName -replace "'", "''"), `
                        $($_.Location -replace "'", "''"), `
                        $($_.DistinguishedName -replace "'", "''"), `
                        $($_.DNSHostName -replace "'", "''")
                   $cmd.ExecuteNonQuery()
           }

$end = (Get-Date).ToString('MM/dd/yyyy hh:mm:ss tt')

Write-Host $start
Write-Host $end

额外优化建议 #

• 如需过滤空的SPN值，可将SPN的拼接逻辑调整为：$($_.ServicePrincipalNames | Where-Object { $_ } -join '|' -replace "'", "''")
• 字符串拼接SQL存在SQL注入风险，生产环境建议改用SqlParameter参数化查询替换字符串格式化写法

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