May 25, 2018 at 7:34 am
Ihope someone can help me out. My C# skills are nill, so I need a lot ofinformation.
A bit of background: I have created a feed that pulls data from a vendor database and houses the massaged result set in a different database on a different server, which feeds a non-SQL financial database. One of the columns I now have to include pulls from an encrypted column on the vendor's database (just the column is encrypted, not the table). As far as I can tell, the column was encrypted with a third partytool outside of SQL Server, not using the symmetric keys tools within thedatabase. Which means I can't (so far as I can tell) decrypt the column using T-SQL code. If I'm wrong on this, please tell me. The encryption is TripleDES and the vendor asked if I could do my decryption if they provided the key. I'm not sure this helps or not.
The alternate solution, previously suggested by my boss and the manager of the dev team, is to use a .dll in a C# code Script Task to decrypt the column. My problem is that I literally know zilch about coding in C# and my time frame for this is urgent. I've been given this snippet of code to start me on my way by another dev who was doing the same thing. The difference is, the dev is generating the data within the script written (and not using SSIS or SQL) and had a whole bunch of other things populating what they called a "list object." I assume that's similar to a SSIS object variable.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Name.Name.BLL;namespace DecryptTheThing
{
class Program
{
static void Main(string[] args)
{ string outputvalue = string.Empty;
outputvalue = MyDecrypt.Decrypt(ListObject.ColumnName);
Console.ReadLine();
}
}
}
I'm not sure if there's a way to update the feed table directly or if I need to pull everything in an ADO recordset source or perhaps an object variable (this one I can do but I'm not sure what to do with the variable once I have it populated), decrypt the column, then create the feed.
I'm kind of lost here, so any links or references pointing me in the right direction would be greatly appreciated. Everyone at work is swamped with the same urgent "mother project," so I would rather not take time from the stuff they're working on if I don't have to. (And, yes, I'm aware of all the security implications here. Everyone knows this decryption is happening and it's been sanctioned for this one specific item.)
Where do I start? Ado recordset source? Object variable? Or C# table update?
May 25, 2018 at 7:54 am
Brandie,
Take a deep breath, this won't be as hard as you think. If you are using SSIS and a data flow task to move the data, you'll just need to add a Script Component transformation to do the decryption and you can use VB.NET or C# whichever you are more comfortable with, if there is one. By using the Script Component Transformation you can just add a decrypted password column to your buffer and use it to load your destination. This article is a pretty good explanation of how to do triple DES in C#. This article is a good start for how to use Script Component Transformation in SSIS.
The hardest part is making sure you implement the decryption EXACTLY like the vendor does so you may need some more communication with them to find out if they has the key, append the key, etc...
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 25, 2018 at 8:02 am
Actually I had just created the Script Task on the Control Flow. Do you think a Data Flow task would be better for this?
I'm a little worried about the RBAR possibility of using the Data Flow, but then again, I might be doing this as a record by record basis no matter what solution I choose.
Thanks for the link. I'll check that now.
May 25, 2018 at 11:58 am
Jack Corbett - Friday, May 25, 2018 7:54 AMThis article is a pretty good explanation of how to do triple DES in C#.
But I did try using a script component, which had the virtual of not showing weird errors like the script task is doing. I added the .dll file in the references section, plus all the USING lines I needed. Component compiled fine, but came up with this error:
Error: 0xC0047062 at Data Flow Task, Decrypt Loan Num [12]: System.IO.FileNotFoundException: Could not load file or assembly 'Name.Name.BLL, Version=1.0.1.0, Culture=neutral, PublicKeyToken=cdc8b0224de147e7' or one of its dependencies. The system cannot find the file specified.
File name: Name.Name.BLL, Version=1.0.1.0, Culture=neutral, PublicKeyToken=cdc8b0224de147e7'
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)
WRN: Assembly binding logging is turned OFF.
EDIT: I suppose the code would help. @=)
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Name.Name.BLL;
#endregion
/// <summary>
/// This is the class to which to add your code. Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
#region Help: Using Integration Services variables and parameters
#endregion
#region Help: Using Integration Services Connnection Managers
#endregion
#region Help: Firing Integration Services Events
#endregion
/// <summary>
/// This method is called once for every row that passes through the component from Input0.
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
string outputvalue = string.Empty;
String MyNum = Row.MyNumber;
outputvalue = Security.Decrypt(LoanNum);
}
}
Any thoughts on this one?
May 30, 2018 at 4:55 am
Bumping the post because I have received no answers for the last question. I've switched back to trying to use the .dll in a Script Task (rather than a Script Component). But now I'm getting a DTS error "The name DTS does not exist in the current context." Any thoughts on this one?
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using My.Global.BLL;
using Microsoft.SqlServer.Dts.Runtime;
namespace NumberDecrypt
{
class Program
{
static void Main(string[] args)
{
string EncryptedNumber;
string DecryptedNumber;
string IdentifyingNumber;
if (Dts.Variables.Contains("IdentifyingNumber") == true && Dts.Variables.Contains("OldNumber") == true)
{
IdentifyingNumber = (string) Dts.Variables["IdentifyingNumber"].Value;
EncryptedNumber = (string) Dts.Variables["OldNumber"].Value;
DecryptedNumber = MySecurity.Decrypt(EncryptedNumber);
Dts.Variables["NewNumber"].Value = DecryptedNumber;
Console.ReadLine();
}
}
}
}
May 30, 2018 at 7:11 am
Sorry Brandie, I didn't get email notifications about your new posts until this morning because I would have replied had I seen your questions.
First, I still think you want a script component in a data flow assuming you are moving many rows. Since this isn't SQL RBAR is required no matter how you do it. In your first attempt using the script component, Name.Name.BLL is a custom DLL that you are trying to use and it needs to be in the GAC (Global Assembly Cache) in order to be used by SSIS and that means on all development PC's and any SSIS servers the package will be run on or you'll get the error you are getting. Unless that is the component being used by the vendor for encryption and decryption I think you'll likely be better off just writing the code in the script task instead of using a DLL. If you have to use the DLL this article may help getting it deployed to the GAC. Or, I just found that you can do this so you don't need the GAC. I would just reference System.Security.Cryptography directly in the script component and your code will look something like this.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Security.Cryptography;
#endregion
/// <summary>
/// This is the class to which to add your code. Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
#region Help: Using Integration Services variables and parameters
#endregion
#region Help: Using Integration Services Connnection Managers
#endregion
#region Help: Firing Integration Services Events
#endregion
/// <summary>
/// This method is called once for every row that passes through the component from Input0.
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
string outputvalue = string.Empty;
string MyNum = Row.MyNumber;
string key = Row.Key // or Dts.Variables["Key"].ToString()
byte[] b = Convert.FromBase64String(MyNum);
TripleDES des = CreateDES(key);
ICryptoTransform ct = des.CreateDecryptor();
byte[] output = ct.TransformFinalBlock(b, 0, b.Length);
outputvalue Encoding.Unicode.GetString(output);
Row.DecryptedValue = outputvalue; // this is a column that you add to the buffer in the Script Component by going to Inputs and Outpus tab of teh SCript Transformation Editor and add an output and add this column
}
}
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 30, 2018 at 7:18 am
Thanks, Jack. I'll look over the links.
Regarding the .dll, someone else built it for this purpose. I'm not sure if it was the vendor or one of our developers. I don't currently have the key that was used to encrypt the columns, which makes other options difficult, but am working on that if I can get the vendor to respond to me.
May 30, 2018 at 7:26 am
<HEADDESK>
I already added the Reference. But I forgot to add the stupid "using" statement. Sometimes it's the simplest things we miss.
Thanks, Jack. Now I can finally get on with testing the stupid thing.
May 31, 2018 at 4:38 am
Okay, that script task didn't work. Back to trying the script component. Will keep you updated on what finally works.
May 31, 2018 at 8:07 am
Brandie Tarvin - Thursday, May 31, 2018 4:38 AMOkay, that script task didn't work. Back to trying the script component. Will keep you updated on what finally works.
Surely you've been around here long enough to know we're going to want to know "why" it didn't work, and exactly what constituted failure...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 6, 2018 at 5:16 am
sgmunson - Thursday, May 31, 2018 8:07 AMBrandie Tarvin - Thursday, May 31, 2018 4:38 AMOkay, that script task didn't work. Back to trying the script component. Will keep you updated on what finally works.Surely you've been around here long enough to know we're going to want to know "why" it didn't work, and exactly what constituted failure...
Very very generic runtime errors. Like literally no information.
And the script component failed with the same issue. So I'm back to the drawing board trying to figure out what the heck is going on. I've been trying to call the .dll and it keeps failing. The vendor doesn't have an encryption key (so they say), which makes me wonder if TripleDES uses keys like other encryptions do. So now I'm going to try and use the code Jack linked to if I can figure it out. The problem is, I think that code needs me to enter a key.
I can't create one from scratch for data that's already encrypted. At least, I don't think this works that way...
June 6, 2018 at 10:11 am
<headdesk>
Found out what the problem is.
My PC is hosed. Something (we're not sure what) won't let Visual Studio see the .dll in the Windows\System32 folder even though we see it in Windows Explorer. Another something (or maybe the same something) won't let me use the .dll at all, even referenced in the once place that VS can see.
My code / package works fine on someone else's machine. Just not on mine. Talk about frustration! 🙁
June 6, 2018 at 10:26 am
Brandie Tarvin - Wednesday, June 6, 2018 10:11 AM<headdesk>Found out what the problem is.
My PC is hosed. Something (we're not sure what) won't let Visual Studio see the .dll in the Windows\System32 folder even though we see it in Windows Explorer. Another something (or maybe the same something) won't let me use the .dll at all, even referenced in the once place that VS can see.
My code / package works fine on someone else's machine. Just not on mine. Talk about frustration! 🙁
That sounds very annoying. Your XML is incorrectly formed, by the way ... needs </headesk> 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 6, 2018 at 10:30 am
Phil Parkin - Wednesday, June 6, 2018 10:26 AMYour XML is incorrectly formed, by the way ... needs </headesk> 🙂
@=pbbbbbt.
😛
June 8, 2018 at 7:55 am
Brandie Tarvin - Wednesday, June 6, 2018 10:11 AM<headdesk>Found out what the problem is.
My PC is hosed. Something (we're not sure what) won't let Visual Studio see the .dll in the Windows\System32 folder even though we see it in Windows Explorer. Another something (or maybe the same something) won't let me use the .dll at all, even referenced in the once place that VS can see.
My code / package works fine on someone else's machine. Just not on mine. Talk about frustration! 🙁
Just wondering if the .dll file in question is registered in your local GAC ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply