Lesson Learned #132: Delays connecting to Azure SQL Database from SQL Server Management Studio.

by Scott Muniz | Jul 1, 2020 | Azure, Microsoft, Technology, Uncategorized

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

Today, I worked on a service request that our customer reported some delays when they are connecting to Azure SQL Database using SQL Server Management Studio. In this article I would like to share with you an explanation about it.

We know that in Azure SQL Database we have two different user types: Login and Contained Users and following I would like to explain what is the impact using both of them connecting to Azure SQL Database from SQL Server Management Studio. 

Before doing anything, I’m going to enable SQL Auditing that allow us to understand the different delays and what is hapenning behind the scenes when we are connecting using SQL SERVER Management Studio:

1) Login:

• I created a login using the following TSQL command: CREATE LOGIN LoginExample with Password=’Password123%’
• I’m going to give the permissions to a specific database that I created, let’s give the name DotNetExample. 
• Connected to this database I run the following TSQL command to create the user and provide the permission. 
  • CREATE USER LoginExample FOR LOGIN LoginExample
  • exec sp_addrolemember ‘db_owner’,’LoginExample’
• Using SQL Server Management Studio with the this user and specify the database in the connection string, the login process took around 15/20 seconds. Why?
  • In order to explain it, let’s try to review the SQL Auditing file and see what is happening. 
  • All points to a normal login time and connection if we review the SQL Auditing of the user database. 
• But, what is happening in the master database?.
  • In this situation, I saw many DATABASE AUTHENTITICATION FAILED, several times, with this error message: additional_information <login_information><error_code>18456</error_code><error_state>38</error_state></login_information> “Cannot open a specified database master”
  • Why? because as we defined as a Login, SQL Server Management Studio is trying to obtain information, most probably, about the databases list, information of the server, … and in every retry is waiting some seconds.

2) Contained User:

• I created an user the following TSQL command: CREATE USER LoginExampleC with Password=’Password123%’ and gave the db_owner permissions. 
• But, Is the connection time the same?…In this situation, not, because as there is a contained user of this database, there is not needed to review any parameter of master database. 

So, based on this situation and as Azure SQL Database is oriented to Database engine the best approach to reduce this time is to use a Contained User. However, if you want to reduce the time using login, you could do the following under the master database, CREATE USER LoginExample FOR LOGIN LoginExample, to allow the permission to connect to this master database.

Enjoy!

Azure APIM – Validate API requests through Client Certificate using Portal, C# code and Http Clients

by Scott Muniz | Jun 30, 2020 | Azure, Microsoft, Technology, Uncategorized

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

Azure APIM – Validate API requests through Client Certificate using Portal, C# code and Http Clients

Client certificates can be used to authenticate API requests made to APIs hosted using Azure APIM service. Detailed instructions for uploading client certificates to the portal can be found documented in the following article – https://docs.microsoft.com/en-us/azure/api-management/api-management-howto-mutual-certificates-for-clients

Steps to authenticate the request –

1. Via Azure portal

Once we have setup the certificate authentication using the above article, we can test an operation for a sample API (Echo API in this case). Here, we have chosen a GET operation and selected the “Bypass CORS proxy” option.

Once you click on the “Send” option, you would be asked to select the certificate that you would have already installed on your machine.

Note – This is the same certificate that you would have uploaded for your APIM service and added to the trusted list in the certificate store of your workstation.

After successful authentication and request processing, you would receive the 200 OK response code. Upon maneuvering to the trace logs, you can also see the certificate thumbprint that was passed for authentication.

The inbound policy definition used for this setup is as below:

(Kindly update the certificate thumbprint with your client certificate thumbprint)

<choose>
<when condition="@(context.Request.Certificate == null || context.Request.Certificate.Thumbprint != "BF3D644C46099A9D7C073EC002312878B8F9B847")">
<return-response>
<set-status code="403" reason="Invalid client certificate" />
</return-response>
</when>
</choose>

2. Through C# or any other language that supports SDKs–

We can use the below sample C# code block to authenticate API calls and perform API operations.

Kindly update the below highlighted values with your custom values before executing the sample code attached below

Client certificate Thumbprint: BF3D644C46099A9D7C073EC002312878B8F9B847

Request URL: https://testapicert.azure-api.net/echo/resource?param1=sample

Ocp-Apim-Subscription-Key: 4916bbaf0ab943d9a61e0b6cc21364d2

Sample C# Code:

using System;
using System.IO;
using System.Net;
using System.Security.Cryptography.X509Certificates;

namespace CallRestAPIWithCert
{
    class Program
    {
        static void Main()
        {
            // EDIT THIS TO MATCH YOUR CLIENT CERTIFICATE: the subject key identifier in hexadecimal.
            string thumbprint = "BF3D644C46099A9D7C073EC002312878B8F9B847";
            X509Store store = new X509Store(StoreName.My, StoreLocation.CurrentUser);
            store.Open(OpenFlags.ReadOnly);
            X509Certificate2Collection certificates = store.Certificates.Find(X509FindType.FindByThumbprint, thumbprint, false);
            X509Certificate2 certificate = certificates[0];
            System.Net.ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
            ServicePointManager.ServerCertificateValidationCallback = new System.Net.Security.RemoteCertificateValidationCallback(AcceptAllCertifications);

            HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://testapicert.azure-api.net/echo/resource?param1=sample");
            req.ClientCertificates.Add(certificate);
            req.Method = WebRequestMethods.Http.Get;
            req.Headers.Add("Ocp-Apim-Subscription-Key", "4916bbaf0ab943d9a61e0b6cc21364d2");
            req.Headers.Add("Ocp-Apim-Trace", "true");
            Console.WriteLine(Program.CallAPIEmployee(req).ToString());
            Console.WriteLine(certificates[0].ToString());

            Console.Read();

        }

        public static string CallAPIEmployee(HttpWebRequest req)
        {
            var httpResponse = (HttpWebResponse)req.GetResponse();
            using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
            {
                return streamReader.ReadToEnd();
            }
        }

        public static bool AcceptAllCertifications(object sender, X509Certificate certification, X509Chain chain, System.Net.Security.SslPolicyErrors sslPolicyErrors)
        {
            return true;
        }
    }
}

3. Through Postman or any other Http Client

To use client certificate for authentication, the certificate has to be added under PostMan first.

Maneuver to Settings >> Certificates option on PostMan and configure the below values:

Host: testapicert.azure-api.net (## Host name of your Request API)

PFX file: C:UserspraskumaDownloadsabc.pfx (## Upload the same client certificate that was uploaded to APIM instance)

Passphrase: (## Password of the client certificate)

Once the certificate is uploaded on PostMan, you can go ahead and invoke the API operation.

You need to add the Request URL in the address bar and also add the below 2 mandatory headers:

Ocp-Apim-Subscription-Key : 4916bbaf0a43d9a61e0bsssccc21364d2 (##Add your subscription key)

Ocp-Apim-Trace : true

Once updated, you can send the API request and receive a 200 OK response upon successful authentication and request processing.

For detailed trace logs, you can check the value for the output header – Ocp-Apim-Trace-Location and retrieve the trace logs from the generated URL.

Plan Migrations to Azure VMware Solution

by Scott Muniz | Jun 30, 2020 | Azure, Microsoft, Technology, Uncategorized

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

Azure Migrate now supports assessments for Azure VMware Solution, providing even more options for you to plan your migration to Azure. Azure VMware Solution (AVS) enables you to run VMware natively on Azure. AVS provides a dedicated Software Defined Data Center (SDDC) for your VMware environment on Azure, ensuring you can leverage familiar VMware tools and investments, while modernizing applications overtime with integration to Azure native services. Delivered and operated as a service, your private cloud environment provides all compute, networking, storage, and software required to extend and migrate your on-premises VMware environments to the Azure.  

Previously, Azure Migrate tooling provided support for migrating Windows and Linux servers to Azure Virtual Machines, as well as support for database, web application, and virtual desktop scenarios. Now, you can use the migration hub to assess machines for migrating to AVS as well.

With the Azure Migrate: Server Assessment tool, you can analyze readiness, Azure suitability, cost planning, performance-based rightsizing, and application dependencies for migrating to AVS. The AVS assessment feature is currently available in public preview.

This expanded support allows you to get an even more comprehensive assessment of your datacenter. Compare cloud costs between Azure native VMs and AVS to make the best migration decisions for your business. Azure Migrate acts as an intelligent hub, gathering insights throughout the assessment to make suggestions – including tooling recommendations for migrating VM or VMware workloads.

How to Perform an AVS Assessment

You can use all the existing assessment features that Azure Migrate offers for Azure Virtual Machines to perform an AVS assessment. Plan your migration to Azure VMware Solution (AVS) with up to 35K VMware servers in one Azure Migrate project.

• Discovery: Use the Azure Migrate: Server Assessment tool to perform a datacenter discovery, either by downloading the Azure Migrate appliance or by importing inventory data through a CSV upload. You can read more about the import feature here.
• Group servers: Create groups of servers from the list of machines discovered. Here, you can select whether you’re creating a group for an Azure Virtual Machine assessment or AVS assessment. Application dependency analysis features allow you to refine groups based on connections between applications.
• Assessment properties: You can customize the AVS assessments by changing the properties and recomputing the assessment. Select a target location, node type, and RAID level – there are currently three locations available, including  East US, West Europe and West US, and more will continue to be added as additional nodes are released.
• Suitability analysis: The assessment gives you a few options for sizing nodes in Azure, between performance-based or as on-premises. It checks AVS support for each of the discovered servers and determines if the server can be migrated as-is to AVS. If there are any issues found, the assessment automatically provides remediation guidance.
• Assessment and cost planning report: Run the assessment to get a look into how many machines are in use and what estimated monthly and per-machine costs will be in AVS. The assessment also recommends a tool for migrating the machines to AVS. With this, you have all the information you need to plan and execute your AVS migration as efficiently as possible.

Figure 1 Assessment and Cost Planning Report

Figure 2 AVS Readiness report with suggested migration tool

Learn More

• For more detailed instructions on how to perform an AVS assessment, go to the documentation page
• Read more about Azure VMware Solution on the website or documentation page
• Learn more about Azure Migrate on the Azure Migrate website
• Watch the latest Azure Migrate video for a demo of performing a server migration
• Check out the new Azure Migrate Ebook

Updating to Azure Functions v3 in Visual Studio

by Scott Muniz | Jun 30, 2020 | Azure, Microsoft, Technology, Uncategorized

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

<?xml version="1.0" encoding="utf-8"?>
<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <TargetFramework>netcoreapp3.1</TargetFramework>
        <AzureFunctionsVersion>v3</AzureFunctionsVersion>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference Include="Microsoft.NET.Sdk.Functions" Version="3.0.1" />
    </ItemGroup>
    <ItemGroup>
        <None Update="host.json">
            <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </None>
        <None Update="local.settings.json">
            <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
            <CopyToPublishDirectory>Never</CopyToPublishDirectory>
        </None>
    </ItemGroup>
</Project>

3a. (Visual Studio) Update Azure Functions Runtime

Note: If you’re using Visual Studio for Mac, skip to the next section

Let’s now install the Azure Functions Runtime for Visual Studio 2019

1. In Visual Studio, select Create a new project

Visual Studio, Create New Project

2. In the Create a new project window, in the search bar, enter Functions

3. In the Create a new project window, in the search results, select Azure Functions

4. In the Create a new project window, select Next

Create a new Azure Functions Project

5. In the Create a new Azure Functions Application window, stand by while it is “Getting information about the latest function tools…”

6. In the Create a new Azure Functions Application window, once the new tools have been downloaded, click Refresh

3b. (Visual Studio for Mac) Update Azure Functions Runtime

Note: If you are using Visual Studio on PC, you may skip this step

Let’s now install the Azure Functions Runtime for Visual Studio for Mac

1. In the Visual Studio for Mac window, select New

2. In the New Project window, on the left-hand menu, under Cloud, select General

3. In the Configure you Azure Functions Project window, standby until it finishes installing the Azure Functions components

Update Functions Runtime

4. Conclusion

Updating to Azure Functions v3 requires a couple steps:

• Installing .NET Core 3
• Updating our csproj
• Updating Visual Studio’s Azure Functions Runtime

If you’d like to see an existing Azure Functions project using v3, feel free to check the Azure Functions Backend in my GitTrends app: https://github.com/brminnick/GitTrends/tree/master/GitTrends.Functions