SQL Server Model Generation

If you have trouble generating models using Windows Authentication with SQL Server, you may see the following errors: “Login failed for user. The user is not associated with a trusted SQL Server connection.” or “The generation request failed. Unable to generate data source view.” These errors can occur when using Microsoft SQL Server Windows Authentication security if the ActiveReports Server Web Application Pool user or the ActiveReports Agent Windows Service user (for each agent) do not have permissions to the SQL Server database. Both users need access to the database for the following reasons.

App Pool User

In preparing to generate a model, the web application selects tables on which to base the model. So the web application’s App Pool user must have access to read the schema of the database.

ActiveReports Agent Service

The process of actually generating the model is delegated to an agent. So the agent (a Windows service) must have access to read the schema and data too. Even after the model is created, the Agent needs access to read data from the database in order to run reports. To change the ActiveReports Server web application pool user

1. Open Internet Information Server (IIS) Manager.
2. In the Connections pane, expand the node for the machine and click Application Pools.
3. In the list of Application Pools to the right, right-click AR_AppPool and select Stop.
4. Right-click AR_AppPool again and select Advanced Settings.
5. In the Advanced Settings dialog that appears, change the identity of the user to one with permission to access the database.
6. When you have finished making changes, right-click AR_AppPool again and select Start.

To change the user for each ActiveReports agent service Note that you must set this on each agent on every machine.

1. From the Start menu, in the Search programs and files box, open services.msc.
2. In the Services window that appears, right-click the ActiveReports Agent service and select Stop.
3. Right-click ActiveReports Agent again and select Properties.
4. In the Properties dialog that appears, on the Log On tab, set the user to one with permission to access the database.
5. When you have finished making changes, right-click ActiveReports Agent again and select Start.