How to impersonate an AD Login within a AD Group defined in SQL
-
We have active directory groups defined within our SQL 2008 server. I have another AD login defined on the server. Is there a possible way to have the AD login impersonate an AD Login within the AD directory? I have done a lot of searches and can't find a definitive answer either way.
If not, the AD groups are assigned to a DB role. Is it possible for the AD login to impersonate the role and would that impersonate anyone assigned to that role?
Thanks for the input!
-
I think I have understood what are asking! When not then I am sure you will let me know!
We have Groups in AD and obviously These Groups are populated with AD Users and/or other Groups. These Groups are added to the SQL Server instance and roles and permissions assigned in exactly the same way as a normal AD user.
From within AD itself is nothing Special required. The Groups Need to be created of course but no Special permissions Need to be granted.
This is a Scenario that we have in our Company and it works very well, is extremely simple to administer and provides excellent oversight where several databases exist in the same instance.
I hope that helped.....
-
Kevaburg, thanks for the reply!
I want to utilize impersonation within SQL Server and not within AD. I want the one AD login to run queries on SQL server impersonating an AD login contained within an AD group. Does that make sense?
Thanks again!
-
Hi there,
If you want to impersonate a user and that user exists in AD, then you need to create a login for that user before it can be impersonated. Unfortunately (perhaps) SQL Server cannot read individuals directly out of a group created as a login in SQL Server.
If you want an AD user to impersonate another AD user then you need to contact your AD team and see what they can do for you.
Regards,
Kev
-
Maybe this will help. I don't know why you want to do this. But I can think of two ways you can "impersonate" another user.
First, you can impersonate another AD user if you know their password. Just go into Start Menu, find Management Studio, hold down SHIFT + Right-Click -> Run as different user. Then type in the username/password of the other user. Now Management Studio will run under their credentials. Any queries you run from that Management Studio window will run as the other user.
There is another way. A linked server object can be configured to connect to the target server using different credentials. So you could setup a linked server object on Server A that points to Server B. When user Y runs a query through the linked server, you can configure it to run under the credentials of user Z. There are some tricky issues here with Kerberos and whatnot, but it is possible.
-
It is possible, if the login you're using has the correct permissions. See EXECUTE AS in BOL.
-
Thanks for the reply. Let me explain what is going on. We are implementing Tableau Server. Tableau is a data discovery/BI tool and server allows sharing of the Tableau workbooks. When developing a workbook, you define a data connection to the SQL databae using windows authentication. When you publish a workbook to Tableau Server, they give you 2 kinds of authentication options: Use the run-as account or impersonate using the Run-as account. The first option is giving the AD login the Tableau Server is running as read permissions to your DB so all queries are run using this account. The second option is giving the AD run-as login account the permissions to impersonate other AD logins defined on the SQL Server. So anyone that runs the workbook on the Tableau server would need to be granted permissions in SQL server and the Run-As account given impersonation permissions on this login. I didn't necessarily like option 1 since this Tableau Server will be used globally and our DB is only for local access so it didn't seem to pass the smell test.
Security is controlled within Tableau Server with AD Groups. I initially was hoping to define these same groups within SQL server but since I have to grant impersonation rights, it looks like I have to define every last login that will run the workbook on the Tableau Server. I had hoped there would be one more authentication type where Tableau Server just passed the AD credentials from the user logged into Tableau Server through to the backend database being referenced in the workbook. This seems to be a better option in my opinion.
-
laker_42 (3/26/2013)
t looks like I have to define every last login that will run the workbook on the Tableau Server.i don't think that's correct;
you should be able to add just a single group, that all the people that will be using this functionality belong to;
For example, we have an AD Group called "Dev" that all our developers belong to; even though there are 20+ lAD users in that group, that's all i need to add to a new server, or to map those logins to a database.
isn't that what you want to do instead?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell, you are correct that I can add a group and that is what we would like to do but from everything I have read, I can't have one AD login impersonate a login that is contained within a defined AD group. Using impersonation is our best authentication option with Tableau Server right now.
-
Sure you can: My AD account does not have a Windows SQL Login, but I am a member of a group that does.
SELECT SUSER_NAME(); returns me (not the group).
go
-- '<domain>\<username>' does not have an SQL login, they are a member of an AD group having a Windows login to SQL Server.
execute as login = '<domain>\<username>';
SELECT SUSER_NAME(); <-- returns <domain>\<username>
revert
SELECT SUSER_NAME(); <-- back to me.
-
That is what I said earlier.....the problem is that SQL Server cannot natively read members out of an AD group confgiured as a login in SQL Server itself.
I am a little confused as to what is really required here that AD users and groups cannot achieve.....
Maybe I should just go and grab a coffee....
-
Same here. I had a quick look at Tableau BOL, I must say, it seems a little obscure to me. Or maybe I also need more coffee.
-
Kevaburg, you were correct in saying it wouldn't work. I was trying to explain the situation to the other 2 posters why I didn't have the luxury of using other methods to accomplish this. I am bound by Tableau Server.
I am putting in a request to add another authentication method where it will pass the AD credentials through from the Tableau Server login to the backend DB. I am not a coder but it doesn't seem to be that difficult of a request. It seems pretty standard these days. Talking with one of their engineers, they did mention that having the each AD login defined in SQL instead of using groups allows for row level security at the user level which Tableau will respect. It just seems strange that they would force you through this model when you aren't using row level security...so we will see where the tool goes.
Thanks for the input everyone. I just wanted to make sure I wasn't missing any other options here.
-
Hi Old Hand,
Most probably your user is sysadmin, or has CONTROL SERVER access. Try without these - it won't work! laker_42 is right!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply