ADMT v3 Migration Guide
Microsoft Corporation
Published: November 2006
Abstract
This guide explains how to use the Active Directory Migration Tool version 3 (ADMT v3) to restructure your operating environment. You can use ADMT v3 to migrate objects between Active Directory forests, between Active Directory domains in the same forest, or from Microsoft® Windows NT® 4.0 source domains to Active Directory. ADMT v3 can also perform security translation from Windows NT 4.0 domains to Active Directory and between different Active Directory forests.
Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
© 2006 Microsoft Corporation. All rights reserved.
Active Directory, Microsoft, Windows, Windows NT, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
Contents
ADMT v3 Migration Guide 11
Windows NT 4.0 Domain Restructure to an Active Directory Forest 11
Interforest Active Directory Domain Restructure 12
Intraforest Active Directory Domain Restructure 12
Terms and Definitions 13
Active Directory Migration Tool 14
Using an Include File 15
SourceName Field 16
TargetName Field 17
TargetRDN, TargetSAM, and TargetUPN Fields 17
Renaming Objects 18
Using Scripts 18
Windows NT 4.0 Domain Restructure to an Active Directory Forest 20
Overview of Restructuring Windows NT 4.0 Domains to an Active Directory Forest 20
Process for Restructuring Windows NT 4.0 Domains to an Active Directory Forest 21
Windows NT 4.0 Domain Migration Background Information 22
Planning to Restructure Windows NT 4.0 Domains to an Active Directory Forest 22
Assign Object Locations and Roles 23
Developing a Migration Test Plan 25
Creating a Migration Rollback Plan 28
Planning for User Profile Migration 29
Establishing Administrative Procedures 30
Administering User Accounts 31
Administering Global Groups 31
Creating an End-User Communication Plan 31
General Information 31
Impact 32
Logon Status during Migration 32
Premigration Steps 32
Expected Changes 32
Scheduling and Support Information 32
Preparing the Source and Target Domains for Restructuring 33
Installation of High Encryption Software 34
Establishing Required Trusts 34
Establishing Migration Accounts 35
Configuring the Source and Target Domains to Migrate SID History 39
Configure the Target Domain OU Structure for Administration 40
Installing ADMT 41
Prerequisites for Installing ADMT 41
Installing ADMT Using the Default Database Store 42
Installing ADMT Using a Preconfigured SQL Database 44
Enabling Password Migration 46
Initializing ADMT 47
Service Account Identification 50
Restructuring Account Domains 55
Transitioning Service Accounts 56
Migrating Global Groups 60
Migrating Users in Batches 65
Migrating User Accounts 66
Translating Local User Profiles 72
Migrating User Workstations 77
Remigrating Global Groups 81
Completing the Account Migration 86
Restructuring Resource Domains 87
Migrating Workstations and Member Servers 88
Migrating Domain Controllers 91
Migrating Shared Local Groups 92
Migrating Backup Domain Controllers 94
Completing the Resource Migration 94
Translating Security on Member Servers 95
Decommissioning the Source Resource Domain 97
Interforest Active Directory Domain Restructure 97
Overview of Restructuring Active Directory Domains Between Forests 98
Process for Restructuring Active Directory Domains Between Forests 98
Background Information for Restructuring Active Directory Domains Between Forests 99
Account Migration Process 100
Resource Migration Process 101
Functional Levels 101
Planning to Restructure Active Directory Domains Between Forests 102
Determining Your Account Migration Process 103
Using SID History to Preserve Resource Access 105
Using SID Filtering When Migrating User Accounts 106
Assigning Object Locations and Roles 107
Developing a Test Plan for Your Migration 109
Creating a Rollback Plan 112
Managing Users, Groups, and User Profiles 113
Administering User Accounts 113
Attributes That Are Always Excluded by the System 114
System Attribute Exclusion List 114
Attribute Exclusion List 114
Administering Global Groups 115
Planning for a User Profile Migration 115
Creating the End-User Communication Plan 117
General Information 117
Impact 117
Logon Status During Migration 117
Premigration Steps 118
Expected Changes 118
Scheduling and Support Information 118
Preparing the Source and Target Domains 118
Installing 128-Bit High Encryption Software 119
Establishing Required Trusts for Your Migration 120
Establishing Migration Accounts for Your Migration 120
Configuring the Source and Target Domains for SID History Migration 124
Configuring the Target Domain OU Structure for Administration 126
Installing ADMT in the Target Domain 126
Prerequisites for Installing ADMT 126
Installing ADMT Using the Default Database Store 127
Installing ADMT Using a Preconfigured SQL Database 129
Enabling Migration of Passwords 131
Initializing ADMT by Running a Test Migration 132
Identifying Service Accounts for Your Migration 135
Migrating Accounts 140
Transitioning Service Accounts in Your Migration 141
Migration of Global Groups 145
Migrating Accounts While Using SID History 149
Migrating All User Accounts 153
Remigrating User Accounts and Workstations in Batches 158
Translating Local User Profiles 158
Migrating Workstations in Batches 163
Remigrating User Accounts in Batches 167
Remigrating All Global Groups Following User Account Migration 172
Remigrating All Global Groups After All Batches Are Migrated 172
Migrating Accounts Without Using SID History 176
Migration of All User Accounts 178
Translating Security in Add Mode 183
Remigration of User Accounts and Workstations in Batches 187
Translating Local User Profiles 188
Migrating Workstations in Batches 192
Remigrating User Accounts in Batches 196
Remigrating All Global Groups Following User Account Migration 201
Remigration of All Global Groups After All Batches Are Migrated 201
Translating Security in Remove Mode 205
Migrating Resources 209
Migration of Workstations and Member Servers 209
Migrating Domain and Shared Local Groups 214
Migration of Domain Controllers 218
Completing the Migration 218
Translating Security on Your Member Servers 219
Decommissioning the Source Domain 223
Intraforest Active Directory Domain Restructure 223
Overview of Restructuring Active Directory Domains Within a Forest Using ADMT v3 224
Process for Restructuring Active Directory Domains Within a Forest Using ADMT v3 225
Background Information for Restructuring Active Directory Domains Within a Forest Using ADMT v3 225
Closed Sets and Open Sets 226
Users and Groups 226
Resources and Local Groups 228
SID History 228
Assigning Resource Access to Groups 229
Preparing to Restructure Active Directory Domains Within a Forest 229
Evaluate the New Active Directory Forest Structure 230
Identify the Source Domains 231
Identify and Evaluate the OU Structure of the Target Domain 231
Assign Domain Object Roles and Locations 231
Plan for Group Migration 233
Plan for Test Migrations 235
Create a Rollback Plan 237
Create an End-User Communication Plan 239
General Information 239
Impact 239
Logon Status During Migration 239
Premigration Steps 240
Expected Changes 240
Scheduling and Support Information 240
Create Migration Account Groups 240
Install ADMT v3 242
Prerequisites for Installing ADMT 242
Installing ADMT Using the Default Database Store 243
Installing ADMT Using a Preconfigured SQL Database 245
Plan for Service Account Transitioning 247
Example: Preparing to Restructure Active Directory Domains 251
Migrating Domain Objects Between Active Directory Domains 252
Migrate Groups 252
Migrate Universal Groups 253
Migrate Global Groups 257
Migrate Service Accounts 261
Migrate User Accounts 266
Migrating OUs and Subtrees of OUs 267
Migrate Accounts 268
Translate Local User Profiles 272
Migrate Workstations and Member Servers 276
Migrate Domain Local Groups 282
Example: Restructuring Active Directory Domains 284
Completing Post-Migration Tasks 285
Examine Migration Logs for Errors 286
Verify Group Types 286
Translate Security on Member Servers 286
Translate Security by Using a SID Mapping File 290
Decommission the Source Domain 290
Example: Completing Post-Migration Tasks 291
Additional Resources 291
Related Information 291
Related Tools 291
Related Job Aids 292
ADMT v3 Migration Guide
As part of deploying Active Directory® directory service, you might choose to restructure your environment. Before doing so, you must determine when and how you want to restructure it. Organizations with an existing domain structure running Microsoft® Windows NT® Server 4.0 might perform an in-place upgrade of some Windows NT 4.0 domains and restructure others. After you deploy Active Directory, you might decide to further reduce the complexity of your environment by either restructuring domains between forests or restructuring domains within a single forest.
You can use the Active Directory Migration Tool version 3 (ADMT v3) to restructure your environment. ADMT can perform object migrations and security translation as necessary, so that users can maintain access to network resources during the restructure process. To download ADMT v3, see http://go.microsoft.com/fwlink/?LinkId=75627.
The following sections briefly explain the main restructure scenarios for using ADMT. After you determine the appropriate scenario for your environment, follow the steps that are provided later in this guide for that scenario.
Note
These guidelines were previously published as part of Designing and Deploying Directory and Security Services in the Windows Server 2003® Deployment Kit (http://go.microsoft.com/fwlink/?LinkId=76005). Designing and Deploying Directory and Security Services explained how to restructure domains within a forest by using the Active Directory Migration Tool (ADMT) version 2. This guide explains how to perform the same process by using ADMT version 3.
Windows NT 4.0 Domain Restructure to an Active Directory Forest
Because of its greater scalability, an Active Directory environment requires fewer domains than a Windows NT 4.0 environment. Instead of performing an in-place upgrade of your Windows NT 4.0 domains, it might be more efficient to consolidate a number of smaller Windows NT 4.0 account and resource domains into a few, larger Active Directory domains.
Interforest Active Directory Domain Restructure
An interforest restructure might be performed for business changes such as mergers or acquisitions. When you migrate objects between forests as part of the restructuring process, both the source and target domain environments exist simultaneously. This enables you to roll back to the source environment during the migration, if necessary.
Important
All target domains must be operating at the Windows 2000 native or Windows Server 2003 functional level.
Intraforest Active Directory Domain Restructure
When you restructure Windows Server 2003 domains in a Windows Server 2003 forest, you can consolidate your domain structure and reduce administrative complexity and overhead. Unlike the process for restructuring Windows Server 2003 domains between forests, when you restructure domains in a forest, the migrated accounts no longer exist in the source domain.
Important
All target domains must be operating at the Windows 2000 native or Windows Server 2003 functional level.
The following table lists the differences between an interforest and an intraforest domain restructure.
Migration Consideration Interforest Restructure Intraforest Restructure
Object preservation Objects are cloned rather than migrated. The original object remains in the source location to maintain user access to resources. Objects are migrated and no longer exist in the source location.
SID history maintenance Maintaining SID history is optional. SID history is required.
Password retention Password retention is optional. Passwords are always retained.
Local profile migration You must use tools such as ADMT to migrate local profiles. For workstations that run the Microsoft Windows®°2000 Server operating system and later, local profiles are migrated automatically because the user’s GUID is preserved. However, you must use tools such as ADMT to migrate local profiles for workstations that run Windows NT 4.0 and earlier.
Closed sets You do not need to migrate accounts in closed sets. You must migrate accounts in closed sets.
Terms and Definitions
The following terms apply to the Active Directory domain restructure process.
Migration The process of moving or copying an object from a source domain to a target domain, while preserving or modifying characteristics of the object to make it accessible in the new domain.
Domain restructure A migration process that involves changing the domain structure of a forest. A domain restructure can involve either consolidating or adding domains, and can take place between forests or in a forest.
Migration objects Domain objects that are moved from the source domain to the target domain during the migration process. Migration objects can be user accounts, service accounts, groups, or computers.
Source domain The domain from which objects are moved during a migration. When restructuring Active Directory domains between forests, the source domain is an Active Directory domain in a different forest from the target domain.
Target domain The domain to which objects are moved during a migration.
Built-in accounts Default security groups that have common sets of rights and permissions. You can use built-in accounts to grant permissions to any accounts or groups that you designate as members of these groups. Built-in account security identifiers (SIDs) are identical in every domain and therefore built-in accounts cannot be migration objects.
Active Directory Migration Tool
ADMT allows you to migrate objects in Active Directory forests. It includes wizards that automate migration tasks such as migrating users, groups, service accounts, computers, and trusts, and performing security translation.
You can perform ADMT tasks by using the ADMT console, a command line, or a script. When running ADMT at the command line, it is often more efficient to use an option file to specify command-line options. You can use the ADMT option file reference shown in the listing that follows to assist you in creating option files. Examples of command-line syntax are provided for each task that you must perform to restructure the domains within the forest.
The following listing shows common options that apply to several migration tasks. Each type of migration task has a section that lists options that are specific to that task. The section name corresponds to the task name when you run ADMT at the command line. Items can be commented out with a semicolon. In the following listing, the default values are commented out.
[Migration]
;IntraForest=No
;SourceDomain="source_domain_name"
;SourceOu="source_ou_path"
;TargetDomain="target_domain_name"
;TargetOu="target_ou_path"
;PasswordOption=Complex
;PasswordServer=""
;PasswordFile=""
;ConflictOptions=Ignore
;UserPropertiesToExclude=""
;InetOrgPersonPropertiesToExclude=""
;GroupPropertiesToExclude=""
;ComputerPropertiesToExclude=""
[User]
;DisableOption=EnableTarget
;SourceExpiration=None
;MigrateSIDs=Yes
;TranslateRoamingProfile=No
;UpdateUserRights=No
;MigrateGroups=No
;UpdatePreviouslyMigratedObjects=No
;FixGroupMembership=Yes
;MigrateServiceAccounts=No
;UpdateGroupRights=No
[Group]
;MigrateSIDs=Yes
;UpdatePreviouslyMigratedObjects=No
;FixGroupMembership=Yes
;UpdateGroupRights=No
;MigrateMembers=No
;DisableOption=EnableTarget
;SourceExpiration=None
;TranslateRoamingProfile=No
;MigrateServiceAccounts=No
[Security]
;TranslationOption=Add
;TranslateFilesAndFolders=No
;TranslateLocalGroups=No
;TranslatePrinters=No
;TranslateRegistry=No
;TranslateShares=No
;TranslateUserProfiles=No
;TranslateUserRights=No
;SidMappingFile="SidMappingFile.txt"
When running ADMT at the command line, you do not need to include an option in your command if you want to accept the default value. In this guide, however, tables that list possible parameters and values are provided for reference. The tables list the command-line equivalent of each option that is shown in the corresponding ADMT console procedure, including those where you accept the default value.
You can copy the option file reference into Notepad and save it by using a .txt file name extension.
As an example, to migrate a small number of computers, you might type each computer name at the command line, using the /N option, and then list other migration options within an option file as follows:
ADMT COMPUTER /N "computer_name1" "computer_name2" /O:"option_file.txt"
Computer_name1 and computer_name2 are the names of computers in the source domain that you are migrating in this batch.
Using an Include File
When you migrate a large number of users, groups, or computers, it is more efficient to use an include file. An include file is a text file in which you list the user, group, and computer objects that you want to migrate, with each object on a separate line. You must use an include file to rename objects during the migration.
You can list users, groups and computers together in one file or you can create a separate file for each object type. Then specify the include file name with the /F option, as follows:
ADMT COMPUTER /F "includefile_name" /IF:YES /SD:"source_domain” /TD:"target_domain" /TO:"target_OU"
To specify the names of users, groups, or computers, use one of the following conventions:
• The Security Accounts Manager (SAM) account name. To specify a computer name in this format, you must append a $ to the computer name. For example, to specify a computer with the name Workstation01, use Workstation01$.
• The Windows NT 4.0 account name. This includes the domain name and the SAM account name. For example, the Windows NT 4.0 account name of a computer named Workstation01 that is in the Asia domain is Asia\Workstation01$.
• The relative distinguished name (also known as RDN). For example, cn= Workstation01. If you specify the account as a relative distinguished name, you must specify the source organizational unit (OU).
• The canonical name. This can be specified as DNS domain name/ou_path/object_name, or ou_path/object_name; for example, Asia.trccorp.treyresearch.net/Computers/Workstation01 or Computers/Workstation01.
The following sections describe the fields of an include file and provide examples for each:
SourceName Field
The SourceName field specifies the name of the source object. You can specify either an account name or a relative distinguished name. If you only specify source names, it is optional to define a header on the first line in the file.
The following example illustrates a header line that specifies the SourceName field. The example also shows a source object name that is specified in several formats. The second line specifies an account name. The third line specifies an account name in Windows NT 4.0 account name format. The fourth line specifies a relative distinguished name.
SourceName
name
domain\name
CN=name
TargetName Field
The TargetName field can be used to specify a base name that is used to generate a target relative distinguished name, a target SAM account name, and a target user principal name (UPN). The TargetName field cannot be combined with other target name fields discussed later in this section.
Note
The target UPN is only generated for user objects, and only a UPN prefix is generated. A UPN suffix is appended using an algorithm that is dependent upon whether a UPN suffix is defined for the target OU or target forest. If the object is a computer, the target SAM account name includes a "$" suffix.
The following example of input generates the target relative distinguished name, target SAM account name, and target UPN as "CN=newname", "newname" and "newname" respectively.
SourceName,TargetName
oldname, newname
TargetRDN, TargetSAM, and TargetUPN Fields
You can use the TargetRDN, TargetSAM, and TargetUPN fields to specify the different target names independently of each other. You can specify any combination of these fields in any order.
TargetRDN specifies the target relative distinguished name for the object.
TargetSAM specifies the target SAM account name for the object. For computers, the name must include a "$" suffix to be a valid SAM account name for a computer.
TargetUPN specifies the target UPN for the object. You can specify either just the UPN prefix or a complete UPN name (prefix@suffix). If the name that you specify contains a space or a comma, you must enclose the name in double quotation marks (" ").
SourceName,TargetRDN
oldname, CN=newname
SourceName,TargetRDN,TargetSAM
oldname, "CN=New RDN", newsamname
SourceName,TargetRDN,TargetSAM,TargetUPN
oldname, "CN=last\, first", newsamname, newupnname
Note
A comma within the CN value must be preceded with an escape ("\") character or the operation will fail, and ADMT will record an invalid syntax error in the log file.
SourceName,TargetSAM,TargetUPN,TargetRDN
oldname, newsamname, newupnname@targetdomain, "CN=New Name"
Renaming Objects
Use the following format in an include file to rename computer, user, or group objects during migration:
• Use SourceName, TargetRDN, TargetSAM, and TargetUPN as column headings at the top of the include file. SourceName is the name of the source account, and it must be listed as the first column heading. The TargetRDN, TargetSAM, and TargetUPN column headings are optional, and you can list them in any order.
• You must specify the account name as user name, relative distinguished name, or canonical name. If you specify the account name as a relative distinguished name, you must also specify the source OU.
The following are examples of valid include files in which the rename option is used:
SourceName,TargetSAM
abc,def
This include file entry changes the TargetSAM account name for user "abc" to "def." The TargetRDN and the TargetUPN, which are not specified in this include file, do not change as a result of the migration.
SourceName,TargetRDN,TargetUPN
abc,CN=def,def@contoso.com
This include file entry changes the TargetRDN for user abc to CN=def and the TargetUPN to def@contoso.com. The TargetSAM for user abc does not change as a result of the migration.
Important
You must specify CN= before using an RDN value.
Using Scripts
The sample scripts provided in this guide reference the symbolic constants that are defined in a file named AdmtConstants.vbs. The listing that follows shows the ADMT constants Microsoft Visual Basic® Scripting Edition (VBScript) file. The constants are also provided in the ADMT installation folder, in the TemplateScript.vbs file, in the %systemroot%\WINDOWS\ADMT directory.
To use the sample scripts in the guide, copy the ADMT constants VBScript file into Notepad, and save it as AdmtConstants.vbs. Be sure to save it in the same folder where you plan to save the sample scripts that are provided in this guide.
Option Explicit
'----------------------------------------------------------------------------
' ADMT Scripting Constants
'----------------------------------------------------------------------------
' PasswordOption constants
Const admtComplexPassword = &H0001
Const admtCopyPassword = &H0002
Const admtDoNotUpdatePasswordsForExisting = &H0010
' ConflictOptions constants
Const admtIgnoreConflicting = &H0000
Const admtMergeConflicting = &H0001
Const admtRemoveExistingUserRights = &H0010
Const admtRemoveExistingMembers = &H0020
Const admtMoveMergedAccounts = &H0040
' DisableOption constants
Const admtLeaveSource = &H0000
Const admtDisableSource = &H0001
Const admtTargetSameAsSource = &H0000
Const admtDisableTarget = &H0010
Const admtEnableTarget = &H0020
' SourceExpiration constant
Const admtNoExpiration = -1
' Translation Option
Const admtTranslateReplace = 0
Const admtTranslateAdd = 1
Const admtTranslateRemove = 2
' Report Type
Const admtReportMigratedAccounts = 0
Const admtReportMigratedComputers = 1
Const admtReportExpiredComputers = 2
Const admtReportAccountReferences = 3
Const admtReportNameConflicts = 4
' Option constants
Const admtNone = 0
Const admtData = 1
Const admtFile = 2
Const admtDomain = 3
Const admtRecurse = &H0100
Const admtFlattenHierarchy = &H0000
Const admtMaintainHierarchy = &H0200
Windows NT 4.0 Domain Restructure to an Active Directory Forest
Restructuring Windows NT 4.0 domains to an Active Directory forest involves migrating the contents of a number of Windows NT 4.0 domains into a smaller number of Active Directory domains. By restructuring your environment to an Active Directory forest, you can take advantage of new and enhanced Active Directory features, while reducing the complexity of your organization and the associated administrative costs.
Overview of Restructuring Windows NT 4.0 Domains to an Active Directory Forest
If you are upgrading from Windows NT 4.0 domains to an Active Directory forest, you can consolidate your existing account and resource domains by restructuring them into a smaller number of larger Active Directory domains. You can restructure Windows NT 4.0 domains into Active Directory domains running the Microsoft Windows 2000 Server operating system or Microsoft Windows Server 2003, Standard Edition; Windows Server 2003, Enterprise Edition; or Windows Server 2003, Datacenter Edition operating systems.
Before you begin to restructure your Windows NT 4.0 domains, ensure that your organization has designed an Active Directory logical structure and a site topology. In a Windows 2000 Server environment, ensure that any domains that you plan to use as target domains are operating in Windows 2000 native mode. In a Windows Server 2003 environment, ensure that for any domains that you plan to use as target domains, you have raised the Active Directory domain functional level to Windows 2000 native or Windows Server 2003.
For more information about performing an in-place upgrade of Windows NT 4.0 domains, see Upgrading Windows NT 4.0 Domains to Windows Server 2003 Active Directory (http://go.microsoft.com/fwlink/?LinkId=76027).
After the restructuring process is complete, you can take advantage of the features available in Active Directory. By consolidating multiple Windows NT 4.0 account and resource domains into a single or a few Active Directory domains, you can create an Active Directory domain structure that is more efficient than your existing Windows NT 4.0 domain structure.
Process for Restructuring Windows NT 4.0 Domains to an Active Directory Forest
Restructuring Windows NT 4.0 domains to an Active Directory forest involves completing the necessary planning and preparation tasks in the appropriate order, and then migrating Windows NT 4.0 accounts and resources to an Active Directory domain or domains.
The following figure shows the process for restructuring Windows NT 4.0 domains to an Active Directory forest.
Windows NT 4.0 Domain Migration Background Information
Restructuring Windows NT 4.0 domains involves migrating user, group, and computer objects from a Windows NT 4.0 account or resource source domain into an Active Directory target domain. The target domain can either be a newly created Active Directory domain or a Windows NT 4.0 domain that has had an in-place upgrade.
After the migration process is complete, clients can log on to the original Windows NT 4.0 domain or the new Active Directory domain for authentication. Clients can also access resources in either the source or the target domain, until the source domain is decommissioned.
To successfully consolidate Windows NT 4.0 account and resource domains into Active Directory domains, it is important to be familiar with the capabilities of both operating systems in the context of restructuring. You must also be familiar with the tools that support object migrations.
The migration process between forests is considered to be non-destructive because the migration objects continue to exist in the source domain until the source domain is decommissioned. Because a Windows NT 4.0 domain cannot be part of an Active Directory forest, when objects are migrated from a Windows NT 4.0 domain to an Active Directory domain, they are copied from the source domain to the target domain. The source account primary security identifier (SID) can be maintained in the SID history of the object. You can use the Active Directory Migration Tool (ADMT) version 3 or a third-party restructuring tool to restructure domains.
For more information about domain migration, SIDs, and SID history, see Domain Migration Cookbook (http://go.microsoft.com/fwlink/?LinkId=77370).
Planning to Restructure Windows NT 4.0 Domains to an Active Directory Forest
Planning your domain restructure involves:
• Assigning object locations and roles.
• Creating a test plan.
• Creating a rollback plan for use if the migration fails.
• Planning for user profile migration.
• Establishing administrative procedures to ensure that users can continue to log on to the network and access resources during the migration.
• Creating an end-user communication plan.
The following figure shows the planning steps associated with restructuring Windows NT 4.0 domains to an Active Directory forest.
Assign Object Locations and Roles
Large organizations are likely to be migrating thousands of user, group, and resource objects from numerous Windows NT 4.0 domains to Active Directory domains. It is important to keep track of these objects by assigning them roles and locations in the target domain. It is useful to document the assignments for all of the objects that you are migrating in object assignment worksheets.
Create one worksheet for account objects, such as users, groups, and service accounts, and one worksheet for resource objects, such as workstations, profiles, and domain controllers. In your worksheets, list the source and target locations for all objects to be migrated. For information about identifying service accounts to be migrated, see Service Account Identification, later in this guide.
Note
Built-in accounts (such as Administrators, Users, and Power Users) and well-known accounts (such as Domain Admins and Domain Users) cannot be ADMT migration objects.
For a worksheet to assist you in documenting account object assignments, see "User and Group Object Assignment Table — Windows NT 4.0 Source" (DSSRENT_1.doc) in the Job_Aids_Designing_and_Deploying_Directory_and_Security_Services download of the Job Aids for Windows Server 2003 Deployment Kit (http://go.microsoft.com/fwlink?LinkId=14384).
The following figure shows an example of a completed worksheet for assigning user and group objects.
Creating a resource object assignment worksheet also involves identifying the target organizational unit (OU) for each object and noting the physical location and role in the target domain. For a worksheet to assist you in documenting resource object assignments, see "Resource Object Assignment Table — Windows NT 4.0 Source" (DSSRENT_2.doc) in the Job_Aids_Designing_and_Deploying_Directory_and_Security_Services download of the Job Aids for Windows Server 2003 Deployment Kit (http://go.microsoft.com/fwlink?LinkId=14384).
The following figure shows an example of a completed worksheet for assigning resource objects.
Developing a Migration Test Plan
ADMT v3 does not include a test migration option which was available in previous versions of ADMT. Develop a test plan to assist you in systematically testing each object after it is migrated to the new environment, and identifying and correcting any problems that might occur. Testing to verify that your migration is successful helps ensure that users who are migrated from the source domain to the target domain can:
• Log on.
• Access resources based on group membership.
• Access resources based on user credentials.
Testing also helps ensure that users are able to access the resources that you migrate.
After your testing is complete, you can proceed with migrating small pilot groups and then gradually increase the size of each batch of migration objects in your production environment.
Use the following process to test the migration of your account object and resource objects:
1. Create a test user in the source domain. Include this test user with your migrations.
2. Join that user to the appropriate global groups to enable resource access.
3. Log on to the source domain as the test user, and verify that you can access resources as appropriate.
4. After you translate the user profile, migrate the workstation of the user, migrate the user account, log on to the target domain as the test user, and verify that the user has retained all necessary access and functionality. For example, you might test to verify that:
• The user can log on successfully.
• The user has access to all appropriate resources, such as file and print shares; access to services such as messaging; and access to line-of-business applications. It is especially important to test access to internally developed applications that access database servers.
• The user profile was successfully translated, and the user retains desktop settings, desktop appearance, shortcuts, and access to the My Documents folder. Also, verify that applications appear in and start from the Start menu.
Note
You cannot migrate every user property when you migrate user accounts. For more information about user properties that cannot be migrated, see Migrate User Accounts, later in this guide.
After you migrate resources, log on as the test user in the target domain, and verify that you can access resources as appropriate.
If any steps in the test process fail, identify the source of the problem, and determine whether you can correct the problem before the object needs to be accessible in the target domain. If you cannot correct the problem before access to the object is required, roll back to your original configuration to ensure access to the user or resource object. For more information about creating a rollback plan, see Creating a Rollback Plan, later in this guide.
As part of your test plan, create a migration test matrix. Complete a test matrix for each step that you complete in the migration process. For example, if you migrate 10 batches of users, complete the test matrix 10 times, once for each batch that you migrate. If you migrate 10 member servers, complete the test matrix for each of the 10 servers.
For a worksheet to assist you in creating a test matrix, see Migration Test Matrix (DSSREER_3.doc) in the Job_Aids_Designing_and_Deploying_Directory_and_Security_Services download of the Job Aids for Windows Server 2003 Deployment Kit (http://go.microsoft.com/fwlink?LinkId=14384).
The following figure shows an example of a completed migration test matrix.
Creating a Migration Rollback Plan
Reduce the risk of disrupting end users in your organization by establishing a rollback plan. In general, it is possible to isolate and resolve any problems that occur during each phase of the migration. However, it is important to analyze potential risks and identify the levels of user impact and downtime that might necessitate rolling back the migration. You might be required to roll back your migration if any of the following occur:
• Users cannot log on to their accounts after migration.
• Users cannot access resources after migration.
• User migration is incomplete; for example, passwords did not migrate.
• User migration was successful, but user workstation migration or local profile translation failed.
If user impact or downtime reaches a level that you have defined as unacceptable in your organization, you can implement your rollback plan and continue to operate in your premigration environment. Because the source domain remains intact during the restructure, you can restore the original environment by completing a few key steps.
To roll back to the premigration environment after migrating account objects:
1. Enable the user accounts in the source domain (if you disabled the accounts during the migration process).
2. Notify the users to log off from the target domain.
3. Notify the users to log on to the source domain.
4. Verify that users are able to access resources.
5. Verify that the logon scripts and user profiles for users work as configured in the source domain.
The rollback process for resource objects is similar to that for account objects. To roll back to the premigration environment after migrating resource objects:
1. Change the domain membership for the server or workstation to the source domain.
2. Restart the server or workstation.
3. Log on as a user, and verify that you can access the resource.
Note
If you need to modify objects such as member servers or domain controllers in order to migrate them to the target domain, back up all the data before making the modifications and performing the migration.
Planning for User Profile Migration
User profiles store user data and information about the user’s desktop settings. User profiles can either be roaming or local, and the migration process differs slightly for each.
Profiles are translated during the migration process. Only the primary security identifier (SID) is used to assign a profile to a user; SIDs in SID history are ignored. If you translate profiles in add mode, the user accounts in the target and the source domains have access to the profile. In this way, if you roll the user account back to the source domain, the user account in the source domain can use the profile. If you translate profiles in replace mode, you must retranslate the profile by using a SID mapping file to restore the environment to the premigration state.
Important
If you need to roll back to your original configuration, notify users that profile changes made in the target domain are not reflected in the source domain.
Some organizations might choose not to migrate user profiles. Other organizations might choose to replace users’ workstations during the user account migration process, and use a tool such as the User State Migration Tool (USMT) to migrate user data and settings to the users’ new computers. The following table summarizes the migration requirements for user profiles.
Type Description Migration Requirements
Roaming profiles User profiles are stored centrally on servers. Profiles are available to the user, regardless of the workstation in use. Select the Translate roaming profiles option on the User Options page in the User Account Migration Wizard. Then, translate local user profiles for a batch of users immediately after you migrate those users.
Local profiles User profiles are stored locally on the workstation. When a user logs on to another workstation, a unique local user profile is created. Translate local profiles as a separate step from the user account migration process. Select the User profiles option on the Translate Objects page of the Security Translation Wizard. Translate local user profiles for a batch of users immediately after migrating those users.
Profiles not managed Same as local profiles. Users lose their existing profiles when their user accounts are migrated.
Hardware refresh User state information is stored locally on the workstation. Migrate as a separate step from the user account migration. Migrate the profiles to the user’s new computer by means of a tool such as USMT.
Establishing Administrative Procedures
You must define how the objects that you are migrating are to be administered during the restructure process. By establishing administrative procedures for migration objects, you can preserve the objects both in the source and the target domains. Consequently, you can fall back to the premigration environment, if the restructure process is not successful.
Plan for the administration and management of the following types of account migration objects:
• User accounts, including SIDs
• Global group memberships
Administering User Accounts
During the migration process, user accounts exist in both the source and the target domains. Continue to administer the accounts in the source domain while the migration is taking place. Use the Migrate and merge conflicting objects option in ADMT to remigrate user accounts as often as necessary during the migration process. This ensures that changes made to the account in the source domain are propagated to the account in the target domain. This operation merges the existing account and the new account, so that administration of the object can continue in the source domain for the duration of the migration process.
Administering Global Groups
Continue to administer the groups in the source domain during the migration process. Remigrate groups as often as necessary by using the Migrate and merge conflicting objects option in ADMT. This ensures that changes made to group membership in the source domain are propagated to the group in the target domain.
Creating an End-User Communication Plan
Develop a plan to inform all affected users about the upcoming account migration, to ensure that they understand their responsibilities, the impact of the migration, and who to contact for help and support.
Before you begin the user migration process, send a notice to all users who are scheduled to be migrated. Because you typically migrate users in batches of approximately one hundred users at a time, it is also helpful to send a final notice to the users in each batch two to three days before their batch is scheduled. If your organization maintains an intranet, publish the account migration schedule and the information contained in the user mail on an easily accessible Web page.
Include the following information in your end-user communication.
General Information
Alert users to the fact that their user accounts are scheduled to be migrated to a new domain. Point users to a Web page or internal resource where they can find additional information, and view a migration schedule.
Inform users of their new domain name. Be sure to let them know that their account passwords will not change. Let users know that the original domain account will be disabled immediately following the migration, and the disabled account will be deleted after a specified period of time. This is not needed if they log on with user principal names (UPNs).
Impact
Make sure that users understand that when their account is migrated, they might be unable to access some resources, such as Web sites, shared folders, or resources that individuals in their group or division do not widely use.
Provide information to users about whom to contact for assistance in regaining access to required resources.
Logon Status during Migration
Make sure that users understand that during the migration process, they will be unable to log on to the domain or access e-mail or other resources. Be sure to specify the period of time for which they will be unable to log on.
Premigration Steps
Alert users to any steps that they need to complete before the migration process begins. For example, they must decrypt files encrypted by means of Encrypting File System (EFS). Failure to decrypt encrypted files will result in loss of access to encrypted files following the migration.
Users must also ensure that their computers are connected to the network when their account is scheduled to be migrated.
Expected Changes
Describe other changes that users can expect to experience following the migration, such as changes in use of smart cards, secure e-mail, or instant messaging if applicable.
Scheduling and Support Information
Provide information about where users can go to find more information; for example, an internal Web site where you post information about the migration. Also, provide information about whom to contact if a user has a conflict with the date scheduled for the migration.
Preparing the Source and Target Domains for Restructuring
Before you restructure your Windows NT 4.0 domains, ensure that your target domains are set to a mode or functional level that supports restructuring. In a Windows 2000 Server environment, the target domains must be operating in Windows 2000 native mode. In a Windows Server 2003 environment, the target domains must be at the Windows 2000 native or Windows Server 2003 Active Directory domain functional level.
The following figure shows the steps involved in preparing the source and target domains for restructuring.
Installation of High Encryption Software
ADMT includes the Password Export Server (PES) service to securely copy account passwords from the source domain to the target domain. When you are migrating accounts and passwords between domains, one source domain controller must have the PES service installed and configured. If you are migrating passwords, both the computer on which ADMT is installed in the target domain and the PES service in the source domain require 128-bit high encryption.
Note
Installing high encryption in a Windows NT 4.0 domain does not affect the ability of domain controllers to replicate, nor does it affect the user logon process or users’ access to resources.
This encryption is standard on domain controllers running Windows Server 2003 or Windows 2000 Server Service Pack 3 (SP3) or later. If you plan to install ADMT on a computer that does not support 128-bit high encryption by default, install the 128-bit high encryption pack from Windows 2000 High Encryption Pack (http://go.microsoft.com/fwlink/?LinkId=76037).
For the PES in the Windows NT 4.0 source domain, install the 128-bit high encryption pack from Internet Explorer High Encryption Pack 4.0 (http://go.microsoft.com/fwlink/?LinkId=76038).
Important
The high encryption pack exists for North America Windows NT 4.0 Service Pack 6a. If you need high encryption on an international Windows NT 4.0 domain controller, you need to install Microsoft Internet Explorer® 4.1 or later and the high encryption pack for this Internet Explorer version.
Establishing Required Trusts
Before you can migrate accounts and resources from a Windows NT 4.0 source domain to an Active Directory target domain, you must ensure that the appropriate external trusts are in place. Trust relationships between the domains enable ADMT to migrate objects and translate local user profiles from the source to the target domains. In addition, depending on how trust relationships are configured, users in the source domains can access resources in the target domains. Moreover, users in the target domains can access resources in source domains that have not yet been migrated.
To migrate users and global groups, you must establish a one-way trust between the Windows NT 4.0 source account domain and the target domain, so that the source domain trusts the target domain.
To migrate resources or translate local profiles, you must do one of the following:
• Create a one-way trust between the source resource domain and the target domain, so that the source resource domain trusts the target domain. Migrated users in the target domain can access resources in source resource domains that have not yet been migrated.
• Create a two-way trust between the source resource domain and the target domain. Users that have been migrated to the target domain can access resources in the source domain.
For more information about creating trusts, see Creating Domain and Forest Trusts (http://go.microsoft.com/fwlink/?LinkId=77381).
Establishing Migration Accounts
To migrate accounts and resources between forests, you must establish migration accounts and assign the appropriate credentials to those accounts. ADMT uses the migration accounts to migrate the objects that you identify. Because ADMT requires only a limited set of credentials, creating separate migration accounts enables you to simplify administration. If the migration tasks for your organization are distributed across more than one group, it is helpful to create a migration account for each group involved in performing the migration.
To simplify administration, create a single account in the source domain and a single account in the target domain for all objects. Include the credentials that are required to modify the objects that the account will migrate. These objects include users, global groups, and local profiles. For example, suppose you have a migration account that you use to migrate the following:
• User accounts with SID history
• Global groups with SID history
• Computers
• User profiles
This account must have the following credentials:
• Local administrator or domain administrator credentials in the source domain
• Delegated permission on the user, group, and computer OUs in the target domain, with the extended right to migrate SID history on the user OU
• Local administrator on the computer in the target domain on which ADMT is installed
A migration account that you use to migrate workstations and domain controllers must have local administrator or domain administrator credentials on the workstations in the source domain. Otherwise, the account must have domain administrator credentials on the domain controller, or both.
In the target domain, it is necessary to use an account that has delegated permission on the computer OU and the user OU. You might want to use a separate account for the migration of workstations, if this migration process is delegated to administrators that are in the same location as the workstations.
The following table lists the credentials that are required in the source and target domains for different migration objects.
Migration Object Credentials Necessary in Source Domain Credentials Necessary in Target Domain
User/Group with SID history Local administrator or domain administrator Delegated permission on the user OU or the group OU, extended permission to migrate SID history, and local administrator on the computer on which ADMT is installed.
Computer Domain administrator or local administrator in the source domain and local administrator on each computer Delegated permission on the computer OU and local administrator on the computer on which ADMT is installed.
Profile Local administrator or domain administrator Delegated permission on the user OU and local administrator on the computer on which ADMT is installed.
The following procedures provide examples for creating groups or accounts to migrate accounts and resources. Procedures differ according to whether a one-way trust or a two-way trust exists. The procedure for creating migration groups when a one-way trust exists is more complex than the procedure for when a two-way trust exists. This is because, with a one-way trust, you must add the migration group to the local Administrators group on local workstations.
The sample procedure for creating migration groups when a one-way trust exists involves creating separate groups for migrating accounts and resources; however, you can combine acct_migrators and res_migrators into one group, if you do not need to separate them to delegate different sets of permissions.
To create an account migration group when a one-way trust exists in which the source domain trusts the target domain
1. In the target domain, create a global group called acct_migrators.
2. In the target domain, add the acct_migrators group to the Domain Admins group, or delegate administration of OUs that are targets for account migration to this group.
3. If you are migrating SID history, and you did not place the acct_migrators group in the Domain Admins group, grant the acct_migrators group the Migrate SID History extended permission on the target domain object. To do this:
a. Start Active Directory Users and Computers, right-click the domain object, and then click Properties.
b. Click the Security tab, click Add, and then select acct_migrators.
If the Security tab does not appear, in Active Directory Users and Computers, click View, and then click Advanced Features.
c. In the Permissions for acct_migrators box, click Allow for the Migrate SID History permission.
4. In the source domain, add the acct_migrators group to the Administrators group.
5. On each computer on which you plan to translate local profiles, add the acct_migrators group to the local Administrators group.
To create a resource migration group when a one-way trust exists in which the source domain trusts the target domain
1. In the target domain, create a global group called res_migrators.
2. In the target domain, add the res_migrators group to the Domain Admins group, or delegate administration of OUs that are targets for resource migration to this group.
3. In the source domain, add the res_migrators group to the Administrators group.
4. On each computer that you plan to migrate or on which you plan to perform security translation, add the res_migrators group to the local Administrators group.
To create a resource migration account when a two-way trust exists between the source and target domains
1. In the source domain, create an account called res_migrator.
2. In the source domain, add the res_migrator account to the Domain Admins group. (The Domain Admins group is a member of the local Administrators group on every computer in the domain by default; therefore, you do not need to add it to the local Administrators group on every computer.)
3. In the target domain, delegate permissions on OUs that are targets for resource migration to the res_migrator account.
ADMT v3 also includes database administration roles that you can use to assign a subset of database permissions to users who perform specific migration tasks. The database administration roles and the migration tasks that they can perform are listed in the following table.
Role Migration task
Account migrators Account migration tasks, such as user and group migration.
Resource migrators Resource migration tasks, such as computer migration and security translation. Account migrators also hold the role of resource migrators.
Data readers Queries against that database. Account migrators and resource migrators also hold the role of data readers.
Users who are assigned the role of SQL Server sysadmin hold all ADMT database administration roles. They have permissions to do the following:
• Display database roles and users who hold those roles
• Add groups or users to roles
• Remove groups or users from roles
By default, the local Administrators group is assigned the role of sysadmin and can perform all ADMT database functions.
For more information about using database administrator roles, see "Configure Database Administration Roles" in ADMT v3 Help.
Configuring the Source and Target Domains to Migrate SID History
You can manually configure the source and target domains to migrate SID history before you begin the migration, or you can allow ADMT to configure them automatically the first time that it runs.
To configure the source and target domains manually, complete the following procedures, so that the source and target domains are configured to migrate SID history from a Windows NT 4.0 domain:
• Create a local group in the Windows NT 4.0 source domain to support auditing.
• Enable TCP/IP client support on the source domain PDC.
• Enable auditing in the Active Directory target domain.
• Enable auditing in the Windows NT 4.0 source domain.
To create a local group in the Windows NT 4.0 source domain to support auditing
• On the source domain primary domain controller (PDC), create a local group called SourceDomain$$$, where SourceDomain is the name of your source domain. For example, in a domain named Boston, create a local group called Boston$$$.
To enable TCP/IP client support on the source domain PDC
1. While you are logged on to the PDC in the source domain, click Start, and then click Run.
2. In Open, type regedit, and then click OK.
Caution
Incorrectly editing the registry may severely damage your system. Before making changes to the registry, you should back up any valued data on the computer. You can also use the Last Known Good Configuration startup option if you encounter problems after you make changes.
3. In Registry Editor, navigate to the following registry subkey:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\LSA
4. On the Edit menu, point to New, and then click DWORD Value.
5. Type TcpipClientSupport in the name field, and then press ENTER.
6. Double-click TcpipClientSupport.
7. In Value data, type 1, and then click OK.
8. Close Registry Editor, and then restart the computer.
To enable auditing in the Active Directory target domain
1. Log on as an administrator to any computer in the target domain.
2. Click Start, point to All Programs, point to Administrative Tools, and then click Active Directory Users and Computers.
3. In the console tree, double-click the domain, right-click the Domain Controllers OU, and then click Properties.
4. On the Group Policy tab, click Default Domain Controllers Policy, and then click Edit.
5. Double-click Computer Configuration, double-click Windows Settings, double-click Security Settings, double-click Local Policies, and then click Audit Policy.
6. Double-click Audit account management, and then select both the Success and Failure check boxes.
7. Click Apply, and then click OK.
To enable auditing in the Windows NT 4.0 source domain
1. Log on as an administrator to any computer in the source domain.
2. Click Start, point to Programs, point to Administrative Tools, and then click User Manager for Domains.
3. On the Policies menu, click Audit.
4. Click Audit These Events, and then, next to User and Group Management, select both the Success and Failure check boxes.
Configure the Target Domain OU Structure for Administration
The Active Directory design team designs the OU structure for the target domain. They also define the groups that are responsible for the administration of each OU and the memberships of each group. You can use that information and the following procedure to configure the target domain for administration.
To configure the target domain OU structure for administration
1. Log on as an administrator to any domain controller in the target domain.
2. Start Active Directory Users and Computers, and then create the OU structure specified by your design team.
3. Create administrative groups, and assign users to these groups.
4. Delegate the administration of the OU structure to groups as defined by your design team.
Installing ADMT
When you install ADMT v3, it also installs Windows SQL Server 2000 Desktop Engine (Windows) (WMSDE) by default to use as its data store. Optionally, you can configure ADMT v3 to use a SQL Server 2000 with SP4 Standard or Enterprise Edition database installation that you have previously created.
Prerequisites for Installing ADMT
Before you install ADMT v3, complete the following prerequisites:
• Install Windows Server 2003. Although you can migrate accounts and resources from Windows NT 4.0 and Active Directory environments using ADMT v3, you can only install ADMT v3 on a server running Windows Server 2003.
• Remove all previous versions of ADMT by using Add or Remove Programs from Control Panel. If you attempt to install ADMT v3 on a server that has a previous version of ADMT installed, you receive an error, and the installation does not proceed. If necessary, you can import the database from the previous version of ADMT (protar.mdb) into ADMT v3 during the installation.
• If you do not plan to use the local default database installation, ensure that another SQL Server 2000 database installation is configured with an ADMT instance. For more information about creating an ADMT instance on a SQL Server 2000 database, see Installing ADMT Using a Preconfigured SQL Database.
Installing ADMT Using the Default Database Store
You can use the default database store or a preconfigured SQL database to install ADMT. The most common and recommended installation method is to use the default database store, which the Active Directory Migration Tool Installation Wizard configures automatically.
To install ADMT using the default database store
• From the download location (http://go.microsoft.com/fwlink/?LinkId=75627), double-click admtsetup.exe, which opens the installation wizard.
Wizard Page Action
Welcome to the Active Directory Migration Tool Installation Click Next.
Configuring Components The ADMT database instance (MS_ADMT) is created on the local computer.
Although WMSDE is installed locally by default whether ADMT uses it or not, ADMT disables WMSDE if you specify another database instance on the next wizard page.
Database Selection Specify the database instance you want to connect to. The recommended selection is Use Microsoft SQL Server Desktop Engine (Windows), which configures ADMT v3 to use the locally installed database instance.
If you are using multiple ADMT v3 consoles or have a dedicated database server where you want to centralize your ADMT database, select the Use an existing Microsoft SQL Server option. Specify the server to connect to in the form of Server\Instance. If you select this option, see Installing ADMT Using a Preconfigured SQL Database.
You should configure the SQL Server database instance before you select this option. Although the ADMT v3 installation proceeds if the database cannot be contacted, you cannot use ADMT to migrate accounts or resources until the database instance is created and available.
Active Directory Migration Tool v2 Database Import Although you cannot upgrade an ADMT v2 installation to ADMT v3, you can import data to an ADMT v3 database from an ADMT v2 database.
If you do not want to import data from an ADMT v2 database, select No, do not import data from an ADMT v2 database (Default).
If you want to import data from ADMT v2 into the new ADMT v3 database, select Yes, please import data from an ADMT v2 database.
If you choose to import data, specify the path to the ADMT v2 database file.
The ADMT v2 database file has the file name protar.mdb, and should be located in the directory formerly used for your ADMT v2 installation.
Summary This page summarizes the options you selected. To complete the ADMT v3 installation, click Finish.
Tuesday, June 24, 2008
Thursday, January 10, 2008
MOVE DHCP Database 2003 Server
How to move a DHCP database from a computer that is running Windows NT Server 4.0, Windows 2000, or Windows Server 2003 to a computer that is running Windows Server 2003
View products that this article applies to.
Article ID : 325473
Last Review : March 27, 2007
Revision : 19.5
This article was previously published under Q325473
On This Page
SUMMARY
Export the DHCP database from a server that is running Windows NT Server 4.0 or Windows 2000
Export the DHCP database from a server that is running Microsoft Windows Server 2003
Install the DHCP server service on the server that is running Windows Server 2003
Import the DHCP database
Authorize the DHCP server
REFERENCES
SUMMARY
This step-by-step article describes how to move a Dynamic Host Configuration Protocol (DHCP) database from a computer that is running Microsoft Windows NT Server 4.0, Microsoft Windows 2000, or Microsoft Windows Server 2003 to a computer that is running Windows Server 2003.
Note You can use the Microsoft Windows backup utility (ntbackup.exe) to back up and restore the DHCP database on a single server. Do not use the backup utility to migrate or to move a DHCP database from one DHCP server to another.
Back to the top
Export the DHCP database from a server that is running Windows NT Server 4.0 or Windows 2000
1. Stop the DHCP Server service on the server: a. Log on to the source DHCP server by using an account that is a member of the local Administrators group.
b. Click Start, click Run, type cmd in the Open box, and then click OK.
c. At the command prompt, type net stop dhcpserver, and then press ENTER. You receive a "The Microsoft DHCP Server service is stopping. The Microsoft DHCP Server service was stopped successfully" message.
d. Type exit, and then press ENTER.
2. Compact the DHCP database by using the JetPack utility: a. Click Start, click Run, type cmd in the Open box, and then click OK.
b. At the command prompt, type cd %systemroot%\system32\dhcp, and then press ENTER.
c. Type jetpack dhcp.mdb temp.mdb, and then press ENTER.
d. After the database is compacted successfully, type exit, and then press ENTER.
3. Export the DHCP database by using the DHCP Export Import utility (Dhcpexim.exe). You can obtain this utility from the Windows 2000 Resource Kit Supplement 1. You can also visit the following Microsoft Web site to obtain Dhcpexim.exe:
http://support.microsoft.com/kb/927229 (http://support.microsoft.com/kb/927229)
To export the database: a. Install the Dhcpexim.exe utility, and then start the Dhcpexim.exe utility.
b. At the Welcome to DHCP Export Import tool screen, click Export configuration of the local service to a file, and then click Ok.
c. In the File name box, type the file name for the exported file, and then click Save. For example, type dhcpdatabase.txt.
d. Click the scope or scopes that you want to export, click to select the Disable the selected scopes on local machine before export check box, and then click Export.
e. Click OK.
4. Disable the DHCP Server service on the server. Disabling the DHCP Server service prevents the service from starting after the database has been transferred. To disable the DHCP Server service: a. Click Start, point to Settings, click Control Panel, and then double-click Services.
b. In the Service list, click Microsoft DHCP Server, click Startup, click Disabled, and then click OK.
c. If the service is started, click Stop, and then click Yes to confirm the stopping of the service.
d. Click Close to close the Services dialog box.
Important Dhcpexim.exe is required to move the database successfully from a server that is running Windows 2000 or Windows NT 4.0 to a server that is running Windows Server 2003. Netsh commands for DHCP are not available in Windows NT 4.0.
Note If only the configuration (not the database) is required, use the following command (instead of Dhcpexim.exe) on the Windows 2000-based server that you want to export from. (Do not use Dhcpexim.exe.)
netsh dhcp dump >C:\dhcp.txt
where C:\dhcp.txt is the name and path of the export file that you want to use.
Note The export option does not exist in the netsh command on Windows 2000 Server. The netsh dhcp server dump and netsh dhcp server import commands are not compatible. If you try to import the data that is created by netsh dhcp server dump > C:\dhcp.txt by using netsh DHCP server import > C:\dhcp.txt, you receive the following error message on the Windows Server 2003-based computer:
The request is not supported.
You can migrate the exported configuration file to the new Windows Server 2003 server by using the following command:
netsh exec c:\dhcp.txt
Dhcpexim.exe is not supported in Windows Server 2003. If a database is exported on a Windows 2000-based computer by using Dhcpexim.exe, and you try to import the data to Windows Server 2003, Dhcpexim.exe quits, and you receive the following error message:
An error occurred. An attempt was made to load a program with a incorrect format.
If this behavior occurs, export data from Windows 2000 by using dhcpexim and then import the data on the Windows Server 2003 environment by using netsh DHCP server import xyz.txt.
Back to the top
Export the DHCP database from a server that is running Microsoft Windows Server 2003
To move a DHCP database and configuration from a server that is running Windows Server 2003 to another server that is running Windows Server 2003: 1. Log on to the source DHCP server by using an account that is a member of the local Administrators group.
2. Click Start, click Run, type cmd in the Open box, and then click OK.
3. Type netsh dhcp server export C:\dhcp.txt all, and then press ENTER.
Note You must have local administrator permissions to export the data.
Back to the top
Install the DHCP server service on the server that is running Windows Server 2003
To install the DHCP Server service on an existing Windows Server 2003-based computer: 1. Click Start, click Control Panel, and then double-click Add or Remove Programs.
2. Click Add/Remove Windows Components.
3. In the Windows Component Wizard, click Networking Services in the Components box, and then click Details.
4. Click to select the Dynamic Host Configuration Protocol (DHCP) check box if it is not already selected, and then click OK.
5. In the Windows Components Wizard, click Next to install the selected components. Insert the Windows Server 2003 CD into your computer CD drive or DVD drive if you are prompted to do this. Setup copies the DHCP server and tool files to your computer.
6. When Setup is complete, click Finish.
Back to the top
Import the DHCP database
Note You may receive an "access denied" message during this procedure if you are not a member of the Backup Operators group. If you receive an "Unable to determine the DHCP server version for server" error message, make sure that the DHCP Server service is running on the server and that the user logged on is a member of the local Administrators group.
Important Do not use Dhcpexim.exe to import a DHCP database in Windows Server 2003. Additionally, if the target Windows 2003 server is a member server, and if you plan to promote it to a domain controller, we suggested that you perform the DHCP database migration before promoting it to a domain controller. Although you can migrate the DHCP database to a Windows 2003 domain controller, the migration to a member server will be easier because of the existence of the local administrator account.1. Log on as a user who is an explicit member of the local Administrators group. A user account in a group that is a member of the local Administrators group will not work. If a local Administrators account does not exist for the domain controller, restart the computer in Directory Services Restore Mode, and use the administrator account to import the database as described later in this section.
2. Copy the exported DHCP database file to the local hard disk of the Windows Server 2003-based computer.
3. Verify that the DHCP service is started on the Windows Server 2003-based computer.
4. Click Start, click Run, type cmd in the Open box, and then click OK.
5. At the command prompt, type netsh dhcp server import c:\dhcpdatabase.txt all, and then press ENTER, where c:\dhcpdatabase.txt is the full path and file name of the database file that you copied to the server.
Note When you try to export a DHCP database from a Windows 2000 domain controller to a Windows Server 2003 member server of the domain, you may receive the following error message:
Error initializing and reading the service configuration - Access Denied
Note You must have local administrator permissions to import the data.
6. To resolve this issue, add the Windows Server 2003 DHCP server computer to the DHCP Admins group at the Enterprise level.
7. If the "access is denied" error message occurs after you add the Windows Server 2003 DCHP server computer to the DHCP Admins group at the Enterprise level that is mentioned in step 4, verify that the user account that is currently used to import belongs to the local Administrators group. If the account does not belong to this group, add the account to that group, or log on as a local administrator to complete the import.
Note If the DHCP IMPORT or EXPORT command fails for users who are not explicit members of the local Administrators group, you must apply the following hotfix on the Windows Server 2003-based computer:
833167 (http://support.microsoft.com/kb/833167/) A Volume Shadow Copy Service (VSS) update package is available for Windows Server 2003
8. After you receive the message that the command completed successfully, quit the command prompt.
Back to the top
Authorize the DHCP server
1. Click Start, point to All Programs, point to Administrative Tools, and then click DHCP.
Note You must be logged on to the server by using an account that is a member of the Administrators group. In an Active Directory domain, you must be logged on to the server by using an account that is a member of the Enterprise Administrators group.
2. In the console tree of the DHCP snap-in, expand the new DHCP server. If there is a red arrow in the lower-right corner of the server object, the server has not yet been authorized.
3. Right-click the server object, and then click Authorize.
4. After several moments, right-click the server again, and then click Refresh. A green arrow indicates that the DHCP server is authorized.
Back to the top
REFERENCES
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
323416 (http://support.microsoft.com/kb/323416/) How to install and configure a DHCP server in a workgroup in Windows Server 2003
130642 (http://support.microsoft.com/kb/130642/) How to move a DHCP database from one server to another in Windows NT 4.0 and in Windows 2000
890480 (http://support.microsoft.com/kb/890480/) "Access denied" error message when you use the "netsh dhcp server import" command to import a DHCP database from a Windows NT Server 4.0-based computer to a Windows Server 2003-based computer
Back to the top
--------------------------------------------------------------------------------
APPLIES TO
• Microsoft Windows Server 2003, Datacenter Edition (32-bit x86)
• Microsoft Windows Server 2003, Enterprise Edition (32-bit x86)
• Microsoft Windows Server 2003, Standard Edition (32-bit x86)
• Microsoft Windows Server 2003, Datacenter Edition for Itanium-Based Systems
• Microsoft Windows Server 2003, Enterprise Edition for Itanium-based Systems
• Microsoft Windows NT Server 4.0 Standard Edition
• Microsoft Windows 2000 Server
Back to the top
Keywords: kbnetwork kbhowtomaster KB325473
Back to the top
View products that this article applies to.
Article ID : 325473
Last Review : March 27, 2007
Revision : 19.5
This article was previously published under Q325473
On This Page
SUMMARY
Export the DHCP database from a server that is running Windows NT Server 4.0 or Windows 2000
Export the DHCP database from a server that is running Microsoft Windows Server 2003
Install the DHCP server service on the server that is running Windows Server 2003
Import the DHCP database
Authorize the DHCP server
REFERENCES
SUMMARY
This step-by-step article describes how to move a Dynamic Host Configuration Protocol (DHCP) database from a computer that is running Microsoft Windows NT Server 4.0, Microsoft Windows 2000, or Microsoft Windows Server 2003 to a computer that is running Windows Server 2003.
Note You can use the Microsoft Windows backup utility (ntbackup.exe) to back up and restore the DHCP database on a single server. Do not use the backup utility to migrate or to move a DHCP database from one DHCP server to another.
Back to the top
Export the DHCP database from a server that is running Windows NT Server 4.0 or Windows 2000
1. Stop the DHCP Server service on the server: a. Log on to the source DHCP server by using an account that is a member of the local Administrators group.
b. Click Start, click Run, type cmd in the Open box, and then click OK.
c. At the command prompt, type net stop dhcpserver, and then press ENTER. You receive a "The Microsoft DHCP Server service is stopping. The Microsoft DHCP Server service was stopped successfully" message.
d. Type exit, and then press ENTER.
2. Compact the DHCP database by using the JetPack utility: a. Click Start, click Run, type cmd in the Open box, and then click OK.
b. At the command prompt, type cd %systemroot%\system32\dhcp, and then press ENTER.
c. Type jetpack dhcp.mdb temp.mdb, and then press ENTER.
d. After the database is compacted successfully, type exit, and then press ENTER.
3. Export the DHCP database by using the DHCP Export Import utility (Dhcpexim.exe). You can obtain this utility from the Windows 2000 Resource Kit Supplement 1. You can also visit the following Microsoft Web site to obtain Dhcpexim.exe:
http://support.microsoft.com/kb/927229 (http://support.microsoft.com/kb/927229)
To export the database: a. Install the Dhcpexim.exe utility, and then start the Dhcpexim.exe utility.
b. At the Welcome to DHCP Export Import tool screen, click Export configuration of the local service to a file, and then click Ok.
c. In the File name box, type the file name for the exported file, and then click Save. For example, type dhcpdatabase.txt.
d. Click the scope or scopes that you want to export, click to select the Disable the selected scopes on local machine before export check box, and then click Export.
e. Click OK.
4. Disable the DHCP Server service on the server. Disabling the DHCP Server service prevents the service from starting after the database has been transferred. To disable the DHCP Server service: a. Click Start, point to Settings, click Control Panel, and then double-click Services.
b. In the Service list, click Microsoft DHCP Server, click Startup, click Disabled, and then click OK.
c. If the service is started, click Stop, and then click Yes to confirm the stopping of the service.
d. Click Close to close the Services dialog box.
Important Dhcpexim.exe is required to move the database successfully from a server that is running Windows 2000 or Windows NT 4.0 to a server that is running Windows Server 2003. Netsh commands for DHCP are not available in Windows NT 4.0.
Note If only the configuration (not the database) is required, use the following command (instead of Dhcpexim.exe) on the Windows 2000-based server that you want to export from. (Do not use Dhcpexim.exe.)
netsh dhcp dump >C:\dhcp.txt
where C:\dhcp.txt is the name and path of the export file that you want to use.
Note The export option does not exist in the netsh command on Windows 2000 Server. The netsh dhcp server dump and netsh dhcp server import commands are not compatible. If you try to import the data that is created by netsh dhcp server dump > C:\dhcp.txt by using netsh DHCP server import > C:\dhcp.txt, you receive the following error message on the Windows Server 2003-based computer:
The request is not supported.
You can migrate the exported configuration file to the new Windows Server 2003 server by using the following command:
netsh exec c:\dhcp.txt
Dhcpexim.exe is not supported in Windows Server 2003. If a database is exported on a Windows 2000-based computer by using Dhcpexim.exe, and you try to import the data to Windows Server 2003, Dhcpexim.exe quits, and you receive the following error message:
An error occurred. An attempt was made to load a program with a incorrect format.
If this behavior occurs, export data from Windows 2000 by using dhcpexim and then import the data on the Windows Server 2003 environment by using netsh DHCP server import xyz.txt.
Back to the top
Export the DHCP database from a server that is running Microsoft Windows Server 2003
To move a DHCP database and configuration from a server that is running Windows Server 2003 to another server that is running Windows Server 2003: 1. Log on to the source DHCP server by using an account that is a member of the local Administrators group.
2. Click Start, click Run, type cmd in the Open box, and then click OK.
3. Type netsh dhcp server export C:\dhcp.txt all, and then press ENTER.
Note You must have local administrator permissions to export the data.
Back to the top
Install the DHCP server service on the server that is running Windows Server 2003
To install the DHCP Server service on an existing Windows Server 2003-based computer: 1. Click Start, click Control Panel, and then double-click Add or Remove Programs.
2. Click Add/Remove Windows Components.
3. In the Windows Component Wizard, click Networking Services in the Components box, and then click Details.
4. Click to select the Dynamic Host Configuration Protocol (DHCP) check box if it is not already selected, and then click OK.
5. In the Windows Components Wizard, click Next to install the selected components. Insert the Windows Server 2003 CD into your computer CD drive or DVD drive if you are prompted to do this. Setup copies the DHCP server and tool files to your computer.
6. When Setup is complete, click Finish.
Back to the top
Import the DHCP database
Note You may receive an "access denied" message during this procedure if you are not a member of the Backup Operators group. If you receive an "Unable to determine the DHCP server version for server" error message, make sure that the DHCP Server service is running on the server and that the user logged on is a member of the local Administrators group.
Important Do not use Dhcpexim.exe to import a DHCP database in Windows Server 2003. Additionally, if the target Windows 2003 server is a member server, and if you plan to promote it to a domain controller, we suggested that you perform the DHCP database migration before promoting it to a domain controller. Although you can migrate the DHCP database to a Windows 2003 domain controller, the migration to a member server will be easier because of the existence of the local administrator account.1. Log on as a user who is an explicit member of the local Administrators group. A user account in a group that is a member of the local Administrators group will not work. If a local Administrators account does not exist for the domain controller, restart the computer in Directory Services Restore Mode, and use the administrator account to import the database as described later in this section.
2. Copy the exported DHCP database file to the local hard disk of the Windows Server 2003-based computer.
3. Verify that the DHCP service is started on the Windows Server 2003-based computer.
4. Click Start, click Run, type cmd in the Open box, and then click OK.
5. At the command prompt, type netsh dhcp server import c:\dhcpdatabase.txt all, and then press ENTER, where c:\dhcpdatabase.txt is the full path and file name of the database file that you copied to the server.
Note When you try to export a DHCP database from a Windows 2000 domain controller to a Windows Server 2003 member server of the domain, you may receive the following error message:
Error initializing and reading the service configuration - Access Denied
Note You must have local administrator permissions to import the data.
6. To resolve this issue, add the Windows Server 2003 DHCP server computer to the DHCP Admins group at the Enterprise level.
7. If the "access is denied" error message occurs after you add the Windows Server 2003 DCHP server computer to the DHCP Admins group at the Enterprise level that is mentioned in step 4, verify that the user account that is currently used to import belongs to the local Administrators group. If the account does not belong to this group, add the account to that group, or log on as a local administrator to complete the import.
Note If the DHCP IMPORT or EXPORT command fails for users who are not explicit members of the local Administrators group, you must apply the following hotfix on the Windows Server 2003-based computer:
833167 (http://support.microsoft.com/kb/833167/) A Volume Shadow Copy Service (VSS) update package is available for Windows Server 2003
8. After you receive the message that the command completed successfully, quit the command prompt.
Back to the top
Authorize the DHCP server
1. Click Start, point to All Programs, point to Administrative Tools, and then click DHCP.
Note You must be logged on to the server by using an account that is a member of the Administrators group. In an Active Directory domain, you must be logged on to the server by using an account that is a member of the Enterprise Administrators group.
2. In the console tree of the DHCP snap-in, expand the new DHCP server. If there is a red arrow in the lower-right corner of the server object, the server has not yet been authorized.
3. Right-click the server object, and then click Authorize.
4. After several moments, right-click the server again, and then click Refresh. A green arrow indicates that the DHCP server is authorized.
Back to the top
REFERENCES
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
323416 (http://support.microsoft.com/kb/323416/) How to install and configure a DHCP server in a workgroup in Windows Server 2003
130642 (http://support.microsoft.com/kb/130642/) How to move a DHCP database from one server to another in Windows NT 4.0 and in Windows 2000
890480 (http://support.microsoft.com/kb/890480/) "Access denied" error message when you use the "netsh dhcp server import" command to import a DHCP database from a Windows NT Server 4.0-based computer to a Windows Server 2003-based computer
Back to the top
--------------------------------------------------------------------------------
APPLIES TO
• Microsoft Windows Server 2003, Datacenter Edition (32-bit x86)
• Microsoft Windows Server 2003, Enterprise Edition (32-bit x86)
• Microsoft Windows Server 2003, Standard Edition (32-bit x86)
• Microsoft Windows Server 2003, Datacenter Edition for Itanium-Based Systems
• Microsoft Windows Server 2003, Enterprise Edition for Itanium-based Systems
• Microsoft Windows NT Server 4.0 Standard Edition
• Microsoft Windows 2000 Server
Back to the top
Keywords: kbnetwork kbhowtomaster KB325473
Back to the top
Moving Security Accounts between MS SQL server
How to transfer logins and passwords between instances of SQL Server
View products that this article applies to.
Article ID : 246133
Last Review : November 2, 2007
Revision : 8.1
This article was previously published under Q246133
On This Page
SUMMARY
How to transfer logins and passwords between servers that are running SQL Server 7.0
How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000
How to transfer logins and passwords between instances of SQL Server 2005
A complete resolution to transfer logins and passwords between different versions of SQL Server
Method 1
Method 2
Remarks
SUMMARY
After you move databases to a new server, users may not be able to log in to the new server. Instead, they receive the following error message:
Msg 18456, Level 16, State 1
Login failed for user '%ls'.
You must transfer the logins and passwords to the new server. This article describes how you transfer logins and passwords to a new server.
Back to the top
How to transfer logins and passwords between servers that are running SQL Server 7.0
The SQL Server 7.0 Data Transformation Services (DTS) Object Transfer feature transfers logins and users between two servers, but it does not transfer the passwords for SQL Server authenticated logins. To transfer logins and passwords from a server that is running SQL Server 7.0 to another server that is running SQL Server 7.0, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.
Back to the top
How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000
To transfer logins and passwords from a SQL Server 7.0 server to an instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS Package Transfer Logins Task in SQL Server 2000. To do this, follow these steps: 1. Connect to the SQL Server 2000 destination server, move to the Data Transformation Services in SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.
2. After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information about the Source, Destination and Logins tabs as appropriate.
Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under a Domain Account to complete the task.
Note The DTS method will transfer the passwords but not the original SID. If a login is not created by using the original SID and user databases are also transferred to a new server, the database users will be orphaned from the login. To transfer the original SID and bypass the orphaned users, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.
Back to the top
How to transfer logins and passwords between instances of SQL Server 2005
For more information about how to transfer the logins and passwords between instances of SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:
918992 (http://support.microsoft.com/kb/918992/) How to transfer the logins and the passwords between instances of SQL Server 2005
Back to the top
A complete resolution to transfer logins and passwords between different versions of SQL Server
To do this, use one of the following methods.
Notes• The scripts in the following methods create two stored procedures that are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure in your master database.
• The scripts are dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server. Selecting directly from system tables is discouraged.
• Review the remarks at the end of this article for important information about the steps in the methods.
• Method 2 assigns logins to roles.
Back to the top
Method 1
This method applies to the following scenarios:• You transfer logins and passwords from SQL Server 7.0 to SQL Server 7.0.
• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2000.
• You transfer logins and passwords between servers that are running SQL Server 2000.
To transfer logins and passwords between different versions of SQL Server, follow these steps:1. Run the following script on the source SQL Server. Continue to step 2 when you finish creating the sp_help_revlogin stored procedure. ----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
2. After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. For example:EXEC master..sp_help_revlogin
Back to the top
Method 2
This method applies to the following scenarios:• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.
• You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.
• You assign logins to roles.
To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:1. Run the following script on the source SQL Server. USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005
@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0
AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN -- NT login has access
SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END
SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GO
2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.
Note If the source SQL Server contains a login that has a blank password, the output contains a statement that resembles the following. CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID
Back to the top
Remarks
• Review the output script carefully before you run it on the destination SQL Server. If you have to transfer logins to an instance of SQL Server in a different domain than the source instance of SQL Server, edit the script generated by the sp_help_revlogin procedure, and replace the domain name with the new domain in the sp_grantlogin statements. Because the integrated logins granted access in the new domain will not have the same SID as the logins in the original domain, the database users will be orphaned from these logins. To resolve these orphaned users, see the articles referenced in the following bullet item. If you transfer integrated logins between instances of SQL Servers in the same domain, the same SID is used and the user is not likely to be orphaned.
• After you move the logins, users may not have permissions to access databases that have also been moved. This problem is described as an "orphaned user". If you try to grant the login access to the database, it may fail indicating the user already exists:
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
For instructions about how to map the logins to the database users to resolve orphaned SQL Server logins and integrated logins, see the following article in the Microsoft Knowledge Base:
240872 (http://support.microsoft.com/kb/240872/) How to resolve permission issues when you move a database between servers that are running SQL Server
For instructions about using the sp_change_users_login stored procedure to correct the orphaned users one-by-one (this will only address users orphaned from standard SQL logins), see the following article in the Microsoft Knowledge Base:
274188 (http://support.microsoft.com/kb/274188/) "Troubleshooting Orphaned Users" topic in Books Online is incomplete
• If the transfer of logins and passwords is part of a move of databases to a new server running SQL Server, see the following article in the Microsoft Knowledge Base for a description of the workflow and steps involved:
314546 (http://support.microsoft.com/kb/314546/) How to move databases between computers that are running SQL Server
• You can do this because of the @encryptopt parameter in the sp_addlogin system stored procedure, that allows a login to be created by using the encrypted password. For more information about this procedure, see the "sp_addlogin (T-SQL)" topic in SQL Server Books Online.
• By default, only members of the sysadminfixed server role can select from the sysxlogins table. Unless a member of the sysadmin role grants the necessary permissions, end users cannot create or run these stored procedures.
• This approach does not try to transfer the default database information for a particular login because the default database may not always exist on the destination server. To define the default database for a login, you can use the sp_defaultdb system stored procedure by passing it the login name and the default database as arguments. For more information about using this procedure, see the "sp_defaultdb" topic in SQL Server Books Online.
• During a transfer of logins between instances of SQL Server, if the sort order of the source server is case-insensitive and the sort order of the destination server is case-sensitive, you must enter all alphabetical characters in passwords as uppercase characters after the transfer of logins to the destination server. If the sort order of the source server is case-sensitive and the sort order of the destination server is case-insensitive, you will not be able to log in with the logins transferred using the procedure outlined in this article, unless the original password contains no alphabetical characters or unless all alphabetical characters in the original password are uppercase characters. If both servers are case-sensitive or both servers are case-insensitive, you will not experience this problem. This is a side effect of the way that SQL Server handles passwords. For more information, see the "Effect on Passwords of Changing Sort Orders" topic in SQL Server 7.0 Books Online.
• When you run the output from the sp_help_revlogin script on the destination server, if the destination server already has a login defined with the same name as one of the logins on the script output, you may see the following error upon execution of the output of the sp_help_revlogin script:
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'test1' already exists.
Likewise, if a different login exists with the same SID value on this server as the one you are trying to add, you receive the following error message:
Server: Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93
Supplied parameter @sid is in use.
Therefore, you must carefully review the output from these commands, examine the contents of the sysxlogins table, and address these errors accordingly.
• The SID value for a particular login is used as the basis for implementing database level access in SQL Server. Therefore, if the same login has two different values for the SID at the database level (in two different databases on that server), the login will only have access to that database whose SID matches the value in syslogins for that login. Such a situation might occur if the two databases in question have been consolidated from two different servers. To resolve this problem, the login in question would have to be manually removed from the database that has a SID mismatch by using the sp_dropuser stored procedure, and then added again by using the sp_adduser stored procedure.
Back to the top
--------------------------------------------------------------------------------
APPLIES TO
• Microsoft SQL Server 7.0 Standard Edition
• Microsoft SQL Server 2000 Personal Edition
• Microsoft SQL Server 2000 Standard Edition
• Microsoft SQL Server 2000 Workgroup Edition
• Microsoft SQL Server 2000 Developer Edition
• Microsoft SQL Server 2000 Enterprise Edition
• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Workgroup Edition
• Microsoft SQL Server 2005 Developer Edition
• Microsoft SQL Server 2005 Enterprise Edition
Back to the top
Keywords: kbhowtomaster kbinfo KB246133
Back to the top
View products that this article applies to.
Article ID : 246133
Last Review : November 2, 2007
Revision : 8.1
This article was previously published under Q246133
On This Page
SUMMARY
How to transfer logins and passwords between servers that are running SQL Server 7.0
How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000
How to transfer logins and passwords between instances of SQL Server 2005
A complete resolution to transfer logins and passwords between different versions of SQL Server
Method 1
Method 2
Remarks
SUMMARY
After you move databases to a new server, users may not be able to log in to the new server. Instead, they receive the following error message:
Msg 18456, Level 16, State 1
Login failed for user '%ls'.
You must transfer the logins and passwords to the new server. This article describes how you transfer logins and passwords to a new server.
Back to the top
How to transfer logins and passwords between servers that are running SQL Server 7.0
The SQL Server 7.0 Data Transformation Services (DTS) Object Transfer feature transfers logins and users between two servers, but it does not transfer the passwords for SQL Server authenticated logins. To transfer logins and passwords from a server that is running SQL Server 7.0 to another server that is running SQL Server 7.0, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.
Back to the top
How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000
To transfer logins and passwords from a SQL Server 7.0 server to an instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS Package Transfer Logins Task in SQL Server 2000. To do this, follow these steps: 1. Connect to the SQL Server 2000 destination server, move to the Data Transformation Services in SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.
2. After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information about the Source, Destination and Logins tabs as appropriate.
Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under a Domain Account to complete the task.
Note The DTS method will transfer the passwords but not the original SID. If a login is not created by using the original SID and user databases are also transferred to a new server, the database users will be orphaned from the login. To transfer the original SID and bypass the orphaned users, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.
Back to the top
How to transfer logins and passwords between instances of SQL Server 2005
For more information about how to transfer the logins and passwords between instances of SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:
918992 (http://support.microsoft.com/kb/918992/) How to transfer the logins and the passwords between instances of SQL Server 2005
Back to the top
A complete resolution to transfer logins and passwords between different versions of SQL Server
To do this, use one of the following methods.
Notes• The scripts in the following methods create two stored procedures that are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure in your master database.
• The scripts are dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server. Selecting directly from system tables is discouraged.
• Review the remarks at the end of this article for important information about the steps in the methods.
• Method 2 assigns logins to roles.
Back to the top
Method 1
This method applies to the following scenarios:• You transfer logins and passwords from SQL Server 7.0 to SQL Server 7.0.
• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2000.
• You transfer logins and passwords between servers that are running SQL Server 2000.
To transfer logins and passwords between different versions of SQL Server, follow these steps:1. Run the following script on the source SQL Server. Continue to step 2 when you finish creating the sp_help_revlogin stored procedure. ----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
2. After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. For example:EXEC master..sp_help_revlogin
Back to the top
Method 2
This method applies to the following scenarios:• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.
• You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.
• You assign logins to roles.
To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:1. Run the following script on the source SQL Server. USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005
@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0
AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN -- NT login has access
SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END
SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GO
2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.
Note If the source SQL Server contains a login that has a blank password, the output contains a statement that resembles the following. CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID
Back to the top
Remarks
• Review the output script carefully before you run it on the destination SQL Server. If you have to transfer logins to an instance of SQL Server in a different domain than the source instance of SQL Server, edit the script generated by the sp_help_revlogin procedure, and replace the domain name with the new domain in the sp_grantlogin statements. Because the integrated logins granted access in the new domain will not have the same SID as the logins in the original domain, the database users will be orphaned from these logins. To resolve these orphaned users, see the articles referenced in the following bullet item. If you transfer integrated logins between instances of SQL Servers in the same domain, the same SID is used and the user is not likely to be orphaned.
• After you move the logins, users may not have permissions to access databases that have also been moved. This problem is described as an "orphaned user". If you try to grant the login access to the database, it may fail indicating the user already exists:
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
For instructions about how to map the logins to the database users to resolve orphaned SQL Server logins and integrated logins, see the following article in the Microsoft Knowledge Base:
240872 (http://support.microsoft.com/kb/240872/) How to resolve permission issues when you move a database between servers that are running SQL Server
For instructions about using the sp_change_users_login stored procedure to correct the orphaned users one-by-one (this will only address users orphaned from standard SQL logins), see the following article in the Microsoft Knowledge Base:
274188 (http://support.microsoft.com/kb/274188/) "Troubleshooting Orphaned Users" topic in Books Online is incomplete
• If the transfer of logins and passwords is part of a move of databases to a new server running SQL Server, see the following article in the Microsoft Knowledge Base for a description of the workflow and steps involved:
314546 (http://support.microsoft.com/kb/314546/) How to move databases between computers that are running SQL Server
• You can do this because of the @encryptopt parameter in the sp_addlogin system stored procedure, that allows a login to be created by using the encrypted password. For more information about this procedure, see the "sp_addlogin (T-SQL)" topic in SQL Server Books Online.
• By default, only members of the sysadminfixed server role can select from the sysxlogins table. Unless a member of the sysadmin role grants the necessary permissions, end users cannot create or run these stored procedures.
• This approach does not try to transfer the default database information for a particular login because the default database may not always exist on the destination server. To define the default database for a login, you can use the sp_defaultdb system stored procedure by passing it the login name and the default database as arguments. For more information about using this procedure, see the "sp_defaultdb" topic in SQL Server Books Online.
• During a transfer of logins between instances of SQL Server, if the sort order of the source server is case-insensitive and the sort order of the destination server is case-sensitive, you must enter all alphabetical characters in passwords as uppercase characters after the transfer of logins to the destination server. If the sort order of the source server is case-sensitive and the sort order of the destination server is case-insensitive, you will not be able to log in with the logins transferred using the procedure outlined in this article, unless the original password contains no alphabetical characters or unless all alphabetical characters in the original password are uppercase characters. If both servers are case-sensitive or both servers are case-insensitive, you will not experience this problem. This is a side effect of the way that SQL Server handles passwords. For more information, see the "Effect on Passwords of Changing Sort Orders" topic in SQL Server 7.0 Books Online.
• When you run the output from the sp_help_revlogin script on the destination server, if the destination server already has a login defined with the same name as one of the logins on the script output, you may see the following error upon execution of the output of the sp_help_revlogin script:
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'test1' already exists.
Likewise, if a different login exists with the same SID value on this server as the one you are trying to add, you receive the following error message:
Server: Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93
Supplied parameter @sid is in use.
Therefore, you must carefully review the output from these commands, examine the contents of the sysxlogins table, and address these errors accordingly.
• The SID value for a particular login is used as the basis for implementing database level access in SQL Server. Therefore, if the same login has two different values for the SID at the database level (in two different databases on that server), the login will only have access to that database whose SID matches the value in syslogins for that login. Such a situation might occur if the two databases in question have been consolidated from two different servers. To resolve this problem, the login in question would have to be manually removed from the database that has a SID mismatch by using the sp_dropuser stored procedure, and then added again by using the sp_adduser stored procedure.
Back to the top
--------------------------------------------------------------------------------
APPLIES TO
• Microsoft SQL Server 7.0 Standard Edition
• Microsoft SQL Server 2000 Personal Edition
• Microsoft SQL Server 2000 Standard Edition
• Microsoft SQL Server 2000 Workgroup Edition
• Microsoft SQL Server 2000 Developer Edition
• Microsoft SQL Server 2000 Enterprise Edition
• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Workgroup Edition
• Microsoft SQL Server 2005 Developer Edition
• Microsoft SQL Server 2005 Enterprise Edition
Back to the top
Keywords: kbhowtomaster kbinfo KB246133
Back to the top
Subscribe to:
Posts (Atom)