RE: DTS, .NET and remote servers

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance



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












.



Relevant Pages

  • RE: Fulltext failure on a 2 node cluster
    ... Server full-text search resource online: "SQL Cluster Resource 'Full Text' ...
    (microsoft.public.sqlserver.clustering)
  • Re: HELP PLEASE ~ ???
    ... You mentioned that it went ahead and added a SQL ... SQL Server 2000 database for all my data. ... find the connectionString in the newly recreated SQLExpress database. ... The connection string specifies a local Sql Server Express instance ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Multi-Channel Raid VS SAN Storage
    ... A 5~6 years old server is a very old server. ... As I mentioned, the server is one node in a cluster environment, and SQL is ... We actually are running RAID 1+0 and our aplication is definately more ... needs it's own SAN device, or at least a dedicated IO channel on the SAN. ...
    (microsoft.public.sqlserver.setup)
  • Re: Multi-Channel Raid VS SAN Storage
    ... A 5~6 years old server is a very old server. ... As I mentioned, the server is one node in a cluster environment, and SQL is ... We actually are running RAID 1+0 and our aplication is definately more ... needs it's own SAN device, or at least a dedicated IO channel on the SAN. ...
    (microsoft.public.sqlserver.setup)
  • Error during pre-login handshake: SSL certificates and IIS?!
    ... After rebooting the server, people were telling me that SQL wasn't working anymore. ... If I'd be installing a new certificate, manually, I think I would break up again the SQL... ... Scott wrote: ...
    (microsoft.public.sqlserver.server)