PRB: SQL Server 6.5 Integrated Security Does Not Function as Expected After You Upgrade to Windows 2000 (274479)


The information in this article applies to:

  • Microsoft SQL Server 6.5
This article was previously published under Q274479

SYMPTOMS

On a computer that has Microsoft SQL Server 6.5 installed, if you upgrade the operating system from Microsoft Windows NT 4.0 to Microsoft Windows 2000, SQL Integrated Security may not function properly after the upgrade.

In SQL Server Security Manager note that local Windows NT groups, which did not have access to SQL Server before you upgraded the operating system, are now listed as having access to SQL Server and other user-created groups that had access before are not listed. When you run the XP_LoginInfo extended stored procedure, note that some user accounts and groups are listed more than once with different access levels to SQL Server and that the user groups that had access before are not listed at all.

CAUSE

SQL Integrated Security is defined by appropriate permissions set on the following registry key for Windows NT authenticated users:

Hkey_Local_Machine\Software\MSSQLServer\MSSQLServer

When you upgrade an existing Microsoft Windows NT 4.0 SQL Server 6.5 platform to Microsoft Windows 2000, custom registry permissions set on the SQL Server local machine keys are not preserved. The Windows 2000 upgrade process applies Windows 2000 default security settings to registry keys and file system objects. This process overwrites any custom permissions that were previously defined.

RESOLUTION

To prevent the Windows 2000 upgrade from modifying the Custom Security settings, use the steps that follow to perform the upgrade. You must follow these steps to ensure that the registry key permissions are not lost when the system is upgraded to Windows 2000.

Microsoft Windows 2000 uses the following security template to apply security settings during the upgrade process:

Dsup.inf (for Windows 2000 Server upgrades)

You can modify this text-based template to ignore the specific registry key that contains custom security settings for SQL Server 6.5 by using the following steps:
  1. Copy the appropriate template file (Dsup.inf) from your Windows 2000 distribution share into the %WinDir%\Security\Templates folder on any Windows 2000 server.
  2. Start Microsoft Management Console. On the taskbar, click Start, and then click Run. In the Run dialog box type the following:

    Mmc.exe

    Click OK.
  3. From the Console menu, click Add/Remove Snap-in, and then click Add. In the dialog box, click Security Templates, click Add, click Close, and then click OK.
  4. To open the template file you want to modify, expand the Security Templates node, expand the %WinDir%\Security\Templates folder, and then expand the template file (Dsup.inf).
  5. Click the security area that you want to modify (Registry).
  6. In the result pane, a list of all of the registry keys configured by the default upgrade template displays.
  7. If the following key is not listed:

    Machine\Software\MSSQLServer\MSSQLServer

    You must add the key by using these steps:

    1. Right-click Registry, and then click Add Key.
    2. Browse the dialog box to select the key you want to protect. If the key does not exist on your computer, you can type the path to the object in the available box. In this case, type:

      Machine\Software\MSSQLServer\MSSQLServer
    3. Click OK to start the Access Control List (ACL) editor.
    4. Click OK again to accept the default security provided by the ACL editor.
    5. Click Do not allow permissions on this key to be replaced.
    6. Click OK to add the object to the template, and then proceed to step 8.
  8. The object that you want the upgrade to ignore should be listed in the result pane, with the Ignore property listed under both the permission and audit columns. Right-click the name of the template, and then click Save.
  9. Copy the modified template back to the distribution share.
Future upgrades of Microsoft Windows NT 4.0 servers with Microsoft SQL Server 6.5, from this distribution share, will not configure the ignored objects with Windows 2000 default settings.

REFERENCES

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

260242 How to Prevent Windows 2000 Upgrade from Modifying Custom Security

Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbprb KB274479
©2003 Microsoft Corporation. All rights reserved.