Author: Hitesh Chauhan

Unleashing the Power of System Managed Identity for Azure SQL Database: A Step-by-Step Guide and Troubleshooting Tips

Login failed for user ‘<token-identified principal>’

Azure SQL Database provides a powerful feature called Managed Identity, which allows applications and services to authenticate and access resources securely without the need for explicit credentials. In this blog post, we will focus on System Managed Identity for Azure SQL Database. We will cover its benefits, common permission-related exceptions, and provide a practical example of connecting Azure Data Factory and Azure App Service using System Managed Identity. Additionally, we’ll demonstrate how to query the roles assigned to the managed identity.

Introduction to System and User Assigned Managed Identity

Azure offers two types of Managed Identity: System Managed Identity and User Assigned Managed Identity. System Managed Identity is automatically provisioned and managed by Azure for a specific Azure resource, such as an Azure SQL Database. On the other hand, User Assigned Managed Identity is created explicitly by the user and can be assigned to one or more Azure resources. Learn more about Managed Identity

System Assigned Managed Identity Provides Several Benefits

  • Simplified authentication: Eliminates the need to store and manage explicit credentials for Azure resources.
  • Secure access: Establishes a secure channel between the Azure resource and the target resource without exposing sensitive information.
  • Seamless integration: Allows applications and services to access other Azure resources, such as storage accounts or Key Vaults, with minimal configuration.

Enable the System Assigned Managed Identity

To enable the managed identity, you can use the Azure portal, Azure CLI, or Powershell.

Azure CLI

az sql server update --name <sql-server-name> --resource-group <resource-group-name> --assign-identity

Azure Portal

Enable System Assigned Managed Identity For App Service

To enable the managed identity, you can use the Azure portal, Azure CLI, or Powershell.

Azure CLI

az webapp identity assign --name <app-service-name> --resource-group <resource-group-name>

Azure Portal

Common Exceptions and Permission Requirements

The more common exception when working with managed identity
Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ‘<token-identified principal>’. Stackoverflow

When working with System Managed Identity, it’s crucial to understand the common exceptions that can occur when the appropriate permissions are not assigned to the managed identity. The most common permission-related exceptions include:

  • Login Permissions: Ensure that the managed identity has the necessary login permissions at the SQL Server or database level. Without these permissions, the managed identity won’t be able to establish a connection to the Azure SQL Database.
  • DDL (Data Definition Language) Permissions: If the managed identity lacks the necessary DDL permissions, it won’t be able to perform operations such as creating or modifying database schema objects.
  • Reader and Writer Permissions: Without proper reader or writer permissions, the managed identity may encounter issues when attempting to access or modify data in the Azure SQL Database.
  • Stored Procedure Execution Permissions: If the managed identity does not have execute permissions on stored procedures, it won’t be able to invoke them.

To avoid these exceptions, it’s essential to ensure that the managed identity is granted the appropriate permissions at the SQL Server or database level, as well as for specific operations within the Azure SQL Database.

Querying Roles Assigned to the Managed Identity

To check the roles assigned to the System Managed Identity in Azure SQL Database, you can use the following query

SELECT p.name AS PrincipalName, r.name AS RoleName
FROM sys.database_role_members m
JOIN sys.database_principals p ON m.member_principal_id = p.principal_id
JOIN sys.database_principals r ON m.role_principal_id = r.principal_id
WHERE p.name = '<app server name or adf name>'

Assign Appropriate Permission

Run the following command using SSMS or Azure Data Studio. You must connect to the database you want the system identity to access and have admin permissions to execute the following commands. A system identity is always Azure resource name, e.g., App Service Name or ADF name—additional reading for Create User command.

USE DatabaseName;
CREATE USER <App_Service_Name or Managed Identity Name> FROM EXTERNAL PROVIDER;

-- DB Reader
ALTER ROLE db_datareader ADD MEMBER <App_Service_Name or Managed Identity Name>;
--DB Writer
ALTER ROLE db_datawriter ADD MEMBER <App_Service_Name or Managed Identity Name>;

-- db_ddladmin [Not required]
ALTER ROLE db_ddladminADD MEMBER <App_Service_Name or Managed Identity Name>;

Limitations

System Assigned Managed identities in Azure have certain limitations that you should be aware of:

  1. One-to-one relationship: Each Azure resource can have only one system-managed identity associated with it. If you require multiple identities for different purposes, you may need to consider using user-assigned managed identities.
  2. Limited to Azure AD authentication: System-managed identities are designed for Azure AD authentication within the Azure ecosystem. They cannot be used for external authentication protocols like OAuth or OpenID Connect.
  3. Limited to Azure resources: System-managed identities are tied to specific Azure resources such as virtual machines, Azure Functions, or App Services. They cannot be used outside the scope of these resources.
  4. Limited to specific Azure services: While system-managed identities are available for a wide range of Azure services, not all services support them. It’s essential to check the documentation for specific services to confirm if they support system-managed identities.
  5. Limited to specific resource types: System-managed identities are available for certain resource types like virtual machines, Azure Functions, and App Services. They may not be available for other Azure resources or services.
  6. Limited control over lifecycle: With system-managed identities, the lifecycle management is handled automatically by Azure. This means you have limited control over aspects like renewal, revocation, or deletion of the identity.
  7. Limited visibility and management: System-managed identities are not visible or manageable through Azure Active Directory. They are managed within the context of the specific Azure resource to which they are assigned

Key Takeaways

  1. System Managed Identity for Azure SQL Database provides a secure and seamless authentication method for accessing resources within Azure.
  2. Understanding the benefits of System Managed Identity, such as simplified authentication and secure access, can help you leverage its full potential.
  3. Be aware of common permission-related exceptions that may arise when working with System Managed Identity, including missing login permissions at the SQL Server or database level, as well as DDL, reader, writer, or stored procedure execution permissions.
  4. By properly configuring and granting the necessary permissions to the managed identity, you can avoid these exceptions and ensure smooth operations.
  5. Practical examples, like connecting Azure Data Factory and Azure App Service using System Managed Identity, demonstrate the real-world application and value of this feature.
  6. Querying the roles assigned to the managed identity can help you verify the access levels and permissions associated with it within the Azure SQL Database.
  7. While system-managed identities offer convenient and secure authentication within Azure, it’s important to understand their limitations and consider whether they align with your specific requirements. For more advanced scenarios or greater control, user-assigned managed identities or service principals may be more suitable.

By keeping these key takeaways in mind, you can harness the power of System Managed Identity in Azure SQL Database and enhance the security and efficiency of your Azure resources.

Exploring Cross-Database Queries with External Tables in Azure SQL Database – Elastic Query

Introduction

In Azure SQL Database, the ability to perform cross-database queries is essential for consolidating data and extracting insights from multiple databases. One powerful feature that enables this capability is the use of external tables. In this blog post, we will delve into the concept of external tables, demonstrate an example using the “Orders” and “Customers” tables residing in different databases, and provide step-by-step instructions to set up and execute a cross-database query.

Learn more about it from Microsoft document

What are External Tables?

External tables in Azure SQL Database allow you to define a table structure that references data residing outside the current database. It provides a seamless way to query and join data from disparate databases, allowing you to access and combine information across different database boundaries.

Example

Consider a scenario where you have two databases, “OrderDB” and “CustomerDB”. The “Orders” table resides in “OrderDB”, while the “Customers” table is located in “CustomerDB”. The CustomerDB is a master database for customer records. We want to retrieve information from both tables using a single query.

Add a database user; skip if you already have one

Run the following scripts to add the user to the master database. We will need this user for the cross-database query. When a new SQL Server is created in Azure, the master database is automatically set as the default database.

CREATE LOGIN ExternalTableTestUser WITH password='Password12345';

Create Database

In our example, we will work with two databases CustomerDb and OrderDb. You can create new databases using Azure Portal.

Database User and Permission

Add the database user to the CustomerDb. This user is required to access tables in CustomerDb. 

CREATE USER CrossDatabaseUser FROM LOGIN CrossDatabaseUser;

EXEC sp_addrolemember 'db_datareader', 'CrossDatabaseUser';

Or you can give permission to a specific table.

GRANT SELECT ON [dbo].[TableName] TO CrossDatabaseUser;

Create Tables

Create the “Customers” Table in CustomerDb. 

CREATE TABLE Customers
(
    CustomerId INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Insert Sample Data into the “Customers” Table

INSERT INTO Customers (CustomerId, FirstName, LastName)
VALUES
    (1, 'John', 'Doe'),
    (2, 'Jane', 'Smith'),
    (3, 'Michael', 'Johnson');

Create the “Orders” Table in OrderDb.

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    ProductName VARCHAR(50),
    Quantity INT
);

Insert Sample Data into the “Orders” Table

INSERT INTO Orders (OrderId, CustomerId, ProductName, Quantity)
VALUES
    (1, 1, 'Product A', 5),
    (2, 1, 'Product B', 3),
    (3, 2, 'Product C', 2),
    (4, 3, 'Product D', 1);

Setting Up External Tables

To enable cross-database queries, we need to follow these steps:

Create a Master Key:

  • Connect to each database separately. Must perform this step in OrderDb and CustomerDb
  • Execute the following SQL command to create a master key:
CREATE MASTER KEY;

Create an External Data Source in OrderDb:

Execute the following SQL command to create an external data source that references CustomerDb:

CREATE EXTERNAL DATA SOURCE RemoteCustomerDb
WITH (
    TYPE = RDBMS,
    LOCATION = '<server-name>.database.windows.net', 
    DATABASE_NAME = 'CustomerDb',
    CREDENTIAL = ElasticQueryCredential
);

Replace <server-name> with the name of the server hosting CustomerDb.

Create an External Table in OrderDb

Execute the following SQL command to create an external table in OrderDb referencing the “Customers” table in CustomerDb.

CREATE EXTERNAL TABLE Customers
(
    CustomerId INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Address VARCHAR(500)
)
WITH
(
    DATA_SOURCE = RemoteCustomerDb,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'Customers'
);

Executing the Cross-Database Query

Now that we have set up the external tables, we can perform a cross-database query to retrieve information from both tables:

SELECT C.CustomerId, C.FirstName, C.LastName, O.OrderId, O.ProductName, O.Quantity
FROM Customers AS C
JOIN Orders AS O ON C.CustomerId = O.CustomerId;

In this example query, we are joining the “Customers” table from CustomerDb with the “Orders” table from OrderDb, using the common column “CustomerId”. By executing this query, we can seamlessly access and combine data from both databases.

Conclusion

External tables provide a powerful mechanism for performing cross-database queries in Azure SQL Database. By referencing tables from other databases, you can consolidate and analyze data effectively, enabling better insights and decision-making. In this blog post, we explored the concept of external tables and demonstrated a practical example with the “Orders” and “Customers” tables residing in different databases. By following the provided steps, you can set up and execute your own cross-database queries using external tables in Azure SQL Database.

Success Story — Azure Solution Architect Certifications

When a person is not confident then the mind forces us to stay away from the thing. I got trapped in a similar mindset for Azure certification. I had been thinking of doing it for the last one or two years but was delayed for no reason. I started many times but did persist enough to go to the next level due to the vast nature of the cloud.

Last year, I pushed my mind to pursue this path. I started late Oct 2019 with Az-300 training from Edureka. It was weekend classes for 6 weeks. I learned a lot from that training but didn’t get the confidence to attempt for the exam though. During the training period, I was learning and trying Azure services. So, the whole thing lasted 3 months and In Feb 2020 I got my Azure Expert certificate.

Tips: Never rely on a single resource when preparing for the exam. I found different ways to achieve the same thing in Azure videos. That’s useful and improved my overall understanding of the topic.

Disclaimer: my personal website and other works I did before starting on this path.

This is how I started.

Az-303 and Az-304

Let’s talk about the exam format. The number of questions varies for each student. I got 45 questions in Az-300 and 61 questions in Az-301. You see the details at the beginning of the exam, don’t forget to read them.

Lab Questions:

Both exams had lab questions, Az-300 had on lab consist of 8 tasks that I had to perform on Azure portal. I got two labs in Az-301 and questions were on the resources that were already there. e.g. how to optimize the cost of the storage account.

They spin Azure environment where you need to perform the given tasks. You don’t need to wait for the resource deployment you can do another task. Since Azure captures all the activities so they can measure the result based on the activity logs.

Case Study:

I got one case study for each exam. There were about 6 questions on the case study.

I had captured notes of key findings and useful links in the document on Google drive to read them later or whenever I had difficulty recalling them. I would recommend keeping the notes for reference.

Exam Preparation

Instructor-Led Training:

As I mentioned earlier; it started with training. I would recommend finding an instructor-led online training so that you can ask questions. Sometimes, we get questions in our head that might seem stupid to an expert but those are legitimate to build up good knowledge. So, the training will help you to clear your understanding of the cloud and Azure.

Microsoft Documentation:

Microsoft has rich documentation and covers almost everything it offers through Azure. They have built consistencies in the document layout and structures. As you can see in the below VPN Gateway documentation. A single place to learn Azure VPN Gateway. It has links to other related topics.

Learn about Azure architecture from the Architecture Center. I found it very useful and learned most of the Azure architecture from this. They have created reference architecture for most of the services that help to understand the architecture.

Microsoft Learn:

In Microsoft Learn, you can find how-to training materials. They are useful I tried them whenever I need more clarity on a topic/service.

For example, Core Cloud Services — Azure data storage options

LinkedIn Learn and Udemy

I bought a couple of training on Udemy. However, most of them only touch the surface. So, I would highly recommend don’t rely on any Udemy training. You can use them for additional learning.

LinkedIn also has a similar level of training material.

You will keep hearing from the author that please do not rely only on this training because they know how much they have covered and what’s the level required to pass the exam.

I also bought practice tests on Udemy, but they have a very low level of difficulty level compared to the actual exam. So, use them to improve your knowledge, and don’t assume you will see similar questions in the exam.

If you are already familiar with Azure and its exams then you can skip the next section.

First time attempting Azure exam?

First, you need to decide where you want to start. If you don’t have any knowledge then start with Az-900. Azure has role-based certifications to evaluate different skill sets e.g. Security, Administration, DevOps, Development, Architect, IoT, Data. You can find more about other certifications here.

I hope this helps you to at least start on this if you have been thinking and delaying it…

You may like to check the sample questions.

Normal -> Anxiety -> Better, is it a blessing or a curse?

Disclaimer: I am not a professional counselor and do not intend to prescribe anything here. You should seek a professional service whenever you have to. I am sharing my own experience, which may relate to you or someone else around you.

Discovery Phase

Always look for the sign inside, check that if you have a mind that is not listening to you. If your answer is yes, that means you already have anxiety, or you will experience it sooner or later. For example, you plan a gym or any other exercise activity, and as the time reaches to execute the plan, your mind finds something else more important than your health. That’s an excuse the mind gives to avoid going through physical pain that one experience during the exercise. Because our mind’s job is to save energy, by design, it doesn’t know that exercise improves health — we have multiple minds/ layers.

There are many online anxiety tests that I find not that helpful. As a human, we experience multiple emotions every day, so the results of the tests vary based on when you are taking the online test.

If you keep a journal, that can help you to find a clue. Write down positive and negative things you experienced throughout the day. If you find more items on the negative side, then your mind is attracting/inviting negative energy. I found this useful to check the state of my mind. This also releases stress because things look smaller when written on paper than running in the brain. That says, our mind cannot size/weigh stuff without the help of other mind layers.

Anxiety Level

It would be best if you determined what’s your anxiety level is. You can scale them as low/medium/high.

Low: you feel happy inside, and things look positive around you. However, you get into a period where negativities crippled your mind. You find it hard to go back to your happy mojo. This phase happens due to abnormal circumstances, and most of the time, it lasts for a short interval a few hours or a day or two.
Medium: This is a kind of wave pattern. Your mood frequently swings, even with a small incident. You feel happy if everything goes well around you, and the very next day, you feel sad/mad, or other emotions take over your mental state.
High: You find it impossible to be happy again. Your mind runs in autopilot mode, and negativity is predominant. Even a small thing looks overwhelming to your mind, whether it’s positive or negative. At this level, a person is on anxiety prescription drugs.
There is one more level, which is extreme, where one experiences a panic attack. At this level, even this article will cause a disturbance in your mind. However, I would recommend reading it until the end.

Acceptance

Your anxiety level goes down the day you acknowledge it and start discussing your feeling with others. The initial realization gives clarity to our minds for better processing and handling. Second, when you talk about it with others, that means you are not holding anything.

We live in a world where everyone shows/talks about their strong side that misleads others who are watching/listening to feel they have a problem in their lives, and everyone else is happy, healthy, or wealthy. However, in reality, every human life experiences anguish and difficulties, but very few talk and share.

Opportunities

You might be asking why me? Others are happy, and why am I going through this? The short answer is you didn’t care when you had to, and you let your mind be trained in things that you don’t want to see. Anxiety is a mental state like a happy mind.

Consider this as your failure to learn about yourself. Anxiety opens up lots of opportunities if you look at it with a positive mind. When you are experiencing anxiety, you start noticing all the inside activities food digestion system, heartbeats, brain patterns, a small-small body pain, which you never experienced before. It improves your awareness, which is very important for your growth. You started noticing how your mind reacts to the events.

That is why I call it a blessing. It has changed my life and certainly made me a better person than I was before the experience

Transitioning from Anxiety to Better life

Your mind does everything to keep the current pattern. It would be best if you use an external force to break mental habits. Also, stop listening to your mind and start training your mind using mental and physical exercise. Use a safe yoga posture, and don’t give up even though your brain can’t take it. Always remember your body can take more than what you mind. I used to do Sleeping tiger and chair postures. I started with a Stopwatch and recorded the initial time where I didn’t feel much muscle pain and kept adding 30 seconds from there.

Physical activity releases endorphins and dopamine that help a lot in the transition to a better life.

Although you are vulnerable, I would recommend talking about your mental states with others. Be vigilant; people can easily influence you.

Don’t run away from the situation/places that trigger anxiety. Tell your mind that your body is safe, and everything is normal.

Write only positive things at least once a day. Don’t dwell on negativity. It’s your mind, and nothing has changed outside.

Meditation is the best way when you mix it up with yoga. If you solely do meditation, you won’t be able to focus because of the stressful mind. You can see the differences, do the slow breathing exercise before and after yoga. In the beginning, you would find it hard to control breathing, but it becomes easy if you do the same after the session.

If you notice kids, they are always happy except when they want something badly. But they let go of things and move on. Another reason for their happiness could be very minimal knowledge of things. Remember, you were a kid too. Don’t feed any information that brings negativity. e.g., watching violent movies or news.

There are many methods I used, and everyone is different, so always try a new way to teach your mind to look at things with a positive mind.

Sustainability

If you keep a little positive approach, you will come out from the anxiety. The techniques you use to come out from stress keep practicing that and get better at it.

Happy Life

The whole journey will make you a better person, and you will learn to let go of things that don’t matter. Everything is beautiful inside and outside. You need to bring that beauty in your mind by mind training.

Stay positive and live a happy life!!

Is anger normal?

The Power of Anger: Understanding and Navigating Our Emotions

In a world where anger and agitation have become the norm, we seldom pause to contemplate their underlying causes. Today, I invite you to explore some thought-provoking questions that can lead you on a path towards eliminating or gaining better control over these so-called negative emotions. Prepare to embark on a journey of self-discovery and emotional mastery.

Anger is more than just an intense emotion—it has left an indelible mark on my own life. I have often found myself filled with regret when negative emotions take control of my mind, leading me to engage in actions that I later wished I hadn’t. The repercussions of such moments have extended far beyond the initial outburst, affecting my overall well-being and the relationships with those around me. It’s a powerful reminder of the importance of understanding and managing these emotions for the sake of our own health and the harmony of our connections with others.

Through personal experiences and self-reflection, I’ve come to understand its significance and the importance of managing it effectively. In this article, I aim to share my insights on anger, its normalcy, and practical strategies for transforming it into a positive force in our lives.

I am writing it is in layman language. I am merely sharing my experience and awareness of my own mind.

When we see and hear something then eyes and hearing sense send chemical signals to our brain for processing and the brain processes them and responds in a logical manner. That’s why we believe what we see and hear. But that’s not always true though. We are limited to our own knowledge we can’t make any logical sense beyond our mental limits. So, how does our mind learn?

In essence, you shape your identity and worldview through the content you consume, the actions you take, and the thoughts you cultivate.

You may be thinking, “What’s the big deal? Everyone does that.” However, the truth is, not everyone possesses the awareness to allow thoughts to pass through their mind without holding onto them. Those who are mindful of their thought processes consciously apply filters to incoming information, safeguarding their overall mental state. By selectively processing and letting go of certain thoughts, they maintain a healthier and more balanced mindset.

What are those filters?

  • Refine your media choices to align with the positive outcomes you desire, focusing on content that uplifts and inspires you.
  • Curate a playlist or seek out audio frequencies that promote brain healing and well-being, incorporating them into your listening habits.

Through personal experience, I have witnessed remarkable enhancements in my mental well-being by consciously abstaining from television consumption and avoiding enticing commercial distractions. I have chosen to disconnect from news sources that inundate me with information beyond my control or interest. By curating my content intake, I have reclaimed control over my mental state and focused on engaging only with what aligns with my values and aspirations.

I would highly recommend incorporating devotional songs into your routine, as they can help foster a deep spiritual connection with God based on your religious beliefs. If you identify as a non-believer, exploring the realm of binaural beats or soothing music can provide a calming and transcendent experience that nurtures your sense of inner peace and spirituality.

You may be wondering, “If I’m not consuming mainstream media, what should I focus on instead?” The key lies in seeking out content that nurtures your overall well-being. Engage in activities that promote personal growth, such as exploring spiritual and psychology books, embracing meditation practices, incorporating meridian exercises, and immersing yourself in the healing power of nature by taking peaceful walks outdoors. By deliberately choosing these enriching experiences, you can cultivate a profound sense of self-discovery and inner harmony.

A physical body or rather I should the brain (including animals) is designed to react to things that are thrown at us in the easiest way without much processing, especially emotions that engage the whole body, because of the urgency. e.g. Fight-and-Flight situation.

Our physical bodies, including our brains, are wired to instinctively react to external stimuli, particularly intense emotions like anger, with minimal processing. This is a survival mechanism, seen even in animals, geared towards immediate response in fight-or-flight situations. Therefore, it is crucial to remember not to simply react but to consciously act in a way that benefits ourselves and others, ensuring a more positive outcome for all involved.

HTTP Error 500.0 — ANCM In-Process Handler Load Failure

Are you see the following page when running ASP.NET API/App? This short guide might help you.

I have seen this multiple times in different machines/servers. This happens mainly with ASP.NET Core InProcess hosting.

Troubleshooting guide:

  • Install ASP.NET Core hosting bundle for specific .NET Core version e.g. .NET Core 2.2. Download it from .NET core 2.2 (Change the version from URL).
  • After step 1, you may still see the issue because of different bitness. Use SysUtil(sigcheck.exe) to check your binary (64-bit or 32-bit).
  • If you are using 32-bit then enable 32-bit in the App pool. Go to the advance settings of the app pool and set true to Enable 32-bit Applications.
  • You may still see the issue; now check your app pool identity has permission to invoke Donet.exe and has write permission if you want to see logs to the file. This was one of the issue on the server and doesn’t look related to this error.

Web.config configuration (This is only for local development machines)

<aspNetCore processPath="C:\Program Files\dotnet\dotnet.exe" arguments="exec "D:\api\bin\x64\Debug\netcoreapp2.2\myapi.dll"" stdoutLogEnabled="true" hostingModel="InProcess" stdoutLogFile=".\logs\stdout\"><environmentVariables><environmentVariable name="ASPNETCORE_ENVIRONMENT" value="Development"/></environmentVariables></aspNetCore>
  • Microsoft team recommend to add UseIIS() to the pipeline for InProcess hosting in program.cs file. For OutProcess use IISIntegration(). Note: Once you enable IIS then, you may not be able to launch EXE or running DLL with dotnet executable.

program.cs

public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>WebHost.CreateDefaultBuilder(args).ConfigureLogging(logging =>{logging.ClearProviders();logging.AddEventLog();}).UseIIS().CaptureStartupErrors(true).UseStartup<Startup>();

I hope this helps!