locked
pass in secure password to SQL connection RRS feed

  • Question

  •  The process runs on a remote(in DMZ SQL box) where I need to pass in a secured password to the process.

      How would the SQL connection look like, and is that the best method?

        Thanks.

    # build the file with the encrypted password
    ConvertTo-SecureString "xxxx" -AsPlainText -Force | ConvertFrom-SecureString | Out-File "C:\password_test\job_pwd.txt"
    Wednesday, February 13, 2019 3:50 PM

Answers

  •  Thanks for replies..

     So if your using a SQL credentials your only option if called from a cmd file would be to lock down
    the folder where the script runs from?

     Thanks.

    That is correct or you can store the password in a file encrypted and decrypt it before you send it to SQLServer.


    \_(ツ)_/

    • Marked as answer by hart60 Sunday, February 17, 2019 2:09 AM
    Saturday, February 16, 2019 7:12 PM

All replies

  •  

     This is what my SQL connection looks like currently that I want to use secured password.

    # Load SMO and instantiate the server object
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |Out-Null;
        [Microsoft.SqlServer.Management.Smo.Server]$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName;
        $sqlServer.ConnectionContext.LoginSecure = $false 
        $sqlServer.ConnectionContext.Login="xxxxx" 
        $sqlServer.ConnectionContext.Password="xxxxx"
     Thanks.
    Wednesday, February 13, 2019 4:51 PM
  • You cannot use a secure password if the authentication is not configured for this and the SQL login is not created as a secure login.  As you SQL admin to help you with this.

    Working through the DMZ can be an extremely difficult thing with SQLServer depending on how your admins and net techs have designed this.


    \_(ツ)_/

    Wednesday, February 13, 2019 8:21 PM

  •  The SQL authentication does both sql\ad group so that's not an issue just looking for the syntax
    technique to handle passing into connection string.

      Current connection in DMZ(To SQL Box) use userid\pw.

      THanks.
    Thursday, February 14, 2019 12:28 AM
  • If the server is in the DMZZ how can it do AD authentication?  You need to talk to your admins to understand how the DMZ is configured.  We cannot help you with that by guessing.

    \_(ツ)_/

    Thursday, February 14, 2019 12:53 AM
  •  I missed typed The authentication uses SQL :

     Current connection in DMZ(To SQL Box) use userid\pw.

    Thursday, February 14, 2019 2:42 AM
  • How were we supposed to guess at that?


    \_(ツ)_/

    Thursday, February 14, 2019 4:53 AM
  •  In my original post I was asking for a SQL connection to server\db.

    Thursday, February 14, 2019 10:45 AM
  •  Any ideas\samples

     Thanks.

    Saturday, February 16, 2019 12:24 AM
  •  

     This is what my SQL connection looks like currently that I want to use secured password.

    # Load SMO and instantiate the server object
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |Out-Null;
        [Microsoft.SqlServer.Management.Smo.Server]$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName;
        $sqlServer.ConnectionContext.LoginSecure = $false 
        $sqlServer.ConnectionContext.Login="xxxxx" 
        $sqlServer.ConnectionContext.Password="xxxxx"
     Thanks.

    Hi hart60,

    Let me guess. you want to encrypt your password and you want to use that encrypted password on your PowerShell script to connect to your SQL server (no matter where it is). You use SQL login, not the windows login.

    All I can suggest is to look into some password hashing algorithm and deploy the one you prefer. This link may be useful for your further study

    https://www.pdq.com/blog/secure-password-with-powershell-encrypting-credentials-part-1/

    https://www.pdq.com/blog/secure-password-with-powershell-encrypting-credentials-part-2/

    Here is my example of quick implementation of Base64 encoding/decoding. Remember this doesn't prevent the user to find out what the actual password is, this only disguises your plain text password to something else. Anyone who understands the script can decode it.

    function EncodeBase64($Text){    
            $ByteStr = [system.Text.Encoding]::UTF8.GetBytes($Text)
            $Base64enc = [Convert]::ToBase64String($ByteStr)
            return $Base64enc
    }
    
    function DecodeBase64($Text){        
            $Base64dec = [system.Text.Encoding]::UTF8.GetString([Convert]::FromBase64String($Text))        
            return $Base64dec
    }
    
    EncodeBase64 -Text "Mypassword"
    DecodeBase64 -Text "TXlwYXNzd29yZA=="
    hope this gets you started with your journey to password encryption.



    • Edited by Naw Saturday, February 16, 2019 1:13 AM
    Saturday, February 16, 2019 1:09 AM
  • If you just want a secure login then this is how to do it:

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Serve($ServerName)
    $srv.ConnectionContext.LoginSecure = $true # Windows Itegrates =  $true SQS login = $false
    $srv.ConnectionContext.Login = 'loginid'
    $srv.ConnectionContext.Password = 'password'  # plain text


    \_(ツ)_/



    • Edited by jrv Saturday, February 16, 2019 1:20 AM
    Saturday, February 16, 2019 1:12 AM
  • Note that there is no encrypted login with SQLServer SMO.  You can login aith Windows credebtials or with SQLServer credentials.  The login is sent encrypted but sent as plain text.  With Windows authentication no username or password is used even if specified.


    \_(ツ)_/

    Saturday, February 16, 2019 1:20 AM
  •  Thanks for replies..

     So if your using a SQL credentials your only option if called from a cmd file would be to lock down
    the folder where the script runs from?

     Thanks.

    Saturday, February 16, 2019 5:34 PM
  •  Thanks for replies..

     So if your using a SQL credentials your only option if called from a cmd file would be to lock down
    the folder where the script runs from?

     Thanks.

    That is correct or you can store the password in a file encrypted and decrypt it before you send it to SQLServer.


    \_(ツ)_/

    • Marked as answer by hart60 Sunday, February 17, 2019 2:09 AM
    Saturday, February 16, 2019 7:12 PM
  •  Thanks.

    Sunday, February 17, 2019 2:09 AM