We are small IT department and we are changing how our infrastructure works. We are trying to make it more secure, manageable and transparent for auditing. We are implementing a domain for our servers and possibly in the future for our workstations.
Disclaimer: This questions won't be very strict but rather touch many different problems like Linked Servers or SSIS. I decided that there is no point of splitting this question into separate threads as it all boils down to a single concept of managing database infrastructure and permissions. Each of this separate concepts need to work in sync with everything else and this question is about that whole concept. So please bear with me. I will try to formulate specific questions at the end but this is hard for me as this is unknown territory for me and I could miss some crucial topic. If you think I should formulate my question in different way please let me know.
Currently our concept looks like this (a little simplified for the sake of this question):
Green arrows show where we would modify access. The rest is pretty much static.
We have multiple computers hosting SQL Server instances. Each instance can have multiple databases. For the most part each computer/SQL Server is "encapsulated" and we can assume that we do not split permissions across each database but rather treat whole SQL instance with all its database as single entity.
For each SQL instance we have couple of permission levels (at the moment we distinguish 4 - Read, Write, Developer, Admin). Each of this permission levels is a database login with windows authentication based on domain groups.
We also have couple of external companies that are deploying and managing many different applications and services living on our servers. We create domain group for each company and then add them to appropriate groups with SQL login attached.
Our goal is to manage access to our servers from a domain. This approach gives us one centralized point of control for all SQL Servers and more transparency, as we basically create logins once and ideally never touch them again by only working from AD level.
But the world isn't so clean and simple. We had all that laid out but then Linked Server, SQL Agent, SSIS, xp_cmdshell etc. came into picture.
First lets talk about Linked Server.
At the beginning we thought that we could just use "login's current security context". People or applications would login with their standard domain credentials and linked server would pass that to the next server. So we could manage from the AD if particular user should have access to that particular remote server or not. Below you can see blue arrows that represent that domain access on both servers.
But we quickly hit the double hop problem where credentials are not passed from one server to another and the whole approach would not work for complex multi server queries.
So the question is what should we do about Linked Servers? Should we just use SQL authentication? This mean in Server 1 we create an SQL login that correspond to a Linked Server in Server 2 etc.? This is potential problem as people could access linked server even when they should not have access to that remote server. And it is more complicated than using domain groups only.
Maybe there is a way of integrating this with concept of managing everything from the domain? I looked into Kerberos and it seems like it might be a solution to a double hop problem, but I'm not sure if our small team can handle such rabbit hole as Kerberos.
Next there is a problem with SQL Agent and similarly with SSIS. Who should be able to run the jobs? Who should have access to what?
Should there be a domain account for SSIS that would have corresponding login on every server inside SSIS? Or we don't need that and we could get by only by assigning database-level roles in SSISDB?
Or are there any gotchas that I'm missing? Like dedicated OS user for SSIS or SQL Agent or proxy?
In case of special command
xp_cmdshell I've read that we need proxy account for it to work for non-sysadmin account. What is recommended way of approaching this? Each server should have its own domain account for running this command?
Is it good idea to use domain user for for everything? SQL Server service account, proxy account, SQL Agent and Linked Servers?
What do you think? I know this big question but I'm stuck and not sure what to do next.