Using DLM Automation Powershell cmdlets in CircleCI pipeline

0x1d

Hi, 

reposting my question in this topic bc in the meantime I got familiar w the forum structure.

I'm trying to set up a CircleCI pipeline to deploy an MSSQL database to AWS and I get a System.Security.Cryptography.CryptographicException: Access is denied. error when invoking any of the DLM Automation Powershell cmdlets.
Do you know what can it be? Is it a missing certificate issue or rather an admin privilege issue?
I do not get the error if I run the cmdlet on my office PC, but there I got the full SQL Toolbelt installed.

This is the error:

Register-DlmSerialNumber, DLM Automation 2.1.3.2175, Copyright c Red Gate Software Ltd 2014-2018

Use of this functionality requires a SQL Toolbelt license.
If you are unsure of your current user license entitlement please contact licensing@red-gate.com

Activating serial number XXX-XXX-XXXXXX-XXXX
WARNING: System.Security.Cryptography.CryptographicException: Access is denied.

   at System.Security.Cryptography.ProtectedData.Protect(Byte[] userData, Byte[] optionalEntropy, DataProtectionScope 
scope)
   at RedGate.DLMAutomation.Compare.RedgateTools.RedgateToolRunner.GetProtectedEnvironmentVariableValue()
   at RedGate.DLMAutomation.Compare.RedgateTools.RedgateToolRunner.Run(GuardedString commandLineArguments, Action`1 
onStdOut, Action`1 onStdErr, String executablePath)
   at RedGate.DLMAutomation.Compare.RedgateTools.RedgateToolRunner.RunCompare(GuardedString commandLineArguments, 
Action`1 onStdOut, Action`1 onStdErr)
   at RedGate.DLMAutomation.Compare.Compare.LicensingFacade.ActivateSerialNumber(String serialNumber)
   at RedGate.DLMAutomation.PowerShell.Commands.RegisterDlmSerialNumberCommandImpl.Process()
   at RedGate.DLMAutomation.PowerShell.DlmAutomationCommandImpl`1.ProcessRecord()
   at RedGate.PowerShell.CommandBase`2.DelegateTo(Action doImplementation)
Register-DlmSerialNumber : An unhandled error occurred: System.Security.Cryptography.CryptographicException: Access is 
denied.
   at System.Security.Cryptography.ProtectedData.Protect(Byte[] userData, Byte[] optionalEntropy, DataProtectionScope 
scope)
   at RedGate.DLMAutomation.Compare.RedgateTools.RedgateToolRunner.GetProtectedEnvironmentVariableValue()
   at RedGate.DLMAutomation.Compare.RedgateTools.RedgateToolRunner.Run(GuardedString commandLineArguments, Action`1 
onStdOut, Action`1 onStdErr, String executablePath)
   at RedGate.DLMAutomation.Compare.RedgateTools.RedgateToolRunner.RunCompare(GuardedString commandLineArguments, 
Action`1 onStdOut, Action`1 onStdErr)
   at RedGate.DLMAutomation.Compare.Compare.LicensingFacade.ActivateSerialNumber(String serialNumber)
   at RedGate.DLMAutomation.PowerShell.Commands.RegisterDlmSerialNumberCommandImpl.Process()
   at RedGate.DLMAutomation.PowerShell.DlmAutomationCommandImpl`1.ProcessRecord()
   at RedGate.PowerShell.CommandBase`2.DelegateTo(Action doImplementation)
At line:6 char:1
+ Register-DlmSerialNumber -InputObject XXX-XXX-XXXXXX-XXXX
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Register-DlmSerialNumber], CryptographicException
    + FullyQualifiedErrorId : UnhandledException,RedGate.DLMAutomation.PowerShell.Commands.RegisterDlmSerialNumberComm 
   and

The CircleCI executor is a windows-server-2019-vs2019:stable box.

The Powershell module version is http://psg-prod-eastus.azureedge.net/packages/dlmautomation.2.1.3.2175.nupkg

0x1d

I've been looking at this last weekend and I can confirm it's not a certificate thing. I was able to set up a working script with the Powershell cmdlets on a Windows EC2 instance. But when I ran the same script in the CircleCI Windows executor I get the above exceptions. If I run the script with Start-Process powershell.exe -Verb Runas -ArgumentList "-File deploy-db.ps1" I can't capture the logs

Sergio R

Hi,
You should be able to run that script without using the Register-DlmSerial number step, just remove it from your script and test again.

Kind Regards,

0x1d

thanks @Sergio R

Unfortunately, the error is thrown at most of the DLM cmdlets, not just the registration step.

It is thrown at Invoke-DlmDatabaseSchemaValidation and also the most important one: Sync-DlmDatabaseSchema.

The only one without error is the New-DlmDatabaseConnection.

Sergio R

I have just tested DLM Automation 2.1.3 on a Windows 2019 Azure DevOps Microsoft Hosted agent and it worked correctly, I think that might be caused by something specific to the CircleCI environment