Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Active Directory Query


Active Directory Query

Author
Message
FroggEye
FroggEye
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 32
I too get the same error you decribed. What I was told is the service SQL Server (MSSQLSERVER) cannot be run as a local user or local network. It has to be set up as a domain admin user within Active Directory. Although I have tried this and it doesn't work either. I am getting a bit agitated with this error as I have been trying to deal with this problem for close to 2 weeks now.

Any ideas on where to look toward next?
ZMAung
ZMAung
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 47
Actually , you don't even need to run sp_addLinkedServer. What you need to do is
1) your SQL server must run under the account which must have access right to the active directory.
2) you must login to active directory to run this query.

SELECT *
FROM OPENROWSET('ADSDSOObject', 'adsdatasource;',
'SELECT mail, displayName,givenname,distinguishedName, SAMAccountName, CN
FROM ''LDAP://ServerName/cn=users, DC=DOMAINName, DC=com'' ')
mark.smith.ctr
mark.smith.ctr
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
Ensure the following services are enabled and started and running under the same service account:

SQL Server
SQL Server Agent
SQL Server Analysis Services
SQL Browser
SQL FullText Search
SQL Reporting Services

We were able to query AD while logged onto the SQL Server (both locally and remotely) but not from a client computer using Management Studio. Once we set all the above services to run under the same service account, we were able to query AD from a remote computer.
holidasa
holidasa
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 232
I was able to retrieve data using a Linked Server if I specified the security context to use to access the Active Directory server. (The server running SQL Server is not a member of any domain and uses a local computer account to start the services.)

Here is a sample of the Linked Server Definition (SQL Server 2005 SP2):

/****** Object: LinkedServer [ADSI] Script Date: 06/27/2008 13:50:37 ******/
IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ADSI')
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'ADSDSOObject',
@provider=N'ADsDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,
@rmtuser=N'Domain\User',@rmtpassword='password'
END
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'



Then, I used the following query to retrieve the results:

SELECT * FROM OPENQUERY
(
ADSI,'SELECT name
FROM ''LDAP://server''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')



I hope this helps.
doug-602931
doug-602931
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 18
In most cases this error has something to do with the permissions of the credentials you are using to query the AD. Make sure these credentials are not restricted from searching/reading the active directory.
Rem70Rem
Rem70Rem
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 745
With SQL2005, how come when I try to execute

SELECT * FROM OPENQUERY(ADSI,'SELECT name
FROM ''LDAP://server''
WHERE objectCategory = ''Person'' AND objectClass = ''user''

I am getting
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "adsi" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name
FROM ''LDAP://server''
WHERE objectCategory = ''Person'' AND objectClass = ''user''".

But when I used OPENROWSET, it works?

I will like to keep using the OPENQUERY, snce I dont want to modify all my code.

Thanks
lanky_doodle
lanky_doodle
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 117
Sorry to dig up this old thread, but i've come across this problem.

The route pcs take is:

Client PC -> Web Server (IIS 6.0) -> SQL Server 2005 -> Domain Controllers

I have run the sp_addlinkedserver command (and tried manually creating). This seems to work fine when run from the server, but when using the exact same query from an ASP page hosted on the web server, I get the following error message:

Microsoft OLE DB Provider for SQL Server error '80040e14' 
An error occurred while preparing the query ";(&(objectCategory=Person)(objectClass=user));givenName, sn" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

/html/moduleADUsers.inc, line 51


I'm sure it's permissions, but everything i've tried has not fixed it!!

Any ideas; this is the code i'm using:

qryAD = "SELECT givenName, sn, FirstName, LastName FROM tbl_StaffDetails " &_
"INNER JOIN OPENQUERY(ADSI, ';(&(objectCategory=Person)(objectClass=user));givenName, sn') ON FirstName = givenName AND LastName = sn " &_
"ORDER BY sn DESC"


Thanks
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 494
Vortex (9/27/2007)
Folks,

I have the same problem here, but the solutions provided solved the problem... while running the query on the server.

select * FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT cn, mail, co, distinguishedName, displayName
FROM ''LDAP://myDomain.lan'' where objectClass = ''User'' ')


If i try to run the same query using SSMS on any desktop accessing this server, I get this error:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM 'LDAP://myDomain.lan' where objectClass = 'User' " for execution against OLE DB provider "ADSDSOObject" for linked server "(null)".


If I try the other solution

select * from openquery
(ADSI,'SELECT name
FROM ''LDAP://myDomain.lan''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')


I get the same weird error people complain about:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name
FROM 'LDAP://myDomain.lan'
WHERE objectCategory = 'Person' AND objectClass = 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".


Any clues? Thanx!



You need to provide the domain context.

You have LDAP://myDomain.lan

It's going to be something like:

LDAP://myDomain.lan/dc.myDomain,dc=lan

If you want to be REALLY tricky, you can use a serverless bind, which is:

LDAP://dc=myDomain,dc=lan

This will pick the closest domain controller.

Random Technical Stuff
lanky_doodle
lanky_doodle
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 117
OK, my problem is solved; it was a double hop issue between the various devices. Chaning the SQL service account to use Kerberos fixed it.

Another problem; is it possible to use AS in OPENQUERY statements, such as

SELECT initials AS StaffInitials

As when trying I get OPENQUERY error messages.

Thanks
techtiger4
techtiger4
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
You can query Active Directory by using Chily Active Directory Query tool. It is a freeware that allows you to query Active Directory.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search