[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1002
  • Last Modified:

Ad-hoc Updates of Active Directory using MS SQL Linked Server

I have a linked server with local DB and remote domain admin credentials setup in MS SQL (2000 or 2005) which I can query AD just fine.   I was wondering if MS SQL supports ad-hoc updates of Active Directory using the linked server.  I have scoured the net finding hundreds of articles that either go unanswered or fall into unrelated territory.  Essentially, when I do very basic update statements against the same table (the linked server connection that works with standard queries), I get the following error:

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].

I can provide code samples, but I have searched the net and have not been able to successfully find a simple answer as to whether or not it is possible.  If it is, then we can go from there.

Any feedback is greatly appreciated.
0
CecilAdmin
Asked:
CecilAdmin
  • 2
2 Solutions
 
Eugene ZCommented:
MS SQL does not supports ad-hoc updates of Active Directory using the linked server.
use windows script and call vbs from sql server via xp_cmdshell
also see example:
http://www.codeproject.com/KB/database/SQL_Server_%E2%80%93_Active_Direc.aspx 
0
 
reb73Commented:
I believe the linked server setup uses an implicit TRANSACTION ISOLATION LEVEL SERIALIZABLE, while the AD source does not support anything other than READ UNCOMMITTED..

Can you retry using the explicit TRANSACTION ISOLATION LEVEL before your your update statements like below -

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
<your update statement goes here>

and execute it as a single batch?
0
 
CecilAdminAuthor Commented:
Reb, I created a transaction as you specified using the explicit transaction statement, but I get the same error...

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
      UPDATE openquery(testad,
            'SELECT samAccountName, employeeID
            FROM ''LDAP://DC=test,DC=com''
                  WHERE objectCategory=''Person''
            AND objectClass = ''user''
            AND samAccountName = ''john.doe''')
      SET employeeID = 232323
COMMIT TRANSACTION

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].
0
 
reb73Commented:
Then, I'm afraid you have to use VBscripting/.Net code to update using ADSDSO Object automation classes..
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now