Execute Stored procedure and SSIS Packages in different servers

  • Hi All,

    I have to execute some stored procedure and SSIS packages (It copies data from one to table to another table between two databases).It can be within the same server or in different server. I should use SQL server Authentication to execute these procedures and packages. But Application is using windows authentication to logging to the application

    I have one table which stores the userid and password in encrypted format.

    Table structure for the above

    Process_ID (PK)Process_NameSource_ServerNameTarget_ServerNameSource_DBNameTarget_DBNameUIDPWD

    From front end application we can choose the Process_Name (SP Name or PackageName) IF the Logged in used has the rights to execute the Process then from SQL we have to choose the default UID and Password and execute the process using this How can I achieve this, Which are the minimum rights should I provide to this SQL User, Which is the best method to connect two SQL server

    Thanks in Advance,

    Roshan

  • Hi roshanmaugustine. I am not trying to be rude here but I must say, I am sure that what you posted makes sense in your head, but reading it without knowing your environment created more questions for me than answers. Please give specific details as to your environment and situation. What kind of application? ASP.NET or is this a Windows Forms app located on the user's desktop? What do you envision the user executing directly?

    - a stored procedure that executes an SSIS package

    - an SSIS package that executes stored procedures

    - both stored procedures and SSIS packages directly

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 1 (of 1 total)

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