SQL Server 2005 Server Server Queries Periodically Fail

Question

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

I can call successfully from my Asp.Net application for the first time, but periodically after that it fails with the following error:

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

It seems that the time between function calls affects the correct operation of the related server request. I do not use any transactions. When I try to call this function in a quick change SQL script, it works fine every time (even with quick testing).

Is there some kind of transaction that remains open, which naturally dies if I do not repeat the procedure again? I don’t get it here.

Here is a simple call to 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

I often worked with SQL Server link servers, although I rarely requested LDAP ... but I was curious and read the Microsoft support page related to the previous Ric Tokyo post. Below he reads:

This is typical of a directory server to provide a server limit on the number of objects that will be returned for this request. This is to prevent denial of service attacks and network congestion. To correctly query a directory server, large queries must be split into many smaller ones. One way to do this is through a process called paging. While paging is available through the ADSI OLEDB provider, there is currently no way available to execute this from a SQL distributed query. This means that the total number of objects that can be returned for the server request limit. In Windows 2000 Active Directory, the default limit for a server is 1000 objects.

I think that the reason why it fails (or not) depending on whether you call it from the application or from the “quick make-shift sql script” (as you put it), may be related to the security context under which it is executed operation. Depending on how the connection to the link server was configured, the operation may be performed under various possible credentials, depending on how you initiate the request.

I do not know, but this is my best guess. I would look at the configuration of the link server, in particular, the link server settings for which the credential set is used as a security context under which the operations performed on the link server are performed.

Answer 2

Instead of querying Active Directory through a linked server, you might be better off caching your AD data in an SQL database and then query it instead. You can use Integration Services by creating an 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 encounter a limit of 1000 lines for the results of an AD query (note that it is NOT recommended to try to increase this limit in AD, this means that the domain controller will not be overloaded). Sometimes it can be used a combination of queries to return a complete set of data, for example. names A - L and M - Z

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

Answer 3

read the support page from Microsoft

Answer 4

I suspect this might be a cached query plan because of your statement: "When I try to call a function in a quick SQL transition, the SQL script works fine every time (even with quick testing)."

Could you try to execute your stored procedure as follows:

 EXEC usp_MyProcedure WITH RECOMPILE 

Answer 5

This question appears at the top of the first google page when looking for an error string, but has the wrong answer.

This error occurs periodically when the isolation level is not specified in the .NET code or in the save procedure.

This error also occurs in SQL Server 2008.

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

Now, since this error is intermediate. Why does ADO.NET or SQLServer switch default isolation to SERIALIZABLE sometimes and sometimes not? What causes this switch?