by ·Comments Off on Install CA12

Product:
Cognos Analytics 12.0.4
Microsoft Windows 2022 server

kit_version=12.0.4-2501300500
Manifest=casrv-manifest-12.0.4-2501300500-winx64h.json
Installer=analytics-installer-3.7.38-win.exe

Issue:

How setup CA12 only for CAM authenticate to be used with Cognos Controller?

Solution:

(This doc describe partly the steps you need to do – please read the IBM documentation for more information)

Install IIS with roles like, Web Server, Application Development, NET extensibility, ASP.NET 4.8 etc

Download and install URL Rewrite Module 2.1 https://www.iis.net/downloads/microsoft/url-rewrite

Download and install ARR 3.0 manually https://www.iis.net/downloads/microsoft/application-request-routing

Create a SQL server database for the content store, with a SQL login as DB-owner; if you are not upgrading a old CA database.

Download the CA12 from https://www.ibm.com/support/pages/downloading-ibm-cognos-analytics-1204

Whats new in CA12 is: https://lodestarsolutions.com/tag/ibm-cognos/

Setup CA on a single server: https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=analytics-single-server-installation

If you do a upgrade, add this lines to the file preserve.txt in folder install_location/configuration/preserve.

#TM1 files
templates/ps/portal/variables_TM1.xml
templates/ps/portal/variables_plan.xml
templates/ps/portal/icon_active_application.gif
webcontent/planning.html
webcontent/PMHub.html
webcontent/tm1/web/tm1web.html
webcontent/bi/planning.html
webcontent/bi/PMHub.html
webcontent/bi/tm1/web/tm1web.html

#CA files
#templates/ps/system.xml
#templates/ps/portal/system.xml
templates/ps/portal/variables_CCRWeb.xml

Run the analytics-installer-3.7.38-win.exe to install CA 12.0.4

Select language and click next

Click next

Mark “i accept the terms.. ” and click next

Ensure you install to correct drive and click next

Click Yes

Leave the 3 selected and click next

Click install

Click done

Copy the SQL driver sqljdbc42.jar to folder C:\Program Files\ibm\cognos\analytics\drivers

Start Cognos Configuration and change to FQDN (full qualified domain names) in this places for the server:

 

  • Environment
    • Gateway URI
    • External dispatcher URI
    • Internal dispatcher URI
    • Dispatcher URI for external applications
    • Content Manager URIs
  • Environment > Configuration Group
    • Group contact host
    • Member coordination host
  • Security > Cryptography > Cognos
    • Server common name
    • Subject Alternative Name > DNS names
    • Subject Alternative Name > IP addresses

 

 

Setup the SQL server database connection for Content Store:
Right Click Content Manager and select Delete. Confirm deletion.
Right Click Content Manager > New Resource > Database.
Set the Name; Content store.
Set the Type (Group); Microsoft SQL server database
Set the Database Server and Port number (1433).
Set the User ID and Password for the database.
Set the Database name.

Save the configuration and start IBM Cognos service from Cognos Configuration.

Browse to  http://yourservername:9300/p2pd/servlet  to see if it is up.

If it is a new installation, use the CA_IIS_Config script to create the needed values in IIS.

Open the C:\Program Files\ibm\cognos\analytics\cgi-bin\templates\IIS\CA_IIS_Config.bat in Notepad++

Edit the servername to FQDN

Change SSO to True, and save the file.

Run the CA_IIS_Config.bat script from a DOS prompt as administrator.

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=services-configuring-iis-in-cognos-analytics

Press Y, then check that the URL rewrite looks like they should.

(*) Always use FQDN (not NetBIOS name or IP address) values when configuring the relevant server name.
(*) Always use lowercase characters for all of your URLs (website addresses), for example Rewrite URLs.

Browse to http://yourservername/ibmcognos/  to check that it works.

 

Configure the Authentication Provider.
Right click Authentication Source > New Resource > Namespace.
Set the name. Name should be the same as the domain name.
Set the Type (Group), Active Directory is Default.
Set the Type, leave as blank, the default.

https://www.ibm.com/docs/en/cognos-analytics/12.0.x?topic=server-configuring-active-directory-namespace

In the Explorer window, under Security > Authentication, and select the Active Directory namespace.
Click in the Value column for Advanced properties and then click the edit icon.
In the Value – Advanced properties dialog box, click Add.
In the Name column, type singleSignonOption
In the Value column, type IdentityMapping.
Click OK.

Before you get SSO to work, you need to change “allow anonymous access” to False, inside Cognos Configuration at Security – Authentication – Cognos.

Before you do more adjustments to CA, install the latest Cognos Analytics Fix Pack.

Get the latest fix pack https://www.ibm.com/support/pages/ibm%C2%AE-cognos-analytics-fix-lists

Entitled Bundled Customers use this link Cognos Analytics 12.0.4 IF2 to get access to download Cognos Analytics 12.0.4 Interim Fix 2.

Cognos Analytics Customers use the Fix Central link below.

IBM Cognos Analytics 64-bit 12.0.4 IF2 Windows 04 Feb 2025 Language Independent 17.31 GB FC

Setup of Cognos Fix Pack, should be similar to this steps:

Stop Internet Information Services (IIS) Manager (the Default Website).
Stop all IBM Cognos services through the Services Manager if they are active.
Set all IBM Cognos windows service to manual.
Reboot Windows server.
Back up the content store database.
If your IBM Cognos environment is customized, back up the entire IBM Cognos location.
Go to the location where you downloaded the files.
Run the analytics-installer-3.7.38-win.exe file, to start install of fix pack.
Follow the directions in the installation wizard, installing in the same location as your existing IBM Cognos server components if already present.
Open IBM Cognos Configuration, save the configuration.
Set all IBM Cognos service to automatic. Except below service if they exist on the server:
IBM Cognos Controller Consolidation
IBM Cognos Controller Java Proxy
IBM Cognos Controller User Manager
IBM Cognos FAP Service
Start the Internet Information Services (IIS) Manager (the Default Website).
Reboot the server.
Check that the IBM Cognos service is running, before you try to browse to: http://yourservername/ibmcognos/

 

If you start with server hostnames and later change to FQDN in Cognos Configuration you get error like this:

ERROR com.ibm.bi.rest.RESTClient [Default Executor-thread-12] NA Certificate for <WIN2022PAL> doesn’t match any of the subject alternative names: [192.168.1.106, win2022pal.pacman.local]
javax.net.ssl.SSLPeerUnverifiedException:

You must use FQDN names from the beginning in the Cognos Configuration.

You must in CA create the two user roles, and add the users that should run Cognos Controller to them.

Go to Manage – People – Accounts, select Cognos namespace, and click on new role.

Enter the name Controller Administrators, and create one more role called Controller Users.

For Controller Users click on dots and select properties. Under Members add the users that should be running the Cognos Controller program.

 

Cognos Controller need two roles called ‘Controller Users’ and ‘Controller Administrators’ in CA. It is considered best practice to ensure that all users in Controller are attached to the ‘Controller Users’ role and those defined within Controller as ‘Controller Administrators’ being to the role of the same name. Additionally add the ‘Controller Administrators’ role to the list of members in the ‘Controller Users’ role to ensure that you don’t have to add the administrative users to the ‘Controller Users’ role.

If you still do not get the Roles in CA for Cognos Controller to work (to give you SSO into cognos controller client), try to restore a old Cognos Content store that already contain this roles, so you get the correct role setup.

To add the Cognos Controller parts to IIS, after you have installed CA11.

  1. In the Internet Information Services (IIS) Manager, expand the node with your server name, and select Application Pools.
  2. Select DefaultAppPool and then from the Actions pane, select Advanced Settings.
  3. Set the .Net CLR Version to v.4.0.
  4. Set Enable 32-Bit Applications to False.
  5. Set Identity to LocalSystem.
  6. Set Idle Time-Out to 600 minutes.
  7. Click OK.
  8. Expand Sites and under your web site, create the following virtual directories as shown in the table, if they not already exist.
    Alias
    Location
    IBMCognos controller_install_location\webcontent or C:\Program Files\ibm\cognos\analytics\webcontent
    IBMCognos/controller controller_install_location\ccrvdir  (e.g. C:\Program Files\ibm\cognos\ccr_64\ccrvdir )
    IBMCognos/controllerbin controller_install_location\webcontent\ccr ( C:\Program Files\ibm\cognos\ccr_64\webcontent\ccr )
  9. Select the controller virtual directory.
  10. Double click on HTTP redirect. 
  11. Select Redirect Requests to this destination and enter the following path:
    /controllerbin/app.publish/CCR.application

  12. Click Apply.
  13. Right-click your parent virtual directory (ibmcognos) and click Add Application.
    1. Set Alias to ControllerServer.
    2. Set Application pool to DefaultAppPool.
    3. In the PhysicalPath field, enter controller_install_location/ControllerProxyServer. (C:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer)
    4. Click OK.
  14. Click Apply and click OK.

To make cognos controller talk to CA, you need to change in Cognos Controller configuration;

  • Start IBM Cognos® Controller Configuration using the Run as administrator option.
  • Under Web Services Server > Report Server point the URI to the addresses.

    Configure the following addresses:

    Server
    Value
    Report server URI for Cognos Analytics http://CA_server/bi/v1/disp
    Dispatcher URI for Cognos Analytics http://CA_Server:9300/p2pd/servlet/dispatch

Save and go to Server Authentication and change to CAM Authentication.

Save and go to Client Distribution Server Configuration – this need to be updated, the WSSurl is used by the client program.

Enter the FQDN to the controller server and save.

 

Then to get Cognos Controller web to use Active Directory from CA, you need to do:

  • In the Cognos BI installation folder, <BI_installation_folder>/templates/ps/portal/, create a file with the name variables_CCRWeb.xml.
  • The content of the file variables_CCRWeb.xml must be as follows: 
    <CRNenv c_cmd="http://{host_name}:{port_number}/#!/CamLogin">
   <cookies> 
      <param name="cam_passport"/>
   </cookies>
</CRNenv>
  • Locate the file com.ibm.cognos.fcm.web.properties in the C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web folder.
  • Open the file com.ibm.cognos.fcm.web.properties and set the following properties:
    1. biUrl: the URL that the user must go to when CAM authentication is needed. The default location is http://servername:80/ibmcognos/bi/v1/disp.
    2. biDispatchEndpoint: the endpoint to which Controller Web connects to validate CAM users and CAM passports. The default location is
      http://servername:9300/p2pd/servlet/dispatch      .
    3. loginMode: the authentication type. Set it to CAM

Save and reboot the server, to ensure that all works as it should.

(You must also do all the other settings that is for Cognos Controller Web, to make it work, above is only to get the SSO to use CA.)

https://www.ibm.com/docs/en/controller/11.1.0?topic=only-configuring-controller-web

More information:

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=gccs-suggested-settings-creating-content-store-in-microsoft-sql-server

https://www.ibm.com/support/pages/how-can-i-change-collation-controller-database

https://www.ibm.com/support/pages/node/559381

https://www.ibm.com/docs/en/controller/11.1.0?topic=web-cam-authentication

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=essbadscc-enabling-single-signon-between-active-directory-server-cognos-components-use-remote-user

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=gateway-configure-cognos-analytics-your-web-server

https://www.ibm.com/docs/en/cognos-analytics/12.0.x?topic=essbadscc-enabling-single-signon-between-active-directory-server-cognos-components-use-remote-user

https://www.ibm.com/support/pages/how-configure-sso-single-sign-controller-cognos-analytics

https://www.ibm.com/docs/en/controller/11.1.0?topic=web-cam-authentication

https://www.ibm.com/docs/en/controller/11.1.0?topic=only-configuring-controller-web

https://www.ibm.com/support/pages/node/7176858 

by ·Comments Off on How change data load to use different gateway?

Product:
Microsoft Power BI service Dataflow

Issue:

How change on-prem gateway from prod to dev?

 

Solution:

You have to click on EDIT for the dataflow.

Then click on EDIT TABLE.

Then on the HOME ribbon find OPTIONS.

Scroll down to Data Load, and select the on-prem gateway you should use in this dataflow.

 

Changing the gateway

To showcase how to change the gateway in a dataflow project, this article uses a query that connects to a local folder as an example.

This query previously used a gateway named “Gateway A” to connect to the folder. But “Gateway A” no longer has access to the folder due to new company policies. A new gateway named “Gateway B” is registered and now has access to the folder that the query requires. The goal is to change the gateway used in this dataflow project so it uses the new “Gateway B.”

Screenshot of a query that has an error message related to the data gateway being unreachable or offline.

To change the gateway:

  1. From the Home tab in Power Query, select Options.

    Screenshot of Options icon and selection in Power Query Home tab.

  2. In the Options dialog box, select Data load, and then select the gateway to use for your project, in this case, Gateway B.

    Screenshot of Project options dialog box with the drop-down menu listing None, Gateway A, and Gateway B.

     Tip

    If there were recent changes to your gateways, select the small refresh icon to the right of the drop-down menu to update the list of available gateways.

  3. After selecting the correct gateway for the project, in this case, Gateway B, select OK to go back to the Power Query editor.

 

To change the dataflow to use a different SQL view for the table of data, go to the navigation icon in the right list, and select a different view from the SQL database, from the list of view in the databases.

 

More Information:

https://learn.microsoft.com/en-us/power-query/change-gateway-dataflow 

by ·Comments Off on List users and there roles in SQL

Product:

Microsoft SQL Azure

Issue:

What user have the db_owner role?

Solution:

Start SSMS and login to your SQL azure database, and run below script (found on internet)

 

SELECT CASE princ.[type]
WHEN 'S' THEN
princ.[name]
END AS [UserName],
CASE princ.[type]
WHEN 'S' THEN
'SQL User'
WHEN 'U' THEN
'Windows User'
END AS [UserType],
princ.[name] AS [DatabaseUserName],
NULL AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--database user
sys.database_principals AS princ
LEFT JOIN
--Permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
LEFT JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
WHERE princ.[type] IN ( 'S', 'U' )
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT CASE memberprinc.[type]
WHEN 'S' THEN
memberprinc.[name]
END AS [UserName],
CASE memberprinc.[type]
WHEN 'S' THEN
'SQL User'
WHEN 'U' THEN
'Windows User'
END AS [UserType],
memberprinc.[name] AS [DatabaseUserName],
roleprinc.[name] AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--Role/member associations
sys.database_role_members AS members
JOIN
--Roles
sys.database_principals AS roleprinc
ON roleprinc.[principal_id] = members.role_principal_id
JOIN
--Role members (database users)
sys.database_principals AS memberprinc
ON memberprinc.[principal_id] = members.member_principal_id
LEFT JOIN
--Permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
LEFT JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT '{All Users}' AS [UserName],
'{All Users}' AS [UserType],
'{All Users}' AS [DatabaseUserName],
roleprinc.[name] AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--Roles
sys.database_principals AS roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
JOIN
--All objects 
sys.objects AS obj
ON obj.[object_id] = perm.major_id
WHERE
--Only roles
roleprinc.[type] = 'R'
AND
--Only public role
roleprinc.[name] = 'public'
AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY princ.[name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.permission_name,
perm.state_desc,
obj.type_desc; --perm.[class_desc]

More information:

https://www.scarydba.com/