Jesper Arnecke

SQL and Deadlocks – What, where and when are they exactly?

Working on for a customer where we were having some issues with some deadlocks. We couldn’t quite get the details on the events for a subset of reasons. Foremost, because the SQL Ring buffer events was recycled within one and a half hour during peak loads.
So we created a powershell script, which was set to run as a scheduled task, taking all deadlock events in the ring buffer and pairing it with relevant queries, one hour backwards.

Basically this script, run as a scheduled task every hour, will output all your deadlocks, for the past hour. If your system is not generating that much Ring buffer events, you can throttle it down or even just run the SQL query once in order to collect this data. Its just important to note, that these Ring buffer events will be overwritten by the good grace of the SQL server.

# Get todays date in order to stamp the output file correctly.
$Date = (Get-date).ToString("yyyy-MM-dd")

# The selected SQL instance to connect to
$SQLInstance = '<Servername>\<Instance>'
$Outputfile= "<Local folder location>"+$date+"Deadlocks.log"

# SQL Query to be performed
$SQLQuery = $("
  (SELECT xevent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph,
          CAST (xevent.value('(event/@timestamp)[1]', 'nvarchar(30)') AS DATETIME) AS FullDate
     (SELECT xevent.query('.') AS XEvent
        (SELECT Cast(target_data AS XML) AS TargetData
         FROM sys.dm_xe_session_targets st
         JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
         WHERE s.NAME = 'system_health'
           AND st.target_name = 'ring_buffer') AS DATA CROSS apply targetdata.nodes ('RingBufferTarget/event[@name=""xml_deadlock_report""]') AS XEventData (xevent)) 
              AS src
WHERE RESULT.FullDate >= Dateadd(HOUR, -1, Getdate());

# Collect deadlock data every hour, using MaxCharLenght to get full output back. 
$DeadlockDataFromSQL=Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLInstance -MaxCharLength 10000 -QueryTimeout 0

# Output exception data to file using stream.
$stream = New-Object -TypeName System.IO.StreamWriter $OutPutFile, $true
foreach($line in $deadlockdataFROMSQL) 
      	$stream.WriteLine(($line.DeadlockGraph | Out-string))

Microsoft PKI with client certificate auto enrollment design specification

Next in the collection of design specifications is a Microsoft PKI with added client certificate auto enrollment settings.
A customer asked me if I could implement this and seeing as I’ve done a fair bit of Microsoft PKIs before, it didn’t seem like too much of a hassle. Of course I wanted it to be documented and shared with all of you.

All in all, it was a little less than 8 hours before client certificates started rolling out, without anyone noticing.

Microsoft PKI with client certificate auto enrollment design specification (located on Google drive)

Onedrive for Business Design Specification

Having worked on this document for quite some time I felt the need to publish it just about now. I see alot of attention on the Onedrive for business client as it has matured greatly over the past year and many national and international companies are looking towards what this solution can offer. The below design outlines a lot of these considerations, including network latency, legislation, features and of course a large collection of valuable links when considering the implementation of Onedrive for Business.
I will update document as my work brings me further into this area. Until then I hope you will enjoy this piece of work and benefit as much from it as I have.

Onedrive for Business Design Specification (located on Google drive)

Microsoft Cloud solution – Security, Auditing, Traceability and how do they react to data breach

You might have an interest in Microsoft cloud hosting security, auditing and / or data protection, just like me. I’ve collected a few good links, that should get you started.

First of, a little on how they react if breaches will happen, because they will at some point in some way. Major or minor…
How Would Microsoft Respond to a Data Breach of the Azure services?

Secondly, this white paper examines how Microsoft investigates, manages, and responds to security incidents within Azure.
Microsoft Azure Security Response in the Cloud

So how do they protect their physical data centers as well as your data and which certifications do they comply to?
Security, Audits, and Certifications

A little on their politics for how they manage and regard your data.
With Microsoft, you are the owner of your customer data.

And lastly a more wide perspective article.
5 questions every executive should be asking their security team

EICAR Standard Anti-Virus Test File

Recently a customer was testing antivirus scanning software, both on trafic and on servers/clients. This had to be tested on several environments, including production and really didnt want to use a real infected file. The following showed up, didn’t know about it, chances are there are others that doesnt know about this.

“The EICAR Standard Anti-Virus Test File or EICAR test file is a computer file that was developed by the European Institute for Computer Antivirus Research (EICAR) and Computer Antivirus Research Organization (CARO), to test the response of computer antivirus (AV) programs. Instead of using real malware, which could do real damage, this test file allows people to test anti-virus software without having to use a real computer virus.

Anti-virus programmers set the EICAR string as a verified virus, similar to other identified signatures. A compliant virus scanner, when detecting the file, will respond in exactly the same manner as if it found a harmful virus. Not all virus scanners are compliant, and may not detect the file even when they are correctly configured.

The use of the EICAR test string can be more versatile than straightforward detection: a file containing the EICAR test string can be compressed or archived, and then the antivirus software can be run to see whether it can detect the test string in the compressed file.”

EICAR test file – Wikipedia, the free encyclopedia

Steps to use it:
Create a .txt file on your drive, open your AV scanner software and create an exclusion on this file and location. Update the contents of the file with the referenced. Scanner software will not quarentine it with that name and location, however anywhere you move it, it should be detected and removed.

Configure Azure Application Proxy application for CRM Internet Facing Deployment

The task was to configure an existing CRM IFD, with an existing ADFS / Azure Application Proxy infrastructure.

CRM IFD deployment was already working and implemented for internal access. What was missing was the external access through Azure Application Proxy.
Following this guide:
With the following configuration will get you there:

Note that once you have the CRM Internet facing deployment done, no changes are required on CRM or ADFS. The below is only the Azure Application Proxy configuration required for CRM internet facing deployment.

Logon to and create a new Application under Active Directory.
Important configuration is:
– External URL: You can use the same URL as internally, however make sure that CNAME record is created as well as certificate is uploaded. This is referred to as custom domain.
– Preauthentication Method: Passthrough
– Translate URL in headers: No

You need to add required CRM IFD service url’s:
organisation, authentication and/or discovery service as seperate applications.