Tricentis Analytics version 1.3.1 is compatible with qTest version 9.7 and Tosca version 12.3.

Configure Database for Windows Authentication

This topic provides a workaround in the case that the database connection string was configured incorrectly during installation. This workaround is only for use with Tosca and does not work with qTest.

There are instances in which a customer will have limited access to their database due to security policies. In these cases, the customer will not allow SQL login authentication and will only use Windows authentication. With Tricentis Analytics, while the Snapshot utility will allow for repository information to be migrated to the Tricentis Analytics database, there is a failure to connect to this database to create QVD files which it leverages for it's dashboard. This guide will walk you through the process of configuring a system to allow for this type of authentication limitation.

Tricentis Analytics Installation

On the Database Connection section, you will set the Connection String with the Server and Database values. Adding Trusted_Connection=true will allow SSPI connections. This added to the snapshot config file will allow the Tricentis.Analytics.SnapshotScheduler Service to connect to the DB with no issues (MS SQL). This Connection string will somewhat carry over, but there will be additional steps that need to be taken to allow the SSPI to work.

Gain Access to the QMC

Log into the QMC. This can be done through the Windows Start Menu or by going to https://<COMPUTERNAME>/qmc/. Log in using any account that you plan on accessing it with. This can be either a domain or local account. (You can use the .\Analyticsadmin account if you want to keep it simple.) Once logged in, you will see that you have access, but all options will be grayed out. The Analyticsadmin user will be the only one that will have some access (at the time of writing this, it will have access to only Users and License management).

Once this step has been completed, you will be able to make changes to this account in the local PostgreSQL database. If logging in with any account other than Analyticsadmin, you will need to log into QMC first before it shows up in the database.

Access PostgreSQL Database

  1. Download and install pgAdmin from https://www.pgadmin.org/download/pgadmin-4-windows/.

  2. Launch pgAdmin.

  3. Select Add Server.

  4. Under General, create a name for the connection.

  5. Select the Connection tab. Enter the following information:

    • Hostname: <COMPUTERNAME>
    • Port: 4432
    • Username: qliksenserepository
    • Password: asdrandompassword

  6. Save the connection information.

  7. On the left panel, you will see the connection under Servers. Expend Databases and access QSR Database.

  8. Expand Schemas > Tables. Right-click on the Users table and choose View/Edit Data > All Rows.

  9. Locate the user you logged in as, double click on the 'RoleString' column on the chosen user's row. Replace the value with ContentAdmin.

  10. Select Save.

  11. On the top left of the Query Window, you will also need to select the Save icon to finalize the changes.

  12. Restart the QlikSenseRepositoryDatabase service in services.msc.

  13. You will be asked if you want to restart all the dependencies. Select Yes.

Once all services restart, you should be able to access the QMC with the previous account. This time all options will be accessible.

QMC Task Management

Access Task Management by selecting Tasks on the left pane.

With a SSPI setup, the QVD Creator will not be able to access the specified database.

By selecting the ? next to the status of the Task, more information can be obtained. Most of the time, there will be an option to download more logs.

In this case, the logs will have the error:

2019-03-27 18:50:42 0025 LIB CONNECT TO 'Analytics'

2019-03-27 18:50:48 Error: Connector connect error: SQL##f - SqlState: 28000, ErrorCode: 11570, ErrorMsg: [Qlik][ODBC] (11570) Required credential settings are missing: {[UID] [PWD]}.

2019-03-27 18:50:48 Execution Failed

2019-03-27 18:50:48 Execution finished.

Make Changes to Qlik Data Connection

The installation takes the Connection String specified  during the installation process and uses it with a custom connection that leverages a packaged adapter called QvOdbcConnectorPackage. The particular built-in connection to this adapter requires a username and password which is why we get an error. You will need to fix this by following these steps:

  1. Navigate back to the QMC start page. Under Management Content, select Data Connections. Double-click on Analytics. The default Connection string (in case you need to reference this) is:

    CUSTOM CONNECT TO "provider=QvOdbcConnectorPackage.exe;driver=mssql;host=SUP10clean-88;database=Analytics;integrated security=True;Encrypt=false;TrustServerCertificate=false;Trusted_Connection=false;EnableQuotedIdentifiers=1;ReturnSpecificTypeAsOdbcType=yes;"

    This would give you the below error if left alone:

    2019-03-27 21:56:56 Error: Connector connect error: SQL##f - SqlState: S1000, ErrorCode: 50360, ErrorMsg: [Qlik][Support] (50360) Integrated security authentication failed. (No credentials are available in the security package

    2019-03-27 21:56:56 )

    2019-03-27 21:56:56 Execution Failed

    2019-03-27 21:56:56 Execution finished.

  2. Substitute values for the local install (host and database value) and change Trusted_Connection to true.

  3. You will still need to make sure the Engine service account has access to the database.

ODBC Driver Setup (Optional in case above fails)

If the above steps fail, follow these steps:

  1. Open ODBC64 from the Windows Start Menu.

  2. Add a new System DSN using the driver for the Database type you are trying to connect to.

  3. Set the name of the connection, and add the <COMPUTERNAME> as the server.

  4. Select 'With Integrated Windows authentcation' and leave the SPN blank.

  5. Leave the rest of the options. Test the Data Source at the end of the process.

  6. If it passes, select OK.

  7. Back in the QMC ,in the Data Connections page, change the Connection string to:

    ODBC CONNECT TO "DSN=<ODBC DSN>;Data Source=<COMPUTERNAME>;Database=<ANALYTICS_DATABASE>;Trusted_Connection=yes;EnableQuotedIdentifiers=1;ReturnSpecificTypeAsOdbcType=yes;"

    where DSN is the name you gave the ODBC connection, Data Source is the server name or IP where the database is at, and Database is the name of the analytics database you were trying to connect to.

  8. Apply the changes at the bottom of the screen.

Database Login Configuration

Depending on the database location, you will have to make a few more changes to allow the QVD Creator Service to function correctly.

The reason we are doing this is because we are using Integrated Windows Authentication for the ODBC driver. When testing on an ODBC setup, it uses the local user Windows Authentication for the connection, but when it is being utilized by QMC, it will use the User account running the Engine Service.

Local Database

If the Database is located on the same server as the Tricentis Analytics install, then you will need to create a New Login on the Analytics Database to allow the local user access.

Remote Database

Unlike with a Local Database configuration, we cannot give database access to the local user since it is on another machine, and, in the case of MS SQL, it can only use local/AD accounts. To work around this, we will change the Engine Service account to an AD account that we can give login access to the database.

It might be a good idea to create a Service Account that will not require periodic password changes.

Follow these steps:

  1. In services.msc, right-click on Qlik Sense Engine Service and choose properties.

  2. Within the Log On tab, select the 'This Account' option and fill out the information for the AD account to be used for authentication. This account will need to be added as a New Login to the SQL Database.

  3. While you are here, you will want to do the same with the Tricentis.Analytics.Authority service. This service is in charge of authentication for the Analytics Login page. If this is not changed, it will not be able to check the DB to authenticate the user and will return a 'Login Failed' message.

  4. Restart both services in order for the changes to take affect.

Initialize QMC Tasks for Final Testing

From here, you can go back to the Tasks page in the QMC and then start the Reload QVD files task. After about 30 seconds, if you refresh the page, you should see that it was successful.

From here, you can start the Reload Data Model task and then the Reload Analytics task. Go to the Tricentis Analytics login page and login using an account associated with the repository.


Was this information helpful?

Tricentis Analytics Manual 1.3.1 © Tricentis GmbH