question

Anon4343-9491 avatar image
0 Votes"
Anon4343-9491 asked Anon4343-9491 answered

Proxy User Unable to Access Server's ODBC Connections Without Local Administrator

I'm having trouble getting an SSIS package to access an ODBC connection via a domain account set up as a proxy user. If I grant the domain account local administrator on the SQL Server, the package will connect with the ODBC connection. If I log into the Windows server as that user, the package will run successfully.

What needs granted to the domain account in order to read a system ODBC connection? Domain users already have read access by default. This may be a case where the ODBC driver isn't able to run without the user being able to spawn processes?

Error: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.

The AcquireConnection method call to the connection manager [ODBC Connection Name].[Username] failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Thanks.

sql-server-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Anon4343-9491 avatar image
1 Vote"
Anon4343-9491 answered

Using Process Monitor as suggested in one of the links provided by Zoe, I found that the NTFS permissions on some of the IBM I Access Client Solutions driver files were not accessible to the domain user account being used as a proxy. Filtering for ACCESS DENIED in Process Monitor revealed the file locations. Resetting the NTFS permissions to allow inheritance set the file permissions to match the folder.

Affected files:

C:\Users\Public\Documents\IBM\Client Access\cwbssldf.sth
C:\Users\Public\Documents\IBM\Client Access\cwbssldf.kdb

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered Anon4343-9491 commented

Hi @Anon4343-9491,

Based on the error message, the issue seems to be related with account permissions.

The account that log on the SQL Agent job may doesn’t have permission to access the database server or SSIS engine uses the design time values of a task until it actually runs a task when DelayValidation is not set to true. Please note that the user has sysadmin permission doesn't means he has permissions to access the database server in ODBC Connection Manager.

More details you may refer:

ssis-package-failed

how-do-i-troubleshoot-ssis-packages-failed-execution-in-a-sql-agent-job

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Zoe.

The domain account does not have sysadmin access on the database server. I think the key is that the package works when the user is logged into the server. Perhaps something required by with the drivers being called by the ODBC connection?

I followed one of your links to grab a Process Monitor file and search for ACCESS DENIED. I found a few entries.

98198-procmon.png


Here are the permissions on the cwbssldf.sth file. Everyone has access to other files and folders within the Public folder. I'm not sure if anything really needs changed on the file permissions.

98199-permissions.png


0 Votes 0 ·
procmon.png (184.9 KiB)
permissions.png (91.6 KiB)