Over 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?
Why does Microsoft Dynamics GP encrypt passwords?
Post a comment and let me know what you think?
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.
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.
The extra level of security is a sketchy argument at best because a user with more passwords tends to use common ones or simple ones, or worse - write them down somewhere.
Leaving a workstation unlocked - what if they leave a workstation unlocked with Dynamics up? That is probably a common scenario - I can walk by our accounting department and see Dynamics up and being used most of the day. I would assume when they walk away from their computer and it is not locked then there will be an instance of Dynamics running. So with or without Windows Authentication that is a problem.
Single click login - if that is your goal then store in a config file what company that user was last logged into. Use that persisted vaue to open the Dynamics company and they can switch companies if they need to. 95% of the time the accounting users in our company are in one company.
Bypassing application level security is easy to do - just have a DBA manually add a user to the Dynamics databases - there in and writing reports using SSRS.
Not using Windows authentication causes Dynamics database restores when you have to move sql server hosts to be a PITA. It can be done but you have to run scripts to capture the logins instead of just doing a restore. If you've just had a disaster and the server is in trouble that might not be an option. So you have to have a scheduled task to run that login capture script along with the backups.
By not using Windows authentication you've made adding a user a very high priveleged operation where somebody at one time will know the Dynamics user password (this might have changed recently).
What happens if another application creates Sql Logins like Dynamics does? Now you've forced them to create additional database servers because of the Dynamics password encryption. That might be a best practice, but adding Sql Server instances is one more maintenance addition.
If you were to use Windows Authentication adding a user to Dynamics could be as easy as adding them to a Domain Group that has Dynamics login permissions in the database.
It seems like Microsoft is pushing everything towards Windows Authentication in databases. Why be a Microsoft product and act like Domains don't exist.
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.
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.
Post from MSDynamicsWorld.com
Post from DynamicAccounting.net
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.
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
If a Dynamics GP installation included only the Dexterity-based Dynamics GP application, I would be indifferent about the authentication method used, and would probably lean more towards using SQL authentication as currently implemented. However, Dynamics GP is no longer a standalone application.
We are using live Excel spreadsheets, ODC's, SharePoint components, SSRS and Crystal. In addition, with the latest "improvements" to the security model it is difficult bordering on impossible retrieve the SQL authentication information required to create connections with some other integrating applications.
As the trend appears to be that these majority of reporting and ancillary components are being added outside the core Dynamics GP application, the development, implementation and administration of security is becoming more involved. I am in favour of using a single authentication method for all data access for a single user. I have no issue with SQL Server authentication, but unless all other components are moving to that model, Windows authentication should be made available to the core application as well. Consistency is crucial!
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.
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.
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.
Please keep the feedback and comments coming.
The product management team are aware of the issue and this post and its comments.
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!
Microsoft Dynamics GP Product Management
Full disclosure: I work at Fastpath but I am taking off the FP hat for this post and going to post as an IT auditor.
One major drawback to switching authentication modes is audit trails. AX and SL have made the move from SQL auth to Windows Auth. In doing so, they destroyed the audit trail at the database level. All DB updates are made by one user making it a massive challenge to determine who made a change to financial data. Many of the AX and SL companies we work with get dinged for this in each audit. So Dave can add improved audit trails to the list of why SQL auth is better.
A second drawback is cost and level of effort. To make the auth mode switch, the Fargo team needs to touch the code in every place the db is updated. In fact, that change alone might take all the GP dev resources an entire release cycle. GP12’s new feature list? Windows Auth. That’s it. See you next year.
As previously mentioned, using the AD policy for inactivity is a sufficient control for unattended work station risk at most companies. That said, most auditors do not like SSO for financial applications. For our SSO solution, we had to provide a login option by user before our audit team would allow us to release the application. This option allows the functionality to be turned off for users with sensitive or high risk access.
The Remember Me feature in GP2010 is a great option for smaller installations but there are a few drawbacks.
1) It’s a system-wide setting so it’s all or nothing. If there is one user or workstation that should not have this functionality (think Payroll and Purchasing) it will have to be turned off.
2) It is permissible to save the 'sa' and DYNSA userIDs which is a major risk on both the financial and the IT side. If there is a change in the GP security model, I would love to see the elimination of ‘sa’ usage in GP.
3) There is no reporting for the feature (this may be missing in the Beta and available later). It is impossible to tell who has it turned on and what userID is being stored. These will be standard questions auditors will ask during any routine audit.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.