RE: Error 15401 using sp_grantlogin (not addressed by current KB articles)

From: Bill Hollinshead [MS] (billhol@online.microsoft.com)
Date: 10/01/02 

From: billhol@online.microsoft.com ("Bill Hollinshead [MS]")
Date: Tue, 01 Oct 2002 21:59:11 GMT

Hi Trevor,

Get_sid() does exactly that <g>, when an account name is supplied as an
argument to get_sid, it returns a sid. Syxlogins contains two relevant
columns: sid and name. The sysxlogins.name column stores the NT account
name that was in existence *when the account was added to SQL Server*
(i.e., DOMAIN\oldusername). When an NT Domain Admin renames an account
(using NT tools, and as opposed to dropping and recreating an NT account
also using NT tools), NT will retain the original sid and simply rename the
account. Thus the problem: NT has the original sid with DOMAIN\newusername
while SQL Server's sysxlogins..name still has the original sid with
DOMAIN\oldusername.

One way (the safest method) to get SQL Server to agree with the renamed NT
accounts is to script out the logins (once for the system), users (once for
every database), and the users' permissions (once for every database). This
is done via SQL Enterprise Manager's menu after selecting a database,
choose Tools|Generate SQL Scripts, on the General tab click Show All and
check "Script all objects", on the Formatting tab UNcheck "Generate the
DROP <object> command for each object", on the Options tab check everything
(4 boxes) under "Security Scripting Options", back to the General tab click
Preview (to ensure the script was created), and then save the script. You
can then open the script within an ASCII text editor, *verify the commands
are just those commands that are desired* (the CREATE TABLE commands can be
deleted, but CREATE TABLE will fail if the table already exists), and
replace DOMAIN\oldusername with DOMAIN\newusername (apart from where the
script drops DOMAIN\oldusername). Then backup all databases and run the
script. *If the script edited and verified properly*, it should drop
DOMAIN\oldusername, create DOMAIN\newusername, and assign appropriate
permissions to the appropriate objects. This script is worth archiving (as
would be a complete script of all user databases) in a safe location. Make
sure to test upon a sacrificial SQL Server system before trying this in
production. Some of that editing can be avoided, and some of this
management overhead/pain can be minimized, by having originally used NT
Groups instead of using individual NT logins - perhaps too late at this
time, though <g>.

Alternatively, you should be able to ignore this issue since the NT
accounts should be able to connect to SQL Server via their
DOMAIN\newusername even though DOMAIN\oldusername still exists in
sysxlogins (i.e., you don't really need to sp_revoke and sp_grant logins
since it is the sid that matters and it is the sid that gets passed to SQL
Server from a client). This is why the issue is not observed by many DBAs
(unless the Authentication problems started happening at about the same
time that the NT account names were renamed). If you are seeing
authentication issues, perhaps the issue is really/instead that the client
cannot currently see the domain controller, and thus the login account's
sid cannot be passed to SQL Server from the client - this normally would be
evidenced by a logon failed for user NT authority\anonymous (on a SQL 2000
domain) or by logon failed for user NULL (on an NT 4.0 domain) being
returned by SQL Server within it's logon failed error message. If logon
failed for user NULL is being seen, then it is likely better to ensure that
client box can see the Domain Controller, and be authenticated by that DC
(i.e., an NT issue). For example, a SET command will return the LOGONSERVER
environment variable which is populated at the last system startup, and if
LOGONSERVER is equal to the client box's name then (when NT was started)
the DC could not be found and cached credentials were instead used. Thus it
is likely better to ensure the account upon that client box has logged onto
a freshly recycled NT with the DOMAIN\newusername. If that logon after a
recycle fails, then the this issue is clearly an NT/Domain Authentication
issue (at the client) since the account cannot logon to NT, and is not a
SQL Server using NT Authentication issue (which should be expected to fail
if a client cannot authenticate with a DC).

Finally, you may be able to find newsgroup scripts that update sysxlogins,
but please be aware that such ad-hoc updates to the system catalog can
cause, and have caused, unexpected system behavior and thus are not
supported. If you want to risk trying such a script, then I strongly
suggest backing up the master database before running it, and I suggest
testing the system after running it but before the backup becomes obsolete
(obsolete perhaps because new logins were added to master since the backup
was made, etc.). Personally, I would rather play it safe and follow the
supported yet slower route within the 2nd paragraph of this response.
Finally, if such a script were run and if the NT Authentication problems
still exist, then the cause could either be that update sysxlogins, or the
cause could be the original (still unresolved) NT Authentication issue -
this is sort of a "one step forward two steps back" situation, for which
rebuilding the master database may be the more sure-fired (and unpalatable)
means to resolve which issue is the cause (and perhaps still needing to
resolve the client NT Authentication issue).

If you need further help, please feel free to contact me offline (remove
'online.' from my alias). In a production server situation, it is perhaps
far better that you open a case with support as their help will be more
immediate. Be sure to mention the impact upon production.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Relevant Pages

  • Re: Error 15401 using sp_grantlogin (not addressed by current KB articles)
    ... Restarting Windows 2000 resolved the problem for this particular account, ... confused when it sees a duplicate SID. ... > One way to get SQL Server to agree with the renamed NT ... > Preview (to ensure the script was created), ...
    (microsoft.public.sqlserver.security)
  • Re: xp_CmdShell and VBScript
    ... The computer the script is attempting to create a local account on is ... in which the SQL Server service is running. ... Microsoft® Windows NT® 4.0 and Windows 2000. ...
    (microsoft.public.windows.server.scripting)
  • Re: Removing user account accross multiple domains
    ... Is there any way that I can tell the script to just use the default ... account, I need to disable his specific user account. ... The trick is to know the "well-known SID" for Administrator. ...
    (microsoft.public.scripting.vbscript)
  • RE: Unable to set the SQL Agent proxy account
    ... I created an account that is only a member of the ... find script engine "VBscript". ... I add the proxy account to the administrator group the ... >Start SQL Server and SQL Agent. ...
    (microsoft.public.sqlserver.security)
  • Protecting against dDOS bots (was: Newbie php problem)
    ... The form mail script posted that was used, ... requires the applicant to pass some kind of Turing test, ... Turing test if the account balance ever drops to zero. ... Log into the same account repeatedly, which consumes your credit ...
    (alt.php)