trying to connect to SQL using Get-Credential in Winpe

Does anyone know how to pass Get-Credentials to a SQL login using powershell?

I have tried many different ways but no success.

This needs to run in winpe - i can get the credentials using Get-Credential but i need to use those to connect to SQL DB - i keep getting.

Login failed for 'NT AUTHORITY\ANONYMOUS LOGIN' The below code works fine in Windows - I am logged on as the user though so it must not be passing the credentials. if i remove the trusted_connection=true; i get the failed login and this is the best test for WINPE as no one is logged on. Is there a way to pass those Get-Credentials to SQL? Either that or the same code does not work in WINPE - not sure why though?

$Cred = Get-Credential

Function SQL_CONNECT ($Query){

    $ConnectionString = "server=VM855;database=LDMS2016;user   id=$Cred.Username;password=$Cred.Password;trusted_connection=true;"
    $SqlConnection = New-Object  System.Data.SQLClient.SQLConnection($ConnectionString)
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandText = $Query
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $a = $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    $DataSet.Tables[0]
}

$Owners = SQL_CONNECT "Select Task_Name, Task_owner, first_action_query_date from PROV_HISTORY_TASK" | Select-Object Task_Owner, first_action_query_date

$Owners

Solution

SQL connections use either:

Windows Authentication ("Trusted_Connection=True")

SQL Authentication ("User Id=myUsername;Password=myPassword;")

You cannot have both "Trusted_Connection" and "User ID/Password", you have to pick one.

In order to use Windows Authentication, the PowerShell process must be running as the user that has access to the database. i.e. you have to launch the PowerShell process as impersonating that user, and run your code.

Rough example will look something like this:

# Get the other user's credentials
$credential = Get-Credential

# Execute a scriptblock as another user
$commands = @'
#....code....    

$ConnectionString = "server=VM855;database=LDMS2016;trusted_connection=true;"

#.....etc....
'@

Start-Process -FilePath Powershell.exe -LoadUserProfile -Credential $credential -ArgumentList '-Command', $commands

Or, the easier method is to just use SQL authentication, and hard code the username/password.

 $ConnectionString = "server=VM855;database=LDMS2016;user id=Username;password=Password;"

Or at the very least you will have to use Read-Host to read in the username and password because $Cred.Password returns System.Security.SecureString and not the password in plain text.

For ex.

 $Username = Read-Host "User:"
 $Password = Read-Host "Password:"

 $ConnectionString = "server=VM855;database=LDMS2016;user id=$Username;password=$Password;"