powershell script failing for windows authentication

Question

We have created powershell scripts which runs all the scripts in specific folder.

$server = Read-Host -Prompt 'Please enter server name'
$usrname = Read-Host -Prompt 'Please enter user name'
$passwd = Read-Host -Prompt 'Please enter password' -assecurestring
$dbname = Read-Host -Prompt 'Please enter DB name'
$ScriptFolder = Read-Host -Prompt 'Please enter the folder path'

$passwd =[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($passwd))

$logfile = "$ScriptFolder"+"\Scripts_Log_"+(Get-
 Date).ToString("yyyyMMdd")+".txt"

"$(Get-Date) : Queries to be executed for Database $dbname on $server " >> $logfile

try{
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = "Server=$server;Database=$dbname;trusted_connection=true;User ID=$usrname;Password=$passwd;"
 $SqlConnection.Open()
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

foreach ($query in Get-ChildItem -path $ScriptFolder -Filter *.sql)
{
    $qu = Get-Content $query.FullName

    try{

        $command = $SqlConnection.CreateCommand()
        $command.CommandText = $qu

        $Reader = $Command.ExecuteReader()

        $Reader

        "$(Get-Date) : $query - Successfully executed`n" >> $logfile
        write-host "$query - Successfully executed`n" -foregroundcolor "Green"

        $Reader.close()

        }



 catch{
        write-host "Unable to execute - $query :" -foregroundcolor "Red"
        write-host "$_.Message`n" -foregroundcolor "Red"
        "$(Get-Date) : $query - Script failed due to:" >> $logfile
        "$_.Message`n" >> $logfile
    }
}
}

  catch{
    write-host "Login failed:" -foregroundcolor "Red"
    write-host "$_.Message`n" -foregroundcolor "Red"
    "$(Get-Date) : login failedn:" >> $logfile
    "$_.Message`n" >> $logfile
}

$SqlConnection.Close()

write-host "All the process has been completed" -foregroundcolor "Yellow"
"$(Get-Date) : All the process has been completed" >> $logfile

But when this scripts run on DB server for which i have windows authentication then it doesnt do anything inside the for each loop and dont fail either. Is there a way that this script will work for both windows and sql authentication

So when it doesn't do what you want, then what does it actually do? Is there an error? To debug things, it is not enough to say it isn't doing what you intend. It is important to know what it IS doing. And to answer your last question, your connection string determines the how the user is authenticated. If you include the trusted_connection option, that is what you get - the user id and password are ignored. So to use sql authentication, you have to leave that bit out. — SMor
– SMor, Commented Sep 5, 2017 at 19:48
@SMor i clearly told that it doesnt go inside for each loop and just moves out of loop — DevelopmentIsMyPassion
– DevelopmentIsMyPassion, Commented Sep 5, 2017 at 19:59

Bacon Bits · Accepted Answer · 2017-09-05 20:24:02Z

1

Is $ScriptFolder a UNC path to a third server while in a PowerShell remote session? If so, this is probably the two-hop kerberos problem.

You're not going to be able to list the files on the third server, so the loop is looping through an empty set. You can work around it with CredSSP or various other techniques, but they're all varying degrees of painful to set up, IMX. The method they recommend above, Resource-Based Kerberos Constrained Delegation, is a method that I have not used but requires a Domain Controller with Windows Server 2012.

You might be able to work around it by granting access to the third server's file share to the computer account that you're running, but depending one exactly what you're doing it may not work.

The $using:cred method may also work, but apparently it doesn't work (or you shouldn't use it... the article is a little ambiguous) when at least one of your domain controllers is Server 2012 or later. At this point, that should certainly be true.

# This works without delegation, passing fresh creds            
# Note $Using:Cred in nested request            
$cred = Get-Credential Contoso\Administrator            
Invoke-Command -ComputerName ServerB -Credential $cred -ScriptBlock {            
    hostname            
    Invoke-Command -ComputerName ServerC -Credential $Using:cred -ScriptBlock {hostname}            
}

Here is another article that discusses the pros and cons of solutions to this problem:

Making the second hop in PowerShell Remoting

edited Sep 5, 2017 at 20:24

answered Sep 5, 2017 at 20:12

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DevelopmentIsMyPassion Over a year ago

Script folder is not on third server but on same server where we run script. The point of using powershell was to trap all errors inside sql queries which runs. Previously we were using batch file but that didn't trap errors

Bacon Bits Over a year ago

@DevelopmentIsMyPassion Wait, why is your connection string using trusted_connection=true;User ID=$usrname;Password=$passwd? trusted_connection=true is the same as Integrated Security=SSPI, which is basically, "authenticate as currently logged on user". You can't meaningfully use that and supply a username and password.

Bacon Bits Over a year ago

@DevelopmentIsMyPassion The only other thing I can think to check is to verify that Get-ChildItem -path $ScriptFolder -Filter *.sql is returning at least one item.

Collectives™ on Stack Overflow

powershell script failing for windows authentication

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related