Minimum Required DB Rights (MS SQL SERVER) for HP ALM Environment (HP QC/ALM)

It’s been about 3 months now since my last blog; I have been busy on couple of projects with very high severity.

The issue I am going to discuss now has very significance which most people ignore for low to medium security level organizations. But of organizations where Security polices take higher preference, we need to identify the minimum database rights required for set up and work properly.

In normal implementations, what I and most of my colleagues around the world end up doing is, creating a user with sysadmin (Members of the sysadmin fixed server role can perform any activity in the server: Courtesy MSDN) rights, and doing the all the implementation using that.

One of my company’s clients, obviously with very high security policies in place, wanted me to setup environment where I sit with their Database administrator in order to identify the minimum requirement of rights.

I had told him my hunch is that you need to give dbcreator and ability to change / set up new users.

So we started our research, here are some important points form it

During installation

A user (HP QC/ALM DB Admin) with rights DB Creator (dbcreator) and Security Admin (securityadmin) is required for HP ALM

Reasons:
  1. A new database is to be created for Site Administration purposes
  2. A USER is to be created for record manipulation and connections establishing

One observation was made, for the created project, the user HP ALM DB Admin, was also given DB Owner permission for the database (Site Administration).

During ALM Usage

Now when ALM was being used, we observed that we need two types of permission sets

Normal Processes

The user TD (with maximum privileges of ddladmin) created during installation is enough for normal processes which include

  1. Record Manipulation
  2. Reports generation
  3. Setting up and working with Configurations (Site Admin)
  4. Working with Project Customization, where no Database Field is to be change

Administration Process

The user HP ALM DB Admin with elevated privileges, which being dbownerdbcreator and securityadmin is required for administrative processes

  1. Project Management (Creating, deleting, restore, export, import, verify, maintain & Upgrade)
  2. Working with Project Customization, where Database Fields are going to be changed (new fields, change field types, delete fields)
  3. Domains
  4. DB SERVES

Precaution

But we need to understand one thing, the user HP ALM DB Admin needs to not be shared with everybody, as this user according to MSDN should be treated with similar care as of sysadmin (Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.)

Syntax Error on Accessing Script Generator & Customization Links (HP QC/ALM)

After writing first blog on issues I faced in last 3 to 4 years of HP ALM/QC Customization and Implementation, I have enjoyed it revisiting old experiences and ‘Heated’ discussions we have had.

The issue I am going to discuss now had such significant effects, that at one stage I was looking at my first failure implementation.

The issue is that, sometimes when user accesses the Script Generator from Project Administration Workflow, the ALM/QC throws an exception and displays message that there is syntax error at particular line.

This error looks something like following, (Line number keeps on changing for different servers)

“Syntax error in line # 106: ‘}’ or ‘,’ expected, but “””” found”

The issue can be generated, when user is working with workflow editor module, and somehow corrupt the script editor itself. The issue is related to BugScript CSET_CATEGORY into the COMMON_SETTINGS table.

CSET_NAME ScriptWizardFieldCust_Add ScriptWizardFieldCust_Details and ScriptWizardListCust

For the affected project it looks like this:

You can see that the CSET_VALUE is {.

Now there are two ways to fix this issue, by one you need to remove the BugScript from the COMMON_SETTINGS Table or update and replace the value of it.

Steps for first solution

  1. Create a backup of the db before trying the fix.
  2. In Site Administrator-> site projects project’s tables open COMMON_SETTINGS table and run the following query:

DELETE FROM COMMON_SETTINGS where CSET_CATEGORY = ‘BugScript’

  1. Connect to the project and Clicking on the links will be working now.

Steps for Second Solution

  1. Create a backup of the db before trying the fix.
  2. In Site Administrator-> site projects project’s tables open COMMON_SETTINGS table and run the following query:

UPDATE COMMON_SETTINGS set CSET_VALUE = ” where CSET_CATEGORY = ‘BugScript’

  1. Connect to the project and Clicking on the links will be working now.

 

Important Announcement: if you are facing any issue regarding HP Quality Center/ HP Application Lifecycle Management, please contact at abkhalid@gmail.com, I will try my best to help you out.

Document Generator Crashing Issues (HP QC/ALM)

After I wrote my last blog on the SSL with JBoss Environment for HP QC/ALM, my colleague asked me “Ahmad, you have also worked on many other issues over the course of last 3 to 4 years, why not try to document them all.”

So here, I am taking up his advice!

There is a common issue that keeps creeping up, and most of the time, reply form HP Support is that they cannot do anything about it. And to reveal a secret, that they really can’t. The issue is when we try to generate document through document generator or try to export an excel report from dashboard, ALM window crashing by displaying the following error.

“”Error: Unable to cast COM object of type ‘Microsoft.Office.Interop.Word.ApplicationClass’ to interface type ‘Microsoft.Office.Interop.Word._Application’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{00020970-0000-0000-C000-000000000046}’ failed due to the following error: Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)). Line: mscorlib”

The issue is generated, if there are registry entries of multiple versions of Microsoft office applications. For example, if user upgraded MS Office from 2007 to 2010 version, there are more than enough chances that some of the registries instances of older version of Office or not removed. Another example can be that if user installed MS Office 2010 and then installed MS Project 2007 or MS Project 2013.

Now when the ALM/QC tries to access Office COM files, its directed to multiple locations, thus it crashes.

Issue can be tackled by following the steps given below (Disclaimer: only personal that have experience with registry should follow following steps, rest should ask their administrators to do it for themselves.)

  1. Copy the CLSID (long data within the curly brackets) form the error window. [in above error window its 00020970-0000-0000-C000-000000000046]
  2. Access Windows registry
    1. Open Run window (‘Window key’ + ‘R’)
    2. Type in ‘regedit’ and press enter
  3. Take backup of the Registry
  4. Place your focus at top of the registry tree (at computer) and now search the copied CLSID in the registry
  5. You will reach a location in HKEY_CLASSES_ROOT whose title is the key which was searched. There are going to be three sub entries “ProxyStubCLSID”, “ProxyStubCLSID32” & “TypeLib”.
  6. Copy the default key of “TypeLib”. [Actually whenever a call to a particular assembly object is generated, Assembly opens up this key to find the exact location of application]
  7. Place your focus at top of the registry tree (at computer) again and now search this newly copied key in the registry
  8. After you find the key, expand it, you will/might find multiple versions of “TypeLib”; please remove all those that do not contain a valid path (verify the path) in their default entry
    1. Inorder to do it, expand each version, then expand 0 key, then copy the path in the default entry, open that path and verify that if tlb file exists on the path

Preventive Measure that might help: Whenever you uninstall a MS Office application, please do restart your machine, before installing any other one.

Important Announcement: if you are facing any issue regarding HP Quality Center/ HP Application Lifecycle Management, please contact at abkhalid@gmail.com, I will try my best to help you out.

SSL Support with JBOSS (HP QC/ALM)

During one of our recent implementation of HP Quality Center, one representative form the client, kept asking me, Ahmad! “Why you are not implementing SSL. We have very secure data and you are exposing it”.

Defiantly I had to look at it as refusing clients, is not part of our company policy.

After working at it for couple of days, I came up with following solution (Got little help from HP Support Community).

Step 1: Install HP Quality Center with JBoss Application Server

Step 2: In command prompt navigate to Java Bin Directory (JRE – Install it if not present).

Default Directory can be <installation drive>\Program Files\HP\<Either of Quality Center or Application Life Cycle Management>\Java\JRE\bin\

Step 3: Run following commands (in place of “Server Name” write the actual server name)

  1. set SERVER_DN=”CN=<server name>, OU=X, O=Y, L=Z, S=XY, C=YZ”
  2. set CLIENT_DN=”CN=Client, OU=X, O=Y, L=Z, S=XY, C=YZ”
  3. set KSDEFAULTS=-storepass changeit
  4. set KEYINFO=-keyalg RSA
  5. keytool -genkey -alias tomcat -dname %SERVER_DN% %KSDEFAULTS% -keystore server.keystore %KEYINFO% -keypass changeit
  6. keytool -export -alias tomcat -file temp_server.cer %KSDEFAULTS% -keystore server.keystore
  7. keytool -import -v -trustcacerts -alias tomcat -file temp_server.cer %KSDEFAULTS% -keystore client.keystore.trust -keypass changeit

Step 4: Take copy of CACERTS file present in the <installation drive>\Program Files\HP\<Either of Quality Center or Application Life Cycle Management>\Java\JRE\lib\security and place it somewhere safe

Step 5: Copy the file created by Step 3 Part 7 (CLIENT.KEYSTORE.TRUST) and to the <installation drive>\Program Files\HP\<Either of Quality Center or Application Life Cycle Management>\Java\JRE\lib\security folder and rename it CACERTS

Step 6: Copy the file created by Step 3 Part 6 (SERVER.KEYSTORE) to an accessible location like C:\CAs\server.keystore

Step 7: Back up “server.xml” present in <installation Drive>\ Program Files\HP\<Either of Quality Center or Application Life Cycle Management>\jboss\jboss.zip\server\default\deploy\jbossweb.sar

Step 8: Edit the “server.xml” by Uncommenting the SSL connector, edit the keystoreFile and keystorePass options:

<!– SSL/TLS Connector configuration using the admin devl guide keystore –> <Connector port=”8443″ address=”${jboss.bind.address}” maxThreads=”100″ minSpareThreads=”5″ maxSpareThreads=”15″ scheme=”https” secure=”true” clientAuth=”false” keystoreFile=”C:\CAs\server.keystore” keystorePass=”changeit” sslProtocol=”TLS” />

Comment out the HTTP connector in the server.xml file.

Step 9:             Save the server.xml file, and restart JBoss and QC (e.g., via the Windows Services)

Step 10: Connect to QC using port 8443. Forexample (https://server name:8443/qcbin)

Translate »