1 files changed, 274 insertions, 0 deletions
diff --git a/utils/ipn_sqlclr/Taggant.cs b/utils/ipn_sqlclr/Taggant.cs
new file mode 100644
index 0000000..c989252
--- /dev/null
+++ b/utils/ipn_sqlclr/Taggant.cs
@@ -0,0 +1,274 @@
+using System;
+using System.Collections.Generic;
+using System.Data;
+using System.Data.SqlClient;
+using System.Data.SqlTypes;
+using System.IO;
+using System.Security.Cryptography.X509Certificates;
+using System.Xml;
+using Microsoft.SqlServer.Server;
+using Org.BouncyCastle.Crypto;
+using Org.BouncyCastle.Crypto.Generators;
+using Org.BouncyCastle.OpenSsl;
+using Org.BouncyCastle.Security;
+using System.Linq;
+
+namespace ipn_sqlclr
+{
+	public class TaggantConfig : Dictionary<string, string>
+	{
+		public X509Certificate ClientCertificate { get; set; }
+	}
+	public partial class UserDefinedFunctions
+	{
+		public static X509Certificate LocateCertificate(string subjectName)
+		{
+			var certStore = new X509Store(StoreName.My, StoreLocation.LocalMachine);
+			certStore.Open(OpenFlags.ReadOnly);
+			X509Certificate2Collection certCollection = certStore.Certificates.Find(X509FindType.FindBySubjectName, subjectName, true);
+			certStore.Close();
+
+			if (0 == certCollection.Count)
+			{
+				throw new ArgumentException(string.Format("No valid client certificate found at LocalMachine.My by SubjectName '{0}'", subjectName), "subjectName");
+			}
+			if (1 == certCollection.Count)
+			{
+				return certCollection[0];
+			}
+			throw new ArgumentException(string.Format("More than one client certificate found at LocalMachine.My by SubjectName '{0}'", subjectName), "subjectName");
+
+		}
+		public static TaggantConfig GetTaggantConfig(SqlInt32 taggantConfigId)
+		{
+			var config = new TaggantConfig();
+			using (var conn = new SqlConnection("context connection=true"))
+			{
+				conn.Open();
+				var readConfigCmd = conn.CreateCommand();
+				readConfigCmd.Parameters.Add(new SqlParameter("@taggantConfigId", taggantConfigId.Value));
+				readConfigCmd.CommandText =
+					"SELECT Name, Value FROM dbo.TaggantConfig WHERE ID=@taggantConfigId";
+				using (var reader = readConfigCmd.ExecuteReader())
+				{
+					while(reader.Read())
+					{
+						config[reader.GetString(0)] = reader[1] as string;
+					}
+				}
+			}
+			config.ClientCertificate = LocateCertificate(config["ClientCertificate"]);
+			return config;
+		}
+
+		[SqlFunction]
+		public static SqlString TaggantPrivateKeyGenerateNew()
+		{
+			var g = new RsaKeyPairGenerator();
+			g.Init(new KeyGenerationParameters(new SecureRandom(), 2048));
+			var pair = g.GenerateKeyPair();
+
+			using (var sw = new StringWriter())
+			{
+				new PemWriter(sw).WriteObject(pair);
+				sw.Flush();
+				return new SqlString(sw.ToString());
+			}
+		}
+
+		[SqlProcedure]
+		public static int TaggantCertRevoke(SqlInt32 taggantConfigId, SqlInt32 customerId)
+		{
+			var tc = GetTaggantConfig(taggantConfigId);
+			var log = new List<LogItem>();
+			try
+			{
+				string mail;
+				using (var conn = new SqlConnection("context connection=true"))
+				{
+					conn.Open();
+					var readCustomerCmd = conn.CreateCommand();
+					readCustomerCmd.Parameters.Add(new SqlParameter("@CustomerID", customerId.Value));
+					readCustomerCmd.CommandText =
+						"SELECT EMail FROM dbo.Customer WHERE ID=@CustomerID";
+					using (var reader = readCustomerCmd.ExecuteReader())
+					{
+						if (reader.Read())
+						{
+							mail = reader[0] as string;
+						}
+						else
+						{
+							throw new ArgumentException("Customer not found", "customerId");
+						}
+						if (string.IsNullOrWhiteSpace(mail))
+							throw new InvalidOperationException("Customer EMail is not set");
+					}
+				}
+				log.Add(new LogItem { MsgId = 1033, P = new[] { customerId.ToString(), tc["CertificateProfileOid"] } });
+
+				TaggantWebService.CertRevoke(tc, mail, log);
+				return 0;
+			}
+			catch (Exception ex)
+			{
+				log.Add(new LogItem { MsgId = 15, P = new[] { ex.Source, ex.Message, ex.StackTrace } });
+				throw;
+			}
+			finally
+			{
+				FlushLog("TaggantCertRevoke", new SqlInt32(2), customerId, log);
+			}
+		}
+
+		[SqlProcedure]
+		public static int TaggantCertEnsure(SqlInt32 taggantConfigId, SqlInt32 customerId)
+		{
+			var tc = GetTaggantConfig(taggantConfigId);
+			var log = new List<LogItem>();
+			var id = "ipn" + customerId.Value;
+			string taggantCert = null;
+			try
+			{
+				string mail;
+				string privateKey;
+				using (var conn = new SqlConnection("context connection=true"))
+				{
+					conn.Open();
+					var readCustomerCmd = conn.CreateCommand();
+					readCustomerCmd.Parameters.Add(new SqlParameter("@CustomerID", customerId.Value));
+					readCustomerCmd.CommandText =
+						"SELECT EMail, PrivateKeyCert, TaggantCert FROM dbo.Customer WHERE ID=@CustomerID";
+					using (var reader = readCustomerCmd.ExecuteReader())
+					{
+						if (reader.Read())
+						{
+							mail = reader[0] as string;
+							privateKey = reader[1] as string;
+							taggantCert = reader[2] as string;
+						}
+						else
+						{
+							throw new ArgumentException("Customer not found", "customerId");
+						}
+						if (string.IsNullOrWhiteSpace(mail))
+							throw new InvalidOperationException("Customer EMail is not set");
+						if (string.IsNullOrWhiteSpace(privateKey))
+							throw new InvalidOperationException("Customer PrivateKeyCert is not set");
+						if (!string.IsNullOrWhiteSpace(taggantCert))
+							return 0; // ensured
+					}
+				}
+				log.Add(new LogItem { MsgId = 14, P = new[] { customerId.ToString(), mail, tc["CertificateProfileOid"] } });
+
+				taggantCert = TaggantWebService.CertRequestNew(tc, id, mail, privateKey, log);
+				using (var conn = new SqlConnection("context connection=true"))
+				{
+					conn.Open();
+					var writeCustomerCmd = conn.CreateCommand();
+					writeCustomerCmd.Parameters.Add(new SqlParameter("@CustomerID", customerId.Value));
+					writeCustomerCmd.Parameters.Add(new SqlParameter("@TaggantCert", taggantCert));
+					writeCustomerCmd.CommandText =
+						"UPDATE dbo.Customer SET TaggantCert=@TaggantCert WHERE ID=@CustomerID";
+					writeCustomerCmd.ExecuteNonQuery();
+				}
+				return 1; // created new
+			}
+			catch (Exception ex)
+			{
+				log.Add(new LogItem { MsgId = 15, P = new[] { ex.Source, ex.Message, ex.StackTrace, taggantCert } });
+				throw;
+			}
+			finally
+			{
+				FlushLog("TaggantCertEnsure", new SqlInt32(2), customerId, log);
+			}
+		}
+
+		private static void FlushLog(string src, SqlInt32 refKindId, SqlInt32 refId, IEnumerable<LogItem> log)
+		{
+			using (var conn = new SqlConnection("context connection=true"))
+			{
+				conn.Open();
+				foreach (var li in log)
+				{
+					var insLogCmd = conn.CreateCommand();
+					insLogCmd.Parameters.Add(new SqlParameter("@RefKindID", refKindId));
+					insLogCmd.Parameters.Add(new SqlParameter("@RefID", refId));
+					insLogCmd.Parameters.Add(new SqlParameter("@MsgID", li.MsgId));
+					int i;
+					for (i = 0; i < 2; i++)
+					{
+						insLogCmd.Parameters.Add(
+							new SqlParameter(string.Format("@xml{0}", i), SqlDbType.Xml)
+							{
+								Value = (li.Xml == null || li.Xml[i] == null)
+									? DBNull.Value
+									: (object)new SqlXml(new XmlTextReader(li.Xml[i].InnerXml, XmlNodeType.Document, null))
+							});
+					}
+					insLogCmd.Parameters.Add(new SqlParameter("@P0", src));
+					i = 1;
+					foreach (var p in li.P)
+					{
+						insLogCmd.Parameters.Add(new SqlParameter(string.Format("@P{0}", i++), p));
+					}
+					for (; i <= 8; i++)
+					{
+						insLogCmd.Parameters.Add(new SqlParameter(string.Format("@P{0}", i), DBNull.Value));
+					}
+
+					insLogCmd.CommandText =
+						"INSERT dbo.Log(RefID, RefKindID, MsgID, xml, xml2, P0, P1, P2, P3, P4, P5, P6, P7, P8)" +
+						" VALUES(@RefID, @RefKindID, @MsgID, @xml0, @xml1, @P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)";
+
+					insLogCmd.ExecuteNonQuery();
+				}
+			}
+		}
+
+		[SqlProcedure]
+		public static SqlInt32 TaggantGetPolicies(SqlInt32 taggantConfigId)
+		{
+			var tc = GetTaggantConfig(taggantConfigId);
+			var log = new List<LogItem>();
+			try
+			{
+				var meta = new[]
+				{
+					new SqlMetaData("defaultName", SqlDbType.NVarChar, -1),
+					new SqlMetaData("groupId", SqlDbType.Int),
+					new SqlMetaData("oIdReferenceId", SqlDbType.Int),
+					new SqlMetaData("certificateProfileId", SqlDbType.NVarChar, -1)
+				};
+				SqlDataRecord[] records = TaggantWebService.GetPolicies(tc, log).Select(x =>
+				{
+					var r = new SqlDataRecord(meta);
+					r.SetSqlString(0, x.defaultName);
+					r.SetSqlInt32(1, (int)x.group);
+					r.SetSqlInt32(2, x.oIDReferenceID);
+					r.SetSqlString(3, x.value);
+					return r;
+				}).ToArray();
+
+				if (SqlContext.Pipe != null)
+				{
+					SqlContext.Pipe.SendResultsStart(new SqlDataRecord(meta));
+					foreach (var r in records)
+						SqlContext.Pipe.SendResultsRow(r);
+					SqlContext.Pipe.SendResultsEnd();
+				}
+				return records.Length;
+			}
+			catch (Exception ex)
+			{
+				log.Add(new LogItem { MsgId = 15, P = new[] { ex.Source, ex.Message, ex.StackTrace } });
+				throw;
+			}
+			finally
+			{
+				FlushLog("TaggantGetPolicies", new SqlInt32(), new SqlInt32(), log);
+			}
+		}
+	}
+}