topic Re: QlikSense Repository user(ROLE) change in Management & Governance

QlikSense Repository user(ROLE) change

j_qlikview — Sat, 16 Nov 2024 07:07:12 GMT

Hi,

I have installed QlikSense November 2017 recently on multi-node(4 Nodes) cluster with external repository database(Postgresql 9.6). When installing postgres I have created repository user as QS and While installing QlikSense november 2017 I have given QlikSense user as QS.

Now I am trying to upgrade to June 2018 version. For this upgrade i have created databases QSMQ and SenseServices but i am not able to make QS as the owner of both the databases(PG Admin was throwing an error as incorrect login role). So I follwed the steps as shown in QlikSense Jun 2018 documentation and followed the below steps:

CREATE ROLE qliksenserepository WITH LOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'; --creates 'qliksenserepository' user and assigns privileges
ALTER ROLE qliksenserepository WITH ENCRYPTED PASSWORD '<qliksenserepository_password>'; --assigns password to qliksenserepository
ALTER DATABASE "QSR" OWNER TO qliksenserepository; --sets qliksenserepository as owner of the QSR database
ALTER DATABASE "SenseServices" OWNER TO qliksenserepository; --sets qliksenserepository as owner of the SenseServices database
ALTER DATABASE "QSMQ" OWNER TO qliksenserepository; --sets qliksenserepository as owner of the QSMQ database
GRANT TEMPORARY, CONNECT ON DATABASE "QSMQ" TO PUBLIC;
GRANT ALL ON DATABASE "QSMQ" TO postgres;
GRANT CREATE ON DATABASE "QSMQ" TO qliksenserepository;
GRANT TEMPORARY, CONNECT ON DATABASE "SenseServices" TO PUBLIC;
GRANT ALL ON DATABASE "SenseServices" TO postgres;
GRANT CREATE ON DATABASE "SenseServices" TO qliksenserepository;

After creating a new role qliksenserepository and making it as a new owner , I proceeded with the June 2018 upgrade. Upgrade was successful and all services are running, but QlikSense is still user the postgres repository user QS, though it is changed to qliksenserepository user.

Please Find the attached snapshot for the same.

Can someone please take a look and suggest me, how to change QlikSense settings to store data into the repository with the new name qliksenserepository instead of old given name i.e QS.

Thanks in Advance,

Re: QlikSense Repository user(ROLE) change

Levi_Turner — Thu, 06 Dec 2018 14:05:10 GMT

Hey @j_qlikview,

My first guess is that the connection string used by the Repository Service to connect to the underlying PostgreSQL databast is still using the old account.

To confirm this:

Open the QlikSenseUtil.exe file (default location C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe)
- If you've customized the installation path for Qlik Sense then it will be located at Drive:\Path\Where\Installed\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe with adjustments to the bolded section to point to where Qlik Sense has been installed
In QlikSenseUtil
- Click on the Connection String Editor button to decrypt the connection string
- Inspect the User ID = element
- If you want to change this, change it in the tool. Save the file and restart the Repository Service.

Hope that helps.

Re: QlikSense Repository user(ROLE) change

j_qlikview — Fri, 07 Dec 2018 20:25:23 GMT

Thanks for your reply, I tried to run the QlikSenseUtil.exe but it doesnt have any of the postgresql or the user information stored in it. Please see the screenshot below and please suggest.

Thanks,

Re: QlikSense Repository user(ROLE) change

Levi_Turner — Fri, 07 Dec 2018 20:32:59 GMT

Because the .config isn't located in the default path (D:\.... is visible in the Windows File Explorer). If you've customized the install path then the .config will be located in Drive:\Install\Path\Repository\Repository.exe.config

Re: QlikSense Repository user(ROLE) change

j_qlikview — Fri, 07 Dec 2018 23:02:59 GMT

Hi,

I looked at the config file but I dont see any information about repository user(QS or qliksenserepository). Please see the below .

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

</configSections>
<connectionStrings configProtectionProvider="DPAPIProtection">
<EncryptedData>
<CipherData>
<CipherValue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAAAjuUJ2yVEOfovGFQHuePgQAAAACAAAAAAADZgAAwAAAABAAAADCnRRq3PHjaZ5WrWJ+DPTVAAAAAASAAACgAAAAEAAAAB/gPnZbSyAKVqeNOWUbaaswAAAAFu+RsB7rIrmLnDjyK6atoGj4vCPjel++flcFeEl7U/fKmD/YD6QLYw5X8Vu0W+ctFAAAAJphqmcldNr0q/9E89bwaSGtt3l7</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings>
<appSettings>
<add key="SenseHome" value="" />
<add key="ArchivedLogs" value="" />

<add key="EnableCustomResource" value="false" />
<add key="ClientSettingsProvider.ServiceUri" value="" />
<add key="RepositoryCache.Size" value="2" />
<add key="RepositoryCache.PollingInterval" value="00:00:30" />
<add key="RepositoryCache.LoggingInterval" value="-1" />
<add key="PostgresCommandTimeout" value="90" />
<add key="ExecutionResultsAmountKept" value="3" />
<add key="RunExecutionResultsTriggeredCleaningAgent" value="false" />
<add key="ExecutionResultsCleaningAgentIntervalInMinutes" value="720" />
<add key="ExecutionResultsDatabaseCleaningThresholdInDays" value="7" />
<add key="ReadAccessControlMode" value="RepositoryFiltering" />

<add key="PublishFromHubEnabled" value="false" />
<add key="helpUrlBase" value="http://help.qlik.com/sense" />
<add key="StartupWaitForDatabaseSeconds" value="300" />

<add key="DisableAuthenticationPortRedirection" value="true" />
</appSettings>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
</startup>
<system.net>
<settings>
<httpListener unescapeRequestUrl="false" />
</settings>
</system.net>
<system.web>
<membership defaultProvider="ClientAuthenticationMembershipProvider">
<providers>
<add name="ClientAuthenticationMembershipProvider" type="System.Web.ClientServices.Providers.ClientFormsAuthenticationMembershipProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" />
</providers>
</membership>
<roleManager defaultProvider="ClientRoleProvider" enabled="true">
<providers>
<add name="ClientRoleProvider" type="System.Web.ClientServices.Providers.ClientRoleProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" cacheTimeout="86400" />
</providers>
</roleManager>
</system.web>
<system.data>
<DbProviderFactories>
<remove invariant="Devart.Data.PostgreSql" />
<add name="dotConnect for PostgreSQL" invariant="Devart.Data.PostgreSql" description="Devart dotConnect for PostgreSQL" type="Devart.Data.PostgreSql.PgSqlProviderFactory, Devart.Data.PostgreSql, Version=7.7.837.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
</DbProviderFactories>
</system.data>
<entityFramework>
<providers>
<provider invariantName="Devart.Data.PostgreSql" type="Devart.Data.PostgreSql.Entity.PgSqlEntityProviderServices, Devart.Data.PostgreSql.Entity.EF6, Version=7.7.837.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
</entityFramework>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Autofac" publicKeyToken="17863af14b0044da" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-3.5.0.0" newVersion="3.5.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Devart.Data.PostgreSql" publicKeyToken="09af7300eec23701" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-7.7.837.0" newVersion="7.7.837.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Devart.Data" publicKeyToken="09af7300eec23701" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-5.0.1629.0" newVersion="5.0.1629.0" />
</dependentAssembly>
</assemblyBinding>
</runtime>
<configProtectedData>
<providers>
<add useMachineProtection="true" name="DPAPIProtection" type="System.Configuration.DpapiProtectedConfigurationProvider, System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</configProtectedData>
</configuration>

Thanks,

Re: QlikSense Repository user(ROLE) change

Levi_Turner — Sat, 08 Dec 2018 19:08:53 GMT

Yes, there is nothing in plain text. You will need to use the utility that I referenced earlier to decrypt the connection string which is found in the <EncryptedData> element. Point the path to the config in the utility so see the plain text representation.

Re: QlikSense Repository user(ROLE) change

j_qlikview — Sun, 09 Dec 2018 14:47:51 GMT

Hi,

All I see this when I point it to the right path which is Drive D:/.

Please find the attachment for the same.

Thanks,

Re: QlikSense Repository user(ROLE) change

j_qlikview — Tue, 11 Dec 2018 18:32:36 GMT

Any Suggestions ...

Thanks,

Re: QlikSense Repository user(ROLE) change

Levi_Turner — Tue, 11 Dec 2018 21:44:25 GMT

I would encourage contacting Qlik Support since that tool is the supported mechanism for inspecting the encrypted connection string.