Fix “Transaction log for database ‘VIM_VCDB’ is full” errors

This is one of those “note to self posts”, in hope this may hit me again so I don’t go wandering the Internet all over again. I have a small VMware lab at home, and a few days ago I was confronted with an issue related to vCenter – the management application for VMware’s hypervisor. I tried to connect to my vCenter installation – connection refused….ok, I’ve seen this before, probably the service is not up. Initially I thought there had been a power outage at my home (they kinda happen) and the vCenter Service hanged upon starting (this also kinda happens)

No problem I can fix it! open services snap-in remote to vCenter machine, start service, service starts, close snapin. Start vSphere Client client works, play around with it a bit, close Client.

Time goes by, I need to log back into the system again for some work. Connection refused….now this is rich, no power outage, why is the service crashing? Ok, it’s just life treating me badly VMware is acting up (not that is usually does), open service, start service, login again to vCenter, do some work, few minutes later client disconnects…reconnect not working.

Ok, troubleshooting mode now; open Splunk, sort by events from that host, anything that is not information from the system log. And there it was:

Error[VdbODBCError] (-1) “ODBC error: (42000) – [Microsoft][SQL Native Client][SQL Server]The transaction log for database ‘VIM_VCDB’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases” is returned when executing SQL statement “UPDATE VPX_VM WITH (ROWLOCK) SET SUSPEND_TIME = ? , BOOT_TIME = ? , SUSPEND_INTERVAL = ? , QUESTION_INFO = ? , MEMORY_OVERHEAD = ? , TOOLS_MOUNTED = ? , MKS_CONNECTIONS = ? , FAULT_TOLERANCE_STATE = ? , RECORD_REPLAY_STATE = ? WHERE ID = ?”

Ouch, something really broke, Immediately I made quick check to see if I had disk space left, which I had, so this was not going to be this easy.

In that case: to the Internets! Found this thread on the VMware communities. I won’t bore you anymore with the storyline, I’ll just get to fixing this issue

Note: this is probably an extremely trivial topic that does not happen on production databases, with vigilant DBA;s. However this is a homelab and I’m not a DBA ๐Ÿ™‚ and if you are reading this, probably so are you.

The Fix

To fix this you will need SQL Server Management Studio Express installed either on the server holding the databases or on a management machine (in which case you better know how to give yourself remote access to the vCenter Database Server, I couldn’t, so I installed it locally on the affected machine). You’l also need a local administrator account to run the management studio under.

Once in the management studio, select the VIM_VCDB database, right click properties:

On the left side of the new window select the File section:

So, there are 2 files, database and the logs. The error we got mentioned log files. A quick look in my setup revealed I had reserved only 460MB for logs (screenshot taken after fix). Scroll down to the right, and find the “…” button, which will let you configure the maximum size of the log files.

Now change this value to a bigger value, for a home lab 2GB is quite a lot actually, but i wanted to be safe. Close all windows by pressing OK, close the Management Studio.

After this restart VMware VirtualCenter Server service and watch your vCenter go :).

Now for a little investigation why this happened. The vCenter database holds performance data, VM metadata and the likes…but how could 8VM’s gather performance data in less than 2 months that fit into 460MB which was the configured size of the log file….Well the answer lies into vCenter Server Settings, once I started browsing the menus I remembered, that just for testing I configured the statistics logging level to 4 (highest) for each retention period, and not just for testing, I Forgot to turn it off, lesson learned now.

p.s. This my first non scripting post ๐Ÿ™‚

Restrict USB Storage Devices on Windows XP

This is one of those topics that are probably on the top 10 to do’s of anyone’s list when it comes to securing their Windows desktops. Whether it is plain dictatorship, security/confidentiality concerns/requirements, unpatched OS’s, weak/no AV solutions, the golden POLP (Principle Of Least Privileges) may force you to come up with a solution to this problem. If you are using anything else (XP, 2000, 2003 Server) except the newer versions of Windows (Vista, 7) which allow you to do this via a GPO setting, you are out of luck, there is no GPO setting or quick-fix that works.

As a short history, I went through CIA documents that were published (can’t find them anymore), Forums, Microsoft KB’s, Whitepapers, and finally came up at the other end of the tunnel with a working process.

The goal is to devise a process of denying access to USB Storage that meets following criteria:

  • Must be implemented at OS level
  • Must be deployed scripted/automatically and/or via GPO
  • Must not cripple other OS functionality (e.g. installing printer/scanner drivers)
  • Must be fully reversible by Administrators only
  • Must be working regardless if USB Storage was used before the process is put in place

The solution – explained

For disabling USB Storage there are 2 situations to cover:

  • No USB storage ever installed, user must not be able to install device
  • USB storage was previously installed by user or admin, user must not be able to use USB Storage again

Both scenarios are covered in these 6 steps:

  1. Copy usbstor.inf, usbstor.pnf, usbstor.sys to their default locations, as if a USB storage device would be installed.
  2. Restrict access to the 3 files mentioned above. We will use an implicit DENY for the local “SYSTEM” Account for these files.
  3. Remove Registry Keys that handle USB Storage device startup: HKLM\SYSTEM\CurrentControlSet\Services\USBSTOR and HKLM\SYSTEM\ControlSet001\Services\USBSTOR and HKLM\SYSTEM\ControlSet002\Services\USBSTOR
  4. Replace USB Storage related registry keys with specially crafted keys that disable startup of the USB Storage driver
  5. Apply an implicit DENY for the local SYSTEM Account on the Registry Keys mentioned above
  6. Insert USB Storage device, wait for it to be detected by OS and marvel at the fact it won’t let you install the device ๐Ÿ™ ๐Ÿ™‚

For enabling USB Storage these steps must be taken from an Account that is member of the Administrators Group

  1. Remove restrictions placed on the ubstor.* files.
  2. Remove following specially crafted Registry Keys: HKLM\SYSTEM\CurrentControlSet\Services\USBSTOR and HKLM\SYSTEM\ControlSet001\Services\USBSTOR and HKLM\SYSTEM\ControlSet002\Services\USBSTOR
  3. Remove restrictions placed on the registry keys from above
  4. Delete incompletely installed USB storage devices fron Device Manager and Reboot Computer
  5. Insert USB Storage device, wait for it to be detected by OS/go to device manager and refresh device list and marvel at the fact it works ๐Ÿ™‚

Implementation – explained

For implementing this in a scripted manner we will use batch scripting, I’m going for a low level approach, assuming you don’t have vbs / powershell on hand, vbs would be rather complicated anyway and Powershell is not installed by default on the OS. You do have some prerequisites:

  • reg.exe (available by default on XP)
  • A network share
  • set-acl (open source utility – get it, copy to a network share of choice and be happy it exists)

Disabling USB

  • The 3 usbstor files mentioned earlier, 2 are available by default (usbstor.inf and usbstor.pnf) under %WINDIR%\inf. The 3rd, usbstor.sys, unless a usb storage device was previously installed is not present. Find it under %WINDIR%\Driver Cache\i386\Sp3.cab or the other cab files there. Extract it from the cab file to the network share.
  • The piece of code that disables USB is written below, but requires that set-acl, the specified .txt, .reg, usbstor.sys files be present in the same directory from which it is executed
::Copy ubstor.sys
xcopy /R /H /Y %CD%\usbstor.sys %windir%\system32\drivers

::Secure USBSTOR.* files with ACE (only Local Administrators Full Control, local "SYSTEM" denied Full Control)
SetACL.exe -on "c:\windows" -ot file -actn restore -bckp "%CD%\usbstor_ACL.txt"

::Delete settings related to USBSTOR Service
REG DELETE HKLM\SYSTEM\CurrentControlSet\Services\USBSTOR /f
REG DELETE HKLM\SYSTEM\ControlSet001\Services\USBSTOR /f
REG DELETE HKLM\SYSTEM\ControlSet002\Services\USBSTOR /f

::Add special crafted registry keys
regedit /s "%CD%\disable_usb.reg"

::Secure keys from above with ACE (only Local Administrators Full Control, local "SYSTEM" denied Full Control)
SetACL.exe -on "hklm\SYSTEM" -ot reg -actn restore -bckp "%CD%\HKLM_ControlSet.txt"
  • Line 5 of the code uses a file that contains a specially formatted ACL applicable to the 3 usbstor files. To generate a different ACL, use the syntax below for each file you are interested in. When you are finished you can merge all text files in a single text file and add it to the script.
SetACL.exe -on "c:\windows\inf\usbstor.inf" -ot file -actn list -lst "f:sddl;w:d,s,o,g;s:b" -bckp "%CD%\usbstor_inf_ACL.txt"
  • REG command is used to delete any data that may exist in the specified registry keys (think previous installed USB Storage)
  • Once the Registry is clean of the keys, we then push a customized reg file (find it at the end of the post), that essentially changes this:

USBSTOR driver points to the file you defined (usbstor.sys, that you just set a restrictive ACL on)

DeviceCount equals zero ๐Ÿ™‚

DeviceStartUp Type is set to Disabled (more details here)

Other standard settings for that key

  • Line 16 of code, similar to the ACL for USBSTOR Files, configures the security for the registry keys we added. To customize the ACL, change it to your liking then export the ACL using the command below and update the batch code to include it.
SetACL.exe -on "hklm\SYSTEM\CurrentControlSet\Services\usbstor" -ot reg -actn list -lst "f:sddl;w:d,s,o,g;s:b" -bckp "%CD%\HKLM_CurrentControlSet.txt"

Enabling USB

This is just a question of reversing the changes made by the Disabling process. The following piece of code does just that:

::enable inheritance of permissions
SetACL.exe -on "c:\windows\inf\usbstor.inf" -ot file -actn setprot -op "DACL:np;SACL:np"
SetACL.exe -on "c:\windows\inf\usbstor.pnf" -ot file -actn setprot -op "DACL:np;SACL:np"
SetACL.exe -on "c:\windows\system32\drivers\usbstor.sys" -ot file -actn setprot -op "DACL:np;SACL:np"

::clear any non-inherited ACE
SetACL.exe -on "c:\windows\inf\usbstor.inf" -ot file -actn clear -clr "dacl,sacl"
SetACL.exe -on "c:\windows\inf\usbstor.pnf" -ot file -actn clear -clr "dacl,sacl"
SetACL.exe -on "c:\windows\system32\drivers\usbstor.sys" -ot file -actn clear -clr "dacl,sacl"

::deleting custom Registry Keys
REG DELETE HKLM\SYSTEM\CurrentControlSet\Services\USBSTOR /f
REG DELETE HKLM\SYSTEM\ControlSet001\Services\USBSTOR /f
REG DELETE HKLM\SYSTEM\ControlSet002\Services\USBSTOR /f
  • As you can see we are enabling inheritance of permissions, clearing any ACE defined explicitly on that object (the ones we pushed actually) and removing the Registry keys we also pushed. Make sure the user running this enabling process has rights to change these objects (in our case he is member of the Local Administrators Group)
  • After this is done manually clean it of any hidden installed USB Storage devices and reboot the computer. After the reboot replugging the device should allow you to install and use it again.

Phew, this was also a long post, but believe me, reaching this compressed format was a lot of work :).

Now I’ve attached this zip file containing the contents of what I’ve been talking about, it should be usable out of the box.

There is also there question I guess of securing these files so that they apply to users but users can’t get to them to “help themselves”, but that is another topics for another post perhaps.

As always any feedback is welcomed.