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.
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 we hit Linked Servers. Unfortunately Linked Servers do not work with Windows Authentication very well.
At the beginning we thought that we could just use "Be made using the 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 it?
Should we just use "Be made using this security context"? It does not support Windows Authentication so we would need to use SQL authentication. This mean in Server 1 we would 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 requires more maintenance that just using domain account for everything.
There is also possibility of using "Local server login to remote server login mappings". From what I gathered this table does support Windows Authentication but the downside is that we would need to specify all logins that should have access manually in each Linked Server and it would be very laborious.
So is there a clean way of integrating Linked Server with our concept? 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.
I'm aware that similar topics were mentioned previously but I'm still unable to make a decision what is the best approach in our case.
So if there isn't any other way - which of this approaches would you recommend for us to take?
This question is based on part of my previous question which was deemed to be too wide in scope. So I'm writing this question focused on only single problem - Linked Servers
SQL Server - Holistic approach to managing permissions from a domain
But we quickly hit the double hop problem [...]This is only a problem if not correctly setup. I would set this up properly and you can achieve what you're looking to do.