TMG SP1 Troubleshooting: Reporting
Introduction
We encourage you to enhance this guide by identifying missing areas (scenarios, features, lifecycle...), provide links to and write descriptions of existing content, and providing new content where there are gaps. Join the community!
This Wiki Article should provide a summary of issues we ran into with TMG Reporting since the installation of TMG SP1.
Scenario 1
Reporting doesn't work, when you check the TMG Alerts and/or Application Event Logs, you find error codes 0x80040E14 and 0x80040e4d when summary files had to be created.
There's a good article covering the steps you need to do in case you see the error 0x80040e4d here htttp://blogs.technet.com/b/isablog/archive/2010/10/05/tmg-reports-stop-working-after-installing-tmg-2010-sp1.aspx
As described in the article, running the fixSQLServerLogin.vbs from http://technet.microsoft.com/en-us/library/ff717843.aspx will solve the issue in most cases.
Please be aware, that this script had been intended for use in scenarios where you planned to uninstall the SP1. For the case where you want to fix the error above, we encourage you to run a slightly modified version of the script (see below). This script will not remove the Softblock feature from being logged in the database:
====Script Begin =============================================================
Dim adStateOpen
adStateOpen = 1
Set TypeLib = CreateObject("Scriptlet.TypeLib")
wscript.echo "Type a new password to access the reporting database, and record it for later use. If you run this script on another array member, be sure to use the same password."
newPassword = wscript.StdIn.ReadLine
Dim newConfigId
newConfigId = TypeLib.Guid
newConfigId = Left(newConfigId, Len(newConfigId)-2)
set root = CreateObject("FPC.Root")
On Error Resume Next
currStorageName = root.ConfigurationStorageServer
On Error Goto 0
If len(currStorageName) = 0 Then
wscript.echo "Please enter credentials to connect to configuration storage."
wscript.echo "Computer name of the EMS or Array Manager:"
ServerName = WScript.StdIn.ReadLine
wscript.echo "Domain:"
DomainName = WScript.StdIn.ReadLine
wscript.echo "User name:"
UserName = WScript.StdIn.ReadLine
wscript.echo "Password:"
Password = WScript.StdIn.ReadLine
root.ConnectToConfigurationStorageServer ServerName, UserName, DomainName, Password
Wscript.echo "Connected to '" & ServerName & "' with user '" & DomainName & "\ & UserName & "'"
Else
Wscript.echo "Connected to configuration storage with the current user's credentials."
End If
Set arr = root.GetContainingArray
arr.Reports.ReportingServicesProperties.Credentials.Password = newPassword
arr.Reports.ReportingServicesProperties.ReportingServicesConfigurationId = newConfigId
wscript.echo "Changing Reporting Services Password (" & newPassword & "), and configuration id (" & newConfigId & ")"
arr.Save
wscript.echo "Waiting for reload on array " & arr.name
arr.WaitForReload
wscript.echo "Done reloading array " & arr.name
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source='localhost\MSFW';Integrated Security=SSPI"
cnn.Open
If cnn.State <> adStateOpen Then
wscript.echo "Failed to open SQL connection: " & cnn.ConnectionString
End If
Dim sqlCommand
sqlCommand = "use master;"
sqlCommand = sqlCommand & " DECLARE @sys_usr varchar(100);"
sqlCommand = sqlCommand & " SET @sys_usr = SYSTEM_USER;"
sqlCommand = sqlCommand & " EXEC sp_addlinkedsrvlogin 'RS_SRV', 'false', @sys_usr , 'ISA_RS_USER', '" & newPassword & "'"
wscript.echo "Will execute " & sqlCommand
Set rs = cnn.Execute(sqlCommand)
cnn.Close
========Script End ============================================================
Scenario 2:
On non-English OS and/or TMG installations, we encountered issues, where reporting failed after the installation of SP1. The issue is already addressed and will be fixed in the upcoming releases.
For the case you're currently encountering this issue, please contact Microsoft Support, or use the following workaround:
Configure the default language for the NETWORK SERVICE account in SQL to Swedish, using the following command:
Sqlcmd –E –S localhost\ISARS
sp_defaultlanguage 'NT AUTHORITY\NETWORK SERVICE',’Swedish’
exec
Sqlcmd –E –S localhost\MSFW
sp_defaultlanguage 'NT AUTHORITY\NETWORK SERVICE',’Swedish’
exec
This change shouldn't affect any other system services on the server, it's "only" changing the language settings for the NETWORK SERVICE account in the specified SQL instance.
We've also seen some cases where changing the system locale to English also helped to solve the issue.
Scenario 3:
We've seen issues with reporting, when the configuration of sql reporting services failed during the installation or when changing the reporting node in TMG.
What you usually see is that the following Regkey:
HKLM\IsaStg_Cache\Arrays\Array-GUID}\Reports\ReportingServicesProperties\msFPCReportingServicesConfigurationId
doesn't match the output of SQL command:
Sqlcmd -E -S .\ISARS -Q "Select * From isa_rs_db..tblversionconfig"
To resolve the issue it usually helps to trigger a reinitialization of the configuration by dropping the Table isa_rs_db..tblversionconfig using the command
SQLCMD -E -S .\ISARS -Q "DROP Table isa_rs_db..tblversionconfig"
Afterwards creating a 'fake' rule im TMG or simply changing a description of a rule, this should trigger a reconfiguration.
Scenario 4:
When you execute netstat -ano we could see, that there was no listener on 127.0.0.1:8008 on the Reporting Server in the TMG array. This socket is usually used by Reporting Services on TMG, and needs to be allocated by RS.
When we analyzed the Reporting services logs, we found the following error, which is logged when RS starts:
rshost!rshost!1844!04/07/2011-12:05:19:: e ERROR: Failed to register url=http://127.0.0.1:8008/ReportServer_ISARS/ for endpoint 2, error=4d0. rshost!rshost!1844!04/07/2011-12:05:19:: w WARN: Endpoint 2 is enabled but no url is registered for vdir=/ReportServer_ISARS, pdir=C:\Program Files\Microsoft SQL Server\MSRS10.ISARS\Reporting Services\ReportServer. servicecontroller!DefaultDomain!424!04/07/2011-12:05:19:: e ERROR: Error creating HTTP endpoint. System.Runtime.InteropServices.COMException (0x800704D0): The network location cannot be reached. For information about network troubleshooting, see Windows Help. (Exception from HRESULT: 0x800704D0) at Microsoft.ReportingServices.HostingInterfaces.IRsUnmanagedCallback.CreateHttpEndpoint(RsAppDomainType application, String[] urlPrefixes, Int32 cPrefixes, String virtualDirectory, String filePath, Int32 authType, Int32 logonMethod, String authDomain, String authRealm, Boolean authPersist, Boolean enabled)
When we verified the http.sys iplisten settings, using netsh http show iplisten we found multiple IPs in the output, but none of them was 127.0.0.1. In a TMG default installation no IP is listened when you execute the command. Most likely some hardening had been run on the system, which added the IPs we could see. Once an IP is configured, it’s not possible for http.sys to bind sockets on not listed IPs.
We solved the issue by adding 127.0.0.1 to the list:
netsh http add iplisten ipaddress=127.0.0.1