Active Directory/ADSI connection problems...Here's the correct syntax

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')