Knowledge Base

You may not be able to connect to an instance of SQL Server by using an anonymous login

Article ID: 839569

Article Last Modified on 1/25/2007

APPLIES TO

Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2005 Standard Edition
Microsoft SQL Server 2005 Developer Edition
Microsoft SQL Server 2005 Enterprise Edition
Microsoft SQL Server 2005 Express Edition
Microsoft SQL Server 2005 Workgroup Edition

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.

Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SUMMARY

When you add the NT AUTHORITY\ANONYMOUS LOGON login to your instance of Microsoft SQL Server 2000 or Microsoft SQL Server 2005 so that the instance of SQL Server accepts anonymous logins through Microsoft Windows Integrated security and then you try to connect to the instance of SQL Server as an anonymous user, the connection attempt may not be successful and you may receive the following error message:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Additionally, the following event is logged in the system event log:

Event Type: Error
Event Source: LsaSrv
Event Category: None
Event ID: 6033
Date: <Date>
Time:
User: N/A
Computer: <Computer Name>
Description:

An anonymous session connected from <Computer Name> has attempted to open an LSA policy handle on this machine. The attempt was rejected with STATUS_ACCESS_DENIED to prevent leaking security sensitive information to the anonymous caller.

The application that made this attempt needs to be fixed. Please contact the application vendor. As a temporary workaround, this security measure can be disabled by setting the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\TurnOffAnonymousBlock DWORD value to 1.

This problem occurs when all the following conditions are true:

The instance of SQL Server is installed on a computer that is running Microsoft Windows Server 2003.
The computer that is running the instance of SQL Server is a member server in a domain.
The HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\TurnOffAnonymousBlock registry value is either missing or is not set to 1.
The Network Access: Allow anonymous SID/Name translation security option on the computer that is running the instance of SQL Server is not enabled.

CAUSE

When you try to connect to the instance of SQL Server as an anonymous user, the anonymous connection tries to open the LSA Policy handle on the computer that is running the instance of SQL Server. By default, a Windows Server 2003 member server denies an anonymous connection attempt that tries to open an LSA Policy handle if the TurnOffAnonymousBlock registry value is not set to 1. Therefore, your anonymous connection is not successful. Additionally, after SQL Server receives the anonymous connection request, SQL Server calls the LookupAccountSid Windows API function to obtain the account name. Because the function is called in the context of the anonymous connection, the function call also fails if the Network Access: Allow anonymous SID/Name translation security option is not enabled.

WORKAROUND

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 problem, follow these steps on the computer that is running Windows Server 2003 to allow anonymous connections to SQL Server 2000 or to SQL Server 2005:

Enable the Network Access: Allow anonymous SID/Name translation security option in Local Security Policy. To do this, follow these steps:
1. Click Start, and then click Control Panel.
2. Double-click Administrative Tools, and then double-click Local Security Policy.
3. In the left pane, expand Local Policies, and then click Security Options.
4. In the right pane, under the Policy column, locate and then double-click Network Access: Allow anonymous SID/Name translation.
5. In the Network Access: Allow anonymous SID/Name translation dialog box, click the Enabled option, and then click OK.
6. Close the Local Security Settings window.
7. Close the Administrative Tools window.
Set the TurnOffAnonymousBlock DWORD registry value to 1. To do this, follow these steps.

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
1. Click Start, click Run, type regedit, and then click OK.
2. In Registry Editor, locate and then click the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa registry key.
3. In the right pane, locate and then double-click the TurnOffAnonymousBlock DWORD registry value.
  
  Note If the TurnOffAnonymousBlock DWORD registry value does not exist, you must create the registry value.
4. In the Edit DWORD Value dialog box, type 1 in the Value data box, and then click OK.

Note By default, the Network Access: Allow anonymous SID/Name translation security option is enabled on the computers that act as domain controllers. However, the security option is disabled on workstations and member servers. The domain controllers do not require the TurnOffAnonymousBlock registry key to control the anonymous connection attempts. Therefore, if your instance of SQL Server is installed on a domain controller that is running Windows Server 2003, the anonymous connection attempts to the instance of SQL Server do not fail.

MORE INFORMATION

On a computer that is running Windows Server 2003, security checks that are performed on the anonymous connections that try to access the computer are more stringent. If you create a Microsoft ASP.NET page that uses Windows authentication and impersonation on a Web server that is running Microsoft Internet Information Services (IIS) 6.0 but you cannot delegate the user accounts, any attempts to connect to a remote instance of SQL Server from the ASP.NET page are in the security context of the NT AUTHORITY\ANONYMOUS LOGON login. You can configure your instance of SQL Server to accept the anonymous connections through Windows Integrated security by adding the NT AUTHORITY\ANONYMOUS LOGON login as a SQL Server user and by granting the required permissions to the user. When you add the NT AUTHORITY\ANONYMOUS LOGON login to your instance of SQL Server, anonymous connections can access SQL Server data without providing any logon credentials.

Important We do not recommend allowing anonymous access to SQL Server. All permissions that are granted to the NT AUTHORITY\ANONYMOUS LOGON login can be used by any user who can connect to the computer that is running SQL Server. If you must allow anonymous access to your instance of SQL Server, we recommend that only read permissions are granted to the NT AUTHORITY\ANONYMOUS LOGON login to view the SQL Server data that you want to be publicly viewable. Additionally, we recommend that only Execute permissions are granted to the SQL Server stored procedures that perform limited operations.

Instead of allowing the anonymous connections to your instance of SQL Server, you can grant the required access to a specific SQL Server account and pass the logon credentials for the SQL Server account in the connection string in the ASP.NET page. Using SQL Server authentication avoids the anonymous connection attempts to the instance of SQL Server and is more secure.

If the Network Access: Allow anonymous SID/Name translation security option is enabled on the computer that is running Windows Server 2003, all the users who can make a network connection to the computer can look up the account names for any known security identifications (SID), such as the Administrator account. A malicious attacker may use this information to connect to the server by using a method such as password guessing or to lock out the accounts with failed login attempts.

If you set the value of the TurnOffAnonymousBlock registry value to 1, the anonymous connections can open a handle to the policy for the Local Security Authority. For more information about the LSA Policy, visit the following MSDN Web sites:

http://msdn2.microsoft.com/en-us/library/ms721831.aspx

http://msdn2.microsoft.com/en-us/library/ms722489.aspx

http://msdn2.microsoft.com/en-us/library/ms721833.aspx

http://msdn2.microsoft.com/en-us/library/ms721874.aspx

REFERENCES

For additional information about troubleshooting the connectivity issues in SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:

827422 How to troubleshoot connectivity issues in SQL Server 2000

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

247931 Authentication methods for connections to SQL Server in Active Server Pages

Additional query words: SQL anonymous TurnOffAnonymousBlock

Keywords: kbprb kbhowto kbclientserver kberrmsg kbuser kbusage kbconfig kbregistry kbsecurity KB839569

Microsoft KB Archive/839569

Contents