Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, August 4, 2006 7:47 PM
Hello I am trying to run a query via tsql against ad. Below is the error I am getting. I have read the http://msdn2.microsoft.com/en-US/library/ms190803.aspx and changed the domain but still having issues. Any help would be appreciated.
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
GO
SELECT *
FROM OPENQUERY( ADSI,
'SELECT Name, SN, ST
FROM ''LDAP://ADSISrv/ OU=Users,DC=XXXXX,DC=LOCAL''
WHERE objectCategory = ''Person'' AND
objectClass = ''user''')
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT Name, SN, ST
FROM 'LDAP://ADSISrv/ OU=Users,DC=XXXXX,DC=LOCAL'
WHERE objectCategory = 'Person' AND
objectClass = 'user'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
Wednesday, March 9, 2011 3:11 PM ✅Answered | 4 votes
I had the same issue like you, if you run in your personal computer is probably that you not experiment the same error but if you move to any other server that need the right permission to any user into the Active Directory.
first execute your query:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject'
, 'adsdatasource'
GO
Like daw21 explain before follow the detail steps:
Second step go to the SqlServer management Studio and edit manually the Linkserver name ADSI:
go to Server Objects/Linked Servers/ and right click on ADSI select the option Properties and then go to Security.
Select the option : Be made using this security context:
and there fill the user and Password about the valid user with permission into Active Directory, not forget before the domain\user.
Wednesday, March 23, 2011 1:37 PM ✅Answered | 2 votes
Took me forever to get this working ...
First you have to make sure that Ad Hoc Distributed Queries is enabled ...
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Second you have to make sure that you specify a security context that has access to AD INCLUDING the DOMAIN
Third you should put a row limit (e.g. SELECT TOP 100) in to avoid the issue where you hit the 1000 row limit so
SELECT
TOP 100 *
FROM
OpenQuery(ADSI, 'SELECT sAMAccountName FROM ''LDAP://DC=core,DC=acllab,DC=net'' WHERE objectCategory=''user'' ')
Friday, August 4, 2006 7:58 PM
Check if the linked server (ADSI) allow remote accessing.
Friday, August 4, 2006 9:16 PM
I got it to work on the local database server. It didn't seem to like the OU. Once I removed it the query completed. However I still get the message if I try and run the view from a remote computer using Mgmt Studio is there something I have to setup on the clients?
SELECT * FROM vcnet.dbo.viewADContacts--gives errors from workstations but not if run from the server itself
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT sAMAccountname,givenname,sn,displayname,useraccountcontrol,CN, manager, mobile , mail, objectCategory,
department,
telephoneNumber,
facsimileTelephoneNumber
FROM 'LDAP://DC=XXXXX,DC=local'
WHERE objectCategory='person' AND objectClass='user'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
Friday, August 18, 2006 11:39 AM
Did you ever get a resolution to this? I am now having the same issue - query runs fine on the local SQL2k5 server but not on a remote server running Visual Dev.
Wednesday, August 23, 2006 10:11 PM
I'm having the same issue - query runs in management studio, but not elsewhere. Any idea why?
Thursday, August 24, 2006 6:54 PM
The domain name is required in the Security settings for the remote login - I should have known that. So in the Security settings I selected Be made using this security context, entered Remote login: domain\user; With password: password.
Thursday, February 28, 2008 6:50 AM | 1 vote
Actually, you don't need to run sp_addlinkedserver.
What you should do is
1) Make sure your SQL Server Service is running under the account which has access right to Active Directory.
2) You must log in 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'' ')
Tuesday, August 16, 2011 10:32 AM
If you excute the query in remote computer, you can try Security for linked Server: right click on the linked Server (ADSI) select properites, select Security tab, choose "Be made using this security context" and enter your remote login and password.
Monday, August 29, 2011 4:44 PM | 1 vote
Enabled adhoc distributed query, added security context to ADSI (with domain name). The ADSI connection test is showing as successful but still getting the same error in trying to run the following:
SELECT
TOP 100 * FROM
O
penQuery
(ADSI, 'SELECT sAMAccountName FROM ''LDAP://OU=XX,OU=XX,DC=XX,DC=XX'' WHERE objectClass=''person'' ')
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT sAMAccountName FROM 'LDAP://OU=Flight Services,OU=FS21users,DC=emulator,DC=com' WHERE objectClass='person' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
Any help will be appreciated, running on SQL server 2008.
Tuesday, October 13, 2015 6:28 AM
with my sql2012 i've used such ladp syntax to get success:
SELECT * FROM OPENQUERY
( ADSI,'SELECT mail,cn,adspath
FROM ''LDAP://my.dnsdomain.local**/OU=X,DC=my,DC=dnsdomain,DC=local**'''
)
Monday, December 14, 2015 7:55 PM
In had the same error, I solved it by changing de domain name by the computer FQN of the domain controler:
SELECT top 100 * FROM OpenQuery (
ADSI,
'SELECT displayName, telephoneNumber, mail, mobile
FROM ''LDAP://DomainControlerComputerName.MyDomain.com/OU=MyOU,DC=MyDomain,DC=com''
WHERE objectClass = ''User''
') AS tblADSI
order BY displayname
Darío León
Tuesday, December 20, 2016 8:50 PM
This worked for me = of note = the DC was case-sensitive (worked with CAPS only)
Friday, January 5, 2018 7:57 PM
"Test connection" succeeds on my linked server. So I assume that the security context I provided has the required access. But my below Open Query still fails with the same error.
I successfully enabled Distributed queries using the below command.
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
But my below "Openquery" still fails.
select top 100 * from openquery
(
LS_LDAP,'SELECT name
FROM ''LDAP://server/OU=..,DC=...''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')
Error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name
FROM 'LDAP://server/OU=..,DC=...'
WHERE objectClass = 'user'
" for execution against OLE DB provider "ADsDSOObject" for linked server "LS_LDAP".
Friday, January 5, 2018 8:03 PM
Juentity, did you get it fixed? I'm facing the same issue.
Friday, January 5, 2018 8:06 PM
Replying to a decade old thread about an obsolete version of sql server is not an effective way to get help. You should start your own thread and provide all of the information that has been requested of OP. And given the difficulties, you should consider that tsql is not particularly suited to this task and that you should reconsider your goal and your decision to choose this approach.
Friday, January 5, 2018 10:23 PM
Thanks for the response scott. I've started the below thread.
We need to get this done using T-SQL as we have downstream dependencies.