What’s new for Security: Training and Certification

by Contributed | Nov 1, 2023 | Technology

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

Microsoft Learn offers you the latest resources to ensure you have what you need to prepare for exams and reach your skilling goals. Here we share some important updates about Security content, prep videos, certifications, and more.

Exam Readiness Zone: preparing for Exams SC-100, SC-200, and SC-300

Now, you can leverage the Exam Readiness Zone, our free exam prep resource available on Microsoft Learn for your next Security certification! View our expert-led exam prep videos to help you identify the key knowledge and skills measured on exams and how to allocate your study time. Each video segment corresponds to a major topic area on the exam.

During these videos, trainers will point out objectives that many test takers find difficult. In these videos, we include example questions and answers with explanations.

For technical skilling, we now have videos available for the following topics:

Preparing for Exam SC-100: Microsoft Cybersecurity Architect

• Design solutions that align with security best practices and priorities


• Design security operations, identity, and compliance capabilities


• Design security solutions for infrastructure


• Design security solutions for applications and data

Review the exam prep videos.

Preparing for Exam SC-200: Microsoft Security Operations Analyst

• Mitigate threats using Microsoft 365 Defender


• Mitigate threats using Microsoft 365 Defender


• Mitigate threats using Microsoft Sentinel

Review the exam prep videos and take a free practice assessment.

Preparing for Exam SC-300: Microsoft Identity and Access Administrator

• Implement identities in Azure AD


• Implement authentication and access management


• Implement access management for applications


• Plan and implement identity governance in Azure AD

Review the exam prep videos and take a free practice assessment.

Visit the Exam Readiness Zone to leverage tips, tricks, and strategies for preparing for your next Microsoft Certification exam.

Newly added Security Cloud Skills Challenge on 30 Days to Learn It

We recently released the new Security Operations Analyst Cloud Skills Challenge on 30 Days to Learn It. Build your skills and prepare for Exam SC-200: Microsoft Security Operations Analyst, required to earn your Microsoft Certified: Security Operations Analyst Associate certification.

Are you thinking of adopting the upcoming Security Copilot? This challenge will help you prepare, as it includes the security operations analyst skills required to tune up your platform and get it ready for Security Copilot.

Complete the challenge within 30 days and you can be eligible to earn a 50% discount on the Certification exam.

Start the 30 Days to Learn it challenge today!

New name: Information Protection and Compliance Administrator Associate certification

As we announced a couple of months ago, we updated the certification name to the Microsoft Certified: Information Protection and Compliance Administrator Associate certification and the exam name to Exam SC-400: Administering Information Protection and Compliance in Microsoft 365 as we recognized the need to expand this certification and exam to include compliance features.

The Exam SC-400 evaluates your proficiency in performing the following technical tasks: implementing information protection, implementing DLP, implementing data lifecycle and records management, monitoring and investigating data and activities through Microsoft Purview, and managing insider and privacy risks in Microsoft 365.

Prepare for the exam with the SC-400 study guide and with our free practice assessment.

Security Learning Rooms

The Microsoft Learn Community offers a variety of ways to connect and engage with each other and technical experts. One of the core components of this experience are the learning rooms, a space to find connections with experts and peers.

There are four Microsoft Security Learning Rooms to choose from that span end-to-end:

• Cloud Security Study Group


• Compliance Learning Room


• Cybersecurity from Beginner to Expert


• Microsoft Entra

Whether you choose one path or all of them, the Microsoft Learn Community experiences are ready to support your learning journey.

To explore additional security technical guidance, please visit the refreshed Security documentation hub on Microsoft Learn.

How to Avoid Transaction Isolation Level Issues on Azure SQL Managed Instance

by Contributed | Oct 30, 2023 | Technology

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

In this technical article, we will delve into an interesting case where a customer encountered problems related to isolation levels in Azure SQL Managed Instance. Isolation levels play a crucial role in managing the concurrency of database transactions and ensuring data consistency. We will start by explaining isolation levels and providing examples of their usage. Then, we will summarize and describe the customer’s problem in detail. Finally, we will go through the analysis of the issue.

Isolation Level

Isolation level is a property of a transaction that determines how data is accessed and modified by concurrent transactions. Different isolation levels provide different guarantees about the consistency and concurrency of the data. SQL Server and Azure SQL Managed Instance support five isolation levels: read uncommitted, read committed, repeatable read, snapshot, and serializable. The default isolation level for both platforms is read committed.

Read uncommitted allows a transaction to read data that has been modified by another transaction but not yet committed. This can lead to dirty reads, non-repeatable reads, and phantom reads. Read committed prevents dirty reads by only allowing a transaction to read data that has been committed by another transaction. However, it does not prevent non-repeatable reads or phantom reads. Repeatable read prevents non-repeatable reads by locking the data that has been read by a transaction until the transaction ends. However, it does not prevent phantom reads. Snapshot prevents both non-repeatable reads and phantom reads by using row versioning to provide a consistent view of the data as it existed at the start of the transaction. Serializable prevents all concurrency anomalies by locking the entire range of data that is affected by a transaction until the transaction ends.

The isolation level can be set for each connection using the SET TRANSACTION ISOLATION LEVEL statement or using the IsolationLevel property of the .NET TransactionScope class. The isolation level can also be overridden for individual statements using table hints such as (NOLOCK) or (READCOMMITTED).

Problem Description

The customer reported that they observed unexpected transaction isolation level changes when running distributed transactions using .NET Transaction Scope on Azure SQL Managed Instance, while the same application was behaving differently when using On premise SQL Server.

The customer was opening two connections to the same database under one transaction scope, one at a time, and they observed the transaction isolation level got reset after the second connection had been opened. For example, if they set the isolation level to repeatable read for the first connection, it would be changed to read committed for the second connection. This caused inconsistency and concurrency issues in their application.

The following code snippet illustrates the scenario:

TransactionOptions transactionOptions = new TransactionOptions
{
                IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
};

string connectionStr = "Data Source=testwest.com;Initial Catalog=test;User id=sa;Password=;Connection Timeout=0";

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
                using (SqlConnection connection1 = new SqlConnection(connectionStr))
                {
                                SqlCommand cmd = new SqlCommand("SELECT transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID", connection1);
                                connection1.Open();
                                SqlDataReader rs = cmd.ExecuteReader();
                                rs.Read();
                                Console.WriteLine(rs.GetInt16(0));
                                connection1.Close();
                }

                using (SqlConnection connection2 = new SqlConnection(connectionStr))
                {
                                SqlCommand cmd = new SqlCommand("SELECT transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID", connection2);
                                connection2.Open();
                                SqlDataReader rs = cmd.ExecuteReader();
                                rs.Read();
                                Console.WriteLine(rs.GetInt16(0));
                                connection2.Close();
                }

                ts.Complete();
}

The customer stated that they are not using the “Pooling” parameter in their connection string, which means that connection pooling is enabled by default.

Problem Analysis

We investigated the issue and found that the root cause was related to how connection reset works on Azure SQL Managed Instance and cloud in general, compared to On-premise SQL Server.

Connection reset is a mechanism that restores the connection state to its default values before reusing it from the connection pool. Connection reset can be triggered by various events, such as closing the connection, opening a new connection with a different database name or user ID, or executing sp_reset_connection stored procedure.

One of the connection state attributes that is affected by connection reset is the transaction isolation level. Resetting the connection on Azure SQL Managed Instance will always reset the transaction isolation level to the default one, which is read committed. This is not true for on-premise SQL Server, where resetting the connection will preserve the transaction isolation level that was set by the application.

This difference in behavior is due to how Azure SQL Managed Instance implements distributed transactions using MSDTC (Microsoft Distributed Transaction Coordinator). MSDTC requires that all connections participating in a distributed transaction have the same transaction isolation level. To ensure this requirement, Azure SQL Managed Instance resets the transaction isolation level to read committed for every connection that joins a distributed transaction.

Since the customer is opening and closing the connection to the same database twice, only one physical connection will be created. The driver will use the same connection for both query executions, but the connection will be reset before being reused. The first connection reset will happen when the first connection is closed, and the second connection reset will happen when the second connection is opened under the same transaction scope. The second connection reset will override the isolation level that was set by the application for the first connection.

This explains why the customer observed unexpected transaction isolation level changes when running distributed transactions using .NET Transaction Scope on Azure SQL Managed Instance.

Conclusion

First and foremost, it is beneficial to emphasize that this is an expected behavior from a design perspective. The customer is advised to either disable connection pooling or explicitly set the transaction isolation level for every opened connection.

To disable connection pooling, they can add “Pooling=false” to their connection string. This will create a new physical connection for every logical connection, and avoid the connection reset issue. However, this will also increase the overhead of opening and closing connections, and reduce the scalability and performance of the application.

To explicitly set the transaction isolation level for every opened connection, they can use the SET TRANSACTION ISOLATION LEVEL statement or the IsolationLevel property of the .NET TransactionScope class. This will ensure that the isolation level is consistent across all connections participating in a distributed transaction, regardless of the connection reset behavior. For example, they can modify their code snippet as follows:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }))

{

    using (SqlConnection conn1 = new SqlConnection(connectionString))

    {

        conn1.Open();

        // Set the isolation level explicitly

        SqlCommand cmd1 = new SqlCommand("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ", conn1);

        cmd1.ExecuteNonQuery();

        // Execute some queries on conn1

    }



    using (SqlConnection conn2 = new SqlConnection(connectionString))

    {

        conn2.Open();

        // Set the isolation level explicitly

        SqlCommand cmd2 = new SqlCommand("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ", conn2);

        cmd2.ExecuteNonQuery();

        // Execute some queries on conn2

    }



    scope.Complete();

}

For additional information about database isolation settings, you can review the below documents.

SET TRANSACTION ISOLATION LEVEL (Transact-SQL) – SQL Server | Microsoft Learn

Transaction locking and row versioning guide – SQL Server | Microsoft Learn

System stored procedures (Transact-SQL) – SQL Server | Microsoft Learn

SQL Server Connection Pooling – ADO.NET | Microsoft Learn

I hope this article was helpful for you, please feel free to share your feedback in the comments section. 

Disclaimer
Please note that products and options presented in this article are subject to change. This article reflects isolation level settings for Azure SQL Managed Instance in October, 2023.