Getting SQL Server registry settings via SQLCLR table-valued user-defined function...
SQL Server stores several configuration values like data path root, program directory, default domain etc in the registry. These values can be obtained by using the SMO Settings object but you can use it only from the client side. SMO is alo not supported in SQLCLR so it is not possible to leverage the functionality on server-side directly. So if you want to obtain the settings from TSQL then it is difficult to do. This problem comes up quite often in the newsgroups and the solutions using undocumented extended stored procedures are not pretty/advisable.
Anyway to solve this problem, I wrote a SQLCLR table-valued user-defined function (TVF) that accesses the registry settings on the server directly and returns them as a table. This can be used to easily query and get registry settings for any instance on a server. The C# code for the SQLCLR TVF is shown below:
using System;
using System.Collections;
using System.Text;
using Microsoft.Win32;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
/*=========================================================================================
File: SqlRegSettings.cs
Summary: Table-valued function to retrieve registry settings for any
SQL Server instance.
Parameter: InstanceName - Name of the instance whose registry entries should be retrieved
Specify default or '' to automatically determine connected instance
Specify NULL to refer to default instance
Specify name of instance to get registry entries for specific instance
Date: May 24, 2005
---------------------------------------------------------------------
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
============================================================================================ */
[assembly: System.Security.Permissions.RegistryPermissionAttribute(System.Security.Permissions.SecurityAction.RequestMinimum, Read = @"HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server")]
[assembly:CLSCompliant(true)]
namespace Microsoft.Samples.SqlServer
{
public sealed class SqlRegSettings
{
const string SOFTWARE_KEY_NAME = @"Software";
const string MICROSOFT_KEY_NAME = @"Microsoft";
const string SQLSERVER_KEY_NAME = @"Microsoft SQL Server";
const string DEFAULT_SQLSERVER_KEY_NAME = @"MSSQLServer";
const string INSTANCES_KEY_NAME = @"Instance Names";
const string PRODUCT_KEY_NAME = @"SQL";
const string SETUP_KEY_NAME = @"Setup";
private static readonly string[] MSSQL_VALUES = new string[11]{
"AuditLevel", "BackupDirectory", "DefaultCollationName", "DefaultDomain", "DefaultLogin", "FullTextDefaultPath",
"LoginMode", "Map#", "Map$", "Map_", "SetHostName"
};
private static readonly string[] SETUP_VALUES = new string[5] {
"Collation", "SQLBinRoot", "SQLDataRoot", "SQLPath", "SqlProgramDir"
};
SqlRegSettings()
{
}
[SqlFunction(Name = "SqlRegSettings_CS", FillRowMethodName = "FillRegistryRow", DataAccess = DataAccessKind.Read, TableDefinition = "RegistryValueName nvarchar(128), RegistryValue nvarchar(4000)")]
public static IEnumerable GetSqlRegSettings(SqlString argument)
{
string argumentValue = null;
string instanceName = null;
RegistryKey instanceRoot = null;
RegistryKey rk = null;
Hashtable registryValues = new Hashtable();
// Determine instance name only if default parameter value was used in TVF call
// select * from SqlRegSettings(default)
// And TVF parameter default is specified as ''
if (!argument.IsNull)
{
argumentValue = argument.ToString();
if (argumentValue.Length == 0)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select serverproperty('InstanceName') as InstanceName";
instanceName = (string)cmd.ExecuteScalar();
}
conn.Close();
}
}
else
{
instanceName = argumentValue;
}
}
// Get the registry key based on instance name:
GetInstanceRootKey(instanceName, out instanceRoot);
// Return a hashtable with the key/value pairs:
if (instanceRoot != null)
{
// Get the default values under MSSQLServer:
rk = instanceRoot.OpenSubKey(DEFAULT_SQLSERVER_KEY_NAME);
foreach (string valueName in MSSQL_VALUES)
{
if (rk.GetValue(valueName) != null)
registryValues.Add(valueName, rk.GetValue(valueName).ToString());
}
rk.Close();
rk = null;
// Get the values under Setup:
rk = instanceRoot.OpenSubKey(SETUP_KEY_NAME);
foreach (string valueName in SETUP_VALUES)
{
if (rk.GetValue(valueName) != null)
registryValues.Add(valueName, rk.GetValue(valueName).ToString());
}
rk.Close();
rk = null;
}
return registryValues;
}
private static void GetInstanceRootKey(string instanceName, out RegistryKey instanceRoot)
{
object registryValue = null;
string instanceRegistryKey = null;
// Get the registry key based on instance name:
if (instanceName == null)
{
// use the default registry path
instanceRoot = Registry.LocalMachine.OpenSubKey(SOFTWARE_KEY_NAME).OpenSubKey(MICROSOFT_KEY_NAME).OpenSubKey(DEFAULT_SQLSERVER_KEY_NAME);
}
else
{
// use the redirected entries via "Instance Names" key for SQL2005 first:
instanceRoot = Registry.LocalMachine.OpenSubKey(SOFTWARE_KEY_NAME).OpenSubKey(MICROSOFT_KEY_NAME).OpenSubKey(SQLSERVER_KEY_NAME).OpenSubKey(INSTANCES_KEY_NAME).OpenSubKey(PRODUCT_KEY_NAME);
if (instanceRoot != null)
{
registryValue = instanceRoot.GetValue(instanceName);
instanceRoot.Close();
instanceRoot = null;
if (registryValue != null)
{
instanceRegistryKey = registryValue.ToString();
instanceRoot = Registry.LocalMachine.OpenSubKey(SOFTWARE_KEY_NAME).OpenSubKey(MICROSOFT_KEY_NAME).OpenSubKey(SQLSERVER_KEY_NAME).OpenSubKey(instanceRegistryKey);
}
else
{
// use the key based on older versions
instanceRoot = Registry.LocalMachine.OpenSubKey(SOFTWARE_KEY_NAME).OpenSubKey(MICROSOFT_KEY_NAME).OpenSubKey(SQLSERVER_KEY_NAME).OpenSubKey(instanceName);
}
}
}
}
public static void FillRegistryRow(Object obj, out string registryValueName, out string registryValue)
{
DictionaryEntry registryItem = (DictionaryEntry)obj;
registryValueName = registryItem.Key.ToString();
if (registryItem.Value != null)
{
registryValue = registryItem.Value.ToString();
}
else
{
registryValue = null;
}
}
}
}
The code demonstrates several programming techniques including requesting for required registry permissions, how to determine registry locations for SQL Server 2000/2005 instances etc. Note that the code uses the documented registry key paths for SQL Server 2005 and access to values is restricted to a subset of what is exposed in the SMO Settings class. Below are some highlights about the C# code:
1. Use of RegistryPermissionAttribute to specifically request permission to read only the required registry keys
2. The static member REGISTRY_VALUES contains a list of registry values that are of interest. This can be modified to include additional values
3. The default for the instance name argument is set as '' in the TSQL TVF definition below. This will make the code to determine the instance name automatically by making a context connection and checking for the InstanceName property. Pass NULL to get registry settings for default instance
4. The GetInstanceRootKey sub-routine is used to get the root registry path for a specific instance. This sub-routine contains all the logic to determine the redirected instance keys in case of SQL Server 2000/2005 and default instance path
5. The Registry classes of the .NET framework is used to read the values from the SQL Server registry keys
6. Save the C# code to a file called SqlRegSettings.cs and compile using command-line "csc /t:library SqlRegSettings.cs". Copy the DLL to a path which the SQL Server 2005 instance can access it. The TSQL script below assumes that the DLL was copied to C:\Temp directory on the SQL Server.
The script to create a login that maps to the asymmetric key from DLL is shown below. This is required for the EXTERNAL_ACCESS assembly creation to work.
USE master
GO
CREATE ASYMMETRIC KEY SQLCLRSamplesKey FROM EXECUTABLE FILE = 'D:\Projects\SqlRegSettings\CS\bin\Release\SqlRegSettings.CS.dll'
CREATE LOGIN SQLCLRSamplesLogin FROM ASYMMETRIC KEY SQLCLRSamplesKey
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRSamplesLogin
GO
The script to create the assembly in TSQL and test the TVF is shown below:
USE <yourdb>
go
-- Cleanup:
if object_id('SqlRegSettings', 'FT') is not null
drop function SqlRegSettings;
if exists(select * from sys.assemblies where name = 'SqlRegSettings')
drop assembly SqlRegSettings;
go
-- Create assembly:
CREATE ASSEMBLY [SqlRegSettings]
FROM 'D:\Projects\SqlRegSettings\CS\bin\Release\SqlRegSettings.CS.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
go
-- Create TVF:
CREATE FUNCTION [SqlRegSettings](@InstanceName nvarchar(128) = '')
RETURNS TABLE(RegistryValueName nvarchar(128), RegistryValue nvarchar(4000))
AS EXTERNAL NAME [SqlRegSettings].[Microsoft.Samples.SqlServer.SqlRegSettings].[GetSqlRegSettings];
GO
select * from SqlRegSettings(default)
go
select coalesce(case i.InstanceName when '' then '<AUTOMATICALLY DETERMINED INSTANCE>' else i.InstanceName end, '<DEFAULT INSTANCE>') as InstanceName
, r.RegistryValueName, r.RegistryValue
from (
-- shows how to get registry values for various instances:
-- you can run query from any SQL Server 2005 instance on box to get all the values.
select ''
union all
select NULL
union all
select 'YUKON'
union all
select 'SHILOH_EN_CS'
) as i(InstanceName)
cross apply SqlRegSettings(i.InstanceName) as r
order by i.InstanceName, r.RegistryValueName;
go
You can download the entire Visual Studio solution which has both the C# and VB.NET version of the SQL CLR TVF from https://umachandar.members.winisp.net/files/SqlRegSettings.zip. Note that the TSQL TVF definition created from Visual Studio does not contain the default value for the parameter since there is no way to specify this from the IDE. So the TVF created by using the Visual Studio deploy option will error out if you specify the DEFAULT keyword for the parameter. Also, the names of the SQLCLR TVF are suffixed with _CS and _VB when created from the IDE.
Please feel free to comment on the sample or ask any questions.
--
Umachandar Jayachandran