Someone Actually Thought This Through
I wrote a rant on the "sa" password a couple weeks ago and got quite a bit of feedback. One of the items was a response from Great Plains Software, which was mentioned in the article as having poorly designed their software to use the "sa" account. What follows is the response that I was sent from Ross Hillier at Great Plains.
Great Plains Explanation of It's Use of the "sa" Account
Thank you for your efforts to make our Dynamics and Enterprise products better. However, I do believe that your criticisms are founded on some incorrect information. I would like to take an opportunity to correct this inaccuracy. If this information was incorrectly communicated to you by other members of our Great Plains' team, then I would like to apologize on their behalf.
Great Plains has two products which utilize Microsoft SQL Server: Great Plains Dynamics for SQL Server and Great Plains eEnterprise. Contrary to your assertion, neither of these products requires access to the sa login for adding users to the accounting system. (Note the usage of "requires". Both of our products have the option of using the sa account to simplify the process of adding users, but neither product "requires" usage of sa).
Great Plains has over five thousand businesses using our two SQL Server based products. As you might imagine, these customers have a vast range of skills. Some of these users are sophisticated and knowledgeable experts on SQL Server like yourself. However, some of our customers' knowledge is focused more along accounting lines, and sometimes their detailed knowledge of SQL Server is not at your level.
To meet the needs of all our customers, we actually support two methods for adding users to the system. One method is targeted at sophisticated users like yourself (which does not require use of the sa account), and the other method is targeted at less SQL Server-savvy users. This second method does utilize the sa account.
Use of the System Administrator account.
The two methods for adding users are controlled using the "SQL Options" window which is located under the "System Setup" menu in Dynamics or eEnterprise. The SQL Options window has two checkboxes which control whether Dynamics or eEnterprise should automatically add server logins and database users to the backend.
For those customers who have a more advanced knowledge of SQL Server, they have the option of "unchecking" these two options within eEnterprise setup. When these options are unchecked the process of adding users is now a two-step process:
1) The SQL DBA adds the new login and grants SQL permissions.
2) The Accounting administrator (or end-user) simply adds the same user within the accounting application and not SQL Server.
This method does not require the Dynamics or eEnterprise user to have access to the sa account.
For less sophisticated users (who may not even have a DBA), we recommend leaving the two setup options "checked". When the setup options are checked, Dynamics and eEnterprise will automatically add the necessary users and logins to SQL Server when a new user is created inside the accounting system. While this method does require that the accounting administrator have access to the sa account, our user feedback indicates that this a more popular choice for our smaller and less sophisticated users than the alternative two-step process. For our smaller users, the "DBA" and the "Accounting Administrator" are almost always the same person. In that case, access to the sa account is not an issue.
By using the sa account, we can get closer to the goal of eliminating the need of having a user to touch the SQL back end during the installation and configuration process. Other than installation and configuration, there are no other processes within the system that require the sa account.
Dedicated SQL Server.
Again, Great Plains has thousands of customers using our SQL Server products, and these customers have a huge range of needs and expertise. When purchasing a new back office product like Dynamics or eEnterprise, many of these customers lack the in-depth SQL Server experience to properly predict the hardware needs for their system. Back office systems are critical "Line of Business" applications. Customers can't afford to have their business compromised by an inability to pay bills or produce the payroll in a timely fashion because of inadequate or overloaded hardware.
With this in mind, our general corporate recommendation is to load our product in a dedicated server. We feel this best protects our customer's vital need to have a stable and high performing line of business application. It would be foolish for Great Plains to imply that it's OK in all cases to load any number of other applications and utilize the same SQL Server and computer infrastructure that is in place to maintain critical financial business information.
Of course, our recommendation to use a dedicated server is simply our blanket starting point of reference for customers. There are cases where customers have the technical background and skills to properly predict loading on their system using tools like performance monitor or SQL profiler. These customers can then make their own informed decision to configure their hardware in any fashion they desire. However, we still believe our customers interests are best served by starting with a more conservative position and consolidating only after a thoughtful and thorough analysis has been performed.
Enterprise Manager, Query Analyzer or any administrative interface with SQL Server.
These tools are designed for use by administrative personnel. There are no requirements for day-to-day use of Great Plains Dynamics for SQL Server or Great Plains eEnterprise to use these tools. It is not necessary to give any of these tools to "anyone that is not trusted and knowledgeable about SQL Server".
Manager, DataServices and Performance
Great Plains Software
Member, WorldWide SQL Server User's Group (www.sswug.org)
The original article is located here:sarant
I happen to like this approach for software development. I just wish that this information was more widely disseminated to Great Plains technical support and VARs. Given this response I am confident that Great Plains will see this gets done. My opinion of this approach to using the "sa" account is valid since in many cases, there will be no DBA in smaller companies that use this software.
As far as the dedicated server section. I understand the position taken and it does make sense. Given the option of using "sa", this is fine with me. If there is no option, then I think it is rather short-sighted of any software company to deliver a SQL Server solution without considering that another application may reside on the same server.