Post reply

authentication problem: RUN SSIS on multiple database servers

  • May 18, 2009 at 4:56 am

    #131676

    Hi,

    I have a authentication problem.

    I have a SSIS package which runs on two different database servers. When I run the package from Management Studio it runs fine on both servers.

    I have windows authentication for one server

    and SQL Sever authentication for other server.

    Windows authentication is on the same server where SSIS runs so its no problem there.

    But on other server it throws error.

    Event ID:18456 "Login failed for user 'SSIS'. Reason: Password did not match that for the login provided"

    This SSIS is a SQL Server user not windows user on second database server.

    I am trying from many hours but no success ):

    Thanks!

  • May 18, 2009 at 9:03 am

    #996407

    Using Management Studio can you log in as user 'SSIS' and connect to the server you are reporting the issues on? If so, does the package run successfully on the server you are having issues with when connected as user 'SSIS'?

  • May 18, 2009 at 9:07 am

    #996413

    thanks!

    Yes I can log in by that user and if I run package from Management Studio it runs.

    But when it run as job on specified time it give login error.

    And I have SSIS on other server not on this one (where I have the login problem).

  • May 18, 2009 at 9:25 am

    #996427

    hmmm... I have experienced something similar in the past but that was related to the user not having a valid DEFAULT DATABASE set. I would expect that to show itself though if you are trying to connect using Management Studio.

    Can you get the event details from the server logs? With that information (state and severity), the following may help you pin point the underlying issue:

    http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

  • May 18, 2009 at 9:30 am

    #996433

    These two links maybe helpful. Maybe setting the password protection level differently, or if the package is in a job, create a proxie which will be able to access both servers and use that for the job.

    http://msdn.microsoft.com/en-us/library/ms141747.aspx

    http://support.microsoft.com/kb/918760

  • May 19, 2009 at 3:42 am

    #996802

    Thanks brad.mason5!

    I tried by creating proxy which has access to both servers and it worked 🙂

    Thanks very much:)

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

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