SQL Server Memory Consumption and MS Ops Manager

Our Ops Manager server has been nagging us about insufficient memory resource of late.  It even started crashing owing to resource exhaustion.  After doubling the available system RAM, it stopped crashing (hurray!), but it has not stopped complaining about insufficient free memory (boo!).

At issue, I believe, is aggressive SQL query and data caching.  SQL DB administrators are no-doubt aware of SQL Server’s aggressive use of free memory for cache.  On dedicated DB servers, this is, no doubt, a good thing.  However, on systems that have other work to do, it appears the cache grabbing can cause some resource contention.

I decided to limit the memory available to SQL to a large, but more reasonable amount.  This will allow OpsMgr more flexibility in the event that it needs more RAM.  A little TSQL is required to make the change happen:

--Reconfigure SQL to display advanced control options:
USE master;
EXEC sp_configure 'show advanced option', '1';

--Display current SQL configuration options, including "max server memory":
EXEC sp_configure;

--Sets "max server memory" to 9728 Mb, or 9.5 Gb:
USE master;
EXEC sp_configure 'max server memory', 9728;

The final fix is to set an override on the server free memory alerting threshold to allow the OpsMgr server to run at about 90% memory utilization… when you have 16Gb of RAM, 90% is not a bad place to be.

Official MS Documentation here:
(note there is a syntax error in the code sample on this page… “options” should be the singular “option”. Also, stored procedures should be called using EXEC… it just looks cleaner.)
General usage of sp_configure, used (incorrectly) in the previous link:

F5 Load Balancing – Performance (Layer 4) mode and Windows Server 2008

NOTE: This article has been updated with a correction to the NetSH commands. Previously I documented the “forwarding” should be enabled on the interfaces, but “weak host receive” and “weak host send” is more accurate, as documented here:

Recently we had a problem with a web applicaiton configured for SSL-offload on our Load Balancers.  Our F5 Guru (Ben Coddington) recommended that we swich to a “Layer 4 forwarding” configuration.  In this mode, the F5 will forward TCP packets from the client directly to the web server without altering packet content, which is just what we needed.

Making this work on Server 2008 took a bit of extra leg work, though.  Here are the bones of it:

  • On the F5, create a new Virtual Server using the Type category “Performance (Layer 4)”.  Make sure that address translation and port translation are disabled.
  • Create a new F5 Pool that uses a simple port 443/ssl health monitor.  You could use any of a number of load balancing methods, but I cose “Round Robin” because it is in keeping with the “simpler is better” school of thought.
  • On the Server 2008 system, add a “loopback adapter” in the Device Manager.  (At the root of the MMC console, right-click the computer and select “Add legacy device”.  It will be of type “network adapter”, from manfacturer “Microsoft”, and have a name containing “loopback adapter”).
  • Assign the load balanced IP to the loopback adpater with netmask “”.
  • Here is the trick… you must now allow “weak host receive” on all network interfaces involved with load balancing on the Server 2008 system, and “weak host send” on the loopback interface. If this step is skipped, the Windows server will drop all packets destined for the load balancer address:
    • netsh
      interface ipv4
      set interface "Loopback Connection" weakhostreceive=enable
      set interface "Public Network" weakhostreceive=enabled
      set interface "Loopback Connection" weakhostsend=enabled
  • Make sure you have a vaild SSL certificate configured on all RDGateway systems in your farm.

That’s about it… The F5 will forward all packets sent to the load balanced IP to the next pool member in the rotation (barring persistence).  The Server 2008 host will receive the packet, and forward it to the loopback adapter (following TCP/IP routing logic).  The Server 2008 host will reply directly to the client.  Amazingly, it all seems to work.

Exploring Smart Cards and Windows Logon

I have been having some “fun” this week in exploring two-factor authentication possibilities for Macintosh and Windows 7 clients when connecting to Server 2008 and Server 2003 resources, especially via RDP.  Findings so far:

All Smart Cards are not created Equal:

  • Microsoft released a new Cryptographic API (CNG) with Vista/Server 2008.  This API allows Smart Cards to use the “Microsoft Smart Card Key Storage Provider” CSP (which, apparently, is part of the MS “Base CSP”), instead of a vendor-specific CSP, when generating certificates for Smart Card-based logon.  You must still install a driver for each Smart Card, but very often these drivers are available though Windows Update.
    • Aladdin/SafeNet eTokens do not support CNG, so you cannot use CNG-based Certificate Templates (i.e. Server 2008-compatible templates) when issuing Smart Card Logon certificates to Aladdin eTokens.  Thus, you must make sure that any Certificate Template that you use for Smart Cards are compatible with Server 2003 or earlier.  You also will need a software stack including the eToken CSP and drivers before you will be able to perform Smart Card login using an eToken.
    • Gemalto .NET Smart Cards do support CNG, so you can use CNG certificate templates with these Smart Cards.
    • RSA SecurID Hybrid Authenticators do not appear to support CNG, according to their (dated) product data sheet, so I expect you would need a custom CSP to use these for Windows Logon, too.
  • PGP Desktop can be configured to use keys stored on a Smart Card to unlock PGP-WDE encrypted drives.  However, only a few vendor’s Smart Cards are supported in this capacity, probably because PGP has hard-coded in support for only a few CSPs:
    • Aladdin eToken devices are supported both for WDE sign-on and for Administrator Key storage.
    • RSA Smart Cards are supported for WDS sign-on only
    • Gemalto Smart Cards are not supported at all.

Web Service Enrollment was dead:

  • Most how-to sites you visit on certificate enrollment and smart card logon (including one in Tech Net) state that you should set up a Certificate Enrollment Agent Workstation to use the Web Services interface on your MS Certificate Server.  Guess what?  The procedures do not work anymore on Server 2008:
  • Server 2008 R2 has a new Web Enrollment interface that supports Smart Card enrollment from Vista/Win7 workstations.  Ban the use of Server 2008 R1!
  • Use the “Certificates” MMC snap-in in place of web enrollment if you must run Server 2008 (R1).

RDGateway – Smart Card Authentication requires trust:

  • Smart Card auth to our Remote Desktop Gateway Load Balancing cluster (based on F5) was failing.  Apparenly something about the SSL-offload configuration was creating a trustworthiness problem, and this was preventing Kerberos/Smart Card authentication from working.  We switched to a TCP/Layer-4 forwarding config, and now Smart Card authorization works just fine.  Note that the config that works is not the one that was recommended by F5.  They are big into TCP offload over there.  The thing is, for Kerberos and Smart Cards to work in an SSL-offload config, you need F5’s expensive Advanced Client Authenticaiton module.  I do not need more cost and complexity, so we will keep things simple this time.