Error "8004005 Access Denied" occurs when running reports. (4294403)

There are known issues with the way Native Authentication is being passed between the IIS Servers and the SQL Servers. Usually there is no issue authenticating remotely when using the account that installed Spotlight On Exchange and also running the reports on the IIS server. It is recommend either to use Kerberos, SQL Authentication or installing both IIS & SQL on the same system. Due to security issues, it may not be desirable to install both IIS and SQL on the same server. It is suggested to use SQL Authentication because the environment has to be in Native Mode Win2K with other settings to be configured for Kerberos and Basic Authentication sends passwords in clear text across the network. Below is more information regarding the different authentication that can be used.

The following Microsoft KB Articles can assist with using Native Authentication:

Article ID 176379 - How to set up IIS and SQL Server on separate machines with a trusted connection
http://support.microsoft.com/default.aspx?scid=kb;EN-US;176379

Article ID 176377 - Accessing SQL Server with Integrated Security from ASP
http://support.microsoft.com/default.aspx?scid=kb;en-us;176377

Article ID 176380 - How To Use ASP with a SQL Trusted Connection with Guest Account
http://support.microsoft.com/default.aspx?scid=kb;EN-US;176380

The following Microsoft KB Article can assist with enabling Kerberos Authentication:

How To: Implement Kerberos Delegation for Windows 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT05.asp

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.

This is an update to solution 13799 with how to create the SQL user in SQL 2005