SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Active Directory Query


Active Directory Query

Author
Message
dareliui
dareliui
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 1
when i run query on SQL server computer not from other computer by using SSMS then everything was working fine.
Qmaar
Qmaar
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 299
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..!!!
Qmaar
Qmaar
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 299
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?
mgalawin
mgalawin
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 14
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
Karl Klingler
Karl Klingler
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1492 Visits: 2109
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
mgalawin
mgalawin
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 14
Thanks for trying to help. For some reason without making any changes, it started working. So that code is fine.
robbkeller
robbkeller
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 67
This worked for me! Thanks a bunch.

Robb
JuanBob
JuanBob
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1098 Visits: 269
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,
Mail
)
SELECT DistinguishedName,
'G',
Name,
Mail
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,
Mail
)
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'
Snorri Kris
Snorri Kris
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 111
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.
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