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 MessageStats 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
WORKAROUND
Enabling IIS to use SQL Authentication resolves this issue. This has been successfully tested and recommended.
Below are instructions for configuring SQL Authentication for SOE Web Reports:
1. 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:
- Launch SQL Enterprise Manager.
- Expand Microsoft SQL Servers | SQL Server Group | (local) (Windows NT) | Security.
- Right-click Logins and select New Login.
- Provide a name for the account, for example SOEWR.
- Select SQL Server Authentication and provide a password, for example SOEWR.
- Select the Database Access tab.
- Check the check box beside the SOE Database under the top window.
- In the bottom window, confirm that Public & db_datareader have check marks in the boxes.
- Click OK and confirm the password to create the account.
2. Perform the following steps on the SOE Web Reports System:
- Using Windows Explorer locate the C:\Program Files\Quest Software\Spotlight\WebReports.UDL. file and open it.
- Select the Provider tab and confirm that Microsoft OLE DB Provider for SQL Server is selected/highlighted and click Next.
- In step 1, provide the SQL Server name that hosts the SOE Database.
- In step 2, select 'Use a specific user name and password' and provide the SQL Authentication Account created above with the password.
- Check the check box to Allow saving password.
- In step 3, Select the SOE database on the server from the drop down list.
- Click the Test Connection Button at the bottom to confirm connectivity to the SOE Database.
- Click OK, then Yes to allow saving password local on system.
3. Lauch SOE Web Reports to confirm that it's working successfully.