Sql-server – BULK INSERT runs fine in window, but fails inside sql job

My personal opinion is that option #1 is the way to go. But I see no need for you to have to grant the domain account local administrator access. It seems to me that it requires access to certain folders and files and so you could grant the domain user access to only the resources that it needs to run the package successfully. This can be done through the file/folder properties dialog box and select the security tab - there should be no need to set it for every file and folder as you could set the permissions of the parent directory and set them to override child properties.

I hope this helps you.

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'