January 12, 2009 at 12:05 pm
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
April 16, 2009 at 7:15 am
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
April 17, 2009 at 5:18 am
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.
May 19, 2009 at 7:59 am
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
October 19, 2009 at 3:50 am
You can query Active Directory by using Chily Active Directory Query tool. It is a freeware that allows you to query Active Directory.
December 14, 2010 at 1:50 am
when i run query on SQL server computer not from other computer by using SSMS then everything was working fine.
January 3, 2011 at 6:15 am
me tooooooo..
Folks.. please check your server editions?
bcoz, i executed the same query in Enterprise Edition works fine but not in Standard Edition. But i am not sure that should be the root cause.
Please check and update your comments..!!!
January 3, 2011 at 6:17 am
Did anyone check your Editions?
i had problem in standard but not in enterprise. but not sure this should be the root cuase..!
Update your comments?
January 4, 2011 at 2:12 pm
Hello, I have a similar problem. I implemented the suggested solution that queries AD without using a linked server and it worked great on my instance of SQL 2008 Enterprise running on Windows 2003. However, when I tried to move it over to an instance of SQL 2008 Enterprise running on any flavor of Windows 2008 it fails.
Actually to avoid the 1000 row limit of AD (which I have upped to 3000 via ADSIEdit), I split my query into two parts and then run a "Union" query to combine the results. Each query brings back around 400 rows and then union into around 800 results.
When I try to run one of the initial queries via the SSMS and "Select Top 1000 rows" on a SQL server that is running Windows 2003 it works, but do the same on a server running any flavor of Windows 2008 or 2008 R2 (32 or 64bit) and it displays the results then displays the message:
"Msg 7330, Level 16, State 2, Line 2" "Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "(null)"."
Both of the initial queries display that same message and if you try to run the "Union" query, it returns no results.
One of the queries that I am running (the other gets the rest of the alphabet and then the union query merges the two) is:
SELECT TOP (100) PERCENT cn, employeeID, samAccountName, mail, givenName, sn
FROM OPENROWSET('ADSDSOObject', 'adsdatasource;',
'SELECT sn, givenName, mail, samAccountName, employeeID, cn
FROM ''LDAP://Insert FQDN here'' where objectCategory = ''Person'' and objectClass = ''User'' ')
AS derivedtbl_1
WHERE (employeeID IS NOT NULL) AND (samAccountName LIKE 'a%' OR
samAccountName LIKE 'b%' OR
samAccountName LIKE 'c%' OR
samAccountName LIKE 'd%' OR
samAccountName LIKE 'e%' OR
samAccountName LIKE 'f%' OR
samAccountName LIKE 'g%' OR
samAccountName LIKE 'h%' OR
samAccountName LIKE 'i%' OR
samAccountName LIKE 'j%')
ORDER BY cn
Naturally the "insert FQDN here" is really populated with our domain name in a format like "search.microsoft.com".
If I modify the "Select Top (100) Percent" to be equal to the actual number of rows that should return (or lower), then the query is successful on a Windows 2008 server. However the number of users is constantly changing and I do not want to miss anyone, so I want it to find all and return the proper values without me having to specify a number to return.
All of my SQL services are running as a domain user with rights to query AD.
Any ideas why this works on a Windows 2003 server but not a Windows 2008 server? We want to retire our Windows 2003 SQL server, but need the functionality provided by the Active Directory query.
FYI, no linked server was necessary on the Windows 2003 server.
Thanks,
Jeremy Hawks
Systems Administrator
Green River Community College
January 5, 2011 at 1:44 am
Hi mgalawin,
try
SELECT cn, employeeID, samAccountName, mail, givenName, sn
FROM OPENROWSET('ADSDSOObject', 'adsdatasource;',
'SELECT sn, givenName, mail, samAccountName, employeeID, cn
FROM ''LDAP://source''
where objectCategory = ''Person'' and objectClass = ''User''
and employeeID = ''*'' and samAccountName < ''k'' ')
ORDER BY cn
Best regards
karl
January 12, 2011 at 11:50 pm
Thanks for trying to help. For some reason without making any changes, it started working. So that code is fine.
December 7, 2012 at 3:07 pm
This worked for me! Thanks a bunch.
Robb
December 8, 2012 at 7:23 pm
I was the original poster of this thread and with help from others here, I have had continued success. I have had success with querying AD on SQL 2000 Standard, all the way up to 2008 R2 32/64bit (with the same linked server config). I find there is a 2000 record limit so I cannot get all of the AD accounts from Exchange in a single result set. Here is an outline of what I do. Some overhead, but I have not gone back and optimized. Hopefully it may help someone out. *You may need to fill in some blanks in code. Also, truncate tables for a reload.
---TABLES
CREATE TABLE [dbo].[tblActiveDirectory](
[ActiveDirectoryId] [smallint] IDENTITY(1,1) NOT NULL,
[Dn] [varchar](200) NOT NULL,
[RecType] [varchar](1) NULL,
[DisplayName] [varchar](100) NULL,
[Sam] [varchar](75) NULL,
[Mail] [varchar](150) NULL,
[Department] [varchar](150) NULL)
CREATE TABLE [dbo].[tblActiveDirectoryMembers](
[DnChild] [varchar](200) NOT NULL,
[DnParent] [varchar](200) NOT NULL)
SPROCS:
-----Insert OU's:
-----spAdInsertOus
-- run first
INSERT INTO dbo.tblActiveDirectory
(
DN,
RecType,
DisplayName
)
SELECT DistinguishedName,
'O',
Name
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT name, displayName, distinguishedName
FROM ''LDAP://DC=corp,DC=mydomain,DC=com''
WHERE objectClass = ''organizationalUnit'' ')
-----Insert Groups:
-----spInsertGroups
-- run 2nd
INSERT INTO dbo.tblActiveDirectory
(
DN,
RecType,
DisplayName,
)
SELECT DistinguishedName,
'G',
Name,
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT Name, DistinguishedName, Mail
FROM ''LDAP://DC=corp,DC=mydomain,DC=com''
WHERE objectClass = ''Group'' ')
--------Insert Users (this is not optimized but it avoids the 1000/2000k limit on my system)
-- you may need to delete records from these results
--------spAdInsertusers
-- run 3rd
INSERT INTO dbo.tblActiveDirectory
(
Dn,
RecType,
DisplayName,
SAM,
)
SELECT DistinguishedName, 'U' [RecType], DisplayName, SamAccountName, mail
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT mail, displayName, samAccountName, distinguishedName
FROM ''LDAP://DC=corp,DC=mydomain,DC=com''
WHERE objectClass = ''User''
AND objectCategory = ''Person''
AND userAccountControl <> ''514''
AND displayName < ''h''')
UNION ALL
SELECT distinguishedName, 'U' [RecType], displayName, samAccountName, mail
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT mail, displayName, samAccountName, distinguishedName
FROM ''LDAP://DC=corp,DC=mydomain,DC=com''
WHERE objectClass = ''User''
AND objectCategory = ''Person''
AND userAccountControl <> ''514''
AND displayName >= ''h''
AND displayName <= ''p''')
UNION ALL
SELECT distinguishedName, 'U' [RecType], displayName, samAccountName, mail
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT mail, displayName, samAccountName, distinguishedName
FROM ''LDAP://DC=corp,DC=mydomain,DC=com''
WHERE objectClass = ''User''
AND objectCategory = ''Person''
AND userAccountControl <> ''514''
AND displayName >= ''p''
AND displayName <= ''z''')
----- Insert Members (for associations between ou's, groups and users):
----- spAdInsertMembers
-- run 4th
DECLARE @csr cursor,
@DN varchar(500),
@sql nvarchar(max)
--------------------------------------------------------------------------
-- Set Cursor for DN of Organizational Units "O"
--------------------------------------------------------------------------
SET @csr = CURSOR STATIC FOR
SELECT Dn
FROM dbo.tblActiveDirectory
WHERE RecType IN ('O', 'G')
AND Dn NOT LIKE '%''%'
OPEN @csr
FETCH NEXT
FROM @csr
INTO @DN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = "
SELECT distinguishedName, '" + @DN + "'
FROM OPENQUERY(ADSI,
'SELECT mail, displayName, samAccountName, distinguishedName
FROM ''LDAP://corp.mydomain.com/DC=corp,DC=mydomain,DC=com''
WHERE memberOf = ''" + @DN + "''
AND userAccountControl <> 514
AND objectClass = ''User''
AND objectCategory = ''Person'' ')
UNION ALL
SELECT distinguishedName, '" + @DN + "'
FROM OPENQUERY(ADSI,
'SELECT mail, Name, distinguishedName
FROM ''LDAP://corp.mydomain.com/DC=corp,DC=mydomain,DC=com''
WHERE memberOf = ''" + @DN + "''
AND userAccountControl <> 514
AND objectClass = ''Group'' ')"
INSERT INTO dbo.tblActiveDirectoryMembers
(
DnChild,
DnParent
)
EXEC sp_executesql @sql
FETCH NEXT
FROM @csr
INTO @DN
END
CLOSE @csr
DEALLOCATE @csr
---- sample view
CREATE VIEW [dbo].[vwAdGroupsByUser]
AS
WITH cteAdGroupMembership
(DisplayName, Sam, Mail, RecType, GroupName, GroupSam, GroupMail, GroupRecType)
AS
(SELECT u.DisplayName, u.Sam, u.Mail, u.RecType,
SELECT DisplayName, Sam, Mail, RecType, GroupName, GroupSam, GroupMail, GroupRecType
FROM cteAdGroupMembership AS cteAdGroupMembership_1
---- query your view for group members
SELECT *
FROM vwAdGroupsByUser
WHERE groupname = 'MyDepartment'
December 23, 2013 at 4:34 am
Providing a username and password in the linked server settings is what solved this problem for me. The user must of course have permissions to read active directory.
December 6, 2019 at 9:31 pm
you are a rock star...any idea how add the user's groups to the out put (member of)?
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply