Problem with Security Manager
Fabrizio Monaci
Hi,
When I try to connect to my SQL Server 6.5 with SQL Security Manager I
receive the following message :
"This SQL Server does not support Windows NT SQL Server security stored
procedures"
I am using integrated security. Can Someone tell me what's happening ?
Thanks for your help.
----------------------------------------------------------------------------
-----------
Fabrizio Monaci --- PGP S.A.
Route de Chavannes 31 --- 1007 Lausanne
(CH) Switzerland --- pgp...@pgpmanagement.ch
Tel. +4121 6244267 --- Fax +4121 6247119
Mike Kruchten
Article ID: Q164167
Creation Date: 24-FEB-1997
Revision Date: 10-APR-1997
The information in this article applies to:
Microsoft SQL Server, versions 6.0 and 6.5
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.
1.Open Regedt32.exe
2.Navigate to the following key:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer
3.On the Security menu, click Permissions.
4.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.
Christopher McCarron
Well, I'm not completely sure but this is the error you will get if the
wrong version of the system stored procedures are installed or if they have
been removed.
SQL Security manager uses a set of stored procedures and extended stored
procedures to manage the login security. These have to be available for
security manger to work.
Check the troubleshooting guide and I beleive you will find a section on
reinstalling the system stored procedures.
Chris
Fabrizio Monaci <pgp...@pgpmanagement.ch> wrote in article
<OE5sL2i...@uppssnewspub05.moswest.msn.net>...