INF: How to Troubleshoot SQL Server 6.5 SQL Security Manager Messages (164167)

SUMMARY

This article explains some messages that you may receive when working with SQL Security Manager, the causes of the problems, and their resolutions. It applies to situations where the MSSQLServer service starts up with an account that has been granted the Windows NT Server 'Log on as a service' right, and is a member of the local Administrators group.

MORE INFORMATION

I.

An attempt to log in to SQL Security Manager is rejected, and the following message returned:

This SQL Server does not support Windows NT SQL Server security stored procedures.

If the server is set up to use Integrated Security, the following message is encountered:

Msg. No.: 18450 Severity: 14 State: 1
Login failed- User: loginid Reason: Not defined as a valid user of a
trusted SQL Server connection

Where loginid is the MSSQLServer service startup account.

This problem occurs if system administrator (SA) privileges have previously been revoked from the Administrators group.

II.

Within Security Manager, the groups that have been granted user or SA privileges cannot be expanded by double-clicking on them. Attempting to grant user or SA privileges produces the following message:

xp_logingrant failure - Unable to query SQL Server security information

This problem occurs as a result of revoking SA privileges on the local Administrators group within the current Security Manager session.

III.

A log on to SQL Security Manager is successful. However, when viewing user or sa privileges, no groups are shown, and the following message appears:

No accounts have been granted user authority.

When you attempt to grant user or SA privileges to any group, the following message appears:

xp_logingrant failure - Unable to query SQL Server security information

If the server is set up for Integrated Security, a connection is refused, and you receive the following message:

You must use the System Administrator's (sa) account when using the SQL Security Manager.

These messages indicate that the registry permission (on the key mentioned in the RESOLUTION section of this article) for the local Administrators group has been set to Read.

RESOLUTION

To effectively use SQL Security Manager, SA privileges must be returned to the local Administrators group. Usually, this cannot be achieved with Security Manager. Use the Registry Editor to restore these permissions.

WARNING: Using Registry Editor incorrectly can cause serious, system-wide problems that may require you to reinstall Windows NT to correct them. Microsoft cannot guarantee that any problems resulting from the use of Registry Editor can be solved. Use this tool at your own risk.

Open Regedt32.exe

Navigate to the following key:

      HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer

On the Security menu, click Permissions.
Grant the local Administrators group Full Control of this key.

If the registry key is set properly as outlined above, the following is another message that may be received when attempting to log in to Security Manager:

Msg. No.: 18452 Severity: 14 State: 1
Login Failed- User: sa Reason: Not associated with a Trusted SQL Server
Connection

This error may occur when the server has been set to use Integrated Security and the default network library does not support trusted connections, such as NWLink IPX/SPX, TCP/IP Sockets or Apple Talk. In this case, you will either have to configure the server for Standard or Mixed Security, or change the default network library to Named Pipes or Multi- Protocol, using the SQL Client Configuration Utility.