How to share a silent ODBC Connection

SOLVED

I've connected a single table from Sage 100c 2021 to Excel (data model) via a silent ODBC connection with saved username/password.  All works great for me, but if anyone else uses this excel workbook and tries to refresh the data it makes them log in a gazillion times.  Is there a way to "share" the DSN?  Or am I going about this the wrong way?  I've tried creating the same System DSN (same name and settings) on another user's workstation to see if it would work that way, but no luck.  We're all using the 32-bit DSN.  Any ideas?

  • FormerMember
    +1 FormerMember
    verified answer

    I use a ODBC system DSN on the server with Scripts I write in ScriptBasic. I don't have any problems with multiple users using the same DSN. Try pointing to the server DSN and see if you have better luck.

    ODBC doesn't track user counts (PVKIO based). BOI sessions use a seat just like 100.

  • +1
    verified answer

    You can avoid using a DSN and instead specify the connection string using by replacing the "DSN=SOTAMAS90;" portion of the connection string with "Driver={MAS 90 4.0 ODBC Driver};"  The rest of the connection string should already be present in the query connection string but you can also generate the connection string by accessing the "ODBC Data Sources", selecting the SOTAMAS90 DSN, clicking the Debug tab, clicking the Connection String button, and then copying everything after the DSN parameter.  Make sure the paths are in UNC format since users on different machines will need to have a consistent path to the Sage 100 installation on the server.

    You can specify the user by adding a "UID=abc;" parameter to the connection string.

    You can specify the password by adding a "PWD=123;" parameter to the connection string.

    You can specify the company by adding a "Company=xyz;" parameter to the connection string.

    This does require saving the credentials in the connection string though and for this reason, I recommend creating a user in Sage 100 whose only purpose will be to authenticate via the ODBC driver.  Give it a role with access to either all or specific companies and make sure the role doesn't have anything selected on ANY of the tabs unless you have ODBC Security enabled, in which case, you will need to specify which tables the role will have access to.  Creating a user like this avoids having to save an actual Sage 100 user account that is used within Sage 100 so if anyone ever sees the connection string, even if they try to log into Sage 100, if using the ODBC user, they won't be able to access any Sage 100 tasks within the system.

  • 0 in reply to David Speck

    Also make sure you check the Excel connection string paths within the query.  If it uses a mapped drive path, and the 2nd machine doesn't have that drive mapped, it won't be a seamless refresh.

  • 0 in reply to David Speck

    David (and all of you who responded), this is great info.  Thanks so much for the time you spend responding to these posts!  Practical knowledge is invaluable.

  • 0 in reply to David Speck

    I'm trying to get this connection setup in Excel's Power Query Editor, but it won't take the UID or PWD parameters.  The error I get reads as follows:

    Expression.Error: ODBC: The connection string is invalid. The connection property 'uid' can only be provided using credentials.
    Details:
    Driver={MAS 90 4.0 ODBC Driver}; Directory=\\TECO-ERP\Sage\MAS90; Prefix=\\TECO-ERP\Sage\MAS90\SY\, \\TECO-ERP\Sage\MAS90\==\; ViewDLL=\\TECO-ERP\Sage\MAS90\HOME; Company=TEC; UID=[XYZ]; PWD=[ABC]; LogFile=\PVXODBC.LOG; StripTrailingSpaces=1; SERVER=NotTheServer

    I don't see how to save the "credentials" with the data connection within Power Query.  So, I'm back to a DSN approach.  How do you reference a System DSN from the server?  I'm not seeing an option to do that in excel either.  Any ideas?

  • 0 in reply to plbcpa

    PowerQuery won't let you save the credentials in the connection string.  Instead, you need to enter the credentials using the Database method.

    You can enter the user id followed by a vertical pipe ("|") followed by the company code as shorthand to specify the company code or you can add Company=xYZ; to the optional credential connection string properties field.