Steve Chowles v SQL Server

Reporting Services 2005 for the DBA – Double Hop Authentication

One of the issues which crop up time and time again is the classic Double Hop Authentication problem. This article will explain what the issueis and how to configure your Reporting Services environment in order to use Double Hop Authentication. I will only be discussing the configuration for IIS6.0 on Windows 2003.

So what is Double Hop Authentication?

Imagine you are sitting at your laptop and you launch Internet Explorer. You enter http://<server>/Reports and this brings up Reporting Services Report Manager. In a real world environment your web server hosting Reporting Services will not be on your laptop but rather a different machine. Using the windows NTLM protocol, your windows credentials will be passed to the web server. This counts as one hop. As long as everything you require is located on the web server, your windows credentials will be used for accessing the resources. So far so good.

Here is the issue. You run a report which needs to connect to a SQL Server to get the data required for the report however; SQL Server is located on a third machine. So you have laptop to web server to SQL Server, a second hop or double hop. In this scenario NTLM is not able to forward your windows credentials to SQL Server, so you will not be able to get the data from SQL Server using your windows account. I mentioned SQL Server however; it does not matter what service you are trying to connect to on the third, the problem is that NTLM will never pass your credentials so it would also fail for Analysis Services as an example. In the situation the account used to access the third server will be the NT Authority\Anonymous Logon account. So if the third server is SQL Server then you need to add the Anonymous account as a SQL Server account with the necessary privileges.

The workaround mostly used is to create a generic account to access the data and store the credentials in the Reporting Services Catalog. This works great however; I will explain how you can configure your environment to use Double Hop Authentication in order for you to connect to the third server using your own credentials.

I have to say right now that I will provide as much information as I can in order for you to get this working however; I am sure there will be issues for some of you which will mean it still does not work. This is why I will be providing links to external resources to help.

Configuring Double Hop Authentication

I have already mentioned that NTLM does not support Double Hop Authentication so in order for this to work we need to configure Kerberos Authentication.

I am going to break this up into three sections:

·         One for the Client configuration

·         One for the Web Server configuration

·         One for a SQL Server configuration. I will only be discussing SQL Server as the third hop since this is probably the most popular.

Before you start you need to be aware of the servers involved. In fact for this to work there are in fact more than three servers involved. There are the three servers I mentioned above and also one or more domain controllers. You will need to make a note of all the machines and the domains the machines reside in.

Client Configuration

Follow these steps on the client machine where you run Report Manager.

·         Review the contents of the file C:\Windows\System32\Drivers\ETC\hosts and ensure there are no references to any of the machines which are used for your configuration including domain controller entries

·         From a command prompt run NSLOOKUP on the Fully Qualified Domain Name (FQDN) of the web server and domain controller and ensure they are all visible.

·         From a command prompt run NSLOOKUP on the IP Addresses of the web server and domain controller and ensure they are all visible.

·         From a command prompt PING the web server and domain controller.

·         From the Event Viewer within the System events locate the latest W32Time entry in the Source column and confirm the time is synchronized.

·         In Active Directory your Windows Account must NOT have the option Account is sensitive and cannot be delegated enabled.

·         Within Internet Explorer choose Tools -> Internet Options -> Advanced and ensure the option Enable Integrated Windows Authentication is enabled

·         When you connect to Report Manager you need to make sure that the Internet Explorer Security Zone is not using Internet. Choose Tools -> Internet Options -> Security -> Local Intranet and then Click Sites and add the Web Server to the list of web sites.

So that is your client ready.

Web Server Configuration

Follow these steps on the web server where Reporting Services has been installed.

·         Review the contents of the file C:\Windows\System32\Drivers\ETC\hosts and ensure there are no references to any of the machines which are used for your configuration including domain controller entries

·         From a command prompt run NSLOOKUP on the Fully Qualified Domain Name (FQDN) of the client, SQL Server machine and domain controller and ensure they are all visible.

·         From a command prompt run NSLOOKUP on the IP Addresses of the client, SQL Server machine and domain controller and ensure they are all visible.

·         From a command prompt PING the client, SQL Server machine and domain controller

·         From the Event Viewer within the System events locate the latest W32Time entry in the Source column and confirm the time is synchronized.

·         We need to ensure the Web Site where the Reporting Services Virtual Directories are created is configured for Kerberos Authentication. Firstly, from IIS Manager click on the Web Sites folder on the left hand side and make a note of the Numeric Identifier for the Reporting Services Website.

·         From a command prompt:

o        Change to folder C:\Inetpub\Adminscripts.

o        Run cscript adsutil.vbs set w2svc/NN/root/NTAuthenticationProviders “Negotiate,NTLM”

where NN is the Numeric Identifier from above.

·         Make sure that the Reporting Services Virtual Directories has the Integrated Windows Authentication enabled only within Properties -> Directory Security -> Authentication and Access Control

·         If the account specified in the Application Pool used by the Reportserver Virtual Directory is a local account, in Active Directory you need to Enable Delegation for the Web Server Machine. If the account is a domain account, you need to Enable Delegation for the Domain Account.

·         Run SECPOL.MSC and under Security settings -> Local Policies -> User Rights Assignment you need to give the account specified in the Application Pool used by the Reportserver Virtual Directory the following rights:

o        Act as part of the operating system

o        Impersonate a client after authentication.

Note: Your company group policy may override these which you can check by running RSOP.MSC and looking in the Source GPO column under Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment.

·         If the account specified in the Application Pool used by the Reportserver Virtual Directory is a local account, create the following Service Principal Names: Note: You may need your AD Administrator to create this if you don’t have the permissions.

o        SETSPN –A HTTP/<web server> <web server>

o        SETSPN –A HTTP/<FQDN of web server> <web server>

·         If the account specified in the Application Pool used by the Reportserver Virtual Directory is a domain account, create the following Service Principal Names: Note: You may need your AD Administrator to create this if you don’t have the permissions.

o        SETSPN –A HTTP/<web server> <domain account>

o        SETSPN –A HTTP/<FQDN of web server> <domain account>

So that is your web server ready

SQL Server Configuration

Follow these steps on the machine where SQL Server resides.

Note: This is not the same SQL Server where the Reporting Services Metadata is held. This will be the SQL Server where you retrieve the data for a report.

·         Review the contents of the file C:\Windows\System32\Drivers\ETC\hosts and ensure there are no references to any of the machines which are used for your configuration including domain controller entries

·         From a command prompt run NSLOOKUP on the Fully Qualified Domain Name (FQDN) of the SQL Server machine and domain controller and ensure they are all visible.

·         From a command prompt run NSLOOKUP on the IP Addresses of the SQL Server machine and domain controller and ensure they are all visible.

·         From a command prompt PING the SQL Server machine and domain controller

·         From the Event Viewer within the System events locate the latest W32Time entry in the Source column and confirm the time is synchronized.

·         Create the following Service Principal Names: Note: You may need your AD Administrator to create this if you don’t have the permissions.

o        SETSPN –A MSSQLSVC/<SQL Server:Port Number> <SQL Server Service Domain Account>

o        SETSPN –A MSSQLSVC/<FQDN of SQL Server:Port Number> <SQL Server Service Domain Account>

So that is your SQL Server ready

Testing Double Hop Authentication

We are now ready to test.

First add your windows account and the user NT Authority\Anonymous Logon to SQL Server. If you are unable to add the accounts for some reason you can still run profiler and capture the Failed Login event to see what it would have been.

Next we need to upload a Report that runs SELECT SUSER_SNAME() on the SQL Server. This function returns the name used to login to SQL Server. I have attached a Report already which you can upload as follows:

·         Run Report Manager

·         Click Upload File

·         Browse to the Report file Check Login Name.rdl file and click OK

·         Click Show Details on the toolbar on the right hand side

·         Select the Edit icon next to report Check Login Name

·         Click Data Sources link

·         Edit the Connection String and replace EnterServerHere with you SQL Server name

·         Confirm Windows Integrated Security is checked

·         Click Apply

·         Click the View tab to run the Report.

The column Login Name will tell you if this is working.

If you see your Windows account it is working fine.

If you see NT Authority\Anonymous Logon then it is not working.

An alternative way to check if it is working is to view the Event Log on the SQL Server machine. Click on the Security log and look for you Windows Account in the User column. Look for a Logon/Logoff category and open the event. In the Description section is an entry with Authentication Package. This should have Kerberos next to it to verify it is working.

If you do not see an entry for your account you will probably see one for the ANONYMOUS LOGON user. This will mean it is not working and can be confirmed by opening the event and checking to see if the Authentication Package entry has NTLM next to it.

Troubleshooting Kerberos Issues

Rather than repeat what already exists in a really good whitepaper, I will instead point you to this link.

Well I hope that was useful for you. See you on the next article

Posted 03 July 2007 14:58 by stevechowles | with no comments

Reporting Services 2005 for the DBA – Changing Application Pool Identity

By default when you install Reporting Services it will by default use the NT Authority\NetworkService account for running the Application Pool processes. If you use NT Authority\NetworkService for the Application Pool used by the Report Server, when it connects to the SQL Server to read the Reporting Services meta data, it will connect using the account Domain\<Server>$ where <server> is the machine name where Reporting Services is installed.

It is quite easy to change the account used to a different name by following the steps below. One point to note is that the accounts used by the Application Pools do not affect Reporting Services Encryption. I will be covering Encryption soon in an article which is probably going to be the most in depth one you will have ever seen so look out for that one.

Another point worth noting is that you can have a different Application Pool for Report Server and Report Manager or you can use the same one. Even if your account is the same for Report Server and Report Manager make sure you complete all the steps below for the account.

The steps below detail how to change the accounts from NT Authority\NetworkService to DOMAIN\MyAccount

Changing the Account for the Report Server Application Pool

On the SQL Server where the Reporting Service Meta Data resides complete the following

·         Create login DOMAIN\MyAccount to SQL Server

·         Add DOMAIN\MyAccount to the database master with the database role RSExecRole

·         Add DOMAIN\MyAccount to the database msdb with the database role RSExecRole

·         Add DOMAIN\MyAccount to the database ReportServer with the database role RSExecRole

·         Add DOMAIN\MyAccount to the database ReportServerTempdb with the database role RSExecRole

Change the Identity of the Application Pool account used by Report Server Virtual Directory

·         Open IIS Manager from Administrative Tools

·         Open the Properties of the Report Server Virtual Directory and confirm the Application Pool name

·         Open the Properties of the Application Pool.

·         Click on the Identify tab

·         Click Configurable and enter the UserName DOMAIN\MyAccount and Password

·         Click OK and enter the password again to complete.

Add DOMAIN\MyAccount to the following Local Groups on the server hosting Reporting Services

·         IIS_WPG

·         SQLServer2005ReportingServicesWebServiceUser$<machine>$<instance>

·         SQLServer2005ReportServerUser$<machine>$<instance>

Update the Report Server Configuration file

·         Open file C:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer\rsreportserver.config

·         Locate <WebServiceAccount>account<WebServiceAccount>

·         Change account to DOMAIN\MyAccount

·         Save the file

Update NETMAN DCOM Permissions

Reporting Services 2005 for the DBA – Connectivity Issues

After an Installation of Reporting Services with the configuration complete, there is no guarantee you will be able to connect to Reporting Services straight away. So this article will discuss issues connecting to Report Server and Report Manager and give you some advice on how to resolve these issues.

The first sign of a connectivity issue is when a DBA first runs Report Manager from their browser. There are a whole host of errors which could return however; hopefully you will be able to resolve them with the help of this article.

Check the Report Server Web Service is working correctly

The Report Server Web Service is the heart of Reporting Services. There is a log file created for the Web Service however; it is only created if all the pieces required to get there are up and running, so first we need to check IIS.

I am going to provide a list of areas to check and it is worth ensuring you have read my previous blogs on IIS and IIS Security.

Event Log

Check out the System and Application event logs, many IIS and Application Pool failures are written there.

IIS Admin Service

Make sure the IIS Admin Service is running. You need this service in order to run web sites and configure IIS.

Report Server Application Pool

Using IIS Manager check the Application Pool whish is used by the Report Server is running. You can confirm the Application Pool used by Report Server by looking at the Properties of the ReportServer web site. It will be at the bottom of the Virtual Directory tab.

Once known click on the Application Pools link and verify the pool is running on the right hand pane.

Report Server Website

Using IIS Manager click on the Web Sites link and verify the Reporting Services web site is running on the right hand pane.

Connection URL

You need to understand what the Connection URL to the Report Server Web Service is. This is really important because an incorrect URL maybe the only issue.

From IIS Manager click on the Web Sites link and look at the right hand pane. There are various columns and the important ones are Host Header Value, IP Address, Port, and SSL Port. Depending on what values you have here will depend on the URL used to connect to the Report Server.

There may be multiple entries with different values and in order to see all of them you need to look at the Properties of the web site and click on the Advanced button next to IP Address.

The different columns are used as follows:

·         IP Address: This gives you the range of IP Addresses that are allowed to be used when connecting to Report Server. If set to All Unassigned then it is listening on any IP address.

e.g http://nnn.nnn.nnn.nnn/ReportServer or use the hostname for that IP Address.

·         Port: This is the Port number that the Web Site is listening on. By default HTTP listens on port 80 however; any value other than 80 needs to be specified in the URL string.

e.g http://server:880/ReportServer is using port number 880 instead of 80

·         SSL Port: This column will only be populated if the web site is using the Secure Socket Layer. By default HTTPS listens on port 443 however; any value other than 443 needs to be specified in the URL string.

e.g https://server:555/ReportServer is using port number 555 instead of 443.

·         Host Header Value: Rather than connect to the web site using the server name, you might want to connect using a completely different name.  You see this all the time on the Internet with names such as www.microsoft.com. This DNS name will point to the server where the Report Server is running.

·         e.g http://www.mydomain.com/ReportServer

Reporting Services Databases

Reporting Services stores all its information in a SQL Server database and we need to ensure SQL Server is running with no problems, so check the SQL Server Errorlog and Event log on that server. As long as the installation was successful all the access for the SQL accounts will have been done however; you will see logon failures in the Reporting Services log files.

Local Connectivity Test to Report Server

We are ready to test we can connect but before we do that I want to quickly review the pieces of the Reporting Services puzzle. When you connect to Report Manager it will send SOAP requests to the Report Server Web Service to retrieve the information to display. So straight away we can see that if Report Server Web Service is not working, nor will Report Manager. The Reporting Services Windows Service does not play any part at this stage.

Our first test needs to be done on the web server itself so logon to the web server where Reporting Services is installed and open Internet Explorer.

You now need to connect to the Report Server Web Service. The URL you use will depend on the information you discovered in the Connection URL section above so you may have to use HTTPS or add a port number.  Another point to bare in mind is that when you connect to a Web Service it has to read the WSDL file which basically means the first connection takes a little while so hang in there. As long as your keep your browser open the subsequent connections will be quick.

Try the following connections

·         Try connecting using localhost as the server name

·         Try connecting using IP address 127.0.0.1

·         Try connecting using the machine name

·         Try connecting using the machines IP address

·         Try connecting using the Fully Qualified Domain Name of the server

·         Try connecting using a Host Header Name

Connection Fails

If any of the above tests fail you need to review the following. These are not all the same checks as above so please review them.

·         If localhost fails and 127.0.0.1 works it means the localhost entry needs to be added to the HOSTS file in C:\WINDOWS\System32\Drivers\etc

·         Check if the C:\WINDOWS\System32\Drivers\etc\HOSTS file does not have incorrect entries

·         Using NSLOOKUP verify your DNS entries have the right IP Addresses for the server names and Host Header Values.

·         Check the server has the correct domain name suffix added if you are not specifying it.

·         Make sure that the URL you specify only hits one web site. For example one web site could be listening on localhost and port 80 and the other may be listening on any IP address and port 80. A quick way to check this is to Stop the other website and try again.

·         Hopefully if the Application Pool has communicated with the Report Server there may be a Report Server log file to view since this file is only created when a connection is first established. If you installed using the default installation path which is a recommended thing to do because articles like mine make reference to them, your log files will be created in folder C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles. The file will be called ReportServer_<timestamp>.log and by default a new file is created every day at midnight and will be kept for 14 days.

Take a look at this file and see if you can spot any errors. They are usually easy to spot because they always appear after a timestamp with the prefix of ERROR:

·         Another issue may be Security related and rather than repeat myself please refer to my previous blog on IIS Security which explains how Reporting Services is configured from an IIS perspective.

·         If you are connecting using SSL then make sure you are not getting any Security Alert messages indicating there is an issue with a certificate. Any issues will need to be resolved with your local web team.

Hopefully if you reach here then you have successfully connected to the Report Server.

Remote Connectivity Test to Report Server

Unless the Local Connectivity test is successful the Remote Connectivity test will not succeed.

So what we want to do now is perform a Remote Connectivity test on either your workstation or laptop or from another server. We can repeat all the tests except the localhost or 127.0.0.1 tests since these always refer to the machine where Internet Explorer is running.

Try the following connections

·         Try connecting using the machine name

·         Try connecting using the machines IP address

·         Try connecting using the Fully Qualified Domain Name of the server

·         Try connecting using a Host Header Name

Connection Fails

If any of the above tests fail you need to review the following:

·         Check the C:\WINDOWS\System32\Drivers\etc\HOSTS file does not have incorrect entries

·         Using NSLOOKUP verify your DNS entries have the right IP Addresses for the server names and Host Header Values.

·         Check the server has the correct domain name suffix added if you are not specifying it.

·         Make sure that the URL you specify only hits one web site. For example one web site could be listening on localhost and port 80 and the other may be listening on any IP address and port 80. A quick way to check this is to Stop the other website and try again.

·         Hopefully if the Application Pool has communicated with the Report Server there may be a Report Server log file to view since this file is only created when a connection is first established. If you installed using the default installation path which is a recommended thing to do because articles like mine make reference to them, your log files will be created in folder C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles. The file will be called ReportServer_<timestamp>.log and by default a new file is created every day at midnight and will be kept for 14 days.

Take a look at this file and see if you can spot any errors. They are usually easy to spot because they always appear after a timestamp with the prefix of ERROR:

·         We need to ensure there are no IP Address and Domain Name Restrictions. Using IIS Manager look at the properties of the Reporting Services web site and click on the Directory Security tab. Now click Edit next to the IP Address and Domain Name Restrictions. If there are any entries added then check to ensure your IP address is not one of them.

Connectivity Tests to Report Manager

If Report Server is working it is now time to test Report Manager. Exactly the same tests are required and exactly the same connectivity failure checks can be performed. The Report Manager has its own log files in the same folder as the Report Server log files in the format ReportServerWebApp_<timestamp>.log

Before you being the tests you need to be aware of how Report Manager determines how to connect to Report Manager.

Within the Report Manager Virtual Directory is a file called RSWebApplication.config. Within this file is a section starting at <UI> are two parameters we are interested in:

·         <ReportServerUrl>

·         <ReportServerVirtualDirectory>

These two values are mutually exclusive which means we can only supply a parameter to one of them at any one time.

If the Report Server and Report Manager share the same website on the same machine then all you need to do is supply the Virtual Directory name to the <ReportServerVirtualDirectory> parameter and