February 7, 2008 at 2:50 pm
I need to create a sproc/UDF that takes the name of a Windows account as input - - and spits out all Windows security groups that this account is a member of.
The opposite of that is given by the well-known xp_logininfo extended sproc:
xp_logininfo ' ', 'members';
I think the CLR is the best way to access Active-Directory info, so I am leaning towards creating a CLR object.
I know the System.DirectoryServices namespace is where the .NET framework methods are found.
But not much more! 🙂
Can someone give me some pointers on this?
thanks!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 7, 2008 at 8:19 pm
I may not be able to check on this topic for the next 10 days or so due to something unexpected.
Pls post any feedback here, and I will get to it when I come back.
Thank you
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 9, 2008 at 12:14 pm
The BOL topic Getting Started with CLR Integration is a good place to begin. That will take you through editing, compiling and deploying a CLR stored procedure.
As far as using System.DirectoryServices the attached demonstration walks through the entire process of preparing the database to deploying the assembly. It's about 5 minutes long.
Hope this helps.
Edit: Modified the attached video (SWF file) to improve the image quality and reduce the file size.
February 13, 2008 at 12:43 pm
Thanks Todd! This is great!
I will not be able to try the download as yet, but I'm taking a note of it so I can try it when I'm back to work.
Thanks again!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 15, 2008 at 1:52 am
Very nice demo indeed ! 😎
Keep in mind CLR is a double edged knife.
You can easily cut your finger with it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2008 at 3:17 am
ALZDBA (2/15/2008)
Very nice demo indeed ! 😎Keep in mind CLR is a double edged knife.
You can easily cut your finger with it.
Sometimes, cutting a finger is what it takes to learn, as long as it is not the whole hand! 🙂
But thanks for the heads up.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 15, 2008 at 4:36 am
Just keep in mind to have your first aid kit available. 😀
Also there are # of articles/forumQ regarding CLR at ssc.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2008 at 5:33 am
ALZDBA (2/15/2008)
Just keep in mind to have your first aid kit available. 😀Also there are # of articles/forumQ regarding CLR at ssc.
I have done some ASP.NET development in the past. That's where I got the CLR bug.
I'm glad there are so many more features now than in sql 2000. Makes for a more interesting work experience, and the challenge of knowing when to use each tool just adds the right spice to the mix.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 15, 2008 at 7:32 am
Just for giggles - have you considered using LDAP queries instead?
LDAP actually has an OLE DB -type provider, so you can actually set your Active Directory as some kind of linked server you can run queries against. I haven't done it directly - but a colleague has, and it spits stuff out from direct queries against AD. (the queries are not quite SQL from what I recall, but the results seem to come out correctly)
Of course - I don't want to stop you from playing with CLR - but I think the OLE DB provider for Microsoft Directory Services is a much more direct way to get where you want to go.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2008 at 9:05 am
There are at least three limitations that I'm aware by using the OLEDB Provider for Microsoft Directory Services.
1. It cannot read multi-valued attributes from AD, such as MemberOf.
2. It cannot modify attributes in AD.
3. Requires special handling for the query.
A linked server using that provider would be a dead-end in this situation.
However, those limitations can be addressed with a CLR stored procedure or function.
February 15, 2008 at 1:23 pm
Todd Engen (2/15/2008)
There are at least three limitations that I'm aware by using the OLEDB Provider for Microsoft Directory Services.1. It cannot read multi-valued attributes from AD, such as MemberOf.
2. It cannot modify attributes in AD.
3. Requires special handling for the query.
A linked server using that provider would be a dead-end in this situation.
However, those limitations can be addressed with a CLR stored procedure or function.
Well - no argument on the modifying AD from within SQL server. Of course - that wasn't what was requested either. Actually I wouldn't modify AD using this or .NET (the full version or SQLCLR) with a ten-foot pole (if nothing else because My network admin would first turn off my network port, and then proceed to revoke all network rights assigned to me if I was that foolish).
As to this actual issue, I just bounced the question to my (former) colleague. The answer was: "query LDAP returning the group with the user as a member, rather than trying to return each of the multi-values in the memberOf list of a user".
Again - no arugment that the query language is somewhat strange.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 17, 2008 at 8:35 am
LDAP actually has an OLE DB -type provider, so you can actually set your Active Directory as some kind of linked server you can run queries against.
This link seems quite relevant:
http://blogs.msdn.com/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 13, 2008 at 12:10 pm
Todd Engen (2/9/2008)
The BOL topic Getting Started with CLR Integration is a good place to begin. That will take you through editing, compiling and deploying a CLR stored procedure.As far as using System.DirectoryServices the attached demonstration walks through the entire process of preparing the database to deploying the assembly. It's about 5 minutes long.
Hope this helps.
Edit: Modified the attached video (SWF file) to improve the image quality and reduce the file size.
Todd, thanks again for the attached flash video, I was finally able to view it today.
It's exactly what I need!
I have one more question though that I'm hoping I could get some help on. I'm kinda lost on defining my DirectoryEntry object. What do I place in the constructor for the string? I looked up BOL but it is not very helpful. In your example the code reads like this:
Dim objDirEntry As DirectoryEntry = New DirectoryEntry("LDAP://myServer.myDomain.com/dc=mydomain,dc=com")
How can I find out what my Active Directory provider is and how to construct the string above?
Any help is much appreciated.
PS. In the Active Directory Management dialog, beside the Active Directory Users and Computers node, I can see the server name, eg. server.domain.com. I presume this would have to be placed in for the string above, but any more info on how to do this would be appreciated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 13, 2008 at 12:41 pm
Marios,
Glad it helped.
myServer is the name of any domain controller for myDomain.com. Ideally you'll want to pick one closest.
Running 'dsquery server' from a command line should get you a list of domain controllers too. I think you're seeing a DC in the Active Directory Managment MMC though.
There are other ways to specify that path too, for example if you didn't want to specify a server this should work as well.
DirectoryEntry("LDAP://DC=myDomain,DC=com")
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply