DISABLE_PARAMETER_SNIFFING

by Contributed | May 31, 2021 | Technology

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

According to MSDN article, ‘DISABLE_PARAMETER_SNIFFING’ instructs Query Optimizer to use average data distribution while compiling a query with one or more parameters. This instruction makes the query plan independent on the parameter value that was first used when the query was compiled. This hint name is equivalent to trace flag 4136 or Database Scoped Configuration setting PARAMETER_SNIFFING = OFF.

Looks like it’s a pretty good hint, However, it doesn’t means you can resolve all parameter sniffing issue by using this query hint.

Actually,  the sentence ‘Query Optimizer to use average data distribution while compiling a query with one or more parameters’ is not 100% correct. It really depends on what symbol you used in the where clause.

‘DISABLE_PARAMETER_SNIFFING’ is a replacement of variable, these two have exactly same effect. If you are not familiar with selectivity of variable, please review  my post Selectivity and Estimated Row: Variable – Microsoft Tech Community

I’m going to use AdventureWorks 2019 in this post.

——————–Please run this script—————

use AdventureWorks2019

go

IF exists(select 1 from sys.tables where name=’SalesOrderDetail’ and schema_id=schema_id(‘dbo’))

      drop table SalesOrderDetail

go

select * into SalesOrderDetail from [Sales].[SalesOrderDetail]

go

create statistics iProductID ON SalesOrderDetail(productid) with fullscan

Go

dbcc traceon(3604,2363)

——————–Please run this script—————

For example, following two stored procedure returns exactly same Estimated rows 456.

create proc ptest1

@pid int

as

select * from SalesOrderDetail where productid>=@pid option(use hint(‘DISABLE_PARAMETER_SNIFFING’))

go

create proc ptest2

@pid int

as

declare @pid1 int =@pid

select * from SalesOrderDetail where productid=@pid1

Go

Trace flag 2363 displays more detail about the selectivity.

———————————-trace flag 2363 output———————————-

Begin selectivity computation

Input tree:

  LogOp_Select

      CStCollBaseTable(ID=1, CARD=121317 TBL: Sales.SalesOrderDetail)

      ScaOp_Comp x_cmpEq

          ScaOp_Identifier QCOL: [AdventureWorks2019].[Sales].[SalesOrderDetail].ProductID

          ScaOp_Identifier COL: @pid

Plan for computation:

  CSelCalcHistogramComparison(POINT PREDICATE)

Loaded histogram for column QCOL: [AdventureWorks2019].[Sales].[SalesOrderDetail].ProductID from stats with id 3

Selectivity: 0.0037594

Stats collection generated:

  CStCollFilter(ID=2, CARD=456.079)

      CStCollBaseTable(ID=1, CARD=121317 TBL: Sales.SalesOrderDetail)

End selectivity computation

———————————-trace flag 2363 output———————————-

 121317*0.0037594=456

Please review Selectivity and Estimated Row: Variable – Microsoft Tech Community for other inequations.

VPN access to Azure from macOS with Azure Active Directory authentication

by Contributed | May 31, 2021 | Technology

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

Whether you are using Microsoft Azure for development, for production workloads, or for both, it’s important to consider the security of the connections to those cloud systems. Virtual private networks are often used to encrypt traffic between a device and Azure using a private tunnel over the public internet – especially for information and systems you don’t want to be made available to the public or open to the possibility of being captured and read. At scale, a site-to-site VPN can be configured to the internet router used by an office (or home office) so the VPN connection can be used by all the devices on that network. But you can also set up a point to site VPN between just one device and Azure – especially useful for laptops and staff who travel or work from home.

Establishing a VPN connection requires some sort of authentication method – commonly a certificate or a username & password. Microsoft Azure point-to-site connections support Azure certificate authentication, authentication with a RADIUS server, or Azure Active Directory authentication with the OpenVPN(r) protocol. Active Directory authentication was limited to only Windows clients, but we’ve just announced a public preview of this capability for macOS.

This means that your macOS device will be able to establish a point-to-site VPN connection to Microsoft Azure using authentication with your Azure Active Directory credentials. And because you’re using native Azure AD authentication, the additional security features of user-based risk policies, conditional access and multi-factor authentication can now also apply from your Mac device when connecting to the VPN. So, for example, you could ensure that macOS VPN connections are only established from allowed locations, or that other locations force a multi-factor-authentication challenge. Note: while authenticating your VPN with Azure Active Directory does not require any additional Azure AD licensing, some of the premium features (like conditional access) do have Azure AD licensing requirements – check the linked feature documentation for details.

Remember: Public preview features are subject to change and don’t come with a Service Level Agreement. Learn more at Choose the right Azure services by examining SLAs and service lifecycle. 

Components of a Microsoft Azure Point-to-Site VPN from macOS with Azure Active Directory authentication

A point-to-site VPN connection from macOS to Microsoft Azure requires:

• An Azure Active Directory tenant


• An Azure virtual network


• An Azure virtual network gateway, with the correct point-to-site configuration.


• A macOS device with a correctly configured Azure VPN Client application.

Network architecture showing a point to site VPN from macOS to Microsoft Azure

The detailed steps

Detailed documentation for each of steps is provided at Microsoft Docs and is updated should the product feature or steps change, but I’ll link to each step in the process here.  To implement a VPN client for point-to-site OpenVPN protocol connections from macOS (preview):

Configure an Azure Active Directory tenant.  
Register the Azure VPN “Enterprise application” 
Create a virtual network 
Create a virtual network gateway 
Note: You can use an existing virtual network or virtual network gateway if you already have one.

Configure the virtual network gateway & download the VPN client (steps 9-13) 

Then on the macOS device:

Install the “Azure VPN Client” application from the Apple Store
Import the connection profile (using azurevpnconfig.xml from the VPN client you downloaded)

Now, when you connect to the Azure VPN, you’ll be promoted for your Azure Active Directory credentials!

Azure AD sign-in for the macOS VPN to Azure

Conclusion:

VPNs are an important component of network security, especially with a remote and mobile workforce. Azure Active Directory authentication for the VPN for macOS devices is easy to configure and lets you take advantage of other Azure AD security features you may be using for other devices in your organisation.  

Learn more:

What is a VPN Gateway? 
Explore Azure networking services 
Architect network infrastructure in Azure 
Implement network security in Azure

CLI for Microsoft 365 v3.10

by Contributed | May 31, 2021 | Technology

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

CLI for Microsoft 365

Manage Microsoft 365 and SharePoint Framework projects on any platform

$lists = m365 todo list list -o json | ConvertFrom-Json

m365 cli config set --key printErrorsAsPlainText --value false

m365 aad app role list --appName "My app"

m365 cli config get --key output

m365 file add --filePath file.pdf --folderUrl "https://contoso.sharepoint.com/Shared Documents"

m365 pa app remove --name 3989cb59-ce1a-4a5c-bb78-257c5c39381d

m365 spo site apppermission set --siteUrl https://contoso.sharepoint.com/sites/project-x --appDisplayName Foo --permission read

npm i -g @pnp/cli-microsoft365

docker run --rm -it m365pnp/cli-microsoft365:latest

Service Bus — .Net AttachmentPlugin to send and receive messages about 1MB

by Contributed | May 30, 2021 | Technology

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

Pre-requirements:

Before we start please read this .Net send and receive Service Bus message , Service Bus message size limit and .Net AttachmentPlugin Simple code.

Following the first document you may understand how to set up a .Net project to send and receive service Bus messages. However, it has limitation for the size of message, Standard pricing tier 256 KB and premium pricing tier 1MB. Then how about the message above 1MB? You may receive an error message about “QuotaExceededException” when your message above the limit. So how to resolve this issue when you need to send message above 1MB?

We plan to release a preview function that allow users send and receive messages up to 100 MB. But currently we don’t have an estimated time. This blog is to explain how to use an AttachmentPlugin in .Net program to send and receive message above 1MB. This is a work around only implement in .Net program. If your system is using other languages like Python, Java and so on, we suggest you separating the messages and change the size of the messages.

Now, let’s talk about how to use this AttachmentPlugin.

Preparation:

The work principle of this AttahchmentPlugin is implement Claim Check pattern with Azure Storage. It based on this pattern to store message data in Azure Storage Account Container (data Store) and pass a Claim Check to Azure Service Bus. Azure Service Bus can use the Claim Check to retrieve the stored information.

• This Plugin is to save the message to Storage Account, so you need to have an Azure Storage Account first.


• Then you can get the AttachmentPlugin from Microsoft Visual Studio “Manage Nugget Packages” and search for “ServiceBus.AttachmentPlugin”. Or if you are using Nuget Package Manager Console, you can use this script in below to install it.

           PM> Install-Package ServiceBus.AttachmentPlugin

Test:

Parameters :

• Service Bus Connection String: You can get it from Azure Portal, your Service Bus Namespace “Shared access Policies”

• Your Service Bus Queue name or Service Bus Topic name and Subscription name


• Storage Account Connection String: you can get from Azure portal. Check your storage account “Access key”.

• And Storage Account Container name.

Program:

After getting all the values, then you can try to use this sample program to send and receive messages.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using ServiceBus.AttachmentPlugin;

using System.Threading.Tasks;

using Microsoft.Azure.ServiceBus;

using Microsoft.Azure.ServiceBus.Core;

namespace ServiceBusAttachmentPlugin

{

    class Program

    {

        const string ServiceBusConnectionString = “<your Service Bus Connection String >”;

        const string ServiceBusQueueName = “<your Service Bus Queue name >”;

        const string StorageConnectionString = “<your Storage Account Connection String >”;

        const string StorageContainerName = “<your Storage Account Container Name>”;

        const string ServiceBusTopicName = “<your Service Bus topic name>”;

        const string ServiceBusSubscriptionName = “<your Service Bus subscription name>”;

    private static async Task MainAsync()

    {

            var sender = new MessageSender(ServiceBusConnectionString, ServiceBusQueueName);

            var config = new AzureStorageAttachmentConfiguration(StorageConnectionString, StorageContainerName);

            sender.RegisterAzureStorageAttachmentPlugin(config);

            byte[] msgBytes = Encoding.ASCII.GetBytes(“Test message”);

            await sender.SendAsync(new Message(msgBytes));

/*If you want to receive message from Service Bus Topic/Subscription, you need to use this EntityNameHelper to get subscriptionPath, then use MessageReceiver() function to receive message .*/

//string subscriptionPath = EntityNameHelper.FormatSubscriptionPath(ServiceBusTopicName, ServiceBusSubscriptionName);

//IMessageReceiver receiver = new MessageReceiver(ServiceBusConnectionString, subscriptionPath, ReceiveMode.ReceiveAndDelete);

            var receiver = new MessageReceiver(ServiceBusConnectionString, ServiceBusQueueName, ReceiveMode.ReceiveAndDelete);

            receiver.RegisterAzureStorageAttachmentPlugin(config);

            var taskrc = await receiver.ReceiveAsync();//Here we use ReceiveAndDelete mode first, if you need use Peeklock mode, please complete the message.

            var message = taskrc.Body;

           // await receiver.CompleteAsync(message.SystemProperties.LockToken);

        }

        static void Main(string[] args)

        {

            MainAsync().GetAwaiter().GetResult();

        }

        }

    }

Attentions:

• You can use your own way to serialize your message, like use function JsonConvert.SerializeObject(). But you need to deserialize the object to your message when you receive it.


• This sample code use SendAsync() and ReceiveAsync() functions, so we need use “await” to make sure the message complete. For the difference between sync and Async please read my previous blog Service Bus –Complete Message Asynchronously or Synchronously? – Microsoft Tech Community


• We use RecivedAndDelete mode to receive the message, if you need use PeekLock mode, please remember to complete the message. For example in Microsoft.Azure.ServiceBus use receiver.CompleteAsync(message.SystemProperties.LockToken).Please check Service Bus Receive mode and .Net example  for more examples.

Results:

• After using the sample code, we sent messages successfully. You can find your message in Azure portal->Service Bus explorer. But it only can Peek 32 messages.

• You also can check your messages from Service Bus Explorer application, you can download it from GitHub Service Bus Explorer. You can use your Service Bus Connection String to connect your Service Bus. You would get more details about this message.

• In addition, these messages will save to your Storage Account container. You can also check it from your container.

In conclusion, this Service Bus AttachmentPlugin is a work around to send and receive messages above 1MB. You need to keep them before you want to receive these messages. And if you received the message from Service Bus the blobs would still in the Azure Storage Account. If you need to delete them when Azure Service Bus receive the message you can add functions to delete it from Storage Account. It would have additional cost in Storage Account, so it would be the limit for this Service Bus AttachmentPlugin.  You can weigh your own interests to make decisions.