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.