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 Tuesday, December 14, 2010 1:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 2:30 AM
Points: 1, Visits: 1
when i run query on SQL server computer not from other computer by using SSMS then everything was working fine.
Post #1034238
Posted Monday, January 3, 2011 6:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 11, 2013 3:44 AM
Points: 19, 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..!!!
Post #1041792
Posted Monday, January 3, 2011 6:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 11, 2013 3:44 AM
Points: 19, 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?

Post #1041794
Posted Tuesday, January 4, 2011 2:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 23, 2013 7:59 PM
Points: 2, 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
Post #1042695
Posted Wednesday, January 5, 2011 1:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 477, Visits: 1,887
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
Post #1042860
Posted Wednesday, January 12, 2011 11:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 23, 2013 7:59 PM
Points: 2, Visits: 14
Thanks for trying to help. For some reason without making any changes, it started working. So that code is fine.
Post #1047025
Posted Friday, December 7, 2012 3:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 12:58 PM
Points: 2, Visits: 53
This worked for me! Thanks a bunch.

Robb
Post #1394260
Posted Saturday, December 8, 2012 7:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, December 8, 2012 7:21 PM
Points: 234, 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'
Post #1394370
Posted Monday, December 23, 2013 4:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:53 AM
Points: 3, Visits: 70
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.
Post #1525463
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse