Month: March 2022

Get read access to all databases tables

March 30, 2022 by Roger·Comments Off

Product:

Microsoft SQL server 2016

Microsoft Windows 2012 server

Issue:

How add read access to all tables in all databases on one server to a user?

Solution:

Add a AD user as a login to the SQL server in SSMS. https://www.guru99.com/sql-server-create-user.html

In our example we use User3, but you change it to your login-name.

EXEC sp_MSforeachdb ' Use ?; Create user User3 for login User3; Grant select on database :: ? to User3 '

In the string after the sp_MSforeachdb command, the character ? is replaced by each database.

Use ?, this will move focus to this database, and execute the command after to this database.

Create user User3 for login User3, adds the user name to the database and connect it with the login user.

If the user3 already is in the database, then above will not work. Then run the command again without the Create user… part.

Grant select on database :: ? to User3, will give this user READ access to all object in the database. Even new tables and views.

The grant select on a database, will give that user full read access to the database tables and views.

More information:

https://www.mssqltips.com/sqlservertip/6702/sql-server-windows-authentication-with-users-and-groups/

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.techonthenet.com/sql_server/users/create_user.php

https://www.tutorialsteacher.com/sqlserver/grant-permissions-to-user

https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.mssqltips.com/sqlservertip/1476/how-to-read-log-file-in-sql-server-using-tsql/

https://dba.stackexchange.com/questions/117759/granting-select-access-to-all-tables-within-a-specific-database-in-ms-sql

To grant SELECT permissions on the whole database use this:

USE <MY_DATABASE>
GRANT SELECT ON DATABASE :: <MY_DATABASE> TO <MY_USER>

where

<MY_USER> is user
<MY_DATABASE> is database name

Granting permissions on schema doesn’t help, simple reason: if new schemas occur the user will not have permissions.

Granting permissions on all objects doesn’t for the same reason, the user will not have permissions on new objects created after GRANT event.

View SQL Agent jobs history

March 29, 2022 by Roger·Comments Off

Product:
Microsoft SQL Server 2016

Microsoft Windows 2012

Issue:
How read the SQL agent jobs logs without being a sysadmin?

Solution:

Add the user to a AD group and add the AD group as a login in the SQL server. In our example we will instead use SQL login User3.

In SSMS go to Security – Logins – New Login. https://www.guru99.com/sql-server-create-user.html Create your login in SQL.

Go to your MSDB database under – Databases – System Databases – msdb – Security – Users – New User.
Add your User3 there to the msdb database.

Expand Membership and select SQLAgentReaderRole, then click OK.

Then that user can in SSMS see SQL Server Agent, and View History and properties of a SQL Agent Job.

With SQL script:

USE [msdb]
exec sp_addrolemember 'SQLAgentReaderRole', 'DomainName\GroupName'

https://zarez.net/?p=36

More Information:

https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?redirectedfrom=MSDN&view=sql-server-ver15

https://www.techonthenet.com/sql_server/users/create_login.php

https://www.tutorialsteacher.com/sqlserver/grant-permissions-to-user

https://www.vb-net.com/Sql/Index.htm

https://www.tutorialsteacher.com/sqlserver/indexes

How use Git?

March 29, 2022 by Roger·Comments Off

Product:

Microsoft Windows

git version 2.33.1.windows.1

Issue:
What is a good order of command to work with GIT?

Suggestion:

Install GIT on your computer from here https://git-scm.com/download/win

https://git-scm.com/book/en/v2/Getting-Started-Installing-Git

Create a folder, where you will work with git, in our example we use C:\kurser\git-demo-example

To setup a repository, you can follow this steps: https://towardsdatascience.com/an-easy-beginners-guide-to-git-2d5a99682a4c

When you are inside your git folder, you can start using GIT from tools like GIT BASH or SOURCETREE. https://www.sourcetreeapp.com/enterprise https://gitforwindows.org/

Git commands are case sensitive, and git commands is always lowercase. git used to that.

If you work with Azure DevOps as master source, the git function can depend on how Azure DevOps is setup.

We guess you have already clone your repo, so you can start to work.

git status (to check you are in correct folder and branch, mostly main at start)

git pull (to download the latest changes to the project from the remote master)

git checkout -b “Feature/1234” (to create a branch and start working on the files)

Work with your file in Notepad++ or Visual Studio, and save your work in the folder you have for git.

git add . (to add all changed files to the git source control)

git commit -m “This is a updated file” (to save the changes to your branch, add a online comment about the files)

git checkout master (switch to your local main branch to update it with any new remote changes)

git pull (download the latest changes done by others to your main branch)

git checkout “Feature/1234” (go back to your local branch, you been working in)

git merge master (get the files from master into your branch, any conflicts have to be solved in Visual Studio locally)

You may have to change the word master to main in your version of GIT, also remember that the branch-name is case sensitive.

git push origin “Feature/1234” (to push your changes up to the remote git server)

Then you have to go to your remote git (or Azure DevOps) in your web browser and complete the pull there.

This picture will look different for what kind of remote repo you use. Above is from https://github.com

Click on Compare & pull request button

Enter some descriptive text and click on Create pull request button.

As we are lucky, no conflicts, you can press Merge pull request. Good practice is to leave explaining comments about what the code change is about.

Click on Confirm merge button.

Recommendation is to Delete the branch, so it is not laying around.

Then you are good to go – and start over from top with your next change.

git log –oneline (enter to see the steps you have done)

Download and install WinMerge, to have a tool to compare files. https://winmerge.org/downloads/

Download and use Visual Studio Code https://code.visualstudio.com/download to solve conflicts.

git reflog (to see more detail’s of what you done)

If I accidental delete files in my local folder for the git project, i can reset my state to the one before with command:

git reset –hard origin

This will only work if i have not committed and push the change up to remote repo.

More Information:

https://education.github.com/git-cheat-sheet-education.pdf

https://towardsdatascience.com/an-easy-beginners-guide-to-git-2d5a99682a4c

https://rogerdudler.github.io/git-guide/

https://rogerdudler.github.io/git-guide/files/git_cheat_sheet.pdf

https://www.atlassian.com/git/tutorials/git-bash

https://docs.microsoft.com/en-us/azure/devops/repos/git/pull-requests?view=azure-devops&tabs=browser

https://www.stanleyulili.com/git/how-to-install-git-bash-on-windows/

https://www.freecodecamp.org/news/practical-git-and-git-workflows/

https://docs.microsoft.com/en-us/visualstudio/subscriptions/download-software

https://visualstudio.microsoft.com/vs/older-downloads/

https://visualstudio.microsoft.com/free-developer-offers/

https://github.com/microsoft/vscode

https://medium.com/@deprov447/a-simple-git-guide-8854d5c3ae12

https://git-scm.com/docs/gittutorial

https://www.edureka.co/blog/git-commands-with-example/

Could not logon to TM1 com.ibm.cognos.tm1.TM1Exception

March 23, 2022 by Roger·Comments Off

Product:
Planning Analytics TM1_version=TM1-AW64-ML-RTM-11.0.5.1008-0

Cognos Analytics Product_version=11.1 R7 (LTS)

Cognos Controller CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2000.1098-0

Microsoft Windows 2019 server

Issue:

IBM Cognos Controller FAP service does not login to the TM1 server for a FAP IP.

In D:\Program Files\ibm\cognos\ccr_64\Server\FAP\lib you have the SQLJDBC42.JAR file.

In Control Panel you have installed Microsoft SQL server 2012 Native client version 11.4.7001.0 (as a older version gave other problems)

In REGEDIT only TLS 1.2 is activated with key;

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
“SchUseStrongCrypto”=dword:00000001

Error in D:\Program Files\ibm\cognos\ccr_64\Server\FAP\error.log file:

ERROR [fap.service.schedule.Scheduler] [schedulerThread], Could not logon to TM1
com.ibm.cognos.tm1.TM1Exception: SystemServerClientNotFound
at com.ibm.cognos.tm1.API.logonCAMNamespace(Native Method)
at com.ibm.cognos.fap.common.persistence.tm1.TM1Context.login(TM1Context.java:124)
at com.ibm.cognos.fap.service.schedule.Scheduler.updateDatamarts(Scheduler.java:591)
at com.ibm.cognos.fap.service.schedule.Scheduler.run(Scheduler.java:261)
at com.ibm.cognos.fap.service.schedule.Scheduler$1.run(Scheduler.java:166)
at java.lang.Thread.run(Thread.java:825)

Solution:

Check that you can login to CA11 with the account that you enter inside FAP client program.

Check the TM1S.CFG file for this url

ServerCAMURI=http://servername.domain.com:9300/p2pd/servlet/dispatch
ClientCAMURI=http://servername.domain.com:80/ibmcognos/bi/v1/disp

Copy http://servername.domain.com:9300/p2pd/servlet/dispatch to your web-browser and try to login with the same username and password as you have entered in FAP DATA MART TM1 Connection.

Can you not login? Ensure that this user is part of the Cognos System administrator group inside Cognos Connection.

Go to Administration Console

Click on Security tab – click on cognos

Go to System Administrators – click on more

Click on Set Members

Add you user by click on Add… and type.

Enter the name domain/username and click on arrow, and click OK.

Now you should test if you can login with that user to CA11 from your web browser.

Create a jvm.options file in folder D:\Program Files\ibm\cognos\ccr_64\Server\FAP to make FAP use TLS1.2

-Dcom.ibm.jsse2.overrideDefaultTLS=true
-Dcom.ibm.jsse2.overrideDefaultProtocol=TLSv12

Also ensure that the Windows account running the Cognos Analytics service is part of the local administrator group.

More information:

https://www.ibm.com/support/pages/how-configure-force-controller-use-tls-12

https://www.ibm.com/support/pages/troubleshooting-could-not-login-tm1-systemserverclientnotfound-error-when-publishing-controller-data-tm1-fap

https://www.ibm.com/support/pages/troubleshooting-could-not-login-tm1-systemserverclientnotfound-error-when-attempting-run-initial-publish

https://www.ibm.com/support/pages/automatic-tm1-cam-user-creation-when-using-cam-bi-non-native-security

Could not load folder contents

March 14, 2022 by Roger·Comments Off

Product:

Microsoft Power BI local server

Microsoft Windows 2019 server

Issue:

When user browse to the home or browse button in the Power BI portal they get a error like:

Could not load folder contents

You are not allowed to view this folder. Contact your administrator to obtain the necessary permissions

This when they go to http://servername/Reports/browse

Solution:

In Power BI, all reports and folders, can be you set security on. The root or browse folder is the same, it is only that it is hard to find. You need to click on “Manage Folder” to get there.

Then click on “manage folder ” on the right to get to the security dialog.

Inside security, click on “Add group or user” to add a group like “everyone”.

Enter the name of the group, mark “browse” and click OK.

Now this group can see this folder, and all sub-folders that do not have “customize security” set.

Recommendation, is that you on all folders and reports in the top (root) folder are having “customize security” active, so you need to manually add the users or AD groups to this folders for users to be able to view them.

Customize Security will make the folder to not inherit the security groups from the folder above.

To be able to see a sub-folder, the user need access to the top folder above as Browser.

More Information:

https://community.powerbi.com/t5/Report-Server/Access-to-the-home-folder/m-p/448374#M6249

https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

https://guyinacube.com/2020/02/25/can-you-use-groups-with-power-bi-row-level-security-rls/

https://www.bconcepts.pt/power-bi-report-server-configuration-and-security-implementation/

https://iterationinsights.com/article/how-to-get-set-up-with-power-bi-desktop-and-power-bi-service/

https://www.bluegranite.com/blog/tips-for-a-successful-power-bi-report-server-implementation

Error response from daemon: endpoint with name mongo already exist in network nat.

March 8, 2022 by Roger·Comments Off

Product:

Planning Analytics Workspace 73

Microsoft Windows 2016 server

Issue:

After upgrade of PAW, the admin tool stop with error like this:

Execution failed with exit code 1: Encountered errors while bringing up the project.

Error: for mongo Cannot start service mongo. Failed to create endpoint mongo on network nat.

When you try to start database mongo with command: docker start mongo, you get error

Error response from daemon: endpoint with name mongo already exist in network nat.

Error: failed to start containers: mongo

Suggested solution:

Start a powershell session as administrator on your PAW server.

Enter to get a list of networks: docker network ls

To see nodes in a network enter: docker network inspect nat

To remove old node in network enter: docker network disconnect nat mongo

To start mongo enter: docker start mongo

To list running containers enter: docker ps

Should look like above, if PAW73 is working fine.

More Information:

https://www.ibm.com/support/pages/failed-start-container-failed-create-endpoint-share-proxy-network-nat-file-local-kvdblock-being-used-another-process

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=web-install-configure-tm1-microsoft-windows

https://www.ibm.com/support/pages/troubleshooting-planning-analytics-workspace-related-docker-issues

https://www.mirantis.com/product/basic-for-mirantis-container-runtime-formerly-docker-engine-enterprise-for-compatible-linux-and-windows-server/

https://docs.docker.com/config/pruning/

bolt.Close(): funlock error: The handle is invalid

March 8, 2022 by Roger·Comments Off

Product:
Planning Analytics Workspace 73

Microsoft Windows 2016 server

Issue:

After upgrade of PAW, the system does not start all containers.
In the c:\programdata\docker\panic.log file you may have this error:

bolt.Close(): funlock error: The handle is invalid

When try to start the PAW with the command ./paw.ps1 we get error similar to “No connection could be made because the target machine actively refused it” and “failed to update store for object type *windows.hnsEndpoint: open C:\ProgramData\docker/network/files/local-kv.db.lock: Access is denied.”

In powershell enter: ./paw.ps1 ps to get a better view of the running containers.

If PAW73 is working, it should look like above.

In powershell enter: docker logs mongo to see the log file for that container.

In powershell enter: docker version to see the version you have installed.

Possible solution:

Cause could be a to old version of Docker or too few resources in the Windows server.

If you have a old version of docker-compose, like 1.23, then you need to update it with this commands:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Invoke-WebRequest "https://github.com/docker/compose/releases/download/1.29.2/docker-compose-Windows-x86_64.exe" -UseBasicParsing -OutFile $Env:ProgramFiles\Docker\docker-compose.exe

Ensure you have a backup of PAW before you run any scripts.

If the docker version is 17.06.2-ee-14 you need to upgrade it with this script, paste the code in a text file, and save it in the same folder as you have your Start.ps1 file (for example d:\ibm\paw73).

IF ((Get-Service -Name docker).status -ne 'Running') { Start-Service docker }
IF (Test-Path ./Start.ps1){
Powershell './scripts/paw.ps1 stop'
docker stop admintool
docker rm $(docker ps -a -q)
Copy-Item -Path 'C:\Program Files\docker\docker-compose.exe' -Destination "$env:TEMP"
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Install-PackageProvider -Name NuGet
Install-Module DockerMsftProvider -Force
Install-Package -Name docker -ProviderName DockerMsftProvider -verbose -RequiredVersion 20.10 -Update -Force
Move-Item -Path "$env:TEMP/docker-compose.exe" -Destination 'C:\Program Files\docker\'
docker network rm $(docker network ls -q)
Stop-Service docker
Get-ContainerNetwork | Remove-ContainerNetwork -force
Get-VMSwitch | Remove-VMSwitch -force   
Get-NetNatStaticMapping | Remove-NetNatStaticMapping -Confirm:$false
Get-NetNat | Remove-NetNat -Confirm:$false
Restart-Service HNS
Start-Service docker
Restart-Service docker
Powershell '.\scripts\paw.ps1'
Powershell '.\scripts\paw.ps1 ps'
}

Above script will clean all containers and networks in docker, and upgrade to a later version of Docker.

Run the above .ps1 script in PowerShell as administrator.

If you get the blue screen with “Planning Analytics Workspace is unavailable. Try again in a few minutes.” message, then wait at least 10 minutes, before you try accessing again. PAW can be only starting up all it’s containers, and that take a long time. Check in TASK MANAGER, if all CPU are 100%, then docker is still loading.

(If you do not have a anti-virus program running, that can interfere with docker startup.)

Also, always test in a other web browser, when there are issues in PAW.

More information:

https://www.ibm.com/support/pages/failed-start-container-failed-create-endpoint-share-proxy-network-nat-file-local-kvdblock-being-used-another-process

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=web-install-configure-tm1-microsoft-windows

https://www.ibm.com/support/pages/troubleshooting-planning-analytics-workspace-related-docker-issues

https://www.ibm.com/support/pages/container-marked-removal-and-cannot-be-started-driver-windowsfilter-failed-remove-root-filesystem-hcsshimgetcomputersystems

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=local-prerequisites

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=wnipaw-2073-whats-new-february-10-2022

docker-compose is version – upgrade it and try again.

March 8, 2022 by Roger·Comments Off

Product:
Planning Analytics Workspace 73

Microsoft Server 2016

Issue:

During Start.ps1 checks at the start of the upgrade of PAW, you get message that docker-compose is to old.

Command: Docker version

will list your docker version, in our case it was 17.06.2-ee-14

Solution:

Ensure server have connection to internet.

Start powershell as administrator.

Run this commands:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Above to active TLS1.2

Invoke-WebRequest "https://github.com/docker/compose/releases/download/1.29.2/docker-compose-Windows-x86_64.exe" -UseBasicParsing -OutFile $Env:ProgramFiles\Docker\docker-compose.exe

Above to install docker-compose version 1.29

Then try to install PAW again.

More information:

https://docs.docker.com/compose/compose-file/compose-versioning/

https://www.ibm.com/docs/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/tm1_prism_gs.pdf

TCP Provider: An existing connection was forcibly closed by the remote host

March 6, 2022 by Roger·Comments Off

Product:

Cognos Controller 10.4.2
LICENSE_CONTROLLER_version=LICENSE_CONTROLLER-AW64-ML-RTM-10.4.2000.2-0
CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2000.1098-0

Microsoft Windows 2016 Server

Microsoft SQL server 2019

Issue:

After change in Windows Server for SQL database, to not allow TLS 1.0 and TLS 1.1 protocol the Cognos Controller and FAP program can not connect to the SQL database. You have not enabled HTTPS in Cognos Configuration, therefor Cognos is not using SSL to the clients.

Cognos Analytics who uses the SQLJDBC42.JAR driver, works fine.

Error message:

TM1.SQLAPI 08001[Microsoft][ODBC Driver 11 for SQL Server]SSL Provider: An existing connection was forcibly closed by the remote host.

TM1.SQLAPI E16) Cannot connect to ODBC data source “ControllerDatabase” 08001[Microsoft][ODBC Driver 11 for SQL Server]SSL Provider: An existing connection was forcibly closed by the remote host.

Above error when you test the UDL connection to the SQL server.

Above error when you try to login to Cognos Controller.

Microsoft SQL server Native Client 11.0 Client unable to establish connection

TCP provider: An existing connection was forcibly closed by the remote host.

in Windows event log:

Could not connect to database. Database CTRL blacklisted. It will be considered again in 20 minutes.
Client unable to establish connection
TCP Provider: An existing connection was forcibly closed by the remote host.

Solution:

Upgrade the SQLNCLI.MSI program to a later version.

Download a new SQL 2012 driver from Microsoft and install that in your Controller Windows server.
https://www.microsoft.com/en-us/download/details.aspx?id=50402

Also install same SQLNCLI.MSI driver on your TM1 server where you have the FAP service running.

Above is the working driver, you should download from Microsoft. Driver version 11.4.7001.0 is correct.

Go to the Control Panel on your Cognos Controller computer and check installed version of SQL 2012.

Above is the working version of SQL 2012 driver, if you not have it – you must install it for Cognos Controller.

Above drivers does not work when you have turned off TLS 1.0 and TLS 1.1, to the SQL server.

When you install the new SQL client, you need to stop IBM Cognos Controller Batch Service and IBM Cognos FAP Service. Otherwise you will be reminded as below.