CODEWITHSUNDEEP
sqlsql-server-2005active-directorylinked-server
anschoewe
anschoewe

Reputation: 1089

SQL 2005 Linked Server Query Periodically Failing

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

Upvotes: 4

Views: 4082

Answers (5)

jlvaquero
jlvaquero

Reputation: 8785

This question appears in the top of the first google page when search for the error string but has not valid answer.

This error happens intermitently when isolation level is not specified on .NET code nor in Store Procedure.

This error also happens in SQL Server 2008.

The fix is force SET TRANSACTION ISOLATION LEVEL READ (UN)COMMITTED because a isolation level any higher is not supported by Active Directory and SQL Server is trying to use SERIALIZABLE.

Now, as this error is intermitent. Why is ADO.NET or SQLServer switching its default isolation to SERIALIZABLE sometimes and sometimes not? What triggers this switching?

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300719

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

Upvotes: 1

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

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

Upvotes: 2

codemonkey
codemonkey

Reputation: 2665

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.

Upvotes: 3

Ric Tokyo
Ric Tokyo

Reputation: 6585

please read the support page from Microsoft

Upvotes: 1

Related Questions