May 3, 2010 at 1:42 pm
Hello,
Using Win2K3, SS2K8 (10.0.2531.0), Standard.
I'm trying to query AD from SS2K8.
I've set up ADSI as a linked server.
I need to be able to get all the AD groups to which the connected user belongs -- i.e., using the user's credentials.
I may be able to use xp_logininfo: how do I / what do I grant permissions on in order to enable it to run in the security context of the connected user?
Thanks,
Paul
May 4, 2010 at 7:44 am
I would recommend using a script outside of SQL Server which runs periodically and populates lookup tables for users, groups, and group memberships. Your AD administrators will thank you. 🙂
K. Brian Kelley
@kbriankelley
May 4, 2010 at 8:24 am
Well, if I can't find a way to do from the server, I'll look into an outside solution.
"Your AD administrators will thank you. :)"
Why do you say that?
May 4, 2010 at 9:04 am
As a former AD architect, here's why I say that:
- One, it can be a nightmare to troubleshoot that linked server connection. It's fraught with issues, which you can see if you Google for all the folks who have struggled with it.
- Two, by using a script and populating the tables, you're making periodic queries to AD which can be planned for. That makes troubleshooting easier than if you're doing ad hoc queries (which can also put greater load on the DCs if you're doing a lot of ad hoc queries... just like doing a lot of ad hoc queries can put a greater load on your SQL Server).
- Three, by using that linked server connection, you're effectively doing a distributed transaction which will almost certainly take longer to complete than if you're doing a query against tables which are likely cached in memory by SQL Server. Therefore, performance will almost certainly be worse and now the AD admin will be on the hook to try and improve performance when there is really very little he/she can do.
K. Brian Kelley
@kbriankelley
May 4, 2010 at 9:08 am
OK, I'm sold. Where could I find such scripts?
May 4, 2010 at 9:14 am
May 4, 2010 at 9:30 am
Thanks Brian!
May 13, 2010 at 6:24 am
So I had one of the developers write a little .net app which queries AD for what I need and outputs it to a .csv in about 20 seconds -- which is ***way*** faster than my old solution.
I set up a job to run every couple of hours which calls the app and BULK INSERTs the results into the SQL Server.
Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply