RE: DTS, .NET and remote servers
- From: craig_amtdatatechnologies@xxxxxxxxxxxxxx <craigamtdatatechnologiesdiscussionsmi@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 24 Mar 2006 01:25:01 -0800
..... and yes in the LoadFromSQLServer the flags are set to UseTrustedConnection
"craig_amtdatatechnologies@xxxxxxxxxxxxxx" wrote:
Hi,.
This is continuation of an issue I posted on before. Thanks to the responses
for to that , as they gave some pointers - but problem is not fully resolved.
The background is:-
Server A is running SQL 2000 and in domain X
Server B is running IIS and host web-sevvice, also in domain X
Web-service (using .NET DTS cookbook ideas - google for details) has a
.NET wrapper that has method LoadFromSQLServer(.... various details etc.)
So, I wish my web-service to trigger a DTS import on server A. The
web-service impersonates an NT user on Server A, and all stored procedures
etc. run ok when called from web-service.
My DTS package is loading a file (stored on server A, and referenced by a
path recognised by Server A) and processing this.
My SQL connection in the package refers to 'Server A', not 'localhost'
When executed, I (that is my code) successfully access and Load the DTS
package onto Server B, manipulate a property or two (e.g. path to file -
demonstrating the DTS package is loaded and accessible on Server B).
Then I call <mypackageobject>.Execute and an exception is thrown, stating ....
" DTSStep_DTSExecuteSQLTask_2, -2147467259, Microsoft OLE DB Provider for
SQL Server, Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection "
So, obviously SQL is right to flip at the null credentials.
The issue seems to revolve around encapsulating my impersonated credentials
within the DTS Package when it runs on Server B (which clearly is not
happening).
I have tried both true and false for 'persist credentials' on the advanced
tab of my SQL Connection within SQL (probably a red herring, but hey I'm
desparate).
... would making the DTS package reference a UDL help? How to force it to
use a specific NT account, in the same-way my .NET web.config has <identity
impersonate="true" etc .... /> ?
Answers v welcome, and I have a few people who would love to know the
solution I come up with ! (please no one say use SQL 2005 SSIS!).
Many thanks for any help offered. (and thank u Alan for your earlier answers).
Craig
- References:
- DTS, .NET and remote servers
- From: craig_amtdatatechnologies@discussions.mi
- DTS, .NET and remote servers
- Prev by Date: Re: SSIS Bulk insert task: Set the input filename at run time
- Next by Date: Dynamic DTS Help
- Previous by thread: DTS, .NET and remote servers
- Next by thread: SSIS Bulk insert task: Set the input filename at run time
- Index(es):
Relevant Pages
|
