Reporting Services 2005 for the DBA – Double Hop Authentication

By : Steve Chowles
http://sqlblogcasts.com/blogs/stevechowles/archive/2007/07/03/reporting-services-2005-for-the-dba-double-hop-authentication.aspx

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.

 

在报表中一种常见的情况是关于双跃认证问题.这篇文章将阐述这些问题并告知怎样配置你的报表服务环境以使用双跃认证.我将以IIS 6.0 和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.

 

什么是双跃认证.

想象你坐在电脑前打开浏览器,输入http://<server>/Reports 打开报表服务管理器,在实际的环境中,你的报表服务器不再你的自己的电脑上,而是在另外一台服务器上.使用Window NTLM协议,你的windows凭证将传给应用服务器.这记为第一次跳跃.只要你所需要的都存放在这台服务器上,那么你的windows凭证将用于访问这些资源.到目前为止,没有任何问题.

 

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.

 

接下来问题就产生了.你所运行的报表需要链接到SQL Server去读取数据,然而,你的SQL Server安装在第三台服务器上.于是,你从客户端连接到报表服务器,在连接到SQL Server,这就是第二次跳跃或者说双跃.在这种情况下,NTLM方式不能将你的Windows凭证传给SQL Server.因而你不能通过你的windows帐号从SQL Server中得到数据.我提到到SQL Server,然而,无论你尝试用任何的服务去连接第三台服务器,问题是NTML根本不会将你的凭证传过去.分析服务的无法连接就是一个很好的例子.在这种情况下,用于连接第三台服务器的帐号将是NT Authority/Anonymous Logon.这个帐号.因此,如果你的第三台服务器是SQL Server,那么你需要将匿名帐号加入SQL Server的帐号中并赋予相应的权限.

 

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.

 

配置双跃认证.

我刚才已经提到 NTLM认证支持双跃认证,因此,为了支持双跃认证,我们需要配置Kerberos 认证.

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.

 

我将把这部分分为三个小节.

   客户端的配置

   应用服务器的配置

   数据服务器的配置.我将SQL Server作为第三个跃点因为这是最常见的方式.

 

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 

    检查如下文件的内容C:/Windows/System32/Drivers/ETC/hosts 并且确保没有任何机器在使用你的这个配置包括你的域控制器.

 

    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.

     在命令提示行中运行 NSLOOKUP找出应用服务器和域控制器的完全合格域名(FQDN)并确保他们都可以找到.

 

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

     在命令提示行中运行 NSLOOKUP找出应用服务器和域控制器的IP地址,并确保他们都是可用的

    

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

      在命令行中 PING 应用服务器和域控制器.

 

    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.

     你在AD里面的Windows帐号的 不能勾选Account is sensitive and cannot be delegated.

 

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

     在IE浏览器中选择 工具 à Internet 选项à高级选项中确保开启集成windows认证

    (译者注:在IE6中集成Windows认证默认是关闭的,IE7中默认是开启的.)

     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.

     当你连接报表管理器的时候,你必须确信IE浏览器的安全区域不是使用的Internet.选择 工具 à Internet选项 à安全 à 本地网络 ,然后点击 站点,将应用服务器加到这个本地站点列表.

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

 检查如下文件的内容C:/Windows/System32/Drivers/ETC/hosts 并且确保没有任何机器在使用你的这个配置包括你的域控制器.

         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.

 在命令提示行中运行 NSLOOKUP找出客户端,SQL Server服务器和域控制器的完全合格域名(FQDN)并确保他们都可以找到.

 

         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.

在命令提示行中运行 NSLOOKUP找出客户端,SQL Server服务器和域控制器的IP地址,并确保他们都是可用的

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

在命令提示行中运行 PING命令 ping 客户端,SQL Server 服务器和域控制器.

 

         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.

我们需要确认报表服务的虚拟路径所在的web站点已经配置支持Kerberos 认证.首先,在IIS管理器中的左侧点击web站点,并且记录下报表服务站点的序号.

 

         From a command prompt:

Change to folder C:/Inetpub/Adminscripts.

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

where NN is the Numeric Identifier from above.

     在命令提示行中,将文件夹指定到C:/Inetpub/Adminscripts.

     (译者注:实际上,指定这个路径的意义是寻找adsutil.vbs 文件.如果IIS是默认安装的话,那么就是这个路径,否则,则要指定到adsutil.vbs 文件所在的文件夹.)

运行cscript adsutil.vbs set w2svc/NN/root/NTAuthenticationProviders “Negotiate,NTLM”

    这里, NN是代表前面的web站点的序号.

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

    确保报表服务的虚拟路径支持windows集成认证.在 属性 à目录安全性 -à授权和访问控制里面设置.

 

         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.

    如果在报表服务器所在的虚拟目录的应用程序缓存池是使用的本地帐号,那么,在AD里面需要指定应用程序服务器支持代理.如果使用的是域帐号,那么该域帐号必须支持代理.

         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:

Act as part of the operating system

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.

 

在命令提示行里面运行 SECPOL.MSC,在安全设置 à 本地策略 -à 用户权限设置下设置报表服务器虚拟目录的应用程序缓存池使用的帐号如下权限:

       作为操作系统的一部分,

       认证后作为代理客户端.

注意: 可用通过运行RSOP.MSC 查看计算机配置à windows设置à安全设置 à本地策略à用户权限设置的 Source GPO栏位来确认你公司的组策略是否覆盖了你设置的权限

 

         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.

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

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

如果用于报表服务器缓存池的帐号是本地帐号,那么需要创建如下的服务主体名称:注意,如果你没有权限的话,需要AD管理员帮你创建.

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

 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.

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

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

     

      如果用户报表服务器缓存池的帐号是域帐号, 那么需要创建如下的服务主体名称:注意,如果你没有权限的话,需要AD管理员帮你创建.

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

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.

 

SQL Server 数据库服务器的配置

以下步骤在SQL Server数据库服务器上完成.

 

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.

注意: 这里说的SQL Server数据库服务器不是你的报表服务的数据存放的位置,而是你的报表抓取数据的那台SQL Server数据库 服务器.

 

         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

     检查如下文件的内容C:/Windows/System32/Drivers/ETC/hosts 并且确保没有任何机器在使用你的这个配置包括你的域控制器.

 

         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.

 在命令提示行中运行 NSLOOKUP找出SQL Server服务器和域控制器的完全合格域名(FQDN)并确保他们都可以找到.

 

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

在命令提示行中运行 NSLOOKUP找出SQL Server服务器和域控制器的IP地址,并确保他们都是可用的

.

 

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

 在命令提示行中运行 PING命令 ping SQL Server 服务器和域控制器.

 

         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.

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

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

创建如下的服务主体名称:注意,如果你没有权限的话,需要AD管理员帮你创建.

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

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

 

So that is your SQL Server ready

到此为止,SQL Server数据库服务器 配置完成

 

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.

 

首先将你的windows账户和用户NT Authority/Anonymous Logon加入 SQL Server用户中.

如果由于某些原因不能添加帐号,你仍然可以使用事件查看器来捕获导致登录失败的原因.

 

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.

 

接下来,我们上传一张在SQL Server数据库中运行SELECT SUSER_SNAME() 的报表.

这个函数返回用户登录SQL Server数据库的所有用户的名称.我已经附上了一张报表,所以你可以按照如下步骤上传.

         运行报表管理器

         点击上传文件

         找到报表文件Check Login Name.rdl 并点击确定

         点击工具栏右边的显示明细 按钮

         选择报表Check Login Name旁边的修改按钮

         点击数据源

         修改连接字符串将EnterServerHere 替换为你的SQL Server服务器

         检查集成Windows 认证已经勾选

         点击应用.

         点击查看运行报表

 

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

 Login Name 栏位可以显示报表是否可以运行.

 

If you see your Windows account it is working fine.

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

 如果你看到的是你的Windows的帐号,则表示双跃认证成功,

 如果你看到的是NT Authority/Anonymous Logon 则表示双跃认证失败.

 

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.

 

另外一个检验双跃认证是否成功的方式是在SQL Server数据库服务器上查看window自带的事件日志.点击安全日志中在用户栏中查找你的Windows 账户. 查看登录和注销事件.在描述中有一个认证包.在它下面应该是有Kerberos来验证双跃认证是否通过.

 

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.

 

如果你不能找到你的windows 帐号,那么你应该可以找到一个匿名用户.这就证明双跃认证没有成功,那么打开事件查看它的认证包中查看是否包含 NTLM. 

 

Troubleshooting Kerberos Issues 

关于Kerberos 认证的问题

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

 与其重复一份已经存在的而且很好的白皮书,我建议你参考如下连接:

Troubleshooting Kerberos Delegation

  

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

好了,我希望这篇文章能够对你有用,下文再会!

Attachment: Check Login Name.zip

Published 03 July 2007 14:58 by stevechowles