如何通过PowerShell从SSRS 2017已发布报表生成Excel文件？

问题背景 #

我需要运行PowerShell脚本自动从SSRS 2017已发布报表生成Excel文件，当前使用的脚本如下：

$reportServerURI = "http://NOCSQL05:80/ReportServer/"
$reportPath = "/Folder/Project"
$format = "EXCELOPENXML"
$parameters = @()

# Update these variables with the correct credentials
$username = "ValidUsr"
$password = "ValidPwd"

$RS = New-WebServiceProxy -Class 'ReportingService2010' -NameSpace 'Microsoft.SqlServer.ReportingServices2017' -Uri $reportServerURI

if($RS -ne $null) {
    $RS.Credentials = New-Object System.Net.NetworkCredential($username, $password)
    $Report = $RS.LoadReport($reportPath, $null)
    if($Report -ne $null) {
        $RS.SetExecutionParameters($parameters, "nl-nl") > $null
        $RenderOutput = $RS.Render($format, $null, [ref] $null, [ref] $null, [ref] $null, [ref] $null, [ref] $null)
        if($RenderOutput -ne $null) {
            $Stream = New-Object System.IO.FileStream("C:\Users\TM0658\Documents\report.xlsx", [System.IO.FileMode]::Create)
            $Stream.Write($RenderOutput, 0, $RenderOutput.Length)
            $Stream.Close()
        }
    }
}

执行脚本时出现401未授权错误，详情如下：

New-WebServiceProxy : The request failed with HTTP status 401: Unauthorized.
At C:\Users\TM0658\Documents\SSRS_Export.ps1:11 char:7
+ $RS = New-WebServiceProxy -Class 'ReportingService2010' -NameSpace 'M ...
+       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (http://nocsql05/ReportServer/:Uri) [New-WebServiceProxy], WebException
    + FullyQualifiedErrorId : WebException,Microsoft.PowerShell.Commands.NewWebServiceProxy

已确认服务器URL有效、用户权限足够，但问题仍存在，希望解决该问题，也接受PowerShell之外的替代方案。

PowerShell脚本修复方案 #

1. 提前传入凭据（核心修复） #

原脚本先创建代理再设置凭据，但New-WebServiceProxy创建时就会发起请求，导致凭据未及时生效。修改为创建代理时直接传入凭据：

$reportServerURI = "http://NOCSQL05:80/ReportServer/"
$reportPath = "/Folder/Project/ReportName" # 补充具体报表名称
$format = "EXCELOPENXML"
$parameters = @()

$username = "ValidUsr"
$password = "ValidPwd"
$domain = "YourDomain" # 域账号需添加域名，本地账号可留空

# 创建凭据对象
$credential = New-Object System.Net.NetworkCredential($username, $password, $domain)

# 创建代理时直接传入凭据
$RS = New-WebServiceProxy -Class 'ReportingService2010' -Namespace 'Microsoft.SqlServer.ReportingServices2017' -Uri $reportServerURI -Credential $credential

if($RS -ne $null) {
    $Report = $RS.LoadReport($reportPath, $null)
    if($Report -ne $null) {
        $RS.SetExecutionParameters($parameters, "nl-nl") > $null
        $RenderOutput = $RS.Render($format, $null, [ref] $null, [ref] $null, [ref] $null, [ref] $null, [ref] $null)
        if($RenderOutput -ne $null) {
            $Stream = New-Object System.IO.FileStream("C:\Users\TM0658\Documents\report.xlsx", [System.IO.FileMode]::Create)
            $Stream.Write($RenderOutput, 0, $RenderOutput.Length)
            $Stream.Close()
        }
    }
}

2. 适配NTLM认证（若SSRS使用该方式） #

如果SSRS启用NTLM认证，需强制代理使用NTLM避免Kerberos双跳问题：

# 在创建代理前添加以下配置
$proxyParams = @{
    Class = 'ReportingService2010'
    Namespace = 'Microsoft.SqlServer.ReportingServices2017'
    Uri = $reportServerURI
    UseDefaultCredential = $false
}
$RS = New-WebServiceProxy @proxyParams
$RS.PreAuthenticate = $true
$RS.Credentials = $credential

3. 验证报表路径 #

确保$reportPath是SSRS中的完整路径，需包含具体报表名称，例如/Folder/Project/SalesReport，而非仅文件夹路径。

替代方案 #

1. 使用SSRS REST API（SSRS 2017+推荐） #

SSRS 2017及以上版本支持REST API，比旧Web Service更稳定，示例代码：

$reportServerURI = "http://NOCSQL05:80/ReportServer"
$reportPath = "/Folder/Project/ReportName"
$outputPath = "C:\Users\TM0658\Documents\report.xlsx"
$username = "ValidUsr"
$password = "ValidPwd"
$domain = "YourDomain"

$credential = New-Object System.Management.Automation.PSCredential("$domain\$username", (ConvertTo-SecureString $password -AsPlainText -Force))

# 构建REST API导出请求URL
$apiUrl = "$reportServerURI/api/v2.0/reports('$reportPath')/Export?format=EXCELOPENXML"

# 发起请求并保存Excel文件
Invoke-RestMethod -Uri $apiUrl -Credential $credential -OutFile $outputPath

2. 使用rs.exe命令行工具 #

创建VB脚本文件ExportReport.vbs：

Public Sub Main()
    Dim rs As New ReportingService2010
    rs.Credentials = New System.Net.NetworkCredential("ValidUsr", "ValidPwd", "YourDomain")
    rs.Url = "http://NOCSQL05:80/ReportServer/ReportService2010.asmx"

    Dim reportPath As String = "/Folder/Project/ReportName"
    Dim format As String = "EXCELOPENXML"
    Dim parameters As ParameterValue() = Nothing

    Dim warnings As Warning() = Nothing
    Dim streamIDs As String() = Nothing
    Dim mimeType As String = ""
    Dim encoding As String = ""
    Dim fileNameExtension As String = ""
    Dim bytes As Byte() = rs.Render(format, Nothing, parameters, Nothing, Nothing, Nothing, warnings, streamIDs)

    Dim fs As New System.IO.FileStream("C:\Users\TM0658\Documents\report.xlsx", System.IO.FileMode.Create)
    fs.Write(bytes, 0, bytes.Length)
    fs.Close()
End Sub

通过rs.exe执行脚本：

rs.exe -i ExportReport.vbs -s http://NOCSQL05:80/ReportServer/

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