Reporting Server Subscription does not run on Schedule

I've setup a SSRS report that pulls data from two SQL Server databases across two different SQL Server instances. It runs under a SQL Server user and works like charm when run manually. However, when I try to setup a Subscription for this report, the report never runs according to the schedule. I don't even get an error in the Reporting Services log.

In order to understand what is going on, I need to go back to the SQL Server Agent and check the logs there. When you create a Report Server Subscription on a Schedule it creates a new Job for this Schedule and this Job initiates the Event that triggers the scheduled report(s) to run.You can read more about it here.

First I need to identify what Job ID is behind the schedule I want to investigate. The Jobs are created using a Unique ID (GUID) and in order to link the actual report to its schedule ID I run this SQL on the Reporting Server Database (Normally called "ReportServer_INSTANCENAME"):

select s.Description, us.UserName, s.LastStatus, s.LastRunTime, c.Path, uc.UserName, rs.ScheduleId,
from ReportServer.dbo.Subscriptions s
join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
join ReportServer.dbo.Users uc on uc.UserID = c.ModifiedByID
join ReportServer.dbo.Users us on us.UserID = s.OwnerId

Going back to the Job, I can see from the Job History it doesn't even get to the first step of the Job. Since this is AX, the Job is run under the same account as the Business Proxy Account. That is how SSRS normally is configured in relation to AX. The error from the history log actually says "the owner of job does not have server access". The job is actually set to run in the context of a user that does have server access, but somehow (a bug maybe) this is ignored by the SQL Server Agent.

The solution is:

1. Change owner of the job to the SQL Server User that has the necessary permissions to run the report
2. Grant the SQL Server User permissions to operate on the the Report Server Database (It does needs permissions to inject data, however I gave it full db_owner permissions)
3. Test the job by doing a manual run

If the job still fails, you can investigate the Job history for any errors. 

Login failed error while processing OLAP cubes

I was requested to help with solving a processing error from SQL Server Analysis Services today.

While processing the engine fails to retrieve data from the Dynamics AX transaction database and throws the following error:
Login failed for user "DOMAIN\SERVERNAME$". Reason: Could not find a login mathcing the name provided (CLIENT: )"
(DOMAIN refers to the actual Active Directory Domain Name and SERVERNAME$ refers to the name of the server.

From the error it seems like the machine account is unable to login. Now in my scenario the SSAS service runs on the same server as the SQL Server Engine and the instance with the Dynamics AX database. In addition the SSAS service is NOT running using a dedicated Domain Service Account, but rather a local service account. The error then becomes sort of misleading if you read off the account name, because it is actually referring to machine account name.

The solution is simple, though.

Open the SQL Server Configuration Manager, find the Analysis Service you are using when processing the OLAP.

Open the properties and copy out the "Account name" which the service runs under. Normally this would be something like "NT Service\MSOLAP$INSTANCE_NAME" (INSTANCE_NAME refers to the name of the SSAS instance).

Now open SQL Server Management Studio, open Security and Logins. Add a new Login and paste in the Account name from previous step as the Login name.

Before you save this new login, open "User Mappings" and find the Dynamics AX database you are trying to use as source for your OLAP. Tick the checkbox in front of the database name and grant the login "db_datareader" role membership.

Now save the Login and jump back to the processing step and try run it again.