netnerds.net

PowerShell Get-WinEvent Bug Workaround on Windows 2008 R2 Server — Importing Windows Forwarded Events into SQL Server using PowerShell

Chrissy LeMaire — Wed, 10 Apr 2013 13:52:49 +0000

This is sort of a continuation of my earlier post, Importing Windows Forwarded Events into SQL Server using PowerShell, where I mentioned that I was unable to get the script to work on Windows 2008 R2 due to a known bug in Get-WinEvents. I had to end up deploying my solution to a Windows 2008 R2 Server and was required to write a workaround -- here it is. As always, I prefer using natively available commands, so I eschewed LogParser and used wevtutil.exe instead.

# Grab events from the last 65 minutes

[xml]$xml = (wevtutil  /r:dc qe Application /e:Events)

# build the sql data connection

$connectionString = "Data Source=SQLSERVER;Integrated Security=true;Initial Catalog=EventCollections;"

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString

$bulkCopy.DestinationTableName = "Events"


#/q:"*[System[TimeCreated[timediff(@SystemTime) <= 3900000]]]"

# build the datatable

$dt = New-Object "System.Data.DataTable"

$null = $dt.Columns.Add("ID")

$null = $dt.Columns.Add("LevelDisplayName")

$null = $dt.Columns.Add("LogName")

$null = $dt.Columns.Add("MachineName")

$null = $dt.Columns.Add("Message")

$null = $dt.Columns.Add("ProviderName")

$null = $dt.Columns.Add("RecordID")

$null = $dt.Columns.Add("TaskDisplayName")

$null = $dt.Columns.Add("TimeCreated")


# populate data table

$xml.Events.Event | ForEach-Object {

   $row = $dt.NewRow()  

      $eventID = $_.System.EventID."#text"

      if (!$eventID) { $eventID = $_.System.EventID }

      $row.Item("ID") = $eventID  

      $eventlevel = $_.System.Level

            switch ($eventlevel)

             {

                  1 {$eventLevel = "Critical"}

                  2 {$eventLevel = "Error"}

                  3 {$eventLevel = "Warning"}

                  4 {$eventLevel = "Information"}

             }

      $row.Item("LevelDisplayName") = $eventLevel

      $row.Item("LogName") = $_.System.Channel

      $row.Item("MachineName") = $_.System.Computer

      $row.Item("Message") = $_.RenderingInfo.Message

      $row.Item("ProviderName") = $_.System.Provider.Name

      $row.Item("RecordID") = $_.System.EventRecordID

      $row.Item("TaskDisplayName") = $_.RenderingInfo.Task

      $row.Item("TimeCreated") =  [datetime]$_.System.TimeCreated.SystemTime

   $dt.Rows.Add($row)

}

  

# Write to the database!

$bulkCopy.WriteToServer($dt)

This code imports events from the last 65 minutes. For the initial import, set $xml to wevtutil.exe qe ForwardedEvents /e:Events. As an aside, I was surprised to see that wevtutil is FAR faster than PowerShell's Get-WinEvent, especially during the initial import of a large logs.

PS C:\Scripts> Measure-Command {c:\scripts\final-getwinevent.ps1}
Days              : 0
Hours             : 0
Minutes           : 1
Seconds           : 19
Milliseconds      : 293
Ticks             : 792930218
TotalDays         : 0.00091774330787037
TotalHours        : 0.0220258393888889
TotalMinutes      : 1.32155036333333
TotalSeconds      : 79.2930218
TotalMilliseconds : 79293.0218

PS C:\Scripts> Measure-Command {c:\scripts\final-wevtutil.ps1}
Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 4
Milliseconds      : 957
Ticks             : 49571333
TotalDays         : 5.73742280092593E-05
TotalHours        : 0.00137698147222222
TotalMinutes      : 0.0826188883333333
TotalSeconds      : 4.9571333
TotalMilliseconds : 4957.1333

From 79 seconds to 5 for 5500 records! Looks like having to rewrite this was a good thing, after all.

Quick and Dirty: Backup SQL Server Express Instances on Enterprise Networks

Chrissy LeMaire — Fri, 05 Apr 2013 15:09:00 +0000

I know the ideal Enterprise network won't have any SQL Server Express instances, but every large network that I've ever worked on has at least a few. Many times, it's powering apps like SolarWinds or BackupExec. Since SQL Express doesn't run SQL Agent, it can be a pain to backup and maintain the systems.

There are a lot of blog posts out there that use PowerShell or some other scheduled script to perform the backups, but it occurred to me the other day that I can totally run all of my backup and maintenance scripts from another SQL Server's SQL Agent. Duh! The key is to use the Connections tab to connect to other SQL instances.

I'll be honest, because these servers may come and go and their databases are unpredictable, I don't want to install anything local like Ola Hallengren's Backup and Maintenance Scripts, so I setup simple Maintenance Plans with the following parameters:

I made one plan with multiple subplans.
Weekly: Full backup, integrity checks, index rebuild, Maintenance cleanup task (4 weeks), History cleanup task (4 weeks)
Daily: Differential, Maintenance cleanup task (4 weeks)
Staggered schedules between server backups -- I don't expect SQL Express databases to take any longer than 30 minutes, so every instance backup is scheduled 30 minutes apart.
The backups go to the same network share as the rest of the SQL instances which kinda look like this: \\sqlbackupserver\backups\HOSTNAME\full, \\sqlbackupserver\backups\HOSTNAME\diff.
No transaction log backups are scheduled -- so far, I haven't found a SQL Express instance with databases that are set to anything but the SIMPLE recovery model.

Make sure the SQL Server Agent account has adequate access to the SQL Server Express, and that you don't check "Compress backups." Otherwise, your backups will fail with a Backup Error: 3041, Severity: 16, State: 1 error code as SQL Express doesn't support compressed backups.

Safely Enable SQL Server Agent MultiServer Administration using PowerShell

Chrissy LeMaire — Fri, 05 Apr 2013 10:40:18 +0000

Update: You can't even independently schedule slave jobs. Count my organization as yet another that won't be implementing MultiServer Administration. Grrr.

I always forget about Multiserver Administration. I've actually never worked in an environment that uses it, even though it seems to have a lot of potential. I think one of the biggest reasons is that most organizations do not use SSL encryption between SQL Servers, yet out of the box, Multiserver Administration requires SSL encryption for communication between the master and the targets.

Want to change this option? You'll have to modify the registry. Come on, Microsoft: nobody wants to touch a production SQL Server's registry and I think this is the biggest roadblock to mass adoption of Multiserver Administration.

The registry subkey that needs to be changed is MsxEncryptChannelOptions. There isn't a whole lot of information about this subkey (such as what other services it impacts) but I'm hoping that since its prefixed with "Msx" that and sits in the SQLAgent key, the change will be isolated to Multiserver Administration. So here are the 3 options:

0	Disables encryption between this target server and the master server. Choose this option only when the channel between the target server and master server is secured by another means.
1	Enables encryption only between this target server and the master server, but no certificate validation is required.
2	Enables full SSL encryption and certificate validation between this target server and the master server. This setting is the default.

Like the table says, 2 (Encryption+SSL required) is the default. Most blogs I've seen change their option to 0 (No encryption), but I tested it with 1 (Encryption enabled+ SSL not required) and default out of the box SQL encryption settings and it worked. Microsoft says this about the default encryption:

Credentials (in the login packet) that are transmitted when a client application connects to SQL Server are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials.

I always prefer encryption if it's not disruptive, so this is the setting I will recommend, and the setting that is default in the script below. This script asks for the SQL Server version (SQL2k5 is not supported because I no longer use it and instance paths are more challenging.)

Write-Host "***** Set SQL Agent Encryption Options on Target Servers *****`n "


# Menu for SQL Server Version. SQL Server 2005 could work in theory, but

# it's registry values are unpredictable and I didn't want to mess.


[int]$menuChoice = 0

     while ( $menuChoice -lt 1 -or $menuChoice -gt 3 ){

     Write-host "1. SQL Server 2008"

     Write-host "2. SQL Server 2008 R2"

     Write-host "3. SQL Server 2012"

     [Int]$menuChoice = read-host "Select your SQL Server version" }

    

Switch( $menuChoice ){

     1{$SQLVersion = "10"}

     2{$SQLVersion = "10_50"}

     3{$SQLVersion = "11"}

default{$SQLVersion = "10_50"}

}


# Enter the name of your SQL Server

Write-Host "Enter the hostname of SQL Server (do not include instance name)"

$ServerName = Read-Host "If you are using a cluster, enter the individual node name"

$ServerName = $ServerName.ToUpper()


# And the instance

$Instance = Read-Host "Enter Instance Name (leave blank for default)"

if (!$Instance) {$Instance = "MSSQLSERVER" }

$Instance = $Instance.ToUpper()


Write-Host "`nOptions for Encryption`n"

[int]$menuChoice = -1

     while ( $menuChoice -lt 0 -or $menuChoice -gt 2 ){

     Write-host "0. Disables encryption between this target server and the master server."

     Write-host "1. Enables encryption only between this target server and the master server, but no certificate validation is required."

     Write-host "2. Enables full SSL encryption and certificate validation between this target server and the master server. "

     [Int]$menuChoice = read-host "Select Encryption Option for SQL Agent Master/Target Communication" }

Switch( $menuChoice ){

     0{$EncryptionOption = "0"}

     1{$EncryptionOption = "1"}

     2{$EncryptionOption = "2"}

default{$EncryptionOption = "1"}

}


$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LocalMachine", $ServerName)

$regKey= $reg.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL$SQLVersion.$Instance\SQLServerAgent",$true)


if ($regkey -ne $null) {

     $oldValue = $regKey.GetValue("MsxEncryptChannelOptions")

     $regKey.SetValue("MsxEncryptChannelOptions","0000000$EncryptionOption",[Microsoft.Win32.RegistryValueKind]::DWORD)

     Write-Host "Done"

     $newValue = $regKey.GetValue("MsxEncryptChannelOptions")

     Write-Host "Server: $Servername`nOld value: $oldValue`nNew value: $newValue"

} else

{ "No match. Make sure you typed in the proper hostname and instance name." }

(Thanks to quickclix for the easy PowerShell menu code.)

Once you've run this script and modified the settings on your Target servers, you can easily setup Multiserver Administration. Note that the default setting may create a SQL Server login for the target server automatically. I uncheck that option because I'm trying to get away from local SQL Server logins and all of my SQL Agents run under the same domain account anyway.

Importing Windows Forwarded Events into SQL Server using PowerShell

Chrissy LeMaire — Wed, 20 Mar 2013 16:38:46 +0000

Over the past couple weeks, I've looked into a number of ways of parsing and importing Windows Forwarded Events into SQL Server: from using SSIS to LogParser to PowerShell to setting up a linked server to the "Forwarding Events.evtx" file.

Ultimately, the only thing that worked was PowerShell's Get-WinEvent cmdlet. And then, it only worked in one specific case for me -- if the events are collected and parsed on a Windows 2012 server. As of today, there's an unresolved bug in Get-WinEvent that often results in NULL LevelDisplayName, Message, and TaskDisplayName columns. I copied the exact code below on a Win2k8 R2 server and a Win 8 workstation and ran into the NULLs issue repeatedly. Your results may vary, however, as some users have reported success by tweaking a few things in Win2k8 R2 Server.

So, fire up a Windows 2012 box, setup your SQL Server and let's get started:

The SQL Part

After looking at the data returned by Get-WinEvent, I found the following columns to be the most useful: ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated. Then I created a table using those columns:

CREATE DATABASE EventCollections

GO

USE EventCollections

GO

-- the table name loosely relates to the name of my Win Event Subscription name

CREATE TABLE [dbo].[GeneralEvents](

     [Id] [int] NULL,

     [LevelDisplayName] [varchar](50) NULL,

     [LogName] [varchar](50) NULL,

     [MachineName] [varchar](255) NULL,

     [Message] [varchar](max) NULL,

     [ProviderName] [varchar](255) NULL,

     [RecordID] [bigint] NULL,

     [TaskDisplayName] [varchar](50) NULL,

     [TimeCreated] [smalldatetime] NULL

)

-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates in sqlbulk imports

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-EventCombo] ON [dbo].[GeneralEvents]

(

     [RecordID] ASC,

     [MachineName] ASC,

     [LogName] ASC

) WITH (IGNORE_DUP_KEY = ON)

GO

In order to avoid duplicates during the hourly imports, I created the table using a unique index with IGNORE_DUP_KEY = ON on 3 columns: RecordID, MachineName and LogName.

Next I had to decide how I'd get the data from PowerShell into SQL Server. After reading up on sqlservercentral.com and technet, I decided on hourly imports using sqlbulkcopy.

The PowerShell Part

Forwarded Events are a tricky thing. For some reason, the way that one would usually filter Get-WinEvent results using FilterHasTable kept returning the result Get-WinEvent : No events were found that match the specified selection criteria. I found a number of others who ran into this issue, too and similar errors occurred when people attempted to use LogParser. After all that, I didn't have much hope in FilterXML working, but it actually did! So we're going to use that after we perform our initial import.

Here's the code for the initial import which gathers ALL events in Forwarded Events.

$events = Get-WinEvent ForwardedEvents |  Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated  


$connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString

$bulkCopy.DestinationTableName = "GeneralEvents"

$dt = New-Object "System.Data.DataTable"


# build the datatable

$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name

foreach ($col in $cols)  {$null = $dt.Columns.Add($col)}

  

foreach ($event in $events)

  {

     $row = $dt.NewRow()

     foreach ($col in $cols) { $row.Item($col) = $event.$col }

     $dt.Rows.Add($row)

  }

  

 # Write to the database!

 $bulkCopy.WriteToServer($dt)

You may noticed that I manually built a datatable instead of using Out-DataTable.ps1, which appears to be a fan favorite. I felt the code above kept things a little more tidy and the performance is still quite good.

Since Event Collection is an on-going thing, you'll likely want to import them on a regular basis. I built the necessary XML query by right clicking on Forwarded Events in Event Viewer -> Filter Current Log... -> Logged: (Change to one hour) -> Click XML tab at top -> Copy/Paste -> Voila.

Actually, using the syntax of this query, I figured out the syntax for FilterHashTable but having the GUI build my query makes it easy, so I stuck with that. Here is the code for the hourly import that you can setup in Task Scheduler.

# While this script is intended to run on an hourly basis, the filter is set for going back 65 minutes.

# This allows the script to run for 5 minutes without any missing any events. Because we setup the

# table using the IGNORE_DUPE_KEY = ON, duplicate entries are ignored in the database.


$xml = @'



  

    

  



'@


$events = Get-WinEvent -FilterXml $xml |  Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated  


$connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString

$bulkCopy.DestinationTableName = "GeneralEvents"

$dt = New-Object "System.Data.DataTable"


# build the datatable

$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name

foreach ($col in $cols)  {$null = $dt.Columns.Add($col)}

  

foreach ($event in $events)

  {

     $row = $dt.NewRow()

     foreach ($col in $cols) { $row.Item($col) = $event.$col }

     $dt.Rows.Add($row)

  }

# Write to the database! $bulkCopy.WriteToServer($dt)

With any luck, your SQL output should look something like this:

Woo.

EDIT: If you care about speed, check out this post where I write about using wevtutil instead of Get-WinEvent.

Centralizing Windows Events using a Collector Initiated Subscription

Chrissy LeMaire — Wed, 20 Mar 2013 14:35:38 +0000

So it seems like the best way to go about centralizing Windows Events in an Enterprise is to use GPO, but if you can't or don't want to involve Active Directory GPOs, here's a guide on using one server to go out and collect Windows Events from other servers on your domain.

Things you'll need: Windows Server 2012 or 2008 R2, the appropriate ports open on your firewall if you're running one, access to a Domain Admin service account or the ability to add a Machine account to a local group on each of the servers you're collecting from.

So here's how to setup a Windows 2012 or 2008 R2 Server as the Event Collection Server: first, open up Event Viewer, right click on Forwarded Events and click Properties.

Note that Application, Security and System look a bit different than the others. I believe that's because they are considered Classic Windows Events. Classic Windows Events can be easily parsed in PowerShell using Get-EventLog and WMI's Win32_NTEventLogFile but the newer Event Types are a bit trickier.

The first thing you see in the Forwarded Events property tab is this:

Note the location of the ForwardedEvents.evtx file as it may come in handy for future troubleshooting or archiving. Next, click on the "Subscriptions" tab, then Create.

Now here, I will create just one generic Subscription to capture all Critical and error events. You can create multiple subscriptions for differing criteria and just send all of them to the Forwarded Events log. So select "Collector Initiated" and "Select Computers." Unfortunately, you have to add each server one by one when using the GUI. If you want to add in bulk, you'll likely have to do it using Group Policy and Source Initiation. I haven't found a way to do it in PowerShell yet.

Click Select Events and select what you'd like. My servers are super verbose, and I really only care about Criticals and Errors so I select those two Event Levels, and then By log: Application and System.

Once you're done with your Event selections, click on Advanced to modify the account you'd like to use as the collector (in my lab, I used a Domain Admin account).

If you use a Machine account or a non-admin, you'll have to go to each of the forwarding servers and add the Computer or User AD account to Event Log Readers.

Here, you can also configure the Event Delivery Optimization. I left it default for now, though later I do plan to explore delivering Events over HTTPS. Click OK until you're back at Event Viewer. Wait a few minutes and the Events should start coming in. In my experience, for each new computer I setup, I'll get one Information event from the Microsoft-Windows-EventForwarder provider. The event itself is kind of useless, saying "the description for Event 111 cannot be found." but I've learned to accept it's the Event Forwarding confirming there's a new computer in the subscription.

When things get going, your Windows Forwarding collection should look something like this:

In this screenshot, I've highlighted Attach Task to Event which allows you to run a program or send an email when the event occurs. Pretty cool.

If you need additional help, Microsoft has a Quick and Dirty Large Scale Eventing for Windows guide that is good for troubleshooting.

HOW-TO Setup Windows 2012 Server Core Remote Desktop Services to Securely Administer Windows over RDP and SSL

Chrissy LeMaire — Mon, 04 Mar 2013 20:30:06 +0000

Alright, so I've wanted to setup a Remote Desktop Gateway for years, but the configuration seemed so.. time-intensive. Then I moved to Belgium, my living situation changed and I didn't want to setup a whole new VPN server to access my virtual lab.

Initially, I set up my RD Gateway using too many Remote Desktop Services: Remote Desktop Connection Broker, Remote Desktop Gateway & Remote Desktop Web Access, but that was because was lead astray by Windows 2012's new GUI. Now, I've narrowed it down only to RD Gateway and I'm even fond of Metro (:O)

So to get this going, all you have to do is install and configure the Remote Desktop Gateway Services (RD Gateway) Role. That seems obvious, but Server Manager's interface which prominently displays an unconfigured "Remote Desktop Services" tab made me think I was missing something.

During the Role installation do: Role-based or feature-based installation -> Remote Desktop Services -> Remote Desktop Gateway

Then click Next a bunch of times. Something odd, when it asks you "Do you need an alternate source path?", even if you have the Windows Server 2012 ISO attached, you'll still need to click "Specify an alternate source path" and enter D:\sources\sxs (assuming your ISO is attached to D:)

Click Install and wait for the installation to complete. Now it's time to configure RD Gateway.

OPTIONAL: If you're on a domain with a Certificate Authority, you'll want to configure IIS to use a Domain Certificate. Open IIS Manager -> Select your server -> Server Certificates -> Create Domain Certificate. For "Common Name" make sure you enter your external FQDN. Note: I chose to go with dyndns.org since I have a dynamic IP. It's required that you use an externally resolvable hostname, otherwise Remote Desktop will fail if you try to use an IP or mismatched hosts.

Now, you'll need to configure RD Gateway. Go to Server Manager -> Tasks -> RD Gateway Manager.

Click View or modify certificate properties. If you don't have a Domain Certificate, just click Create and import certificate and ensure you use your external FQDN for the certificate name. Otherwise, choose Select an existing certificate.... Choose your certificate

Click Import -> Apply. Now that you're back at the RD Gateway Manager, expand the tree under your server name. Click Policies then on the right, click Create Authorization Polices for RD Gateway. Create an RD CAP and RD RAP (Recommended). In the name field, you can enter whatever you'd like. I chose "Default" -> Next -> Add Group -> Domain Admins -> (leave Client Computer blank)

Next, you'll be given the option to Enable or Disable Device Redirection. I just choose the default (all clients) and click Next -> Next -> Next - Default -> Next -> Allow users to connect to any network resource -> Next -> Allow Connections only to port 3389 -> Next -> Finish

Finally, open up Services and Start Remote Desktop Gateway

Voila! Now you can go modify your router rules to connect port 443 to your RD Gateway Server and/or read the important notes below.

A few important things to note
As an added security pre-caution, I went into IIS and disabled Anonymous access to my root IIS folder and ensured Windows Authentication was still enabled for the RPC folders.

Configuring the Remote Desktop Client is easy. Open up your Remote Desktop Client -> Advanced -> (Connect from Anywhere) Settings.

Enter the external hostname that you entered earlier during the configuration of RD Gateway. Go back to the general tab, and enter the FQDN of the domain server you wish to connect to. Don't worry about resolving the hostname if you're using an external DNS server -- DNS is resolved at the RD Gateway so if the RD Gateway can resolve the hostname, you're set.

If you choose to use a self-signed cert or you are attempting to connect from a computer that's not on the domain, you'll have to import the SSL cert to your Trusted Root Certification Authority. Otherwise, you'll receive the error "This computer can't verify the identity of the RD Gateway 'sample.server.com'. It's not safe to connect to servers that are not identified. Contact your network administrator for assistance."

There are a few ways to do this, but here's how I do it. I use Chrome to hit my server (ex. https://myserver.dyndns.org)

Click Certificate Information -> Details -> Copy to File. Save the cert, then find it using Windows Explorer. Right-click on the cert -> Install Certificate -> Place all Certificate in the Following Store -> Trusted Root Certification Authority -> Next -> Finish -> Yes.

You should now be able to connect and securely manage your network, all over SSL

NoMachine NX for OS X Successfully Authenticates But Won’t Load Sessions

Chrissy LeMaire — Fri, 01 Mar 2013 13:31:46 +0000

I recently discovered NoMachine's NX while looking for an easy way to encrypt VNC. NX, if you aren't familiar with it, is generally a PITA to setup, but well worth the pain (on Linux anyway). All traffic goes over SSH and while the performance isn't as great as Windows' RDP, it's faster and more secure than VNC. NoMachine's NX client works on Windows, OS X and Linux and even works well on roaming profiles.

Installing NX Server on OS X is challenging. Well, installing it is easy, getting it to work is another story.

First, starting with Mountain Lion, Apple no longer ships OS X with X11 already installed so XQuartz must be downloaded and installed manually. Initially, I installed XQuartz X11 2.7.4 and NX for OSX 4.0.181-7.

After adding the nx user to my allowed list of SSH logins, connecting was easy using the account I use to login to my MacBook Pro everyday. When prompted for Desktop type, I selected Shadow.

Once I connected, my session would appear for a split second then disappear. If you're quick, sometimes you can catch the session before it disappears then click Attach.

Oops! Missed it

Got it!

Now, initially when I'd attach, it would hang at "Requesting users authorization" but no window would pop up to request the authorization. I checked to ensure that PhysicalDesktopAuthorization 0 was set in server.cfg and sure enough it was. I thought, then, that maybe there was an issue with xauth. I spent about a day troubleshooting that, and eventually tried a different version of XQuartz. I uninstalled everything across the board. Then reinstalled everything across the board and still no dice. Messing with shadowing in the configs proved fruitless and decided to pour over NX's documentation one last time. This time, I saw that it should have installed the nxnode --mirror service, but no such service was running.

I created /Library/LaunchDaemons/com.nomachine.node.plist and pasted the following into it, ensuring that Username was set to my regular user account (otherwise, my session never showed)









        Label

        com.nomachine.node

        KeepAlive

        

        ProgramArguments

        

                /Applications/NoMachine Service.app/Contents/Frameworks/bin/nxnode

                --mirror

        

        UserName

        chrissylemaire

        RunAtLoad

        

        StandardErrorPath

        /Applications/NoMachine Service.app/Contents/Frameworks/var/log/nxnode_stderr.log

        StandardOutPath

        /Applications/NoMachine Service.app/Contents/Frameworks/var/log/nxnode_stdout.log

        WorkingDirectory

        /Applications/NoMachine Service.app/Contents/Frameworks/bin/

        Debug

I ran launchctl load -w /Library/LaunchDaemons/com.nomachine.node.plist and voila! My screen appeared. It was ugly and smudgy, though, no matter how well I optimized my client settings. Look:

Also, it often freezes about 10 seconds after. Seeing that the screen quality wasn't worth it anyway, I decided to go with VNC over SSL until NoMachine NX is out of beta for OS X. Ultimately, I really wanted remote access to manage my virtual Windows farm, so I finally got around to installing and configuring Remote Desktop Gateway. If you're a Windows admin who wants secure Remote Desktop access, you should try Remote Desktop Gateway which does RDP over SSL RPC. It was easy-ish to setup and the quality is just as you'd expect from RDP. Slick!

OS X: launchctl: error unloading

Chrissy LeMaire — Thu, 28 Feb 2013 15:05:17 +0000

Need to uninstall xquartz but keep running into the following error?

macbook:~ clem$ sudo launchctl unload -w /Library/LaunchAgents/org.macosforge.xquartz.startx.plist

launchctl: Error unloading: org.macosforge.xquartz.startx

For whatever reason, you have to run this particular unload as a regular user and not as root or using sudo. Once you do that, it should successfully unload.

Leveraging SQL Database Snapshots

Brandon Abshire — Thu, 02 Aug 2012 21:18:34 +0000

I've known about SQL Database Snapshots for a while, but never really took the time to use the feature. Lately, I've gotten into mirroring again and decided to play around with Database Snapshots. I should clarify at this point that I'm speaking about the native SQL snapshot capabilities, and not a filer technology (such as NetApp Snaps).

There are probably many good reasons to use a snapshot, but I'm going to cover three scenarios.

Application Upgrades

Often times prior to an application upgrade I am asked to run a full backup for the database. My typical technique for large databases is to schedule a full backup a bit earlier than the maintenance period and then run a differential backup once the application services have been shut down. This will dramatically speed up the time it takes to back up the database, while still providing the ability to fail back if the upgrade goes awry. This has been successful for me in the past, but I decided maybe I should try to take a database snapshot instead and if a fail back is needed, I can restore the database from the snapshot.

I am a little apprehensive to use this method, simply because I'm not comfortable with it. And since data is so precious (and I'd get fired for losing it), I typically have continued to employ the full/differential backup method. I will, however, consider in a test environment testing this methodology on databases of various sizes to increase my comfort level.

Assuming you are comfortable with giving it a shot, here's what you would do...
Prior to your application upgrade, once all app services have been shut down and the database is no longer experiencing changes by users, create the database snapshot.
In this example, taken from MSDN, a snapshot is taken of the AdventureWorks database. The value shown for NAME = , is the actual logical data file name of the database you want to make the snapshot from. This is not a new name for your snapshot.

CREATE DATABASE AdventureWorks_dbss1800 ON

( NAME = AdventureWorks_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )

AS SNAPSHOT OF AdventureWorks;

GO

Once your snapshot is successful, you can access it from the "Database Snapshots" folder in Management Studio. It's a point-in-time copy of your database and is created instantly. Then, once your application upgrade is complete, you can delete it, or restore back to it if necessary. I have not recovered a large database from a snapshot, but I believe it would be extremely fast as well. From what I know of snapshots, it maintains pointers to the original data, and when new data blocks are modified, they are written to new blocks on the storage system. So I assume that restoring from a snapshot will revert the pointers back to that state, allowing for an instant restore.

USE master;

-- Reverting AdventureWorks to AdventureWorks_dbss1800

RESTORE DATABASE AdventureWorks from

DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';

GO

Mirroring

Another handy way to leverage snapshots is when mirroring. As you probably know, when you are mirroring a database to another server, the target database on the mirror server cannot be read. If you need to verify that something made it over, or whatever the circumstance may be, you can create a snapshot of that mirrored database and then query it as a normal database. Depending on performance impact, you can most likely use this as a reporting server as well if your data does not have to be real-time.

Nightly Refreshes in a Training Environment

Many training environments have the desire to revert to a clean state prior to each training session. The main way to revert may be to set up a SQL Agent job to restore from a flat file, but this is another scenario that I want to restore from a snapshot. When my training environment is in it's "gold copy" state, I took a flat file backup just to cover my bases. Then a snapshot. Instead of setting up a job to overwrite the database from the flat file, I just execute my restore from snapshot command, and the database reverts back. It's easier and there are less moving parts.

Things to Consider

As I stated earlier, from my knowledge of snapshots, a snapshot is just pointers to blocks on a file system. That's why the snapshot is instant. The downside to this, however, is that when you query your snapshot, you are hitting the same blocks on the disk as the live database. If you create too much I/O load against your snapshot, you will also be affecting the live database and can impact performance.

When the live database modifies data, it will write new blocks to the file system. If your system has a high rate of change and snapshots exist, you will have to account for extra storage. As new blocks are written, storage usage will increase, even if the database is not actually growing in size.

OS X: How to Setup NAT on Lion and Mountain Lion

Chrissy LeMaire — Sun, 15 Jul 2012 03:33:12 +0000

It appears Apple changed the way NAT works, starting in Lion and this upset a lot of people. Apparently, nat would just stop working for some folks after they upgraded to Lion from Snow Leopard.

Finding really in-depth information about Apple's NAT implementation is surprisingly hard. While attempting to figure out how to setup a virtual network for two MacBooks connected to one another over Ethernet, I found a few helpful resources, but none helped me understand exactly how OS X does natd within InternetSharing and no matter whose blog or tutorial I followed, I just couldn't get /usr/sbin/natd to forward my packets. This is how my setup first looked...

I experimented with VLANs, bridges, routing tables, Parallels NAT, Apple NAT, InternetSharing, ipfw, config files and other stuff I've forgotten. I typed in netstat -nr|more and ifconfig more times than I can count. One thing I didn't get into was pf, though. It looked a little too complex.

First, the reason(s) why I didn't want to use Apple's InternetSharing app even though while using it, the VMs on both MacBooks could ping one another and the Internet. I'm a control freak. I want to manage my own everything. My own DNS, my own DHCP server and I want to pick my own damn subnet. Apple says that you can change the subnet by modifying SharingNumberStart in the nat defaults file but this won't work if you already have any device assigned to an IP in that subnet. To avoid conflicts, InternetSharing takes it upon itself to modify the subnet by one octet (172.20.0.1 -> 172.20.1.1) which is the opposite of what I want. I was also unable to find any documentation on how to modify the subnet mask, so I don't even think that's possible with InternetSharing.

My network requirements aren't that common, admittedly. It's not often that someone will want to hook up two MacBook Pros and run a lil virtual network between them using only an Ethernet cable but, eh, I do. Btw, did you know that you don't need a crossover cable for MacBooks to talk to one another directly over gigabit Ethernet? Me either. Anyway, I didn't want to buy a gigabit router because this is a very short term project. And I thought I'd enjoy the challenge of figuring this out.

I knew my nat configs were mostly right, I was just missing something with the routes. I finally found the last piece using route monitor and watching what InternetSharing changed when it was turned on and all client VMs could ping each other and the Internet(s).

Check out the routing table InternetSharing created:

192.168.1.1 is my Wireless connection, 192.168.2.1 is Apple's NAT subnet and the other nets were created by Parallels. Most network admins I talked to immediately thought the multiple gateways were causing an issue but this is the setup that actually worked, it just wasn't customizable enough. By the way, BSD has one of the few tcp/ip stacks that allow you to have multiple gateways (so I read.)

So after about a week of prodding, I figured out that this is what Apple basically does within InternetSharing:

gwdev=en1

ifconfig bridge0 create

ifconfig bridge0 up

ifconfig bridge0 addm en0

ifconfig bridge0 172.20.0.1

route add default -interface bridge0 -ifscope bridge0 -cloning

sysctl -w net.inet.ip.forwarding=1

/sbin/ipfw add 100 divert natd ip from any to any via $gwdev

/usr/sbin/natd -interface $gwdev -use_sockets -same_ports -unregistered_only -dynamic -clamp_mss -enable_natportmap -natportmap_interface en0

That and a bunch of error handling and things related to DHCP and DNS. It also checks to see if the Internet connection exists and deletes the nat/bridge if it doesn't and recreates it when the connection is back up.

Here's a visual of my network after running that script:

I need to start working on my project so further error handling and automation are going to be dealt with later.

Below is the code I've started using on my machine. My guest OS network runs a Windows domain and has its own DNS and DHCP servers so those are not addressed in this script. They are really important services, though so if you don't have them setup, you'll want to do that.

Oh, it also doesn't make any changes to the firewall except to add a forwarding rule. You'll want to setup your firewall, too. Annnd this script doesn't use a specific network interface. I make it detect the current gateway then use that interface. If it can't find a gateway, it uses en1. I should probably make it just die but whatever.

#!/bin/bash


# Get the interface name for the gateway

gwdev=`netstat -nr | grep default | awk '{ print $6 }' | head -1`


# If none are found, set the gateway to en1 (generally Wifi on OS X)

if [ -z "$gwdev" ]; then

gwdev=en1

fi


# Create a bridge, add the Ethernet device

ifconfig bridge0 create

ifconfig bridge0 up

ifconfig bridge0 addm en0

# Give it an IP, route bridge0's traffic to bridge0

ifconfig bridge0 172.20.0.1

route add default -interface bridge0 -ifscope bridge0 -cloning


# Enable IP forwarding, add a firewall rule to send all natd traffic to the real gateway

# Start natd with a whole bunch of options

sysctl -w net.inet.ip.forwarding=1

/sbin/ipfw add 100 divert natd ip from any to any via $gwdev

/usr/sbin/natd -interface $gwdev -use_sockets -same_ports -unregistered_only -dynamic -clamp_mss -enable_natportmap -natportmap_interface en0

And to stop...

#!/bin/bash

# All these steps look excessive but address

# network instability issues created by not doing them


gwdev=`netstat -nr | grep default | awk '{ print $6 }' | head -1`


if [ -z "$gwdev" ]; then

gwdev=en1

fi


killall natd

/sbin/ipfw delete 100 divert natd ip from any to any via $gwdev

sysctl -w net.inet.ip.forwarding=0

ifconfig $gwdev down

ifconfig en0 down

route delete default -interface bridge0 -ifscope bridge0

ifconfig bridge0 172.20.0.1 delete

ifconfig bridge0 deletem en0

ifconfig bridge0 destroy

ifconfig en0 up

ifconfig $gwdev up

One big important thing for laptop users is that natd becomes unstable after the laptop wakes from sleep. To address that, I created the scripts above, installed sleepwatcher, and set sleepwatcher to run /sbin/stopnat on sleep and /sbin/startnat it on wake. This makes natd run 24/7 so make sure that's what you want. I also need to put it in the startup but haven't yet.

Here's that setup, real quick:
sudo port install sleepwatcher
/usr/local/sbin/sleepwatcher -d --wakeup /sbin/startnat --sleep /sbin/stopnat

It's likely I'll keep develop this more over time, but for now, my clusters need my attention. I know it's a bit of a pain just copy/pasting so give me about a month or eight and I'll package up the files nice and put em up here.

Update: If you're looking to do this without NAT, check out Thomas' post about bridging ethernet interfaces in OS X.

References: OS X Advanced Server Guide, Mac OS X for Unix Geeks, a blog post by akutz and every support forum for OSX, FreeBSD and Parallels ever.