Resolution for SharePoint Designer 2010 Data Source “Non-Specific Error”

Boy, this one was fun… but I learned a few things tonight.

What I learned

• Lesson 1: There are several extra steps necessary now, to expose list data from one site on another using a dataview web part – no more can you simply connect to another library” as you could in SPD 2007 (remember this? http://sharepointgeorge.com/2009/display-sharepoint-list-site-data-view-web-part).
• Lesson 2: SharePoint keeps its own list of trusted CA’s, in addition (and different) to those that Windows manages in its certificates store

What I was trying to do

First off, I was trying to do something relatively simple. I wanted to display the latest blog posts that existed in a blog site, in the root site. I used to do this all the time, with a couple of clicks. First off, this is involves a few more steps now, and I’ll outline them quickly for the uninitiated…

Figure 1 : Create a SOAP Service Connection

Figure 2 : Connect to the LISTS.ASMX Web Service

Syntax to connect is:

https://site_URL/blog_Site/_vti_bin/lists.asmx?WSDL

Figure 3 : Finally, give it a meaningful name and assign whatever authentication necessary

Where it went wrong

Ok, here’s where the wheels fell off the proverbial bus. The above should have worked. But it didn’t and it was (after some painful/long troubleshooting) because the SSL certificate used for the site (our site is behind an HTTPS URL) was not part of the SharePoint trusted list of CA’s. I discovered this through ULS log viewing (see below), some Googling (er, Binging) – see references below, as Claims is also impacted by this…

So, the end result of the above steps “SHOULD” have simply allowed me to add the dataview to any SharePoint page, like this:

Figure 4 : this should have been the end of this story…

Then I could have merrily added items from lists, massaged them with conditional formatting and did all those things I actually enjoy doing, instead of pulling out my hair.

Figure 5 : this is what you SHOULD get

But alas, holidays hate me and I got this instead…

Figure 6 : Mr. Yuck says Blech…

And for those of you searching for this dialog box on Google, here’s me trying to help you below:

Data Source Details

Current Data Source:

The server returned a non-specific error when trying to get data from the data source. Check the format and content of your query and try again. If the problem persists, contact the server administrator.

Troubleshooting

Alas, I did the following…

Using the trusty ULS Log Viewer (http://code.msdn.microsoft.com/ULSViewer), I detected that “the root of the certificate chain is not a trusted root authority” occurred whenever I tried to look at the above data source.

Finally, the fix…

Ok, get on with it, everyone is likely saying at this point…

First, you need to acquire the root cert (DER encoded library) so that you can plug it into Central Administration. There are lots of ways to get the root certificate, but this way worked quick for me:

Open MMC, Add the Certificates Snap-In, find the related Root cert for your SSL Certificate (in my case, it was an Equifax Certificate) and Export it as a file…

Figure 7 : Find your root cert, export it DER encoded

Figure 8 : Save the file somewhere, you’ll need it in a moment

As this point, there are several different suggestions out there, Central Administration or Powershell. Central Administration was quick, easy and worked first time, so why go all Matrix on the problem, eh? 🙂

Figure 9 : Open Central Administration and click on Security

Figure 10 : click on Manage Trust

Figure 11 : give your root a name and browse to that CER file you created and click OK

That’s it folks. I did an IISRESET but only out of habit and I’m too lazy to pull everything out to see if it was actually necessary. 😉

But now my dataviews display properly…

Figure 12 : Hope this helps someone!

References

Thanks to these resources for helping me find my way…

• http://social.technet.microsoft.com/Forums/en/sharepoint2010setup/thread/179f9080-5456-414a-9cc5-1ea07c26cc47
• http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/2dc6af5b-3afa-466d-b6e7-0c66368c5aff
• http://blogs.technet.com/b/speschka/archive/2010/02/13/root-of-certificate-chain-not-trusted-error-with-claims-authentication.aspx