-
Title
How to setup Windows Authentication with use of Kerberos for accessing Web Reports -
Description
How to setup Windows Authentication through Kerberos for accessing the Web Reports.
-
Cause
-
Resolution
Part A - Locate the TCP Port that the SQL Instance that hosts the MessageStats database is listening on
Note: When setting up Delegation in Step 11, you cannot use a Dynamic Port number (E.g 57770) or Instance name. You must have the SQL instance running on a port that was defined by a SQL Database Administrator. This article assumes you have the TCP Port already set.
The default SQL instance listens on port 1433. You may have a different port for the database instance MessageStats is on.
1. To find the port of the database instance MessageStats is on open Sql Server Configuration Manager.
2. Click SQL Server Network Configuration
3. Click on Protocols for
4. In the right-pane double click on TCP/IP
5. Click the IP Addresses tab
6. Scroll down to IPAll
7. Take note of the TCP Port.
Note: If the TCP Port is blank, and TCP Dynamic Port has a number (e.g 57770) that means the SQL instance is using Dynamic Ports and not running under a defined port by a SQL Administrator. You will not be able use Web Reports with Kerberos until you configure the Instance to run under a specific port.
Disclaimer: Quest Technical Support will not assist with configuring the TCP Port for an instance if you're using Dynamic Ports nor assist with any issue that may arise with the instance after you set the TCP port manually. This article assumes you have the TCP Port already set.
For assistance in configuring the instance to listen on a TCP Port please see this Microsoft TechNet article:
http://technet.microsoft.com/en-us/library/ms177440.aspx
Part B (If applicable) - Manual Service Principle Name (SPN) Registration
**Note: If the SQL Service is configured with a Domain Run-As account you must register the SPN Manually (Proceed with Part B)
If the SQL Server Service is running under a Virtual Account(NT Service\MSSQL$), Network Service, Local System or Local Service you do not need to register the Service Principal Name (SPN) manually. (Skip Part B, Proceed to Part C)To find out account what your SQL Server service is running under, open Sql Server Configuration Manager | SQL Server Services or services.msc on the SQL server and take note of the account.
To register the SPN Manually run the following command from command prompt on the SQL server:
setspn -A MSSQLSvc/SQLServer.company.local:Port_Of_Instance Domain\SQL_Service_account
When running the command the SQL server must be the FQDN with the port of the instance followed by the user account.
E.g.
setspn -A MSSQLSvc/SQLServer.quest.com:1433 quest\zz_SQL
Part C - Set Delegation on the MessageStats Reports server
8. Open Active Directory Users and Computers
9. Locate the computer running IIS that has the MessageStats Web Reports installed on it.
Typically this is the MessageStats machine, however in a distributed install the reports could be on installed on a seperate server.
10. Right-click the reports machine and select Properties.
11. Click the "Delegation" Tab.
12. Click "Trust this computer for delegation to specified services only"
13. Click "Use Kerberos Only"
14. Click Add...
15. Click Users or Computers...
16. Type in the SQL Server that hosts the MessageStats database and click OK.
**Note: If the SPN was set manually, you would type or browse to the Domain run as account that you registered the SPN for.
17. Under Available Services, scroll down until you find MSSQLSvc with the FQDN of the SQL Server and the TCP Port found in Step 7.
E.g. Service Type: MSSQLSvc
User or Computer: sqlserver.company.local
Port: 143318. Click OK
19. You will now see the SPN of the SQL server listed with the port number.
20. Click Apply and OK.
Part D - Set QMSReports.udl file to use Windows Authentication and confirm IIS Authentication
21. From the IIS Server that has the MessageStats Reports installed locate the "QMSReports.udl" file.
By Default it is located here (Before 7.3):
C:\Program Files (x86)\Quest Software\MessageStats
7.3 or later:
C:\Programs Files (x86)\Dell\MessageStats
22. Double-click to open the "QMSReports.udl" file.
23. From the Connection tab, click "Use Windows NT Integrated security" and click Test Connection. Once it passes click OK. (if it is already selected, move on to the next step).
Note: On Windows Server 2008 or later systems you may experience file virtualization. If you make a change to a file you notice the change did not take place.
If you made a change in the QMSReports.udl, reopen the file and confirm the change took place. If it did not, close the file and right-click | Copy and then Paste it. This will make a copy of the file. You can then open the file and make the change and close it. Now you can simply rename the old QMSReports.udl file to .old, and rename the copy of the QMSReports.udl file so it is used by MessageStats.
24. Open Internet Information Services (IIS) Manager
25. Select Sites | Default Web Site | MessageStats Reports
26. Double-click Authentication and ensure "ASP.NET Impersonation" and "Windows Authentication" are set to Enabled.
Part E - Configure MessageStatsReports website to use Application Pool credentials
27. Browse to "%windir%\System32\inetsrv\config" directory, and make a backup of the "applicationHost.config" file.
28. Run Notepad as Administrator, then click File | Open... and browse to the "applicationHost.config" file. (you may need to change to All files in order to see it)
29. Press CTRL+F and do a search for:
30. The line that says windowsAuthentication enabled="true", add useAppPoolCredentials="true". If useAppPoolCredentials already exists, ensure the value says true.
E.g
useAppPoolCredentials="true">
31. Save the File.
Part F - Perform IISReset and open the MessageStatsReports Website
32. Open Command Prompt on the IIS Reports server and type iisreset and press enter.
You should now be able to browse to the MessageStats Reports Website URL from any computer who is logged in as a Domain User.