3

We have a SSIS project where one of the packages is connecting to a REST API. We use the HTTP connection manager (with username/password) and a script component to open the connection manager and parse the response. Protection level for all packages are EncryptSensitiveWithUserKey. Everything works in Visual Studio, and can be deployed with Deployment Wizard to the SSIS-DB. In the SSIS-DB we can run the package, and also change connection manager password/username via environments.

But we are not able to achieve this via our normal automated deployment: Check-in to TFS and use VSTS-buildserver with Powershell scripts. When running the package from SSIS-db we get:

Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

We (believe we) know how SSIS protection levels and encryption works, and the cause is obvious: The SSIS file is encrypted with user key, and the Deployment Wizard (run by developer!) decrypts/re-encrypts with the SSIS-catalog key. But the build server does not have the user key, hence the decryption-step is invalid. However, we would expect that this should not be an issue, since the password is replaced by the SSIS-environment, but is gives the above error.

We have tried all protection levels:

  • DontSaveSensitive: Package can't run in either VS/SSISDB.
  • EncryptSensitiveWithPassword: Passwords are unsupported in the PowerShell $folder.DeployProject command. Same method as here.
Martin Thøgersen
  • 1,538
  • 18
  • 33
  • If you exclude the package from the project, and leave it as `EncryptSensitiveWithUserKey`, does it deploy properly to the SSISDB? – billinkc Jun 25 '18 at 12:37
  • @billinkc Yes this package with http connection is newly added to a working project. The remaining project/packages still deploys and executes fine. – Martin Thøgersen Jun 25 '18 at 18:44
  • Do not find the PowerShell object model can deploy with EncryptSensitiveWithPassword encrypted. – starian chen-MSFT Jun 26 '18 at 04:41
  • I am fine to use `EncryptSensitiveWithPassword`, but which deployment script supports password parameter? (To be called in VSTS.) – Martin Thøgersen Jun 26 '18 at 06:52
  • I take all steps to only use `DoNotSaveSensitive`. You mention _DontSaveSensitive: Package can run in either VS/SSISDB_ - whats the problem there? – Nick.Mc Jun 26 '18 at 13:13
  • @Nick.McDermaid. 1) Create new SSIS project in VS2017. 2) Set `DontSaveSensitive` on proj+package. 3) Add `HTTP` connection manager, type username/password, test connection ok. 4) Add Data Flow Task with Script component (Source type), map the connection manager as `myConnection`, add dummy output column, and edit the C# script as follows: – Martin Thøgersen Jun 26 '18 at 13:56
  • (cont.) In the `CreateNewOutputRows()` method add: `IDTSConnectionManager100 connMgr; HttpClientConnection100 hcc; connMgr = this.Connections.myConnection; hcc = (HttpClientConnection100)connMgr.AcquireConnection(null); Byte[] buffer = hcc.DownloadData();` – Martin Thøgersen Jun 26 '18 at 13:58
  • (cont.) Package fails with `403 - Forbidden`. If you change the ProtectionLevel back to `EncryptSensitiveWithUserKey`, the package succeeds. – Martin Thøgersen Jun 26 '18 at 14:02
  • I don't have a public HTTP REST API with username/password for demo, maybe someone can be helpful here. – Martin Thøgersen Jun 26 '18 at 14:07
  • I haven't used HTTP connection manager but If I understand correctly, credentials are saved in the HTTP connection manager and are then "protected" (lost) by various protection levels (either userkey or dontsavesensitive). The usual solution is to parameterise the connection at the project level, then at runtime, set that parameter from a configuration. In other words, define the HTTP connection string in the environment and apply that at runtime. – Nick.Mc Jun 28 '18 at 02:40
  • In other words, your deployment tool contains (an protects) the sensitive HTTP connection string. It deploys that connection sting to an SSIS environment at deployment time. When the SSIS package runs, it is configured to use that environment, which is applied to an SSIS project parameter, which in turn overwrites your connection string at runtime. All through the process, the sensitive string is protected. – Nick.Mc Jun 28 '18 at 02:42
  • Step 2 to Step 5 in this link explains what I'm going on about... https://www.sqlchick.com/entries/2015/1/4/parameterizing-connections-and-values-at-runtime-using-ssis-environment-variables – Nick.Mc Jun 28 '18 at 02:44
  • @Nick.McDermaid I think was you explain is basically what we do. However, the credentials are not only "lost", the package is also blocked(encrypted) for adding new credendtials via SSIS environments. – Martin Thøgersen Aug 13 '18 at 11:43
  • The API use basic authentication, which must be provided through the authentication header. The HTTP Connection Manager takes care of this, when filling the username/password fields, but then the problems arise as explained above. – Martin Thøgersen Aug 13 '18 at 12:02
  • I have tried to authenticate via URL instead of header http://username:password@server.domain, but the API does not support this, and it should also be avoided according to https://serverfault.com/questions/371907/can-you-pass-user-pass-for-http-basic-authentication-in-url-parameters – Martin Thøgersen Aug 13 '18 at 12:04
  • There are a lot of factors here. It's tricky to troubleshoot, particularly since you seem to have covered all the obvious bits already. I'm not sure what you mean by _blocked(encrypted) for adding new credentials via SSIS environments_. Do you get an error message? If you are just using a script component anyway to call the web service, one option is to sidestep the whole connection manager thing and just write some C# to pick up details from somewhere and decrypt / use them. That's a workaround. If you are using basic authentication then I don't think login/pwd is encrypted anyway. – Nick.Mc Aug 13 '18 at 12:26
  • 'blocked': I mean exactly what's explain in the main question; When I use the user/password field in the HTTP connection manager, encryption is added to the package. It doesn't matter if we try to run it with new SSIS env. variables - because the package is still over over encrypted (and decrypted with missing userkey), so we get the error "Failed to decrypt protected XML node" etc. – Martin Thøgersen Aug 13 '18 at 12:37
  • True, basic auth is not encrypted with HTTPS afaik, but acceptable here because this is only on LAN. (That's not my call, sadly.) Still, the passwords should be stored safely when at rest. – Martin Thøgersen Aug 13 '18 at 12:40
  • Regarding workaround writing everything in C# (no HTTP connection manager): Would highly appreciate a small code example/blog to get started :-) – Martin Thøgersen Aug 13 '18 at 12:42

2 Answers2

1

With EncryptSensitiveWithUserKey mode, you can try to setup build/release agent on your machine and change service account to your account, then deploy through this agent.

starian chen-MSFT
  • 33,174
  • 2
  • 29
  • 53
  • 1
    Thanks, but this goes against the idea of having a cd/ci pipeline. TFS and VSTS is a requirement for the solution. We use it for test & release management to three environments. – Martin Thøgersen Jun 26 '18 at 05:13
  • With this requirement, you can't do it with EncryptSensitiveWithUserKey mode. You need to call script or tool (I don't find) to do deployment with EncryptSensitiveWithPassword model. – starian chen-MSFT Jun 26 '18 at 05:40
1

I am encountering the same problem now with Azure DevOps and the SSIS DevOps tasks targeting SQL Server 2016.

I suspect that using the Microsoft.SQLServer.Management.IntegrationServices assembly behaves differently to the ISDeploymentWizard executable.

I have found that this issue occurs for sensitive package parameters only and not project parameters so one solution is to replace your sensitive package parameters with project parameters.

The issue would occur when running the package with the sensitive package parameter from the catalog but in some cases the package would run without issue when executed as a child package.

I also found that some packages would report successful package execution but looking at the event messages the Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 would be present.

An alternative solution is to execute the ISDeploymentWizard from the command line. This does require that the target catalog folder already exists as the wizard will not create it. Therefore a step is needed before this to create the catalog folder if it does not already exist.

PowerShell script below should work for SQL Server 2016 as is:

### Variables
$targetServer = "localhost"
$targetCatalogFolder = "IsDeploymentWizard"
$sourceFolder = "C:\Users\mhept\source\repos\SsisDeploy\AzureDevOpsSensitiveInChildPackage"

### Ensure Target Catalog Folder Exists
Add-Type -AssemblyName "Microsoft.SQLServer.Management.IntegrationServices, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL"

$ssisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

# Create a connection to the server
$sqlConnectionString = "Data Source=" + $targetServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ssisNamespace".IntegrationServices" $sqlConnection

# Get the Integration Services catalog
$catalog = $integrationServices.Catalogs["SSISDB"]
$catalogFolder = $catalog.Folders[$targetCatalogFolder]

if($null -eq $catalogFolder){
    # Create the target folder
    Write-Host "Creating Catalog Folder $targetCatalogFolder"
    $catalogFolder = New-Object $ssisNamespace".CatalogFolder" ($catalog, $targetCatalogFolder, "")
    $catalogFolder.Create()
}

$targetCatalogPath = "/SSISDB/$targetCatalogFolder"

$ispacs = Get-ChildItem -Path $sourceFolder -Filter "*.ispac" -Recurse
$isDeploymentWizard = Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\130\SSIS\Setup\DeploymentWizardPath" -Name "(default)"

foreach($ispac in $ispacs) {
    $projectName = $ispac.BaseName
    $sourcePath = $ispac.FullName

    Write-Host "Deploying $projectName ..."
    Start-Process -Wait -FilePath $isDeploymentWizard -ArgumentList "/Silent", "/SourceType:File", "/ModelType:Project", "/SourcePath:$sourcePath", "/DestinationServer:$targetServer", "/DestinationPath:$targetCatalogPath/$projectName"
    Write-Host "Successfully deployed $projectName"
}
mheptinstall
  • 2,109
  • 3
  • 24
  • 44