Solving Double authentication prompt with Document Libraries in SharePoint for Internet Sites

Ran into this issue while I was on vacation. Dawn loved that while I’m in paradise, I break out my laptop to diagnose the issue. That took some serious explanation.

Anyways, I have an internet site deployed on SharePoint For Internet Sites (FIS). Although the site is setup for anonymous users we have a secured document library using a custom claims provider. The unfortunate thing I’ve found is that Office docs are not claims aware. What this means is that a user could login to the site using the custom claims provider, navigate to the doc library, click on a document, and they’re hit with another authentication prompt:

clip_image001

But they’re already logged in. What gives!

One thing to keep in mind with the double auth is that the user will only be required to do this once as it appears a cookie is cached in Office, but the particular site in question users typically only open one document at a time and then go on their merry way. So the double authentication is not gonna fly.

Workaround: use _layouts/download.aspx

I created a separate links list and created links to each file. The syntax you should use is as follows:

http://site.com/_layouts/download.aspx?SourceURL=%5Blibrary name]/[file name.extension]

Using the download.aspx convention causes SharePoint to use a different web service to deliver the file to the user. It adds a few steps for the admins but I’d rather make my life harder than make the site user’s life hard. If anyone knows a different way on how to solve this problem I’m all ears.

SQL Query Basics

Needless to say, I’m a SQL n00b. For the longest time I drank the Microsoft Kool-aide and believed “you shouldn’t touch the SQL side of SharePoint.” And when I say “shouldn’t touch” I mean absolutely no touching or querying of any kind. Well, I’ve wised up. It’s not that you can’t touch anything, just be mindful of what you can and can’t do. Querying – as long as you do it with no lock – will not adversely impact anything. And above all else, you must have a healthy respect for what you’re doing lest you decide to anger the SharePoint Gods. This post is for all my peers out there that do a little SP Admin work on the side, but – for a variety of reasons – don’t get into the SQL side of things much.

SQL Management Studio is the best (and only?) tool used to query SQL. It’s a pretty straightforward, easy-to-use product. Open it up, connect to a database, and click the “New Query” button:

image

Pretty simple. From here you can query SQL (i.e. “ask” SQL things):

USE [database name]

select * from [database table]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The Use statement dictates which database you want to query. There is a dropdown near the “New Query” button, but the Use statement is so much easier to use. Plus, I’ve – on many occasions – forgot to select the correct database from the dropdown. I just use the Use statement from now on. “Select *” means you want to return all the columns in that table. This can be helpful to use when you just want to poke around and learn about SharePoint’s innards.

Use [database name]

select * from [database table] with(nolock)

 

“with(nolock)” is important because it doesn’t lock up the table while you’re querying it. I again learned this one the hard way. Locking up tables is a bad thing, meaning no one else can use said table until the query is done. Don’t do what I did and lock up the content database. Does not make for good job security. Furthermore, it’s generally a good idea to run just about any query (at least in the case of SharePoint) with no lock on it.

One last query nugget of wisdom. Replace the * in the select statement with column names (comma separated). So for example:

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Use [database name]

select LastName,FirstName,Address from [database table] with(nolock)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Your query will return the results with just the Last Name, First Name, and Address columns. Doing so will help clear the query results up and remove any extraneous data.

These 3 basic queries – while n00b’ish – are very valuable to know and will hopefully help you when working with Management Studio.

SQL Saturday #118 was a success

So I admit that I’ve been slacking on my blog and Twitter posts, but I swear it was for a good reason. I was preparing for my presentation at SQL Saturday #118 here in Madison this past Saturday.

My presentation was entitled “Demystifying PowerPivot from the SharePoint Admin’s Perspective.” Feedback was overwhelmingly positive. Had a great crowd and they asked some fantastic questions.

I’ve had several inquiries about my slidedeck. Ask and ye shall receive! PowerPivotSQLSAT118.pptx

I also be posting the slides to the PASS site as well.

I’ve been asked to give this (or similar) presentation at Tech on Tap & MADPASS. There are a whole handful of other possibilities for this presentation too (i.e. SharePoint Saturday Chicago and/or St. Louis, SPUG’s, and maybe even another SQL Saturday). I’m all about getting out there and spreading the PowerPivot love.

Thanks again to everyone that came out.

Update to REST API

Still playing around with the REST API. You can read the official Microsoft documentation HERE.

You can add filters to your URL string to refine what items you see. Try this with a Choice column:

http://%5Byoursite%5D/_vti_bin/listdata.svc/%5BList title]?$filter=[Choice column title]/Value eq ‘[Value you want to filter on]’

Be sure to remove the square brackets and everything in between. Also, be sure to pick a choice column that does not allow multiple selections. I’m still playing around and learning more every time I try something, but I figured I would share this little nugget.

SharePoint’s REST API and PowerPivot will change your life

Make sure – before you read any more of this post – you have ADO.Net Data Service Update for .NET Framework 3.5 SP1 installed in your environment. Linkage:  http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=2343

Now with that out of the way, let me say again, the SharePoint REST API and PowerPivot will change your life.

Slap this into the URL Address bar:

http://site.net/sites/sitename/_vti_bin/listdata.svc/

From there you’ll be presented with a list of all the lists and libraries that are compatible with the REST API. Grab the list you’re interested in and tack it on to the end of the above URL like so (case sensitive so be as exact as possible):

http://site.net/sites/sitename/_vti_bin/listdata.svc/%5BList Name]

You should then be presented with an ATOM feed (looks something like an RSS feed). You may or may not see much, which is fine. The background XML has all the metadata of the list/library you’ll need.

Next, fire up Excel with the PowerPivot add-in installed. Open the PowerPivot window and select “From Data Feeds.”

image

Paste in the URL above (e.g. http://site.net/sites/sitename/_vti_bin/listdata.svc/%5BList Name]) and then click Next. It’ll chew on that for a second. Then click Finish. The list will now be imported into memory (i.e. PowerPivot does its thing). Keep in mind here that the REST API only imports 1000 items in at a time. However, for a lot of lists and use cases this is more than sufficient. [See update below.]

Close the PowerPivot window. Go back to Excel and click Insert > Pivot Table/Chart. Then click the “external data source” radio button and then click “Choose Connection”

SNAGHTML5798800

Find the PowerPivot Data connection you just created above and click Open. Then click OK.

From here you can add all the Values, Slicers, Filters, etc. you want.

image

Now how will this change your life? The applications of this are endless. One example could be a project list sliced and diced to your users’ liking and displayed via Excel Services web part. Users get the familiar Excel look and feel while you can rest comfortably knowing that they’re not manipulating your list data. You get far more control over the look and feel too as opposed to PerformancePoint. In addition, you get the ability to integrate outside data as well (try integrating TFS or Oracle data in PerformancePoint). Winking smile

But why not just use SSRS? Good question. Not everyone has access to SSRS. That takes a considerable amount of time, energy, resources, and cash to get that going. But again, with the ability to use slicers and allow your users to see what they want, when they want, that alone makes this a pretty powerful feature in your bag of tricks.

Anyone else tried this? Any other use cases come to mind?

UPDATE 4/20/2012

So only 1,000 items will show up in the ATOM view; however, PowerPivot can pull ALL the items down. VERY COOL! I tested it on a list with +32K items and PowerPivot had no problem.

Add Form Filter to filter single column

A manager had a request the other day for a filter that would allow him to quickly search for an item in a list by the ID. I had done this numerous times at my former employer but couldn’t remember how to. I reached out to 2 former colleagues (Erich and Erik) and they helped me out.

  1. Site Actions > Edit Page
  2. Add or Insert web part
  3. Choose Forms > HTML Form Web Part
  4. Edit Web Part
  5. Click Source Editor button in edit web part pane
  6. Add the following code
  7. Click OK
  8. Choose Connections > Provide Form Values to > Title of the list you wish to filter in the web part’s dropdown
  9. Leave “Get Filter Values From” and click Configure
  10. Choose the Consumer Field Name you wish to filter
    Note: You cannot choose more than one column with this type of filter
  11. Click Finish
  12. Click Stop Editing in the Ribbon

You can also find more details around this on Microsoft’s site:  LINK.

Large Lists and Datasheet view in 2010

I’ve been in a never-ending battle with 2 large lists (29K items). I migrated the lists from MOSS, which took a GREAT deal of time, patience, and effort (topic of another blog post). I got the lists migrated, views setup, and permissions all where they needed to be. The views consisted of datasheet views since the users of these lists were used to doing massive uploads at once. I got it all setup and thought “job well done.” Well not really…

Shortly after everything was setup users noticed that they would be hit with the list view threshold of 5000 items. This setting is a global setting, and one that you don’t want to change. Just trust me on this. But their views worked fine in MOSS. What gives?

…taken from: http://technet.microsoft.com/en-us/library/cc262813.aspx

In MOSS there was no limit on datasheet views like there is in 2010 (note highlighted text above). My guess is that datasheet views were treated like Standard views meaning it would filter on un-indexed columns no problem. SharePoint 2010 queries a little differently than MOSS did so if you have a filter in a datasheet view that should return 100 items based on an un-indexed column, but the list has 10,000 items, you’ll hit the list view threshold since it is querying all 10,000 items. Adding an index will alleviate this issue.

You’ll want to index the column that the view is filtering on. In this case it was the “Status” column. I added the index, SharePoint crunched on it for about 5 seconds, and then I tested it with several real users and a test user. The lists are as good as new!

Where’d my InfoPath options go?

Real quick post here.  Working with a business partner who wanted to create InfoPath forms. Easy enough right? Oh but wait, she doesn’t have the option. Is there a configuration in CA to modify? Is it a down Service App? What do I do!?!

Well, for one I knew it was a site setting or feature since other site collections allowed users to use InfoPath. So I figured I’d start at the site collection features. Navigate down the page and lo and behold:

SharePoint Server Enterprise Site Collection feature

I go ahead and hit Activate. It chews on that for a minute or two and after that you should see this:

SharePoint Server Enterprise Site Collection feature activated

Now my user can create any and all InfoPath forms they want.

Side note: I know this isn’t the sexiest post, but I found it to be one of those “Gee, I wonder how I turn that functionality on” and figured someone else might run into the same issue.

The command line IS your friend: SP hotfix crisis averted

I’ve always been somewhat nervous about using the command line for doing things. But today has turned me into a convert.

I had to install a particular hotfix in order to remedy an issue we were having with alerts.  I did my due diligence by documenting my procedures, making sure to fully research EVERYTHING. I tried the hotfix in DEV and everything looked good to go. I followed Microsoft’s guide TO THE LETTER (link). Last thing to accomplish was to run PSConfig. I figured why tempt fate by doing it in the command line and just do it in the GUI.

I let the Wizard do it’s thing with each server, checked Central Admin (CA) to make sure everything was good, and then figured I was done. 25 minutes tops. I opened a fresh browser and navigated to the site. NOTHING. [enable freak out mode]

I did EVERYTHING I was supposed to do. I started to panic. Would I really need to roll back? CA told me everything was running correctly. I restarted IIS. Contemplated rebooting. I had no idea what to do.

Then, on a hunch, I figured why not try it in the command line. There are lot’s of times I’ve tried to do things in CA, only to find PowerShell or STSADM is the only way to ensure it gets accomplished. So I went to Google and did a little searching and I came up with the following:

  1. Open up a command terminal (Run > cmd > Hit enter)
  2. Change Directory to the BIN folder in the 14 hive (cd C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14BIN > Hit Enter)
  3. And type in the following command:

    PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures

Once completed I hit the site again and we’re back. Whew.

Let this be a learning lesson. If you have the choice to do it in the command line vs. GUI, always pick the command line. Will I follow this advice every time? Probably not, but I’m a glutton for punishment.