Possible to Grab NT Users List Information through SQL Server 2000? 2

[roccorocks]

You all know about how to add a user in SQL Server, manually. However, I would like to capture the NT user information through a query (which would be tied to a trigger). Is this possible? If you dont know what I am talking about, when you click on Security --> Logins and then right click to create a new Login (then click on the ... next to the name). How do I capture the user information (ie [domain\user {real name} {group}]). ChipH, I know you are one of the Gurus of SQL, any thoughts?

Thanks,

Rocco

 

[mrdenny]

You can create a linked server to your Active directory LDAP servers, however it will only return the first 1000 records when queried (it's a built in AD protection method).

AD comes with tools that make is fairly easy to extract the directory to a text file, then import it into sql via DTS. I use csvde to export from the directory.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[chrissie1]

ChipH, I know you are one of the Gurus of SQL, any thoughts?

It is not recommended to ask for someone special. And allthough chiph is a wise man I think he hangs out in the .net forums.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[roccorocks]

... Thanks for the advice, I will surely take it to heart!

 

[MDXer]

the only information stored within SQL when you add an Windows Domain user is the name and the domain SID (the unique id assigned to the account when it was created). Your best bet is o do as mr denny recomends is use a linked server and join the SID from SQL to AD.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[roccorocks]

Yes, I have been looking into the info that MRDenny has displayed. However, I am running into a brick wall as far as setting up the Linked Server. First, is there suppose to be an LDAP provider in the Provider Name dropdown (I do not see one)? If not for the LDAP provider, which provider is suffice? Second, I have no clue as to how I would set up the Provider string. Have you done this b4 MDXer? I appreciate any help.

Thanks,

Rocco

 

[mrdenny]

The Connection needed isn't listed in the drop down. You have to create the linked server through T/SQL code.

exec sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

That will create a linked server named ADSI.

You can then select off that linked server.

select * 
from openquery
(
ADSI,'SELECT cn, mail, SAMAccountName
FROM ''LDAP://domain.com''
WHERE objectCategory = ''Person'' 
AND objectClass = ''user''
order by mail
'
)

You must specify the columns you want to pull. AD does not support SELECT *.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[roccorocks]

Man Denny, you are the bomb. Thanks a lot! Works like a charm. What book did you find this info in? I would like to research more.

Thanks,

Rocco

 

[mrdenny]

I found it on the web. There's a couple of threads on this site that talk about it.

I haven't seen much info out there on linking to AD directly.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[chrissie1]

you could always use th ADSI SDK from MS but you would need to write a program around it to work.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[roccorocks]

Yes chrissie1, that was more of the information that I originally found. Doing it that way would be fun, however, I dont have the time to be building another application (currently working on many). Thanks for the following.

Thanks,

Rocco

 

[itrmat1]

This looks like a good solution to my current problem where I am trying to extract a user's AD groups.
I am having trouble identifing the column names that will give me this information.

Where is the best place to find this info?

Thanks in advance

 

[mrdenny]

I haven't found a good source for this info.

I have able to find some of the column names in AD here

http://enterprise.linux.com/enterprise/04/12/09/2318244.shtml?tid=102&tid=101&tid=100.

I haven't found a full AD schema as of yet though.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[chiph]

ChipH, I know you are one of the Gurus of SQL, any thoughts?

Sorry, I don't know the answer to this one!

I will point out that the AD schema is extensible -- you can add new columns to objects. I don't know how common this is in the field (I suspect that only the largest organizations have the resources/skills to do this for their AD trees), but depending on your target audience, it might be something to watch out for.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[roccorocks]

itrmat1, here is a good start for the Attribute names. Some of the ones listed do not work correctly (not sure if there is something "special" needed to pull the info).

http://support.microsoft.com/default.aspx?scid=kb;EN-US;257218

Thanks,

Rocco

 

[laic]

Hi, itrmat1
If you have access to your domain controller, you can use
LDP.exe this tool. It's in the "Windows Support Tools".
You will need to install them in your computer.
They are in the Windows CD. (I am using Windows XP)
Use this tool, you will be able to bind to AD then browser the AD under LDP.exe, it will show you the information with the filed name in from of it.
For example:
cn: LastName, FirstName
sn: LastName
c: US
givenName: FirstName
mail: LastName.FirstName@YourCompany.Com

You get the picture . . .
If you do not see the information you need in here, they are probably not maintained. Meaning, if you did not put the information for title in your AD users info, you will not see them in the LDP.exe tool.

Hopefully this can help you.