Product:
Microsoft SQL server 2016
Microsoft Windows 2019 server
Issue:
List size and location of SQL database files?
Solution:
In SQL management studio enter this query;
SELECT db.name AS DBName, type_desc AS FileType, Physical_Name AS Location,mf.size/128 as Size_in_MB FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id ORDER BY Size_in_MB DESC,DBName
More information:
How to determine free space and file size for SQL Server databases
Product:
Microsoft SQL server 2016
Microsoft Windows 2019
Issue:
How do i see what roles a user is member of? The roles show what kind of user access he gets in the database.
Solution:
In SQL management studio, enter this query, to find roles the user is part of;
exec xp_logininfo 'DOMAIN\username', 'all'
In powershell you can enter this command to find content of groups
Get-ADGroupMember -Identity adgroupname | select name, objectclass
Or also expand to find users in groups
Get-ADGroupMember -Identity adgroupname -Recursive| select name, samaccountname
More information:
https://4sysops.com/wiki/how-to-install-the-powershell-active-directory-module/
https://ss64.com/ps/get-adgroupmember.html
https://www.sqlserver-dba.com/2016/01/how-to-query-active-directory-with-xp_logininfo.html
https://www.mssqltips.com/sqlservertip/1252/auditing-windows-groups-from-sql-server/
Product:
Cognos Analytics
Microsoft Windows 2016 server
Issue:
How do i check my computer for this issue?
Suggested solution:
Paste below code in a text file, name the file to check.ps1
gci 'C:\' -rec -force -include *.jar -ea 0 | foreach {select-string "JndiLookup.class" $_} | select -exp Path
Place the file in c:\temp and start a powershell session:
.\check.ps1 > result.txt
Run the file with pipe the result to a text file, so you later easy can check what files can be an issue.
The command will check in jar files if they have the string JndiLookup.class
It will list the jar files that can have the issue, one example is C:\Program Files\ibm\cognos\analytics\bin\ThirdPartyCertificateTool.jar. Think that this program is not running all the time, it is only used when you use the tool from the command line. Then this jar file have a very little risk. It is worse for web-servers and web applications that run all the time.
You can unzip a jar file, to check its content.
If you remove the file JndiLookup.class and zip it back to a JAR file, you have cleaned the program.
More information:
https://www.ibm.com/support/pages/node/6526474
https://www.ibm.com/blogs/psirt/an-update-on-the-apache-log4j-cve-2021-44228-vulnerability/
Within IBM Planning Analytics 2.0, only the IBM Planning Analytics Workspace component of IBM Planning Analytics is affected by a security vulnerability. Apache Log4j is used by IBM Planning Analytics Workspace as part of its logging infrastructure. This bulletin addresses the exposure to the Apache Log4j (CVE-2021-44228) vulnerability.
Log4j 1.2.17 and 1.x does not contain any of the same lookup / template evaluation code, and the only class related to JNDI (JMSAppender) does not appear to connect to user-controlled remote systems based on log events. Older version of Cognos Controller that uses log4j-1.2.7.jar, does not have the same issue.
Product:
Microsoft SQL server 2016
Issue:
Want a list of all collation settings on the databases.
Solution:
Run this SQL query on server:
USE Master GO SELECT NAME, COLLATION_NAME FROM sys.Databases ORDER BY DATABASE_ID ASC GO
More information:
Most common are: SQL_Latin1_General_CP1_CI_AS
Product:
Microsoft SQL server 2016
Issue:
Try to find if any SQL agent jobs have reference to file foo.
Solution:
Run this SQL query on the server:
SELECT s.step_id as 'Step ID', j.[name] as 'SQL Agent Job Name', s.database_name as 'DB Name', s.command as 'Command' FROM msdb.dbo.sysjobsteps AS s INNER JOIN msdb.dbo.sysjobs AS j ON s.job_id = j.job_id WHERE s.command LIKE '%foo%'
More Information:
https://www.sqlserver-dba.com/2020/05/how-to-search-sql-server-agent-command-text-for-a-string.html
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
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:
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
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.
More information:
https://help.zaptechnology.com/zap%20cubexpress/currentversion/content/topics/HowTo/UsingSPN.htm
https://kb.parallels.com/en/124644
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/