SQL Command text is not collected by App Insights for web app running on Cloud Service

Question

Followed standard installation guide from Microsoft

• Checked "Send diagnostics data to Application Insights" in role properties and published the app to CS - no request data collected at all.
• Added SDK to the project and re-uploaded the app - got request data, but all SQL Dependencies collected with totally useless "Command Text" - "server name | database name".

However, SQL Command are traced for the same app on my local IIS if Azure Status Monitor is installed.

I connected to the server via RDP and it seems that AI Status Monitor is installed successfully by Azure PaaS Diagnostics plugin, but not registered as COR_PROFILER for W3SVC, so it actually do nothing.

Is there any specific configuration switch exists to enable full SQL command tracing for cloud services?

Answer 1

It seems that enabling "Send diagnostics data to Application Insights" and adding SDK to the project should be enough to collect dependency data.

However, current installer of Status Monitor included in Azure Cloud Service PaaS Diagnostics Plugin v1.10.1.1 is broken. I tried installer from previous version of plugin (v.1.10.0.0) - and it works perfectly.

Running \StatusMonitor\ApplicationInsightsAgent.msi from latest version of plugin logs the following exception:

Calling custom action CustomActions!CustomActions.CustomActions.SetEnvironmentVariables
Error: could not load custom action class CustomActions.CustomActions from assembly: CustomActions
System.IO.FileLoadException: Could not load file or assembly 'CustomActions, Version=2.4.0.18059, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. Strong name validation failed. (Exception from HRESULT: 0x8013141A)
File name: 'CustomActions, Version=2.4.0.18059, Culture=neutral, PublicKeyToken=31bf3856ad364e35' ---> System.Security.SecurityException: Strong name validation failed. (Exception from HRESULT: 0x8013141A)

I unpacked that CustomActions.dll and checked strong name for it via sn.

sn.exe -vf CustomActions.dll

CustomActions.dll is a delay-signed or test-signed assembly

Same check for CustomActions.dll from v1.10.0.0 shows

Assembly 'CustomActions.dll' is valid

Apparently, someone in MS forgot to sign that dll before release, so Status Monitor is no longer correctly installed on CS VMs. Reported that to MS Support, hope they will fix that soon.

---

A workaround could be manually adding missing registry key on role start, while W3SVC is not started yet:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC\@Environment 

of type Multi-String Value with value

COR_ENABLE_PROFILING=1
COR_PROFILER={324F817A-7420-4E6D-B3C1-143FBED6D855}
MicrosoftInstrumentationEngine_Host={CA487940-57D2-10BF-11B2-A3AD5A13CBC0}

Raw reg file is:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC]
"Environment"=hex(7):43,00,4f,00,52,00,5f,00,45,00,4e,00,41,00,42,00,4c,00,45,\
  00,5f,00,50,00,52,00,4f,00,46,00,49,00,4c,00,49,00,4e,00,47,00,3d,00,31,00,\
  00,00,43,00,4f,00,52,00,5f,00,50,00,52,00,4f,00,46,00,49,00,4c,00,45,00,52,\
  00,3d,00,7b,00,33,00,32,00,34,00,46,00,38,00,31,00,37,00,41,00,2d,00,37,00,\
  34,00,32,00,30,00,2d,00,34,00,45,00,36,00,44,00,2d,00,42,00,33,00,43,00,31,\
  00,2d,00,31,00,34,00,33,00,46,00,42,00,45,00,44,00,36,00,44,00,38,00,35,00,\
  35,00,7d,00,00,00,4d,00,69,00,63,00,72,00,6f,00,73,00,6f,00,66,00,74,00,49,\
  00,6e,00,73,00,74,00,72,00,75,00,6d,00,65,00,6e,00,74,00,61,00,74,00,69,00,\
  6f,00,6e,00,45,00,6e,00,67,00,69,00,6e,00,65,00,5f,00,48,00,6f,00,73,00,74,\
  00,3d,00,7b,00,43,00,41,00,34,00,38,00,37,00,39,00,34,00,30,00,2d,00,35,00,\
  37,00,44,00,32,00,2d,00,31,00,30,00,42,00,46,00,2d,00,31,00,31,00,42,00,32,\
  00,2d,00,41,00,33,00,41,00,44,00,35,00,41,00,31,00,33,00,43,00,42,00,43,00,\
  30,00,7d,00,00,00,00,00