I'm attempting to make a Powershell script that needs to interact with SQL Server, so part of the script will involve invoke-sqlcmd. However, the script can't be scheduled as a job in SQL Server and thus make use of the Agent to act as the Windows account used for authentication. Instead, it is going to be run using a different service under an arbitrary Windows account. The documentation for invoke-sqlcmd states that the -Username and -Password parameters are for SQL Server authentication, not Windows, and that it defaults to using the Windows account that is running the script for authentication if no SQL Server parameters are provided.
Is there any way to allow invoke-sqlcmd to use an arbitrary Windows account for authentication instead of the one it's being run under? I am aware that a possible solution would be to make accounts on the SQL Server instances that I want to run the script for and just use the SQL Server authentication option on the command, but I would ideally not want to have to do that. Some other research has also shown that a certificate could be an option to pursue, but I don't think that invoke-sqlcmd can take in certificates or otherwise utilize them like suggested. I could be wrong here though.
Best Answer
You could grant the account that is going to run the job the necessary permissions onto the SQL Server to run the query. That would be the easiest. If you are going to use Task Scheduler, it even gives a method for specifying the account to use.
The next best would be to use SQL Authentication.
Finally, you can use PowerShell to invoke a new thread to run under a different windows account, that thread then to use Invoke-sql. However, the password then needs to be stored somewhere somehow (usually encrypted in a file). This is not the best solution and not one that I would recommend using.