Lets suppose I have to create and deploy an SSIS package to sql server (name it SQL1) load some data from another sql server (SQL2). On SQL I want to create a job as well to execute this package. Lets suppose other untrusted persons also can create SSIS packages and jobs on this server, who are not allowed to access the SQL2 databases from here.
But first I think I must grant some permission to let my job access the required database on SQL2. The problems starts here - how?
- as I see the SSIS jobs runs on SQL1 on a service user called "NT SERVICE\SQLSERVERAGENT", which seems to be a local user (but I don't understand the NT SERVICE\ prefix) - how can I add permission for this user on SQL2?
- as far as I know I can change the SQL1 agent to run as a defined AD service user - but this will affect all the SSIS jobs on SQL1 - looks bad
- I can add an AD service user account as credential and a proxy on SQL1, and let the SSIS step runs as this user
- other way? which way is best practice?
So without involving an AD service user can I solve this problem? For me #1 solution needs no other service user - but #2 and #3.
Security level: can I solve this problem without letting another untrusted person to re-use easily the permissions granted for my own SSIS on SQL1? As I see #1 is very bad - as in this case all SSIS packages on SQL1 can access SQL2. On case #2 - seems to be very bad also for the same reason. On #3 case - as the credentials and proxies are "public" on SQL1, everybody can assign this proxy for his own SSIS's steps - so looks bad.
Maybe there is no good solution for my problem - just when I use a brand new SQL server without letting other untrusted persons to access it?
Any advice or article to read on this topic is highly appreciated! Thanks!
CodePudding user response：
The best practice for running SSIS packages by using the SQL Server Agent is that SQL Server Agent is run by a domain account (actually this is the best practice for all SQL Server services, you should never use the defaults in production environments) and create a credential and a proxy account for running the SSIS packages.
From the security point of view, you could create the connection to SQL2 in the SSIS-package and provide the connection with a hard coded user and password. To protect the credentials, you can use a Package protection level that suits you (password protection might be the easiest): https://docs.microsoft.com/en-us/sql/integration-services/security/access-control-for-sensitive-data-in-packages?view=sql-server-ver15#protection-levels