SQL Server – Files In use – Day 1

Background

One of the many areas that that one needs to keep an eye one when monitoring database engines  is which files are opened, how they are opened ( exclusively, read only), what other processes are competing for them, etc.

Day 1

This is Day 1 and so we will start off with the basic tools.

 

Microsoft

Lineage

Microsoft really did a very job with Resource Monitor.

Prior to Resource Monitor, Task manager was the go to quick tool.

Task Manager

Here is what Task Manager exposes:

unfortunately, it only exposes information at process level.

 

Resource Monitor

Tab – Disk

Here we see the active Disk Activities.

Explanation

  1. We are able to filter by Process
  2. And, we can order by
    • Process Name
    • File name
    • Reads/Writes/Totals

Tab – CPU

Tab – CPU – Original

When we switch over to the CPU Tab, here is what we see

Explanation

We are forced to choose a process.

Tab – CPU – Process = sqlservr.exe

Once we chose sqlservr.exe from the list of processes:

Tab – CPU – Process = sqlservr.exe = Drive C:

Explanation:
  1. Unfortunately, we were getting really bad storage utilization a few months ago, and had to move to System Drive C: till more storage was allocated
  2. Will come back and move the rollover data and log files
Tab – CPU – Process = sqlservr.exe = Drive D & E:

Explanation:
  1. Most of our SQL Server Data and log files are on Drive D & E:
  2. Will come back and segregate them

SysInternals

I real like the straightforwardness of SysInternals tools.

Handle

Here is how to use handle.

Scripts

Look for file handles

Here we ask for a specific process:

  1. -p = sqlservr
  2. type = file
Code

handle -nobanner -p sqlservr | find "File" | more

Output

Look for file handles – File  – Extension [ mdf, ndf, ldf]

Here we ask for files that have have df in their names.

  1. -p = sqlservr
  2. type = file
Code

handle -nobanner -p sqlservr | find "File" | find "df" | more

Output

 

SQL Server

Sql Server Management Studio ( SSMS )

You can also use SSMS Activity Monitor.

But, to me it is a big hammer to what one really needs.

 

Dedicated

Can’t go anywhere else but to SysInternals, Mark Russinovich & Bryce Cogswell.

To me they made it cool to want to look into things, Windows Internals anyone.

Anti Virus – Free and Inexpensive AV for Windows Server

Background

These days the top tier AntiVirus tools are free for Desktop computers.

 

Server

As Vendors have to make money somehow they usually charge a bit for server products.

And, more for corporate deploy and management.

Here are some products that can be run on servers dating back to Windows 2003.

Vendor Product OS Link Price
Avast  
 Avast For Business Endpoint Security Microsoft Windows XP/Vista/7/8/8.1/10 (32/64 bit)
Windows Server 2003, 2008, 2012/R2, 2016
Product
Link
 $31.99/Year
Comodo  
 Comodo Cloud Antivirus XP (32bit), Win7/Win8/Win8.1/Win10 (32 bit & 64 bit) Product
Link
 Free
 Immunet  
 Immunet Windows XP, Windows Vista, Windows 7, Windows 8,
Windows 10, Windows 2003, Windows 2008, Windows 2012
 Link  Free
 Panda
 Panda Free AntiVirus  Link  Free

 

 

References

Vendors

  1. Avast
    • Avast For Business Endpoint Security – PC and server
      • Security Features
        Link
      • System Requirements
        Link
  2. Comodo
    • Introduction to Comodo Cloud Antivirus
      Link
    • System Requirements
      Link
    • Download
      Link
  3.  Panda
    • Operating System
      • What are the minimum installation requirements for Panda Security for Desktops?
        Link
    • Cloud Cleaner
      Link

 

Blogs

  1. Solvps
    • Top 3 Free Antivirus Compatible with Windows Server or VPS (Updated 2017)
      Link

Cyberarms – Intrusion Detection

Background

Cyberarms Intrusion Detection is the second IDS product that we will be evaluating.  The first product been BF Guard.

Idealistic

As the saying goes everything is idealistic until it get reals.

My real world came almost week ago.  I was home that Saturday morning having cleared my schedule.  A friend had dropped off two laptops.  There were viruses and malwares to rid off, cluttered full hard drive to prune, and personal data to remove.

Never imagined, I had similar problems until I tried connecting to one of the computers in our Lab and was faced with that now familiar error message, “exceeded maximum number of connections”.

Cyberarms

Download

Downloaded Cyberarms version 2.2 from here.

Install Files

Version 2.2 only supports 64 bit systems and so those of us that that still have 32-bit systems are out of luck.

Compressed Package

The installer is packaged as a zip file.

Extracted Files

InstallationFiles_20170615_0421PM

Installation

Screen Shot

We have two sets of Install Steps:

  1. Prerequisite
    • Visual C++ 2010 Runtime Libraries (x64)
  2. Core
    • Cyberarms Intrusion Detection

Prerequisite

Visual C++ 2010 Runtime Libraries (x64)
Outline
  1. Install Components
  2. Extract Files
  3. If  “Visual C++ 2010 Runtime Libraries (x64)” exist on system
    • User is asked whether to remove or repair package
  4. Runtime Installed on system

 

Install Components?

Extracting Files

Repair or Remove

Repair is Complete

Core

Outline

  1. License Agreement
  2. Select Install Folder
  3. Confirm Installation
  4. Installing…
  5. Install Complete

License Agreement

 

Select Installation Folder

 

Confirm Installation

Installing Cyberarms Intrusion Detection

 

Installation Complete

 

Configuration

Screen Shot

Settings

Settings – Lock out configuration

Explanation
  1. Set lock threshold (unsuccessful logins )
    • By default set @ 3 unsuccessful logins
  2. Set lock duration ( minutes )
    • By default set for 20 minutes
  3. Hard lock threshold ( unsuccessful logins )
    • By default set or 10 unsuccessful logins
  4. Hard lock duration ( hours )
    • By default set for 24 hours
  5. Hard lock forever
    • In cases of multiple failed logins where hard lock threshold reached, a choice on whether to lock forever

 

Settings – Safe Networks

Explanation
  1. Define safe network also known as whitelisting

 

Settings – Email Notification

Explanation
  1. We indicated that we want to be notified on everything
    • Events
      • On soft lock events
      • On hard lock events
      • On unlock events
  2. Reports
    • Unfortunately, we are only in the evaluation phase and have yet to allocate money for a paid version.
      Therefore, No Reports yet.

Settings – SMTP Configuration

Explanation
  1. Enter Email Addresses
    • Sender address
    • Recipient address
  2. SMTP server
    • SMTP Server
  3. SMTP/SSL Port
    • 25
  4. Use SSL for communication
  5. This server requires authentication
    • Username & Password

Agents

Agents – TLS/SSL Security Agent

Because Terminal Services is the lone service that we are exposing and securing via Active Directory on this box, it is the only service that we enable.

Original Screen

Revised Screen

Explanation
  1. Override Configuration
    • Check
  2. Enable this Security Agent
    • Check
  3. Extended Configuration
    • RdpPort
      • Keep port 3389 if you have left Terminal Services running on its default port

 

Usage

We tried connecting thru RDP from a few boxes and intentionally entered wrong password.

 

Security Log

 

Explanation

The following information is logged:

  1. Intrusion #1
    • Type :- Intrusion
    • Latest Entry :- Date
    • IP Address :- z.y.x.w
    • Message :- TLS/SSL Security Agent: Possible intrusion attempt.

 

Email Notifications

Outline

  1. We have attached a couple of emails
    • Our Inbox filtered to show messages from Cyberarms IDDS
    • Test Email Notification
    • Soft Lock Notification
    • Hard Lock Notification

 

Inbox

Message – Test Message

Message – Soft Lock

Message – Hard Lock

 

Summary

Cyberarms Intrusion Detection tool is a very, very strong well engineered product.

It capables identifies intrusion attempts based on monitoring Windows Event Viewer.

The newest version requires x64 based system and for those still running 32 bit OS that might be an impedance.

 

References

  1. Configure Intrusion Detection to block based on your requirements
    Link
  2. Secure your systems today with Cyberarms Intrusion Detection and Defense System
    Link

 

BFGuard – Day 2

Background

In this post we actually start BFGuard and try to connect to the host from other workstations.

 

Target OS

Here are the Windows OS that we will use for this exercise:

  1. Server
    • Windows Server 2012 R2
  2. Client
    • Windows Server 2012 R2
    • Windows 7

What we saw

BF Guard

BF Guard – Application

Scenario – After 1 failed Login

BF Guard – Application – Statistics

Explanation
  1. ip
    • Count :- 1
    • Date :- 2016-06-15 15:34:13
      • Time in GMT, not local time

 

Scenario – After Numerous failed Logins

BF Guard – Application – Statistics

Explanation
  1. ip
    • Count :- 7
    • Date :- 2016-06-15 16:47:18
      • Time in GMT, not local time

 

BF Guard – Application – Log entrys

Explanation
  1. @ 2017-06-15 09:47:39
    • – Auto blocking IP: for: 54864000 minutes

 

BF Guard – Application – Blocked IP

Explanation
  1. IP Address
    • IP Address :-
    • From :- 2017-06-15 09:47:39
    • To      :- 2017-06-15 10:47:39
    • City    :- Blocked

 

OS – Windows

Windows Logs

Windows Logs – Security

Windows Logs – Security – Filter

Here we filter for “Audit Failure“.

Windows Logs – Security – Logs

And, here are the events captured.

 

Windows Logs – Security – Log – Detailed

Windows Logs – Security – Log – Detailed- Event ID = 4625
Image

 

Explanation
  1. Subject
    • Security SID :- NULL SID
      • Since account that we entered to login in under is not known to the targeted computer or Active Directory, we get “NULL SID
    • Logon ID :-  0x0
      • Again, unknown Logon ID
  2. Logon Type
    • Our Logon Type is 3
      • Logon Type = 3
        • Network
  3. Account for which Logon Failed
    • Security SID :- NULL SID
    • Account Name :- bobsmith
    • Account Domain :- LAB
  4. Failure Information
    • Failure Reason :- unknown username or password
    • Status :- 0xC00006D
    • Sub Status :- 0xC0000064
  5. Process Information
    • Caller Process ID :- 0x0
      • Remote Caller Process is not known
    • Caller Process Name :-
      • Remote Caller Process is not known
  6. Network Information
    • Workstation Name :- ASTSQL01
    • Source Network Address :-
      • Network Address not passed in
    • Source Port :-
      • Network Port not passed in
  7. Detailed Authentication Information
    • Logon Process :- NtlmSsp
    • Authentication Package :- NTLM
  8.  Summary
    • Log Name :- Security
    • Source :- Microsoft Windows security
    • Event ID:- 4625
    • Task Category :- Logon
    • Keywords :- Audit Failure
    • Computer :- Host attempted for logon

 

OS – Windows Firewall

Reviewed server’s Windows Firewall to see how it is configured for “Remote Desktop“.

 

Explanation

  1. Remote Desktop configured as:
    • Domain :- Yes
    • Home/Work :- Yes
    • Public :- No
    • Group Policy – Yes

 

Summary

Unfortunately, Windows was not able to capture the incoming IP Address.

BF Guard was thus unable to read the IP Address from the Windows Logs.

Because of this inability, it is not able to re-configure the local Windows Firewall and have it start blocking the Source IP Address.

 

BFGuard – Day 1

Background

Googled online to identify steps to take for securing MS Windows Terminal Services.

One of the tools mentioned is BFGuard.

BFGuard

What is BFGuard?

BFGuard stands for “Brute Force Guard”.

How does it work?

It principally monitors the local machine’s event log.  The relevant log file in this case is the “Security Log“.

Upon finding entries that indicate failed logins correlative data is gathered.  Obvious correlative data includes username and IP Address.

Once the configured maximum number of failed attempts are reached the specific IP Address is blacklisted.

Download

Please download the free tool from here.

Screen Shots Please

Log Entries

BFGuard_Free_LogEntries_20170615_0629AM

Blocked IP

BFGuard_Free_BlockedIP_20170615_0633PM

WhiteList

BFGuard_Free_WhiteList_20170615_0635AM

Statistics

BFGuard_Free_Statistics_20170615_0638AM

Functionalities

From the screen shots here are the functionalities offered:

  1. A listing of “Blocked IPs
  2. Ability to whitelist specific IP Addresses
  3. Statistics on each connected IP Address

 

Summary

Wanted to introduce the product.

In the days ahead we will revisit and update our post.

Apple – MobileSync

 

Background

This is the third of many planned posts where I attempt to recount my experience working on removing personal data and generally availing storage on a friend’s computer.

A single laptop is hard enough, but in this case he has two.

What is the score?

Let us quickly see how much storage we have overall along with what is currently available.

Computer_20170610_1024AM [BrushedUp_v2]

We appear to have 280 GB, but only 2GB is available.

Used Storage where are thou?

WinOS Tools

Disk CleanUp

Reached for “Disk Cleanup” to determine whether there are remnant files in the usual folders where those files are often located.

DiskCleanup_20170610_1023AM

Not worth our time as the best that we can reclaim is 512 MB.

 

Space Sniffer

Downloaded Uderzo Umberto’s SpaceSniffer from here.

No need to install, just run it.

MobileSync_Property (CroppedUp)

Discovered 71 GB in MobileSync.

Apple – MobileSync

What is Apple – MobileSync?

Here is the documentation when one googles on MobileSync

It’s the folder where iTunes saves the backUps of your iDevice.
And it takes so much space because each time you sync a device, previous backUps are not deleted.
Deleting the folder will, then, mean losing those backUps.

Where are Mobile Sync files stored?

MobileSync files are stored in C:\Users\<username>\AppData\Roaming\Apple Computer\MobileSync.

Remove It

Note that I did not use the techie term, delete it, as that might mean a soft delete.  I do not what 70 GB going into the recycle bin.

Below we can see that there are 22 dated items or folders

Initiate Removal

PermDelete

Progress

Deleting_140ThousandItemsRemaining (CroppedUp)

Terminal Services / Max # of Connections

Background

This last weekend, I tried connecting to one of our Lab computers and got one of those messages stating that we have reached the maximum number of connections allowed.

TroubleShooting

Confirmation

Task Manager

As I happen to be physically close to the computers, I walked over and logged on the console.

Launched Task Manager and confirmed that we indeed have ongoing sessions.

Image

Explanation

  1. In the screenshot above, yours truly is logged on from the console
  2. Whereas, os and string are remotely connected

 

Remediation

Sessions

Thankfully, the connected sessions bored usernames that I was not familiar with.

And, so acquiescing to disconnecting them was easy.

Computer Management

Next in line is to disable the account.  As they were local and not Active Directory accounts, launched Computer Management and disabled each off the ill gotten accounts.

Image

Terminal Services

Registry

Next in line is to change the network port that Terminal Services is listening on.  As we all know Terminal Services, TS, default port is 3389.

Accessed Windows Registry and changed it to a previously unused port.

As we are really not able to simply restart Terminal Services for the change to take effect, rebooted the box.

Image
Image – Before

Image – After

Windows Firewall

New Port

Configured local Windows Firewall to allow incoming connections to the new port.

Logging

Re-enabled Windows Firewall logging for failed connections.

Plans

Windows Firewall

Rather than allow the whole internet access to new network port, make a list of Internet subnets that we usually connect from and allow those alone.

Network Firewall

Review our Network router and likewise tighten its network availability, as well.

Local Windows Accounts

Be more proactive about monitoring local Windows SAM Accounts.  Investigate whether we can be alerted when new ones are created.

Moral of the Story

The same ease that you allow for your usage is the same ease passer bys can access your resources.