Condividi tramite

Do we really want Windows Authentication for Microsoft Dynamics GP?

  • Articolo

David Meego - Click for blog homepageOver the years, I have seen many requests for Windows Authentication support for Microsoft Dynamics GP, and I have to say I have mixed feelings about it. In theory, it sounds good, but in practice it may be a threat to your customer's financial information security.

Regardless of authentication method, users will still have to select a company to access which defeats the purpose of having a single sign-on.

If we have true Windows Authentication, then a workstation left unattended without being locked, could be used to access the financial system without the additional level of security of requiring a login.  Also, if Windows Authentication is used, the password will not be encrypted (see article below). 

The encryption of the passwords is what prevents access to the financial data using external tools to access the SQL Server.  Having an encrypted password means that you must use the Microsoft Dynamics GP application to access the data and so are then subject to the application's security system. You cannot bypass the application level security as the password will not work from an external tool.

When a customer asks for Windows Authentication, I think we should not apologize and say that it is not supported.  Instead we should sell the benefits of having an extra level of security provided by SQL Server Authentication with encrypted passwords.  This extra level will protect the customer's valuable financial data. 

Note: There are some third party ISV solutions which can synchronize the SQL user names and passwords with the Windows user names and passwords.  While this simplifies the system by not having to remember more than once password, it is not true Windows Authentication. [Edit] Microsoft Dynamics GP 2010 now has the option to remember the user name and password as well as the company selection.

For more information related to this topic, have a look at the following article:

Why does Microsoft Dynamics GP encrypt passwords?

Post a comment and let me know what you think?

David

11-Dec-2009: Added follow up comment:

Please don't get me wrong, I am not saying I don't want Windows Authentication, just that the extra layer of security with a second login and encrypted password can be a good thing.

I think we should sell the benefits of the way it works now rather than getting defensive when asked by a customer about Windows Authentication.

I would like to see both methods supported in future so that the customer can choose what they want.

The idea of this post WAS to start an open discussion on the topic.... so please keep posting your thoughts as comments.

15-Jun-2010: Added info about new Microsoft Dynamics GP 2010 feature to remember user name and password.

Comments

  • Anonymous
    December 09, 2009
    David, Excellent blog.  I have been installing and upgrading GP since 1995 and this question always comes up.  I have always believed in using SQL Authentication for GP for the reasons you stated. Once I explain to the client the danger of unsing Windows Authentication, the client agrees with my position. One other thing I tell them is with Windows Authentication a user can access the SQL tables using Excel or Access and damage data as well. Perry

  • Anonymous
    December 09, 2009
    The comment has been removed

  • Anonymous
    December 09, 2009
    David, The flip side of the argument is that Microsoft's larger message is that Windows Authentication is preferred. You see it in all kinds of public messaging from Microsoft about SQL server and SQL server applications. Users and prospect get confused because MS pushes Windows authentication and then doesn't supply it for GP. It definitely creates perception problems in the marketplace and resentment when working with client DBA's. In terms of leaving things unsecured, I have those same issues with everything else that uses Windows authentication including SQL Server. Someone could do just as much damage with access to signed in SQL Management Studio session. (like delete db). I'd like to see Windows authentication. I think it sends an important message to the marketplace that Dynamics is an important part of Microsoft's business mix. Mark

  • Anonymous
    December 09, 2009
    Hi David I would have to agree with you. I think financial information should be secured separately from other business information. With Windows Authentication there is too much room for access to the financial data from the client systems especially with more and more MS Office integrated tools. Kelly

  • Anonymous
    December 09, 2009
    Post from MSDynamicsWorld.com http://msdynamicsworld.com/story/accounting/gp-blogs-best-way-compare-gp-vs-nav-hazards-windows-authentication-top-5-myths-abou

  • Anonymous
    December 10, 2009
    Post from DynamicAccounting.net http://msdynamicsgp.blogspot.com/2009/12/david-musgrave-on-dynamics-gp-and.html

  • Anonymous
    December 10, 2009
    Added security? I agree with many of Mike Doerfler comments above. If a naive user is willing to leave the system open and logged into windows, they are also more than likely going to leave it logged into Dynamics GP as well. I can take care of inactivity issues on the OS with Group Policy.

  • Anonymous
    December 11, 2009
    But, which is the lesser of the two evils? I still believe that Windows Authetication to financial data provides more room for security breaches. Mariano Gomez, MVP MG.-

  • Anonymous
    December 11, 2009
    The comment has been removed

  • Anonymous
    December 11, 2009
    Hi All With the current implementation of Dexterity tables and other resources at the SQL Server level, once you have access to a database, you can access any table in that database. So with Windows Authentication, you will have access to the system and company databases including all of the tables. With SQL Authentication with an encrypted password, while the application can access all the tables, the application level security prevents access to forms, reports and tables that a user should not be able to access. Because the password is encrypted, it is not possible to access the data using SQL Authentication from other "data aware" applications.  So this application level security cannot be bypassed. If we implement Windows Authentication, then we will also need to implement permissions in SQL at the table level to provide the same level of security. From a programming perspective, this will cause all kinds of issues when Dexterity table commands fail due to SQL permissions.... and this time you can't run GRANT.SQL to fix it. David

  • Anonymous
    December 29, 2009
    I'm sorry.  I just don't buy it! I have written applications for my company that run on SQL with Windows Authentication, and I understand the security issues.  With the tools available in SQL Server, AD security groups, and Group Policy, you can lock down databases so that no one can gain access.  Additionally, you can set password policies by securtiy group to strengthen security even more. Using SQL Server Authentication doubles the number of user accounts to be managed by the security administrator.  A simple role based security model could be maintained by associating GP roles with AD security groups.  SQL Authentication also requires additional administrative time when moving databases from one server to another.  This is completely avoided with Windows Authentication. Granted, it will take an extensive re-write of the application.  But, moving costs to your customers is never the best way to serve them. Lynn Huff

  • Anonymous
    January 08, 2010
    I agree with Lynn Huff. I believe that Windows Authentication provides enough ways to secure the financial data of Dynamics GP. Besides this is the integration issue, as kpollard saids, more and more we need to interact from outside the Dynamics security scope with the data, and is difficult to manage both security types, even eConnect uses Windows Authentication. So I think that WA should be a must in not so long term releases of Dynamics GP. David Ayala Guatemala

  • Anonymous
    January 10, 2010
    Hi all Please keep the feedback and comments coming. The product management team are aware of the issue and this post and its comments. Thanks David

  • Anonymous
    January 13, 2010
    Excellent discussion everyone! We are certainly aware of the request to integrate Windows Authentication into Microsoft Dynamics GP. Not surprisingly perhaps, we receive feedback on both sides of this debate (SQL vs. Windows Auth).  While we chose not to make the Windows Auth investment in our next release, we have added a new option which would allow users to have the application remember their User ID, password, and last company...enabling a very rapid and automated process for those customers who desire a fast login (this new option can be disabled at the system level). We'll definitely continue to monitor feedback on this issue, and welcome all opinions on the matter. Thanks! Andy Westby Microsoft Dynamics GP Product Management

  • Anonymous
    January 28, 2010
    The comment has been removed

  • Anonymous
    January 28, 2010
    Great arguments on both sides. Ultimately, I think I prefer Windows Authentication for many of the same reasons cited by Mike Doerfler and mpolino. But I do agree with David on this point: I would like to see both methods supported in future so that the customer can choose what they want.

  • Anonymous
    January 28, 2010
    Posting from Mark Polino at DynamicAccounting.net http://msdynamicsgp.blogspot.com/2010/01/gp-login-with-active-directory.html

  • Anonymous
    January 28, 2010
    Posting from Vaidy Mohan http://www.vaidy-dyngp.com/2010/01/gp-login-with-active-directory-is-it.html

  • Anonymous
    March 11, 2012
    Having an option to select which authentication method to use while installation/ or later would be good idea. If this comes in future releases of GP, it will be very handy for the users to choose what ever they want. If you see AX also works on windows authentication and provides security. So if we can get the structure/ authentication in GP also it would be useful and flexible.

  • Anonymous
    February 12, 2016
    The fact that MS Dynamics cannot handle SQL Server Windows Authentication mode shows a security weakness that quite frankly is embarrassing.  Anyone who has been a production DBA managing 100's of SQL instances will tell you that Windows Authentication receives much less scrutiny from auditors due to Active Director.  SQL Accounts are far more difficult to answer auditor's questions.

  • Anonymous
    February 14, 2016
    Hi Dynamo Dynamics GP does support Windows Authentication and Active Directory credentials when used with the Web Client. The SQL Server authentication on the desktop client is actual an advantage in my opinion once you actually understand the benefits. If I walk away from my machine, the separate credentials mean that someone sitting on my machine cannot log into the financial system even if they have got past windows security. If you attempt to access the SQL database directly, you cannot as the password is not the windows password Even if you know the SQL Authentication password, you cannot access the data directly as the password is encrypted and only works from inside Microsoft Dynamics GP and there is protected via application level security. All this makes SQL Authentication more secure. David

    • Anonymous
      May 24, 2016
      You have taken an extremely narrow view of security, security concepts, and general security best practices. While your arguments may apply to a super narrow view of how the log in process occurs, it is ignorant of the fact that security is best done in layers. My biggest worry was that this was the first post that came up when I searched for SSO and the gp client. Sad
      • Anonymous
        May 24, 2016
        Hi ShamirAfter working with the Dynamics GP product for over 20 years, I feel that I do have a good understanding of what Windows Authentication and Single Sign On would mean for Dynamics GP.A single windows login with the current desktop client design would mean that that same login credentials can be used to log into SQL Server directly and access any data in any table. This is a really bad thing for a financial system. The use of SQL Authentication with an application encrypted password means that users cannot access the SQL Server directly.That said, the Dynamics GP Web Client does offer Window Authentication, but uses a different "Web Client" account to access the SQL Server. This again prevents users having access to SQL Server directly.I would not change the way that either the Desktop client or the Web client works as they both protect the data.David