SQL 2005链接服务器查询定期失败

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.

我们有一个在SQL 2005上运行的数据库。其中一个存储过程使用链接服务器从Active Directory查找用户的电子邮件地址。对链接服务器的调用发生在数据库函数中。

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:

我能够第一次从我的Asp.Net应用程序成功调用,但在此之后,它会在以下错误中失败,并且会失败:

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

{“无法执行请求的操作,因为链接服务器\”ADSI \“的OLE DB提供程序\”ADsDSOObject \“不支持所需的事务接口。”}

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

调用函数之间的时间量似乎会影响链接服务器查询是否正常工作。我没有使用任何交易。当我尝试在快速的make-shift SQL脚本中调用该函数时,它每次都运行正常(即使在快速连续测试时)。

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

5 个解决方案

#1

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:

我经常使用SQL Server链接服务器,虽然很少使用LDAP查询...但我很好奇并阅读了链接到Ric Tokyo之前帖子的Microsoft支持页面。它的底部是:

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.

目录服务器通常对将为给定查询返回的对象数量强制实施服务器限制。这是为了防止拒绝服务攻击和网络过载。要正确查询目录服务器,应将大型查询分解为许多较小的查询。一种方法是通过一个称为分页的过程。虽然可以通过ADSI的OLEDB提供程序进行分页,但目前无法通过SQL分布式查询执行分页。这意味着可以为查询返回的对象总数是服务器限制。在Windows 2000 Active Directory中,默认服务器限制为1,000个对象。

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.

我认为它失败的原因(或不是)取决于是从应用程序调用它还是从“快速make-shift sql脚本”调用(如你所说)可能与安全上下文有关操作正在执行。根据链接服务器连接的设置方式,可以在各种可能的凭据下执行操作,具体取决于您启动查询的方式。

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.

我不知道,但这是我最好的猜测。我将查看linkserver配置,特别是linkserver设置,了解哪些凭据集用作在链接服务器上运行的操作运行的安全上下文。

#2

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:

而不是通过链接服务器查询Active Directory,最好将AD数据缓存到SQL数据库中,然后再查询它。您可以通过使用“OLE DB PRovider for Microsoft Directory Services”创建OLE DB连接并使用带有以下查询的DataReader源来使用Integration Services:

    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

使用此方法,您仍将遇到来自AD查询的结果的1000行限制(请注意,建议不要尝试在AD中增加此限制,以防止域控制器过载)。有时可以使用查询组合来返回完整的数据集,例如,名字A - L和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).

或者,您可以使用Windows Server中的CSVDE命令行实用程序将目录信息导出到CSV文件,然后将其导入SQL数据库(有关详细信息,请参阅http://computerperformance.co.uk/Logon/Logon_CSVDE_Export.htm使用CSVDE导出AD数据)。

#3

please read the support page from Microsoft

请阅读Microsoft的支持页面

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

我怀疑它可能是缓存的查询计划,因为你的声明“当我尝试在快速的make-shift SQL脚本中调用函数时,它每次运行都很好(即使在快速连续测试时)。”

Could you try executing your stored procedure like so:

你能尝试执行你的存储过程吗:

EXEC usp_MyProcedure WITH RECOMPILE

#5

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

当搜索错误字符串但没有有效答案时,此问题会出现在第一个Google页面的顶部。

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

当在.NET代码和存储过程中未指定隔离级别时,此错误会间歇性地发生。

This error also happens in SQL Server 2008.

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.

修复是强制SET TRANSACTION ISOLATION LEVEL READ(UN)COMMITTED,因为Active Directory不支持更高级别的隔离级别,并且SQL Server正在尝试使用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?

现在,因为这个错误是间歇性的。为什么ADO.NET或SQLServer有时会将其默认隔离切换为SERIALIZABLE,有时不会?什么引发这种转变?

#1