Today's blog is about connecting Internet/Extranet users to a PerformancePoint solution that uses Kerberos delegation to pass end-user credentials through the application layers to back-end databases. This article is about how to do it in a way that doesn't require VPN deployment, is easy to use and convenient for end-users, and adds no additional burden on SharePoint administrators or DBAs.

What? Impossible you say?  Not at all.  In fact it can be relatively easy to implement without the commonly suggested security trap-doors.  The technique has really been around for quite a while, and it's accomplished through the use of a reverse-proxy solution such as Forefront TMG or ISA server (Forefront is the name of the latest version of the product formerly known as ISA Server).

The video below is an overview and demonstration of a working solution combining the following components.

1. Windows Server 2008 R2 x64
2. SharePoint Server 2010 (CTP)
3. PerformancePoint Services (part of SharePoint 2010)
4. SQL Server 2008 R2 (CTP)
5. Forefront TMG 2010

Note: You can view this video full screen by pressing the full screen button on the bottom toolbar. It's the second item from the right-hand side.

The following issue is a problem in SSAS that you might run into either in test or deployment environments (see references). 

In a nutshell, when a client session running Vista or Windows 2008 talks over the network to an SSAS server running on Windows 2008, and they use Kerberos for authentication, there likely will at some point be connectivity problems that resemble timeout or firewall blocking problems. 

Errors might look like one of the following:

The connection either timed out or was lost.
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
An existing connection was forcibly closed by the remote host

The two places we’ve observed these problems are:

1.       MDX queries submitted to the server that are reasonably large, such as those PPS submits, or perhaps those coded by hand that become large.  “Large” might be something like 1000 bytes or so.

2.       Deploying databases over the network from a Vista/2008 client to a 2008 server.

This problem still exists as of SQL 2008 SP1, so applying that update isn’t a resolution. This problem definitely exists when client & server are on different machines; may not be a problem when client/server are on the same computer (e.g. VPC or BIDS on a remote desktop session). 

From what I understand, MS is aware of this and we should expect a post-SP1 CU soon.  Whether this is to be resolved as a Windows patch or a SSAS patch is unclear to me. 

The following are work-arounds in the mean-time:

1.       If either the server or client is running XP or Windows 2003, the problem should not occur in any case.

2.       If you can edit the connect string (e.g. SSRS/Excel), specify ;SSPI=NTLM as a parameter.    Be advised that user security delegation will not succeed if this workaround is used.

3.       Connect with the IP address instead of machine name or FQDN.  Since Kerberos is unsupported with IP address connections, this also forces a fallback to NTLM.  Kerberos delegation also will not succeed when this workaround is used.

 Of particular note is that if SSAS is to be deployed on a Windows 2008 server, all available workarounds currently imply kerberos delegation will be impossible (a short-term driver for SSAS deployment on Server 2003?). 

 References:

http://blogs.msdn.com/psssql/archive/2009/04/03/errors-may-occur-after-configuring-analysis-services-to-use-kerberos-authentication-on-advanced-encryption-standard-aware-operating-systems.aspx

http://denglishbi.spaces.live.com/Blog/cns!CD3E77E793DF6178!1214.entry

http://sqlblogcasts.com/blogs/drjohn/archive/2009/03/28/kerberos-kills-large-mdx-queries-on-windows-server-2008-ssas.aspx

A while ago Ken Schaefer posted some great documentation on Kerberos delegation.  His series is titled IIS (Internet Information Services) and Kerberos FAQ.  This is a great backgrounder, and a nice guide on advanced delegation concepts.

Just as with other BI front-end technologies in a Microsoft environment, Excel Services worksheets that access back-end data (e.g. Cubes, Databases) require Kerberos delegation configuration.  However, most MOSS installations are initially configured for NTLM security, and making the transition over to Kerberos becomes a challenge since all the things done by installer programs have to be done by hand.

 If you're trying to get your Excel Services worksheets to refresh to a back-end database and receive "Data Refresh Failed" error messages, odds are Excel Services hasn't been configured to delegate security. 

 1. Open Command Prompt
 2. cd C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN
 3. stsadm -o set-ecssecurity -accessmodel delegation -ssp SharedServices1
 4. stsadm -o execadmsvcjobs
 5. iisreset

In the interest of giving credit where due, thanks to Gunter Staes (http://blogs.msdn.com/gunterstaes) for the original command sequence some time back.

1. Install Server 2008 Core x64 from DVD Media
 
2. Rename computer to new name
   Netdom renamecomputer %computername% /newname:SERVER1
 
3. Lookup the name of the network interface
  netsh interface ipv4 show interface
 
4. Assign IPv4 Address
   netsh interface ipv4 set address name=”Local Area Connection 2”
   source=static address=192.168.3.1 mask=255.255.255.0 gateway=255.255.255.0 1
 
5. Assign DNS servers
 Netsh interface ipv4 add dns name=”Local Area Connection” 192.168.3.5
 Netsh interface ipv4 add dns name=”Local Area Connection” 192.168.3.6 index=2
  
6. Join the domain
   netdom join %ComputerName% /domain:INTRANET /userd:<domain\user> /passwordd:*
 
7. Reboot windows (if not prompted)
   shutdown -r -t 0
 
8. After reboot, enable remote administration
   netsh advfirewall firewall set rule group="Remote Administration" new enable=yes
   netsh advfirewall firewall set rule group="Windows Management Instrumentation (WMI)" new enable=yes
 
9. Enable remote desktop from any client type
   Cscript %windir%\system32\SCRegEdit.wsf /cs 0
   Cscript %windir%\system32\SCRegEdit.wsf /ar 0
   netsh firewall set service type=remotedesktop mode=enable
 
10. Add Hyper-V Role
   bcdedit /set hypervisorlaunchtype auto
   start /w ocsetup Microsoft-Hyper-V
 
11. Update Hyper-V
   Run the file: 
   mu_update_for_windows_server_2008_kb950050_x64.msu

12. Activate Windows
   cscript %WinDir%\System32\slmgr.vbs -ato
 
12a. If windows won't activate and needs a VLK changed to a MAK
   start /w slmgr.vbs -ipk FFFFF-FFFFF-FFFFF-FFFFF-FFFFF
   start /w slmgr.vbs -ato
   (careful, the feedback popups sometimes popup behind the DOS window, so move it around before re-entering the command over and over)
 
13. Reboot windows (if not prompted)
   Shutdown -r -t 0

14. Install remote admin tools for Hyper-V
   Install the following on your desktop (choose depending on whether you run Vista x86 or x64)
   Hyper-V_Admin_Windows6.0-KB952627-x64.msu (Vista x64 Client)
   Hyper-V_Admin_Windows6.0-KB952627-x86.msu (Vista x86 Client)
   mu_update_for_windows_server_2008_kb950050_x86.msu  (Server 2008 x86)

15. Add the Hyper-V snap in to an MMC.exe instance on your desktop, connect to the Hyper-V virtual server from there, and you're off to the races!

 

In my work I often use Microsoft VirtualPC on my laptop to demonstrate software for clients and to conduct training.  Most anyone who's used a VPC to do a demonstration of server-based products--the kind that should be deployed on "real servers"--has at some point had the unpleasant experience of apologizing for poor performance during a pivotal moment in a demo.

I'm always looking for ways to speed up demos, and there are only a few general approaches I know of:

1. Lighten the "weight" of the VPC, so there's not so much bulk for the VPC OS to deal with and so the demo "fits" the constraints of the host's capabilities
2. Use a better VM technology (I'm currently converting my demos to Hyper-V--more on that in the future)
3. Separate the guest OS I/O from the host and use a fast external HDD

The faster disk option (#3) is one I employed some time ago when I had a spare 7200rpm disk to use as an external VHD store, but I never really "proved" that it worked better than the 5400 rpm disk it replaced.  I also never "proved" that moving to external USB 2.0 disks had helped in the first place! 

Recently I happened to have several USB 2.0 hard disks and some spare time available. (I upgrade way too often and I have too many computers).

I decided to do a somewhat scientific experiment to see if, in fact, the faster hard drive had actually helped, and if the performance of the external disks were similar to their internal alternatives.  The two variables I was able to change based on available hardware:

1. Dive interface SATA/IDE
2. Disk rotation speed (5400rpm/7200rpm)

Other than these factors, all else is the same (same PC, same brand USB 2.0/HDD enclosure, same tests). In addition to comparing the performance with the different external disks, I benchmarked the internal disk.  This was an interesting test because the internal and external 200GB drives are identical Seagate 200GB 2.5" drives--so the test should be an "apples to apples" comparison of throughput lost through USB 2.0.

The test environment:

• Lenovo ThinkPad X61 2Ghz Core2Duo CPU, Windows 2008 Standard x64, 4GB RAM 
• Test #1 - Copy a single 8GB VHD file from the ThinkPad to the USB hard disk
• Test #2 - SQLIO with large IO load (sequential I/O, 256KB read/write, 2 threads, 60 seconds, 100MB file size

Here are the test results:

	100GB IDE USB 2.0	160GB SATA  USB 2.0	200GB SATA USB 2.0	200GB SATA Internal
Disk Mfg	Fujitsu	WD	Seagate	Seagate
RPM	7200	5400	7200	7200
8GB Copy (MB/sec)	21.3	20.9	21.3	N/A
Read
IO/sec	111.6	114.3	115.1	225.1
MB/sec	27.9	28.6	28.8	56.2
Avg Latency (ms)	142	139	138	70.0
Write
IO/sec	83.1	83.3	83.3	214.2
MB/sec	20.8	20.8	20.8	53.6
Avg Latency (ms)	191.0	211.0	191.0	73.0

Well, it appears that switching from a 5400 RPM disk to 7200 RPM USB 2.0 disk had actually delivered no benefit.  But I was a little suprised that the SATA interface also seemed to have no benefit over the older IDE technology...I had assumed that would have made a difference given the difference in transfer rates between the two. 

So, when USB 2.0 is the external I/O path, it seems that one needn't be too picky about the drive itself.  The USB interface is just too slow and inefficient to leverage the latest hard drive technologies.

 

 

 

Windows 2008 shipped recently, and I've been updating my field kit with all of the various installation media and supplemental programs needed for client site deployments.

One issue I ran into is when installing the Books Online standalone package on my Vista x64 laptop:

"This installation package cannot be installed by the Windows Installer service.  You must install a Windows service pack that contains a newer version of the Windows Installer service."

The message is pretty self-explanatory, but it was strange that at this time Windows Update indicates my computer is all up-to-date. 

To resolve this issue I needed to manually download the update to Windows Installer 4.5, apply it, and reboot. Then installing the updated install package proceeded normally.