0

My requirement is to run a SQL script on a remote server via Invoke-SqlCmd and extract some data to provide to end user.

For this purpose we got a service account made and hence the invoke-sqlcmd has to work using the same service account

This is how I tried using it

OPTION 1

Invoke-Sqlcmd -Username 'service account' -Password $SQL_creds_.GetNetworkCredential().Password -ConnectionString "Data Source=$SqlListener2;Initial Catalog=$Database2; Integrated Security=True;" -InputFile "$SQL_HOME\SQLScript\$SQLScript"

Response - This did not work as the service account is not a SQL Login ( is not an SSMS user)

OPTION 2

$Secure_Password = ConvertTo-SecureString $Password -AsPlainText -Force
$Credential = New-Object System.Management.Automation.PSCredential($User, $Secure_Password)

Invoke-Sqlcmd -Credential $Credential -ConnectionString "Data Source=$SqlListener2;Initial Catalog=$Database2; Integrated Security=True;" -InputFile "$SQL_HOME\SQLScript$SQLScript"

Response - Invoke-Sqlcmd : Parameter set cannot be resolved using the specified named parameters. At line:1 char:1

  • Invoke-Sqlcmd -Credential $Credential -ConnectionString "Data Source= ...
  •   + CategoryInfo          : InvalidArgument: (:) [Invoke-Sqlcmd], ParameterBindingException
  + FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Option 3 Through Invoke-Command but remoting is not enabled on target server so this is not an option.

HOW IT WORKS and WHICH IS NOT FEASIBLE OPTION if we have interactive Logon on, we can logon as the service account and then invoke-SqlCmd works without passing the creds as base login is of service account

tried impersonation through .NET too but unable to run the PS containing invoke-sqlcmd through the service account

Any thoughts on how to run Powershell script through a different user?

Just Another Developer
  • 479
  • 1
  • 4
  • 12
  • `Invoke-SqlCmd` can't use anything that's not a SQL credential; it doesn't support logging in as another user by itself. One option that's still available to you is to set the credentials using the Credential Manager with a Windows credential (use `:1433` for the network name). This would have to be done on server that you're running the script on, while logged in as whatever you plan on running `Invoke-SqlCmd` with. Alternatively, anything that can start a process as another user (*not* impersonation) could be used (PowerShell has a `-Credential` option). – Jeroen Mostert Apr 05 '23 at 12:08
  • Forge tthe `-Credential` bit, unfortunately the comment was already out the door by the time I noticed the problem. :P – Jeroen Mostert Apr 05 '23 at 12:15
  • You are using Integrated Security = True which is Windows Credentials. You cannot also use -Credentials. Remove the -Credentials. – jdweng Apr 05 '23 at 12:57

0 Answers0