SA! Why’d it have to be SA?

Admin Note: Please review the comments section for corrections for this blog post.

Indiana Jones

“Snakes! Why’d it have to be snakes?”- Indiana Jones

The Made2Manage ERP system accesses its database with one login. I suspect the reason they do so is because it makes programming easier. So, while all of your users log into M2M with the user name assigned by the System Administrator, every database command is sent to SQL via the SA (System Administrator) single login.

In my opinion, this has several drawbacks.

  • Tracking database changes by user is difficult to impossible. M2M provides ECM auditing, but this has issues as well. I’ll follow up with a post on that at a later date.
  • By default, SA has all rights to all databases on the entire server. Using this is inherently insecure.
  • Changing the SA password is a bit more complicated in this situation and you should do this periodically.

As a user, you don’t have a choice in this, but many administrators I’ve spoken with perpetuate the problem by using the SA login themselves. For example, I recently had a company send me an Excel spreadsheet with an ODBC connection which pulled data from their M2M Sales tables. They wanted my help adding a link to another table in the SQL query. However, when the sent it to me they used the SA login and the password was plainly visible for anyone to see. Not only can the user see any data in the tables that they want, but they could easily write to the database, truncate or drop tables, etc. You could argue that I would need access to their network to harm them, but any one of their own employees could. A few years ago, I warned another Admin against this practice and he told me “it’s alright, none of my users would know how to do that.” To me, that’s as unacceptable as having a pet cobra because it’s gentle and rarely bites.

Therefore, if you use an outside reporting vehicle such as Crystal Reports, ODBC Spreadsheets, or SQL Reporting Services, you should create an account with read only rights. If you’d like, you can create report logins with read access for specific tables and a login for managers with access to all tables.

Do you really want to risk someone getting access to your payroll tables?

6 comments to SA! Why’d it have to be SA?

  • Mark

    While it is true M2M currently uses the same SQL login for each M2M user and all database access, it is not required to use the built in ‘sa’ SQL Server account. M2M implementation consultants actually suggest otherwise and to use a SQL login you create specifically for M2M (e.g. m2msa).

    When you create a new company you are prompted for which SQL login and password you would like to use for the current company. The default is sa, but this can be changed while creating the new company.

    If you have an existing company you can change the SQL login used for the account by running CREATEDBC and checking the option to change the login credentials.

    This solves two of your problems of sharing the sa login with the M2M application and not needing to run CREATEDBC when you change your sa password as part of your routine maintenance. You can at least confine the M2M application and users to the effected M2M company databases, which is a very valid concern.

    Database access in M2M is controlled by application permissions inside M2M and the user should not be able to see the SQL login assigned to M2M as it will be encrypted. I do not think It is so much a programming issue because you can just roll back a transaction if a query fails because the SQL user (Windows authentication, etc) did not have permission to a required database object. Rolling back a transaction is trivial. It seems like a choice was made to control access inside the application by form, work flow or job description so the system administrator did not need to worry about which database objects each user needed access to may need access to, to perform their tasks (which is not always obvious). I guess M2M could be written to have SQL access be by user and still use the form, work flow or job description categories to assign permissions and take care of permissions in SQL for you. This is slightly less trivial, but still a straight forward task not a huge programming hurdle. I agree there are trade offs to each school of thought.

    Anyway I hope the information about changing your SQL login in M2M will be of some relief!

  • admin

    Thank for the correction Mark, that’s exactly the reason I have the blog. I’ve worked for 2 M2M companies, and consulted with several others, and have yet to run into an install that didn’t use the SA login. Unfortunately, I just assumed that it was required.

    I would like to say however, that at least some of the Implementation Consultants are not following the recommended practice you mentioned, as every install I’ve ever come across was originally set up by M2M.

    I’m going to set up a time to remedy that at my install, and would encourage any Made2Manage Admin to do so as well.

    Let me take moment to address your comment regarding M2M security. I disagree with your assessment that transaction rollbacks in this case are trivial. Sure, it would be a fairly trivial matter if someone did something drastic which attracted attention.

    However, the more insidious attack would be subtle and difficult to detect. I would predict that a malicious person would methodically delete a few records in key areas over time which would go unnoticed for some time. The time factor is key because back ups would do you little good. Restoring from 3 weeks ago, even a week ago, is simply not an option. In addition, finding all of the deletions and repairing them would be very expensive.

    Furthermore, most people of experience know that the M2M login account and password, SA or otherwise, can be retrieved from a non-encrypted source. There is simply no way for an admin to change that.

    Obviously, I’m not going to post the directions on this blog.

    Thanks again for the advice, and I will be changing my M2M set up immediately.

  • Mark – it’s the responsibility of the vendor to encourage better security. If the installation suggests that the users pick SA by default, then the user will assume there’s a good reason for that. Could we maybe file an enhancement request to get the installation to use something other than SA?

  • (admin) I completed another fresh install of SQL Server and Made2Manage. M2M does not specify what login you should use for it. Even though use of the SA login is not required, there really should be a recommendation not to use it during set up.

    As for everything else I posted, I still stand by it.

  • […] user is still using the SA login. This is a tremendous security […]

  • […] when I first started with Crystal, I was instructed to use the SA account, the same account most M2M installations are configured to use. This is a bad idea for several […]

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>