Open Forum

MR 2012 CU16 on SQL 2019

  • 1.  MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 20 days ago
    Edited by Mark Ehleben 20 days ago
    Trying to install MR 2012 CU16 on SQL 2019.  According to the CU16 HF list, SQL 2019 is supported with BugID 337780.

    Getting the following error attempting to create a new SL DataMart:
    The database deployment failed. Additional information: Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package. ---> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Unable to connect to master or target server 'ManagementReporterDM'. You must have a user with the same password in master or target server 'ManagementReporterDM'.

    I successfully migrated the ManagementReporter DB from another server and that one is connected fine. (When I tried to create a new ManagementReporter database on SQL 2019, I got the same message, only with the ManagementReporter DB name instead).

    I then tried to create ManagementReporterDM to a different SQL Server (2014) and it creates fine.

    Wondering if anyone else out there has been successful in creating an MR database on SQL 2019 (or any version higher than 2014).

    Trying to determine if I have another variable denying this in SQL 2019 that I need to figure out.  Have done some research that one issue may be that the SSDT tools are not up to date, but I have VS 2019 installed (which comes with it) and have run VS updates to get the latest version.

    Thanks.

    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 19 days ago
    Hi Mark and DSLUG, please look to the latest MR 2012 CU16 5th version (that's a mouth full) for SQL 2019 compatibility which should do the trick: https://community.dynamics.com/gp/b/dynamicsgp/posts/management-reporter-feature-and-version-number-summary



    ------------------------------
    Derek Krebs
    Sr Consultant
    MSX Group
    Carlsbad, CA
    Derek.Krebs@msxgroup.com
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 19 days ago
    Derek,

    Thanks for your input, but I have already downloaded the latest build from Nov 2019 but it does not work for building an SL DataMart.  This is supposed to include fix 337780, which provides for SQL 2019 support.


    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 19 days ago
    Are you using the same username on the new server against the migrated database from the old server?  If so, you may have a SID mismatch on the username IN the database vs the SID for the same username in the new server.  Read the article at the link below for info on how to transfer users (and preserve the SID) from one server to another:

    https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

    Hope that helps!

    Joe

    ------------------------------
    Joe Miller
    Director of Systems Architecture
    Harrison, Walker & Harper
    Paris TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 19 days ago
    Joe,

    I am using the "Use Windows Authentication" option to connect to the SL System DB and create the MR DataMart.  Has always worked in previous MR server moves or new installations.  This method works when connecting to another SQL Server (2014) but not this one.  The user has sysadmin rights to SQL.

    The SL DB Sync ownership and security process has been run, so I thought that always resolves any SID issues.

    If I treat this as a new MR installation (do not migrate the ManagementReporter DB) and try to create a new reporting DB, it also fails in that DB creation.

    If there was a way to create a blank MR datamart on SQL 2014 and "attach" and restore to SQL 2019, then somehow connect it to the MR installation w/out going through the datamart build process I'd be willing to try that.

    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 19 days ago
    Edited by Joe Miller 19 days ago
    Well, the creating the new DB and failing definitely signals that it's probably not the SID's being out of sync.  Did you try using SQL authentication to create the DB (assuming that's even an option)?  Alternatively, is the account your logged in with when creating the DB in the sysadmin role on the SQL server?  It would have to be in order to create database objects.

    I will add some additional comments separate from the issue at hand so that any in the future might find this info!  The SL DB Maintenance Sync screen doesn't fix user SID's, all it does is ensure that the out of the box SL accounts/roles have proper access to all the DB objects as well as rebuild views & triggers.  As for using Windows Authentication, that makes no difference as SQL still makes a user account that has a SID and associates the Win credentials with it and the SID will be different from SQL server to SQL server.  @Jonathan Van Houtte corrected me after posting this and said that the SID in SQL is actually the same but stored in a binary format so it must be run through a conversion function to be compared to the text based SID in AD.  Thanks to Jonathan for stopping the spread of bad information and you can read more about the wonderful world of SQL/AD SID's here:  https://www.sqlshack.com/sql-server-logins-users-security-identifiers-sids/

    You can run the script below against your DB and see if the SID in the DB matches the SID in the server.  It will also return any users defined in the database that are NOT defined in SQL server.  Replace the four instances of MyDBName with your database name.

    select 'MyDBName' as [database], sid, name as [user_name], type_desc,default_schema_name,create_date,modify_date select 'MyDBName' as [database], sid, name as [user_name], type_desc,default_schema_name,create_date,modify_date from MyDBName.sys.database_principals where type in ('G','S','U')  and [sid] not in ( select [sid] from MyDBName.sys.server_principals where type in ('G','S','U') )  and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')

    Thanks for letting me get all that off my chest!  :)

    Joe

    ------------------------------
    Joe Miller
    Director of Systems Architecture
    Harrison, Walker & Harper
    Paris TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 19 days ago
    Also fails using SQL UserID with sysadmin SQL role.  The Windows Authentication acct also has SQL sysadmin role.  Neither user appears in your query results.  There are a handful of rows, but none related to either acct used in the datamart build.

    Thanks for the clarification on the SID, I always thought one of the things that process does is to sync the SID as well (one of the reasons it needs to be run).

    Might need to roll back SQL to a previous version, would just need confirmation on the latest SQL version that works with SL.

    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: MR 2012 CU16 on SQL 2019

    BRONZE CONTRIBUTOR
    Posted 19 days ago
    Make sure that you have the 2nd release of CU16.

    ------------------------------
    Mike Lee
    President
    IT Evolution Management, LLC
    Milliken CO
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 19 days ago
    I downloaded the latest (and only) CU16 build available from Partnersource


    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 18 days ago
    Can anyone confirm that they have indeed successfully integrated MR 2012 CU16 with SQL 2019?  Getting a lot of suggestions, but no confirmations that it has been proven to work.  If I know it does work, then I can continue to look at environmental variables.  If no one else has tried it, then maybe it just does not work.  Would hate to be trying to debug something that just will not work.

    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: MR 2012 CU16 on SQL 2019

    BRONZE CONTRIBUTOR
    Posted 18 days ago

    Yes, I have MR 2012 CU16 running on SQL 2019.  This was an upgrade from CU14 and a move to a new server.  Failed on the 1st attempt as I was using a CU16 download from sometime back.  Once I downloaded the current CU16 it worked.

     

    Michael Lee

    IT evolution Management, LLC

    970.587.6788 Direct

    970.590.4636 Mobile

    Mike.Lee@ITeMLLC.com

    www.ITeMLLC.com

     




    Academy - Online Interactive Learning from Experts


  • 12.  RE: MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 18 days ago
    Mike,

    Thanks.  Can you confirm the build nbr of that download, and also just want to confirm it was the SL DataMart.

    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 13.  RE: MR 2012 CU16 on SQL 2019

    BRONZE CONTRIBUTOR
    Posted 18 days ago
    MR Build 2.12.16007.0
    SL Data Mart
    MS SQL Server 2019 (RTM) - 15.0.2000.5

    ------------------------------
    Mike Lee
    President
    IT Evolution Management, LLC
    Milliken CO
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 14.  RE: MR 2012 CU16 on SQL 2019

    SILVER CONTRIBUTOR
    Posted 17 days ago
    Went back to the beginning, uninstalled all of MR, and re-downloaded the CU16 build, verified MRLaunch.exe is 2.12.16007.0 and was able to get it working.  My only assumption is that while I was confident I had already downloaded and ran the latest build, I must not have (face palm).

    Thanks for all the help, and sorry for all the back and forth over something I should have caught much earlier.


    ------------------------------
    Mark Ehleben
    President
    QBI Consulting, LLC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 15.  RE: MR 2012 CU16 on SQL 2019

    BRONZE CONTRIBUTOR
    Posted 17 days ago

    I did the exact same thing (which is why I was confident in my answer).  ��

     

    Michael Lee

    IT evolution Management, LLC

    970.587.6788 Direct

    970.590.4636 Mobile

    Mike.Lee@ITeMLLC.com

    www.ITeMLLC.com

     




    Academy - Online Interactive Learning from Experts