Hello everyone,
I’m getting Login Failed on a Invoke-SqlCMD on both calls from PowerShell ISE as that user.
From here , it looks like I’ll need to pass in the credentials before calling Invoke-SqlCMD.
Tested passing username and password in as plaintext and confirmed it does work.
However, that’s not a secure solution for a SQL Server Agent job leaving password as clear text.
Edit: Also tried $securePassword= $username.GetNetworkCredential().Password with error Method invocation failed because [System.String] does not contain a method named ‘GetNetworkCredential’
Edit 2: Is there a way to not pass in clear text in password field in example 4 OR prompt for password in example 2 on this Microsoft Docs?
Please, When you post code, sample data, console output or error messages format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.
( !! Sometimes the preformatted text button hides behind the settings gear symbol. )
How would you like to pass it?
I actually don’t have experiences with SQL Server Agent jobs, but shouldn’t it handle permissions gracefully by itself?
How do you actually want to run this job?
A simple solution to carry out recurring tasks is to use the Task Scheduler and its ability to run tasks using impersonation. This way you don’t have to deal with permissions or credentials in your scripts at all.
not pass in clear text in password field in example 4
Did some research and decided to use credential manager route. Also, for some that are viewing this in the future, this blog by Martin9700 has an interesting approach as well.
I actually don’t have experiences with SQL Server Agent jobs, but shouldn’t it handle permissions gracefully by itself?
How do you actually want to run this job?
We are running it from SQL Server Agent job; calling CmdExec. SQL Server Agent service account works well until Security decided that SQL Server Agent service account should not have access to other servers remotely. So, we are having to run the PS1 as a different service account to do what it needs to do. We changed the job owner and run as on SQL Server Agent (credentials and proxy); but, for some reason it’s not passing in the job owner credentials.
But, as I had mentioned, we’re likely going with the credential manager route.
