How can I update fields in Active Directory

Question

Post reply

How can I update fields in Active Directory

RobV-219457

SSC Veteran

Points: 267
More actions
September 7, 2006 at 8:45 am

#94540

I was wondering if anyone could tell me how to update a field in Active Directory. I can query successfully with the following:
SELECT
givenname As GivenName,sn,[name] As Name, sAMAccountName As DomAcct, employeeNumber As EmpNo,telephoneNumber As PhNumber,
mail As Email
FROM OpenQuery(
ADSI,'<LDAP:// CN=xxx,DC=yyy,DC=zzz,DC=com>;(&(objectCategory=Person)(objectClass=User));
givenname,sn,name, sAMAccountName, userPrincipalName, employeeNumber,
telephoneNumber, UserAccountControl, mail, adspath;subtree')
I tried using:
UPDATE A set department = 'cccc' FROM (The above select clause) A
It didn't work. Any ideas?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DavidP-340734 Right there with Babe Points: 737 More actions · Answer 1

Have you tried creating a Linked Server to AD?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/distributed_query.asp

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

GO

EXEC sp_addlinkedsrvlogin 'ADSI', false, 'DOMAIN\Administrator', 'CN=Administrator,DC=DISTINGUISHED_NAME,DC=com', 'PASSWORD'

GO

RobV-219457 SSC Veteran Points: 267 More actions · Answer 2

Thank you for resonding! Yes, I did create a linked server for which my query uses successfully. I also added a login to the linked server that has update permission in the Active Directory. I created a VIEW as suggested in the URL that you provided and I can SELECT via the VIEW. If I try:

Update MyView set department = 'cccc' from MyView, I get:

Server: Msg 7390, Level 16, State 1, Line 1

The requested operation could not be performed because the OLE DB provider 'ADSDSOObject' does not support the required transaction interface.

OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IUnknown::QueryInterface returned 0x80004002].

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

I'm researching the possibility of updating Active Directory from SQL Server. Has anyone been able to successfully use an Openquery UPDATE to push data to AD?

Thanks,

Greg

Ronald Mac Donald Grasshopper Points: 14 More actions · Answer 4

Greg,

I see no one has responded to your post yet. Have you been able in the meantime to resolve the problem.

Regards

Ronnie

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Hi Ronnie,

No, I haven't. I'm really just looking for an example to refer to. I'm working on a team trying to figure out how to use employee data in SQL Server to keep Active Directory up-to-date.

Thanks for your reply.

Greg

Ronald Mac Donald Grasshopper Points: 14 More actions · Answer 6

Ive probably been researching this exact problem for the last two weeks non stop. The only solution I have yet to have seen that comes remotely close to updating ad using sql (not direct update) was by utilising the ldifde.exe utility. Now this is not directly from SQL but could be automated to producee the results were looking for. Im positive it could be accomplished until we find something more permanent.

Check out this article. Not an extremely clean solution but it will work:

https://blogs.pointbridge.com/Blogs/seaman_derek/Lists/Posts/Post.aspx?ID=5

If you do of course find something we can work with please let me know.

Cheers

Ronnie