Integration Services Catalogs remote execution

  • Hi,

    I have created into my SERVER1 a SSIS package (into a Integration Services Catalogs), this package is parametric so I have create many Environments each with specific parameters.

    If I run this package from the SERVER1 with Sql Management Studio (right click on the package and execute.. after selecting the environment) everything work well.

    If I open Sql Management Studio from a remote server [for example SERVER2] connecting to the sql instance SERVER1 and I try to run the package in the same way described above, it fails.

    Why this behavior? What's the difference to run the package from the SERVER1 rather than from SERVER2? In both situation Sql Management Studio is connected to SERVER1 instance.

    Can I run the package only from the SERVER1?

    My final goal is to have a central SSIS and many remote Sql Server instance which run a centralize package scheduled with Sql Server Agent, is it possible or Do I have to implement remote SSIS on each remote Sql Instance?

    Thanks for your help.

    Regards

  • vincenzo_capelli (3/8/2016)


    Hi,

    I have created into my SERVER1 a SSIS package (into a Integration Services Catalogs), this package is parametric so I have create many Environments each with specific parameters.

    If I run this package from the SERVER1 with Sql Management Studio (right click on the package and execute.. after selecting the environment) everything work well.

    If I open Sql Management Studio from a remote server [for example SERVER2] connecting to the sql instance SERVER1 and I try to run the package in the same way described above, it fails.

    Why this behavior? What's the difference to run the package from the SERVER1 rather than from SERVER2? In both situation Sql Management Studio is connected to SERVER1 instance.

    Can I run the package only from the SERVER1?

    My final goal is to have a central SSIS and many remote Sql Server instance which run a centralize package scheduled with Sql Server Agent, is it possible or Do I have to implement remote SSIS on each remote Sql Instance?

    Thanks for your help.

    Regards

    When you connect from Server2 to Server1, are you using a SQL login or a Windows login? It needs to be the latter, I think.

    Your 'final goal' is perfectly achievable, as long as the Agent jobs reside on the SSIS server (the remote servers can execute the jobs on the SSIS server).

    There is no such thing as 'remote SSIS', as far as I know.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • When I connect from Server2 to Server1 I'm using Windows Login, the same domain user I use when I connect from Server1.

    Thanks

    Regards

  • Post the full error message, please.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Hi,

    I moved forward in this problem even if I did solve.

    One detail probably important: The SSSI package I'm trying to run, has a Sql task (with a parametric connection) which run a remote stored procedure.

    Coming back, the error I have is "Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection " so this is a login issue.

    Only today, I noticed when I run the package from the SERVER2, into Sql Server Error Log of the remote Sql Server (the Sql Server initialized with the parametric connection I wrote above) I find "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided".

    I try to summarize:

    From Server1: From Sql management Studio connected to Server1 (with a Windows Account), I launch the SSIS package which launch a stored procedure on Server2: It works

    From Server2: From Sql management Studio connected to Server1 (with a Windows Account), I launch the SSIS package which launch a stored procedure on Server2: It fails

    From Server2: With a Sql Agent job (*) I launch the SSIS package configured on Server1 which run a stored procedure on Server2: It fails

    If I well understood we are speacking about Kerberos delegation issues. Is it right?

    Thanks

    Regards

    *The Sql Server Agent runs with a Windows Account

  • I'm no expert, but that does sound like it might be Kerberos related.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

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