MS SQL Server DTS - DTS, .NET and remote servers

Author

DTS, .NET and remote servers

craig_amtdatatechnologies@discussions.mi

2006-03-23, 8:35 pm

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_DTSExecuteSQ
LTask_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

craig_amtdatatechnologies@discussions.mi

2006-03-24, 3:27 am

..... and yes in the LoadFromSQLServer the flags are set to UseTrustedConnection

" craig_amtdatatechnol
ogies@discussions.mi" 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_DTSExecuteSQ
LTask_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
>
>
>
>
>
>
>
>
>
>
>
>

Sponsored Links