QlikSense Repository user(ROLE) change

[j_qlikview]

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,

J

[Levi_Turner]

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.

[j_qlikview]

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,

J

[Levi_Turner]

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

[j_qlikview]

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" />
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
</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 the desired folder path for logs Archiving from this node. Not applied when SharedPersistence is true. -->
<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" />
<!-- Valid values are: "RepositoryFiltering" and "DualTestMode". "EntityFrameworkFiltering" is no longer supported, and "RepositoryFiltering will be used instead. "DualTestMode" should _not_ be used for anything but test purposes since it has a significant performance impact.-->
<add key="PublishFromHubEnabled" value="false" />
<add key="helpUrlBase" value="http://help.qlik.com/sense" />
<add key="StartupWaitForDatabaseSeconds" value="300" />
<!-- Minimum value is 5 seconds -->
<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,

J

[Levi_Turner]

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.

[j_qlikview]

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,

J

[j_qlikview]

Any Suggestions ...

Thanks,

J

[Levi_Turner]

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