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,