Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Active Directory Query Expand / Collapse
Author
Message
Posted Friday, November 30, 2007 1:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 23, 2012 7:17 AM
Points: 5, 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?
Post #428306
Posted Wednesday, February 27, 2008 11:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 24, 2011 2:06 AM
Points: 1, 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'' ')
Post #461423
Posted Wednesday, June 18, 2008 8:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2008 8:18 AM
Points: 1, 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.
Post #519100
Posted Friday, June 27, 2008 1:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 9:17 AM
Points: 120, Visits: 214
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.
Post #525304
Posted Friday, September 5, 2008 12:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 22, 2012 12:01 PM
Points: 1, Visits: 15
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.
Post #564853
Posted Monday, January 12, 2009 12:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:13 PM
Points: 219, Visits: 716
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
Post #634921
Posted Thursday, April 16, 2009 7:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:53 AM
Points: 51, Visits: 101
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 "<LDAP://DC=1,DC=2,DC=3>;(&(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, '<LDAP://DC=1,DC=2,DC=3>;(&(objectCategory=Person)(objectClass=user));givenName, sn') ON FirstName = givenName AND LastName = sn " &_
"ORDER BY sn DESC"

Thanks
Post #698387
Posted Friday, April 17, 2009 5:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, 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
Post #699281
Posted Tuesday, May 19, 2009 7:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:53 AM
Points: 51, Visits: 101
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
Post #719839
Posted Monday, October 19, 2009 3:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 19, 2009 3:49 AM
Points: 1, 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.
Post #804951
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse