7

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 the user account. I then created an SSIS proxy (BatchLoad-Proxy) using the BatchLoad-credential credential, active within the SSIS package subsystem, and added the SQL login for Domain1\NewUser as a proxy account principal. Then I changed the SQL Agent job step running the SSIS package to run as the BatchLoad-Proxy, and changed the owner of the job from Domain1\Admin2 to the Domain1\NewUser account.

When the job is run, we get this error (some masking on the log has been done):

Executed as user: Domain1\NewUser. Microsoft (R) SQL Server Execute Package Utility Version 11.0.6020.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:46:17 PM

Error: 2016-06-24 12:46:28.56 Code: 0xC0202009 Source: xxxxxxx_xxxx Connection manager "yyyyyyyyyyy" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.". End Error

Again, the package works fine when a SYSADMIN runs it interactively within BIDS so we know the file is in the correct format and the package is designed ok.

I do not have permissions to the network share. When I try to connect manually I get a similar error:

Windows cannot access
\\<path to the network share>
Error code: 0x80004005
Unspecified error

Connecting to the network share using the Domain1\NewUser account and password, I can view the folder ok so I have verified the proper user account has access.

  • 3
    Great first question. The error External table is not in the expected format doesn't smell like a permissions issue which I was expecting given the title. You say it runs fine from BIDS, but that's for you in an interactive mode. Given the breadth of the potential problem domain, I'd start by using RunAs to run the SSIS package on your machine (or the server, whatever) to identify is this a SQL Agent/Proxy issue or a user issue. Command approximately "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx – billinkc Jun 30 '16 at 2:46
  • @billinkc , I also mentioned that this same package runs fine when executed in a SQL Agent job within a job step as "SQL Server Agent Service Account". In other words, all things kept the same, it is only when I change the job step to run as the new proxy and change the owner of the job to Domain1\NewUser does the job fail. If I only change the job step it fails but with less information. We have ruled out the obvious things like the XLS is in a bad format, file in use, permissions for the NewUser account, etc. It seems to me the only piece is the SSIS proxy and credentials... – GregDBA Jun 30 '16 at 13:18
  • Are you setting the job owner so that the group with the proxy credentials can manage the agent jobs? Change schedule and what not. – Jason Cumberland Aug 25 '16 at 8:19
  • Yes I added the group the job owner is a member of to the MSDB.SQLAgentUserRole. Because this Domain1\NewUser account is really a batch execution account, there's no concern about it needing to modify its own job. I did this anyway. Since this was a problem with the local user profile for Excel, none of the SQL permissions were ever really an issue. I posted on 8/23 that after realizing this the job has been running fine. Thanks Jason. – GregDBA Sep 2 '16 at 13:05
2

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:

  1. 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."

0

Have you tried to give permission to Domain1\NewUser to MSDB(db_owner for tests)?

Here you have a great article about the SSIS Proxy users: https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

Especially excerpt:

EXEC msdb.dbo.sp_update_proxy 
@proxy_name = N'proxy_name', 
@enabled = 1 
  • Thanks for the suggestion. Yes, I've read this and several other articles as I went through the SSIS proxy setup. – GregDBA Aug 23 '16 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.