Azure Data Factory (ADF) has introduced a feature called Managed Virtual Network (MVN) to connect Azure PaaS resources securely and privately from on-premises without opening corporate boundary firewall to Azure PaaS public IPs.

To run an on-premises backend job using PaaS services such as Azure Storage, Azure SQL, a corporate boundary firewall may need to be opened to Azure PaaS service public IP addresses. If you receive pushback from InfoSec to punch a hole in the firewall, you are not alone. I understand “the why” part of that pushback. Luckily, no more convincing is needed!

Managed Virtual Network (MVN)

In the picture below, MVN contains an integration runtime. This integration runtime can have multiple ADF private managed endpoints. These ADF private endpoints establish private links with other Azure resources such as Azure Storage and Azure SQL database.

ADF Managed Virtual Network

The advantages of using MVN are:

  1. MVN eliminates the need to have a deep understanding of Azure networking and upfront network planning
  2. MVN takes care of automatic DNS entry registration
  3. MVN is secure and private. Public internet access to azure PaaS services is disabled
  4. Corporate firewall rules will be much cleaner. No more clutter from punching holes in on-premises corporate boundary firewall to allow Azure PaaS Services
  5. Built-in data exfiltration protection

How to enable MVN, private link & private endpoints in ADF?
Let’s see how to enable MVN with an Azure PaaS implementation. Often, we come across a typical business scenario where we want to transform a broad set of flat, semi-structured CSV files into a schematized and structured format ready for further querying.

Azure Data Factory PaaS

As shown above, Azure Data Factory is a PaaS orchestration engine to convert from flat file data into SQL data. The CSV data is stored in Azure Data Lake and is converted from the raw formats into binary formats and stored in Azure Synapse SQL Data Warehouse. Ingested CSV data is transformed and stored in a columnar format and will be more performant for querying.

We will create an Azure Data Lake Storage, Azure SQL Server, SQL Database, and ADF. These PaaS resources have been created in the same Azure region. For step-by-step instructions on creating these unique PaaS resources, refer to the Credits section.

  1. In ADF, go to Manage connections and select Integration runtimes. Create an Azure Integration Runtime (IR). Enable Virtual Network Configuration, as shown below. This step will ensure the data integration process is isolated and secure.
    Create an Azure Integration
  2. After creating the IR, in the bottom left corner, select managed private endpoints. This helps to connect to Azure Data Lake from the ADF securely. It’s a two-step process; select “create a new managed private endpoint” and then selected Data Lake Storage. You will get a prompt to go to the Azure portal. In the portal, select Data Lake Storage, approve the newly created private endpoint as shown below:
    Approve Newly Created Private End Point
  3. Similarly, as above, create a private endpoint in ADF for SQL database and approve in the portal.
  4. The last step is to create a linked service in ADF for Azure Data Lake Storage and Azure SQL Database.
    Create Linked Service in ADF
  5. Finally, configure and run the data factory pipeline to transfer flat file data from Azure Data Lake to Azure SQL database.Configure and Run Data Factory Pipeline

Conclusion

In conclusion, Azure customers can avail the power and flexibility of a PaaS solution with no internet exposure. As demonstrated above, Azure Private Link provides all back-office jobs to run in PaaS privately and securely.

Credits:

  1. Create an Azure Data Storage
  2. Create an Azure SQL Db
  3. Create an Azure Data Factory

Committed to Protecting Your Data

SOC 2 CertifiedAIS is happy to announce our most recent SOC 2 Type II audit completion, showing our dedication and commitment to security and to delivering high-quality services through internal controls and processes. At AIS, we are passionate about the work we do and the peace of mind we provide our clients. Companies are moving fast to the cloud and expanding capabilities by the minute. We plan to move just as quickly, delivering innovative and transformative solutions without risking security, compliance, or quality.

A SOC 2 audit provides an independent, third-party validation that our information security practices meet industry standards stipulated by the AICPA. During the audit, a service organization’s non-financial reporting controls related to security, availability, processing integrity, confidentiality, and privacy of a system are tested. The SOC 2 report delivered verifies our suitability of the design and operating effectiveness controls to meet these standards.

Our team has worked diligently to support the qualifications for SOC 2 to ensure the protection of our clients. Our dedication to compliance, security, and governance allows us to support our large enterprise clients in the Federal Government, Financial Services, Healthcare, and other highly-regulated industries. We understand data security’s sensitivity and importance. When threats arrive, AIS is ready to act. Our detailed processes allow us to indicate threats to your organization, alert you when something happens to move quickly, and prevent data loss or compromise.

LET'S WORK TOGETHER
Interested in learning more? Contact AIS today.

In this blog post, I’m going to discuss the new feature currently in public preview – “Connection References (Preview).” This feature was released to maintain a level of abstraction between flows and the connections they are using. Connection references come in handy when a user wants to move across environments, update the flow definitions, or automate the deployment using pipelines for secure and healthy ALM.

A connection reference is a component of the solution that contains information about a connector. Both actions within a Power Automate flow and Canvas Application bind to a connection reference. You can import your connection reference into a target environment with no further configuration needed after the import is successful. To change a specific connection associated with a Power Automate Flow or Canvas Application, you can edit the connection reference component within the solution. Firstly, we need to understand how flows and connections are associated with each other. Every action inside a flow is bound to a connection that it uses for the “Execution.” Users need to rebind every operation for the connection after import across the environments (Overriding all the customizations). Below is the image reference for the same.

Rebinding every action to a connection

If we look at the larger view, it seems to be like a scenario defined by the below image.

Larger view at connection reference

When we move flows (part of the solution) across the environments, all links between the triggers, actions, and the connection are severed. We need to rebind all the links as soon as the import is successful in the target environment. But in the case of the “Connection Reference,” it provides the abstraction layer between the actions and the connections. As soon as we move across, the solutions link between the actions & connection references severed, but connection reference and connection remain intact. So, after the import is successful, it will automatically make links of the actions with the connection reference of the target environment, thus avoiding all the rebinding & reconfiguration.

Mostly, users get confused between CDS connectors’ current environment and using connection reference for the CDS connector. Are they the same, or are they different? A CDS current environment connector provides access to the org-based database on Microsoft Common Data Service in the current environment. We can use this connector to connect to the database and perform various operations on data using the connection to the backend provided by this connector. Coming onto “Connection References,” they act as a variable that contains information about the connectors. It includes the name of the connector we are using as well as the connection to that connector. So, both of them are different concepts.

Demo describing the use of the “Connection References (Preview)”

  1. Go inside your sample solution. Create a connection reference by clicking on “New”. Fill out the required details.
    Demo Step 1 Step 1 New Connection
  2. Inside the solution, there is a flow using the connection of any user as a direct connection which was prior connection reference was introduced. And have created a new flow using the connection reference created in step – 1.
    Without Reference

    Without a Connection Reference
    Demo Step 4

    With Connection Reference 

  3. Import the solution to the target environment. During the import, it will ask for the connection reference to be used in the target environment. Select one from the list or create a new one at the time of the import and select import.
    Demo Step 3 Import Solution
  4. Now make some changes to the solution in Dev Env. After you make certain changes and want to re-import the solution again to target env by overriding the customizations. You will observe the impacts on the two different flow in the following way.
    Demo Step 4 With

Without a Connection Reference

Demo Step 4 With

With a Connection Reference

The flow with the direct reference to connections users needs to rebind all the connections of every action. In contrast, inflow using the “Connection Reference,” all connections are set up automatically. There is not to reconfigure the flow after the import. If you want to update the connection reference (if your reference account has an issue) in the target environment and your solution is managed in the target environment.

Follow the below steps:
Go to: Default > Filter by Connection Reference (Preview) > Select your connection reference> Update the selected Connection reference > Make a new connection > Save And Close.

Limitation:

While connection references are in preview, one connection reference can be used within a maximum of 16 flows. If the same connection needs to be used in more than 16 flows, then create another connection reference with a connection to the same connector. There is no limit to the number of actions in each flow associated with the connection reference. Thank you for reading!

Have you spent a lot of time getting comfortable using Team Foundation Server (TFS) or Azure Pipelines only to be switched to GitLab CI/CD for a project? That’s what happened to me about four months ago. Since then, I’ve been learning the differences between the tools. There are several differences between Azure Pipelines and GitLab CI/CD that I’ve experienced. This post aims to be a primer for you to understand these differences and save you a lot of research time.

Classic Pipelines vs YAML

I worked with TFS’/Azure Pipelines’ Classic Editor the most before this project. While it’s a great visual designer for automating builds, they aren’t very portable from project-to-project or environment-to-environment because the exported JSON files reference instances of other resources like Service Connections by ID, not name. When importing these JSON files into another TFS or Azure Pipelines instance, changes need to be made to update affected references. This is where YAML, which stands for “YAML Ain’t Markup Language,” comes in. YAML pipelines are the future for both Azure Pipelines and GitLab CI/CD. YAML references the Service Connection by name, making it easier to use the pipeline in other environments if the connection name remains the same.

I didn’t realize before diving into YAML for GitLab because it’s not a language but more of a file format. I had expected the little Azure Pipelines YAML I had written would translate almost perfectly to GitLab. However, GitLab doesn’t use a lot of the syntax that Azure Pipelines does. I’ve listed some of the differences I’ve become aware of, but this is likely just a partial list.

Azure Pipeline and Description

For more details, here is the YAML reference for GitLab and Azure.

Integration with Azure

Azure Pipelines offers Service Connections to connect to remote services like Azure, Docker, or GitHub.
GitLab doesn’t have an equivalent offering. Making those connections must be done in Jobs. One of the concerns is that you would have to keep the connection credentials in environment variables. When doing this, it’s important to remember to mask sensitive variables like passwords to not appear in job logs. This also means you can’t use Azure Key Vault Secrets as pipeline variables, either, which is something my previous projects used heavily.

Runners

While project-specific, the GitLab runners that were made available don’t have all the tools needed for my project. Another team that is developing the runners has provided some basic images that don’t include tools like the Azure or JFrog CLIs, which are required for the environment. This is likely to be remedied, but it’s caused a need to learn how to install it. This isn’t an issue with more open solutions. Still, my project will be isolated from the web in the staging and production environments, making it challenging to use tools like Chocolatey, Nuget, or NPM to install packages from the web. This is still something the other team is working to resolve. Still, the only resolution I see is maintaining their own repository of packages in something like Artifactory and having images or scripts pull from there.

I’m far more comfortable with YAML because of this project. While GitLab is powerful and has received a few excellent features since I began the project, the creature comforts I’m used to with Azure Pipelines (notably the Service Connections) require additional work that seems unnecessary.

A Place to Feel Welcome

2020 Best Place to Work AIS

“AIS has made me feel welcome and appreciated from day one. The employees at the Ohio office are friendly, approachable, and super smart. AIS has gone the extra mile to make sure the office is a safe place to work during the Coronavirus pandemic.” says Nikkala Thomson, a recent hire.

Applied Information Sciences (AIS) was recently named an honoree in the “Best Places to Work” awards by the Dayton Business Journal. After a self-nomination, Quantum Research surveys employees of nominated companies on staff engagement, team effectiveness, manager effectiveness, and other workplace best practices. The results were tallied, and companies ranked from highest score to lowest.

AIS strives to be a friendly, professional, and innovative place to work. Employees are empowered by leadership to manage workloads and research solutions with autonomy. The team works hard to have a healthy team atmosphere – bonding via moral events such as lunch and learns, escape rooms, happy hours, family-friendly game nights, and road trips to Top Golf.

As an ESOP, every employee has ownership in the success of AIS. Employees are regularly asked for ideas on brand building, recruitment, training, and morale events. You can see the bond through events like the annual open house, where there are panel-based discussions for relevant community topics and speed interviews for individuals interested in joining the company. Most of the office contributes to the planning and execution of the open house.

AIS was able to stand out and win the honoree award due to these activities and the people-first mindset. Corporate benefits include technology and wellness reimbursements, on the spot bonuses, and charitable giving matches that distinguish AIS as one of the top places to work.

WE'RE HIRING
If you’re committed to curiosity, passionate about emerging technology, and excited to find innovative solutions for the biggest tech challenges – AIS is the place for you.

When building and managing an Azure environment, Microsoft maintains control of the network traffic as a core operations responsibility. The primary Azure platform resource to implement network traffic control is the Network Security Group (NSG). A Network Security Group allows you to define security rules, like firewall rules, that control traffic by specifying allowed and denied sources, destinations, ports, and protocols. Like all Azure resources, there are multiple options to manage NSGs, including the standard Azure Management tools: The Azure Portal, Scripts (PowerShell and CLI), APIs, and Azure Resource Manager (ARM) templates.

Managing NSG security rules using an ARM template can be challenging. Each security rule is defined using a large chunk of JSON, and many security rules may be required. The verbose JSON structure makes it difficult to see many rules at once, visualize changes from version to version, and encourage team members to revert to the Azure Portal to view and edit rules. Why use the Azure Portal? It turns out the portal’s grid format for NSG Security Rules was comfortable for quickly viewing multiple rules and for making minor edits to individual rules.

Since the portal’s grid view was comfortable the CSV file format seemed like the right idea based on its similarity to a grid. CSV files have a few pros:

  • Good viewers and editors including Excel and VS Code.
  • One vertically compact line for each security rule.
  • A vertically compact view that makes it easier to visually scan rules and to see the changes that are made from version to version when viewing differences.
  • Anyone who can edit a CSV can edit the NSG Security Rules allowing a larger group of security rule editors.

NSG in JSON format

This is a simple example of the NSG Security Rule JSON. A rule like this can get much larger vertically when numerous ports and address prefixes are defined:

{
          "name": "I-L-All_HttpHttps-UI_Layer-T",
          "description": "Allow HTTP + HTTPS traffic inbound.",
          "priority": 1110,
          "access": "Allow",
          "direction": "Inbound",
          "protocol": "Tcp",
          "sourceAddressPrefix": "",
          "sourceAddressPrefixes": [
            "AzureLoadBalancer"
          ],
          "sourceApplicationSecurityGroups": null,
          "sourcePortRange": "*",
          "sourcePortRanges": null,
          "destinationAddressPrefix": "*",
          "destinationAddressPrefixes": null,
          "destinationApplicationSecurityGroups": null,
          "destinationPortRange": "",
          "destinationPortRanges": [
            "80",
            "443"
          ]
        }

NSG in CSV Format

Excel

Example CSV Excel

Example CSV

Converting Between CSV and JSON

The transition from CSV to JSON and from JSON back to CSV must be repeatable and simple. In this scenario, PowerShell scripts manage this process: Convert-NsgCsvToJson.ps1 and Convert-NsgJsonToCsv.ps1.

The Convert-NsgCsvToJson.ps1 script is straightforward and does the following:

  1. Read the source CSV file.
  2. Read the destination JSON file.
  3. Split multi-value fields into an array based on the parameter: CsvArraySeparator. The default is the pipe character ‘|’. For fields like source and destination port ranges, this collapses multiple values into a single CSV field.
  4. Structure of the CSV format data into objects that match the ARM Template NSG Security Rule JSON structure.
  5. Use a JsonFileType parameter to determine where in the destination JSON structure to place the security rules array. This allows placement of the security rules array into a parameter file, template file, or into an empty JSON file.

A New Workflow

With PowerShell scripts, the new workflow for NSGs is:

  1. Create and edit NSG Security Rules in a CSV file – usually using Excel.
  2. Visually scan the CSV looking for obvious anomalies (Excel makes it easy to see when one rule stands out from the others and as an example, a value is in the wrong column).
  3. Execute the script: Convert-NsgCsvToJson.ps1 to convert the rules to the Json Structure and update the destination JSON file.
  4. Deploy the ARM Template and updated parameters file to a dev/test environment using standard deployment approaches such as the Azure CLI. This will fully validate the NSG Json prior to production deployment.
  5. Deploy to Production during a planned change window.

From JSONback to CSV

At times, a team member may change the portal, for example, during troubleshooting. Once an update is made in the portal, transfer Azure changes back to the code that defines this infrastructure. The CSV files are the canonical source, so there needs to be a process to return to CSV from JSON.

  1. To retrieve the NSG Security Rules from the portal execute a CLI command to retrieve NSG security rules and export them to a JSON File.
    az network nsg rule list --nsg-name subnet-01-nsg --resource-group net-rgp-01 | set-content subnet-01-export.json
  2. Execute the Convert-NsgJsonToCsv.ps1 script using the generated file as the input and the corresponding CSV file as the output.

Constraints

The environment these scripts were built for may not match your own. This environment includes several constraints:

  • Azure Resource Manager Templates are the language for Azure Infrastructure as Code.
  • Manual steps are required: automated build and release pipelines are not yet available.
  • There is no guarantee that NSG security rules will not be modified in the Azure Portal, so a mechanism is required to synchronize the code with the environment.

Future Improvements

This solution represented a significant improvement for this team instead of managing NSG security rules directly in the JSON format. As with every answer, there are ideas on how to improve. Here are a few that have come to mind:

  • Use CI/CD tools such as GitHub Actions to automatically execute the Convert-NsgJsonToCsv.ps1 script when an NSG CSV file is committed.
  • Implement a release pipeline so that modified NSG Csv files trigger the conversion script, wait for approval to deploy, and deploy the ARM Template to the dev/test environment.
  • Add Pester tests to the PowerShell scripts.
  • Try this approach with other IaC languages such as Terraform.

Additional Notes

  • The example template has been dramatically simplified.
    • The production template also configures NSG Diagnostic Settings and NSG Flow Logs.
    • The production template builds all resource names based on several segments defined in a naming convention.
  • There are NSG Security Rules that are considered baseline rules that should be applied to every NSG. These rules are managed in a CSV file and placed in an array in the base template and not repeated in each parameter file. An example of this is a rule that allows all servers to contact the organization’s DNS servers.
  • Application Security Groups are used to group servers in the local VNET so that NSG Security Rules do not need to include IP addresses for servers contained in the VNET. The only IP Address Prefixes specified directly in our rules are from outside the current VNET. As with the NSGs, this template defines ASGs in the template (baseline) and parameters file (local) combined and created during template deployment. Only the unique portion of the name is used to define the group, and to specify rules. The remainder of the term is built during deployment. ASGs in Azure are currently only valid for the VNET where they are created, and only one ASG may be specified per security rule. This script creates all the ASGs defined in the template and parameters file.

Code

The code for these scripts including the conversion scripts and a sample ARM Template, ARM Template Parameters files, and matching NSG Security Rule CSV files is available on GitHub: https://github.com/matthew-dupre/azure-arm-nsg-via-csv

Code Scripts