Unable to open database connection. (0x80004005: Login failed for user '(null)'. (0x80040114)
When launching Spotlight Report, the main windows displays the following error:
Unable to open database connection. (0x80004005: Login failed for user (null). Reason: Not associated with a trusted SQL Server connection.), Provider=SQLOLEDB.1;Server=ServerName;Database=SOE;Trusted_Connection=Yes;Current Language=English;Locale Identifier=1033
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 remotely when using the account that installed Spotlight 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, you may not want 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. Provided below more information regarding the different authentication that can be use.
The following Microsoft KB Articles were found and can assist with using Native Authentication,
IIS and SQL Server on Separate Machines with Trusted Connection (http://support.microsoft.com/default.aspx?scid=kb;en-us;176379)
Accessing SQL Server with Integrated Security from ASP (http://support.microsoft.com/default.aspx?scid=kb;en-us;176377)
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 below 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)
Included below are instructions for enabling IIS to use SQL Authentication; this has been successful tested.
1. Create an empty text file under C\Program Files\Quest Software\Spotlight called Spotlight.udl
2. Edit the SQL Connection string in the registry value HKLM\Software\Quest Software\Spotlight on Exchange 2.0\ClientDB\SQLConnect to reference the newly created UDL file. Change the value to, File Name=C:\Program Files\Quest Software\Spotlight
3. Double-click Spotlight.udl to invoke the Database Connection wizard. Select SQL Authentication with the user and password that has been configured in the database for access to the Spotlight database. The advanced tab will need to be selected to ensure that Current Language value is set to English. When finished, try the Test Connection Button to ensure that the connection works correctly.
4. Edit the ACL on the file to make access more secure to prevent unintended users from having read access to the file.
Configure the ACL with: Administrators [Full Control] SYSTEM [Full Control] Web Report Administrators [Read] Web Report Authors [Read] Web Report Users [Read]
This is a solution for protecting the SQL Username and Password, but it should limit the people who could read it to those having log on access to the box, or Administrators through the UNC administrative file share.