May 4, 2016 at 3:17 am
Should I create a single proxy for each SQL Job Subsystem or a proxy for each job?
Currently, for each job that runs an SSIS package, I create a new Windows User, a New SQL Login, a new SQL Credential, and a new SQL Proxy.
The Windows User only has NTFS permissions that each SSIS package needs (read on the package, modify on output folders etc)
The SQL Login only has execute permissions on SPROC used by each SSIS package.
While I think this is secure, it feels overkill. Should I simplify this process?
May 5, 2016 at 3:10 am
Personally, I think this is overkill. Usually I have a single service account to run all SQL Agent jobs and ensure that job has appropriate permissions.
If you are doing something that really needs separate security permissions then set up different accounts for those jobs but otherwise limit the number of different accounts that you use.
Jez
May 5, 2016 at 4:45 am
Jez-448386 (5/5/2016)
Personally, I think this is overkill. Usually I have a single service account to run all SQL Agent jobs and ensure that job has appropriate permissions.If you are doing something that really needs separate security permissions then set up different accounts for those jobs but otherwise limit the number of different accounts that you use.
Jez
So is your service account the account that runs the agent service or another account that you configure via credential/proxy just for SSIS agent jobs?
I'm struggling with this because all web resources talk about just getting it working, ie, how to create a credential and a proxy for the agent so SSIS can read/write to non local locations. Nowhere can I find anything about best practise or security scenarios.
May 5, 2016 at 5:43 am
i'm pretty close to Jez's methodology; i've pretty much had one specific account for 90% of my proxy situations; it's mostly about accessing network resources. i tend to use that account as a proxy on all my servers.
there's that other ten percent where one size doesn't quite fit all, so there are situations where I did create other proxies for specific scenarios, though; instead of expanding permissions for my default proxy, i created a new one that was picking up files from the drop location from some secureFTP stuff,so specialized stuff that doesn't need to be on the general proxy account,
i've created a handful of additional credentials for those one off or specific purposes. I had another situations where i was moving files based on database values, so i created a credential for that too.
Lowell
May 5, 2016 at 6:14 am
As Lowell said, one service account to run all SQL Agent jobs whether than is an SSIS package or stored procedures for batch process.
Jez
May 5, 2016 at 6:39 am
So you have one Windows Account, One Credential,and One Proxy.
This account then has read/write on any external file locations as needed by any SSIS package.
Is this Windows account then used as the Login/User as the job owner? If so, do you make the User a DBO on the database or do you wrap all SSIS db calls into SPROCs and only give EXECUTE on each SPROC do this user?
May 5, 2016 at 7:19 am
Generally the account had DBO rights in each DB. Where the SSIS package needs to access data on a remote server or sFTP we would use login details stored in an encrypted DB table which are retrieved at run time - I never store login details in a package.
Jez
May 5, 2016 at 7:30 am
Is this account separate from the one used as the SQL Agent Service "Log On As" Account?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply