How to enable the diagnostics logs for SQL Database in Azure

If you want to monitor your Azure SQL instances through the OMS then you should enable this first. To do that, first go into the SQL Databases and select the correct DB that you want to enable the diagnostics.

Then click Diagnostics settings under Monitoring. As you can see, If the Diagnostics not enabled, then you can click on “Turn on Diagnostics”

Give a name and select the service that you want to use to get the logs. Here I’m using send to log analytics. Then select the logs that you want to grab and send to your Log analytics.

Now you will be able to get the data into OMS.

How to enable Enable Diagnostics Extension - Microsoft Azure

Azure Diagnostics is the capability within Azure that enables the collection of diagnostic data on a deployed application. You can use the diagnostics extension to collect diagnostic data like application logs or performance counters from an Azure virtual machine.

This is  supported for Windows and Linux environments.

To enable this you should go into the Virtual Machine instance that you want to enable the “diagnostics extension” then click on Diagnostic settings 

If this was not enabled, then you will get the above mentioned window and by clicking on “enable Guest level Monitoring” you will be able to enable this.

Under performance counter Tab you can select the counters that you want to monitor.

Once you select Custom, then you can do the customization to the counters and add new counters as well.

Under Logs, you can enable the Event logs that you want to get the alerts. If you want to customize them, then you can go with the custom Tab.

Can select the Storage account can be select under the Agent tab and if you want to remove the Diagnostics Agent, that is also you can do here.

Hope this will help you to the Log analytics through OMS. Next post will be more into the OMS fine-tuning.

[Solved] Cannot manage active directory certificate services … error

If you get an error message when you try to start the Active directory certificate service,

Cannot manage active directory certificate services. The system cannot find the file specified: 0x800700002 (WIN32: 2 ERROR_FILE_NOT_FOUND).

You can follow the below steps to overcome  this issue,

Basically what you need to do is to run the Post Deployment Configuration again and complete the installation of the Certification authority.

I have copy paste  the steps to do that and hope My help will not need to configure this as it was already there in the configuration file.

Once this is successful, you can restart the PC and next time you will be able to open your Certification Authority.

Enable Log Analytics in Azure

As I mentioned in my previous post. to use the OMS Log analytics feature, you can enable the Azure log analytics to collect the data from the Azure hosted instances.

To enable this, you can go in to the More Service in Azure Portal and search for Log Analytics.

Then select  create new and give a OMS workspace name.

You can create a new resource group or can select the existing resource group with the servers that you ant to monitor there.

Then select the location where you want to deploy this service.

Initially you can go with the free pricing tier which enables 500MB daily limit.

Then you can select the Virtual machine and in the next select the correct the virtual machines that you want to log the events.

Go into the selected VM and click connect. Once that is done, you will see the status as This Workspace.

Once you go into the Log Analytics Usage blade, you will be able to see the data consumption. Means you can plan, whether you can go with the free pricing tier or need to purchase some other tier.

Azure Log Analytics

Azure Log  analytics are used in the Operation Management suit to collect the availability and the performance of the cloud  and the on-premise environments.

As you can see in the below diagram, you should be able to monitor and get the log information from the Azure hosted VMs, SCOM managed Servers and VM with the OMS agents.

At the center of Log Analytics is the OMS repository which is hosted in the Azure cloud.

Data is connected into the data source and then you have a chance of selecting the mothered that you want to filter them. As you can see in the below diagram, Log search can be done through the OMS itself and then you can select the option whether you want to send the alert to a specific Ticketing tool or a recipient, Download the data which can be used in the Power BI to do the reporting, or exporting as a CSV file.

This is a sample image of Azure Activity Log in the OMS. I will post the step by step guide about the OMS configuration .

To start this you can use the OMS free data plan package which comes with an 500 MB daily upload limit.

If you have an azure subscription already then, go into OMS portal.

Troubleshoot the Azure site to Site VPN issues.

I  have experienced that most of the time the Azure site to site configuration, making issues in the initial setup because of not following the correct steps.

For an example Some Firewall / Routers support only the “ RouteBased configuration” and not the  “PolicyBased configuration ”. Then you need to select the correct configuration method initially.

Even the IKE version needs to be 2 not 1. I saw sometimes those small mistakes kills your whole day.

Before starting the configuration you can refer the below mentioned Microsoft documentation,

https://docs.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-about-vpn-devices

Here you will be able to find the deployment guide for most of the known Firewall and Routes with the “ RouteBased configuration” and the “PolicyBased configuration ” methods.

You can download the step by step guide for the firewall configuration and for Azure VPN setup details are also in the document.

Azure Site-To-Site VPN configuration

Steps to create a S2S VPN is mentioned below.

If you have not set up the virtual network gateway then you can follow my previous post about  Azure Point to site Configuration.

There you can find the details about the Virtual Network Gateway setup.

Next, you can open the Virtual Network Gateway and click on connect. I have already set up one connection and you can click on the add button to start with a new VPN setup.

Then you can give a name for the VPN and select the collection type. Here I have selected VNet-to-VNet as I was planning to configure S2S VPN.

First give a name for the configuration then select the VPN as the type to create the VPN gateway.

Then you need to select correct Virtual network which you need to establish the connection and the IP address for the destination.

For the Network Gateway again you can give a name IP address and the address space.

You can give the same Preshared key which you have entered in the destination gateway.

Or else later you can change that by goin into the Settings > Preshared Key

Once you Save that VPN status is Connecting.

You can get the Connected status if you have done the both the side configuration properly   

Azure Point to Site VPN Configuration

Steps to configure P2S VPN is mentioned below and you can follow the steps to configure your site to point VPN.

There are mainly four categories of VPN provided by the Azure for the time. Those are.

  1. Point-to-Site (VPN over IKEv2 or SSTP)

  2. Site-to-Site and Multi-Site (IPsec/IKE VPN tunnel)

                               –> Site to Site and Multi Site VPN service

  3. VNet-to-VNet connections (IPsec/IKE VPN tunnel)

  4. Site-to-Site and ExpressRoute coexisting connections

If  you want more information or the connectivity information, you can click on the links and find them.

If you want more information about the Azure VPN pricing, then click on this Link

To start the S2S configuration, you need to go into

Virtual network gateways > Select the Gateway that you have created earlier 

Then select Point-To-Site Configuration and click configure now.

If you have not created your Virtual Network Gateway, you can follow my blog post which is describe about that.

Then give an address pool which you want to use and select the tunnel types below.

Select SSTP protocol.

SSTP(Secure Socket Tunneling Protocol) is a new form of VPN tunnel with features that allow traffic to pass through firewalls that block PPTP and L2TP/IPsec traffic. SSTP provides a mechanism to encapsulate PPP traffic over the SSL channel of the HTTPS protocol. The use of PPP allows support for strong authentication methods such as EAP-TLS. The use of HTTPS means traffic will flow through TCP port 443, a port commonly used for Web access. Secure Sockets Layer (SSL) provides transport-level security with enhanced key negotiation, encryption, and integrity checking.

I have selected the Azure Certificate as the authentication type. And to use the certificate you need to Generate a Root and a Client certificate.

Steps to create the certificate can be found here.

Once you copy and paste the Certificate you will be able to save the new configuration.

Next the Download VPN client tab will be active and you will able to download the VPN client from that.

You can generate the client Certificate by using the below command,

makecert.exe -n "CN=AzureClientVPNCert" -pe -sky exchange -m 96 -ss My -in "AzureClientVPNCert" -is my -a sha1

Then import the certificate into the Personal Certificate folder and install the VPN client on the PC which you want to use this VPN.

If you have imported the Client Certificate correctly, then you will be able to connect your client VPN without any issues.

You can download the generated Certificate from https://gallery.technet.microsoft.com/Azure-Point-to-VPN-3613dbeb location.

This will help you to do the initial testing.

Azure Virtual Network Gateway

Creating a Virtual Network gateway in an Azure environment is not a rocket science. But there a few things that you need to clearly defined and if you missed them, then, will kill your day to find solutions for those errors.

To configure the Virtual network gateway select the “Virtual Network Gateway” by going into the ore services as shown below.

If you see your Virtual network in inactive that means you have to create the Gateway subnet for this network.To do that you can go into Vnet >  Subnet then you can go into “Gateway Subnet”

But If you have deployed by using the default configuration then you will not be able to

add the CIDR (Classless Inter-Domain Routing). That is because you cant use a overlapping IP range.

To overcome this you can change the “Address Space”. Here I have change to *.*.*.*/16 Which will be easy for me to manage my demo environment. but when you are doing this, you should plan this properly to reduce the unwanted broadcasts.

Once that is done, You can assign a name for the Gateway and a Static IP for the Gateway.

Hope this helps you to configure your Virtual Gateway in your Azure environment.

How to create Windows Root Certificate By using PowerShell

This will be required for the few occasions like when you want to create an Azure site to site VPN and when creating an ADFS.

First, you need to download the Window SDK and install on the server. You can find the download URL below.

Windows 10 SDK 

Once that's done, you can go into the location where this “makecert.exe” located in.

As you can see below now the location was changed and you can find that

C:\Program Files (x86)\Windows Kits\10\bin\10.0.16299.0\x86>

I have highlighted the new folder location in yellow color.

Now you can run “Dir” a check whether the ““makecert.exe” file is available there.

To create the Root Certificate you can run,

makecert -sky exchange -r -n "CN=AzRootVPNCert" -pe -a sha1 -len 2048 -ss My "AzRootVPNCert.cer"

Once that is succeed, you should be able to find the created certificate under the personal certificate.

Next  will find the way that we can create the client certificate.