Sql-server – Command runs fine in elevated command prompt, but fails when run in a Windows Task Scheduler task

I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.

Summary of solution:

• SQL Job with one Job Step
• The Job Step executes the SSIS package

To run a SQL Job with a custom you must also create:

• an instance-level Credential
• a Proxy Account which should be mapped to the Credential

I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!

For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.

Hope this helps someone else in the same situation -

Potential Workarounds

1. Since you're running PowerShell through SQL Server CmdExec from an agent job, you could try calling a PowerShell script with your saved PS logic in it rather than running raw PowerShell commands to see if that makes a difference for a potential workaround.

2. Try explicitly putting the full OS path on the server to point to PowerShell.exe and then pass your PowerShell commands after that in case the issue has to do with environmental variables not working correctly for PowerShell within the CmdExec shell.

My question: Why does the same command fail as a job, but succeed from the console when both executed from the same user account on the same server?

Potential Reasons

• It could be an issue when calling PowerShell.exe thru the SQL Server Agent job with the CmdExec shell and how it interprets security context of the account it's run from or as (e.g. the service account) when it tries to run PowerShell.exe that way so it's not able to authenticate the execution access to the EXE due to this.
• Check and read up on the version of PowerShell installed, the version of SQL Server installed, and the Windows OS all these are on and run from for "known" issues related to version incompatibilities in this respect - it may be a known issue depending on ALL the specifics in this environment.

A working way I run PowerShell from SQL Agent Jobs

You will need to:

1. Create the [user account object] service account in AD (<DomainName>\<ServiceAccount>)
2. Create a SQL login i.e. the service account in #1 (<DomainName>\<ServiceAccount>)
3. Create a SQL credential with that same (<DomainName>\<ServiceAccount>) account credentials,
4. Create a SQL Server Agent Proxy account to use for the "Run As" from the SQL Agent job to execute PowerShell

Below is a script of the T-SQL part of this process which I run after doing the above 4 steps, and it always works in my environment to accomplish what seems to be similar to what you've explained that you're trying to accomplish. I also have to ensure the AD account has a strong/complex password, and set it to never expire.

--- // Create login on SQL Instance for domain\PSSQLJobs service account if it does not exist
IF NOT EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
BEGIN
    CREATE LOGIN [domain\PSSQLJobs]
    FROM WINDOWS WITH DEFAULT_DATABASE = [master]
        ,DEFAULT_LANGUAGE = [us_english]
END

USE [master]

IF NOT EXISTS (
        SELECT *
        FROM sys.database_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
    CREATE USER [domain\PSSQLJobs]
    FOR LOGIN [domain\PSSQLJobs]
    WITH DEFAULT_SCHEMA = [dbo]

EXEC sp_addrolemember N'db_datareader'
    ,N'domain\PSSQLJobs'

GRANT CONNECT
    ON DATABASE::[master]
    TO [domain\PSSQLJobs]

USE [msdb]

IF NOT EXISTS (
        SELECT *
        FROM sys.database_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
    CREATE USER [domain\PSSQLJobs]
    FOR LOGIN [domain\PSSQLJobs]
    WITH DEFAULT_SCHEMA = [dbo]

EXEC sp_addrolemember N'db_datareader'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentOperatorRole'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentReaderRole'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentUserRole'
    ,N'domain\PSSQLJobs'

GRANT CONNECT
    ON DATABASE::[msdb]
    TO [domain\PSSQLJobs]



--- // Create Credential *** Type in password of account into value of SECRET ***
USE [msdb]

CREATE CREDENTIAL PSSQLJobs
    WITH IDENTITY = 'domain\PSSQLJobs'
        ,SECRET = '*******'


--- // Create PowerShell Proxy account and give PSSQLJobs access to it
USE [msdb]

EXEC msdb.dbo.sp_add_proxy @proxy_name = N'ExecutePowershell'
    ,@credential_name = N'PSSQLJobs'
    ,@enabled = 1

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'ExecutePowershell'
    ,@subsystem_id = 12

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name = N'ExecutePowershell'
    ,@login_name = N'domain\PSSQLJobs'