Zero Trust Identity Controls – Essentials Series – Episode 2

Zero Trust Identity Controls – Essentials Series – Episode 2

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

Screen Shot 2021-05-19 at 1.25.36 PM.png


 


Tour your identity options when moving to the Zero Trust security model. Our last Essentials episode gave a high-level overview of the Zero Trust security model principles: identity, endpoints, applications, networks, infrastructure, and data.


 


Join our host, Jeremy Chapman, as he unpacks the foundational layer of the model with identity. As the primary control plane for Zero Trust, it acts as the front door for people, service accounts, and devices as each requests access to resources. Identity is at the core of the Zero Trust concepts of never trust, always verify and grant the appropriate level of access through the principle of least privilege.


 


 





Verify Explicitly


Azure AD — easily implement additional protections to verify explicitly Multi-factor authentication (MFA) — requires an additional authentication factor. Replace passwords with Microsoft Authenticator, Windows Hello, or FIDO2 keys. Activity reports in the Authentication methods — see who’s capable of MFA and passwordless authentication, how many recent registrations and by type. Usage — see the distribution of MFA sign-ins and by method, as well as the number of password changes and resets.


 


Least Privilege access


Conditional Access in Azure AD — uses real-time intelligence at the time of sign-in to assess the risk level, then blocks or grants access. Built-in Insights and Reporting — expose the impact of enabled policies pre- and post enforcement.


 


QUICK LINKS:


00:37 — Demo in Azure AD


01:47 — Azure AD Application Proxy


02:50 — How to set up multi-factor authentication


04:44 — Activity Reports for admins


05:21 — Least privileged access and conditional access


07:22 — Conditional Access Insights and Reporting


08:16 — Wrap up


 


Link References:


For tips and demonstrations, check out our series at https://aka.ms/ZeroTrustMechanics


 


Learn more at https://aka.ms/zerotrust


 


Unfamiliar with Microsoft Mechanics?


We are Microsoft’s official video series for IT. You can watch and share valuable content and demos of current and upcoming tech from the people who build it at Microsoft.



 


Keep getting this insider knowledge, join us on social:











-Welcome back to our series for Zero Trust on Microsoft Mechanics. In our Essentials Episode, we gave a high-level overview of the principles for Zero Trust security model, spanning identity, endpoints, applications, networks, infrastructure, and data. Now in this episode, we’re going to unpack the foundational layer of the model with Identity, the primary control plane for Zero Trust, which acts as the front door for people, service accounts, and devices as each request access to resources. Identity is at the core of the Zero Trust concepts of verifying explicitly, and also granting appropriate level of access through the principle of least privilege.


 


-Now, this begins with Azure Active Directory and first establishing a common and unified directory service to authenticate users, devices, and processes to your resources, apps, and services. In the Microsoft 365 admin center, you’ll see your users, resource accounts, and all of your groups. Now this is powered by Azure Active Directory in the background. In fact, if I click into Azure Active Directory here under the admin centers, it’s going to take me right into Azure AD. And when I click into users, you’re going to see the same people and resource accounts and groups. As I mentioned, one of the key starting points as you move to Zero Trust is unifying the identity and access management environment with your other cloud apps and even your on-premise resources. So now I’m going to click into Enterprise Applications. And here you can see all the SaaS applications that I’ve configured in this tenant that are using an Azure AD to log in. Now, there are thousands of apps that you can choose from. And if I click into Box, for example, you’re going to see the groups that are assigned with access to this application. And under Conditional Access, you can even see some of the access policies assigned to this specific app. And we’re going to go further into Conditional Access policies in a moment.


 


-Before I jump into the user experience, I want to show you one more thing for connecting your web apps hosted on-premises, the Azure AD Application Proxy. Now here, I can see a few apps that I already have configured for Single Sign-in, along with connector and IP address information. But let me show you what it looks like then to log into a non-Microsoft SaaS app now that Azure AD is configured as its identity provider. So I’m here in the My Apps portal. And if you aren’t familiar with this, you can find it at myapps.microsoft.com. And it gives me a unified view of all the apps that Woodgrove, in this case, has set up for me. And most of these are SaaS apps, but you can see the ones branded Woodgrove. Those are actually on-prem apps. So those are going to work here as well. I’m going to click into ServiceNow and you’ll see that it authenticates me directly into that app.


 


-If I go back to My Apps, I’ll click into the one called Sales Dashboard On-Prem. And in that case, it’s using the Azure AD App Proxy that we saw earlier to connect me directly, even though that resource is on-premises. Now with Azure AD as your unified identity provider across your apps and services, you can easily implement additional protections to verify explicitly, and here multi-factor authentication, or MFA, is key. Now MFA goes beyond weak password-only authentication and requires an additional authentication factor, like a passcode relayed over an SMS or phone call. And you can even replace passwords using options such as Microsoft Authenticator, Windows Hello, or FIDO2 keys. I’m going to show you how to set this up as an admin. Then we’re going to walk through the user experiences as well as new admin reporting to monitor usage. Now to find your options to configure this in the Azure AD admin center, go to Security, then Authentication methods.


 


-Here you’ll find methods for FIDO2 security keys, along with the options to target users and groups. This one here is for the Microsoft Authenticator mobile app that you can use in combination with built-in biometric sensors for fingerprint or facial recognition on your phone. And you’ll even find the new Temporary Access Pass method that allows you to provide a time-limited passcode that you can use to register a passwordless sign-in method so that you don’t even need to share the password with the user. Since the account I’m using is setup for passwordless MFA with the Authenticator app, I’ll show you how this even works when logging into non-Microsoft sites directly. So for example, here with ServiceNow, I can use a tenant-specific URL to sign in directly with the service. You’ll see that the Azure AD sign-in page launches, and I’m going to type in my password and username. It’s going to then request passwordless sign-in to match the number on the screen, and then it’s going to send a notification on my phone.


 


-Okay, so now you can see the notifications come in, I’ll tap on that. And that’s going to open up the Authenticator app and I will then tap on the option here for 26. There we go. And that’s going to sign me in directly into ServiceNow, and I can start working from there. And for admins, we also have reporting to help you track how well your organization is doing. The Activity reports and the Authentication methods area help you to see who’s capable of MFA and passwordless authentication, how many recent registrations have been made, and by which type. Then in Usage, you can see the distribution of MFA sign-ins and by method, as well as the number of password changes and resets. And this will really help as you roll out new authentication methods and track usage over time. So now we’ve shown a few things that you can do to explicitly verify requests to your cross-cloud and on-prem resources and services.


 


-Another core tenant of Zero Trust is applying Least Privileged Access. So here in Conditional Access in Azure AD, it’s using real-time intelligence at the time of sign-in to assess the risk level of the user or sign-in, the device platform, along with the sign-in location, client apps and device state to make decisions, enforcing access policies in real time, either to block or to grant access. Now earlier, I showed you how ServiceNow could be accessed directly using Azure AD authentication. And in this policy, you’ll see that for ServiceNow you’ll need to log in with MFA. And because I use passwordless earlier that satisfied the requirement.


 


-A recent addition to Conditional Access is the ability to look at device filters. Now, these filters allow you to scope your Conditional Access policy to a group of devices. For example, you can decide to allow access to privileged resources only from secure access workstation VMs, or conversely, exempt MFA from shared meeting room devices like conference phones, Teams Meeting Room devices, or Surface Hubs. Just to show how this works, I’ll log into a VM that doesn’t meet our secure access workstation requirements, and try to reach the Azure portal. And you’ll see here that I’m blocked from going to the Azure portal. But now let me switch to a second VM that’s a secured access workstation, and you’ll see that when I try to access the Azure portal, it meets the requirements. And then it asks me to verify my identity using passwordless auth. And I’m granted access to my applications and resources in Azure.


 


-Now Conditional Access can also extend to specific sites and content. So for example, even though I can log into Woodgrove’s marketing site, as you can see here, if I go back to SharePoint’s home and then try to open a highly confidential site, like Project Saturn, you’re going to see that it requires step-up authentication, in my case, again, using passwordless auth, before it allows me to access the protected resource. And now here I can see Project Saturn’s site. So finally, as you roll out Conditional Access, built-in Insights and Reporting in Azure AD can expose the impact of enabled policies, both pre and post-enforcement. Now the Impact Summary is interactive. And for example, can help you identify why sign-ins are failing. And here you can see the device state, device platform, client app, sign-in risk and location. And detailed sign-in events can be found at the bottom of the page. Now, one tip here that you can use is to enforce policies as report only to gauge the impact of them before turning them on. This can help you avoid setting policies that may lock people out of resources they should be able to access.


 


-That was a tour of your identity options and all the considerations when moving to the Zero Trust security model. Up next, we’ll explore your options for endpoints and applications. And keep checking back to aka.ms/ZeroTrustMechanics for more in our series where I share the tips and hands-on demonstrations of how the tools for implementing the Zero Trust security model work across all the six layers of defense. Now you can also learn more at aka.ms/zerotrust. And thanks for watching.




Import data from local files using Power Query in Excel for Mac

Import data from local files using Power Query in Excel for Mac

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

We are excited to announce a new update to Power Query (PQ) in Excel for Mac – the ability to import data from local files.  We’ve received many requests for this feature after the introduction of PQ query refresh.  Now you can import data from local files including Excel workbooks and text & CSV files.


 


Getting started


This new functionality is available to Beta channel users running version 16.50 (build 21050400) or later.  See this support article to check which version you’re using.


 


Importing data from local files


You can now import data from local Excel workbooks or text & CSV files.



  1. Click Get Data (Power Query) on the Data tab

  2. Select Excel workbook or Text/CSV in the Choose data source dialog box

  3. Click Browse to select the local file

  4. Select the data you want to import and click the Load button


 


Import data from local filesImport data from local files


 


For a feature deep dive, see Import data from local files with Power Query in Excel for Mac on the Office Insider Blog.


 


Sharing your feedback


Let us know what you think.  Send us your feedback via Send a Smile/Frown button at the top right.


 


Also, subscribe to our Excel Blog and join our Excel Community to stay connected with us and other Excel fans around the world.


 

'Lock request time out period exceeded' when connecting to the database from SSMS

'Lock request time out period exceeded' when connecting to the database from SSMS

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



When you connect to the database using SQL Server Management Studio (SSMS), It will perform several queries to gather the information necessary for the user interface. If one of the queries performed by SSMS is blocked, you may face a lock time-out in the connection:


 


Thamires_Lemes_0-1621442395428.png

 


You can use the PowerShell script below, while attempting to connect, to identify the session that is causing the block:


 


Note: It is necessary to set values for the highlighted variables. It will create CSVs with the result of the queries in the $OutputFolder.


 









#Connect to SQL and run QUERY


$SQLServer = “”


$SQLDBName = “”


$SQLUsername = “”


$SQLPassword = “”


$OuputFolder = “C:”


 


#To get information about sessions that are blocking and being blocked


$SqlQuery = “SELECT current_timestamp as [CURRENT_TIMESTAMP]


       , DB_NAME(dtl.resource_database_id) AS database_name


       , req.session_id AS blocked_sessionID


       , ses.program_name blocked_programName


       , ses.host_name blocked_hostname


       , ses.login_name blocked_login


       , CASE ses.transaction_isolation_level


              WHEN 1 THEN ‘ReadUncomitted’


              WHEN 2 THEN ‘ReadCommitted’


              WHEN 3 THEN ‘Repeatable’


              WHEN 4 THEN ‘Serializable’


              WHEN 5 THEN ‘Snapshot’


       END blocked_isolation_level


       , REPLACE(REPLACE(sqltext.TEXT, CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS blocked_last_query


       , req.status AS [blocked_status]


       , req.command AS blocked_command


       , req.cpu_time AS blocked_cpuTime


       , req.total_elapsed_time AS blocked_totalElapsedTime


       , blocked_tran.transaction_id blocked_transaction_id


       , osw.blocking_session_id AS blocker_SessionID


       , blocker_ses.program_name blocker_programName


       , blocker_ses.host_name blocker_hostName


       , blocker_ses.login_name blocker_login


       , CASE blocker_ses.transaction_isolation_level


              WHEN 1 THEN ‘ReadUncomitted’


              WHEN 2 THEN ‘ReadCommitted’


              WHEN 3 THEN ‘Repeatable’


              WHEN 4 THEN ‘Serializable’


              WHEN 5 THEN ‘Snapshot’


       END blocker_isolation_level


       , REPLACE(REPLACE(iif(blocker_sqltext.TEXT is NULL,blocker_sqltext2.event_info,blocker_sqltext.TEXT), CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS blocker_last_query


       , blocker_req.status AS [blocker_status]


       , blocker_req.command AS blocker_command


       , blocker_req.cpu_time AS blocker_cpuTime


       , blocker_req.total_elapsed_time AS blocker_totalElapsedTime


       , blocker_proc.lastwaittype blocker_last_waittype


       , blocker_proc.last_batch blocker_last_batch


       , blocker_proc.open_tran blocker_open_tran


       , blocker_tran.transaction_id blocker_transaction_id


       , blocker_proc.cmd blocker_command


       , dtl.request_mode AS lockRequestMode


       , dtl.resource_type AS lockResourceType


       , dtl.resource_subtype AS lockResourceSubType


       , osw.wait_type AS taskWaitType


       , osw.resource_description AS taskResourceDescription


       , osw.wait_duration_ms


FROM sys.dm_exec_requests req


INNER JOIN sys.dm_exec_sessions ses on ses.session_id = req.session_id


CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext


INNER JOIN sys.dm_tran_locks dtl on dtl.request_session_id = req.session_id


INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id


LEFT JOIN sys.dm_tran_session_transactions blocked_tran on blocked_tran.session_id =req.session_id


INNER JOIN dbo.sysprocesses blocker_proc on osw.blocking_session_id = blocker_proc.spid


LEFT JOIN sys.dm_exec_requests blocker_req on blocker_req.session_id = osw.blocking_session_id


LEFT JOIN sys.dm_exec_sessions blocker_ses on blocker_ses.session_id = osw.blocking_session_id


LEFT JOIN sys.dm_tran_session_transactions blocker_tran on blocker_tran.session_id =osw.blocking_session_id


OUTER APPLY sys.dm_exec_sql_text(blocker_req.sql_handle) AS blocker_sqltext


OUTER APPLY sys.dm_exec_input_buffer(osw.blocking_session_id,0) as blocker_sqltext2;”


 


#To get the locks that are being held by the sessions that are blocking and being blocked


$SqlQuery2 = “


select DB_NAME(locks.resource_database_id) AS database_name


 , locks.request_session_id


 , locks.resource_type, locks.resource_subtype


 , locks.resource_description


 , locks.resource_associated_entity_id


 , locks.resource_lock_partition


 , locks.request_mode


 , locks.request_type


 , locks.request_status


 , locks.request_reference_count


 , locks.request_lifetime


 , locks.request_exec_context_id


 , locks.request_request_id


 , locks.request_owner_type


FROM sys.dm_exec_requests req


INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id


INNER JOIN sys.dm_tran_locks locks on osw.blocking_session_id = locks.request_session_id or (osw.session_id = locks.request_session_id and osw.blocking_session_id is not null)


order by locks.request_session_id;”


 


#Dummy query to test if queries are running successfully, in case the previous ones do not return data


$SqlQuery3 = “select CURRENT_TIMESTAMP as timestamp, @@SERVERNAME as server_name, DB_NAME() as database_name, @@SPID as session_id;”


 


#Connect to SQL Server


$SqlConnection = New-Object System.Data.SqlClient.SqlConnection


$SqlConnection.ConnectionString = “Server = $SQLServer; Database = $SQLDBName; User ID = $SQLUsername; Password = $SQLPassword”


 


$SqlCmd = New-Object System.Data.SqlClient.SqlCommand


$SqlCmd.Connection = $SqlConnection


 


#Create the objects that will be used to run the queries


$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter


 


#Run 1st query


$DataSet = New-Object System.Data.DataSet


$SqlCmd.CommandText = $SqlQuery


$SqlAdapter.SelectCommand = $SqlCmd


$SqlAdapter.Fill($DataSet)


 


#Output RESULTS of 1st query to CSV


$DataSet.Tables[0] | Export-Csv -Delimiter ‘;’ -Path “$OuputFolderlock_waits_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv” -NoTypeInformation


 


#Run 2nd query


$DataSet = New-Object System.Data.DataSet


$SqlCmd.CommandText = $SqlQuery2


$SqlAdapter.SelectCommand = $SqlCmd


$SqlAdapter.Fill($DataSet)


 


#Output RESULTS of 2nd query to CSV


$DataSet.Tables[0] | Export-Csv -Delimiter ‘;’ -Path “$OuputFolderlock_list_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv” -NoTypeInformation


 


#Run 3rd query


$DataSet = New-Object System.Data.DataSet  


$SqlCmd.CommandText = $SqlQuery3


$SqlAdapter.SelectCommand = $SqlCmd


$SqlAdapter.Fill($DataSet) 


 


#Output RESULTS of 3rd query to CSV


$DataSet.Tables[0] | Export-Csv -Delimiter ‘;’ -Path “$OuputFoldertest_connection_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv” -NoTypeInformation


 


#Close the connection


$SqlConnection.Close()


 



 


In the CSV files, you should be able to see the last queries of the sessions involved in the block, their isolation level, if they are inside a transaction and the locks they are holding. This should help you understand why the block is happening.


 


This PowerShell script uses two queries from this blog post. Please refer to this blog post for additional information.


 


References:


https://techcommunity.microsoft.com/t5/azure-database-support-blog/troubleshooting-high-lock-wait-time-and-lock-time-out/ba-p/2368875


 


Experiencing Alerting failure for Log Search Alerts – 05/19 – Resolved

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

Final Update: Wednesday, 19 May 2021 15:58 UTC

We’ve confirmed that all systems are back to normal with no customer impact as of 05/19, 15:45 UTC. Our logs show the incident started on 05/19, 14:30 UTC and that during the 1 hour 15 minutes that it took to resolve the issue customers may have experienced issues with missed or delayed alerts in Fairfax.
  • Root Cause: Engineering team has determined that part of the backend workflow for processing Log Search Alerts became unhealthy after it had reached an operational threshold.
  • Incident Timeline: 1 Hour & 15 minutes – 05/19, 14:30 UTC through 05/19, 15:45 UTC

We understand that customers rely on Log Search Alerts as a critical service and apologize for any impact this incident caused.

-Vincent


Cisco Releases Security Updates for Multiple Products  

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

Cisco has released security updates to address vulnerabilities in multiple Cisco products. A remote attacker could exploit some of these vulnerabilities to take control of an affected system. For updates addressing lower severity vulnerabilities, see the Cisco Security Advisories page.

CISA encourages users and administrators to review the following Cisco advisories and apply the necessary updates:

Troubleshooting high lock wait time and lock time-out

Troubleshooting high lock wait time and lock time-out

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



 


Locks are held in the database to ensure data consistency. If there are errors due to lock time-out or performance issues due to lock waits, the recommendation is to review the transactions that are involved in the locks to check if it is possible to change anything in their logic or isolation level to improve concurrency and avoid the blocks. Blocks can also be caused by transactions taking longer than expected and, in this case, it may require query tuning.


 


We can use T-SQL while the issue is happening to identify the queries involved in the block and the applications that are running them.


 


With the query below, we can see the sessions involved in the block, last query they ran, and which one is the head of the block. It might already give us enough information to identify where the issue is:


 









–Blocking tree


 


SET NOCOUNT ON
GO
SELECT SPIDBLOCKEDREPLACE (REPLACE (T.TEXTCHAR(10), ‘ ‘), CHAR (13), ‘ ‘ ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLET
GO
WITH BLOCKERS (SPIDBLOCKEDLEVELBATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE (‘0’4LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHARAS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED OR BLOCKED SPID)
AND EXISTS (
SELECT FROM #T R2 WHERE R2.BLOCKED R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL RIGHT (CAST ((1000 R.SPIDAS VARCHAR (100)), 4AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED BLOCKERS.SPID WHERE R.BLOCKED AND R.BLOCKED <> R.SPID
)
SELECT N’    ‘ REPLICATE (N’|         ‘LEN (LEVEL)/– 1) +
CASE WHEN (LEN(LEVEL)/– 10
THEN ‘HEAD –  ‘
ELSE ‘|——  ‘ END
CAST (SPID AS NVARCHAR (10)) + N’ ‘ BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO



 


If the information provided by the last query is not enough and we have to look deeper into the sessions that are part of the block, we can run the query below:


 









–Details about the sessions that are blocking and being blocked:


 


SELECT current_timestamp as [CURRENT_TIMESTAMP]


       , DB_NAME(dtl.resource_database_id) AS database_name


       , req.session_id AS blocked_sessionID


       , ses.program_name blocked_programName


       , ses.host_name blocked_hostname


       , ses.login_name blocked_login


       , CASE ses.transaction_isolation_level


              WHEN 1 THEN ‘ReadUncomitted’


              WHEN 2 THEN ‘ReadCommitted’


              WHEN 3 THEN ‘Repeatable’


              WHEN 4 THEN ‘Serializable’


              WHEN 5 THEN ‘Snapshot’


       END blocked_isolation_level


       , REPLACE(REPLACE(sqltext.TEXT, CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS blocked_last_query


       , req.status AS [blocked_status]


       , req.command AS blocked_command


       , req.cpu_time AS blocked_cpuTime


       , req.total_elapsed_time AS blocked_totalElapsedTime


       , blocked_tran.transaction_id blocked_transaction_id


       , osw.blocking_session_id AS blocker_SessionID


       , blocker_ses.program_name blocker_programName


       , blocker_ses.host_name blocker_hostName


       , blocker_ses.login_name blocker_login


       , CASE blocker_ses.transaction_isolation_level


              WHEN 1 THEN ‘ReadUncomitted’


              WHEN 2 THEN ‘ReadCommitted’


              WHEN 3 THEN ‘Repeatable’


              WHEN 4 THEN ‘Serializable’


              WHEN 5 THEN ‘Snapshot’


       END blocker_isolation_level


       , REPLACE(REPLACE(iif(blocker_sqltext.TEXT is NULL,blocker_sqltext2.event_info,blocker_sqltext.TEXT), CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS blocker_last_query


       , blocker_req.status AS [blocker_status]


       , blocker_req.command AS blocker_command


       , blocker_req.cpu_time AS blocker_cpuTime


       , blocker_req.total_elapsed_time AS blocker_totalElapsedTime


       , blocker_proc.lastwaittype blocker_last_waittype


       , blocker_proc.last_batch blocker_last_batch


       , blocker_proc.open_tran blocker_open_tran


       , blocker_tran.transaction_id blocker_transaction_id


       , blocker_proc.cmd blocker_command


       , dtl.request_mode AS lockRequestMode


       , dtl.resource_type AS lockResourceType


       , dtl.resource_subtype AS lockResourceSubType


       , osw.wait_type AS taskWaitType


       , osw.resource_description AS taskResourceDescription


       , osw.wait_duration_ms


FROM sys.dm_exec_requests req


INNER JOIN sys.dm_exec_sessions ses on ses.session_id = req.session_id


CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext


INNER JOIN sys.dm_tran_locks dtl on dtl.request_session_id = req.session_id


INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id


LEFT JOIN sys.dm_tran_session_transactions blocked_tran on blocked_tran.session_id =req.session_id


INNER JOIN dbo.sysprocesses blocker_proc on osw.blocking_session_id = blocker_proc.spid


LEFT JOIN sys.dm_exec_requests blocker_req on blocker_req.session_id = osw.blocking_session_id


LEFT JOIN sys.dm_exec_sessions blocker_ses on blocker_ses.session_id = osw.blocking_session_id


LEFT JOIN sys.dm_tran_session_transactions blocker_tran on blocker_tran.session_id =osw.blocking_session_id


OUTER APPLY sys.dm_exec_sql_text(blocker_req.sql_handle) AS blocker_sqltext


OUTER APPLY sys.dm_exec_input_buffer(osw.blocking_session_id,0) as blocker_sqltext2;


 



 


This query will provide information about the last queries on both sides, the isolation level of each and if they are inside a transaction.


 


Locks are held for the duration of the transaction (between commits/rollbacks), so the lock might be being held due to a previous query of the current transaction. Said that, there may be cases in which the block does not make sense when looking at both sessions last query, but it does when we look at the whole transaction. However, we cannot see the previous queries the transaction ran using T-SQL.


 


In the cases where the blocks are happening due to a lock acquired for a previous statement, we can use the query below to see which locks are being held by the sessions involved in the block. It won’t give us the previous queries in the transactions, but it might help us understand why the block is happening and assist in the investigation.


 









 


–Details about the locks that are being held by the sessions that are blocking and being blocked:


 


select DB_NAME(locks.resource_database_id) AS database_name


 , locks.request_session_id


 , locks.resource_type, locks.resource_subtype


 , locks.resource_description


 , locks.resource_associated_entity_id


 , locks.resource_lock_partition


 , locks.request_mode


 , locks.request_type


 , locks.request_status


 , locks.request_reference_count


 , locks.request_lifetime


 , locks.request_exec_context_id


 , locks.request_request_id


 , locks.request_owner_type


FROM sys.dm_exec_requests req


INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id


INNER JOIN sys.dm_tran_locks locks on osw.blocking_session_id = locks.request_session_id or (osw.session_id = locks.request_session_id and osw.blocking_session_id is not null)


order by locks.request_session_id;


 



 


For Azure SQL Database, if the database has auditing enabled in the Portal, we can find all the queries that were run by the transaction in the audit logs by filtering by session ID and transaction ID provided in the second query.


 


For both Azure SQL Database and Azure SQL Managed Instance, we can create an XEvents session to see which queries are being run by the applications and filter by the transaction ID and session returned by the second query.


 


Both XEvents and Auditing will only assist in the investigation of locks that happened after they were enabled/started.


 


Examples:


 


1. Using Extended Events:


 


For this example, I created an XEvents session in a Managed Instance for the events sql_statement_starting and sql_statement_completed filtering by the database name and adding the global fields sessionID and transaction_id. You may also want to include other global fields to help you in the investigation, such as client_app_name, client_hostname and username.


 


Note: For more information about XEvents, please refer to the extended events documentation.


 


After the Extended Events is active, we have a block in our database with the following blocking tree:


 


Thamires_Lemes_1-1621439559912.png


 


As you can see, the head (session 98) last statement was an alter in the table Person and It is blocking the session 97 (insert in the table Person), which we can understand just looking at the queries.


 


However, the session 97 last statement was an insert into the table Person and It is blocking the session 115, that is trying to insert into the table Person2. If it is a different table, how can they this block happen?


 


We can understand when we look at the information collected by the XEvents:


 


Thamires_Lemes_2-1621439585985.png


 


We see the session 97 ran 2 queries in this same transaction (same transaction_id) and the first one was an alter in the table Person2.


 


It may be difficult to find the information right away if there is high activity on the database, so you can filter by the session_id and transaction_id, provided by the second query in this article to find all the statements that were executed within the same transaction.


 


2.  Using Azure SQL DB Auditing


 


If you have auditing enabled for your Azure SQL DB server/database, you can use the audit log to see the statements that were run by the transaction(s).


 


If we have the same scenario as the previous example, but now in an Azure SQL DB with auditing enabled:


 


Thamires_Lemes_3-1621439607209.png


 


As you can see above, the head (session 102) last statement was an alter in the table Person and It is locking the session 104 (insert in the table Person), which we can understand just looking at the queries.


 


However, the session 104 which last statement was the insert into Person is blocking the session 100 that is trying to insert into the table Person2. If it is a different table, how can they this block happen?


 


If we open the audit log and add the columns session_id and transaction_id, we can see the session 104 ran in this same transaction (same transaction_id) an alter in the table Person2:


 


Thamires_Lemes_4-1621439622694.png


 


We cannot see in the audit log the statements that are blocked, because it only shows the statements that have been completed. We can use the session_id and transaction_id returned by the second query of this article to filter the audit information and see the previous queries ran by the transaction(s).


 


References:


https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/


Hang up on auto warranty robocalls

Hang up on auto warranty robocalls

This article was originally posted by the FTC. See the original article here.

Have you gotten a recorded phone message from “Susie” with the “Vehicle Service Department” calling about your vehicle warranty? That’s, like, so retro. But fanny packs, scrunchies, and tie dye are back — and so are vehicle warranty robocalls.

It starts when you pick up a call and hear a recorded message saying something about your vehicle’s manufacturer’s warranty. It might say that they’ve sent you several notices in the mail. They say you haven’t extended your warranty past the factory cut off, and they’re going to close your file soon if you don’t respond. And, you should press a number if you’re interested in renewing your warranty, or press a different number if you want to be removed from the list.

Slam on the brakes. This is an illegal robocall and likely a scam. The companies behind this type of robocall are not with your car dealer or manufacturer, and the “extended warranty” they’re trying to sell you is actually a service contract that often sells for hundreds or thousands of dollars. If you buy it, you may find that the contract doesn’t actually cover any problems you have with your car because of the restrictions in the fine print.

If you get one of these calls:

  • Hang up. Don’t engage with a company that reaches you with an illegal call.
  • Consider using an app or device to block unwanted calls. Learn more about how to do that.
  • Report the robocall at DoNotCall.gov.

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Conditional Access GPS-based named locations now in public preview

Conditional Access GPS-based named locations now in public preview

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

Today, I am excited to share how you can improve your Conditional Access policies and ensure compliance with data regulations thanks to the public preview of GPS-based named locations. This feature helps admins strengthen their security and compliance posture and allows them to restrict access to sensitive apps based on the GPS location of their users.


 


I have asked Olena Huang, a PM on the Identity team, to tell you more. Let us know what you think!


 


Alex Weinert


 


————————————-


 


Hello,


 


With the public preview of GPS-based named locations, admins can refine their Conditional Access policies by determining a user’s location with even more precision.  GPS-based named locations allow you to restrict access to certain resources to the boundaries of a specific country. Due to VPNs and other factors, determining a user’s location from their IP address is not always accurate or reliable. Leveraging GPS signals enables admins to determine a user’s location with higher confidence. This is especially helpful if you have strict compliance regulations that limit where specific data can be accessed.


 


When the feature is enabled, users will be prompted to share their GPS location via the Microsoft Authenticator app during sign-in.


 


 


Create a policy to allow or restrict access based off a user’s GPS location


There are two simple steps:



  1. Create a GPS-based named location.

  2. Create or configure Conditional Access with this named location.


You’ll first need to create a countries named location and select the countries where you want the policy to apply. Configure the named location to determine the location by GPS coordinates instead of by IP address.


 


Named Locations.png


 


 


Next, create a Conditional Access policy to restrict access to selected applications for sign-ins within the boundaries of the named location.


 


New.png


 


 


For more information, check out our admin documentation  or our Graph API documentation.


 


 


Test out the location-sharing experience


First, make sure you have the Microsoft Authenticator app installed and set up with your test account.


 


Next, try to access the files or data restricted by the Conditional Access policy.  You’ll be prompted to share your geolocation from the Authenticator app.


 


Contoso.png


 


The first time you encounter this prompt, you will need to grant location permissions to the Authenticator app.


 


 


iOS


IOS.png


 


Android


Android.png


 


For the next 24 hours, your location will be shared silently once per hour from that device, so you won’t keep getting notifications.


 


After 24 hours, you will be re-prompted when trying to access the same resource. However, you will not need to grant permissions again (unless you’ve disabled them).


 


Authenticator.png


 


 


If you have questions, check out our FAQ page.


 


We’d love to hear from you! Feel free to leave comments below or reach out to us on Twitter.


 


 


 


Learn more about Microsoft identity:


Best practices for using global navigation in the SharePoint app bar

Best practices for using global navigation in the SharePoint app bar

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

CroppedHeaderAppBar.png


 


Your tenant just got the new SharePoint app bar, and you are probably wondering how it should fit in with the rest of your intranet architecture. You may be asking yourself, what should be in the global navigation and what should be home site navigation? What if my home site is also a hub site? In this blog, we’ll share best practices on how to think about global navigation, how to align with existing home site and hub navigation, and how to prepare for the app bar. 


 


What’s the SharePoint app bar?


First, let’s review the SharePoint app bar. The SharePoint app bar is a fixed navigation experience across all modern SharePoint sites that provides quick access to the most important sites, news, and files as well as the organization’s global navigation.


 


The SharePoint app bar can be broken down into two main parts:



  • Global navigation – Enable and customize the global navigation tab to display universally relevant links and use audience targeting to surface important content to specific audiences.

  • Personalized content – The remaining tabs in the SharePoint app bar consist of My sites, My news, and My files and dynamically displays personalized content based on insights from Microsoft Graph.


SharePoint App BarSharePoint App Bar


 


 


 


SharePoint global navigation and Viva Connections


Earlier this year, Microsoft announced a new product offering called Microsoft Viva, an employee experience platform that brings together communications, knowledge, learning, resources, and insights. 


 


One of the four pillars of Microsoft Viva is Viva Connections which uses SharePoint and Microsoft Teams to engage and connect your organization on a whole new level. To take full advantage of Viva Connections for desktop, make sure your organization has a home site and enable global navigation in the SharePoint app bar. When global navigation is enabled, your organization’s most important intranet resources will display in Microsoft Teams. 


 


Re-thinking intranet wayfinding


Now that we’ve reviewed the basic concept behind the SharePoint app bar and global navigation, let’s explore how to re-think your organization’s intranet architecture to accommodate this new wayfinding resource.


 


Most intranet experiences begin “at the top” with a landing destination. This is the place where users go to catch up on the latest organizational news, find out about upcoming events, and access important resources. In SharePoint this top-level landing experience is called the home site. The home site is unlike all other SharePoint sites in the sense that it has many superpowers. The home site is a vital piece of a great intranet, but users need a more efficient option to navigate between intranet resources without having to go back to the home site first. That’s where global navigation comes in because it allows you to provide a consistent set of navigational links regardless of where the user is in the intranet. For example, let’s say the user is viewing the human resources site to confirm how many hours of vacation are available and also needs to view the current time-off request policy in the policies center. Instead of having to switch back and forth between sites, global navigation enables users to navigate to universally relevant resources (like HR policy) no matter their location in SharePoint.


 


Previously customers could achieve this using a SharePoint hub site and associating all other intranet sites to it. This approach is great too but it’s just starting point! As your organization grows, your intranet will need to scale too. Soon, you’ll realize that you need more and more hubs (families of related sites) and you’ll need to make decisions on what resources to prioritize.


 


Global navigation solves this issue by providing navigation across all sites. You can then choose to use SharePoint hub sites to group and sync branding, permissions and navigation of related sites based on your departments, divisions, regions, or portfolio.


 


SPAppBarDiagram.png


 


How to think about global navigation


So, what should you use global navigation for? From talking to many customers across the years, we’ve learned successful global navigation designs focus on the most important resources like:



  • The home site itself and other top hubs and departmental sites (for example, HR)

  • Popular destinations for resources like benefits, company policies, and how to get support

  • Links to line of business apps and custom applications

  • Content relevant to the daily job functions of people in your organization


What does this mean for the home site navigation? The home site navigation transitions to focus more on wayfinding inside the home site as well and other relevant (but not critical) resources.



  • Wayfinding inside the home site

  • Links to news from inside the organization

  • Link to news from outside the organization

  • Organizational profiles and stories

  • Leadership teams, divisions, and stakeholders

  • Topics of interest

  • Public social feeds


Now, hub navigation can focus on resources related to the hub topic. For example, a human resources hub can have associated sites for all the different sites like benefits, payroll, time-off requests, and more. If the hub is for a division or department, it will have associated sites linked as topic sites that talk about business strategy, planning, metrics, leadership, and all the related teams within that division or topic. Learn more about how to think about home site, hub, and global navigation from the product team.


 


Next, decide the source for global navigation


Now that you know which resources are ideal for global navigation, it’s time to enable this feature and pick the source. We’ve given you multiple options so you can determine what best fits your needs.


 


First, to enable and customize global navigation, your organization must have a home site. From the home site’s home page, select Settings and then Global navigation.


 


GlobalNavigationSettings.png


Then you can decide which source the global navigation should pull from, either the home site navigation or the hub navigation (whether it’s officially a hub or not). Now for some organizations, this decision depends on how you want your home site navigation experience to look like, so here are some tips:



  • If you want global navigation to match the home site navigation, select the Home site navigation as the source. Then, decide to display or hide the site navigation on the home site

  • If you want global navigation to be different from the home site navigation, select Hub or global navigation (even if your home site is not a hub).

  • If the home site is already a hub, you can select either navigation source, but we recommend using hub navigation and hiding the site navigation to simplify the navigation experience.

  • Finally, if the home site is a hub and you’re using the extended header style, note that the site navigation automatically becomes hidden.


 


Example of global navigation at Microsoft


At Microsoft, our home site is also a hub site because there are multiple sites that power the Microsoft Web intranet experience from various news resources to a leadership connection site and more. For Microsoft’s global navigation, the home site navigation is the source and is hidden from the user interface on the home site.


The SP App bar used on Microsoft's intranetThe SP App bar used on Microsoft’s intranet


 


Enable and customize global navigation today


The SharePoint app bar is now available to most SharePoint customers. If you already have a SharePoint home site, you are ready to enable and set up global navigation. Next, integrate your SharePoint intranet with Microsoft Teams by using Viva Connections for desktop.


 


If you do not already have the SharePoint home site, now is a great time to plan and create a home site for your organization. Consider getting a head start on your home site by using a template named The Landing from the SharePoint look book. Learn  more from the Microsoft product team on how to think about and plan home sites.


 


We hope you find this information useful and that it provides further clarity on you should think about leveraging global navigation for both SharePoint and Viva Connections.


 


More resources


Learn more about information architecture in SharePoint


Onboard end-users to the SharePoint app bar


Check out the Viva Connections desktop experience


Watch: Architecting your intelligent intranet

Internet Explorer 11 desktop app retirement FAQ

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

The future of Internet Explorer (“IE”) on Windows 10 is in Microsoft Edge. What does this mean for commercial organizations, IT admins, developers, and end users? Microsoft Edge brings you a faster, more secure, and more modern web experience than Internet Explorer. Also, Microsoft Edge with Internet Explorer mode (“IE mode”), is the only browser with built-in compatibility for legacy IE-based sites and apps.

As announced today, Microsoft Edge with IE mode is officially replacing the Internet Explorer 11 desktop application on Windows 10. As a result, the Internet Explorer 11 desktop application will go out of support and be retired on June 15, 2022 for certain versions of Windows 10.



Which platforms will be affected when the IE11 desktop application is retired and goes out of support on June 15, 2022?


In scope at the time of this announcement (will be retired):



  • Internet Explorer 11 desktop application delivered via the Semi-Annual Channel (SAC):

    • Windows 10 client SKUs (version 20H2 and later)

    • Windows 10 IoT (version 20H2 and later)




Out of scope at the time of this announcement (unaffected):



  • Internet Explorer mode in Microsoft Edge

  • Internet Explorer platform (MSHTML/Trident), including WebOC

  • Internet Explorer 11 desktop application on:

    • Windows 8.1

    • Windows 7 Extended Security Updates (ESU)

    • Windows 10 Server SAC (all versions)

    • Windows 10 IoT Long-Term Servicing Channel (LTSC) (all versions)

    • Windows 10 Server LTSC (all versions)

    • Windows 10 client LTSC (all versions)




What about Windows 10 LTSC and Windows Server?


In-market Windows 10 LTSC and Windows Server are out of scope (unaffected) for this change.


What if Microsoft Edge is already installed?


Great! You already have a faster, more secure, and more modern browser than Internet Explorer and have completed some of the steps to help with your migration. If you’re an organization, the next steps will be to determine if your organization has legacy browser dependencies. To enable legacy browser support in Microsoft Edge, you’ll need to set up Internet Explorer mode. Learn more on our Internet Explorer mode webpage and read the Getting Started guide.


What does this announcement mean for my organization?


If your organization has legacy apps and sites dependent on IE11, you can follow the Getting Started Guide to start configuring IE mode. You may be concerned about change management, so please check out the Internet Explorer Retirement Adoption Kit for ready-made content to help you notify users and leaders in your organization about the upcoming changes and help move them to Microsoft Edge.


What does this announcement mean for developers?


Apps developed for IE should work in Microsoft Edge through IE mode. If you encounter an issue, contact App Assure for remediation assistance (ACHELP@microsoft.com).


For developers working on modern websites or applications, we understand that it has been increasingly difficult to support Internet Explorer side-by-side with modern browsers. While this announcement will start the transition of moving users from Internet Explorer to the more modern Microsoft Edge browser, it will take time and we recommend that you develop a plan to end support for Internet Explorer. Read this Moving users to Microsoft Edge from Internet Explorer article to learn how we can help.


What does this announcement mean for end users?


Microsoft Edge offers a faster, more secure, and modern browsing experience than Internet Explorer, and a growing number of websites no longer support Internet Explorer. After the Internet Explorer desktop application is retired on June 15, 2022, it will be out of support. After this date, the IE11 desktop application will be disabled and will redirect to Microsoft Edge if a user tries to access it.


If a user encounters a broken website that requires IE11, they should open it in IE mode. They can open websites that require Internet Explorer without leaving Microsoft Edge. Learn more about Internet Explorer mode in Microsoft Edge.


What is the MSHTML (Trident) engine? How does that relate to IE mode?


The MSHTML (Trident) engine is the underlying platform for Internet Explorer 11. This is the same engine used by IE mode and it will continue to be supported (in other words, unaffected by this announcement). WebOC will also continue to be supported. If you have a custom or third-party app that relies on the MSHTML platform, you can expect it to continue to work. For future app development, we recommend using WebView2.


How long will IE mode be supported?


IE mode support follows the lifecycle of Windows client, Server, and IoT releases at least through 2029. Additionally, Microsoft will give one year of notice before retiring the IE mode experience when the time comes. Windows support dates are documented on the Product Lifecycle page. Some editions of Windows may require an ESU license, if available, to receive operating system security updates beyond end of support dates. End of service dates for currently supported versions of Windows are as follows:






























































Platform



Windows release



End of service 



Windows client 



Windows 10 Enterprise, version 20H2 



5/9/2023



Windows 10 Enterprise, version 2004 



12/14/2021



Windows 10 2019 LTSC 



1/9/2029



Windows 8.1



1/10/2023



Windows 7 (ESU required) 



1/10/2023



Windows Server 



Windows Server, version 20H2 (SAC) 



5/10/2022



Windows Server, version 2004 (SAC) 



12/14/2021



Windows Server 2019 (LTSC) 



1/9/2029



Windows IoT 



Windows 10 IoT Enterprise, version 20H2 



5/9/2023



Windows 10 IoT Enterprise, version 2004 



12/14/2021



Windows 10 IoT 2019 LTSC 



1/9/2029



Windows Server IoT 2019 



1/9/2029



 


If I reach out to Microsoft for an exception to this timeline, can I continue to use the Internet Explorer 11 desktop application after June 15, 2022?


Microsoft Edge provides a dual engine advantage of Internet Explorer mode for compatibility with legacy websites and the Chromium project–the technology that powers many of today’s browsers–for world-class compatibility and performance with modern websites.


As such, we’re not allowing exceptions or providing extended support to continue using the IE11 desktop application on the in-scope platforms after June 15, 2022.


For those using IE11 at home, you can run IE mode in Microsoft Edge by following the steps outlined on this support page: Internet Explorer mode in Microsoft Edge.


Commercial IT pros will need to set up IE mode in Microsoft Edge to enable access to legacy IE-based sites and apps for their commercial users. To set up IE mode, use the resources in the Getting Started guide.


Supporting IE mode through at least 2029 is not long enough. Can I get an extension?


IE mode will continue to be supported through at least 2029 and Microsoft will give one year notice before deprecating the IE mode experience in-market when the time comes.


What IE functionality is available in IE mode?


IE mode supports all document and enterprise modes, Active X controls (such as Java or Silverlight), and more. For a list of what is supported and what is not supported, see the What is Internet Explorer (IE) mode Docs page.


Are there any changes to the Microsoft Edge lifecycle?


There are no changes to the Microsoft Edge lifecycle. Microsoft Edge continues to be supported. For more details, please visit the Microsoft Edge Lifecycle page.


How do I set up Internet Explorer mode in my organization?


You can get detailed guidance on how to set up Internet Explorer mode through our Getting Started guide or by visiting our IE mode documentation.


Will the Internet Explorer 11 desktop application be removed from devices? 


No. The IE11 desktop application will not be removed from devices, as the IE11 engine is required for IE mode to function. However, after the IE11 desktop application is retired on June 15, 2022, it will be disabled permanently. 


Will iexplore.exe be removed from devices?


No, but if a user tries to access it, they will be unable to open IE11 and will be redirected to Microsoft Edge.


If my browser default isn’t Internet Explorer 11, will the retirement affect my browser default?


No, this retirement will only change your browser default if your default had been set to Internet Explorer 11. If IE11 is set as your browser default, you will now have Microsoft Edge.


Will Internet Explorer-based sites and apps open automatically in Microsoft Edge after the Internet Explorer 11 desktop application is retired on June 15, 2022?


After the IE11 desktop application is retired, IE11 will redirect to Microsoft Edge. To open Internet Explorer-based websites and apps, you will need to either set up Internet Explorer mode (as an organization) or enable Internet Explorer mode (as a consumer at home).


If you’re an organization, you can set up IE mode using the Getting Started guide.


If you are an end user, you can enable IE mode by following the steps in this Internet Explorer mode in Microsoft Edge support article.


What if some of my sites don’t work in Microsoft Edge using Internet Explorer mode? How do I get help for website compatibility issues?


If you’re an organization and experience compatibility issues such as an error loading a site, please connect with the App Assure team for remediation assistance. You can submit a request for assistance through their website or reach out via email (ACHELP@microsoft.com).


If you’re a consumer at home and encounter an error loading a page, try loading it in IE mode by following the instructions in this Internet Explorer mode in Microsoft Edge support article. If the issue persists, please notify us by sending feedback through the in-product feedback tool found in the three-dot settings menu under ‘Help and feedback’ or by using the shortcut Alt + Shift + I. When submitting feedback, please check the box to ‘Send diagnostic data’.


Will the IE Group Policies work in IE mode?


We are committed to have IE Group Policies work in IE mode. If for any reason you encounter an issue, please connect with us at AppAssure for assistance. You can submit a request for assistance through their website or reach out via email (ACHELP@microsoft.com).




Continue the conversation. Find best practices. Visit the Windows Tech Community.


Stay informed. For the latest updates on new releases, tools, and resources, stay tuned to this blog and follow us @MSWindowsITPro on Twitter.