Reporting Services – Max Memory

Background

Reviewing one of our SQL Server Instances and noticed this entry in the SQL Server Error Log.

ErrorLog

Image

Textual

2017-10-02 09:23:00.29 Server Detected 16371 MB of RAM. This is an informational message; no user action is required.
2017-10-02 09:23:00.29 Server Using locked pages in the memory manager.
2017-10-02 09:23:01.42 Server Large Page Allocated: 32MB
2017-10-02 09:23:01.99 Server Cannot use Large Page Extensions: Failed to allocate 32MB

 

TroubleShooting

Resource Monitor

Image

Explanation

  1. Total
    • 97% Used Physical Memory
    • 428 MB available
  2. Processes
    • ReportingServicesService.exe
      • Commit :- 2,495,860 KB ( 2.495 GB )
      • Working Set :- 2,243,072  KB ( 2.495 GB )
      • Sharable :- 52,700 KB ( 50 MB )
      • Private :- 2,190,372 KB ( 2.190 GB )
    • sqlservr.exe
      • Commit :- 795,204 KB ( 795 MB )
      • Working Set :- 123,712  KB ( 123 MB )
      • Sharable :- 32,376 KB ( 32 MB )
      • Private :- 90,378 KB ( 90 MB )

Remediation

Overview

  1. Identify location where Reporting Services is located
    • We can use Control Panel, Services applet
      • “C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe”
        • MSRS12
          • SQL Server 2014
  2. Make backup of rsreportserver.config
  3. Edit rsreportserver.config
    • Add or amend
      • WorkingSetMaximum
        • Syntax
          • <WorkingSetMaximum>ValueInKB</WorkingSetMaximum>
        • Sample
          • <WorkingSetMaximum>1000000</WorkingSetMaximum>
            • 1000000 KB is 1 GB

 

Services Applet

 

rsreportserver.config

WorkingSetMaximum

Overview

Here we set maximum memory for Reporting Services to 1000000.

Value is in KB.

And, 1000000 KB translates to 1 GB.

 

Textual


<!-- Added by dadeniji on 2017-10-05 10:18 AM -->
<WorkingSetMaximum>1000000</WorkingSetMaximum> 

Image

 

Confirm

Restart MS Reporting Services and review through Log files and Resource Monitor.

ReportServerService*.log

Image

Textual

library!DefaultDomain!a5c!10/05/2017-10:17:28:: i INFO: Initializing MemorySafetyMargin to '80' percent as specified in Configuration file.
library!DefaultDomain!a5c!10/05/2017-10:17:28:: i INFO: Initializing MemoryThreshold to '90' percent as specified in Configuration file.

Explanation

  1. MemorySafetyMagin
    • Initializing MemorySafetyMargin to ’80’ percent as specified in Configuration file.
  2. MemoryThreshold
    • Initializing MemoryThreshold to ’90’ percent as specified in Configuration file.

 

Resource Monitor

Image

Explanation

  • Total
    • 85% Used Physical Memory
    • 2031 MB available
  • Processes
    • ReportingServicesService.exe
      • Commit :- 271,732 KB ( 270 MB )
      • Working Set :- 138,360 KB ( 140 MB )
      • Sharable :- 32,512 KB ( 34 MB )
      • Private :- 104,168 KB ( 104 GB )

Summary

We are satisfied with the change we made.

 

References

  1. CSS SQL Server Engineers
    • PSSQL
      • SQL Server and Large Pages Explained….
        Link
    • Adam W. Saxton
      • Troubleshooting Memory Issues with Reporting Services
        Link

Icacls – Usage – Scheduled Tasks

Background

In an earlier post, Raimund Andrée – NTFSSecurity, Link, we spoke of having considered using Microsoft’s icacls to review NTFS permissions.

icacls

Let us try using Microsoft’s icacls.

 

Sample Script

Scenario

In our use case we will be using icacls to review NTFS Permissions on the C:\Windows\System32\Tasks\ folder.

Windows saves metadata on scheduled tasks in this folder.

 

 

Code Overview

  1. Issue “setlocal” to initiate localize environment settings
  2. Set environment variables
    • set target folder to C:\Windows\System32\Tasks\
    • set appPgm to icacls
    • Set local variables to indicate entries that we will like to discard from showing in the output
  3. Issue forfiles
    • /p
      • set Target folder
        • “%_folder%.”
          • Notice the period that prefixes the enclosing quotes
          • It is needed to handle Windows batch treating the ending backslash as an escape
    • /c
      • Set command to invoke
        • cmd /c
        • if @ISDIR==FALSE
          • Skip folders, work only actual files
        • if @ext==\”\”
          • Tasks are saved without extensions and so limit files we work on to those files that do not have extensions
        • Issue “%_appPgm% @PATH /q”
          • _appPgm is previously set to icacls
            • Parameters
              • @PATH
                • Forfiles is asked to substitute the current full filename
              • /q
                • Tells icacls to suppress success when displaying messages
  4. Issue “endlocal” to revert localized environment settings

Code Actual


setlocal

set _folder=C:\Windows\System32\Tasks\
set _appPgm=icacls

set _skipInherited=
set _skipSP=

set _skipInherited=find /V "(I)"
set _skipSP=find /V "Successfully processed"

REM Added period (.) after folder name for /p argument

forfiles /P "%_folder%." /c "cmd /c  if @ISDIR==FALSE  if @ext==\"\"  echo @PATH | %_appPgm% @PATH /q"  | %_skipInherited% | %_skipSP%

endlocal

Output

Listening

Talking about traps and ‘ving to escape them.

Xscape – Who Can I Run To
Link

 

References

  1. Command Line Reference
    • Icacls
      • Management and Tools > Command-Line Reference > Command-Line Reference
        Link
    • Setlocal
      • TechNet Archive > Windows XP > Command-line reference A-Z
        Link
  2. QandA
    • StackOverflow
      • Forfiles Batch Script (Escaping @ character)
        Link
      • How to use forfiles to delete files without extension
        Link
  3. Blogs
    • SS64
      • FORFILES.exe (Native command in Vista/Windows7/2008, via Resource Kit for XP)
        Link
    • Windows Command Line
      • Srini

Win OS – Task Scheduler – Access Rights – Read & Synchronize

Background

Let us move another pebble in our understanding of Scheduled Tasks.

Tasks are saved as files in the File System.

Lineage

Here are earlier posts on our journey:

  1. Task Scheduler – The user account is unknown, the password is incorrect, or the user account does not have permission to modify the task”
    Link
  2. Raimund Andrée – NTFSSecurity
    Link
  3. Raimund Andrée – NTFSSecurity – Usage Scenario – Day 1
    Link

Error

Here is the original message graciously captured by our developer.

Image

Textual

Task Scheduler cannot apply your change.

The user account is unknown, the password is incorrect, or the user account does not have permission to modify the task.

Scenarios

Review permissions

Remote DB Server

Code


set _folder=\\p-hrdb01\c$\Windows\System32\Tasks\PS\HRDB\DBA\SQLServer

powershell .\..\getNTFSPermissions.ps1 -folder %_folder% 

Output

Explanation

  1. Inherited Permissions
    • We are not showing inherited permissions
  2. Explicit Permissions
    • NT AUTHORITY\SYSTEM
      • Access Rights
        • Read, Synchronize
          • Read
            • The user that has been assigned to run this script is granted Read access
          • Synchronize
            • Same user has also been granted Synchronize permissions
            • The helps lessens the likelihood of disruptions and conflicts as the job is ran and edited

Remote App Server

Code


set _folder=\\p-hrapp01\c$\Windows\System32\Tasks\

powershell .\..\getNTFSPermissions.ps1 -folder %_folder% 

Output

Explanation

  1. Google Update Tasks
    • Tasks
      • C:\Windows\System32\Tasks\GoogleUpdateTaskMachineCore
      • C:\Windows\System32\Tasks\GoogleUpdateTaskMachineUA
    • Account :- NT AUTHORITY\SYSTEM
    • Permissions :-
      • ReadAndExecute
      • Synchronize
  2. Ours
    • LaunchSignup
      • Task :- c:\Windows\System32\Tasks\LaunchSignup
      • Account :- daemon Account we created in Active Directory
      • Permissions :- Read, Synchronize
    • Public Equity – Allocated MV Devel
      • Task :- c:\Windows\System32\Tasks\Public Equity – Allocated MV Devel
      • Account :- daemon Account we created in Active Directory
      • Permissions :- Read, Synchronize

Summary

Job’s metadata are saved in files arranged in hierarchical folders.

The Scheduled task creator does not have permissions on the corresponding file.

The account assigned to run the job is granted read permission on the file.

The file captures and contain metadata on the task and so read permission is need for  the account to open the envelope and read its contents.

Advisory

Best practice will suggest the following

  1. Create Folders for different teams
  2. Assign NTFS permissions to folders and files to delegate and manage access

 

Sql Server – TroubleShooting – Starting Minimally

Background

In some cases we have to dig a bit into SQL Server and see why things are not running in the manner we expected.

To get that kind of sit, we have to start the engine in so called minimal mode.

 

Starting Up

Identify SQL Server Path

Using Services applet, find “SQL Server” services, right click on the service and in the “General” Tab record the executable.

What we need is the path of that executable.

Console

Launch Command Shell

Launch Command shell ( cmd.exe ) in administrator mode.

Change Directory to Services BINN Folder

Change folder using cd /D to the identified path.

Stop

Please via command line, services applet, or SQL Server Configuration Manager stop the SQL Server Instance if it is current running.

 

Starting Up

Depending on whether it is the default or named instance we take a different tact.

Default Instance

Syntax
sqlservr.exe -f -sMSSQLSERVER
Sample

Here we start the default SQL Server Instance.

sqlservr.exe -f -sMSSQLSERVER

Named Instance

Named Instance – CHASE

Syntax
sqlservr.exe -f -s [instance-name]
Sample

Here we start a named SQL Server Instance, CHASE.

sqlservr.exe -s CHASE -f

 

Output
Image

Textual
  1. Server process ID is 336.
    • Task Manager :- Process ID
      • PID :- 336
  2. Server System Manufacturer: ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform‘.
    • Server System Manufacturer
      • ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform’.
  3. Server Logging SQL Server messages in file ‘C:\Progr
    am Files\Microsoft SQL Server\MSSQL13.CHASE\MSSQL\Log\ERRORLOG‘.

    • Log File
      • C:\Progr
        am Files\Microsoft SQL Server\MSSQL13.CHASE\MSSQL\Log\ERRORLOG’
  4. Warning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
    After the server has been started with minimal configuration, you should change
    the appropriate server option value or values, stop, and then restart the server.

    • Startup Option
      • -f
        • Single-user mode
        • Change appropriate server option value or values
        • Restart Server
  5. Server is listening on [ ‘any’ <ipv6> 50000]
    Server is listening on [ ‘any’ <ipv4> 50000]
    Server is listening on [ ‘any’ <ipv6> 53275]
    Server is listening on [ ‘any’ <ipv4> 53275]

    • Network Ports of IVP6 and IPV4
    • Network Port # 50000 and 53275

Summary

Accessing the SQL Server Engine through the command line is a time tested, battle hardened pathway that we will return to in later posts.

Because it is rarely needed and because of the harm it can cause, will only revisit when can be done responsibly in a public space.

Raimund Andrée – NTFSSecurity – Usage Scenario – Day 1

Background

Now that we have downloaded and installed Raimund Andrée’s NTFSSecurity in one of the standard PowerShell Module’s folder, we are ready to write a little test code and see how well it works.

Code

Script

getNTFSPermissions.ps1


param (
      [string]$file
    , [string]$folder
    , [string]$fileExt
 )
Set-StrictMode -Version 2.0
 
#Import NTFSSecurity
Import-Module NTFSSecurity

# Declare variables
[string] $CONST_FILEMODE_DIRECTORY = "d-----";
[boolean] $fileCheck = $false;

if ([string]::IsNullOrEmpty($file))
{
    $fileCheck = $false;
}
else
{
    $fileCheck = $true;

}

if ($fileCheck -eq $false)
{

    if ([string]::IsNullOrEmpty($folder))
    {
    
        $folder = Get-Location
        
    }
    
}

function getNTFSFile([string] $_fileLocal)
{

       
    Get-NTFSAccess -Path $_fileLocal

    
} #getNTFSFile()    

function getNTFSFolder([string] $folderLocal, [string] $fileExtLocal)
{

    #Declare Local variables
    [string]$_file = $null;
    [string]$_fileFullName = $null; 
    [string]$_fileExt = $null;
    [string]$_fileMode = $null;   
    [boolean]$_fileExtMatch = $true;

    # Get files
    Get-ChildItem $folderLocal | foreach {

       $_file = $_
       $_fileFullName =  $_.FullName 
       $_fileExt = $_.extension
       $_fileMode = $_.mode    
       
       <# #$_file #$_fileFullName #$_fileMode #>
       
       $_fileExtMatch = $true;

       <# If we are matching on file extensions let us see whether it matches #>
       if ([string]::IsNullOrEmpty($fileExt ))
       {
            $_fileExtMatch = $true;
       }
       else
       {
       
            if ( $_fileExt -eq $fileExtLocal )
            {
                $_fileExtMatch = $true;
            }
            else
            {
                $_fileExtMatch = $false;
                
                #"file extension $_fileExt does not match $fileExt "
            }
       
       }
       
       
       if (`
                 ($_fileMode -ne $CONST_FILEMODE_DIRECTORY )`
            -and ( $_fileExtMatch)`
          )
       {
       
            Get-NTFSAccess -Path $_fileFullName -ExcludeInherited

       }
        
    }
    
} #getNTFSFolder()  

if ($fileCheck -eq $true)
{

    getNTFSFile $file 

}

elseif ($fileCheck -eq $false)
{

    getNTFSFolder $folder $fileExt

}


 

Sample

Get Permissions for Excel files

Code


powershell .\getNTFSPermissions.ps1 -folder C:\temp -fileExt .xlsx

Output

Get Permissions for Scheduled Tasks ( Local to machine)

Code


powershell .\getNTFSPermissions.ps1  -folder C:\Windows\System32\Tasks

Output

Raimund Andrée – NTFSSecurity

Introduction

As  a quick follow-up to our last post, “Task Scheduler – The user account is unknown, the password is incorrect, or the user account does not have permission to modify the task” ( Link ), googled for available utilities that list NTFS permissions.

Utilities

Here are some available options:

  1. Microsoft
    • icacls
  2. Raimund Andrée – NTFSSecurity
    ( powershell module )

Raimund Andrée – NTFSSecurity

We settled on “Raimund Andrée – NTFSSecurity“, we will discuss the reasons later.

Repository

URL

  1. GitHub
    • Releases

Image

Tabulated

Version File Size
NTFSSecurity 4.2.3 NTFSSecurity.zip 183 KB

 

Installation

Prepare Downloaded File

Once downloaded please unblock file…

Obviously to unblock, please click the “Unblock” button.

 

Identify Install Folder

In Powershell parlance the files are delivered as modules and need to be placed in one of the folders listed in the PSModulePath environment variable.

Command


set PSModulePath

Output

Image

Tabulated

  1. C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\
  2. C:\Program Files\WindowsPowerShell\Modules\
  3. SQL Server
    • C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\
    • C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\
    • C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\
  4. Baseline Configuration Analyzer
    • C:\Program Files\Microsoft Baseline Configuration Analyzer 2\Modules\

 

Vendor’s Installation Guideline – Location

Image

How to install
Link

 

Explanation

  1. Development
    • During development I think it is best to place in the contextual user’s documents\windows\powershell folder
  2. Production
    • In production, depending on your Version of Power
      • <= v4 C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules
      • >= V4
        • C:\Program Files\WindowsPowerShell\Modules

Deploy

Script

Sample


set _folderSrc=C:\downloads\raandree\NTFSSecurity\NTFSSecurity

set _folderDestUserSpecific=C:\Users\%username%\Documents\WindowsPowerShell\Modules\NTFSSecurity
set _folderDestPowerShellModule=C:\Program Files\WindowsPowerShell\Modules

set _folderDest=%_folderDestPowerShellModule%\NTFSSecurity

if not exist "%_folderDest%" mkdir "%_folderDest%"

xcopy "%_folderSrc%" "%_folderDest%" /s /D

Output

Validation

Script

Sample


# Import NTFSSecurity
Import-Module NTFSSecurity

#get help on Get-NTFSAccess
get-help Get-NTFSAccess

Output

Dedicated

Dedicates to MSFT’s own Raimund Andrée.

References

  1. Tutorial
    • NTFSSecurity Tutorial 1 – Getting, adding and removing permissions
      Link
    • NTFSSecurity Tutorial 2 – Managing NTFS Inheritance and Using Privileges
      Link
  2. Script Center
    • File System Security PowerShell Module 4.2.3
      Link