Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to impersonate an AD Login within a AD Group defined in SQL


How to impersonate an AD Login within a AD Group defined in SQL

Author
Message
laker_42
laker_42
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 Visits: 275
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!



kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 Visits: 912
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.....
laker_42
laker_42
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 Visits: 275
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!



kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 Visits: 912
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
Chadwick-788357
Chadwick-788357
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 170
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.
schleep
schleep
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 1287
It is possible, if the login you're using has the correct permissions. See EXECUTE AS in BOL.



laker_42
laker_42
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 Visits: 275
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.



Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38926
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!

laker_42
laker_42
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 Visits: 275
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.



schleep
schleep
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 1287
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search