Remotely Logging Perfmon Files into SQL Server—Security Issues?

This post comes with a strongly worded warning—don’t do what I’m going to do here to anything that resembles a production environment. Hopefully some smart person, will come in and give me the obvious fix, but for now the only way to make this work is to do something really bad from a security perspective. So if you want to do this—create a test instance grant minimal rights, and then revoke them after you are done with your data loads.

Last week, our VM team asked if I could get the I/O Operations per Second (IOPs) for a large number of representative physical servers, that would be VM candidates. In this case, I took a SWAG, and took all of our physical servers that have averaged less than 20% CPU utilization and had less than 800GB total data. In order to get the IOPs number I would have to run perfmon (or go through the SAN team, but perfmon seemed easier). Using Logman, I was able to execute some PowerShell to start log collections on a large number of hosts. Below is the code I used—much of it I borrowed from HappySysAdmin.

In case you are wondering, the only two perfmon counters I was really interested in were “\PhysicalDisk(*)\Disk Reads (and writes)/sec”. I wasn’t trying to tune anything—just trying to get raw data.

So now that I had all of this data collected, I brought it back to my tools server, where my plan was to use Relog in order to load into my SQL Server. This was a flawed plan—it turns out different versions and Windows, Perfmon and Relog don’t play nice together—I ran into some issues. So rather than waste a bunch of time troubleshooting—I decided to go back to my old friend PowerShell, and it’s Invoke-Command cmdlet. For those of you who aren’t familiar Invoke-Command runs the command in the context of the host it’s being executed on (as opposed to the remote host executing the script). So in this case the version of relog would be correct for the version of PerfMon that created the *.blg file. Grant Holliday has a great blog post here on how to log *.blg files into SQL Server. I took his code and wrapped it in some PowerShell.

The Relog to SQL process does rely on an ODBC connection to the target SQL Server. So as part of my code I would create that, and then clean it up. The process for this is much better in Windows 2012/PowerShell 3.0, but since I’m not on those platforms I needed to call DOS commands like obdcconf.exe. One concern I immediately noticed was that even though I tried to pass SQL credentials to the ODBC, it would resolve using Windows credentials. I wasn’t overly concerned—Invoke-Command runs with the credentials of the executing user, in this case my account. This code is below:

So, I ran that—and I saw the following error.

So like any good admin, I went to the log

That’s interesting—Relog tries to connect to SQL Server as Anonymous Login. If you look carefully at the about PoSH—you’ll note that I run a “whoami > test.out”, which shows the credentials of the current user, well it shows up as my account. Does anyone have any idea why Relog tries to connect as Anonymous—or a workaround?

In my case, I had an instance with no secure data—so I created a login for anonymous, gave it just the rights it needed, and then deleted it after I was done. Only do this short term, and someplace where there is no secure data.

Anyway, I thought I had developed something that could be pretty useful, but was hampered by this security piece. Any thoughts in the comments are greatly appreciated.