No Data returned from OPENQUERY to Active Directory

I am trying to query our active directory for user information, but even
though I get the query to work I am getting no data back. I have a linked
server to the active directory from SQL Server 2000 and I am running the
query in the Query Analyzer.
My query is as follows:
select * from openquery(adsi, 'select name, homephone from
''ldap://DC=mydomain,DC=com'' where objectclass = ''User'' ')
This query will complete without error, but no data is returned. I have
seen one other question of this nature here, but I saw no final resolution.
What is wrong?
Alan
--
Message posted via http://www.sqlmonster.comAlan, not sure if it matters, but I usually do the
ADIS/LDAP calls more like this format... Check the
Linked Server also, for correct setup and security. Bruce
SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
((objectClass=User));distinguishedname,homePhone')
where distinguishedname like '%a%'
>--Original Message--
>I am trying to query our active directory for user
information, but even
>though I get the query to work I am getting no data
back. I have a linked
>server to the active directory from SQL Server 2000 and
I am running the
>query in the Query Analyzer.
>My query is as follows:
>select * from openquery(adsi, 'select name, homephone
from
>''ldap://DC=mydomain,DC=com'' where objectclass
= ''User'' ')
>This query will complete without error, but no data is
returned. I have
>seen one other question of this nature here, but I saw
no final resolution.
>What is wrong?
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>|||Bruce,
thanks for the response, but I tried what you suggested and still the query
returned no data. It completed without error just no data.
Thanks,
Alan
--
Message posted via http://www.sqlmonster.com|||Alan, not sure why youre getting no data back then.
You're sure of the domain name? there's no special
security on your AD to prevent you from accessing? The
linked sever is setup correctly? like so..'
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
go
Are you sure the data sits in the User ObjectClass?
Might be somewhere else, check it.
There was a max 1000 row limit by default and we had to
have that kicked up to get more then 1000 rows returned.
These links talk about that...
http://support.novell.com/cgi-
bin/search/searchtid.cgi?/10081596.htm
http://www.ldapadministrator.com/forum/viewtopic.php?t=14
>--Original Message--
>Bruce,
>thanks for the response, but I tried what you suggested
and still the query
>returned no data. It completed without error just no
data.
>Thanks,
>Alan
>--
>Message posted via http://www.sqlmonster.com
>.
>|||Bruce,
I ran an export of the data in the user objects using ldifde and got back
exactly what I expected, but the same query in SQL Server Query Analyzer
comes back empty.
I guess it could be something with the link, but it was set up according to
the instructions I found on the web. The link was created in Enterprise
manager, but using the same settings that you specified in the
sp_addlinkedserver statement. It could also be a user security issue, but
I have tried logining in as the admin, SQL Sever admin and myself with no
data back.
Still playing around with it.
Alan
--
Message posted via http://www.sqlmonster.com|||Finally, thanks Bruce. I was reading your posting and I was finally able to
pull data.
I'm trying to pull just users that are a member of a certain group, how can
I do that?
This is what I have but not working; however, I was able to used your query
just to get a start and that was a big help.
SELECT *
FROM OPENQUERY( ADSI,
'SELECT cn
FROM ''LDAP://internal/OU=Account
Executives,DC=internal,DC=homequest,DC=com''
WHERE objectClass = ''users''')
GO
"Bruce de Freitas" wrote:
> Alan, not sure if it matters, but I usually do the
> ADIS/LDAP calls more like this format... Check the
> Linked Server also, for correct setup and security. Bruce
>
> SELECT * FROM OPENQUERY( ADSI,'<LDAP://mydomain>;
> ((objectClass=User));distinguishedname,homePhone')
> where distinguishedname like '%a%'
>
> >--Original Message--
> >I am trying to query our active directory for user
> information, but even
> >though I get the query to work I am getting no data
> back. I have a linked
> >server to the active directory from SQL Server 2000 and
> I am running the
> >query in the Query Analyzer.
> >
> >My query is as follows:
> >
> >select * from openquery(adsi, 'select name, homephone
> from
> >''ldap://DC=mydomain,DC=com'' where objectclass
> = ''User'' ')
> >
> >This query will complete without error, but no data is
> returned. I have
> >seen one other question of this nature here, but I saw
> no final resolution.
> >What is wrong?
> >
> >Alan
> >
> >--
> >Message posted via http://www.sqlmonster.com
> >.
> >
>|||Thank you, after playing with it for awhile I was able to get it to work.
Do you know if there is a way to extract users' contact folder from the
server-level?
"Bruce de Freitas" wrote:
> Sonya, this query would list all the GROUPs.
> SELECT * FROM OPENQUERY( adsi,'<ldap://mydomain>;
> (&(objectCategory=GROUP)); distinguishedname,CN')
>
> You'd then have to join all the Users to what groups they are in, if
> that is what youre trying to do... Bruce
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>sql

No Client Protocols Enabled yet I can still get a DSN connection to SQL Server 2005

Perplexed...Why am I able to get a connection to my SQL Server from an
Access 2003 database via DSN linked SQL server db table without a
common protocol between the client and server? Could it be that the
client does have TCP/IP or Named Pipes enabled but they are just not
showing up in the cliconfg.exe utility as such? Help!Clients have always TCP/IP and/or Named Pipes enabled. This setting is for
the server, not for the client tools.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"lacdn76" <dakarpiak@.yahoo.com> wrote in message
news:1175526801.931586.126790@.y66g2000hsf.googlegroups.com...
> Perplexed...Why am I able to get a connection to my SQL Server from an
> Access 2003 database via DSN linked SQL server db table without a
> common protocol between the client and server? Could it be that the
> client does have TCP/IP or Named Pipes enabled but they are just not
> showing up in the cliconfg.exe utility as such? Help!
>

No Client Protocols Enabled yet I can still get a DSN connection to SQL Server 2005

Perplexed...Why am I able to get a connection to my SQL Server from an
Access 2003 database via DSN linked SQL server db table without a
common protocol between the client and server? Could it be that the
client does have TCP/IP or Named Pipes enabled but they are just not
showing up in the cliconfg.exe utility as such? Help!
Clients have always TCP/IP and/or Named Pipes enabled. This setting is for
the server, not for the client tools.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"lacdn76" <dakarpiak@.yahoo.com> wrote in message
news:1175526801.931586.126790@.y66g2000hsf.googlegr oups.com...
> Perplexed...Why am I able to get a connection to my SQL Server from an
> Access 2003 database via DSN linked SQL server db table without a
> common protocol between the client and server? Could it be that the
> client does have TCP/IP or Named Pipes enabled but they are just not
> showing up in the cliconfg.exe utility as such? Help!
>

NH: Best Practices Approach - call stored proc - or run it via linked server?

what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.

i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?

thanks.

Jeff

anyone?|||

One obvious thing to keep in mind is the performance. Depending on your network the executiion time could be different.

Now I am not sure if I understand your question. If you do not have permission to create the stored proc on the original server you would still need access to the underlying tables if you want to access them through server B, irrespective of whether its via stored proc or directly T-SQL.

|||

well typically I associate using a linked server to pull data from something using openquery such as ibm as400 so my question is do i get access to put the stored proc on the remote sql server (not one of my groups sql servers), or do i ask for setting up a linked server to it and execute the stored proc from my local sql server using the linked server approach.

either way?
get access to put the stored proc right on the sql server and call it in c#?
use the linked server and call it in c#

Just looking for some reason why one way may be better than another, primarily performance, and reducing putting in another possible point of failure.

thanks,

JB

|||

info@.learnbartending.com:

Just looking for some reason why one way may be better than another, primarily performance, and reducing putting in another possible point of failure.

Those are exactly the reasons why you might want to put the proc directly where the data is.