从SSIS脚本保存SSRS 2019报表为PDF的认证问题

问题详情 #

从SQL Server 2005迁移至2019环境，原SSIS包中的VB脚本任务可自动生成报表PDF并保存至指定路径。在Visual Studio 2019中重新创建包对接SSRS 2019实例时，生成的PDF均为0字节。手动访问报表URL时需输入凭据（2005环境无此要求），添加异常捕获后得到错误：

远程服务器返回错误: (401) 未授权。

核心代码 #

Main() 过程

Public Sub Main()
    Dim url, destination As String

    destination = Dts.Variables("FolderDestination").Value.ToString + "\" + Dts.Variables("SchemeMemberID").Value.ToString + "_" + Format(Now, "yyyyMMdd_HHmmss") + ".pdf"
    url = "http://my-report-server.my.org/ReportServer/Pages/ReportViewer.aspx?/ReportFolder/ReportName&rs:Command=Render&sKey0=" + Dts.Variables("Param0").Value.ToString + "&sKey1=" + Dts.Variables("Param1").Value.ToString + "&rs:Format=PDF"

    SaveFile(url, Dts.Variables("svcAccountName").Value.ToString, Dts.Variables("svcAccountPwd").Value.ToString, destination)
    Dts.TaskResult = ScriptResults.Success

End Sub

SaveFile 方法（原代码）

Protected Sub SaveFile(ByVal url As String, ByVal svcAccountName As String, ByVal svcAccountPwd As String, ByVal localpath As String)
    Dim loRequest As System.Net.HttpWebRequest
    Dim loResponse As System.Net.HttpWebResponse
    Dim loResponseStream As System.IO.Stream
    Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
    Dim laBytes(256) As Byte
    Dim liCount As Integer = 1
    Dim Cred As New System.Net.NetworkCredential(svcAccountName, svcAccountPwd)
    Dim CredCache As New System.Net.CredentialCache

    Try
        CredCache.Add(New Uri(url), "Basic", Cred)
        loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
        'loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
        loRequest.Credentials = Cred
        loRequest.Timeout = 600000
        loRequest.Method = "GET"
        loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
        loResponseStream = loResponse.GetResponseStream
        Do While liCount > 0
            liCount = loResponseStream.Read(laBytes, 0, 256)
            loFileStream.Write(laBytes, 0, liCount)
        Loop
        loFileStream.Flush()
        loFileStream.Close()
    Catch ex As Exception
    End Try
End Sub

解决方案 #

关键问题分析 #

SSRS 2019默认使用Windows身份验证（NTLM或Kerberos），而非代码中指定的Basic认证，导致凭据验证失败返回401错误，进而生成0字节文件。

修改后的代码 #

调整认证类型为NTLM，完善凭据缓存使用，并启用预认证：

Protected Sub SaveFile(ByVal url As String, ByVal svcAccountName As String, ByVal svcAccountPwd As String, ByVal localpath As String)
    Dim loRequest As System.Net.HttpWebRequest
    Dim loResponse As System.Net.HttpWebResponse
    Dim loResponseStream As System.IO.Stream
    Dim loFileStream As System.IO.FileStream = Nothing
    Dim laBytes(256) As Byte
    Dim liCount As Integer = 1
    Dim domain As String = ""
    Dim username As String = svcAccountName

    ' 拆分域账号（如果是DOMAIN\Username格式）
    If svcAccountName.Contains("\") Then
        Dim parts() As String = svcAccountName.Split("\"c)
        domain = parts(0)
        username = parts(1)
    End If

    Dim Cred As New System.Net.NetworkCredential(username, svcAccountPwd, domain)
    Dim CredCache As New System.Net.CredentialCache

    Try
        ' 使用NTLM认证，匹配SSRS默认Windows身份验证
        CredCache.Add(New Uri(url), "NTLM", Cred)
        loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
        loRequest.Credentials = CredCache
        loRequest.PreAuthenticate = True ' 启用预认证，避免多次握手
        loRequest.Timeout = 600000
        loRequest.Method = "GET"
        
        loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
        loResponseStream = loResponse.GetResponseStream
        
        loFileStream = New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
        Do While liCount > 0
            liCount = loResponseStream.Read(laBytes, 0, 256)
            If liCount > 0 Then
                loFileStream.Write(laBytes, 0, liCount)
            End If
        Loop
        
        loFileStream.Flush()
    Catch ex As Exception
        ' 不要吞异常，便于排查
        Dts.Events.FireError(0, "SaveFile", ex.Message, String.Empty, 0)
        Dts.TaskResult = ScriptResults.Failure
    Finally
        ' 确保流资源释放
        If loFileStream IsNot Nothing Then loFileStream.Close()
        If loResponseStream IsNot Nothing Then loResponseStream.Close()
        If loResponse IsNot Nothing Then loResponse.Close()
    End Try
End Sub

额外检查项 #

• 确认SSRS服务器的身份验证模式：打开报表服务配置管理器，在「身份验证」选项中确认启用了Windows身份验证。
• 验证服务账号权限：确保svcAccountName对应的账号有访问目标报表的权限（在SSRS门户中给账号添加报表的查看权限）。
• 账号格式：如果是域账号，需使用DOMAIN\Username格式传入，代码中已处理拆分逻辑。

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