Azure Synapse and Dynamics 365 CDM Data – An Overview

Microsoft is leveraging Azure to enable extensibility across Dynamic 365 and Power apps by making data easily accessible to clients via the export to Azure Data Lake feature. Before that could happen, Microsoft needed to collaborate with other industry heavyweights to create a data format standard named Common Data Model (CDM) which is the format used to save data in the Data Lake.

In this article, we are going to focus on how to read and write data saved in the CDM format which is a hierarchy of folders that store the data file in CSV and the schema in JSON format. This enables any compute type to access this data if it understands how to query the data in CSV while fetching its schema in the model.json or the newer manifest.cdm.json files.

To begin this process, we need to export our data to the Datalake from either Azure Dataverse or Dynamics 365 Finance and Operations (F&O). The reason other Dynamics 365 and Power apps are not called out is because they leverage Dataverse as their data backend, while F&O leverages Azure SQL database in the backend. For folks struggling to grasp what Dataverse is, it is the new branding of the Common Data Service (CDS), and it can be considered Database as a service. Apps and Users interface with Dataverse through a set of API calls. The components of the product itself are a combination of Azure services such as Azure SQL Database, Azure Cosmos, Azure Blob, Azure Search, Azure Functions, Azure Service hub, Azure events hub, and additional components such as app service and load balancer etc.. The services are orchestrated and managed by Microsoft which hides the complexity under the hood. Users, however, like to have access to their data to do further analytics and reporting, which is enabled by the export to data lake option that benefits all Dynamics 365 and power apps that leverage Azure Dataverse as their data backend.

For the ERP system, F&O’s export to data lake is a work in progress. It is currently being tested with a select set of customers in private preview, after that it should be open to more testers in public preview before it becomes Generally Available. We will get in front of the public preview event to prepare ourselves with knowledge and tools necessary to analyze that data once it lands in our Data lake. The same learning can be applied to CDM data synced to Data lake from Dataverse which is generally available, or even Power BI Data flows. If you need CDM data to experiment, you can use the SQL to lake solution to export SQL data to a Datalake in CDM format, and you can leverage this limited time offer for Azure Synapse Analytics to save on cost. Once the data lands in the Data Lake in CDM format, it can be acted upon by different compute types such as Power BI Data Flows, Azure Databricks, or Azure Synapse Analytics. We will focus on the latter, since it offers the capability of attaching a Data lake to a workspace, and also offers two types of compute to read the data, transform it, and use it for ML model training among several other use cases.

It’s time to share some code snippets that I gathered from different docs and modified as needed. We will start with reading CDM data from an attached Data Lake with Apache Spark using a Synapse notebook. In Synapse Develop hub, click the plus sign to create a new notebook, and copy and paste each code snippet to a cell.

# Specifying appid, appkey and tenanid is optional in spark-cdm-connector-assembly-0.16.jar with Premium Databricks Cluster and Synapse
appid = "<appId&gt;"
appkey = "<appKey&gt;"
tenantid = "<tenantId&gt;"

storageAccountName = "storageAccount.dfs.core.windows.net"
# Reading the content of the entity SalesCube_Customers into the readDF dataframe
readDf = (spark.read.format("com.microsoft.cdm")
  .option("storage", storageAccountName)
  .option("manifestPath","/dynamics365-financeandoperations/lm-d365-xxxxxxxxxxxx.cloudax.dynamics.com/AggregateMeasurements/SalesCube/model.json")
  .option("entity", "SalesCube_Customers")
  .load())

readDf.select("*").show()
# Create a Spark SQL Database and write the contents of our Dataframe to the Customers table.
#Please note that this database and its content can be read with the built-in SQL pool compute.
spark.sql("CREATE DATABASE IF NOT EXISTS financeopdb")
 
readDf.write.mode("overwrite").saveAsTable("financeopdb.Customers")
#Changing the context from pyspark to SQL and reading the content of the newly created table
%%sql
select * from financeopdb.Customers

That was pretty straight forward, and if you need to make transformations to data before you write it back, you will need to add appropriate code to accomplish that. In this example, we wrote data to a Spark SQL database that can be accessed by the built-in SQL compute or even Power BI. If you need to write the dataframe to storage in CDM format, then follow these instructions.

To read CDM data with the built-in SQL compute (On Demand), we will use these scripts from Vesa Tikkanen github repo that were written for reading Power BI Data Flow CDM folders, but can be repurposed for any CDM data produced by Azure Dataverse or Dynamics 365 F&O. I have done some slight modifications to the scripts to account for some differences in the folders hierarchy, and also changed a datatype that Synapse was complaining about. Below, I will paste the three scripts equivalent to the three steps right after creating a schema.

Step 1: Create a view with information about entities and their model.json
IF EXISTS(select 1 from sys.views as v INNER JOIN sys.schemas as s ON v.schema_id=s.schema_id where v.name='MyDynamixModels' and s.name='dynamixfo')
BEGIN
    DROP VIEW [dynamixfo].[MyDynamixModels]; 
END

GO

CREATE VIEW [dynamixfo].[MyDynamixModels] AS
SELECT 

    /*
        Originally Power BI Dataflow reader for Synapse.	
        
        Reader created by Vesa Tikkanen
    */

     [result].filepath(1) AS [rootfolderName]
    ,[result].filepath(2) AS [EntityOrCubeName]
    ,JSON_VALUE(jsoncontent, '$.name') as EntityCubeName
    ,convert(datetime2,JSON_VALUE(jsoncontent, '$.modifiedTime')) as modifiedTime 
    ,JSON_VALUE(entity.Value, '$.name') as entityName
    ,JSON_VALUE(partitions.Value, '$.location') as FileName
    ,JSON_VALUE(attributes.Value, '$.name') as ColumnName
    ,JSON_VALUE(attributes.Value, '$.dataType') as ColumnDataType
    ,
    CASE WHEN JSON_VALUE(attributes.Value, '$.dataType')='date' then 'date'
     WHEN JSON_VALUE(attributes.Value, '$.dataType')='dateTime' then 'datetime2'
     WHEN JSON_VALUE(attributes.Value, '$.dataType')='int64' then 'bigint'
     WHEN JSON_VALUE(attributes.Value, '$.dataType')='string' then 'nvarchar(4000)'        
     WHEN JSON_VALUE(attributes.Value, '$.dataType')='decimal' then 'decimal(18,6)'
     WHEN JSON_VALUE(attributes.Value, '$.dataType')='boolean' then 'bit'
     WHEN JSON_VALUE(attributes.Value, '$.dataType')='double' then 'float'
    ELSE JSON_VALUE(attributes.Value, '$.dataType') END as ColumnDataTypeSQL
    
    ,[result].filepath() as modelFileName
FROM
    OPENROWSET(
        -- HERE CHANGE YOUR Azure Datalake Gen2 account that you're using for your DataFlow's
        BULK 'https://storageAccounr.dfs.core.windows.net/ContainerName/lm-d365-xxxxxxxxxxxxxx.cloudax.dynamics.com/*/*/model.json',        	
        FORMAT = 'CSV',
        FIELDQUOTE = '0x0b',
        FIELDTERMINATOR ='0x0b',
        ROWTERMINATOR = '0x0b'
    )
    WITH (
        jsonContent varchar(MAX)
    ) AS [result]
      CROSS APPLY OPENJSON(JSON_QUERY(jsoncontent, '$.entities')) as entity
      CROSS APPLY OPENJSON(entity.Value, '$.attributes') as attributes
      CROSS APPLY OPENJSON(entity.Value, '$.partitions') as partitions

Step 2: Create Stored Procedure that takes three parameters and returns a view with entity data
/****** Object:  StoredProcedure [dynamixfo].[spcreateModelView]    Script Date: 3.30.2021 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dynamixfo].[spcreateModelView] 
    @rootfolderName nvarchar(200), @EntityOrCubeName nvarchar(200),  @entityName nvarchar(200)
AS
BEGIN
    /*
        Power BI Dataflow reader for Synapse.	
        
        Reader created by Vesa Tikkanen

    */


    DECLARE @columnsChar nvarchar(max)='';
    DECLARE @columnsCharSel nvarchar(max)='';


    -- check if names of the workspace, dataflow or entity contains not compatible characters.
    IF CHARINDEX('[',@rootfolderName)&gt;0 OR CHARINDEX(']',@rootfolderName)&gt;0 OR
    CHARINDEX('[',@EntityOrCubeName)&gt;0 OR CHARINDEX(']',@EntityOrCubeName)&gt;0 OR 
    CHARINDEX('[',@entityName)&gt;0 OR CHARINDEX(']',@entityName)&gt;0
    BEGIN
        PRINT 'Escape characters detected. Cannot create.'
        RETURN
    END


    select @columnsChar = STRING_AGG(colname,', ')  from 
    (select  '[' + ColumnName + '] ' + CASE 
		when ColumnDataTypeSQL='datetime2' then 'nvarchar(200)' 
		when ColumnDataTypeSQL='date' then 'nvarchar(200)' 
		else ColumnDataTypeSQL END as colname  from  [dynamixfo].[MyDynamixModels] where rootfolderName=@rootfolderName
        and EntityOrCubeName=@EntityOrCubeName and entityName=@entityName
        ) as a;



    select @columnsCharSel = STRING_AGG(colname,', ')  from 
    (select  CASE 
	
		when ColumnDataTypeSQL='datetime2' then 'convert(datetime2,[' + ColumnName + '],101) AS [' + ColumnName + '] ' 
		when ColumnDataTypeSQL='date' then 'convert(date,[' + ColumnName + '],101) AS [' + ColumnName + '] ' 
		else '[' + ColumnName + '] ' END as colname  from  [dynamixfo].[MyDynamixModels] where rootfolderName=@rootfolderName
        and EntityOrCubeName=@EntityOrCubeName and entityName=@entityName
        ) as a;

    --select @columnsChar;


    DECLARE @sqlcmd nvarchar(max);

    SET @sqlcmd = N'DROP view if exists dynamixfo.[' + @rootfolderName + '_'+ @EntityOrCubeName + '_' + @entityName + N'];';
    EXECUTE sp_executesql @sqlcmd;
    --select @sqlcmd;

    select @sqlcmd = N'CREATE view dynamixfo.[' + @rootfolderName + '_' + @EntityOrCubeName + '_'  + @entityName + N']
    AS' + STRING_AGG(subselect,'

    UNION ALL

    ')  from 
    (select  

    N'

    SELECT
        ' + @columnsCharSel+ N'
    FROM
        OPENROWSET(
            BULK ''' + REPLACE(FileName,'%20',' ') + N''',
            FORMAT = ''CSV'',
            PARSER_VERSION=''2.0''
        ) 
        WITH (
    ' + @columnsChar + N'
    )
        
        AS [result]

    '
    as subselect  from  [dynamixfo].[MyDynamixModels] where rootfolderName=@rootfolderName
        and EntityOrCubeName=@EntityOrCubeName and entityName=@entityName group by FileName
        ) as a;


    --select @sqlcmd;

    EXECUTE sp_executesql @sqlcmd;


END
GO
Step 3: Execute the stored procedure
# You can repeat this step for all the views that you need to create.
# Note the examples are for Aggregate Measurements, but F&amp;O datalake sync will expose raw tables as well
EXECUTE [dynamixfo].[spcreateModelView] 'AggregateMeasurements','Recruiting','Recruiting_Performance'

In terms of data refresh, the views once created in the SqlOnDemand database will be pointing to the datalake files, and as they get updated by the data sync process, the views will return the latest information when executed. I have tested it manually by changing data in the CSV files, and it does pick the new changes. This solution can be used with the serverless SQL compute until the Openrowset function can handle the CDM format. There is a feature request under review, which we hope will make it to the roadmap as CDM format is becoming widely used across Microsoft Data estate.

Azure SQL Managed Instance YAQS

Azure SQL Managed Instance (MI) is a Platform as a Service (PaaS) that lives in Vnet, inside a subnet and is assigned IP addresses from the private IP address space.

Power Bi Embedded with PHP CURL- App Owns Data

Power BI Embedded will enable you to embed powerful Dashboards, Reports, and Tiles into your application. An ISV can leverage the continuous development efforts from the Microsoft Power Bi team and embed several types of reports for their users and customers, while branding the application with their Logo and color scheme.
Before you get started, you will need to double check the business requirements. Are you embedding for organization users? External users such as customers, vendors, or suppliers? Embedding in SharePoint or Microsoft Teams? Embedding a public dashboard that’s using a public dataset?
This discovery stage is very important, as you might not need to program anything, if you are embedding a Power bi Dashboard into a SharePoint site, or if you are publishing an iframe of a “Publish to Web” URL with a public dataset or unsecure content.
PBI embedded requires you to buy one of the three SKUs below:


There are three areas where you must do some work to create this solution:
1- Power BI: You need a Power BI pro account to be able to create an app workspace, which will be the container of your reports and dashboards that you will embed. In Addition, you will need either an Azure power bi embedded subscription, or a Power bi premium subscription to allow your users to consume the reports.


Then you will need to link your app workspace to your provisioned capacity.

2- Microsoft Azure: This is where you need to register an Azure AD application that will give you the credentials to allow you to embed Power BI content. This is discussed in more details below. If you are going to use the Azure PBI embedded resource, then you would need to add the resource capacity here as well.

3- Your Application: You can host it anywhere, use any programming language to communicate with Azure AD and Power BI REST API. You will also need to download or link to the power bi JavaScript library.
Once you ascertain that you will need power bi embedded into your own application, you would need to decide between the two types of embedding:
User owns Data: This is for a scenario where an organization wants users to login to their website and see their PBI reports. Users need to have a Power BI account either free or Pro, and the organization needs to provision Premium Capacity
App owns Data: This is a scenario for organizations that will be serving reports to customers that don’t have a power bi license, and usually unaware that the reports are provided by power bi. At least one Power bi Pro user will be required to create the app workspace and reports, as well as either Azure power bi embedded capacity (A SKU) or Office premium capacity (P SKU).
This article will discuss embedding for the “APP owns Data” Scenario, so lets’ get started.
First, study the image below, as you will programmatically need to go through this Authorization Grant Flow to access the power bi resource:


Step 1: Get an OATH2 token through a CURL POST request to the Azure AD API.
To get started with step 1, we must register an application in Azure AD. You can do that through the Azure management portal > Azure Active Directory > App registrations. Make sure you register a native application not a web app.

You can also use the Power BI App Registration tool. After you register the app, you need to assign it the required permissions as illustrated in this updated article.
You are ready to get started with coding: (The code below is mixed with comments and image illustrations. You can download the complete source from GitHub.)

<?php $curl1 = curl_init(); curl_setopt_array($curl1, array( CURLOPT_URL => "https://login.windows.net/common/oauth2/token",
CURLOPT_RETURNTRANSFER => true,
CURLOPT_ENCODING => "",
CURLOPT_MAXREDIRS => 10,
CURLOPT_TIMEOUT => 30,
CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
CURLOPT_CUSTOMREQUEST => "POST",
CURLOPT_POSTFIELDS => array(
grant_type => 'password',
scope => 'openid',
resource => 'https://analysis.windows.net/powerbi/api',
client_id => ' ', // Registered App Application ID

username => ' ', // Your Power BI Pro account. For example john.doe@yourdomain.com
password => ''  // Password for above user
)
));
$tokenResponse = curl_exec($curl1);
$tokenError = curl_error($curl1);
curl_close($curl1);
// decode result, and store the access_token in $embeddedToken variable:
$tokenResult = json_decode($tokenResponse, true);
$token = $tokenResult["access_token"];
$embeddedToken = "Bearer "  . ' ' .  $token;

The above code has accomplished the following:
Application identity with OAuth 2.0 client credentials grant
1. First, the server application needs to authenticate with Azure AD as itself, without any human interaction such as an interactive sign-on dialog. It makes a request to Azure AD’s token endpoint, providing the credential, Application ID, and application ID URI.
2. Azure AD authenticates the application and returns a JWT access token that is used to call the web API.
The code below will accomplish the third step in the authentication/authorization process:
3- Over HTTPS, the web application uses the returned JWT access token to add the JWT string with a “Bearer” designation in the Authorization header of the request to the web API. The web API then validates the JWT token, and if validation is successful, returns the desired resource.
Let’s define what a group is before introducing a group id in the code, because it’s a very important concept, and understanding it will save you time going forward.
Groups are a collection of unified Azure Active Directory groups that the user is a member of and is available in the Power BI service. These are referred to as app workspaces within the Power BI service. To learn how to create a group, see Create an app workspace.
/* Use the token to get an embedded URL using a GET request */
$group_Id = 'f6xzs20-850b-04az-9hga-3d00087191'; // Your power bi app workspace group id.

Easiest way to get your group id is by browsing to your app workspace in Power BI, and getting the alphanumerical string after groups in the URL. You can also get it programmability through a REST call.

/*Use the token to get an embedded URL using a GET request */
$group_Id = ' ';
$curl2 = curl_init();
curl_setopt($curl2, CURLOPT_URL, 'https://api.powerbi.com/v1.0/myorg/groups/'.$group_Id.'/reports/');
curl_setopt($curl2, CURLOPT_RETURNTRANSFER, trUE);
curl_setopt($curl2, CURLOPT_ENCODING, "");
curl_setopt($curl2, CURLOPT_MAXREDIRS, 10);
curl_setopt($curl2, CURLOPT_TIMEOUT, 30);
curl_setopt($curl2, CURLOPT_CUSTOMREQUEST, "GET");
curl_setopt($curl2, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
curl_setopt ($curl2, CURLOPT_HTTPHEADER,array(

        'Authorization:'.$embeddedToken,
        'Cache-Control: no-cache'
    ));

$embedResponse  = curl_exec($curl2);
$embedError = curl_error($curl2);
curl_close($curl2);

if ($embedError) {

echo "cURL Error #:" . $embedError;

} else {

$embedResponse = json_decode($embedResponse, true);

$embedUrl = $embedResponse['value'][1]['embedUrl'];

}
?>

Now that our application has retrieved the bearer Token from Azure AD, and it presented it to Power BI REST API along with a group id where the reports reside. The REST service will return an array with the report names, their ID, embed URL, and some other data.
Next, we will use jQuery and Power BI JavaScript file to render the report:

<?php
include 'pbi-logic.php';
include 'header.php'; ?>
<div id="reportContainer"></div>
<script>
// Get models. models contains enums that can be used.
var models = window['powerbi-client'].models;
// Embed configuration used to describe the what and how to embed.
// This object is used when calling powerbi.embed.
// This also includes settings and options such as filters.
// You can find more information at https://github.com/Microsoft/PowerBI-JavaScript/wiki/Embed-Configuration-Details.
var embedConfiguration= {
type: 'report',
id: ' ', // the report ID
embedUrl: "<?php echo $embedUrl ?>",
accessToken: "<?php echo $token; ?>" ,
};
var $reportContainer = $('#reportContainer');
var report = powerbi.embed($reportContainer.get(0), embedConfiguration);
</script>
</body>
</html>

You should be done now. Make the required changes to the embed configuration as needed to turn on and off different aspects of your report, dashboard, or tile. To create a report from your application, for instance, you need to supply a dataset:

var embedCreateConfiguration= {
type: 'report',
datasetId: ' ', // dataset ID
embedUrl: "<?php echo $embedUrl ?>",
accessToken: "<?php echo $token; ?>" ,
};
var $embedContainer = $('#reportContainer');
// Create report
var report = powerbi.createReport($embedContainer.get(0), embedCreateConfiguration);

// Report.off removes a given event handler if it exists.
report.off("loaded");

// Report.on will add an event handler which prints to Log window.
report.on("loaded", function() {
    Log.logText("Loaded");
});
report.off("error");
report.on("error", function(event) {
    Log.log(event.detail);
});
// report.off removes a given event handler if it exists.
report.off("saved");
report.on("saved", function(event) {
    Log.log(event.detail);
    Log.logText('In order to interact with the new report, create a new token and load the new report');
});
</script>
</body>
</html>

Power bi embedded for ISV will save you a lot of time, provide you with a lot of flexibility, and will offer you continuous development and enhancements. I have written this article to help fellow developers, especially if you don’t know .net to be able to follow the samples available from Microsoft.
Find a link to my GitHub repo, where you can download the working code tested on Apache/PHP7.
Links to websites that helped me along the way:

https://docs.microsoft.com/en-us/power-bi/developer/embedded-faq

https://github.com/Microsoft/PowerBI-JavaScript

https://docs.microsoft.com/en-us/azure/active-directory/develop/active-directory-authentication-scenarios#web-application-to-web-api

http://www.msbiblog.com/2018/01/12/power-bi-embedded-example-using-curl-and-php/

Remember that failure is an event, not a person

I have been through a fair share of failures in my life, which correlates to the amount of success I have achieved so far. It is proven that the more you fail, as long as it’s not accepted as such, the more you will eventually succeed and grow.
I have experienced losing in a variety of ways throughout my life. Losing a foot race, a soccer game, securing a lucrative station while waiting tables at a restaurant, losing at getting a soccer scholarship at an NCCA Division 1 school, then also failing to secure enough scholarship money at an NAIA University, then later on being benched at a no scholarship school, failing some classes in college, failing in creating a business on the side, and then failing at convincing my interviewer that I am a good hire.
When I reminisce on those failures, they were a glaring sign that I am not ready, so I needed to invest time and effort in more preparation, or maybe I needed to take a detour, or sometimes it is as easy as fix my attitude. The problem is that when you know everything, and you believe the world is conspiring against you, then you will see nothing that needs fixing.

Luckily for me, my spiritual beliefs go against the defeatist mental attitude, but actually promote the positive mental attitude, which Napoleon hill preached in his talks and books. I also believe that watching or playing sports enhances the mental toughness, and as a new Dad, I will make sure my son is exposed to a variety of sports. I gained discipline from Martial arts and grit from playing soccer. Lifting weights and watching your muscles develop teaches you patience and the rewards of hard work. Receiving a punch in the face and seeing stars in daylight in a smoker fight in my MuayThai gym made me experience 3 minutes of time turn into eternity, so once you go through such experiences, then everything else is a piece of cake. You say work is tough?! You mean doing some tasks for a specified period of time where I am not gonna get punched by mistake during training, or your chin will not hit my knee when practicing a leg kick? When we learn how to knee, you are not gonna hang all your weight on my neck so it will be sore for a week? How about breaking my arm or finger like I did playing soccer? Oh, so none of that is going to happen at work? Joking aside, the mental demands of work sometimes make you more exhausted then any physical effort. I don’t mind a lot of work and tasks and deadlines, what really gets to me is stress inducing people. Especially, the ones that want to climb over bodies to the top, leaving behind them a pile of corpses. But again, if you can take a punch in the face, you really can withstand a lot in life. I leave you with some quotes to inspire you to keep going in your journey, and to never let failure make you doubt the true successful person you are!

Success consists of going from failure to failure without loss of enthusiasm.

Winston Churchill

Remember that failure is an event, not a person

Zig Ziglar

I can accept failure, everyone fails at something. But I can’t accept not trying

Michael Jordan

During my 18 years I came to bat almost 10,000 times. I struck out about 1,700 times and walked maybe 1,800 times. You figure a ball player will average about 500 at bats a season. That means I played seven years without ever hitting the ball

Mickey Mantle

Some people believe football is a matter of life and death, I am very disappointed with that attitude. I can assure you it is much, much more important than that

Bill Shankly

EC2 AWS Community AMI Security

In the wake of yesterday’s ransomware incident, Information security is in the lime light, and before I connected to the internet today, I clicked restart on the annoying windows updates message, instead of postponing it another 4 hours.

Computer is up to date with all security fixes and anti-virus definition files have been updated. I am ready to go.

I wanted to experiment with a web application, but didn’t want to spend the time configuring LAMP, and a PHP framework, so I opted for using an Amazon AWS EC2 community API pre-loaded with the application stack.

I was lucky to find one, so I went ahead and launched it, and accessed it via the server IP.

I created my RDS DB in order to change the “database.php” config file to point to my own DB.

When I opened the config file to edit it; there I found some brazilian website “.br” as the host name, and mysql root credentials.

I thought, there is no way this is still live, so I tried to connect via command line to the remote host.

I got the prompt back!

I had root on their MySQL database with no effort.

I am not sharing any screenshots on this blog post to protect the server. But this incident is a reminder to make sure you don’t share AMIs until you remove all credentials, user history, etc.

Follow this guide for more info: https://aws.amazon.com/articles/0155828273219400

Another tip is to run the mysql secure installation script.

It’s also a good idea to block remote connections to your SQL server.

I went ahead and contacted AWS security to alert them to this AMI, so they can take care of taking it down, and contacting the site owner.

In the wake of security breaches, one is amazed how these criminals are so successful with their exploits. It comes down to our deficiency in not following basic, annoying security steps one has to take. I say annoying, because they kind of are. Nobody wants to have multiple locks to their house, and then a lock for every room, security guard outside, etc… You get where I am going with this. However, today’s reality where most of our computing is in public networks makes it a priority to follow good security practices. Creating hard to guess passwords, implementing password expiration, multi factor authentication, software patching, updates to both OS and anti-virus definition files should be part of our disciplined approach to cyber security.

The ransomware that ravaged UK NHS computers was caused by running unpatched OS. It’s easy for me to sit here and speculate about the why?!

What’s apparent is that the NHS CTO or CSO (if they have one) decided to take on that risk, and we now know it was a costly decision.

Update: AWS Security replied to my e-mail where they referred to their “Shared responsibility model“, and that they can not take down the EC2 instance. So, until the owner takes action, this EC2 image is still there for people to use.

Excerpt from AWS Security message:

To be clear, the security concern you have reported cannot be resolved by AWS but must be addressed by the customer, who may not be aware of or be following our recommended security best practices.

We have passed your security concern on to the specific customer for their awareness and potential mitigation.

 

 

Reset Password of Bitnami WordPress Installation

I forget my password regularly, and I don’t have e-mail setup on my WordPress site; therefore, clicking on the forget password link doesn’t do me any good. This article will serve as a step by step process I can use in the future in case this happens again, and it can save you time as well if you face the same problem

An AWS bitnami WordPress EC2 machine comes with a packaged installation of LAMP, WordPress, and PhpMyAdmin that’s accessible through localhost.

In order to reset your WordPress password, you will need access to the database either through the command line or through a MySql database management tool like PhpMyAdmin.

This article will guide you through the process of resetting your WordPress password through PhpMyAdmin.

These instructions are for a windows environment using putty SSH client. Same concepts apply to other platforms.

  1. Create an SSH tunnel using putty. You will need to have your EC2 private key, and you need to know your domain name/IP. (Image 1 and Image 2)
  2. After you have entered your HostName under Session Category, your private key under Connection>SSH>Auth; click on Tunnels to create a secure tunnel by forwarding port 80 on the server to port 8888 on the local host (127.0.0.1 or localhost). In Source Port type port “8888”, and in the destination type “127.0.0.1:80”, the click add. If you happen to be using port 8888 for anything in your local machine, then change the source port to any non-privileged open port. (Image 3)
  3. Login using your user name, the default user is “bitnami” if you haven’t changed it. (Image 4)
  4. Now that you have a tunnel between your local machine and your server, you can access phpMyAdmin through the URL: http://127.0.0.1:8888/phpmyadmin (Image 5)
  5. Next, you will need a DB user and password. Since you are on the server, navigate to “/opt/bitnami/apps/wordpress/htdocs” and open wp-config.php, look for the database user and password your WordPress is using to connect, and use that to login to PhpMyAdmin.
  6. Once Logged in, search for the table: “wp_users”, and double click on the name to open it.
  7. Click on edit next to the username you want to edit its password.
  8. The password is MD5 hashed, so you can’t recover it there, but you can delete it, and copy and paste the MD5 hash of a new password. Use this website to get the hash: http://www.miraclesalad.com/webtools/md5.php
  9. Once you paste your new password (Hint, don’t use “new password”, but your real password), click Go to update the table row for that user.
  10. Repeat same process for other users.
  11. Test logging in to WordPress, it should work now!

Design VPC CIDR Block For No Conflict

Today is Saturday, and I am out for an early breakfast at Panera, as usual, and I have my laptop with me to work on some AWS stuff.

My plan is to finish setting up installing Laravel PHP framework as part of a tutorial I am following for storing laravel sessions on DynamoDB. Since my Web Server is hosted on an EC2 instance on a private Subnet, I need to first VPN into my VPC to be able to get to it.

So I proceeded to connect through VPN to my OpenVPN EC2 on my public subnet. Connection is established. Now, I need to SSH to my 10.0.3.208 machine, but nothing is happening, and eventually the connection times out!

I checked my security groups, Routes, and NACLs; everything looks good, and I haven’t changed my setup from when I successfully connected the day before.

Is AWS flaky? What’s going on?

I run Wireshark to help me figure out what’s causing the connection timeout. I am trying to connect through port 22 with putty, and port 80.

I am seeing ICMP packets from a 10.128.128.128 device telling me that the destination is unreachable, and that the communication is administratively filtered.

Not Sure what this device is, but let’s do an ipconfig on the local machine:

Ok, so Panera bread, wireless router is rejecting my request for a server that it thinks is in it’s 10.0.0.0/8 network.

My VPC is on 10.0.0.0/16

My private subnet is on 10.0.3.0/24

Ok, so I guess the wireless router will not let this slide by him so my packets can travel through the VPN tunnel to my EC2 machine on my VPC.

I did some google search to see if I can make this work through some Ninja networking tricks. The threads I read unanimously agree that you need to design your VPC with a CIDR block that doesn’t conflict with your local network.

Every now and then, some network guru will tell you about all the hoops he went through to make it somehow work, but why go through the trouble, when you could design your VPC with no conflict in mind? Plus, I needed access to the local network router to try those solutions, and Panera wasn’t about to hand me the credentials to their router.

A Cisco article I read presents a solution through the use of NAT and DNS. A use case for that could be two companies merging with the same private  address space, and what you would do to prevent re-addressing the local network of one of them.

Anyway, I can’t stress enough the importance of choosing the right VPC CIDR.

Avoid the 192.168.x.x since most home network routers use that RFC 1918  IP addresses.

To solve my issue, I need to go home and connect. Surely enough, as soon as I connected to my home network, I was able to VPN, and browse to my private web server and SSH to it:

Traffic through wireshark looks good. Syns are being acknowledged, and traffic is flowing nicely through the VPN adapter:

One caveat is that if you want to see network traffic for your private IP address as the destination IP before it gets encapsulated into VPN encrypted packets, you need to make sure you are listening to the VPN adapter not your wireless adapter, or your network card if you are connected with an Ethernet cable to your router. (It took me some googling to figure that out)

My VPN server is configured with this CIDR: 172.27.224.0/20, which it uses to assign IPs to connected clients.

I have tried to produce some conflict again with my VPC, and I changed my VPN server CIDR to 10.0.0.0/16:

My VPN server couldn’t route traffic correctly, I couldn’t resolve domains to browse the internet, so that’s another place to look at in case you have problems with your VPN.

Software defined networks enables services such Amazon AWS VPC, which gives us greater control over our cloud network topology.

“With great power comes great responsibility”, so choose your CIDR blocks wisely.

AWS CloudFormation and PHP SDK

Recently, I have gotten involved in a project where, among other things, we needed to create EC2 instances from a web application.
We are using CloudFormation templates to create VPC with public and private subnets, Internet gateway, Security Groups, NACLs, etc.
We have decided to have separate stacks for network infrastructure, servers, ELB and auto-scalers, and we are also considering a separate security stack.
What makes this possible is using the cross-stack reference option.
“To create a cross-stack reference, use the Export output field to flag the value of a resource output for export. Then, use the Fn::ImportValue intrinsic function to import the value”
So simply put, you create a new stack where you import values from a parent stack where those same values are exported in the “Outputs” section.
In this example, I am exporting values from my main template to be consumed later by my EC2 instances template:

"Outputs" : {
"PrivateSubnet" : {
"Description" : "The subnet ID to use for web servers behind an ELB",
"Value" : { "Ref" : "PrivateWebServer1" },
"Export" : { "Name" : {"Fn::Sub": "${AWS::StackName}-SubnetID" }}
},
"BasicSecurityGroup" : {
"Description" : "The security group ID to use for private web servers",
"Value" : { "Fn::GetAtt" : ["BasicSecurityGroup", "GroupId"] },
"Export" : { "Name" : {"Fn::Sub": "${AWS::StackName}-SecurityGroupID" }}
}
}

Notice that I am first referencing the value that need to be exported using the “Ref” function.
Next, I am using the Export parameter to export the SubnetID, and in the next declaration the SecurityGroupID of a Stack Name.
Using the Fn-Sub function, I am substituting the $(AWS::StackName) variable with an input parameter.
This Parameter is defined in our Parameters section of the child template:

“"Parameters": {
"NetworkStackName": {
"Description": "Name of an active CloudFormation stack that contains the networking resources, such as the subnet and security group, that will be used in this stack.",
"Type": "String",
"MinLength" : 1,
"MaxLength" : 255,
"AllowedPattern" : "^[a-zA-Z][-a-zA-Z0-9]*$",
"Default" : "Your-Parent-Template"
}
}

This is the complete child stack that will import values from its parent stack:

{
"AWSTemplateFormatVersion": "2010-09-09",

"Description" : "CF stack to create an EC2 instance, with Parent VPC name as parameter",

"Parameters": {
"NetworkStackName": {
"Description": "Name of an active CloudFormation stack that contains the networking resources, such as the subnet and security group, that will be used in this stack.",
"Type": "String",
"MinLength" : 1,
"MaxLength" : 255,
"AllowedPattern" : "^[a-zA-Z][-a-zA-Z0-9]*$",
"Default" : "MyVPC3"
}
},

"Resources": {

"WebServer": {

"Type": "AWS::EC2::Instance",

"Properties": {

"InstanceType" : "t2.micro",

"ImageId" : "xxxxxxxx",

"KeyName" : "xxxxxxxxx",

"NetworkInterfaces": [ {
"AssociatePublicIpAddress": "false",
"DeviceIndex": "0",
"GroupSet" : [{ "Fn::ImportValue" : {"Fn::Sub": "${NetworkStackName}-SecurityGroupID" } }],

"SubnetId":{ "Fn::ImportValue" : {"Fn::Sub": "${NetworkStackName}-SubnetID" } }
} ]

}
}
}
}

when the child stack is ran, it will create an EC2 machine inside the VPC and subnet created in the parent stack, and assign it the appropriate security group.
After testing that everything works, now it’s time to create an EC2 instance from a web application.
We will use PHP, and the well documented AWS SDK for PHP.
Best way to install the SDK is to use Composer.
Go through the trouble of installing it because it’s worth it. You won’t have to worry about troubleshooting issues with dependencies, because composer takes care of that.
I am using PHP with IIS on a windows server.
Download the exe file, and install composer. Check if it’s installed:

Next, install the PHP SDK by following these instructions, and run the following command from the vendor directory of your PHP project: (If PHP binary is not in your path, then specify the full command path)

php -r "eval('?>'.file_get_contents('https://getcomposer.org/installer'));"

To use the PHP SDK for any AWS service, you will need to first start with creating a client. For more information on how to get started with calling an AWS service API, read this guide:
The basic usage pattern of the SDK is that you instantiate a Client object for the AWS service you want to interact with

My Cloudformation service client looks like this: (You can use credentials online, or use a profile in your credentials file. For security, use profiles!)

<?php require 'C:\awscf\vendor\autoload.php'; //Create a client use Aws\CloudFormation\CloudFormationClient; $client = CloudFormationClient::factory(array( 'region' => 'us-east-1',
    'credentials' => [
        'key'    => 'xxxxxxxxxxxxxxxxxxx',
        'secret' => 'xxxxxxxxxxxxxxxxxxxx',
    ],
));

// Create your stack stored on S3 that imports data from your network stack
    $result = $client->createStack(array(
    // StackName is required
    'StackName' => 'Webserver1',
    'TemplateURL' => 'https://s3.amazonaws.com/yourbucket/yourfile.template',
    ));
?>

Very straight forward way of creating a stack from your PHP web application. We have only used 2 elements of the array to keep it simple, but you are welcome to take advantage of the full capabilities of the API call.
For example, you could pass on your parameter of parent stack name to the child CF stack, instead of using the default.

I hope you found this article helpful to get you started quickly with using the AWS cloudformation PHP SDK.

Amazon AWS CloudFormation stack with Eclipse- Step by step guide_Part1

To follow this step by step guide, please install Eclipse with AWS toolkit.

This article will guide you through the installation and configuration.

Before using CloudFormation, It’s a good idea to navigate different services through the web console; create a VPC with subnets, and assign them CIDR IP blocks; launch EC2 instances, and pay attention to the different options available in the wizard; create security groups, Routes, and NACL rules.

The web console knowledge gained will help you read CF templates, and appreciate the logical relationships between a Resource and its properties, as well as relationships between different sections.

A CloudFormation template can have up to 8 sections, but only the Resources section is required.
If you use some of the optional sections, you will most likely need to reference the data in those sections using Intrinsic Functions.
For example, if you create a Mappings sections; inside your Resources section, you will use the function Fn::FindInMap to return the value corresponding to the key you declared under Mappings.

Let’s take a look at this closely:

“ImageId” : { “Fn::FindInMap” : [ “ImageMap”, { “Ref” : “AWS::Region” }, “MonitoringAMI” ]},

Here, ImageId is a property of AWS::EC2::Instance Resource, and as the name implies, it defines the AMI that will be used for the EC2 instance 

We could have easily assigned an AMI inline without using Intrinsic functions:
“ImageId” : “ami-79fd7eee”,

Using Mappings; however, will make your CF templates more readable and maintainable.

For instance, in the example below, you can add multiple mappings that will cover the regions where you intend to run your stack.

 "Mappings" : {
"ImageMap" : {"us-east-1" : { "OpenVpnAMI" : "ami-bc3566ab", "MonitoringAMI" : "ami-b73b63a0","NiFiAMI" : "ami-b73b63a0", "ClouderaAMI" : "ami-20b6c437", "RstatAMI" : "ami-b73b63a0", "VisualAMI" : "ami-b73b63a0" },
"us-east-2" :{ Hop on to your webconsole, and fill in us-east-2 AMI mappings}
 }
},

As you know, an AMI number is region specific, so for the same image, the ID will be different in each region. Mapping AMI IDs to a region will aid you in optimizing your template, and not needing to create a separate template for each region.
This pseudo parameter that’s predefined by cloudformation is what passes the region name back to the Mappings using the Ref function: AWS::Region

You can expand upon this ImageMap section by creating a mapping for each of the 14 Amazon AWS regions available. (I excluded the US government region)

Amazon AWS documentation is top notch, so there is no need to replicate what’s already available, and in great details from their website. Here is a link that describes the different template sections, and their use: 

http://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/template-anatomy.html

The best way to learn is by doing, so let’s get started with an example of creating a stack for a Big data in the Cloud project.

This project will be housed in a VPN with one public subnet and 6 private subnets. Each subnet will run an EC2 instance that preforms a task in this miniaturized big data ecosystem.

I will be unconventional, and I will list how I started and the end result.

In future articles, we can expand more by explaining the stack creation process in detail, updating our CF stack with security groups, RDS database, and more updates to route table or NACL to tighten up security.

If you are a visual person, and you need to see it to believe it, then I would recommend using the web console CF template designer to kick start the building of your stack.

Here is a screenshot from my web console:

After a certain point, where you will need to fill out properties of your resources, that’s where you can save the template to your local drive and open it in Eclipse.

The designer needs to keep track of the dimensions and placement of boxes , objects, and lines in your template, so it adds Metadata containing this extraneous information throughout your template.

It will look like this:

"Metadata": {
"AWS::CloudFormation::Designer": {
"c733e469-afeb-4ccb-b0c1-f6c4125295f8": {
"size": {
"width": 1200,
"height": 1230
},
"position": {
"x": -80,
"y": -130
},
"z": 0,
"embeds": [
"8c3863c1-d144-4baf-8b8b-167eb0c83aae",
"01c6050d-1dc2-40e2-ace6-9c595b881719",
"7d4f0b22-bcdd-4595-a650-b9911f4479ef",
"8cfe599e-6f64-4c67-b720-82a8d3ee91ca",
"d4d7a5a5-7a23-44a8-b8e4-6e4b65d23407",
"b7769298-ed5a-4cea-9507-4b8e1c0709d6",
"74ad0ea8-0d45-4c27-92bc-5d70cac6d2ad",
"55003a29-f97f-4b22-8990-c8c5989a293d",
"4517c146-da09-4c8c-a9bc-ce8613f52f83",
"a0c8a6f3-037c-4957-b48a-415508e57fac",
"375e3d1a-007b-4393-8b05-c5ee7a7a6e15",
"d31fc2ee-1ca6-4ffa-982d-f914481aa62c",
"ed8ca7d4-12f7-42a8-a211-0b6788bef0fd",
"55912648-c1f4-4e93-a12d-1ef206bc28f8",
"71d77869-3266-4421-aaa1-b0efc9b9f19c"
]
},
"8c3863c1-d144-4baf-8b8b-167eb0c83aae": {
},
"size": {
"width": 490,
"height": 120
},
"position": {
"x": 0,
"y": -110
},
"z": 1,
"parent": "c733e469-afeb-4ccb-b0c1-f6c4125295f8",
"embeds": [
"10172244-abe9-49d4-923b-597566a0f720"
]
},
"01c6050d-1dc2-40e2-ace6-9c595b881719": {
"size": {
"width": 490,
"height": 120
},

None of that information is going to be used in creating your stack, so I have decided to clean up my template from all this Metadata, and continue building the stack in Eclipse.

I have decided to use the following sections: Format version, Description, Parameters, Mappings, and Resources. Here is the final CF stack, please note that it’s missing the RDS database, and security groups.

{
  "AWSTemplateFormatVersion": "2010-09-09",
  
  "Description" : "AWS CloudFormation template for a VPC with one public subnet and six private subnets for running a Big Data ecosystem. The following instances will be deployed in each subnet with different tasks: OpenVpn for authentication, Nagios or Kabana for monitoring, Apache Nifi for ETL, RDS MySql for database storage, Cloudera for BigData, OpenR or Revo for analytics,  Qlik or Tabelaux for visualization", 
 
  "Parameters" : {
    "InstanceType" : {
      "Description" : " Lab instances are t1/t2.micro, or t1.small",
      "Type" : "String",
      "Default" : "t2.micro",
      "AllowedValues" : ["t1.micro","t2.micro","t2.small"]
    },
    
    "ClouderaInstanceType" : {
      "Description" : " Lab instances are t1/t2.micro, or t1.small",
      "Type" : "String",
      "Default" : "t1.micro",
      "AllowedValues" : ["t1.micro","t2.micro","t2.small"]
    },
    
    "KeyName" : {
      "Description" : "Name of an existing EC2 keyPair to enable SSH access to the instance",
      "Type": "AWS::EC2::KeyPair::KeyName",
      "ConstraintDescription" : "Must be the name of an existing Key pair"
       },
       
      "CFKeyName" : {
      "Description" : "Name of an existing EC2 keyPair to enable SSH access to the instance",
      "Type": "AWS::EC2::KeyPair::KeyName",
      "ConstraintDescription" : "Must be the name of an existing Key pair"
       },
      
    "SSHLocation": {
      "Description": "The IP address range that can be used to SSH to the EC2 instances",
      "Type": "String",
      "MinLength": "9",
      "MaxLength": "18",
      "Default": "0.0.0.0/0",
   
      "ConstraintDescription": "must be a valid IP CIDR range of the form x.x.x.x/x."
    }
}, 
   
  "Mappings" : {
    
     "ImageMap" : {
      "us-east-1" : { "OpenVpnAMI" : "ami-bc3566ab", "MonitoringAMI" : "ami-b73b63a0","NiFiAMI" : "ami-b73b63a0", "ClouderaAMI" : "ami-20b6c437", "RstatAMI" : "ami-b73b63a0", "VisualAMI" : "ami-b73b63a0" },
      "us-east-2" :{ }
      }   
    }
 }, 
    
  "Resources": {
 
    "VPC": {
      "Type": "AWS::EC2::VPC",
      "Properties": {
        "CidrBlock" : "10.0.0.0/16",
        "EnableDnsSupport" : "true",
        "EnableDnsHostnames": "true",
        "InstanceTenancy": "default"
          }
          
         },
         
      "BasicSecurityGroup" : {
      "Type" : "AWS::EC2::SecurityGroup",
      "Properties" : {
        "VpcId" : { "Ref" : "VPC" },
        "GroupDescription" : "Enable SSH access",
        "SecurityGroupIngress" : [
          {"IpProtocol" : "tcp", "FromPort" : "22", "ToPort" : "22", "CidrIp" : { "Ref" : "SSHLocation"}}
        ]
      }
    },
      
      "PublicAuthentication": {
      "Type": "AWS::EC2::Subnet",
      "Properties": {
        "VpcId": {
          "Ref": "VPC"
        },
        "CidrBlock" : "10.0.0.0/24",
       "AvailabilityZone" : "us-east-1e"
      }
      
    }, 
  
    "PrivateDataLanding": {
      "Type": "AWS::EC2::Subnet",
      "Properties": {
        "VpcId": {
          "Ref": "VPC"
        },
       "CidrBlock" : "10.0.1.0/24",
       "AvailabilityZone" : "us-east-1c"
      }
     
    },
    "PrivateDatabase": {
      "Type": "AWS::EC2::Subnet",
      "Properties": {
        "VpcId": {
          "Ref": "VPC"
        },
        "CidrBlock" : "10.0.2.0/24",
        "AvailabilityZone" : "us-east-1d"
      }
      
    },
    
    "PrivateDatabase2": {
      "Type": "AWS::EC2::Subnet",
      "Properties": {
        "VpcId": {
          "Ref": "VPC"
        },
        "CidrBlock" : "10.0.3.0/24",
        "AvailabilityZone" : "us-east-1a"
      }
      
    },
    "PrivateDataLake": {
      "Type": "AWS::EC2::Subnet",
      "Properties": {
        "VpcId": {
          "Ref": "VPC"
        },
       "CidrBlock" : "10.0.4.0/24",
       "AvailabilityZone" : "us-east-1d"
      }
    },
    "PrivateAnalytics": {
      "Type": "AWS::EC2::Subnet",
      "Properties": {
        "VpcId": {
          "Ref": "VPC"
        },
         "CidrBlock" : "10.0.5.0/24",
       "AvailabilityZone" : "us-east-1d"
      }
     
    },
    "PrivateVisualization": {
      "Type": "AWS::EC2::Subnet",
      "Properties": {
        "VpcId": {
          "Ref": "VPC"
        },
        "CidrBlock" : "10.0.6.0/24",
       "AvailabilityZone" : "us-east-1a"
      }
     
    },
   
    "PrivateMonitoring": {
      "Type": "AWS::EC2::Subnet",
      "Properties": {
        "VpcId": {
          "Ref": "VPC"
        },
       "CidrBlock" : "10.0.7.0/24",
       "AvailabilityZone" : "us-east-1a"
      }
      
    },
    
      "InternetGateway" : {
      "Type" : "AWS::EC2::InternetGateway",
      "Properties" : {
        "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ]
      }
    },

    "AttachGateway" : {
       "Type" : "AWS::EC2::VPCGatewayAttachment",
       "Properties" : {
         "VpcId" : { "Ref" : "VPC" },
         "InternetGatewayId" : { "Ref" : "InternetGateway" }
       }
    },
    
    "PublicRouteTable" : {
      "Type" : "AWS::EC2::RouteTable",
      "Properties" : {
        "VpcId" : {"Ref" : "VPC"},
        "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ]
      }
    },

    "Route" : {
      "Type" : "AWS::EC2::Route",
      "DependsOn" : "AttachGateway",
      "Properties" : {
        "RouteTableId" : { "Ref" : "PublicRouteTable" },
        "DestinationCidrBlock" : "0.0.0.0/0",
        "GatewayId" : { "Ref" : "InternetGateway" }
      }
    },

    "SubnetRouteTableAssociation" : {
      "Type" : "AWS::EC2::SubnetRouteTableAssociation",
      "Properties" : {
        "SubnetId" : { "Ref" : "PublicAuthentication" },
        "RouteTableId" : { "Ref" : "PublicRouteTable" }
      }
    },

    "NetworkAcl" : {
      "Type" : "AWS::EC2::NetworkAcl",
      "Properties" : {
        "VpcId" : {"Ref" : "VPC"},
        "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ]
      }
    },

    "InboundHTTPNetworkAclEntry" : {
      "Type" : "AWS::EC2::NetworkAclEntry",
      "Properties" : {
        "NetworkAclId" : {"Ref" : "NetworkAcl"},
        "RuleNumber" : "100",
        "Protocol" : "6",
        "RuleAction" : "allow",
        "Egress" : "false",
        "CidrBlock" : "0.0.0.0/0",
        "PortRange" : {"From" : "80", "To" : "80"}
      }
    },

    "InboundSSHNetworkAclEntry" : {
      "Type" : "AWS::EC2::NetworkAclEntry",
      "Properties" : {
        "NetworkAclId" : {"Ref" : "NetworkAcl"},
        "RuleNumber" : "101",
        "Protocol" : "6",
        "RuleAction" : "allow",
        "Egress" : "false",
        "CidrBlock" : "0.0.0.0/0",
        "PortRange" : {"From" : "22", "To" : "22"}
      }
    },

    "InboundResponsePortsNetworkAclEntry" : {
      "Type" : "AWS::EC2::NetworkAclEntry",
      "Properties" : {
        "NetworkAclId" : {"Ref" : "NetworkAcl"},
        "RuleNumber" : "102",
        "Protocol" : "6",
        "RuleAction" : "allow",
        "Egress" : "false",
        "CidrBlock" : "0.0.0.0/0",
        "PortRange" : {"From" : "1024", "To" : "65535"}
      }
    },

    "OutBoundHTTPNetworkAclEntry" : {
      "Type" : "AWS::EC2::NetworkAclEntry",
      "Properties" : {
        "NetworkAclId" : {"Ref" : "NetworkAcl"},
        "RuleNumber" : "100",
        "Protocol" : "6",
        "RuleAction" : "allow",
        "Egress" : "true",
        "CidrBlock" : "0.0.0.0/0",
        "PortRange" : {"From" : "80", "To" : "80"}
      }
    },

    "OutBoundHTTPSNetworkAclEntry" : {
      "Type" : "AWS::EC2::NetworkAclEntry",
      "Properties" : {
        "NetworkAclId" : {"Ref" : "NetworkAcl"},
        "RuleNumber" : "101",
        "Protocol" : "6",
        "RuleAction" : "allow",
        "Egress" : "true",
        "CidrBlock" : "0.0.0.0/0",
        "PortRange" : {"From" : "443", "To" : "443"}
      }
    },

    "OutBoundResponsePortsNetworkAclEntry" : {
      "Type" : "AWS::EC2::NetworkAclEntry",
      "Properties" : {
        "NetworkAclId" : {"Ref" : "NetworkAcl"},
        "RuleNumber" : "102",
        "Protocol" : "6",
        "RuleAction" : "allow",
        "Egress" : "true",
        "CidrBlock" : "0.0.0.0/0",
        "PortRange" : {"From" : "1024", "To" : "65535"}
      }
    },

    "SubnetNetworkAclAssociation" : {
      "Type" : "AWS::EC2::SubnetNetworkAclAssociation",
      "Properties" : {
        "SubnetId" : { "Ref" : "PublicAuthentication" },
        "NetworkAclId" : { "Ref" : "NetworkAcl" }
      }
    },

    
    "OpenVPNSFTP": {
      "Type": "AWS::EC2::Instance",
      "Properties": {
        
      "InstanceType" : {
        	"Ref" : "InstanceType"
         },
         
      "ImageId" : { "Fn::FindInMap" : [ "ImageMap", { "Ref" : "AWS::Region" }, "OpenVpnAMI" ]},
         
       "KeyName" : {
           "Ref" : "CFKeyName"
         },       
      "NetworkInterfaces": [ {
      "AssociatePublicIpAddress": "true",
      "DeviceIndex": "0",
      "GroupSet" : [ {"Ref" : "BasicSecurityGroup"} ],
     
      "SubnetId": { "Ref" : "PublicAuthentication" }
    } ]
    
      }
     
    },
    
    "NagiosOrKabana": {
      
      "Type": "AWS::EC2::Instance",
      
      "Properties": {
        
         "InstanceType" : {
        	"Ref" : "InstanceType"
         },
         
         "ImageId" : { "Fn::FindInMap" : [ "ImageMap", { "Ref" : "AWS::Region" }, "MonitoringAMI" ]},
         
         "KeyName" : {
           "Ref" : "KeyName"
         },         
      "NetworkInterfaces": [ {
      "AssociatePublicIpAddress": "false",
      "DeviceIndex": "0",
      "GroupSet" : [ {"Ref" : "BasicSecurityGroup"} ],
     
      "SubnetId": { "Ref" : "PrivateMonitoring" }
    } ]
        
      }
      
    },
    
    "ApacheNiFi": {
      "Type": "AWS::EC2::Instance",
      
      
      "Properties": {
          "ImageId" : { "Fn::FindInMap" : [ "ImageMap", { "Ref" : "AWS::Region" }, "NiFiAMI" ]},
          "InstanceType" : {
        	"Ref" : "InstanceType"
         },
         "KeyName" : {
           "Ref" : "KeyName"
         },        
       "NetworkInterfaces": [ {
      "AssociatePublicIpAddress": "false",
      "DeviceIndex": "0",
      "GroupSet" : [ {"Ref" : "BasicSecurityGroup"} ],
     
      "SubnetId": { "Ref" : "PrivateDataLanding" }
    } ]
        
      }
     
    },
    
     "Cloudera": {
      "Type": "AWS::EC2::Instance",
      "Properties": {
        
         "ImageId" : { "Fn::FindInMap" : [ "ImageMap", { "Ref" : "AWS::Region" }, "ClouderaAMI" ]},
          "InstanceType" : {
        	"Ref" : "ClouderaInstanceType"
         },
         "KeyName" : {
           "Ref" : "KeyName"
         },         
       "NetworkInterfaces": [ {
      "AssociatePublicIpAddress": "false",
      "DeviceIndex": "0",
     "GroupSet" : [ {"Ref" : "BasicSecurityGroup"} ],
      "SubnetId": { "Ref" : "PrivateDataLake" }
    } ]
        
      }
    
    },
    
    "OpenOrRevoR": {
      "Type": "AWS::EC2::Instance",
      "Properties": {
        
         "ImageId" : { "Fn::FindInMap" : [ "ImageMap", { "Ref" : "AWS::Region" }, "RstatAMI" ]},
          "InstanceType" : {
        	"Ref" : "InstanceType"
         },
         "KeyName" : {
           "Ref" : "KeyName"
         },       
       "NetworkInterfaces": [ {
      "AssociatePublicIpAddress": "false",
      "DeviceIndex": "0",
     "GroupSet" : [ {"Ref" : "BasicSecurityGroup"} ],
      "SubnetId": { "Ref" : "PrivateAnalytics" }
    } ]
       
      }
    
    },
    
    "QlikQlikviewTableau": {
      "Type": "AWS::EC2::Instance",
      "Properties": {
        
         "ImageId" : { "Fn::FindInMap" : [ "ImageMap", { "Ref" : "AWS::Region" }, "VisualAMI" ]},
          "InstanceType" : {
        	"Ref" : "InstanceType"
         },
         "KeyName" : {
           "Ref" : "KeyName"
         },       
       "NetworkInterfaces": [ {
      "AssociatePublicIpAddress": "false",
      "DeviceIndex": "0",
  	  "GroupSet" : [ {"Ref" : "BasicSecurityGroup"} ],
      "SubnetId": { "Ref" : "PrivateVisualization" }
    } ]
        
      }
    
    }
    
  }
}

Go ahead try and run this stack from your eclipse by right clicking on the page, click on “Run on AWS”, then click “Create stack”.

You can then hop on to your console to watch it in action as your VPC, subnets, and EC2 machines are being created.

Some tips before I conclude the article:

  • There is no delete stack command in Eclipse, so I deleted mine using AWS CLI with the following command:” C:\Program Files\Amazon\AWSCLI> aws cloudformation delete-stack –stack-name SunTest2 “
  • You can also delete the stack from the cloudformation web console. But it’s not recommended that you delete individual stack components manually. It also defeats the purpose of using CF.
  • If you need to update anything in your stack, you can do it in Eclipse with the “Update Stack” command.
  • Please restrict your Stack names to the following characters: [Az az 0-9], or your stack creation will fail.
  • Resource Properties and Parameters are case sensitive, so Default is not the same as default.

That’s it for now, and stay tuned for follow up articles that go on more details about CF stack creation, updating, and troubleshooting.