SQL Server Big Data Clusters CU8 release surfaces Encryption at Rest capabilities and more

by Contributed | Dec 7, 2020 | Technology

This article is contributed. See the original author and article here.

SQL Server Big Data Clusters (BDC) is a new capability brought to market as part of the SQL Server 2019 release. BDC extends SQL Server’s analytical capabilities beyond in-database processing of transactional and analytical workloads by uniting the SQL engine with Apache Spark and Apache Hadoop to create a single, secure and unified data platform. BDC is available exclusively to run on Linux containers, orchestrated by Kubernetes, and can be deployed in multiple-cloud providers or on-premises.

The availability of the latest cumulative update (CU8) for SQL Server 2019 BDC includes several fixes, optimizations and adds two main capabilities for SQL Server BDC:

• Encryption at Rest experiences for SQL Server and HDFS


• Oracle Proxy Authentication Support on Data Virtualization.

We’ve also surfaced a number of documentation articles to help you plan and deploy more advanced Active Directory enabled BDC scenarios on Azure Kubernetes Services (AKS).

This announcement blog highlights some of the major improvements, provides additional context to better understand the design behind these capabilities, and points you to relevant resources to learn more and get you started.

Encryption at Rest on SQL Server Big Data Clusters

Starting on SQL Server Big Data Clusters CU8, a comprehensive encryption at rest feature set is available to provide application level encryption to all data stored in the platform. This enables application level encryption capability allows administrators to securely encrypt data on both SQL Server and HDFS services of BDC to comply with desired enterprise-grade requirements. If your organization needs to enable encryption at rest, read all concepts and guidelines in our new documentation set for the feature.

Oracle Proxy Authentication support

As a modern data platform, BDC have long supported Oracle as one of its Data Virtualization sources. In order to support more enterprise-grade compliant scenarios, we’ve enabled Oracle Proxy Authentication support to provide fine grained access control. A proxy user connects to the Oracle database using its credentials and impersonates another user in the database. To learn how to enable this feature on your Oracle connection scenarios, read the updated documentation article here.

A proxy user can be configured to have limited access compared to the user being impersonated. For example, a proxy user can be allowed to connect using a specific database role of the user being impersonated. The identity of the user connecting to Oracle database through proxy user is preserved in the connection, even if multiple users are connecting using proxy authentication. This enables Oracle to enforce access control and to audit actions taken on behalf of the actual user.

Azure Data Studio Operational Notebooks for SQL Server BDC CU8

Along the Cumulative Update 8 we are also releasing an updated set of Operation Notebooks to Azure Data Studio. This update contains a new section of notebooks for managing Encryption at Rest, adds endpoint certificate rotation and include many fixes.

In order to get the latest version of the notebooks, either install the latest Azure Data Studio or use the “Add Remote Jupyter Book” feature as highlighted bellow.  Using the Command Palette (Shift + Cmd + P), find and execute the “Jupyter Books: Add Remote Jupyter Book” command.

Fill out the information as displayed in the screenshot bellow, the click the “Add” button at the botton of the pane.

Active Directory enabled SQL Server BDC on Azure Kubernetes Services (AKS)

SQL Server Big Data Clusters have always supported Active Directory (AD) enabled deployment mode for Identity and Access Management (IAM). Yet, IAM on Azure Kubernetes Service (AKS) has been challenging because of industry-standard protocols such as OAuth 2.0 and OpenID Connect which are widely supported by Microsoft identity platform yet are not supported on SQL Server.

We are pleased to announce that you can now deploy a big data cluster (BDC) in AD mode while deploying in Azure Kubernetes Service (AKS). Learn more about how to plan Active Directory integration for BDC on AKS and how to Deploy SQL Server Big Data Clusters in AD mode on Azure Kubernetes Services (AKS) hand-by-hand follow the links to our documentation page.

Private BDC cluster with further egress traffic restriction using user-defined Route table ( UDRs ) on Azure Kubernetes Service (AKS)

To complement the above platform security enhancements regarding deployment of BDC in Active Directory mode on AKS, we are pleased to announce that we added support for deploying BDC in Azure Kubernetes Service (AKS) private cluster for both AD and non-AD clusters, it makes sure the network traffic between API server and node pools remains on the private network only. The control plane or API server has internal IP addresses in an AKS private cluster. You can find how to Deploy BDC in Azure Kubernetes Service (AKS) private cluster step-by-step here.  

Leverage this configuration will help our customer restrict use of public IP addresses in enterprise networking environment. Furthermore, to restrict the additional hops are required for egress traffic, we also provide guidance on restricting egress traffic of Big Data Clusters (BDC) clusters in Azure Kubernetes Service (AKS) private cluster, please refer to this article on our documentation page. To know more about how to manage an Azure Kubernetes Service (AKS) private cluster with SQL Server big data clusters (BDC) deployed in Azure in here.

SQL Server BDC team hears your feedback

If you would like to help make BDC an even better analytics platform, please share any recommendations or report issues through our feedback page. SQL Server engineering team is thoroughly going through the reported suggestions. They are valuable input for us, that is being considered when planning and prioritizing the next set of improvements. We are committed to ensuring that SQL Server enhancements are based on customer experiences, so we build robust solutions that meet real production requirements in terms of functionality, security, scalability, and performance.

Ready to learn more?

With SQL Server 2019 CU8 updates, BDC continues to simplify the security, deployment, and management of your key data workloads. 

Check out the SQL Server CU8 release notes for BDC to learn more about all the improvements available with the latest update. For a technical deep-dive on Big Data Clusters, read the documentation and visit our GitHub repository.

To get started using Encryption at Rest on BDC, follow the instructions on our documentation page. 

What's New: 80 out of the box hunting queries!

by Contributed | Dec 7, 2020 | Technology

This article is contributed. See the original author and article here.

This installment is part of a broader series to keep you up to date with the latest features in Azure Sentinel. The installments will be bite-sized to enable you to easily digest the new content.

This post was written in collaboration with @JulianGonzalez.

Introduction:

Threat hunting is a powerful way for the SOC to reduce organizational risk, but it’s commonly portrayed and seen as a complex and mysterious art form for deep experts only, which can be counterproductive. Sophisticated cybercriminals burrow their way into network caverns, avoiding detection for weeks or even months, as they gather information and escalate privileges. If you wait until these advanced persistent threats (APT) become visible, it can be costly and time-consuming to address. In today’s cybersecurity landscape, SOC analysts need controls and integrated toolsets to search, filter, and pivot through their telemetry to derive relevant insights faster.

Taken into account, we are delighted to announce that we have introduced 80 new hunting queries that can empower your SOC analyst to reduce the gaps in your current detection coverage and ignite new hunting leads. 

Hunting queries included:

The following queries are designed to help you find suspicious activity in your environment, and whilst many are likely to return legitimate activity as well as potentially malicious activity, they can be useful in guiding your hunting. If after running these queries you are confident with the results you could consider converting them to analytics or add hunting results to existing or new incidents. 

The figure below illustrates the diversification and threat hunting coverage the new hunting queries provide across the MITRE ATT&CK framework matrix:

Feel free to expand the spoiler below to view the new list of out the box hunting queries and the MITRE ATT&CK tactics that are covered:

How to access the new hunting queries:

All of the queries are available via the Hunting UI page. For this example, I will demonstrate how to filter the new Microsoft Teams hunting queries. To learn more, see article on the new Microsoft Teams data connector (Preview).

Navigate to the Azure Sentinel console, and select “Hunting” under the Threat Management area

Next, use the filter pills to filter the data source (TeamsData) for the hunting query. In this example, we are highlighting hunting queries that satisfy several use cases for Microsoft Teams:

Example: Previously unseen bot or application added to Microsoft Teams

As an example, this hunting query helps identify new, and potentially unapproved applications or bots being added to Microsoft Teams. This query requires you to have Microsoft Teams data ingesting into Azure Sentinel. Ensure you have the relevant data collectors configured for the target hunting queries. Visit here (Connect data sources to Azure Sentinel | Microsoft Docs) for more information.

// If you have more than 14 days worth of Teams data change this value
let data_date = 14d;
let historical_bots = (
TeamsData
| where CreationTime > ago(data_date)
| where isnotempty(AddOnName)
| project AddOnName);
TeamsData
| where CreationTime > ago(1d)
// Look for add-ins we have never seen before
| where AddOnName in (historical_bots)
// Uncomment the following line to map query entities is you plan to use this as a detection query
//| extend timestamp = CreationTime, AccountCustomEntity = UserId

Get started today!

Happy Hunting! 

SharePoint 2010 Workflows to O365

by Contributed | Dec 7, 2020 | Technology

This article is contributed. See the original author and article here.

If you haven’t heard already, SharePoint 2010 Workflows will be completely deprecated in O365 by November 2020: SharePoint 2010 Workflow Retirement. This How-To will walk you through the process of Exporting and Importing your workflows from one tenant to another. If you find yourself in a situation where:  

• You currently do not have an O365 Subscription but have a demo tenant or possibly a CVR tenant 

• You will be migrating to O365 after or relatively close to the retirement date 


• You have workflows that are important business process, and you cannot afford for them to down while you recreate them from scratch 

Follow me through this How-To to give you ideas on how you can get a leg up on Workflow Migrations from SharePoint 2010 or SharePoint 2013 to O365. 

*Currently this solution is available in GCC and GCC High tenants. 

Initial SharePoint List 

I am going to start by creating a simple list that keeps track of issues. This list will help us keep track of the SharePoint 2010 Workflow issue. I am not going to spend any time talking about the details of the list because it will take away from the article, however, if you would like to know more about how to create a list and what they are all about you can go here.  

Let me set the stage to help you better understand how all this will work.  

• Created a new list in Microsoft TEAMS 


• I used the “Issue Tracker” template and called it “O365 Migration” 

• I added a new item and assigned the “Issue” to myself 

My “O365 Migration” list looks like this: 

Microsoft Teams

Create Data Connections 

There is more than one way to get to Power Automate. For our purpose, we will navigate to Power Automate using the following URL which points to a GCC Tenant: Power Automate in GCC. For a commercial tenant you will want to navigate to: Power Automate Commercial 

We need to navigate to Power Automate and login. 

The first thing we need to do is connect to our data. Our data is the “O365 Migration” list in SharePoint Online (SPO). On the right-hand menu click on “Data” and then “Connections”. For this How-To, we are going to assume that you don’t have any connections created yet (if you do have a connection already created for your SPO then you can skip the following few steps:

• In the Ribbon click on “New connection” 


• Select “SharePoint” 

• “Connect directly (cloud services)”. 


• Click on “Create” 


• You will be asked to authenticate 


• Your connection to SharePoint should now be listed 

Creating A New Workflow  

Once we have our data connection to SharePoint we need to click on “Create” on the left-hand menu and then on “Automated Flow”. This will bring up a pop-up window which will allow you to select the type of workflow you want to create. In our scenario, we want to send an email to the user(s) who gets assigned an issue, therefore, we will select “When an item is created or modified”. 

 New Flow

Adding Workflow Steps 

Your workflow canvas should consist of only one Step: “When an item is created or modified”.  To configure this step, click on the pull-down arrow from the “Site Addresses” row. Because we created the data connection on a previous step, your site should be listed as an option. Select the List Name to complete configuring this step.

To complete our workflow, we need to be able to send an email to the user(s) who gets assigned the task. Click on the “New Step” button and filter by O365 to quickly find the “Send an email (V2)” step. 

• Once the step is added, click on the “To” field (textbox) to display the “Dynamic Content” pane to the right. In the Dynamic Content options, search for “Assigned to Email” under the “When an item is created or modified” header. This field has the email of the user that we want to send the email to. 


• Moving over to the “Subject” field, click on the textbox to display the “Dynamic Content” pane. Under the “When and item is created or modified” header select “Title” to display the title of the task as the “Subject” of the email.


• In the next few steps, you will follow the steps above to get the “Dynamic Content” pane to display. In the “Body” field select “Issue Description”. 


• In the “From (Send as)” field select the “Issue logged by Email”. This will let the user know who assigned the task to them. 


• Finally, the “Send as email (V2)” should look very similar to the image below: 

Send Email

Flow Checker 

This is the extent of our workflow. Like I said, nothing big, just a simple workflow to start testing our idea with. Now we need to make sure that our workflow follows Microsoft’s flow best practices by clicking on “Flow Checker” on the upper right hand corner of your window. If you are lucky, you will have the same results I did. 

Test Flow 

Now we get to test the workflow with actual data. Click on “Test” on the top right-hand corner of your screen to start testing your workflow. If this is your first time testing your workflow, the only option that will be available will be the first one: “I’ll perform the trigger action”. Once you have tested the flow at least once, the other option will be available to you. 

Test Flow

Because we have to “trigger” the workflow ourselves we must navigate to TEAMS immediately after clicking on the “Test” button. In TEAMS we need to create a new list item. Make sure that you have assigned the task to a valid email and have entered a valid email to the assigned to field. Once you have created the new item, go back to your workflow, and see that it was successful.  

Test Results

The email that the user will receive should resemble the following: 

Export Workflow 

On the left navigation pane, select “My flows”, then click on the workflow you just created. You should see something like the image below. 

Click on “Export” on the top of the image, then “Package (zip)”. 

Export Package 

In the “Export package” page fill out the “Name”, “Environment”, and the “Description” details under the “Page details” header. 

Export Package

In the “Review Package Content” section, pay close attention to “Import Setup”. We are going to be moving the workflow from one tenant to another, and therefore, we cannot “Upgrade” the workflow. To change this setting, click on the ‘Wrench” icon to the right or simply click on “Update”. When the window displays, select the “Create as new” option.  

Another section we need to make sure we modify is the “Related resources” section. It is important to make sure that the setting for this feature is set to “Select during import” for all of the connections that your workflow is using. We are using two, a SharePoint connection, and an Office 365 connection to be able to send out the email. Both of our settings are set correctly. 

Related Resources

Finally, once it all looks good and everything is filled in hit the “Export” button at the button to save the file locally. Make a note of the where the file, the package, gets saved to because we will need the file on the next steps. 

Import Workflow 

We are almost done with this How-To. We now need to import our flow to our new tenant but before we do that, we need to make sure of a few things: 

1. We need to make sure that the list we are going to use the workflow with exist


2. That the connections to SharePoint is available in our new tenant 


3. And finally, that the connection to O365 Outlook is available in our new tenant 

For simplification, I have created the same type of list, Issue Tracker, using TEAMS, on a different tenant making sure the fields are the same and the name of the list is the same. 

To verify that the Data Connections have been made, simply click on “Data” and then on “Connections” on the left side menu. If you see the following connections listed, you have nothing else to do. You can move on to the next step. 

 If those connections are not listed, you will have to create them. We walked through the steps above on how to create the connections here.  

Upload Zip File 

Navigate and login to your other tenant. On the left navigation pane, click on “My flows” and this time click on “Import” on the ribbon. Click on the blue “Upload” button and navigate to the location of the zip file that you exported in the previous step. Select the zip file, click on open. The “Upload” button will change to “Uploading” to indicate the file is being uploaded. 

Upload Zip File

 Import Package

After the file gets imported your screen should look similar to this one: 

Import Package Screen

The only changes we will have to modify here, and they are very important, are the two errors showing under “Related resources”. Those errors mean that the Data Connections that it is expecting to see don’t exist. We must tell the import process of the new Data Connections we have created.  

Click on “Select during import” for the “SharePoint Connection” and select the connection that connects to SharePoint. Then click on “Select during import” to choose the Office 365 connection. 

Import Package

Finally, click on the “Import” button at the bottom of the screen. After the system finishes trying to import the workflow you will see an error: 

Import Failed

The error is self-explanatory: one of the resources did not migrate. To fix this issue and complete the import simply click on “Save as a new flow”. Once it is done creating the import as a new workflow you will see the following screen: 

Flow Errors

This screen tells you one very important thing: Although the flow doesn’t work in its current state, it is, however, imported. The import did what we hoped it would do and that is to move an existing workflow from one tenant to another. 

To fix the warnings that are displayed click on each step and point to the correct data connection. 

Connections

The SharePoint data connection will have to be configured to point to the new SharePoint Site and List. Without this step, the flow will remain pointing to the old URL and not work as expected. 

The only thing left to do is test, test, and test again. 

I hope this was helpful for you. Please comment on this article if it is! If you need additional support or information reach out Power Automate Support by going to their site.  

Additional Resources 

• Microsoft Power Automate Community 


• Learn Power Automate 


• Power Automate for Government 


• Power Automate for Commercial