Execute Stored procedure and SSIS Packages in different servers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Security (SS2K8) » Execute Stored procedure and SSIS Packages in...

Execute Stored procedure and SSIS Packages in different servers

Rate Topic

Display Mode

Topic Options

Author

Message

roshanmaugustine

Posted Thursday, August 30, 2012 1:18 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:33 AM
Points: 10, Visits: 112

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_Name Source_ServerName Target_ServerName Source_DBName Target_DBName UID PWD

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

Post #1352032

opc.three

Posted Thursday, August 30, 2012 9:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1352336

« Prev Topic | Next Topic »

Permissions