Comments on Aaquagua!: Querying Active Directory Through SQL Server

when I say "SELECT * FROM LDAP://svrname"...

2011-09-22T04:44:56.879-04:00

when I say "SELECT * FROM LDAP://svrname", it returns only AdsPath. How can I see other fields like cn, c, sn, title and others ?

Hi Boyan, You saved me by mentioning that ldap is...

2011-07-05T12:29:49.514-04:00

Hi Boyan,

You saved me by mentioning that ldap is case sensitive, I was scratching my head for two days on an error that I was recieving. As soon as I changed ldap to "LDAP" it worked like magic. Thanks a lot!
My LDAP queries work only when I am on that machine, where as if I try running the same query through sql management studio connecting from some other machine, it doesnot run. Can you please tell me why this is happening and how to resolve this?

Thanks,
TP

I could try this on SQL Server 2005 snd 2008 and s...

2011-06-20T14:16:45.825-04:00

I could try this on SQL Server 2005 snd 2008 and see what I figure out.

Would you have any interest in updating this for M...

2011-06-20T14:12:17.867-04:00

Would you have any interest in updating this for MSSQL 2005? You provide details that other places do not, but the process changed for 2005, so I'm still having issues.

You rock mate. This has been an irritating one for...

2011-03-03T19:33:12.419-05:00

You rock mate.
This has been an irritating one for me for some time but finally I am able to query my LDAP through SQL.

Thanks a zillion

I don't know a way to get past 1000 records. I...

2010-07-08T18:08:01.686-04:00

I don't know a way to get past 1000 records. I think that's the limit of this method.

Great blog, thanks couple of questions though my ...

2010-07-03T13:48:26.435-04:00

Great blog, thanks couple of questions though
my query only return 1000 rows, how to deal with the 1000 record limit that AD imposes on a return ?
how do we collect lastpwdset value from AD using same query ?
Appriciate any help

Sorry mate, not sure why you are only getting 256 ...

2010-01-26T16:27:57.669-05:00

Sorry mate, not sure why you are only getting 256 characters. Never tried to use that field. Google is your friend I guess.

Hi - I'm querying AD from SQL 2000 and am tryi...

2010-01-26T06:08:24.600-05:00

Hi - I'm querying AD from SQL 2000 and am trying to extract the 'Info' field which I believe is the user notes field. However, I'm having the problem where only the first 256 characters are coming across...is there any way round this? None of the convert/format functions appear to work...

@Koningin Juliana: The user accessing AD must be ...

2010-01-13T19:30:42.979-05:00

@Koningin Juliana:

The user accessing AD must be an AD user. You cannot use a SqlServer account to access AD.

If rmtuser is not an AD user then it won't work. If rmtuser is an AD user try rmtuser@yourdomain.com or YOURDOMAIN\rmtuser.

You may need to set up a new account for this.

I'm guessing you're using Windows Authentication to log into SqlServer which would explain why it.

I'm pretty sure you can't create views in AD, either. You'd have to create a view in your local database over a query from the AD database.

Hello Boyan, Thank you for the clear overview. I ...

2010-01-13T07:20:02.267-05:00

Hello Boyan,

Thank you for the clear overview.
I am trying to get this to work with AD LDS, but I am running in to the error "The provider indicates that the user did not have the permission to perform the operation" almost every time. To only way I can get it to work is by configuring the linked server to use the login's current security context (I think this is the same as setting "useself" to TRUE in the procedure sp_addlinkedsrvlogin). If I configure the same credentials (the credential with which I am logged in to SQL Server) for the linked server (rmtuser and rmtpassword) the connection fails. I don't understand this at all, do you?
My goal is to have an internal SQL Server user create a view on the AD LDS.

Thanks for your reply. I am testing by running s...

2009-08-20T15:32:15.527-04:00

Thanks for your reply. I am testing by running

select * from openquery(LS, 'select * from ''LDAP://dc=Domain,dc=com''')

but get the message:
OLE DB error trace [OLE/DB Provider 'ADsDSOObject' ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that the user did not have the permission to perform the operation.].
Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'ADsDSOObject' reported an error. The provider indicates that the user did not have the permission to perform the operation.

That prompts me to ask this question:
Can the Remote User account be the same as the Local Login?

Thanks so much, again.

"AllowInProcess" should be "dword&q...

2009-08-20T14:41:43.793-04:00

"AllowInProcess" should be "dword" type with a valud of "00000001".

To configure DC for mail.yahoo.com it would be: dc=yahoo,dc=com

Hello, Thanks for the article on the useful featur...

2009-08-20T13:52:29.539-04:00

Hello, Thanks for the article on the useful feature. I hope to make it work.

can you please explain if the
"AllowInProcess" should be "dword:00000001" or just "00000001". Is it Hexa or just decimal?

Also I am not sure how to configure the 'dc' part of the connection string: I tried the foll:
dc=xxx,dc=yyy,dc=zzz (where the company domain is xxx.yyy.zzz example: mail.yahoo.com)
dc=MyDomain
dc=ldap-server
dc=Domain,dc=com
etc

Can you please guide me where I can get the exact setting for this.

Thanks

Yes, you can use an OU in the LDAP query.

2009-07-30T13:11:30.596-04:00

Yes, you can use an OU in the LDAP query.

Hi, i have a question....can i specify an OU in th...

2009-07-30T10:44:32.848-04:00

Hi, i have a question....can i specify an OU in the query?

regards

Was wonder how to deal with the 1000 record limit ...

2009-02-20T16:41:00.000-05:00

Was wonder how to deal with the 1000 record limit that AD imposes on a return and is hadled by a paging object in vb?