SQL 2005 Linked Server Query Periodically Failing

Question

We have a database running on SQL 2005. One of the store procedure looks up a user's email address from Active Directory using a linked server. The call to the linked server occurs in a database function.

I'm able to call is successfully from my Asp.Net application the first time, but periodically after that, it fails with the following error:

{"The requested operation could not be performed because OLE DB provider \"ADsDSOObject\" for linked server \"ADSI\" does not support the required transaction interface."}

It appears that the amount of time between calling the function affects whether the linked server query will work correctly. I am not using any transactions. When I try calling the function in a quick make-shift SQL script, it runs fine everytime (even when tested in quick succession).

Is there some sort of transaction being left open that naturally dies if I don't try calling the procedure again? I'm at a loss here.

Here is the simple call in the store procedure:

DECLARE @email varchar(50)


SELECT @email = LEFT(mail, 50)
FROM OPENQUERY (
 ADSI,
 'SELECT mail, sAMAccountName FROM ''LDAP://DC=Katz,DC=COM'' WHERE objectCategory = ''Person'' AND objectClass = ''User'''
)
WHERE sAMAccountName = CAST(@LoginName AS varchar(35))

RETURN @email

Answer 1

Hi, please read the support page from Microsoft

Answer 2

I've worked with SQL Server linkservers often, though rarely LDAP queries... but I got curious and read the Microsoft support page linked to in Ric Tokyo's previous post. Towards the bottom it reads:

It is typical for a directory server to enforce a server limitation on the number of objects that will be returned for a given query. This is to prevent denial-of-service attacks and network overloading. To properly query the directory server, large queries should be broken up into many smaller ones. One way to do this is through a process called paging. While paging is available through ADSI's OLEDB provider, there is currently no way available to perform it from a SQL distributed query. This means that the total number of objects that can be returned for a query is the server limit. In the Windows 2000 Active Directory, the default server limit is 1,000 objects.

I'm thinking that the reason it fails on you (or not) depending on whether call it from the app or from a "quick make-shift sql script" (as you put it) might be related to the security context under which the operation is executing. Depending on how the link server connection was set up, the operation could be being executed under a variety of possible credentials depending on how you initiate the query.

I don't know, but that's my best guess. I'd look at the linkserver configuration, in particular the linkserver settings for what set of credentials are used as the security context under which operations executed across the linkserver run.

Answer 3

Rather then query Active Directory through a linked server, you might be better off caching your AD data into a SQL database and then querying that instead. You could use Integration Services by creating a OLE DB connection using "OLE DB PRovider for Microsoft Directory Services" and having a DataReader source with a query like:

    SELECT physicalDeliveryOfficeName, department, company, title, displayName, SN, 
    givenName, sAMAccountName, manager, mail, telephoneNumber, mobile  
    FROM 'LDAP://DC=SOMECO,DC=COM' 
    WHERE objectClass='User'  and objectCategory = 'Person' 
    order by mail

Using this method you will still run into the 1000 row limit for results from an AD query (note it is NOT advisable to try and increase this limit in AD, it is there to prevent the domain controller from becoming overloaded). Sometimes its possible to use a combination of queries to return the full data set, e.g. names A - L and M - Z

Alternatively you could use the CSVDE command line utility in Windows Server to export your directory information to a CSV file and then import it into a SQL database (see http://computerperformance.co.uk/Logon/Logon_CSVDE_Export.htm for more info on exporting AD data with CSVDE).

Answer 4

I suspect that it might be the cached query plan due to your statement that "When I try calling the function in a quick make-shift SQL script, it runs fine everytime (even when tested in quick succession)."

Could you try executing your stored procedure like so:

EXEC usp_MyProcedure WITH RECOMPILE

Answer 5

I am using CSLA Framework from Rockford Lotka and I ran into the same problem. I have this problem only after I do an update to the database and that too when I do insert/update with the

[Transactional(TransactionalTypes.TransactionScope)]
protected override void DataPortal_Insert()
{
    DoInsertUpdate();
}
[Transactional(TransactionalTypes.TransactionScope)]
protected override void DataPortal_Update()
{
    DoInsertUpdate();
}

If I make this

[Transactional(TransactionalTypes.Manual)]

then the problem is solved. But I cannot make this manual because there are a whole lot of related tables and business logic involved and I don't want any orphan records.

Is there any solution to this issue?