Here's one way to get to AD for SQL, to do things like synchronizing your application's security with AD.
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!
No comments:
Post a Comment