On my current project we’re using SolutionScripts to perform automated initialization of the development environment. One of our steps restores a SQL Server database from a provided backup filename. However, the Powershell script that performs the restore looks in the registry to determine the default SQL Server locations for Data and Log files (this script was previously used on a build server where we had put the data and log files on separate physical drives for performance reasons).
The problem is that the Package Manager Console window in Visual Studio runs in 32-bit mode, while SQL Server is a 64-bit application and thus its registry entries are not accessible. Thus, the following command fails to read the desired value:
$dataPath = gi "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instanceName\MSSQLServer" | %{$_.GetValue("DefaultData")}One possible solution would be to use Invoke-Command to effectively execute the statement in a 64-bit process, like so:
$dataPath = Invoke-Command -ComputerName localhost -ScriptBlock {gi "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instanceName\MSSQLServer" | %{$_.GetValue("DefaultData")}}The problem with this approach is that it is using Powershell Remoting and requires that each developer execute the following command as Administrator: Enable-PsRemoting -Force
Anytime I have to execute something as an Administrator and use the -Force flag (or confirm through a bunch of prompts to explicitly change system settings), a shiver goes up my spine.
Powershell 2.0 Solution
I pieced together a solution that works with Powershell 2.0 using the following tidbits:
- While the out-of-the-box APIs prior to .NET 4.0 do not give you the ability to read the 64-bit registry from a 32-bit process, the Windows API does support it.
- With a little help from pinvoke.net you can piece together the import definitions in C# to make the necessary Windows API calls.
- With a little help from LINQPad (and plenty of tinkering and process crashes), you can get the allocation and releasing of memory buffers just right to successfully read 64-bit registry values from a 32-bit process.
- With the use of the Add-Type commandlet in Powershell, you can actually make an embedded C# class available to your scripts.
Here’s the C# source:
using Microsoft.Win32;
using System;
using System.Text;
using System.Runtime.InteropServices;
namespace Dlp
{
public static class Registry
{
static UIntPtr HKEY_LOCAL_MACHINE = new UIntPtr(0x80000002u);
const int KEY_WOW64_64KEY = 0x100;
const int KEY_ALL_ACCESS = 0xf003f;
const int KEY_QUERY_VALUE = 0x0001;
const int RegSz = 0x00000002;
const int RegMultiSz = 7;
// Define other methods and classes here
[DllImport("advapi32.dll")]
static extern int RegOpenKeyEx(
UIntPtr hKey,
string subKey,
int options,
int sam,
out UIntPtr phkResult );
/* Retrieves the type and data for the specified registry value. */
[DllImport("Advapi32.dll", EntryPoint = "RegGetValueW", CharSet = CharSet.Unicode, SetLastError = true)]
internal static extern long RegGetValue(
UIntPtr hkey,
string lpSubKey,
string lpValue,
int dwFlags,
out int pdwType,
IntPtr pvData,
ref uint pcbData);
public static string GetRegistryStringValue(string key, string subkey, string valueName)
{
UIntPtr hkey = UIntPtr.Zero;
RegOpenKeyEx(HKEY_LOCAL_MACHINE, key, 0, KEY_WOW64_64KEY | KEY_ALL_ACCESS, out hkey);
int pdwType = 0;
IntPtr pvData = IntPtr.Zero;
uint pcbData = 0;
// Determine necessary buffer length
RegGetValue(hkey, subkey, valueName, RegSz, out pdwType, pvData, ref pcbData);
// If there's no data available...
if (pcbData == 0)
return null;
// Allocate the correctly sized buffer
pvData = Marshal.AllocHGlobal((int) pcbData);
RegGetValue(hkey, subkey, valueName, RegSz, out pdwType, pvData, ref pcbData);
// Copy unmanaged buffer to a byte array
byte[] ar = new byte[pcbData-2]; // Trim trailing null character from buffer
Marshal.Copy(pvData, ar, 0, ar.Length);
// Free the unmanaged buffer
Marshal.FreeHGlobal(pvData);
// Convert the unicode bytes to a string
string value = Encoding.Unicode.GetString(ar);
return value;
}
}
}
The code makes use of the RegOpenKeyEx and RegGetValue Windows API functions, along with the ever so important KEY_WOW64_64KEY option flag which is what actually provides the 64-bit registry access. Beyond that, there’s some boring (but very dangerous) buffer management and unicode string encoding (boy, if I had a nickel for every time I crashed LINQPad while…)
With the C# function written and nicely packaged up in a namespace and a static class, it’s time to make it available to Powershell environment. I simply added a function to my database management Powershell module that looks like this:
Function Define-GetRegistryStringValue () {
$sourceCode = @"
using Microsoft.Win32;
using System;
using System.Text;
using System.Runtime.InteropServices;
namespace Dlp
{
public static class Registry
{
static UIntPtr HKEY_LOCAL_MACHINE = new UIntPtr(0x80000002u);
....
public static string GetRegistryStringValue(string key, string subkey, string valueName)
{
UIntPtr hkey = UIntPtr.Zero;
RegOpenKeyEx(HKEY_LOCAL_MACHINE, key, 0, KEY_WOW64_64KEY | KEY_ALL_ACCESS, out hkey);
....
// Convert the unicode bytes to a string
string value = Encoding.Unicode.GetString(ar);
return value;
}
}
}
"@
Add-Type -TypeDefinition $sourceCode
}
Define-GetRegistryStringValue
I probably could have defined this more succinctly with just a string variable and an inline call to Add-Type, but this worked and I had spent enough time on the problem. With the code now available to Powershell, my updated call to read the registry (which works in both 64-bit and 32-bit modes) looks like this: $dataPath = [Dlp.Registry]::GetRegistryStringValue("SOFTWARE\Microsoft\Microsoft SQL Server\$instanceName\MSSQLServer", $null, "DefaultData")
Powershell 3.0+ Solution
In .NET 4.0, Microsoft introduced the RegistryView enumeration. This enables 32-bit processes to read 64-bit values from .NET, and so it significantly simplifies the solution. Here is a Powershell function that provides the same behavior:
Function Get-RegistryValue64([string] $keyPath, [string] $valueName) {
$hklm64 = [Microsoft.Win32.RegistryKey]::OpenBaseKey([Microsoft.Win32.RegistryHive]::LocalMachine, [Microsoft.Win32.RegistryView]::Registry64);
$key = $hklm64.OpenSubKey($keyPath);
if ($key) {
return $key.GetValue($valueName)
} else {
return $null
}
}
