Error while opening Web Reports for Archive Manager Reporting Pack: Login failed for SQL Server login 'Mstats'. The password for this login has expired
SELECT [AMServerName], COUNT(*) AS [_Count] FROM (SELECT * FROM (SELECT AMServerName, MailboxName, MessageID, [MsgSize(KB)], DateProcessed, [Date] FROM (SELECT TOP 1000 'xxx' AS AMServerName, Name AS MailboxName, OrigionalMessageID AS MessageID, Size / 1024.0 AS [MsgSize(KB)], DateProcessed, CONVERT(DATETIME, CONVERT(NVARCHAR, DateProcessed, 112), 112) AS [Date] FROM [xxx].[ArchiveManager].dbo.Message ME INNER JOIN [xxx].[ArchiveManager].dbo.MailboxMessage MAME ON MAME.MessageID = ME.MessageID INNER JOIN [xxx].[ArchiveManager].dbo.Mailbox MA ON MA.MailboxID = MAME.MailboxID ORDER BY DateProcessed DESC ) InnerQuery) DummyQuery) [_IntResult] WHERE (([AMServerName] IN (xxx))) GROUP BY [AMServerName] ORDER BY [AMServerName] ASC
(0x80040217)
There is a user account that is used to perform "linked server" queries from the MessageStats database to the Archive Manager database.
Users initially connecting to the Archive Manager server are required to type a username and password, within a UDL editor dialog box, from the "Connection" page.
The UDL editor in there requires the user to enter a user/password. MessageStats does not save that information anywhere and it just creates a linked server from the MessageStats server to the Archive Manager server.
1) Right-click on the Archive Manager server connection within MessageStats mmc console and make a note of the settings
2) Select "Disconnect"
3) Right-click the Archive Manager report packs main node, and select "Connect". Enter in all the same information that was found in Step 1), but this time use the updated password for that account. This account is located on the archive manager SQL server.
4) Open a report for Report Pack for Archive Manager. All data should still be there if all information is typed in exactly the same as in step 1.
Note: All of this depends on sorting out the user on the Archive Manager database. he user "Mstats" can be set to never have its password expire. If this solves the problem then disconnecting/reconnecting is not required.
