[Breaking] Web GUI authentication

In the upcoming release of the Web GUI, a major and possibly breaking change has been made in the way in which database connections are set up.



Regardless of the type of authentication (database, Windows, IAM or Kerberos), the Web GUI now always uses a single database pool user to connect. This change is required for future developments but also offers a number of important advantages right now:

• Improved performance through more efficient connection pooling.
  
• Better support for DB2 and Oracle end products using a SQL Server IAM.
  
• Improved security as users do no longer need direct access to the database and the GUI does not have to keep passwords in memory.
  
• Simpler and more efficient code because the GUI does not have to switch context as often to impersonate users.
  
• Easier user rights management without the need to apply rights on the database.
  

Impact on reports

Using a single pool user instead of impersonating end users may have an impact on existing reports.



At present, only when using database authentication, reports can make use of queries or views that use the Tsf_user() function, for example for filtering data. Connections to the database are set up by the reporting tool with the user’s credentials, and the Tsf_user() function returns the database username if the session user name is not explicitly set.



Now that the connection is initiated with the pool user’s credentials, the Tsf_user() function no longer returns the correct user name. (For authentication types other than database this has never been the case.)

What to do next?

First, check if your application uses database authentication, as the change has no impact on other authentication types.



Next, check the reporting tool you are using. For DevExpress and SQL Server reports, we managed to intercept the report generation to correctly set the session user, even for IAM and Windows authentication!



Unfortunately, this is impossible for Crystal Reports and iNet Clear reports. It is therefore necessary to adjust or replace these reports if they use queries or views in which the Tsf_user() function is used.



Two options are available:

1. Provide the user name as a parameter of the report. Make sure to always set the user name in the default so that the value can not be tampered with when using Indicium.
   
2. Switch to SQL Server Reporting or DevExpress Reporting. DevExpress provides the option to import (simple) Crystal Reports reports.
   

Which option is best for your project depends on various factors, such as the complexity of the reports and queries and of course the number of reports. More information on the pros and cons of the different reporting tools will follow soon in a separate blog post…

Configuration

The new database pool identity can be set up in the web.config file. If no database pool identity is specified, the IIS application pool identity is used for database connections.



The database (or application) pool user must be granted full permissions on the IAM database and the end product database. The pool user should not be an IAM user, so that it can not be used to log in to IAM.



Need help or advice? Please refer to the Deployment manual for detailed instructions or drop by at Product Innovation!