Application Insights not tracking sql queries

6
votes

I'm trying to configure my own environment to send data to App Insight with Status Monitor and works fine except sql queries.

I have one environment on Azure VM with Azure Database and the SQL queries are been tracking well, but in my own VM against my own Databases not working.

The sql trace recorded on that environment is like that: "mssql-IP | database-name" instead of query content.

The application tested in both environment is the same, the SO version is the same.

I tryed configure my environment against Azure Databases, but still the same problem.

I installed .NET 4.6 and still the same problem.

3
sql-serverazure-application-insights
Can confirm; I have a Server 2012 R2 hosted web app that is .NET 4.6.1 with AppInsights 2.1 installed (app pool is running with domain service account) and I am not getting SQL dependency detail data either. I have seen it work on other apps on other servers, but we have too many permutations to nail anything down (8 different web servers with 5-8 web apps on each, in various versions of .NET and various versions of App Insights SDK) – BrettJ

3 Answers

8
votes

NEW METHOD (Application Insights Agent, Formerly Status Monitor v2)

Note: A new PowerShell window/session is required when stated.

  1. Install prerequisites in an elevated PowerShell session.
  2. Open a new elevated PowerShell session.
  3. Install the agent module.

  4. Enable just the instrumentation engine. Open a new elevated PowerShell session and run the following:

    Enable-InstrumentationEngine

  5. Perform Step 2 for OLD METHOD (below).

  6. Restart IIS:

    iisreset

See more instructions.

OLD METHOD (Status Monitor v1)

After reviewing every scrap of documentation, GitHub issue, and blog post I could find on this problem, I believe I have the definitive answer for an IIS environment:

  1. Status Monitor needs to be installed to collect dependency details. This configures the necessary .NET profiler. Even if you have Application Insights configured at build time (i.e. not having Status Monitor configure Application Insights for you at runtime) and are using .NET 4.6+, Status Monitor still needs to be present. (In my opinion, the Microsoft documentation does not make this clear.)

  2. Correct permissions:

    CMD

    icacls %WINDIR%\Temp /t /c /grant IIS_IUSRS:(OI)(CI)M
icacls %WINDIR%\System32\config\systemprofile\AppData\Local /t /c /grant IIS_IUSRS:(OI)(CI)M

    or

    PowerShell

    icacls $env:WINDIR\Temp /t /c /grant IIS_IUSRS:'(OI)(CI)M'
icacls $env:WINDIR\System32\config\systemprofile\AppData\Local /t /c /grant IIS_IUSRS:'(OI)(CI)M'

  3. Correct IIS service environment variables:

    Get-Item 'HKLM:\SYSTEM\CurrentControlSet\Services\W3SVC' | Remove-ItemProperty -Name Environment -ErrorAction SilentlyContinue
Get-Item 'HKLM:\SYSTEM\CurrentControlSet\Services\W3SVC' | New-ItemProperty -Name Environment -Value "COR_ENABLE_PROFILING=1","COR_PROFILER={324F817A-7420-4E6D-B3C1-143FBED6D855}","MicrosoftInstrumentationEngine_Host={CA487940-57D2-10BF-11B2-A3AD5A13CBC0}" -PropertyType MultiString

  4. Restart IIS:

    iisreset
4
votes

You should verify Status Monitor-collected data is making its way to Application Insights.

In Analytics try the query:

dependencies | where timestamp > ago(1d) | summarize count() by cloud_RoleInstance, sdkVersion

If you see sdkVersion results prefixed with rddf instead of rddp, the SDK is sending data without the enhancements from Status Monitor (like SQL query content). If this is the case, check your installation of Status Monitor to ensure it's active for your application.

4
votes

I finally solved this problem.

I grant modify access to IIS_IUSRS group for these paths:

  • %WINDIR%\Temp
  • %WINDIR%\System32\config\systemprofile\AppData\Local

Then, Application Insights started to record SQL queries.

You also can do this via the command line:

  • icacls %WINDIR%\Temp /t /c /grant IIS_IUSRS:(OI)(CI)M
  • icacls %WINDIR%\System32\config\systemprofile\AppData\Local /t /c /grant IIS_IUSRS:(OI)(CI)M