Wednesday, April 6, 2016

Introduction to "SimpleNet.Data.dll" a Simple Data Access Layer

I found a lot of teams implementing their version of a "Data Access Layer" some allowed the users to interact with Data Tables other versions allowed for using a Mapping Layer to interact with Objects. I personally would rather interact with an array (list) of typed objects. The most difficult part of this is usually finding a simple solution that allows for mapping of DataRow to an Object.

The usual approach using a DataTable

    • Query the database load all data into a DataTable
    • Pass the DataTable to a "IMapper" that returns an array of objects
    • Using an AutoMapper when the columns match the properties in an object.

One of the best and most flexible approaches I found was implemented in "Enterprise Library Data Access", however using Enterprise Library's data access to pass parameters to your query is a Pain.

So a super simple implementation of DAL with an IRepository wrapper.


If you wish to query the database and work with DataTables

var dal = new Simple.Data.Repository.SimpleDataAccess("connectionname");
var dataTable = dal.ReadSql("SELECT * FROM USERS", null);
var dataTable = dal.ReadSql("SELECT... where u.UserId = @UserId",  new []{ dal.GetDbParameter("@UserId", 123});


Working with Objects instead of Data Tables




// 1. Create the Base SQL Repository
public class BaseSqlRepository : AbstractSimpleSqlRepository
{
    public override sealed ISimpleDataAccess Database { get; set; }

    public BaseSqlRepository()
    {
       Database = new SimpleDataAccess("Connection_Name");
    }
}



// Create your concrete implementation of IRepository
public class StateRepository : BaseSqlRepository, IStateRepository
{
         // Please review documentation from Enterprise Libary Db Accessors 
         // to Find out more about IRowMapper

 private static readonly IRowMapper StateMapper = 
   MapBuilder.BuildAllProperties();

         // sample 2
 private static readonly IRowMapper StateRowMapper = 
   MapBuilder.MapAllProperties().Build();

          // sample 3 for mapping
 private static readonly IRowMapper StateRowMapper2 = 
   MapBuilder
                   .MapNoProperties()
                   .MapByName(x => x.Id)
                   .Map(x => x.Code).ToColumn("Code")
                   .Map(x => x.Name).WithFunc(x => x["Name"].ToString())
                   .Build();



        public State GetById(int id)
        {
                const string SQL = @"SELECT Id, Code, Name 
                     FROM STATE s where s.Id = @Id";

                return Read(StateMapper, SQL, CommandType.Text, 
                 new[]
                 {
                     GetDbParameter("@Id", id)
                 }).FirstOrDefault();
        } 
}




// Working with transactions
using (var connection = Database.GetConnection())
{
    using (var transaction = connection.BeginTransaction())
    {
        try{

            Database.ExecuteNonQuery(connection, ..... , transaction);
            ...     
            Database.ExecuteScalar(connection, ..... , transaction);
            ...
            transaction.Commit();

        }catch(){
            transaction.Rollback();
        }
    }
}


Friday, March 18, 2016

How to Setup SSL Local ROOT Authority, Intermediate Authority, and wildcard certificates

This article is for Enterprise users who would like to create SSL certificates for internal domains (.local)

  • For a SSL certificate to be trusted on the browser the Signing Authority has to be "Trusted" explicitly, this will lovingly be referred to as the "Root CA".
  • We will also create an "Intermediate CA" this will be signed by the "Root CA" and will be the signing authority for the Server (websites).
  • We will then create the "Website Certificate", essentially you can issue as many SSL certificates as you want (one for each domain / sub-domain URL)

It is my understanding that the "Root CA" is the only one that needs to be installed and explicitly trusted on the user's computer. The "Intermediate CA" and the "Website Certificate" can be sent from the web server. This might happen auto-magically (should be installed on the web server/proxy server) or both certificates can be packaged as one. I'm not sure :)

Note: The common name (also known as the "CN" in the config files) is important!


Certificate Examples
  1. MyCorp Root CA :- This is the main "trusted" certificate.
  2. MyCorpIntermediate CA:- This is what will sign all our domain certificates.
  3. www.mycorp.local :- This certificate is domain specific
  4. *.mycorp.local :- Wildcard certificate that can be used for any mycorp.local subdomain (mail.mycorp.local, web.mycorp.local, etc...)


My sub directory structure (organization baby)

SSL (contains batch files so I do not type this in a command prompt)
  • certs: The Final SSL certificates
  • config: contains config files as I really do not like answering questions in a command prompt
  • keys: do not loose this baby, guard it with your life... or not.
  • requests: This is what you would traditionally send to a signing authority

Open SSL


  • Just get it!

Step 1: MyCorp Root CA

  1. Create the key oh and password protect it with "- des3".
    • openssl genrsa -des3 -out keys/ipg-root-ca.key 2048
Do not forget the password
Do not loose the key
Do guard it with your life or not!
  1. Create a config file in [config/mycorp-root-ca.conf]
    • [ req ]
    • prompt = no
    • distinguished_name = req_distinguished_name
    • x509_extensions = v3_ca

    • [ req_distinguished_name ]
    • C = US
    • ST = TN
    • L = Nashville
    • O = MYCORP, INC
    • OU = MYCORP RootCA
    • CN = MYCORP-RootCA
    • emailAddress = hem@mycorp.local

    • [ v3_ca ]
    • subjectKeyIdentifier=hash
    • authorityKeyIdentifier=keyid:always,issuer:always
    • #basicConstraints = critical,CA:true
    • basicConstraints = CA:true
  2. Create the self signed root certificate
    • openssl req -new -sha256 -x509 -days 3650 -key keys/mycorp-root-ca.key -out certs/mycorp-root-ca.crt -config config/mycorp-root-ca.conf
  3. You can validate the certificate
    • openssl x509 -noout -in certs/mycorp-root-ca.crt -text -purpose

You will need to install this certificate (" MYCORP-RootCA" ) as "trusted" on your users computers...




Now that we have our root certificate and it is installed on all users computers as a trusted certificate we can now issue certificates that our computers will trust...



Step 2: MyCorp Intermediate CA

We will create an "MyCorp Intermediate-CA" certificate that will exclusively be used to sign other certificates (websites, domains, users, etc...)
  • You may opt to create multiple intermediate certificates (one for each division, example:- Security, Business Intelligence, Web Application, Accounting & Finance)
Don't go crazy, simplicity usually is the best :)

  1. Create a new RSA Key

    • openssl genrsa -des3 -out keys/mycorp-intermediate-ca.key 2048

  2. Generate a request to be signed by the "Root-CA" (enter password as requested)

    • openssl req -new -sha256 -key keys/mycorp-intermediate-ca.key -out requests/mycorp-intermediate-ca.req -config config/mycorp-intermediate-ca.conf

  3. Oh! the config file [ mycorp-intermediate-ca.conf] :)
    • [ req ]
    • prompt = no
    • distinguished_name = req_distinguished_name

    • [ req_distinguished_name ]
    • C = US
    • ST = TN
    • L = Nashville
    • O = MYCORP, INC
    • OU = MYCORP IntermediateCA
    • CN = MYCORP-IntermediateCA
    • emailAddress = hem@mycorp.local

    • [ ssl_server_ca ]
    • subjectKeyIdentifier=hash
    • authorityKeyIdentifier=keyid:always,issuer:always
    • #basicConstraints = critical,CA:true
    • basicConstraints = CA:true
    • keyUsage = keyCertSign

  4. Sign the certificate using the root certificate... (Enter password when requested)
  • openssl x509 -req -sha256 -in requests/mycorp-intermediate-ca.req -out certs/mycorp-intermediate-ca.crt -extfile config/mycorp-root-ca-sign.conf -extensions ssl_server_ca -CA certs/mycorp-root-ca.crt -CAkey keys/mycorp-root-ca.key -CAcreateserial -days 3650
  1. Oh! another config file [ mycorp-root-ca-sign.conf]
  • [ ssl_server_ca ]
  • subjectKeyIdentifier=hash
  • authorityKeyIdentifier=keyid:always,issuer:always
  • #basicConstraints = critical,CA:true
  • basicConstraints = CA:true
  • keyUsage = keyCertSign



Cool Yeah!!!! now for the meat and potatoes baby!!!

Step 3: Let's create the website SSL certificate

The common name (CN) is important, this should match the domain address.

Example:
https://mail.mycorp.local
https://blog.mycorp.local
https://vpn.mycorp.local


You can issue three different SSL certificates for each of these sub-domains or you could issue a wildcard certificate and use that across all three subdomains (*.mycorp.local)


  1. Create the RSA Key
    • openssl genrsa -out keys/_.mycorp.local.key 2048
  2. Create the request (CSR)
    • openssl req -new -sha256 -key keys/_.mycorp.local.key -out requests/_.mycorp.local.req -config config/_.mycorp.local.conf
  3. Sign the request using the intermediate ssl
    • openssl x509 -req -sha256 -in requests/_.mycorp.local.req -out certs/_.mycorp.local.crt -CA certs/mycorp-intermediate-ca.crt -CAkey keys/mycorp-intermediate-ca.key -CAcreateserial -days 3650
  4. Yet! another config file [config/mycorp.local.conf]

    • [ req ]
    • prompt = no
    • distinguished_name = req_distinguished_name

    • [ req_distinguished_name ]
    • C = US
    • ST = TN
    • L = Nashville
    • O = MYCORP, INC
    • OU = MYCORP Web Team
    • CN = *.mycorp.local
    • emailAddress = hem@mycorp.local
Enjoy!!!
-Hem