Querying Windows Active Directory from Sql Server 2000

[Lucky]

Hi guys,
after long long time. i'm back again with another problem. this time i
think the problem is very very interesting and i really need you help
on this.

i'm trying to connect to the Active Directory. for that i've added one
linked server to connect to the active directory. and then query like
this:

adding linked server:

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
Query:
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://imsdom.dk/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')

Error:
OLE DB provider 'ADSDSOObject' reported an error. The provider
indicates that the user did not have the permission to perform the
operation.
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.].
i can't say anything more on this. as even i dont know about this. it
is just a R&D on for very important functionality. if someone has done
anything before like this, please do share with me.

Tools:

windows Active Directory
sql server 2000 sp4
VS.net 2005
thanks,
Lucky

[Stu]

Hey Lucky,

I've never used ODBC to connect to ActiveDirectory before, but the
error indicates that it's a permissions problem. My guess is that the
SQL Server account (the account under shich SQL Server runs) does not
have permission to read the ActiveDirectory tree. Again, just
guessing, but you are probably running SQL Server as a local system
account, which wouldn't have any rights on the network.

Stu

Lucky wrote:

Hi guys,
after long long time. i'm back again with another problem. this time i
think the problem is very very interesting and i really need you help
on this.

i'm trying to connect to the Active Directory. for that i've added one
linked server to connect to the active directory. and then query like
this:

adding linked server:

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
Query:
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://imsdom.dk/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')

Error:
OLE DB provider 'ADSDSOObject' reported an error. The provider
indicates that the user did not have the permission to perform the
operation.
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.].
i can't say anything more on this. as even i dont know about this. it
is just a R&D on for very important functionality. if someone has done
anything before like this, please do share with me.

Tools:

windows Active Directory
sql server 2000 sp4
VS.net 2005
thanks,
Lucky

[Lucky]

Hi Pal,
Thanks for your replay. yeah i'm running the SQL server locally but it
is added to the active directory and i've rights in active directory to
insert,delete,update as well as i'm using the administrator("sa")
account in SQL Server to run the query.

yeah, it seems the problem is with user permission but i don't know how
and where i need to correct them.

any idea on this?

Lucky

Stu wrote:

Hey Lucky,

I've never used ODBC to connect to ActiveDirectory before, but the
error indicates that it's a permissions problem. My guess is that the
SQL Server account (the account under shich SQL Server runs) does not
have permission to read the ActiveDirectory tree. Again, just
guessing, but you are probably running SQL Server as a local system
account, which wouldn't have any rights on the network.

Stu

Lucky wrote:

Hi guys,
after long long time. i'm back again with another problem. this time i
think the problem is very very interesting and i really need you help
on this.

i'm trying to connect to the Active Directory. for that i've added one
linked server to connect to the active directory. and then query like
this:

adding linked server:

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
Query:
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://imsdom.dk/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')

Error:
OLE DB provider 'ADSDSOObject' reported an error. The provider
indicates that the user did not have the permission to perform the
operation.
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.].
i can't say anything more on this. as even i dont know about this. it
is just a R&D on for very important functionality. if someone has done
anything before like this, please do share with me.

Tools:

windows Active Directory
sql server 2000 sp4
VS.net 2005
thanks,
Lucky

[Erland Sommarskog]

Lucky (tu************@gmail.com) writes:

Thanks for your replay. yeah i'm running the SQL server locally but it
is added to the active directory and i've rights in active directory to
insert,delete,update as well as i'm using the administrator("sa")
account in SQL Server to run the query.

yeah, it seems the problem is with user permission but i don't know how
and where i need to correct them.

any idea on this?

I don't know much about Active Directory, but it appears to me that you
failed to understand Stu's reply.

Right-click My Computer, select Manage, find Service. In the list,
find the SQL Server service. Double-click and go to the Log on tab.
What does it say? If it says Local System, you are out of luck, because
Local System does not have network privileges. You need to specify a
domain account that has permissions in the AD.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx