You may experience a 45-second delay when you run a full-text query in an instance of SQL Server 2005 that is running on a server without Internet access
Important This article contains information that shows you how to help lower security settings or how to turn off security features on a computer. You can make these changes to work around a specific problem. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this workaround in your particular environment. If you implement this workaround, take any appropriate additional steps to help protect your system.
Consider the following scenario. You run a full-text query in an instance of Microsoft SQL Server 2005 that is running on a server. The server does not have Internet access. In this scenario, you may experience a 45-second delay.
This issue occurs because the Microsoft Full-Text Engine for SQL Server (MSFTESQL) tries to load the word breaker that is related to the query. This word breaker requires an Internet connection.
When you run a full-text query, a word breaker that is used by the query is loaded if the word breaker is not loaded yet. Loading a word breaker encapsulates signature verification. Signature verification also involves a certificate revocation check. This certificate revocation check requires Internet connectivity. A 45-second time-out exists for the certificate revocation check. If no Internet connectivity is detected, the revocation check fail after 45 seconds. However, the rest of signature verification still occurs. Then the word breaker is loaded.
MSFTESQL unloads a word breaker that has not been used for 5 minutes to reduce resource consumption. Therefore, the next time that the work breaker is loaded, signature verification is required again.
Important These steps may increase your security risk. These steps may also make your computer or your network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to, or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you choose to implement this process, take any appropriate additional steps to help protect your system. We recommend that you use this process only if you really require this process.
To resolve this issue, restore Internet connectivity before you run a full-text query. If you are behind a firewall, install a firewall client, and then open the 80 port that is required by signature verification.
Warning This workaround may make your computer or your network more vulnerable to attack by malicious users or by malicious software such as viruses. We do not recommend this workaround but are providing this information so that you can implement this workaround at your own discretion. Use this workaround at your own risk.
To work around this issue, turn off signature verification by using the following Transact-SQL statement:
Warning If you ignore signature verification, the instance of SQL Server 2005 is less secure. We recommend that you sign any word breaks that you install or make sure that any word breakers that you acquire are signed.
sp_fulltext_service 'verify_signature', 0;GO
Signature verification starts whenever a work breaker loads to eliminate the risk that third parties install word breakers that are not safe.
This behavior is by design.
For more information about the sp_fulltext_service stored procedure, visit the following Microsoft Developer Network (MSDN) Web site:
Article ID: 915850 - Last Review: 11/20/2007 17:41:09 - Revision: 1.2
Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems, Microsoft SQL Server 2005 Enterprise X64 Edition
- kbprb kbtshoot kbsql2005fts kbexpertiseadvanced KB915850