debugMinor
SSIS proxy/credentials not working from within SQL Agent job step
Viewed 0 times
proxysqlssisagentworkingcredentialswithinstepfromnot
Problem
I have been looking around and found similar questions but nothing specific to what I am having trouble with.
Problem:
I cannot get the SSIS proxy and SQL credentials configured correctly to impersonate a restricted-access service account for a SQL Agent job step that runs an SSIS package. The restricted-access service account has all the needed privileges and the package is designed correctly. I have read much material on this subject and would like some help in case I missed something.
Background:
(SQL 2012 SP3 server running on Windows Server 2012 R2.
SQL engine runs under Domain1\Admin1 and SQL Agent runs under Domain1\Admin2. both also are in the SYSADMIN server role.)
We have an SSIS package that works fine when run interactively by a SYSADMIN, and also runs fine when run within a job step as "SQL Server Agent Service Account". However our Security group wants us to run using credentials limited to what the job needs to do, and I understand this as a best practice anyway. Everything I have read indicates that an SSIS proxy and credential will help solve for this requirement; however I can't get the configuration to work so I must be doing something wrong.
The package is run via SSIS package job step. It is a local file and is not deployed to MSDB or SSISDB. The package connects to a network share, loads into a database that is local to the SQL server, truncates a table, runs a few stored procedures, then deletes the XLS file. The job step is set to use the 32-bit runtime.
Security created an account (Domain1\NewUser) that will have Modify rights to the network share. I also asked they add Domain1\Admin2 as Modify to the same share.
For setup, I created a SQL login for Domain1\NewUser using only the Public role, and added it as db_owner for the database in question because of the range of actions the package needs to perform. I created a credential (BatchLoad-credential) using as its identity the Domain1\NewUser account and the exact working password of t
Problem:
I cannot get the SSIS proxy and SQL credentials configured correctly to impersonate a restricted-access service account for a SQL Agent job step that runs an SSIS package. The restricted-access service account has all the needed privileges and the package is designed correctly. I have read much material on this subject and would like some help in case I missed something.
Background:
(SQL 2012 SP3 server running on Windows Server 2012 R2.
SQL engine runs under Domain1\Admin1 and SQL Agent runs under Domain1\Admin2. both also are in the SYSADMIN server role.)
We have an SSIS package that works fine when run interactively by a SYSADMIN, and also runs fine when run within a job step as "SQL Server Agent Service Account". However our Security group wants us to run using credentials limited to what the job needs to do, and I understand this as a best practice anyway. Everything I have read indicates that an SSIS proxy and credential will help solve for this requirement; however I can't get the configuration to work so I must be doing something wrong.
The package is run via SSIS package job step. It is a local file and is not deployed to MSDB or SSISDB. The package connects to a network share, loads into a database that is local to the SQL server, truncates a table, runs a few stored procedures, then deletes the XLS file. The job step is set to use the 32-bit runtime.
Security created an account (Domain1\NewUser) that will have Modify rights to the network share. I also asked they add Domain1\Admin2 as Modify to the same share.
For setup, I created a SQL login for Domain1\NewUser using only the Public role, and added it as db_owner for the database in question because of the range of actions the package needs to perform. I created a credential (BatchLoad-credential) using as its identity the Domain1\NewUser account and the exact working password of t
Solution
I appreciate the feedback on this, and I hope this helps people in the future. I finally narrowed down the true root cause of the problem. I had not provided enough detail around the fact that an Excel file was being used. After disabling everything in the package, If I only enabled the Data flow task using the Excel source, I would get the failure, but only when using the SSIS proxy (which launched the job using the Domain1\NewUser account). If I set the job step to use the SQL Server Agent account, everything would work fine.
After some time away from the problem, I tried logging into the server with the NewUser1 credentials, and ran Excel for the first time. It prompted me for initials and then I closed the program. I had also added the account to the server's local Administrators group so I could RDP.
Then I ran the job using the SSIS proxy and everything worked fine. When I removed the account from the local Administrators group it would fail again, but I determined it was the local policy "logon as a batch job" that was being granted in that membership.
Here is what I have learned from this experience:
SSIS proxy is a valid solution for granting necessary privileges for
a batch job. Excel (and possibly other applications) may need to be
launched once using the related credentials, just to complete
application settings in the user's profile on the server. Logon as a
batch job is required for a proxy that runs DTExec to launch a
package from the file system. Excel sources are problematic, and the
OLE DB driver may report this problem when it isn't a layout/format
issue:
Hresult: 0x80004005 Description: "External table is not in the
expected format."
After some time away from the problem, I tried logging into the server with the NewUser1 credentials, and ran Excel for the first time. It prompted me for initials and then I closed the program. I had also added the account to the server's local Administrators group so I could RDP.
Then I ran the job using the SSIS proxy and everything worked fine. When I removed the account from the local Administrators group it would fail again, but I determined it was the local policy "logon as a batch job" that was being granted in that membership.
Here is what I have learned from this experience:
- SQL Credentials can only impersonate a user account, NOT a group.
SSIS proxy is a valid solution for granting necessary privileges for
a batch job. Excel (and possibly other applications) may need to be
launched once using the related credentials, just to complete
application settings in the user's profile on the server. Logon as a
batch job is required for a proxy that runs DTExec to launch a
package from the file system. Excel sources are problematic, and the
OLE DB driver may report this problem when it isn't a layout/format
issue:
Hresult: 0x80004005 Description: "External table is not in the
expected format."
Context
StackExchange Database Administrators Q#142575, answer score: 2
Revisions (0)
No revisions yet.