CogKnowHow

Product:
Planning Analytics 2.0.9.10
Microsoft Windows 2016 server

Issue:
I have several Cognos BI servers, where i need to update the planning.html file on, to make SSO work for TM1.

Solution:

On your source TM1 server, create a folder c:\script\tm1.

Create a text file serverslist.txt, where you enter the name or IP address of the servers.

Copy the updated planning.html, pmhub.html and tm1web.html files to this folder.

Create a powershell file, copyfiles.ps1, with this content:

# run the script on the source machine
# remote machines list 
$machines= Get-Content -Path "c:\Script\tm1\serverslist.txt" 
foreach ($onemachine in $machines) 
{ 
Write-Host "Currently the script is copying files on" $onemachine 
Copy-Item -Path "c:\Script\tm1\planning.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent" -Recurse
Copy-Item -Path "c:\Script\tm1\planning.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent\bi" -Recurse
Copy-Item -Path "c:\Script\tm1\pmhub.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent" -Recurse
Copy-Item -Path "c:\Script\tm1\pmhub.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent\bi" -Recurse
Copy-Item -Path "c:\Script\tm1\tm1web.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent\tm1\web" -Recurse
Copy-Item -Path "c:\Script\tm1\tm1web.html" -Destination "\\$onemachine\c$\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web" -Recurse
}

Ensure that the paths are correct for your servers, if you have installed to D: drive you need to update the powershell script to reflect this; “\\$onemachine\d$\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web”

Start a powershell window as administrator.

The person logged in must have local admin rights on all cognos servers for this to work.

When you run the script it will print out any errors, like above when it can not find the server.

Test the script first in you LAB environment, as it will overwrite any existing files on the CA11 servers.

More Information:

https://www.heelpbook.net/2020/copy-file-to-remote-servers-powershell

Powershell Copy Files | Single | Multiple Computers

https://docs.microsoft.com/en-us/answers/questions/337705/copy-file-to-multiple-remote-computers.html

https://exploringtm1.com/install-planning-analytics-workspace-to-windows-2016/

https://exploringtm1.com/how-to-upgrade-install-planning-analytics-for-excel-pax/

https://exploringtm1.com/how-to-upgrade-planning-analytics-tm1/

Product:
Microsoft SQL server 2016

Issue:
Try to find what a users have for access rights inside a database.

Suggested solution:

Enter this code as sysadmin, to find person access rights in one database:

EXECUTE AS LOGIN = 'YourDomain\User.Name' --Change This

USE database  --Change This

GO

SELECT * FROM fn_my_permissions(null, 'database');

REVERT

Enter this to see what databases a person have access to

EXECUTE AS LOGIN = 'YourDomain\User.Name' --Change This
SELECT [name]
FROM MASTER.sys.databases
WHERE HAS_DBACCESS([name]) = 1
REVERT

To find your rights in the current database:

SELECT * FROM fn_my_permissions(null, 'database');

To list dbowners

SELECT user_name(member_principal_id)

FROM   sys.database_role_members

WHERE  user_name(role_principal_id) = 'db_owner'

More Information:

https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database

http://www.sqlnuggets.com/blog/sql-scripts-check-logins-permissions/

http://www.sqlnuggets.com/blog/sql-scripts-list-databases-a-login-can-access/

https://sqlgeekspro.com/users-db_owner-access-databases/

https://www.sqlserver-dba.com/2015/05/find-database-users-mapped-to-db_owner-role.html

https://schottsql.blogspot.com/2016/04/getting-all-permissions-for-server.html

fn_my_permissions() – Sprawdzenie jakie mamy uprawnienia do bazy danych

Product:
Microsoft SQL server 2016

Issue:
When moving a SQL database to a different Windows server, you want to check the other database if they have any SP reference to that database.

Solution:

This query will loop all database and run the select statement on each, and find any store procedure that contain the word “foo”.

EXEC sp_MSforeachdb 'USE ? SELECT ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_NAME, ROUTINE_DEFINITION, LAST_ALTERED

FROM INFORMATION_SCHEMA.ROUTINES 

WHERE ROUTINE_DEFINITION LIKE ''%foo%'' 

AND ROUTINE_TYPE=''PROCEDURE'' 

ORDER BY ROUTINE_NAME'

It is necessary to set the database for the query to run against, by using the USE ? statement, otherwise the code will execute in the context of the current database.

You can also use this to search inside one database:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES 

WHERE ROUTINE_DEFINITION LIKE '%foo%' 

AND ROUTINE_TYPE='PROCEDURE'

ORDER BY ROUTINE_NAME

or use below, as above have a limit of searching only the first 8000 characters of the stored procedure.

SELECT OBJECT_NAME(object_id), definition

    FROM sys.sql_modules

    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1

    AND definition LIKE '%foo%'

More information:

https://www.mytecbits.com/microsoft/sql-server/search-find-stored-procedure

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

http://www.bradleyschacht.com/search-stored-procedure-text/

Product:

Microsoft SQL server 2016

Issue:

Is there someone inside the database?

Suggested Solution:

Run this SQL script to see who have logged in to the database:

select 

@@servername as [ServerName],

getdate() as RunTime, 

spid, DB_NAME(dbid) as DatabaseName, 

login_time, 

last_batch, 

hostname, 

program_name, 

nt_domain, 

nt_username, 

loginame 

From master..sysprocesses

To get more information, like kerberos, run this SQL script:

select c.auth_scheme,* from sys.dm_exec_sessions s join sys.dm_exec_connections c on s.session_id=c.session_id where s.host_name ='servername'

To see active connections:

EXEC sp_who2 'active';

More Information:

https://www.sqlservercentral.com/articles/is-this-database-being-used

Product:

Microsoft SQL server 2016

Issue:

Error when testing linked servers in SQL server.

Solution:

Ensure the port is open first, by start PowerShell on the from server and use Test-NetworkConnection.  Enter below to check port 1433;

tnc servername -port 1433

If firewall is open, and you get a error saying “anonymous login” then the kerberos delegation does not work. You need to check that your account (or the account that run the SQL agent service) is allowed to be delegated, check with this power-shell command;

Get-ADUser -Filter {SamAccountName -eq "YourAccountSamName"} -Properties msDS-AllowedToDelegateTo | Select-Object -ExpandProperty msDS-AllowedToDelegateTo

You must check that the SQL server machine, have in AD allow to delegate, you do this with this PowerShell command (for both servers);

Get-ADComputer ComputerName -Properties * | Format-List -Property *delegat*,msDS-AllowedToActOnBehalfOfOtherIdentity

TrustedForDelegation must be True.

Ask the Active Directory team to correct above.

 Get-ADServiceAccount -Filter {SamAccountName -eq "YourServiceAccuntNameHere"} -Properties * | Format-List -Property *delegat*

Above need to be used, if the account is a Service Account in AD.

https://docs.microsoft.com/en-us/powershell/module/activedirectory/get-adserviceaccount?view=windowsserver2022-ps

More information:

Microsoft Made an Easy Button for SPN and Double Hop Issues

Double-Hop Linked Servers

https://help.zaptechnology.com/zap%20cubexpress/currentversion/content/topics/HowTo/UsingSPN.htm

https://social.technet.microsoft.com/wiki/contents/articles/717.service-principal-names-spn-setspn-syntax.aspx

https://kb.parallels.com/en/124644

https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio/

https://www.mssqltips.com/sqlservertip/6083/understanding-sql-server-linked-servers/

https://www.c-sharpcorner.com/UploadFile/suthish_nair/linked-servers-in-sql-server-2008/

https://en.hackndo.com/constrained-unconstrained-delegation/

https://www.microsoft.com/en-us/download/details.aspx?id=39046&WT.mc_id=soc-n-%5bTR%5d-loc-%5bServices%5d-%5bfarukc%5d

Product:

Microsoft SQL server 2016

Issue:

SQL agent can not send mail. There is no error in windows event log, and no errors in SQL server log.

Solution:
Install NET Framework 3.5 on your SQL server.  Download from here:

https://www.microsoft.com/en-us/download/details.aspx?id=25150

SQL server 2016 demands Net Framework 3.5 for the mail function to work.

Check if NET Framework is installed on your server in folder:

C:\Windows\Microsoft.NET\Framework64

Folder is missing from the SQL server where SQL Mail is not working.

On a SQL server, where mail function is okay, the v3.5 folder exist.

You can also test this by go to the SQL server, start a command prompt, and go to folder

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn

Run DataBaseMail.exe

If you get a error that Net Framework can not be installed, then this is the problem.

Please note that the windows account that run the SQL Server Agent (MSSQLSERVER) must have access rights to the Net Framework 3.5 folder and its files.

You can check if SMTP mail host is working by run below PowerShell command:

Send-MailMessage -To "your.mail@company.com" -From "noreply@company.com"  -Subject "Test mail powershell" -Body "Some important plain text!" -Credential (Get-Credential) -SmtpServer "smtp-servername" -Port 25

If that works, then the SMTP and firewall is correct. Then you need to check SQL servers settings;

SELECT [sysmail_server].[account_id],

       [sysmail_account].[name] AS [Account Name],

       [servertype],

       [servername] AS [SMTP Server Address],

       [Port]

FROM [msdb].[dbo].[sysmail_server]

     INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id];

Above gives you the SMTP setup

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail';

SELECT TOP (1000) [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]

Above will show the SQL Mail profiles.

More information:

Troubleshooting Database Mail Failures

https://www.sqlshack.com/configure-database-mail-sql-server/

Check version of SQL server : https://sqlserverbuilds.blogspot.com/