Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to impersonate an AD Login within a AD Group defined in SQL Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 6:46 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 10:02 AM
Points: 1,547, 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!



Post #1434100
Posted Sunday, March 24, 2013 1:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
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.....
Post #1434694
Posted Sunday, March 24, 2013 6:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 10:02 AM
Points: 1,547, 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!



Post #1434718
Posted Monday, March 25, 2013 4:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
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
Post #1434822
Posted Monday, March 25, 2013 9:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:14 AM
Points: 134, Visits: 165
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.
Post #1435241
Posted Tuesday, March 26, 2013 5:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 5:21 AM
Points: 428, Visits: 928
It is possible, if the login you're using has the correct permissions. See EXECUTE AS in BOL.



Post #1435400
Posted Tuesday, March 26, 2013 6:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 10:02 AM
Points: 1,547, 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.



Post #1435422
Posted Tuesday, March 26, 2013 6:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 12,889, Visits: 31,839
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1435428
Posted Tuesday, March 26, 2013 6:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 10:02 AM
Points: 1,547, 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.


Post #1435432
Posted Tuesday, March 26, 2013 6:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 5:21 AM
Points: 428, Visits: 928
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.




Post #1435437
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse