Powershell Plug-in 1.0.6 - how do I pass a SecureString from vRO to Powershell host?

[Czernobog]

I need to pass a SecureString variable value from a vRO input parameter to the Powershell host.

I've imported a powershell script using "Generate action from PS script". One of the variables is a securestring used for an authentication mechanism on the remote PS host. The value is inserted by the user when he starts the workflow in vRO (type SecureString).

It looks like this:

- User puts in a secure string in the presentation

- the workflow is executed and the value of the variable is used during the script execution on the PS host

- there it has to be used as System.Data.SqlClient.SqlCredential

Edit:

some more clarification. Part of the script hat to check if the SQL User credentials provided by the user can be used to connect to a MS SQL database:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server = $strServer; Database = $dbname;"

$SqlCredential = New-Object System.Data.SqlClient.SqlCredential($loginAccount,$securePassword)

$SqlConnection.Credential = $SqlCredential

$SqlConnection.Open()

$securePassword must be passed through from vRO.

How could I achieve this? I unserstand the SecureString from vCO has to be decrypted - which key could I use? How do I convert the vRO SecureString input so that the PS host recognizes it as such?

Thanks!

[iiliev]

Hmm, I don't think the SecureString needs decryption. It is secure in a way that its value is not shown in the UI (eg. in presentation it is shown as * characters) but in the scripting code you should be able to access its value as a normal string.

[Dylan09]

Ilian Iliev is correct.  Secure strings in Orchestrator are not like secure strings in .NET.  Orchestrator will securely store (e.g. When used as an attribute) and mask secure strings, but they can be accessed and set like conventional strings.

[SpasKaloferov]

HI ,

you can just pass the secureString to powreshell without decryption. Check the example under "Use ConvertTo-SecureString in the PowerShell code"

http://kaloferov.com/blog/using-credssp-with-the-vco-powershell-plugin/#Use

Best Regards,

[Czernobog]

Thank you all for answering.

In my OP I probably got SecureString and EnryptedString confused, fact is, the vRO SecureString can be read as a plain string in PS.

The CredSSP method will not work in my case, since the vRO appliance is not connected in any way to my AD.

Event though I pass the Password parameter to the PS Script, I still get an error the I try to open the SQL connection.

This happens when I use SQLCredentials:

psScript +='$instance = "' + SQLInstanceName + '" # REPLACED WITH VRO INPUT\n';
psScript +='$server = "' + SQLServerName + '" # REPLACED WITH VRO INPUT\n';
psScript +='$dbname = "' + SQLDBName + '" # REPLACED WITH VRO INPUT\n';
psScript +='$strServer = "$server\\$instance" # REPLACED WITH VRO INPUT\n';
psScript +='$loginAccount = "' + SQLUserName + '" # REPLACED WITH VRO INPUT\n';
psScript +='$loginPassword = "' + SQLUserPassword + '" # REPLACED WITH VRO INPUT\n';
psScript +='$securePassword = $loginPassword | ConvertTo-SecureString -AsPlainText -Force\n';
psScript +='$securePassword.MakeReadOnly();\n';
psScript +='$primaryFGfileSize = "' + DBFGSize + '" # REPLACED WITH VRO INPUT\n';
psScript +='\n';
psScript +='$SqlConnection = New-Object System.Data.SqlClient.SqlConnection\n';
psScript +='$SqlConnection.ConnectionString = "Server =$strServer; Database = $dbname;"# Integrated Security = False; Uid = $loginAccount; Pwd = $loginPassword"\n';
psScript +='\n';
psScript +='$SqlCredential = New-Object System.Data.SqlClient.SqlCredential($loginAccount,$securePassword)\n';
psScript +='$SqlConnection.Credential = $SqlCredential\n';
psScript +='\n';
psScript +='$SqlConnection.Open()\n'

and also when a plain text password is passed to the SQLConnection connection string like this:

psScript +='$instance = "' + SQLInstanceName + '" # REPLACED WITH VRO INPUT\n';
psScript +='$server = "' + SQLServerName + '" # REPLACED WITH VRO INPUT\n';
psScript +='$dbname = "' + SQLDBName + '" # REPLACED WITH VRO INPUT\n';
psScript +='$strServer = "$server\\$instance" # REPLACED WITH VRO INPUT\n';
psScript +='$loginAccount = "' + SQLUserName + '" # REPLACED WITH VRO INPUT\n';
psScript +='$loginPassword = "' + SQLUserPassword + '" # REPLACED WITH VRO INPUT\n';
psScript +='$primaryFGfileSize = "' + DBFGSize + '" # REPLACED WITH VRO INPUT\n';
psScript +='\n';
psScript +='$SqlConnection = New-Object System.Data.SqlClient.SqlConnection\n';
psScript +='$SqlConnection.ConnectionString = "Server =$strServer; Database = $dbname; Integrated Security = False; Uid = $loginAccount; Pwd = $loginPassword"\n';
psScript +='\n';
psScript +='$SqlConnection.Open()\n'

In both cases I now get an error:

PowerShellInvocationError: Errors found while executing script

System.Management.Automation.MethodInvocationException: Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)" ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

When launched directly on the PS host, the script runs fine. I think this is an issue not with the script or with the passing of parameters from vRO itself, but with WinRM - I guess it is not possible to use SQLConnection with Integrated Security = false and SQL User credentials during a remote PS session?

I will now try to launch the script on the PS host itself and pass the arguments using the workflow "Invoke an external Script".

[SpasKaloferov · Accepted solution]

HI,

well if the account with which you have added the powerShell host in vRO is different then the account credentials which you use for the SQL connection , then you need credSSP.  Is it ?

Best Regards,

Spas Kaloferov

[Czernobog]

I guess you're right, configuring credSSP is unavoidable in this case. Thanks for posting the guide.

In the meantime I will use the JDBC URL Generator which works just as well for validating a database connection.