Search code examples
ssisfiddler

Error when running with Fiddler4 and decrypting HTTPS alongside SSIS

I am currently using SSIS to connect to and transfer data from a Microsoft Project Online (Cloud-based) database to an on-prem SQL Server db. The issue is that the SSIS package executes as expected when not running Fiddler4.

While troubleshooting with MS techs, it has been requested we capture a Fiddler trace and decrypt HTTPS traffic. We were able to do this in months past, but as of late, it will cause the SSIS package to fail with the following error:

"Cannot acquire a managed connection from the run-time connection manager."

I have followed the instructions to remove certificates a few times, as well as a few other net suggestions, but still having the issue. We are at a point where MS cannot troubleshoot further unless I can capture a trace.

Full error:

Information: 0x4004300A at TimeSet, SSIS.Pipeline: Validation phase is beginning. Error: 0xC020801F at TimeSet, OData Source [2]: Cannot acquire a managed connection from the run-time connection manager. Error: 0xC0047017 at TimeSet, SSIS.Pipeline: OData Source failed validation and returned error code 0xC020801F. Error: 0xC004700C at TimeSet, SSIS.Pipeline: One or more component failed validation. Error: 0xC0024107 at TimeSet: There were errors during task validation.

Thanks for any and all help!

Edit: Went through my CA store and saw that the FiddlerRoot was not part of the Trusted CA list. Attempted to explicitly import the CA certificate in my security store, but the cert is still not showing in the trusted list. The Cert is named (for some reason) DO_NOT_TRUST_FiddlerRoot. Could this be part of what I have been seeing if the CA is marked to not be trusted?

Solution

  • Stepping back a bit: The Fiddler root certificate is always called DO_NOT_TRUST_FiddlerRoot and its name has no impact on anything (other than what is shown in the UI).

    Does Fiddler properly collect HTTPS traffic from your web browsers?

    If so, there are several possible explanations for what's going on here, but the most likely is that the Fiddler root certificate isn't trusted by the process that is using the connection; by default, for instance, Fiddler only tries to trust the root certificate in the per-user certificate store, but sometimes things run in a different user account (e.g. a service account) and thus the root must be placed in the per-machine certificate store.

    You can try the following:

    1. In Fiddler’s Tools > Fiddler Options > HTTPS tab, click Export Root Certificate to Desktop.
    2. Launch mmc.exe.
    3. Click File > Add/Remove Snap-In.
    4. Select the Certificates snap-in and press Add.
    5. When prompted This snap-in will always manage certificates for: choose Computer Account
    6. Click Local Computer, then Finish, then OK.
    7. Open the Certificates (Local Computer) node.
    8. Right-click the Trusted Root Certificate Authorities folder and choose All Tasks > Import.
    9. Choose the file you exported in step #1 and import it.