Fix: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider ‘ADsDSOObject’. OLE DB error trace [OLE/DB Provider ‘ADsDSOObject’ ICommandPrepare::Prepare returned 0x80040e14].
Whenever you get the issue “Msg 7321” make sure your security context is correctly supplied. Otherwise you will get below error.
Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider 'ADsDSOObject'. OLE DB error trace [OLE/DB Provider 'ADsDSOObject' ICommandPrepare::Prepare returned 0x80040e14].
We should use domain user account in sp_addlinkedserver stored procedure that has access rights to Active Directory. The domain name is required in the Security settings for the remote login. So in the Security settings I entered Remote login as domain user with password. Find below dummy example.
Step1: Run below command.
EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject', @datasrc=N'LINLDP2'
Step2: Execute below command now.
EXEC sp_addlinkedsrvlogin @rmtsrvname ='ADSI', @useself='false', @rmtuser='DomainNameUserName', @rmtpassword='#Security@SWIM#'
Also, Make sure your SQL Server Service is running under the account which has appropriate access.
- How to Improve Bulk Data Load Performance in SQL Server?
- SQL Server Backup and Recovery Interview Questions
- SQL Server Update Statistics Best Practices - April 15, 2020
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018