Hello people,
Good afternoon!
In today's post, I will demonstrate how to manage (read, list, create, change, and delete) Windows registry keys through the database using SQL CLR and the C # programming language (CSharp), which allows us to greatly extend the capabilities and functionality of the SQL Server database.
This kind of functionality is very useful when you need to quickly look up some information in a server's Windows registry or make a registry change without connecting to the server, just performing a Stored Procedure, or even applying a registry change to multiple machines. or servers in an automated manner.
Remember that I had already made a post (SQL Server - Querying instance information in Windows Registry using sys.dm_server_registry and xp_instance_regread) where it was possible to read some information from the Windows registry without using the CLR, but relating only to SQL Server instance information and not the general reading of any registry key let alone manipulating that information, which motivated me to create this new post for this need.
Some Stored Procedures below require the use of the Return class, which I use to send CLR alert and / or error messages to the database. The source code of this class can be viewed in the post. SQL Server - How to send warnings and error messages to the bank through CLR (C #)
How to read and list Windows registry keysHow to read and list Windows registry keys
To list the Windows records, I will use the RegistryRow class, which encapsulates various methods of the Microsoft.Win32.RegistryKey library and I use it in the table-valued fncRegEdit_Listar function, as in the example above, where I enter as parameters the machine name and key which will be read.
RegEdit.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 |
using System; using System.Collections; using Microsoft.Win32; namespace Bibliotecas.Model { public class RegistryRow { public string Tipo; public string Chave; public string Valor; public RegistryRow(string tipo, string chave, string valor) { Tipo = tipo; Chave = chave; Valor = valor; } public static ArrayList RegEdit_Listar(string Ds_Servidor, string Ds_Chave) { var RegistryRowCollection = new ArrayList(); if (string.IsNullOrEmpty(Ds_Servidor)) Ds_Servidor = Environment.MachineName; var registryHive = RegistryHive.LocalMachine; if (Ds_Chave.Substring(0, 5).ToUpper() == "HKEY_") { var palavras = Ds_Chave.Split('\\'); var raiz = palavras[0]; switch (raiz) { case "HKEY_CURRENT_CONFIG": registryHive = RegistryHive.CurrentConfig; break; case "HKEY_CURRENT_USER": registryHive = RegistryHive.CurrentUser; break; case "HKEY_USERS": registryHive = RegistryHive.Users; break; case "HKEY_CLASSES_ROOT": registryHive = RegistryHive.ClassesRoot; break; case "HKEY_LOCAL_MACHINE": default: registryHive = RegistryHive.LocalMachine; break; } Ds_Chave = string.Join("\\", palavras, 1, palavras.Length - 1); } var myRegChave = RegistryKey.OpenRemoteBaseKey(registryHive, Ds_Servidor).OpenSubKey(Ds_Chave); var subKeys = myRegChave?.GetSubKeyNames(); if (subKeys != null) { foreach (var key in subKeys) { try { RegistryRowCollection.Add(new RegistryRow( "Chave", key, "" )); } catch (Exception e) { // ignored } } } myRegChave = RegistryKey.OpenRemoteBaseKey(registryHive, Ds_Servidor).OpenSubKey(Ds_Chave); var valorNames = myRegChave?.GetValueNames(); if (valorNames == null) return RegistryRowCollection; foreach (var chave in valorNames) { try { var valor = myRegChave.GetValue(chave).ToString(); RegistryRowCollection.Add(new RegistryRow( "Valor", chave, valor )); } catch (Exception e) { // ignored } } return RegistryRowCollection; } } } |
fncRegEdit_Listar.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
using System.Collections; using System.Data.SqlTypes; using Bibliotecas.Model; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction( FillRowMethodName = "FillRegistryRow", TableDefinition = "Ds_Tipo nvarchar(50), Ds_Chave nvarchar(max), Ds_Valor nvarchar(max)" )] public static IEnumerable fncRegEdit_Listar(string Ds_Servidor, string Ds_Chave) { var RegistryRowCollection = RegistryRow.RegEdit_Listar(Ds_Servidor, Ds_Chave); return RegistryRowCollection; } protected static void FillRegistryRow(object objRegistryRow, out SqlString tipo, out SqlString key, out SqlString value) { var registryRow = (RegistryRow) objRegistryRow; tipo = registryRow.Tipo; key = registryRow.Chave; value = registryRow.Valor; } } |
How to Create Windows Registry Folders
To create folders in Windows registries, I use the stpRegEdit_Pasta_Criar Stored Procedure, as in the example above, which allows you to create directories under a Windows registry key.
stpRegEdit_Pasta_Criar.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
using System; using System.Data.SqlTypes; using Bibliotecas.Model; using Microsoft.Win32; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpRegEdit_Pasta_Criar(SqlString Ds_Servidor, SqlString Ds_Caminho, SqlString Ds_Chave) { try { if (Ds_Servidor.IsNull) Retorno.Erro("Favor informar o servidor"); if (Ds_Caminho.IsNull) Retorno.Erro("Favor informar o caminho"); if (Ds_Chave.IsNull) Retorno.Erro("Favor informar a chave"); var caminho = Ds_Caminho.Value; var registryHive = RegistryHive.LocalMachine; if (caminho.Substring(0, 5).ToUpper() == "HKEY_") { var palavras = caminho.Split('\\'); var raiz = palavras[0]; switch (raiz) { case "HKEY_CURRENT_CONFIG": registryHive = RegistryHive.CurrentConfig; break; case "HKEY_CURRENT_USER": registryHive = RegistryHive.CurrentUser; break; case "HKEY_USERS": registryHive = RegistryHive.Users; break; case "HKEY_CLASSES_ROOT": registryHive = RegistryHive.ClassesRoot; break; case "HKEY_LOCAL_MACHINE": default: registryHive = RegistryHive.LocalMachine; break; } caminho = string.Join("\\", palavras, 1, palavras.Length - 1); } var registro = RegistryKey.OpenRemoteBaseKey(registryHive, Ds_Servidor.Value).OpenSubKey(caminho, true); registro?.CreateSubKey(Ds_Chave.Value); registro?.Close(); } catch (Exception e) { Retorno.Erro("Erro : " + e.Message); } } } |
How To Delete Windows Registry Folders
To delete folders in the windows registry, I use the stpRegEdit_Pasta_Apagar Stored Procedure, as in the example above, which allows you to delete directories from the windows registry. The @Fl_Recursive = 1 binary flag allows you to recursively delete keys and sub directories from a given directory.
If the @Fl_Recursivo = 0 flag, it will only be possible to delete directories that do not have no sub-directories and if you try, you will encounter the error message “System.ApplicationException: Error: Registry key has subkeys and recursive removes are not supported by this method. ”.
stpRegEdit_Pasta_Apagar.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
using System; using System.Data.SqlTypes; using Bibliotecas.Model; using Microsoft.Win32; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpRegEdit_Pasta_Criar(SqlString Ds_Servidor, SqlString Ds_Caminho, SqlString Ds_Chave) { try { if (Ds_Servidor.IsNull) Retorno.Erro("Favor informar o servidor"); if (Ds_Caminho.IsNull) Retorno.Erro("Favor informar o caminho"); if (Ds_Chave.IsNull) Retorno.Erro("Favor informar a chave"); var caminho = Ds_Caminho.Value; var registryHive = RegistryHive.LocalMachine; if (caminho.Substring(0, 5).ToUpper() == "HKEY_") { var palavras = caminho.Split('\\'); var raiz = palavras[0]; switch (raiz) { case "HKEY_CURRENT_CONFIG": registryHive = RegistryHive.CurrentConfig; break; case "HKEY_CURRENT_USER": registryHive = RegistryHive.CurrentUser; break; case "HKEY_USERS": registryHive = RegistryHive.Users; break; case "HKEY_CLASSES_ROOT": registryHive = RegistryHive.ClassesRoot; break; case "HKEY_LOCAL_MACHINE": default: registryHive = RegistryHive.LocalMachine; break; } caminho = string.Join("\\", palavras, 1, palavras.Length - 1); } var registro = RegistryKey.OpenRemoteBaseKey(registryHive, Ds_Servidor.Value).OpenSubKey(caminho, true); registro?.CreateSubKey(Ds_Chave.Value); registro?.Close(); } catch (Exception e) { Retorno.Erro("Erro : " + e.Message); } } } |
How to create / change Windows registry keys
How to create keys and values in the Windows registry:
How to change keys and values in the Windows registry:
To create / change Windows registries, I use the stpRegEdit_Chave_Criar Stored Procedure as exemplified above and the source code is available below:
stpRegEdit_Chave_Criar.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
using System; using System.Data.SqlTypes; using Bibliotecas.Model; using Microsoft.Win32; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpRegEdit_Chave_Criar(SqlString Ds_Servidor, SqlString Ds_Caminho, SqlString Ds_Chave, SqlString Ds_Valor) { try { if (Ds_Servidor.IsNull) Retorno.Erro("Favor informar o servidor"); if (Ds_Caminho.IsNull) Retorno.Erro("Favor informar o caminho"); if (Ds_Chave.IsNull) Retorno.Erro("Favor informar a chave"); if (Ds_Valor.IsNull) Retorno.Erro("Favor informar o valor"); var caminho = Ds_Caminho.Value; var registryHive = RegistryHive.LocalMachine; if (caminho.Substring(0, 5).ToUpper() == "HKEY_") { var palavras = caminho.Split('\\'); var raiz = palavras[0]; switch (raiz) { case "HKEY_CURRENT_CONFIG": registryHive = RegistryHive.CurrentConfig; break; case "HKEY_CURRENT_USER": registryHive = RegistryHive.CurrentUser; break; case "HKEY_USERS": registryHive = RegistryHive.Users; break; case "HKEY_CLASSES_ROOT": registryHive = RegistryHive.ClassesRoot; break; case "HKEY_LOCAL_MACHINE": default: registryHive = RegistryHive.LocalMachine; break; } caminho = string.Join("\\", palavras, 1, palavras.Length - 1); } var registro = RegistryKey.OpenRemoteBaseKey(registryHive, Ds_Servidor.Value).OpenSubKey(caminho, true); registro?.SetValue(Ds_Chave.Value, Ds_Valor.Value); registro?.Close(); } catch (Exception e) { Retorno.Erro("Erro : " + e.Message); } } } |
How To Delete Windows Registry Keys
To delete Windows registry keys, I use the Stored Procedure stpRegEdit_Chave_Apagar, in the same patterns as the Stored Procedures above.
stpRegEdit_Chave_Apagar.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
using System; using System.Data.SqlTypes; using Bibliotecas.Model; using Microsoft.Win32; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpRegEdit_Chave_Apagar(SqlString Ds_Servidor, SqlString Ds_Caminho, SqlString Ds_Chave) { try { if (Ds_Servidor.IsNull) Retorno.Erro("Favor informar o servidor"); if (Ds_Caminho.IsNull) Retorno.Erro("Favor informar o caminho"); if (Ds_Chave.IsNull) Retorno.Erro("Favor informar a chave"); var caminho = Ds_Caminho.Value; var registryHive = RegistryHive.LocalMachine; if (caminho.Substring(0, 5).ToUpper() == "HKEY_") { var palavras = caminho.Split('\\'); var raiz = palavras[0]; switch (raiz) { case "HKEY_CURRENT_CONFIG": registryHive = RegistryHive.CurrentConfig; break; case "HKEY_CURRENT_USER": registryHive = RegistryHive.CurrentUser; break; case "HKEY_USERS": registryHive = RegistryHive.Users; break; case "HKEY_CLASSES_ROOT": registryHive = RegistryHive.ClassesRoot; break; case "HKEY_LOCAL_MACHINE": default: registryHive = RegistryHive.LocalMachine; break; } caminho = string.Join("\\", palavras, 1, palavras.Length - 1); } var registro = RegistryKey.OpenRemoteBaseKey(registryHive, Ds_Servidor.Value).OpenSubKey(caminho, true); registro?.DeleteValue(Ds_Chave.Value); registro?.Close(); } catch (Exception e) { Retorno.Erro("Erro : " + e.Message); } } } |
If you didn't know what CLR is and would like to know more about it, visit my post. Introduction to SQL Common Language Runtime (CLR) in SQL Server.
That's it folks!
Until the next post.
sql server C # csharp access access list list view view read windows registry registry regedit
sql server C # csharp access access list list view view read windows registry registry regedit
