March 14, 2017

Streaming Data Into AWS S3 using KMS Encryption

After a few day of coding, finally I have develop a script component that can upload files into S3 aws buckets, it was a pain to do it this way, if I have a more time, next time it will be done as a Java service instead.
Cheers.





#region Help:  Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion


#region Namespaces
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using Amazon.KeyManagementService;
using Amazon.KeyManagementService.Model;
using Amazon.S3;
using Amazon.S3.Model;
using System.Security.Cryptography;
using System.Threading.Tasks;

using Microsoft.SqlServer.Dts.Runtime;


#endregion

namespace ST_69f7c19bae0d4e359409f7ed3cdecd0f
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
        #region Help:  Using Integration Services variables and parameters in a script
        /* To use a variable in this script, first ensure that the variable has been added to 
         * either the list contained in the ReadOnlyVariables property or the list contained in 
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable.  To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and 
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         * 
         * Example of reading from a variable:
         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         * 
         * Example of writing to a variable:
         *  Dts.Variables["User::myStringVariable"].Value = "new value";
         * 
         * Example of reading from a package parameter:
         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *  
         * Example of reading from a project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         * 
         * Example of reading from a sensitive project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         * */

        #endregion

        #region Help:  Firing Integration Services events from a script
        /* This script task can fire events for logging purposes.
         * 
         * Example of firing an error event:
         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         * 
         * Example of firing an information event:
         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         * 
         * Example of firing a warning event:
         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         * */
        #endregion

        #region Help:  Using Integration Services connection managers in a script
        /* Some types of connection managers can be used in this script task.  See the topic 
         * "Working with Connection Managers Programatically" for details.
         * 
         * Example of using an ADO.Net connection manager:
         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         *  string filePath = (string)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         * */
        #endregion


/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
public void Main()
{
            try
            {
                /*
                  User::objRawData,User::strAmazonKey,User::strAmazonSecretKey,
                 * User::strARN,User::strBucketDirectory,
                 * User::strBucketName,User::strFileName
                 * */
                AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);

                //Variables 
                var tbl = GetJsonRawData(Dts.Variables["User::objRawData"].Value); // Table Object
                int val = (int)Dts.Variables["User::intLoopController"].Value; // File Interactiption
                var FileName = Dts.Variables["User::strFileName"].Value.ToString()+val.ToString()+".txt"; // File Name 
                String BucketDirectory = Dts.Variables["User::strBucketDirectory"].Value.ToString();// Bucket Directory
                

                if (!String.IsNullOrEmpty(BucketDirectory))
                {
                    FileName = BucketDirectory + "/" + FileName;
                }
                
                
                String AmazonKey = Dts.Variables["User::strAmazonKey"].Value.ToString(); 
                String AmazonSecKey = Dts.Variables["User::strAmazonSecretKey"].Value.ToString();
                String ARN = Dts.Variables["User::strARN"].Value.ToString();
                String BucketName = Dts.Variables["User::strBucketName"].Value.ToString();
                
             
                Stream input = GetRawData(tbl);
              
                Encrypt(input, AmazonKey, AmazonSecKey, ARN,FileName,BucketName);
              
                input = null;
               
                Dts.Variables["User::intLoopController"].Value = val + 1;

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
}
       
        private DataTable GetJsonRawData(object DataSetData)
        {
            OleDbDataAdapter oleDb = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            oleDb.Fill(dt, DataSetData);

            return dt;

        }
        private Stream GetRawData(DataTable dt)
        { 
            StringBuilder rawdata = new StringBuilder();
            try
            {
             
                if (dt != null)
                {
                   

                    foreach (DataRow dr in dt.Rows)
                    {
                        foreach (DataColumn dc in dt.Columns)
                        {

                            byte[] json = (byte[])dr[dc];
                            rawdata.Append(Encoding.UTF8.GetString(json));

                        }
                        rawdata.Append(Environment.NewLine);
                    }
                                     
                }
                return new MemoryStream(Encoding.UTF8.GetBytes(rawdata.ToString()));
            }
            catch (Exception e)
            {
                Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
                return new MemoryStream();
            }
           
        }
        /*
         *  //string dk = Convert.ToBase64String(dataKey.CiphertextBlob.ToArray());
                    //algorithm.Key = dataKey.Plaintext.ToArray();
                    //string k = Convert.ToBase64String(dataKey.Plaintext.ToArray());
                    //byte[] Iv = ASCIIEncoding.UTF8.GetBytes(System.Guid.NewGuid().ToString().Substring(0, 12));
                     //string siv = Convert.ToBase64String(algorithm.IV);
                     //byte[] iv = Encoding.UTF8.GetBytes("0123456789123456");
                    //algorithm.IV = iv;
         **/
        private void  Encrypt(Stream input, string AmazonKey,string AmazonSecretKey,string arn,String s3FileName,String s3BucketName)
        {
            try
            {
                string meta_datakey = "x-amz-meta-datakey";
                string meta_iv = "x-amz-meta-iv";
                string meta_encoder = "x-amz-meta-encoder";
                string meta_encrypt = "x-amz-meta-clientside-algorithm";
                string ProxyHost = Dts.Variables["User::strProxyHost"].Value.ToString();
                Int32 ProxyPort =(Int32) Dts.Variables["User::intProxyPort"].Value;
                using (var output = new MemoryStream())
                { 
                    var conf = new AmazonKeyManagementServiceConfig();
                    conf.ProxyHost = ProxyHost;
                    conf.ProxyPort = ProxyPort;
                    conf.RegionEndpoint = Amazon.RegionEndpoint.USEast1;
                    using (var kms = new AmazonKeyManagementServiceClient(AmazonKey, AmazonSecretKey, conf))
                    using (var algorithm = Aes.Create())
                    {
                        var dataKey = kms.GenerateDataKey(new GenerateDataKeyRequest
                        {
                            KeyId = arn,
                            KeySpec = DataKeySpec.AES_256,
                            
                        });
                        
                      
                        using (var cryptoStream = new CryptoStream(
                    output,
                    algorithm.CreateEncryptor(dataKey.Plaintext.ToArray(), algorithm.IV),
                    CryptoStreamMode.Write))
                        {
                            input.CopyTo(cryptoStream);
                            cryptoStream.FlushFinalBlock();
                            var s3conf = new AmazonS3Config();
                            s3conf.ProxyHost = ProxyHost;
                            s3conf.ProxyPort = ProxyPort;
                            s3conf.RegionEndpoint = Amazon.RegionEndpoint.USEast1;
                            using (var s3client = new AmazonS3Client(AmazonKey, AmazonSecretKey, s3conf))
                            {
                                PutObjectRequest request = (new PutObjectRequest()
                                {
                                    BucketName = s3BucketName,
                                    Key = s3FileName,
                                    InputStream = output
                                  });
                                request.Metadata.Add(meta_encrypt, "AES_256");
                                request.Metadata.Add(meta_encoder,"Base 64");
                                request.Metadata.Add(meta_iv,Convert.ToBase64String(algorithm.IV));
                                request.Metadata.Add(meta_datakey,Convert.ToBase64String(dataKey.Plaintext.ToArray()));
                                
                                s3client.PutObject(request);
                            }
                        }
                      

                    }
                 
                }
                 
            }
            catch (Exception e)
            {
                Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
                
            }
        }
        void Decrypt(Stream output, string AmazonKey, string AmazonSecretKey, string arn, String s3FileName, String s3BucketName)
        {
            try
            {
                using (var input = new MemoryStream())
                using (var kms = new AmazonKeyManagementServiceClient(AmazonKey, AmazonSecretKey, Amazon.RegionEndpoint.USEast1))
                using (var algorithm = Aes.Create())
                {
                    var length = input.ReadByte();
                    var buffer = new byte[length];
                    input.Read(buffer, 0, length);
                    var decryptedData = kms.Decrypt(new DecryptRequest
                    {
                        CiphertextBlob = new MemoryStream(buffer)
                    });
                    algorithm.Key = decryptedData.Plaintext.ToArray();
                    var iv = algorithm.IV;
                    input.Read(iv, 0, iv.Length);
                    algorithm.IV = iv;
                    using (var cryptoStream = new CryptoStream(input,
                        algorithm.CreateDecryptor(), CryptoStreamMode.Read))
                    {
                        cryptoStream.CopyTo(output);
                    }

                }
            }
            catch (Exception e)
            {
                Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }
        static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
        {
            
                if (args.Name.Contains("AWSSDK.Core"))
                {
                    string path = @"C:\Program Files (x86)\AWS SDK for .NET\bin\Net45\";
                    return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "AWSSDK.Core.dll"));
                }

                if (args.Name.Contains("AWSSDK.KeyManagementService"))
                {
                    string path = @"C:\Program Files (x86)\AWS SDK for .NET\bin\Net45\";
                    return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "AWSSDK.KeyManagementService.dll"));
                }
                if (args.Name.Contains("AWSSDK.S3"))
                {
                    string path = @"C:\Program Files (x86)\AWS SDK for .NET\bin\Net45\";
                    return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "AWSSDK.S3.dll"));
                }
                return null;
            }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

}
}

No comments:

Post a Comment

Contact Form

Name

Email *

Message *