1

I am trying to migrate a very old SQL Server environment (2005!) to 2019. In our old environment we have an SSIS package that includes a VB script task to generate PDF copies of a number of reports and save them to a specified location. When I try and re-create this package using Visual Studio 2019 with the Integration Services add-in, and run this against our SSRS 2019 instance, all the PDFs get generated with 0 bytes. I have a suspicion that this is related to authentication in reporting services 2019 - whenever I run the reports manually, I am prompted for credentials, which doesn't happen on my 2005 environment.

The Main() procedure that does the work is:

    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

This is calling the following procedure to generate and save the PDF files. The commented line is what it was doing before @billinkc posted a response - I made the suggested changes, but still get 0 byte files:

    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

After adding a line in the Catch to surface the Exception, the script reports

The remote server returned an error: (401) Unauthorized

billinkc
  • 59,250
  • 9
  • 102
  • 159
Skippy
  • 1,595
  • 1
  • 9
  • 13
  • You'll want to include the definition for `SaveFile` but the answer is going to be something about `NetworkCredential` need to be specified, the account running the package needs to be authorized, etc https://stackoverflow.com/questions/25451606/getting-web-service-to-authenticate-using-network-credentials-vb-net Don't store credentials in the script but this might be a solid nudge if you can't get a combination of the SQL Agent account or an SSIS proxy to talk to the SSRS instance. – billinkc Mar 05 '23 at 22:27
  • @billinkc - Thanks for the suggestion. I tried passing in credentials to the `SaveFile` procedure call, but this made no difference to the results. The script runs without errors, but the saved PDF files are still 0 bytes. – Skippy Mar 06 '23 at 11:20
  • I suspect you are raising an exception that is then discarded in your `Catch ex As Exception End Try` because well you have no alerting there. I would do something like `System.File.WriteAllText(localpath + "err.txt", ex);` – billinkc Mar 06 '23 at 18:53
  • Google the syntax and possibly the namespace as I'm too lazy to find my reference code. But the idea is you will dump the the location on disk as the empty pdf with any exception data. – billinkc Mar 06 '23 at 18:54
  • Hi @billinkc, thanks for the pointers. As anticipated, the error message is "The remote server returned an error: (401) Unauthorized", so setting the credentials doesn't look to have worked. Any other suggestions? – Skippy Mar 07 '23 at 10:24
  • You need to identify 1) What was the account that successfully connects to SSRS in the 2005 environment? 2) What is the account that is being presented to the SSRS instance that is generating the 401? If they're the same, then ...are they pointing to the same SSRS instance? – billinkc Mar 07 '23 at 16:29
  • @billinkc, in the 2005 environment, as per the commented line in my original (edited) post, it is using `loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials`. The problem is that SSRS 2019 no longer allows anonymous authentication, so I am trying to work out what I need to change to allow this to work in the later environment. I found this [MS article](https://learn.microsoft.com/en-us/sql/reporting-services/security/authentication-with-the-report-server?view=sql-server-ver16), but am having trouble understanding it - I need help to translate this into easy steps – Skippy Mar 07 '23 at 19:55
  • 1
    Hi @billinkc, I have an update. It turns out that using `System.Net.Credentials.DefaultCredentials` does work, just not when running from SSIS on the server - it works fine from my development machine. So, it looks to be something weird about how SSIS is making the initial connection to SSRS; it's not using the credentials in the 'Run As' settings for the job step or the service account that SQL Server and SQL Server Agent are running under. We see Audit Failure events for Logon in the event viewer Security log. No idea where to go from here! Any thoughts? – Skippy Aug 22 '23 at 16:17
  • On your development machine, are SSIS and SSRS both installed there? Something about this is triggering a hazy memory of a kerberos double hop issue. If so, then the resolution is to set the ... "trusted for delegation" bit on the SQL Agent service account in Active Directory and the then it should work. Swamped with other things and can't afford the context switch right now ;) – billinkc Aug 22 '23 at 17:53
  • Hi @billinkc, really appreciate you taking the time to respond. Development machine only has Visual Studio, so I'm running the package from within VS talking to SSRS on the server. I then copy the 'working' dtsx file to the server and invoke that from a SQL Agent job; it's this that fails. SQL Server/Agent/SSRS are all on the same machine. I will look into your "trusted for delegation" suggestion tomorrow when I'm back in the office. – Skippy Aug 24 '23 at 16:10
  • Hi @billinkc - Sorry, that didn't work :( – Skippy Aug 26 '23 at 18:58

0 Answers0