Enabling IIS to use SQL Authentication resolves this issue. This has been successfully tested and recommended.
Below are instructions for configuring SQL 2005 Authentication for SOE Web Reports:
Create a SQL Account on the SQL Server hosting the SOE Database and provide the account Database Access to SOE using the Public and db_dataaccess roles by doing the following:
1. Launch SQL Server Management Studio and connect to the SQL server where the SOE database resides.
2. Expand Security | Logins
3. Right-click Logins and select New Login.
4. Provide a name for the account, for example SOEWR.
5. Select SQL Server Authentication and provide a password, for example SOEWR.
- NOTE: If using a non complex password you will need to uncheck the Enforce password policy box
6. Ensure that the Enforce password expiration and User must change password at next login boxes are unchecked
7. Select User Mapping from Select a page section on the left hand side.
8. Check the Map box next to SOE in the users mapped to this login section on the top right
9. In the Database Role membership for section on the bottom right ensure that both Public and db_datareader are checked
10. Click OK
Perform the following steps on the SOE Web Reports System:
1. Using Windows Explorer locate WebReports.UDL found in <InstalledDrive>\Program Files\Quest Software\SpotlightOnExchange\ Click File | Open.
2. Select the Provider tab and confirm that Microsoft OLE DB Provider for SQL Server is selected/highlighted and click Next.
3. In step 1, provide the SQL Server name that hosts the SOE Database.
4. In step 2, select 'Use a specific user name and password' and provide the SQL Authentication Account created above with the password.
5. Check the check box to Allow saving password.
6. In step 3, Select the SOE database on the server from the drop down list.
- NOTE: For this to work your SQL server must be set to use both SQL Server Authentication and Windows Authentication
8. Click the Test Connection Button at the bottom to confirm connectivity to the SOE Database.
9. Click OK, then Yes to allow saving password local on system.
Launch SOE Web Reports to confirm that it's working successfully.