Windows Server 2008 Kerberos Bug – Transport Connection Issues with SSAS data

So you want to be on the latest and greatest software with your Business Intelligence platform…well watch out for this one.  Back in January this year I was in the middle of a major SQL Server 2008 and Windows Server 2008 upgrade when we started to experience some issues with our Reporting Services (SSRS) 2008 testing against Analysis Services (SSAS) 2008 data sources.  What we were doing was building out all of the SQL Server 2008 components with Windows Server 2008 and moving the SQL Server 2005 items to these new servers to perform testing.  All of the SQL Server 2005 components were running on Windows Server 2003.

The error that we were running into when trying to running SSRS 2008 reports against SSAS 2008 was 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

So after getting this error we made sure that Kerberos was properly configured and then started to dive into a more deeper analysis using WireShark and reviewing network traces.  We also made sure that all of the servers had updated firmware and drivers for the network adapters (http://support.microsoft.com/kb/942861) and we turned off all offloading on the adapters to see if that would resolve the issue including the TCP Chimney (sorry Santa, we closed the Chimney down) and UPD Checksum and any other Offloading that was setup on the network adapters (http://support.microsoft.com/default.aspx/kb/951037) – these properties can be found if you go into Device Manager—>Network adapters—>and check the Advanced tab of the properties of each network adapters.

The funny thing was that if we pointed SSRS 2008 against our SSAS 2005 server (that was running Windows Server 2003) the reports executed just fine.  We also ran all of the reports with issues from SSRS 2005 servers to the SSAS 2008 servers and they worked fine.  So what was going on?  After numerous testing and network traces we finally determined that this was out of our control and needed to contact Microsoft.

After going through more testing, network traces, SSRS log files, etc. and about seven support people I finally got a phone call one night while I was in Cub Foods getting a few groceries at about 8 or 8:30 at night.  The Microsoft engineer was with the SSAS team and he was aware of the issue…this was starting to pop-up with customers and we were about the fourth case of this that he had heard of

After additional testing it turns out there is an issue with AES (advanced encryption standard) aware operating systems (Vista, Windows Server 2008, Windows 7) and with Kerberos.  I was given a very detailed description of different messages that are used with Kerberos.  There is an issue with the decrypt data for security buffer not being the correct size for AES and this causes the error.  This occurs when you are using Kerberos and are trying to get AES aware operating systems to talk to each other.

So our workaround was to keep the front-end servers (SSRS, PerformancePoint, ProClarity, and SharePoint) on Windows Server 2003 and the backend servers for our databases on Windows Server 2008.  This was a very frustrating item to uncover and took us a few weeks of testing along with additional time working with Microsoft on the issue.  The Microsoft case has been moved over to the Windows Server team and they have stated that a fix should hopefully be available in May 2009 (need to perform a few months of testing and going through the red tape for the hotfix).

For more information in regards to this and a similar posting check out this link –  SSAS: Kerberos kills ‘large’ MDX queries on Windows Server 2008.  Sorry I didn’t get this posted sooner to warn more people, but hopefully this will now help out other people experiencing this same issue or planning their infrastructure upgrades

UPDATE (4/5/2009):  I saw that one of the engineers on my case posted a blog posting here in reference to this issue if you want some more details – Errors may occur after configuring Analysis Services to use Kerberos authentication on Advanced Encryption Standard Aware Operating Systems.

UPDATE (6/2/2009): The knowledge base number assigned to this is 968700, but has not been published yet since a hotfix is not available at this time.  Based on Microsoft information they now state that this will possibly slip to August for the fix, but there is a possibility of a June (or July) release if it passes all of the comprehensive testing that they require.  They did state that this is resolved with Windows 7 and also Windows Server 2008 R2.  Unfortunately these operating systems are in Release Candidate right now and are slated to be released possibly in Q3 of this year.  So the wait continues  Keep using Windows Server 2003 for either the front-end servers or the database (front-end probably easier, especially if they are virtual — just swap them out).

UPDATE (7/20/2009): According to  Dr. John Tunnicliffe a patch has been released for this and is available to download – SSAS: Microsoft release fix for “Kerberos killing MDX” issue .

Microsoft has released a patch to us that fixes the issue. However, we do not know when Microsoft intends to roll this patch into a service pack for Windows Vista/Server 2008. As several people have contacted me about the issue, I provide the patch for those suffering the same plight can get a quick fix. There is no warranty and no guarantee with this patch. Works for us, but test it first!

UPDATE (8/23/2009): Removed old links and replaced with the official KB link here –> http://support.microsoft.com/default.aspx/kb/969083 

SSIS 2008 ADO NET Source issue with SSAS data source – error code 0×80004002

A few weeks back I upgraded a SSIS 2005 package to SSIS 2008 and ran into a issue with my Data Flow Task source.  In the original package I was using a DataReader source to query information from Analysis Services (SSAS) and during the upgrade this gets converted to a ADO NET source in SSIS 2008.  The reason I was using a DataReader with an ADO.NET connection manager was because you could get around the annoying about being unable to map external data properly and that it will use the DT_WSTR data type when you try to preview the results of your query for the data source.

I did a blog posting last year Using Reporting Services (SSRS) with SSAS data where I went through some different options of getting SSAS data into a format to easily use with SSRS.  This is when I basically decided that I would use the DataReader over the OLE DB source when using SSAS.  Also, if you use the OLE DB data source and have the Data Flow Task embedded in a Loop container this could cause some issues depending on the connection manager setting (more on this later).

So back to the ADO NET source issue… after I did my upgrade and ran my package I noticed a couple of things now with the ADO NET source.  First you get a warning message now in regards to the data type that I did not get with the DataReader source in SSIS 2005:

Warning    3    Validation warning. ADO Source: {568FF45A-6A41-43F8-A122-4BBFF40DD7BF}: The data type "System.Object" found on column "[Measures].[Reseller Sales Amount]" is not supported for the component "ADO NET Source" (1). This column will be converted to  DT_NTEXT.      Package2.dtsx    0    0   

And then now when the package is executed the ADO NET source does not work pulling to extract the SSAS data in the Data Flow Task even though you can Preview the data just fine:

 

And the Error messages that I got were not what I would consider helpful in resolving the issue:

[ADO NET Source [43]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "ADO NET Source" (43)" failed because error code 0x80004002 occurred, and the error row disposition on "output column "[Measures].[Reseller Sales Amount]" (67)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[ADO NET Source [43]] Error: The component "ADO NET Source" (43) was unable to process the data. Pipeline component has returned HRESULT error code 0xC0209029 from a method call.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "ADO NET Source" (43) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

I was able to find a KB Article that talks about a similar issue when using Oracle (http://support.microsoft.com/kb/959794), so I thought I just needed to install the SQL Server CU2 or higher.  I went with the SQL Server 2008 CTP SP1 just to make sure I was using the latest and greatest and unfortunately this did not resolve my issue.  So what do I do now… post the issue on Microsoft Connect  I went ahead and logged the bug on Connect here – SSIS 2008 ADO NET Source issue with SSAS data source – error code 0x80004002 and have already gone back and forth a couple of times with Microsoft about the issue.  You might sense some of my frustration in the bug posting.  I did decide to move on with the development and switch the data source over to an OLE DB source.  This brings up what I consider to be a bug with that data source is that when the Data Flow Task is embedded in a Foreach Loop container you need to make sure that you put a Script Task after (or this could be before I guess) the Data Flow Task that puts the thread to sleep prior to performing the next loop.  If you don’t you will get the following error:

[SSAS OLEDB Cube Source [928]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E05.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for Analysis Services 2008."  Hresult: 0x00000001  Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".

[SSIS.Pipeline] Error: component "SSAS OLEDB Cube Source" (928) failed the pre-execute phase and returned error code 0xC0202009.

So what do you need to get around this issue?  You need to make sure that the ‘RetainSameConnection’ property on the OLE DB connection manager is set to ‘False’.  I also had some initial issues after changing that property value, so I also added a Script Task to the Control Flow within the Foreach Loop Container after the Data Flow Task with a single line of code that puts the thread to sleep (yes I am a fan of Visual Basic and the code for C# is not different for this except you need to include semicolons):

    Public Sub Main()

        System.Threading.Thread.Sleep(15000)
        Dts.TaskResult = ScriptResults.Success

    End Sub

Once you have verified the connection manager property and have this added to the Control Flow the package will run fine.  Still get the annoying warning messages when trying to preview and work with the OLE DB source, but at least the package runs.

Here is the final layout of my SSIS 2008 package:

If you would like this issue with the ADO NET source resolved so that you can use this with SSAS then please vote for this bug here – SSIS 2008 ADO NET Source issue with SSAS data source – error code 0x80004002

Also, if you want to setup the SQL command for the ADO NET source so that it is dynamic you can do this within the Control Flow by using an expression on the Data Flow Task for the ADO NET source SqlCommand property (same thing for SSIS 2005 and the DataReader source).  It would be nice if you could just reference a variable within the ADO NET data source editor like the OLE DB source, but oh well

SQL Server 2008 Management Studio (SSMS) displaying incorrect build number?

One way to verify the version of your SQL Server components like the database engine, Reporting Services (SSRS), Analysis Services (SSAS), and Integration Services (SSIS) is by connecting to the component with SQL Server Management Studio (SSMS).  Once you connect to one of these components you will see the name of the component along with the version number information and your connection information.  Here is an example of what you will see (not the SSRS and SSIS information):

In looking at this we can see that I have installed SQL Server 2008 CU3 (SQL Server 2008 Build List – 2520 is not listed, but that is for CTP SP1).  The odd thing is that SSRS and SSIS state that I am running build number 1600 which is the RTM release…  That is odd  So how can I verify the version information for these two products or did I really install CU3 for just the database engine and SSAS?

There are quite a few ways to check out the build for both of these two products (and I won’t go into each option here).  For SSRS if I take a look at the properties I will see this information:

And you can see that I am actually on the CU3 build and not RTM.  So now for SSIS I can take a look at the actual version number of the MsDtsSrvr.exe file in the %programfiles%Microsoft SQL Server100DTSBinn directory, but I would see this:

So that doesn’t help out at all.  Apparently this has not been updated since RTM…well then lets take a look at a file in the CU3 list that was updated for Integration Services like dtexec.exe.  By doing so you will see this information:

There are other ways to check out your SSIS build number (How to determine the version of SQL Server 2005 Integration Services), but in my case the information displayed the RTM build until I verified a specific file that was updated like dtexec.exe.

So if this is annoying for you and you would like to see this corrected then please vote on this bug on the Microsoft Connect site – Incorrect Version (build) number in SSMS with SSRS and SSIS

And by the way, you need to be on at least CU3 if you are using SQL Server 2008 maintenance plans and do not have SSIS installed on the same server – FIX: Error message when you run a maintenance plan in SQL Server 2008: "The SQL Server Execute Package Utility requires Integration Services to be installed".  So if you don’t want to install SSIS on your database engine server then make sure you install CU3 if you are planning on using maintenance plans

UPDATE (4/4/2009): I received two comments yesterday in regards to this bug from Microsoft on the Connect site.  I am assuming this means Kilimanjaro?  Or maybe SP2 (unless it will be part of SP1)…

This bug has been fixed for the next release of SQL Server.

Posted by Microsoft on 4/3/2009 at 4:15 PM

 

The problem with SSMS and SSRS has been fixed for the next release. We are following up on the IS problem.

Posted by Microsoft on 4/3/2009 at 4:23 PM

 

UPDATE (4/5/2009): I guess it will be fixed in SP1…so I guess I will need to wait until the RTM of SP1 to see if this is the case or not.  Here is the last comment I just got today from Microsoft:

Thank you for filing this bug.

To clarify, this bug should be fixed in Katmai PCU1 also called Katmai SP1. If you do not find this to be the case, please re-activate this issue or file a new bug in this regard.

HTH
Prash

Posted by Microsoft on 4/5/2009 at 7:24 PM

UPDATE (6/2/2009): This is still a bug with SQL Server 2008 SP1.  Check out one of my more recent blog entries – SQL Server 2008 SP1 Available and Install Experience – build 2531.

PeopleReady Business Intelligence Summit 2009 – April 7 Bloomington, MN

Coming up next month on Tuesday, April 7, 2009 is a free one-day event at the Microsoft office in Bloomington, MN.  The PeopleReady Business Intelligence Summit 2009 event will showcase Microsoft’s market-leading business intelligence (BI) products, solution expertise and customer successes.

I will be speaking in the afternoon about business dashboards.  In the current economic climate it is more important than ever for business stakeholders  to have a pulse on what is happening in their organization.  Implementing an Executive Dashboard allows company executives, managers, and stakeholders at all levels to view critical and role appropriate metrics in a dashboard setting, with the ability to drill down within the dashboard and view those metrics at any level. These dashboards allow an organization to more effectively and efficiently execute on strategy, improve business processes and manage key company metrics proactively.

Here are some of the event details:

Tuesday, April 07, 2009 8:30 AM – Tuesday, April 07, 2009 4:30 PM Central Time (US & Canada)
Welcome Time: 8:00 AM

Microsoft Corporation

8300 Norman Center Drive
Suite 950 Bloomington Minnesota 55437
United States

Event Overview

We are excited to announce that registration for the PeopleReady Business Intelligence Summit 2009 is now open! We invite you to join Microsoft and our Partner Community, which will showcase Microsoft’s market-leading business intelligence (BI) products, solution expertise and customer successes. The Summit will be held on April 7th at the Microsoft Bloomington Event Center.

The event will feature keynote addresses by Jeanne Harris. Jeanne Harris is Executive Research Fellow and Director of Research at the Accenture Institute for High Performance Business where she leads research in the areas of information, technology and strategy. She is the co-author of Competing on Analytics: The New Science of Winning (Harvard Business School Press, 2007), which explains how high-performance businesses are building competitive strategies around data-driven insights that are generating impressive business results.

The Summit is designed to educate you on every aspect of Microsoft’s BI offering with technical and business-focused educational tracks.

Bring your entire BI team to the event to take home all the information you need to get started on sharing, collaborating, delivering business intelligence to your business.

Who Should Attend:

There is something for everyone at this business intelligence summit!

Business decision makers, business users and information workers of enterprise resource planning and customer information systems within your organization. Users and managers of departmental or enterprise reports.

Technical decision makers, developers or IT Professionals working with business systems. People working on business intelligence projects.

If you are interested in attending please contact info@magenic.com.

 

PASSMN April 21, 2009 Monthly Meeting

The next PASSMN Minnesota SQL Server User Group meeting is coming up.  The meeting is on April 21 from 4:30 PM to 6:45 PM.  You can visit the local site to get registered at http://www.mnssug.org or click on the link to register here.

This month the topics are:

Drive Your Way to the DMV (speaker Jason Strate, Digineer) – Dynamic Management Views (DMVs) and Dynamic Management Functions (DMF) have been around since the release of SQL Server 2005. But for many people DMVs and DMFs are still a mystery. Step under the hood and review some of the main DMVs and DMFs in SQL Server. From sessions to caches a walk through of some of the more useful DMVs and a practical look at what they all really mean.

Performance Troubleshooting with Wait Statistics (speaker Joe Beck, Microsoft) – When encountering a performance issue on a SQL Server instance, where do you begin? DBAs sometimes use troubleshooting methods that are biased towards past experience, instead of using a disciplined and consistent methodology. The danger of using instinct-based methods is that you may overlook the true root cause, or spend time troubleshooting less significant bottlenecks. In this session, Joe Sack will walk you through how to troubleshoot performance issues by evaluating Wait Statistics, which represent waits encountered for executed threads on a SQL Server instance. Joe will also discuss common wait type patterns seen in the field, and describe recommended next steps and resolutions. Performance troubleshooting with Wait Statistics allows you to focus on the higher priority bottlenecks, saving you time, and allowing you to apply your efforts towards the most impactful changes.

Just a reminder that the group meets on the 3rd Tuesday of each month.  If you are in the area and available to attend please join us and stop by and say ‘Hi’.

Making yourself marketable

Back in November I did a posting about Contributing to the Technical Community where I stated “There is nothing I enjoy more than helping out the technical community and my colleagues.  There are different ways that I do this.  I talk to people about technology to share my excitement and knowledge about what I know, have read, or done.  Besides talking to colleagues, clients, and people that I meet I have been involved in local user groups (PASSMN and BI-SIG), made screencasts (YouTube and Soapbox – because of YouTube 10 minute limit some videos are posted on Soapbox), and actively contribute in the forums (MSDN SQL Server, TechNet PerformancePoint, SQL Server Central, and internally at Magenic).”

Well now during this economic downward spiral this is extremely important.  It is very important to make sure you are marketing yourself properly and providing visibility to yourself.  I would encourage you to find a user group to participate in, help out in a forum area responding to questions, and networking with others. 

One of the great networking tools that you can get setup on is LinkedIn.  Here you can create a profile, Dan English, put in your experience (basically an online resume) and connect with colleagues, classmates, friends, and groups (like Microsoft Business Intelligence, PASS, SQL Server Central, TWDI, etc.).  You can also setup different types of applications and share presentations.  LinkedIn is really nice because it gives you another option for people to contact you and to network with people either locally or worldwide.

You also need to make sure you are keeping your skill set current.  If you are in technical areas then you can utilize webcasts, podcasts, virtual labs, online books, blogs, wikipedia, attend user group meetings, read forums, etc. to keep yourself up-to-date.  There is tons of information that is completely free along with the tools that you can download to train yourself.  Here are some Microsoft links that you can reference as examples:

• Training
  • SharePoint Server 2007 Training
  • Heroes Happen {Here} Hands on Lab Manuals
  • What’s New (SQL Server 2008)
  • PerformancePoint Server Online Training Videos
  • Microsoft Virtual Labs (TechNet and MSDN)
  • On-Demand Events and Webcasts
  • Azure Services Training Kit February Update
  • SQL Server and Business Intelligence Screencasts
• Tools
  • Express Editions (Visual Studio and SQL Server)
  • Microsoft Business Intelligence VPC Release 7.1
  • Microsoft Virtual PC 2007

So my recommendations for everyone right now are the following:

• Contribute to communities – through forums, blogging, user groups, writing articles, screencasts
• Training – need to stay current; use webcasts, podcasts, virtual labs, attend user group meetings
• Network – extremely important; get your name out there and connect with people (LinkedIn)

By contributing you will not only make yourself more visible to others, but also will build on your writing and presentation skills.  This also allows a way for you to network with others both locally and worldwide.  Training is critical with our ever changing world and make sure that your skills do not get outdated and that you are well rounded.  If you are specialized in just a single area I would recommend checking out some webcasts or virtual labs in a different area.  Like if you specialize in Reporting Services or Integration Services (ETL) then pickup a book, watch a webcast, or do some virtual labs on Analysis Services, MDX, SharePoint, LINQ, or Azure.  Lastly, networking is critical and being connected to others provides a great opportunity for you to market yourself and open the doors to new options.

So what are you waiting for?  Get started now and start marketing yourself today.

UPDATE (3/14/2009): seems to be a popular topic lately.  Just found another really nice posting here in regards to this – Before The Interview: Job Search Techniques.