May 7, 2007 at 4:08 pm
I have been able to link the server to query AD. I use a Cursor to go through and import OU's, Groups, and associated Users. This works like a charm, but upon further review, not all Group members are coming across.
Query For OU's: I only care about those under Departmental Accounts. This returns accurately.
SELECT name, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT name, distinguishedName
FROM ''LDAP://corp.company.com/OU=Departmental Accounts,OU=company,DC=corp,DC=company,DC=com''
WHERE objectClass = ''organizationalUnit'' ')
Query For Groups: This is run within a Cursor from the above result set. @DN is the distinguishedName for each OU from above. This returns accurate results.
SELECT mail, displayName, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT mail, displayName, distinguishedName
FROM ''LDAP://corp.company.com/" + @DN + "''
WHERE objectClass = ''Group'' ')
Query For Group Members: This runs within a Cursor from the Groups result set above. @DN is the distinguishedName for each Group from above. This returns some Group members but not others and is where I need help.
SELECT mail, displayName, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT mail, displayName, distinguishedName
FROM ''LDAP://corp.company.com/OU=Departmental Accounts,OU=company,DC=corp,DC=company,DC=com''
WHERE memberOf = ''" + @DN + "''
Most examples on the web use OpenQuery. Anytime I try that method I get an error. Permissions is not an issue, or so it should not be given that I had Domain Admin rights while trouble-shooting this. The following query is an example of a Group that does not return any results:
SELECT mail, displayName, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT mail, displayName, distinguishedName
FROM ''LDAP://corp.company.com/OU=Departmental Accounts,OU=Company,DC=corp,DC=company,DC=com''
WHERE memberOf = ''CN=Analysts,OU=Marketing,OU=Departmental Accounts,OU=Company,DC=corp,DC=company,DC=com''
AND objectClass = ''User'' ')
I'v got pretty far after many trial and error routines. This has me stumped. Any help would be appreciated!
May 9, 2007 at 2:45 pm
Another update: After querying users and testing group memberships, I have found that:
A user with userAccountControl = NULL do not return in my memberOf query.
A user with userAccountControl of 512 shows up in my memberOf query.
Reading documentation shows values of 512 = Active and can Logon, and 514 = Disabled. Why or how would an account be NULL?
Thanks again!
May 11, 2007 at 1:51 pm
ISSUE RESOLVED.
It was a permissions issue. Messing with the local login, the login used on the Linked Server, and what the expected permissions were on the network were, I was finally able to find the right combination.
May 11, 2007 at 1:55 pm
Good Stuff Man!
I wonder if this would work in 2005 w/ the default Surface Area Configuration...
Cheers,
Ben
Ben Sullins
bensullins.com
Beer is my primary key...
December 9, 2008 at 2:06 pm
JuanBob...I'm having the same issue. How did you set the security for the linked server? For my Local Login, I used the domain administrator: domain\administrator. What should the remote user be? The same as the Local Login? And also, how do you check on AD to see if the MSSQLSERVER service account has permissions to query the directory? Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply