How to run Invoke-Sqlcmd in PowerShell script from Tidal

Question

I have a PowerShell script (test.ps1) that executes a simple SQL query via Invoke-Sqlcmd. The script lives on a remote, file-share server. Below is the script:

param
(
[string]$SQL_username,
[string]$SQL_password
)

Write-Host ('begin script')
$db = 'some_DB'
$server = 'some_IP'
$query = 'SELECT TOP 10 * FROM some_schema.some_table'
Invoke-Sqlcmd -Username $SQL_username -Password $SQL_password -ServerInstance $server -Database $db -Query $query -QueryTimeout 60000
Write-Host ('end script')

When I remote to the file-share server with a Tidal user account, the script executes successfully and as expected.

I have a corresponding Tidal job that executes this test.ps1 script. Here is the Tidal job info:

Command

C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe

Command Parameters

-ExecutionPolicy bypass -File D:\some_path\test.ps1
-SQL_username "<SQL_username.1>"
-SQL_password "<SQL_password.2>"

When I run the script via Tidal, the Output screen in Tidal shows:

begin script

Invoke-Sqlcmd: The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program...

end script

So, it's clear that whatever user Tidal is using to run the script, the user recognizes the Write-Host cmdlet, but it does not recognize the Invoke-Sqlcmd cmdlet. How can this be? And, how can I resolve this? (I found that the Tidal user is NT AUTHORITY\SYSTEM, not the Runtime User specified in the Run tab of the job, which is the same Tidal user that I logged into the remote server as.)

The SQL module is not loading in the SYSTEM's PowerShell profile. See jasonq.com/blog/2012/…. You may need to set the task to run with a user account as I think maybe it won't be able to access the network when running as SYSTEM. — Tony Hinkle
I think you're right. I created a new PowerShell session using New-PSSession using another user account, and it works. — skyline01

Tony Hinkle Tony Hinkle · Accepted Answer · 2016-06-28T18:16:07

The SQL module is not loading in the SYSTEM's PowerShell profile. You will need to run something similar to the following before any invoke-sqlcmd statements:

# For SQL 2008
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

# For SQL 2012
Import-Module SqlPs

You may also need to set the task to run with a user account as it won't be able to access the network when running as SYSTEM.

How to run Invoke-Sqlcmd in PowerShell script from Tidal

1 Answers