Microsoft KB Archive/252519

From BetaArchive Wiki
Knowledge Base


BUG: Transfer Manager Fails When Application Role Defined

Article ID: 252519

Article Last Modified on 10/17/2003



APPLIES TO

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition



This article was previously published under Q252519

BUG #: 57439 (SQLBUG_70)

SYMPTOMS

If you run the Transfer Manager to transfer objects and users or roles between databases, the process may fail when an you define an application role. The Transfer Manager fails with the following error message:

Failed to transfer objects from Microsoft SQL Server to Microsoft SQL Server.

After you press OK and double-click the error in the Executing DTS Package dialog box, the following error displays:

[Microsoft][ODBC SQL Server Driver][SQL Server] The application role password must not be NULL.

CAUSE

There is a conflict between two of the steps that the Transfer Manager performs. In the first step, the Transfer Manager sets the password to NULL when creating scripts from the source database. The following informational message is found in the file of the following type:

SourceServer.SourceDatabase.Log file (the file is in the script directory selected in the wizard).


Warning::

Transfer Status: Scripting Users
The Application DatabaseRole 'RoleName' was scripted with a Null password for security reasons.

This warning indicates that the application role password was set to NULL when scripted out.

The second step occurs during the transfer into the destination database. As the first error message suggests, the transfer requires that the password for the application role, on the destination database, not be null. The following error is found in the file of the following type:

DestinationServer.DestinationDatabase.Log file.


Error::

In file SourceServer.SourceDatabase.USR in statement on line 15.

Transfer Status: Creating Groups on destination database [Microsoft][ODBC SQL Server Driver][SQL Server]The application role password must not be NULL.

WORKAROUND

To work around this problem, use one of these alternatives:

  • To transfer the application roles:


    1. Using the Generate SQL Scripts Utility, script the roles and drop the application roles from the source database.NOTE: Remember that at least one object needs to be scripted in order for users or roles to be scripted.


    1. Perform the transfer.
    2. Re-create the application roles on both the source and destination servers from the scripts generated earlier.
  • Do not transfer any users and roles:


    1. Clear the Use Default Options check box in the wizard.
    2. Clear the Transfer database users and database roles check box.


STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.


Additional query words: transfer manager applicaton role

Keywords: kbbug kbpending KB252519