Post reply

SQL 2000 scheduled job fails ...Not associated with a trusted SQL Server connection

  • May 13, 2009 at 7:58 am

    #132267

    My issue is quite simple I think – we have 2 SQL 2000 servers and for simplicity I will call them Server1 and Server2. Server1 is the server containing the table I need and Server2 is my server that the data is to be copied to.

    I simply need to copy a table via a DTS package and scheduled job from Server1 to Server2 using the SQL Server scheduler. Our IT guys would prefer to set up the DTS package and schedule the job on Server1 rather than me doing it from my Server2.

    The DTS runs fine within enterprise manager outside the scheduler on Server1 but the problem is that when the scheduled job runs from Server1 it falls over with the error “Login failed for user null. Not associated with a trusted SQL Server connection”. I think that this is something to do with the Server1 not having the appropriate Windows permissions on Server2 but I don’t know which permissions need setting up on Server1 in order to get this working (and indeed matching SQL logins on Server2).

    Any help will be greatly appreciated

    Cheers

    Len

  • May 13, 2009 at 3:09 pm

    #994126

    What type of security are you using within the DTS package?

    If it's Windows Authentication, then the package is using the account that starts the SQL Agent from the scheduled job.

    _____________________________________________________________________
    - Nate

    @nate_hughes

  • May 13, 2009 at 3:16 pm

    #994133

    Yes it's Windows authentication

  • May 13, 2009 at 3:44 pm

    #994155

    Is your SQL Agent service running as a local system account, or a domain account?

    When using Windows authentication, the package will use the credentials of the user running the package. When you run it in Enterprise Manager, it connects as you. When it runs as a scheduled job, it connects using the SQL Agent service login. If SQL Agent is not using a domain login, it has no domain rights and cannot connect to another server with Windows authentication.

  • May 14, 2009 at 6:21 am

    #994409

    Since I don't have access to manage domain accounts in our environment, my preference is to use SQL Server Authentication in packages that go against other servers. That way if something's FUBAR'd, there's one less variable to deal with ;-).

    _____________________________________________________________________
    - Nate

    @nate_hughes

  • May 16, 2009 at 8:57 am

    #995872

    Thanks to you all for the replies, I got a result in the endSQL . The stuff about domain accounts got me into using SqlServerAgentCmdExec and this works now across the 2 servers but....

    If anyone has time I think it would be good to know when communicating between SQL servers, the advantages/disadvantages of using the different options i.e.

    * Windows authentication

    * sql server logins or

    * another method which was suggested - linked servers with Windows or SQL authentication.

    It would be nice to know of peoples experiences using the 3 different methods.

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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