DWP 18.05 upgrade scenarios if Windows authentication is used for a SmartIT or DWP database user

Version 2
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    MyIT Digital Workplace


    APPLIES TO:

    DWP 18.05



    PROBLEM:

    If a Windows authentication user is used to connect to Microsoft SQL Server the DWP 18.05 Installer will fail on the data transfer stage with the following error:

    SEVERE,com.bmc.install.product.projectgalileo.rdbms.migration.DbMigrationInstallerTask,
    THROWABLE EVENT {Description=[Error migrating data],Detail=[]},

    The data transfer tool log will also contain this error:

    Exception in thread "main" org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
    Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user WindowsDomain\SmartIT_Business'. ClientConnectionId:8a45de2a-6bdc-412b-842d-91490dd212af
     


    SOLUTION:

    Additional Windows authentication specific configuration should be applied to the DWP Data Transfer tool to migrate data from the old SmartIT\MyIT database to the new DWP database.
     
    DWP 18.05 Installer data migration operations:

       
    1. Upgrade existing SmartIT\MyIT database to 18.05 (using user credentials from \Tomcat8.5\conf\Catalina\localhost\ux.xml)
    2.  
    3. Create tables in the DWP database
    4.  
    5. Perform SmartIT\MyIT database dump to the %MYIT_HOME% \DWP\data-transfer\scripts export.zip file (with user credentials from \Tomcat8.5\conf\Catalina\localhost\ux.xml)
    6.  
    7. Perform import of export.zip file to the DWP database (with user credentials for database configuration step in Installer )
    According to this flow there are four possible scenarios with a fail point for the Installer with user credentials: 
                                                                                                                                                           
    ScenarioSmartIT\MyIT database user authentication type
    (used for data dump)
    DWP database user authentication type
    (used for data import)
    DWP 18.05 Installer
    failing point for
    the Data Transfer
    #1SQL ServerSQL Serverno failing point
    #2WindowsSQL Serveron data dump stage
    #3SQL ServerWindowson data import stage
    #4WindowsWindowson data dump stage
      
    Before continuing with installation steps please check which scenario is matching your environment configuration, where (old) SmartIT\MyIT database user or (new) DWP database user could have Windows authentication or SQL Server authentication. If scenario #2 #3 or #4 is matching your environment, please follow the next steps. 
      
    Steps to run DWP 18.05 Installer and Data Transfer tool:

     1. Run the DWP 18.05 Installer according to 18.05 upgrade instruction and process installation until it fails with this error: 

    SEVERE,com.bmc.install.product.projectgalileo.rdbms.migration.DbMigrationInstallerTask,
    THROWABLE EVENT {Description=[Error migrating data],Detail=[]},
      
    The Data Transfer import or export log at the folder %MYIT_HOME% \DWP\data-transfer\scripts will also have this error: 

    Exception in thread "main" org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
    Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user WindowsDomain\SmartIT_Business'. ClientConnectionId:8a45de2a-6bdc-412b-842d-91490dd212af

    2. Close the DWP 18.05 Installer and in the (new) DWP Database check if tables are present and all tables are empty. 

    MyIT\SmartIT database data dump.

    3.1   Scenario #2 or #4: Data dump is a fail point: 

    a) Set Windows authentication   MyIT\SmartIT database user in the file %MYIT_HOME% \DWP\data-transfer\connection\mssql.connection.properties: 
      
    business_user=domain\\SmartIT_Business  
    business_password=password
    system_user=domain\\
    SmartIT_Business  
    jdbc_url=jdbc:sqlserver://dbserver:1433;DatabaseName=SmartIT;integratedSecurity=true
      
    ...where SmartIT_Business is a   Windows authentication user for the    SmartIT\MyIT database and domain is Smart IT\MyIT Server domain. 

    b) Open the %MYIT_HOME% \DWP\data-transfer\scripts folder and add the following additional option to the data_dump.bat script: 

    -Djava.library.path="%MyIT_HOME%\DWP\dll"

    c) Run data_dump.bat as a SmartIT_Business user  . Please note that it’s   VERY IMPORTANT to execute data_dump.bat as a   SmartIT_Business user to get the correct schema from the SmartIT\MyIT database. 
      
    3.2 The   scenario #3: Data dump is not a fail point. 

    Check if export.zip file is present at the %MYIT_HOME% \DWP\data-transfer\scripts folder. Verify if export.zip has xml files. If for some reason it’s has only one xml file with zero size: 

    a) Configure %MYIT_HOME% \DWP\data-transfer\connection\mssql.connection.properties file for the   SmartIT\MyIT database user, for example: 

    host=MSSQL_host 
    port=1433 
    business_db_name= 
    business_user= 
    business_password=password 
    system_db_name= 
    system_user= 
    system_password= password 
      
    b) Run data_dump.bat 

    For all #2 #3 or #4 scenarios, the result of this step is the export.zip archive with more than one non-zero size .xml file in it. 
      
    4.  DWP database data import. 

    4.1   Scenario #3 or #4: Data import is a fail point. 

    a) Set Windows authentication   DWP configuration   and user in the file%MYIT_HOME% \DWP\data-transfer\connection\mssql.connection.properties in the same format as for 
    step #4.a but for   DWP database

    business_user=domain\\DWP_Business  
    business_password=password
    system_user=domain\\
    DWP_Business  
    jdbc_url=jdbc:sqlserver://dbserver:1433;DatabaseName=DWP;integratedSecurity=true
      
    b) Open %MYIT_HOME% \DWP\data-transfer\scripts folder and add additional option to the run_import.bat script: 

    -Djava.library.path="%MyIT_HOME%\DWP\dll"
      
    c) Run run_import.bat as a  DWP_Business  user.
      
    For   Scenario #2

    a) Configure %MYIT_HOME% \DWP\data-transfer\connection\mssql.connection.properties file for the   DWP database user, as example: 

    host=MSSQL_host 
    port=1433 
    business_db_name= 
    business_user= 
    business_password=password 
    system_db_name= 
    system_user= 
    system_password= password 

    b) Run run_import.bat 

    For all #2 #3 or #4 scenarios, the result of this step is a non-empty tables in the   DWP database
      
    5. In the   DWP database

    Open a new SQL tab and run this query: 

    insert into CONFIGURATION_PARAMS(ID, TENANT_ID, KEY_NAME, VALUE) values('ee8992c8-3279-4da4-bf2a-fb98edbfb8b2','000000000000001','isDwpDatabaseMigrationReady','true')

    'isDwpDatabaseMigrationReady'  is a marker for DWP 18.05 Installer do not perform any operations with the database so on this stage it’s upgrading the file system only on the MyIT\SmartIT server. 
      
    6.  Run the DWP 18.05 Installer again and perform the same input for the DWP database user.  

    Do NOT select the checkbox: "I have already migrated the Digital Workplace Social and Notifications data".
    To run Social Migration manually after Installer complete upgrade.

    7.  Get the social2.war from the DWP 18.05.01 patch (available on EPD) and replace it in the "%MYIT_HOME% \DWP\social2-migration" directory and run the social migration manually according to the 18.05 upgrade instructions.

    8.  Restart DWP Tomcat
     

     


    Article Number:

    000161611


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles