DTS Authentication Issue

Question

DTS Authentication Issue

bridgt01

SSCommitted

Points: 1890
More actions
September 23, 2003 at 10:29 am

#159920

Scheduling a DTS package as a job. SQL Server 2000 on Windows 2000 cluster. All activity within the package pertains to the cluster, nothing else, no remote connections, etc... When we schedule the job with an id not belonging to sysadmin we get the following error:
DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
The SQLServerAgent Proxy account is set to an id belonging to sysadmin for this particular job. When we use an id to run the job belonging to sysadmin it runs fine. The owner of the dts package belongs to a non-trusted domain, not sure if this has anything to do with the problem. Does anyone have any ideas on how to configure this job to run with a non sysadmin level account successfully?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

bridgt01 SSCommitted Points: 1890 More actions · Answer 1

The owner of the DTS package is from a non-trusted domain. When we created a new package it gave ownership of the package to an account with sysadmin..this is the one that executed successfully. Does the owner of the dts package need to be a part of a trusted domain or even the same domain or does the owner of the package even matter? Seems to me as if it would in this case.

philcart SSC-Forever Points: 47794 More actions · Answer 2

Have a read of the following, it may give you an idea of what is wrong,

http://msdn.microsoft.com/library/en-us/dtssql/dts_pkgmng_sec_09dl.asp?frame=true

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

cmille19 SSCertifiable Points: 5970 More actions · Answer 3

The owner of the package really doesn't matter. In this case the DTS package will run under the security context of the owner of the job (provided you are using trusted connections in the DTS package) which executes the DTS package. If you want to use a non-sysadmin account. You will need to change the job owner to this account. The account must have permissions to do all of the tasks which are in the DTS package. I'm not postive about this, however I think if you're using Trusted connections inside the DTS package the job will need to be owned by a Windows Account.