none
Key not valid for use in specified state RRS feed

  • Question

  • Hi Experts,

    Below is the code which is running fine in PS manually in my session but when I schedule the same in SQL Agent as job it’s failing with ‘key not valid for use in specified state’ error.

    # Prompting & saving credentials, delete the XML file created to reset

    # Setting credential file

    $SNOWCredentialsFile = "N:\CMDB\SNOWCredentials.xml"

     

    # Testing if file exists

    $SNOWCredentialsFileTest =  Test-Path $SNOWCredentialsFile

     

    # IF doesn't exist, prompting and saving credentials

    IF ($SNOWCredentialsFileTest -eq $False)

    {

    $SNOWCredentials = Get-Credential -Message "Enter SNOW login credentials"

    $SNOWCredentials | EXPORT-CLIXML $SNOWCredentialsFile -Force

    }

     

    # Importing credentials

    $SNOWCredentials = IMPORT-CLIXML $SNOWCredentialsFile

     

    $SNOWUsername = $SNOWCredentials.UserName

    $SNOWPassword = $SNOWCredentials.GetNetworkCredential().Password

     

    $SNOWPassword = ConvertTo-SecureString $SNOWPassword -AsplainText -Force

     

    $Cred = New-Object System.Management.Automation.PSCredential -ArgumentList ($SNOWUsername, $SNOWPassword)


     

    # Build auth header

    $base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $SNOWUsername, $SNOWPassword)))

    The script saving the credentials in xml file and from there we are retrieving in the above script. But the same is not working in job as it runs in service account

    When I googled, I got suggestion that to use KEY/SecureKey instead of AsplainText if we need to resue irrespective of user/systems. I have tried out but failed and I am not sure where I was missing and unable to fix it.

    So please help me to get the complete correct query accordingly with the following requirements

    1. Only once security team come and enter the password when it prompts for user – due to security in prod
    2. It should be encrypted and written in xml or txt file in some path
    3. And when we schedule the ps script to run via sql agent it has to pick it from there and runs without an issues

    Thanks

    Dave

     


    • Edited by SQL_Dave Thursday, October 3, 2019 9:25 AM Type
    Thursday, October 3, 2019 9:24 AM

All replies

  • Please see the following post on StackOverflow that describes the basic functionality of how to store and retrieve encrypted credentials in PowerShell:

    https://stackoverflow.com/questions/49437141/


    -- Bill Stewart [Bill_Stewart]

    Thursday, October 3, 2019 1:55 PM
    Moderator
  • I am trying the following query but its failing with error, Please let me know if I am missing something

    Read-Host "Enter Password" -AsSecureString | ConvertFrom-SecureString | Out-File "N:\CMDB\SNOWPassword.txt"

    function ConvertTo-String {
        param(
            [Security.SecureString] $secureString
            )
        try {
            $bstr = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($secureString)
            [Runtime.InteropServices.Marshal]::PtrToStringAuto($bstr)
    write-host $bstr
            }
        finally {
            if ($bstr -ne [IntPtr]::Zero) {
                [Runtime.InteropServices.Marshal]::ZeroFreeBSTR($bstr)
                }
            }
        }
    $SNOWPassword = Get-Content "N:\CMDB\SNOWPassword.txt" | ConvertTo-String

    $Cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList ($User, $SNOWPassword)

    Error:

    Cannot convert argument "s", with value: "", for "ZeroFreeBSTR" to type "System.IntPtr": "Cannot convert null to type "System.IntPtr"."
    At line:32 char:13
    +             [Runtime.InteropServices.Marshal]::ZeroFreeBSTR($bstr)
    +             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodException
        + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

    Exception calling "SecureStringToBSTR" with "1" argument(s): "Value cannot be null.
    Parameter name: s"
    At line:26 char:9
    +         $bstr = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($secureStr ...
    +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : ArgumentNullException

    New-Object : Exception calling ".ctor" with "2" argument(s): "Cannot process argument because the value of argument "password" is null. Change the value of argument "password" to a non-null value."
    At line:38 char:9
    + $Cred = New-Object -TypeName System.Management.Automation.PSCredential -Argument ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodInvocationException
        + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

      
    Wednesday, October 9, 2019 11:40 AM

  • $SNOWPassword = Get-Content "N:\CMDB\SNOWPassword.txt" | ConvertTo-String

    This code is not correct. Get-Content reads as String already; you don't need to pipe to ConvertTo-String (it's already String!).


    -- Bill Stewart [Bill_Stewart]

    Wednesday, October 9, 2019 1:38 PM
    Moderator
  • Thanks Bill

    But I do not want to provide the password every time. It has to pick from the password file and schedule as a sql job. It's not working

    Wednesday, October 9, 2019 3:23 PM
  • Of course it isn't working (?). The code is wrong, as I pointed out.

    You need to have an understanding of the components and how they work together before you will be able to script a solution.

    Are you sure you even need to convert the password to plain-text, if you are constructing a PSCredential object?

    If my follow-up questions don't make sense, you will need to study and understand the pieces first.

    Alternatively, you may need to find or pay someone with PowerShell scripting experience to build something for you (but that's beyond the scope of this forum).


    -- Bill Stewart [Bill_Stewart]

    Wednesday, October 9, 2019 3:58 PM
    Moderator
  • Hi Bill,

    I have modified the scripts to use key and it's working fine for different users on PS ISE but only failing in SQL Agent though I have created a same user (where i have create the password file initially) as the proxy. Could you please help me to know what I am missing

    $SNOWUsername = "rdb.inventory"
    $PasswordFile = "N:\CMDB\ServiceNow\SNOWPassword.txt"
    [Byte[]] $key = (1..16)

    # Testing if file exists
    $SNOWCredentialsFileTest =  Test-Path $PasswordFile

    # IF doesn't exist, prompting and saving credentials
    IF ($SNOWCredentialsFileTest -eq $False)
    {
        Read-Host "Enter Password" -AsSecureString | ConvertFrom-SecureString -key $key | Out-File $PasswordFile
    }

    $PasswordFile = "N:\CMDB\ServiceNow\SNOWPassword.txt"
    [Byte[]] $key = (1..16)

    $Cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $SNOWUsername, (Get-Content $PasswordFile | ConvertTo-SecureString -Key $key)

    Thanks

    Dave


    • Edited by SQL_Dave Friday, October 18, 2019 12:07 PM typo
    Friday, October 18, 2019 12:07 PM
  • The problem description is very vague - you need to explain precisely what "created a same user (where i have create the password file initially) as the proxy" means. You also have not provided the exact error message (cut and paste please; no screenshots).

    -- Bill Stewart [Bill_Stewart]

    Friday, October 18, 2019 2:28 PM
    Moderator
  • [As an aside, I would also add that using $key = (1..16) is an egregiously insecure key that provides no security whatsoever.]

    -- Bill Stewart [Bill_Stewart]

    Friday, October 18, 2019 2:47 PM
    Moderator
  • Consider i logged on with user x on a server and run the above script manually in PS ISE and it creates a password file. Also I have created a proxy account xx using x credential in sql server.

    Now I login the server with user y and and try running the same script in PS ISE it works fine. But only when I try run it from SQL as schedule job it's not working

    Executed as user: domain\user1. ...id not stop the script:  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'Powershell -file "C:\Untitled4.ps1"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Invoke-WebRequest : The remote name could not be resolved:   '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'Powershell -file "C:\Untitled4.ps1"'. Correct the script and reschedule the job. The error information returned by PowerShell is: ''website.com'

    Monday, October 21, 2019 9:13 AM
  • Sorry, but the additional information you provide doesn't make sense in the context of your original question. I'm afraid you're going to need to get someone to assist you with a more "hands on" approach. The nature of a web forum is such that it just is not interactive enough to be able to assist with multiple separate issues.

    Good luck with your project.


    -- Bill Stewart [Bill_Stewart]

    23 hours 42 minutes ago
    Moderator