Blank Report Exports from Access Web Databases in SharePoint

If you have users that have reported receiving empty report exports from an Access Web Database – read on. This problem exists with exporting to Excel or Word. Export to PDF works correctly.

Here is the scenario:

  1. The user runs / views an access report within the access web database on SharePoint, then click Export -> Excel from the bottom of the report

 clip_image002

  1. Excel (or Word, both have the same issue) will open. When it tries to open the file, you get the following error:

clip_image002[4]

  1. You will also notice that the file is completely empty, as you can see on the backstage:

clip_image002[6]

This problem will happen when:

  • You are using Kerberos authentication on your web application
  • You do not have a full SQL Reporting Services instance installed in SharePoint Integrated Mode in your farm
  • SharePoint 2010 (still happens with the Feb 2012 CU patch level, I haven’t tested with newer CUs yet)

You will also notice that if you try to do this with an account that is a SharePoint admin (on the server side), it will work just fine.

So here is what is happening:

  1. When you do the export, SharePoint is generating the Excel or Word file in the temp directory for the identity of the app pool of your web app
    1. If you are using dedicated app pools and app pool service accounts, you have multiple “TEMP” users on your servers
      1. C:\Users\TEMP
      2. C:\Users\TEMP.<DOMAIN>
      3. C:\Users\TEMP.<DOMAIN>.000
      4. C:\Users\TEMP.<DOMAIN>.001
      5. Etc
  2. What should be happening is that it would use the identity of the app pool to write this temp file to the local disk on the server, BUT, when you are using Kerberos, it is impersonating the user that is trying to do the export!
    1. The problem here is that the end user would/should not have any local NTFS file level permissions on the SharePoint server
  3. Because the user does not have write access to the temp folder, the temp Excel or Word file is never generated and the user ends up downloading a 0 byte file

To figure this out and confirm you are experiencing the same issue in your environment, you can replicate the testing that I did.

  1. Shut down all servers in the farm except for one of them that is running the web front end and access services. This is not required, but it makes it much easier because you are only dealing with one set of logs and you know where the traffic within the farm is going. If you are doing this in your production environment, skip this step
  2. Have a test account that has no SharePoint server level permissions (like a regular end user). Grant this account access in SharePoint to the web database for testing.
  3. Open up Process Monitor on the SharePoint Server
  4. Duplicate the issue with the test account
  5. Stop capturing in Process Monitor and start digging and filtering.
    1. Filter on Path contains: \AppData\Local\Temp
    2. Operation equals: CreateFile

You will see entries such as:

Date & Time: 12/14/2012 5:26:43 PM

Event Class: File System

Operation: CreateFile

Result: ACCESS DENIED

Path: C:\Users\TEMP\AppData\Local\Temp\3b1c45a99fe84f9fa26ff9b5201f3052.xls.tmp

TID: 13684

Duration: 0.0001140

Desired Access: Generic Read/Write

Disposition: OpenIf

Options: Synchronous IO Non-Alert, Non-Directory File

Attributes: N

ShareMode: Read

AllocationSize: 0

Impersonating: DOMAIN\testuser

Date & Time: 12/14/2012 5:26:43 PM

Event Class: File System

Operation: CreateFile

Result: NAME NOT FOUND

Path: C:\Users\TEMP\AppData\Local\Temp\3b1c45a99fe84f9fa26ff9b5201f3052.xls.tmp

TID: 13684

Duration: 0.0000347

Desired Access: Read Attributes

Disposition: Open

Options: Open Reparse Point

Attributes: n/a

ShareMode: Read, Write, Delete

AllocationSize: n/a

Impersonating: DOMAIN\testuser

As you can see, it is impersonating the end user to write the file.

Workarounds

1 – Use NTLM

This is bad solution. We should be using Kerberos.

2 – Grant NTFS permissions

This is bad solution. You would need to grant “AUTHENTICATED USERS” change level NTFS permissions on each temp folder on every server in your farm.

Directory: C:\Users\<TEMPUSERNAME>\AppData\Local\Temp

Users should not have local file permissions on the server. That being said, it solves the problem.

Solution

Microsoft has resolved this issue with the SQL Server 2012 Reporting Services add-in for SharePoint.  The new version of the add-in can be downloaded here: http://www.microsoft.com/en-us/download/details.aspx?id=29068

The install was painless on my environment, but always be sure to do these sort of updates in a test environment first!

Advertisements

4 thoughts on “Blank Report Exports from Access Web Databases in SharePoint

  1. office rubbish removal

    Greetings from Ohio! I’m bored to tears at work so I decided to browse your blog on my iphone during lunch break. I enjoy the information you present here and can’t wait to take a look when I get home.
    I’m shocked at how fast your blog loaded on my cell phone .. I’m not even
    using WIFI, just 3G .. Anyways, fantastic blog!

    Reply
  2. Angela

    The Microsoft update did not solve the problem..do you happen to have an updated solution for this? Thanks you for posting one – been researching for ages to solve this error. My error shows that the 0KB dowloaded file is in a different file format(xxx.xls)

    Reply
    1. danmyhre Post author

      If this update did not fix it, I would suggest turning up the ULS logging level on the farm, then replicate the problem, and see if there are any more clues in the ULS logs. What were the results from procmon?

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s