SQL usernames in Progression

[brutus6294]

In Enterprise Manager, all users typically have a username and a username_sql entry. I understand the username_sql is used for Progression, but what is the other non_sql account user for? I've heard it is used for 3rd party apps like Crystal, but wouldn't the standard method be to create a Crystal Group and add the network username to that? Or is the non_sql username used for ICRs?
Thanks in advance....

 

[brutus6294]

Also, if anyone knows the answer to this question... Each user_SQL macola user in Enterprise Manager has a server role of System Administrator in EM. Is this because they could need access to everything in the database and are 'controlled' by the Macola security system. Sorry for the questions, undergoing an audit...

 

[crystalreporting]

The non SQL name is created when users are added through Visual Menu Builder and is designed to be used with 3rd-party applications that require access to the SQL data outside of Macola itself (Crystal, Excel, Access, MSQuery etc.).

It's been my experience that you have to create users in SQL anyway for 3-party applications via ODBC as the username also needs to include the domain name e.g. MACOLA\johnsmith. I've also found life is easier if you do not change either of the user names created automatically.

If you create a DSN and select windows authentication, as long as you have added the user name as described above and assigned the appropriate read access rights then the user will not be prompted for a password to connect to the SQL data when using 3rd-party applications.

Peter Shirley

http://www.srhconsulting.com

 

[crystalreporting]

Sorry - didn't see your second question. Your assumption is correct; Macola needs complete access to all of the Macola files so the security within the Macola application is controlled by the application, and not SQL roles/rights.

For example when user FRED logs in to Macola, behind the scenes Macola connects to the SQL database as FRED_SQL. FRED_SQL has complete access to the Macola tables via SQL, but the access to applications (views, reports etc.) is limited based on the menu options that have been assigned to user FRED.

This applies to Progression only - ES uses a different model.

Peter Shirley

http://www.srhconsulting.com

 

[abaldwin]

To explain a little further. When you create a user in Macola, Macola will create 2 logins in SQL server.

One as name
One as name_sql.

As explained above. The _SQL user is the one that is used by Macola using the conversion from entered name to name_sql. This users abilities are controlled, not through SQL server but by Macola itself in the visual menu builder.

The NON_SQL users are created but have no rights to databases. Conceivably you can give the non_sql user, say, rights to read the ARCUSFIL_SQL table and then when a user named jsmith signs into macola he can do whatever and when crystal or odbc ask for a user name and password he would type in jsmith. To make this happen you need to also assign a password to the non_SQL user and it will need to be maintained outside of macola.

Most of us set up the domain user as a login so that we dont have to mess with the passwords.

Basically if you want to do something with the data OUTSIDE of a macola screen, you either setup and use the NON_SQL user or add the domain user as a valid login under SQL and setup / configure the rights there.




Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[tpllc]

Andy is right on track with setting up the domain user as a read only SQL user with windows authentication and a then everyone you grant access to your domain has access with out passwords as long as you set the ODBC connection on each workstation to use windows authentication. We take it a step further by creating a Macola, Macola accounting and Macola payroll group on the domain. This way we can restrict non accounting folks from the G/L tables and non payroll folks from the payroll tables. Individual users can belong to as many groups as desired. Usually companies that adapt the Macola groups in the domain administration do not add the domain users but they almost always add the domain administrators.

This is all great but I recently implemented this at a site with 75 users and found the several of them did not log onto the domain but logged into their local PC and then connected. That won’t work. Could not find a valid reason why they did not log onto the domain but that has been changed and not it works great.


Steve Henley
Trianglepartners.com
Exact Software consulting, sales and implementations.

If the only tool you can use is a hammer then all your problems look like nails.