Sage 100

 View Only

  • 1.  Premium to Premium Migration - SQL security issue?

    Posted 11-17-2020 17:36
    This is a messy one. I have a client that is using 2013 Premium along with DSD Multi-Currency (DSD Multi-Company is installed but the client no longer uses it). We installed Sage 100 2020 Premium SP1.  I installed all the enhancements that are installed in 2013, even the ones the client no longer uses with the intention to uninstall those after the data converts. The data migrates okay but only 1 of the 6 companies converts. The other companies throw this error during conversion: The following conversion record failed while converting BIG:\06.00.06.00:DATA:GL_DetailPosting:000000:234-Failed to create table GL_DetailPosting: Create Table already exists on table 'GL_DetailPosting'.  Sage support pointed me to DSD because of the "234" in the error message and DSD has gone above and beyond to help but they think it might be a SQL permission issue. They tried to use syzcon to create table (ss'createtable("GL_DETAILPOSTING","COMPANY:XXX") and for the companies that fail, syzcon returns "error 15 Operating System Command Failed". This happens on GL_DetailPosting and GL_Account. What pointed them to SQL is the fact that if you look at permissions for the GL_DetailPosting and the GL_Account tables, DataReader is attached as a User type. The tables that don't generate the error 15 don't have datareader attached. We tested removing datareader from GL_DetailPosting but that didn't change anything. Is there somewhere in SQL that I could look to make sure it is set up correctly? The fact that one company converted okay confuses the heck out of me. I know there are SQL gurus out here so I'm hoping I'm just missing something. Thanks for any help you can provide!

    ------------------------------
    Kate Krueger
    Business Applications Manager
    Eide Bailly, Inc.
    Denver CO
    970-692-5113
    ------------------------------


  • 2.  RE: Premium to Premium Migration - SQL security issue?

    Posted 11-17-2020 18:01
    Premium to Premium should do a backup and restore for the migration... not create tables.  Did you use Windows authentication for the migration instead of the SQL "sa" account?
    Database owner should be MAS_User.  That should be the only thing that matters for permissions related to Sage controlling data.

    Is there a bunch of old M4T's in the \MAS90\MAS_###\ folders?  Remove those.  The conversion may see those files and be trying to read them into SQL, which would be wrong.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------



  • 3.  RE: Premium to Premium Migration - SQL security issue?

    Posted 11-17-2020 18:18
    Hi Kevin! I used the SA account for the migration. I confirmed that the DB owner for all the MAS_XXX and MAS_SYSTEM is MAS_USER. There were a bunch of .m4t files so I moved those out but I'm still seeing the same error when I try to convert all but one of the companies.

    ------------------------------
    Kate Krueger
    Business Applications Manager
    Eide Bailly, Inc.
    Denver CO
    970-692-5113
    ------------------------------

    Original Message


  • 4.  RE: Premium to Premium Migration - SQL security issue?

    Posted 11-18-2020 09:49
    Are there any non-Sage tables in the databases (created manually in SQL, not through Sage / enhancements)?  We've seen that cause problems with company copies.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 5.  RE: Premium to Premium Migration - SQL security issue?

    Posted 11-19-2020 10:57
    @Kevin Moyes that is a good suggestion! I looked but there were not any. Thanks so much for the suggestions on this thread (and all threads)!

    This issue ended up being caused by that datareader role (not dbdatareader) attached to GL_Account and GL_DetailPosting. Track database changes was also turned on for the companies that wouldn't convert - I'm not sure what the client was trying to do or if they even knew that was on.  The phenomenal @Alnoor Cassim walked me through creating a new, clean table and the SQL queries to create the indexes and populate the new table with the original data. With the new and shiny table in place, the conversion completed without any problem. This is far deeper into SQL than I have had to go before and I truly appreciate all the help this group provides! ​

    ------------------------------
    Kate Krueger
    Business Applications Manager
    Eide Bailly, Inc.
    Denver CO
    970-692-5113
    ------------------------------

    Original Message


Related Content