Unable to connect to the database. You may need to use SQL Authentication to connect to the database (4262266)

When launching MessageStats Reports, then main windows displays one of the following error messages:

"Error Unable to connect to the database"

"You may need to use SQL Authentication to connect to the database server. Please review the Installation Issues section of the MessageStats Release Notes.Login failed for user NT AUTHORITY\ANONYMOUS LOGON"

OR

"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=MessageStats;Trusted_Connection=Yes;Current Language=English;Locale Identifier=1033
(0x80040114)"

OR

"HTTP 500 Internal Server Error - ASP Error"

"QSWebWizard9.WebWizard.1 error 80004005
Unspecified Error
MessageStatsReports /ForcedAuthenticate.asp, line 31"

OR - The error below is displayed when Messagestats is installed on Windows 2008

"Error
Unable to connect to the database"

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 recommended either to use Kerberos, SQL Authentication or install both IIS and SQL on the same system. Due to security issues, it may not be desirable to install both IIS and SQL on the same server.

RESOLUTION 1 - Enabling IIS to use SQL Authentication

Below are instructions for configuring SQL Authentication for MessageStats Reports:

A. To be done on SQL Server hosting the MessageStats Database:

1. Create a Microsoft SQL Account on the SQL Server hosting the MessageStats Database
2. Grant the new SQL account Database Access to MessageStats by assigning the Public and MessageStats_Web.

B. To be done on the IIS Server hosting the MessageStats Reports:

1. Navigate to <Installed Directory>\Quest Software\MessageStats
2. Double-click QMSReports.udl to invoke the Data Link Properties page.
3. Select the Provider tab and confirm Microsoft OLE DB Provider for SQL Server has been selected.
4. Select the Connection tab and confirm the following:
   - In Step 1. Provide the SQL Server Name
   - In Step 2. Provide the SQL user and password that was created in Part A above and check off the"Allow saving password" check box
   - In Step 3. Select the MessageStats Database in the drop down box

• When finished, try the Test Connection Button to ensure that the connection works correctly. Once the connection has been confirmed, click ok and yes to save the password to the text file.

NOTE: If the SQL Server is using a non standard port add the port to the server name in the UDL file. When specifing the name use ServerName,(comma)Port Number. Example: DBSERVER,1112


NOTE: Windows Server 2008, Windows Server 2008 R2 or Windows Server 2012, Windows Server 2012 R2 USERS: To change the settings of the QMSReports.udl file you have to run the QMSReports.udl file with elevated permissions otherwise the changes are not saved properly.

This occurs because changes to the qmsReports.udl are saved in a per-user area of the file system instead of the per-computer location used by MessageStats Reports.

To avoid this issue, you must edit and save the file with elevated UAC (User Account Control) rights:

1. From the Start menu, right-click CMD.EXE, and select Run as Administrator.
2. At the command prompt, enter: cd <driveletter>:\Quest\MessageStats\ Start qmsReports.udl

NOTE: For Windows Server 2012 or 2012 R2 users, you may also notice that even though the changes to the QMSReports.UDL file are not being omitted when closing the UDL file,  you still cannot open Web Reports and get a "Windows NT\Anonymous" logon error.  This is because Server 2012 R2 UAC may show the UAC slider turned off, but in the registry the EnableLUA value is "1" which means UAC is on.

To avoid this issue, turn off UAC from the registry, and recreate the QMSReports.udl file:

1. Turn off UAC from the Registry on the Messagestats machine, open regedit and goto "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System"
2. Edit EnableLUA to be 0 instead of 1
3. Recreate the QMSReports.UDL file manually by right-clicking and select New Text Document and name it QMSReports.udl (ensure show known file extensions are enabled)
4. Open the UDL file and type in the credentials.

Here is also a Video that will show you how to edit the QMSReports.udl file so SQL Authenciation is enabled.

480 X 270 player for the Support Link Solution pages

RESOLUTION 2 - Configuring Kerberos Authentication

To configure Web Reports to use Kerberos Authentication follow this article:

https://support.quest.com/SolutionDetail.aspx?id=SOL114574

For more information about UAC Virtualization please see the following article:
http://msdn.microsoft.com/en-us/library/bb756960.aspx
 

The location of the UDL file is controlled by the following registry key:
HKLM\Software\Quest Software\MessageStats\ClientDB\SQLConnect

If it is necessary to lock down the UDL File do the following on the IIS Server hosting the MessageStats Reports

1. Edit the ACL on the file to prevent unintended users from having read access to the file by granting the following permissions:

• 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 as it limits the users who can read it to those having log on access to the box or Administrators through the UNC administrative file share.