Home - See the Point

1/17/2012

So it has been a while since I’ve blogged, I know. I’ve got no real reason, but I can make up a couple of good excuses: writer’s block, no time, no good ideas, or maybe the dog ate my homework. The truth is, I just have been so busy that I haven’t made time in my down time to do it. My apologies, and I’ll try to do better this year.

This is a good one, though. It certainly had me banging my head against my desk for a while. I was recently on site with a client and one of the things we decided to do while I was there was apply SharePoint Server 2010 Service Pack 1 to their system. When I originally built it, it was prior to the release. So I naturally went out to Microsoft.com and downloaded the service packs. Since there was nothing in the environment yet (we are planning the migration now) and no one was using it, we decided it would be fine to go ahead and work on this during business hours. It’s a good thing, too!

So, like any good administrator, I made sure that I took a quick backup and then installed the bits. That was easy enough, and had no real issues with that. Now comes the fun part. Running the SharePoint Products Configuration Wizard… The nice thing about 2010, is you can kick it off on all of the servers (there were 4) at once and each will just wait for its turn to run. So kicked this off and then went about some other business. Later, when I came back to check on the progress, it stated there were some errors, but acted like it had finished.

Puzzled, I went to look at the PSCDiagnostics logs and found something very non-specific:

01/12/2012 22:41:42 14 INF SyncUpgradeTimerJob: Upgrade timer job failed. Return -1.
01/12/2012 22:41:42 14 ERR The exclusive inplace upgrader timer job failed.

You know exactly what that means, right? Yeah, me neither. So I went into Central Administration to see what I could find. What I found puzzled me completely.

I went to the Upgrade and Migration section of Central Administration to check the status. Clicking on “Check product and patch installation status” showed that the farm and server status were all patched up to Service Pack 1 (14.0.6029.1000). Hmmm ok. So I went back and clicked on “Review Database Status”. Oh my, that doesn’t look right! All of the Content Databases, the Central Admin database (SharePoint_AdminContent), the configuration database, and about half of the databases for the service applications showed “No action required” and had upgraded appropriately. The others, well, let’s just say they weren’t so happy!

Some of those databases stated that “Database is too old and upgrade is required” while the others stated “Database is in compatibility range and upgrade is recommended.” Um ok, they were all installed at the same time, so they should be at the same level, but they don’t show the same. Weird, right? So I do a little research and find some other commands to try to force the databases to upgrade. I tried the following command:

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

It failed on step 5 of 6 each time… now I’m really getting frustrated. Each time, I get the same error in the logs, no further details. Finally, I’m so frustrated that there MUST be something else going on. Thankfully, the PSCDiagnostics log shows the time of the failure down to the hundredth of a second. Since I’m running all of this on a single server at this point, I can know which server is doing the work. So, I crack open the ULS logs and start looking at the times to find the EXACT time that is shown in the PSCDiagnostics log. What do I find? Well at that exact time, it is just telling me that the psconfigui.exe job is ending. Okay, so that isn’t super helpful at that hundredth of a second, so I back up one… and lo and behold if my world isn’t shaken!

01/12/2012 22:41:41.16 OWSTIMER.EXE (0x09F4) 0x0CC0 SharePoint Foundation Timer 6398 Critical The Execute method of job definition Microsoft.SharePoint.Administration.SPUpgradeJobDefinition (ID 8762c5ac-ffd3-4326-8d12-e9079efa384a) threw an exception. More information is included below. The operation cannot be performed on database <Database Name> because it is involved in a database mirroring session. ALTER DATABASE statement failed.

OH MY GOODNESS! So when we built this farm, we built it on temporary SQL hardware because the new, more robust hardware was not in yet. It is now in, and in order to prepare for the move, mirroring had been set up on all of the databases to the new server (cluster). We had also set up an alias to make this move as easy as possible. But I was in shock, mirroring is a very common DR practice, why would that affect the ability to upgrade SOME of the databases? I stress some, because some databases would upgrade and some would not. So I contacted the DBA and asked them to pause the mirroring for the upgrade. After the pause, I had the same results, it still would not upgrade. So I ended up having them completely remove the mirror from the databases, once I did this, it upgraded all databases completely.

Now, what is the point of this blog, you may ask? I am hoping for two things:

1. If someone else has this problem, they will be able to find the issue much easier and faster than I (I looked on EVERY forum and blog I could find, and no search engine even indicated this as an issue).

And 2. If there’s anyone who may have had this issue and found it or worked with someone to find out why mirroring isn’t working, or if there is a fix to make updates work with mirroring to find out that information!

I really hope there is some way to make this work, as mirroring is pretty useful as a Disaster Recovery strategy and it is not something you can turn on and off with the flip of a switch, it is a little more involved than that. I just would like to know if there is some accurate guidance somewhere that will help with this in the future. In our case, since it was to prepare for a move, we just turned it off to complete the upgrade and then turned it on again. Many thanks to Todd Klindt and Sean McDonough for listening to me rant and assuring me that it was unusual and I am not crazy (boy did they get that part wrong, huh?)!

Happy SharePointing!

Posted at 8:00 AM by Lori Gowin | Permalink | Email this Post | Comments (2)

8/17/2011

Plastic tubes and pots and pans, bits and pieces…

Yes, if you grew up in the 80s, you are now singing “Weird Science”. You’re welcome! So why do I want you to be singing that song? Well.. because I am, of course! Actually I think it is the perfect title for this post, and it is such a catchy tune, you just have to sing while reading. I really like the line “Fantasy and microchips, shooting from the hip.” Doesn’t that just really describe what SharePoint is and does?

So why all of the Weird Science? Well, much like Gary and Wyatt, I pieced together a bunch of stuff and made a creation of my own. No, I didn’t create Lisa, but I did create a nifty little something that some of you other admins, devs, and branders might find useful.

Large lists and libraries are becoming more and more common and the issues that come with it are going to be something that all of us will have to overcome. I recently had to troubleshoot a problem with large list throttling on a document library, and needed to see if I could recreate the issue. This sounds simple, get a document library with thousands of documents and create some views and make sure that you have an indexed column to see the difference in your views with filters. Well, I didn’t have the time or desire to create thousands of documents or to upload them, do you?

So I thought hey, there’s bound to be a way to do this with something like a PowerShell script. I began to search and found a few scripts online for uploading documents, but again, I didn’t want to deal with creating the documents to upload. I pinged my good friend and mentor Todd Klindt to see if he maybe had a script already that would do this. He had one that would create list items and shared that with me, which was very helpful in getting this started. I did use it to test the issue with lists, but still needed to work with documents in a library.

So now what? I needed to create something that would create the documents and would also add metadata in columns. I did some research and found that creating list items was a little different than creating files in a document library. I found out using SPFileCollection.Add was what must be used to create items. I found this blog post that showed how to create a new wiki page in a wiki library and modified it some to create a document in a document library.

I took part of the script Todd had given me and part of the script that the other guy had written and used it to test creating multiple documents. As it would turn out, this was the easy part. Below is a script you can use to create a bunch of documents in a library.

   1:  # connect to sharepoint and open the library

   2:  $library = (get-spweb http://portal.s7gear.com/IT/Help).lists["LargeLibrary"]

3:

   4:  “Got List…”

   5:  for ($i=1; $i -le 1000; $i++)

   6:  # try to add a new item # Try to add a new item

   7:  {

   8:  [byte[]]$mydata = “Lori Rocks, with help!”.ToCharArray(); # dummydata

   9:  $item = $library.RootFolder.Files.Add("ItemTitle" + $i + ".doc", $mydata)

  10:  }

This was very cool. Of course, now I had a bunch of documents, but no metadata to work with for list filtering. The $mydata string can be changed to whatever you want the document to contain. Of course, this means it really can only be used for testing, since you can’t create a huge formatted document, but at least you can create a bunch of them!

Now comes the fun part. Adding in metadata. It turns out that using the SPFileCollection.Add method allows you to add the data to the document and title the document, but adding metadata is much more difficult. It apparently includes the ability to create a hash table that can be used for this, but I was unsuccessful in my non-dev attempts to get this to work. So I thought about it and decided that it might be possible to add the metadata after the creation of the document. I didn’t have check out forced on my library, so it should be a fairly simple thing to do, right? Yeah… a dev probably could have done this in their sleep, but not me, oh no, it was a challenge, but one I was willing to accept!

So after repeatedly banging my head on my desk, and bouncing ideas off of Todd (lifesaver!!!!) I was finally able to come up with a solution that would add the metadata to existing documents.

   1:  # connect to sharepoint and open the library

   2:  $library = (get-spweb http://portal.s7gear.com/IT/Help).lists["LargeLibrary"]

   3:  "Got List"

   4:  #SetMetadataforItems

5:

   6:  $library.Items|Where-Object {$_.Name -like "ItemTitle1*"}|ForEach-Object{

   7:  $_["Number"]="1"; $_["Indexer"]="1"; $_.Update()}

This is great, right? It would find only those items that matched the title pattern and add the metadata to the appropriate columns. Which is a great little tool. Now, to put it all together:

   1:  # connect to sharepoint and open the library

   2:  $library = (get-spweb http://portal.s7gear.com/IT/Help).lists["LargeLibrary"]

3:

   4:  “Got List…”

5:

   6:  for ($i=1; $i -le 1000; $i++)

   7:  # create new document

   8:  {

   9:  [byte[]]$mydata = “Lori Rocks, with help!”.ToCharArray(); # dummydata

  10:  $item = $library.RootFolder.Files.Add("ItemTitle010-" + $i + ".doc", $mydata)

  11:  }

12:

  13:  #Add Metadata

  14:  $library.Items|Where-Object {$_.Name -like "ItemTitle010-*"}|ForEach-Object{

  15:  $_["Number"]="010"; $_["Indexer"]="010"; $_.Update()}

With this tool, you can create documents with different titles and metadata. You need only to change the titles and metadata for each series of documents that you want to create and run the script again. In my case, I ran the script 10 times and created 10000 items in a matter of a few minutes, versus the hours it would take to create the documents and upload them. I do recommend using a number with a dash if that is how you choose to name your documents so that you can keep each series separate. Don’t forget if your column is a lookup type of column, you’ll have to make the value of it like: “ID';#Value”. Where the ID is the id of the value of the item.

I hope this helps those of you out there who may need to build in a large list or library for troubleshooting, branding, or performance testing and evaluation.

Oh, and big thanks to Mike Mukalian (@mmukalian) for keeping me sane during the troubleshooting part, I didn’t go into the details in this post, but he was a great help!

Happy SharePointing!

Posted at 3:00 PM by Lori Gowin | Permalink | Email this Post | Comments (1)

8/15/2011

Now you see it, now you don’t

Nope, I’m not the Magic Man, or the magic woman for that matter, but I think the June 2011 CU for SharePoint 2010 just might be some kind of magic! I had the opportunity to work as go-live support for a client that had already installed SP1 and the June 2011 CU in their environment when it went live. I was brought it to help track down escalated issues that couldn’t be handled by support personnel.

As I walked in, one of the first issues they bring to me is that when a user goes to a Status List (formerly a KPI list in 2007) and views the details of the list item, it doesn’t show the list in the view below the details. OK, so maybe this is the way it is supposed to be, I think to myself. I’ll be honest, I know the basics of KPI/Status lists, but I don’t work with them all that much, so I wasn’t sure. So I sat down at my computer and fired up my VM, which is at SP1, but not the June 2011 CU and tested it out.

Here’s how I tested. First, I created a simple tasks list in a team site, then I created a few test tasks and set them at various percentages of completion, 10, 50, 100, pick your poison if you are doing this, just do a few and set them all at different percentages. Then I created a new status list. This seems like something easy, but then you look at it and wonder ok what now? So click New, and create a new SharePoint List Based Status Indicator. I titled the indicator as “Good Status” (creative, I know!) and then set it up to be an average of all of the percentages of completion and if the average was 50% then it was yellow, and 100% then it was green, all other things would be red. My average was 63.33333…% so it was a yellow indicator that appeared.

Then I clicked on the status indicator, which would open the details. Lo and behold, there is the list view showing below my status details. It looks like this:

Ok, so now I’m thinking not seeing the list definitely isn’t the default behavior of 2010. I test it again on one of the client’s dev machines, which is also at SP1, but no June 2011 CU applied. Identical behavior.

Ok, so now I’m thinking, this was an upgrade, so maybe it is just something with the upgrade causing it, so we log into the production server, create a NEW site collection, new site, new lists, and what do you know, it doesn’t show the list. Hmm… ok so it is definitely not just an upgrade issue. We begin to troubleshoot those pesky little issues that sometimes cause these weird behaviors. We check the hosts files entries on the servers, they are all set to come back to the local host for those host entries. OK so the next thought is to check out that “wonderful” loopback check, we check all servers, and the host headers are set up in the registries appropriately so that loopback won’t be an issue. I checked all of the logs, there is nothing except the entry where it was clicked, no errors, no nothing.

Now I’m grasping at straws. I start searching to see if anyone else has had this problem and I come up empty handed. Now what? I decided to install the June 2011 CU on my VM and then check, then activate any features that might be activated on their production servers in my VM and check after each one to see where/when it might break. If I didn’t find the culprit, then we’d start finding some more heavy hitters to help with this issue.

So after staring at blue lines across my screen for nearly 2 hours (it takes a REALLY long time to install the June 2011 CU, especially in a VM on a laptop) I finally am able to go in and test the first difference in our environments. Well what do you know, the list I created before now shows no list in the details now. Thankfully I didn’t have to go any further. This is what appeared:

So for those of you who have installed or plan to install the June 2011 CU, this behavior may occur in your environment. I am reporting this to Microsoft and will let you know in comments or updates if there is any response and if there is a fix that is reported.

Happy SharePointing!

Posted at 5:20 PM by Lori Gowin | Permalink | Email this Post | Comments (2)

6/23/2011

Anonymous Users Need Love (or data), Too

When I’m logged into a site sometimes I stare at the page

and in the back of my mind I hear my conscience rage

Telling me I need some data that I just don’t see

For the first time I wonder just how can it be

Okay, so I’m not LL Cool J and no one wants to hear me rap, but recently I was presented with a need to share external content through SharePoint to anonymous users. Sounds simple, right? Turns out, not so much.

First, we had to set up a site that had anonymous access, that’s the easy part!

Next set an account that can manage the Metadata Store permissions in the Business Connectivity Service Application.

Go to Central Admin
Manage Service Applications
Click beside the Business Data Connectivity Service Application
Click Manage
Click on “Set Metadata Store Permissions”
Add the account
Give it ALL permissions

This was a simple SQL database table that needed to be viewed, so we used SharePoint Designer 2010 to create the external content type.

Open the Site
Click on External Content Types
Click New External Content Type
Name the content type
Click on “Click here to discover external data sources and define operations”
Click Add Connect
Select SQL Server and then click OK
Enter the database server, the database name, and select connect with user’s identity (make sure the user actually does have access to the database, at least as “datareader”) and then click OK
Expand the data source
Select the table you will be using and then right-click on it to create the operations (since this was going to an anonymous site, we only wanted read item and read list)
Save the connection and the External Content Type (ECT)

Since this was going to be anonymous access, we had to set up the ECT to be able to read no matter who was logged on. So we decided to use Revert To Self in the connection. What this means is that the connection will be made with the credentials from the account used as the application pool identity. You will have to make sure this account has permissions to the database you are connecting to as well. This is really the only method I’ve found that will allow anonymous users access in any manner. Don’t worry, you can set permissions on the data in the service application after you have created the ECT.

Go back to the External Content Types gallery in SPD2010
Click on your new ECT
Click Edit Connection Properties.
Change the Default Authentication Mode to BDC Identity

You will probably get a warning that you cannot set it up this way unless you have turned on the ability to use revert to self. If you haven’t, open PowerShell and run these commands. In SP2010, revert to self is disabled by default due to security, however, you will need this ability for anonymous users to have the ability to read external data.

$bcsapp = Get-SPServiceApplication | where {$_ -match "Business Data Connectivity Service"}

$bcsapp.RevertToSelfAllowed = $true;

$bcsapp.Update();

Next you will need to set permissions on the new ECT you have create

Go to Central Admin
Manage Service Applications
Manage Business Connectivity Service
Select External Content Types in the drop down
Select the ECT by checking the box next to it
Click Set Object Permissions
Type NT Authority\Anonymous Logon and then click Add
Give execute permissions (this is all you can give to anonymous users, anyway!)

Now you can create your external list

Go to the site
Create a list by clicking on Site Actions and create External List (if that is not there, you will need to turn on the Team Collaboration Lists feature)
Name your list
Select whether or not to display on quick launch (your choice)
Select your ECT
Click Create
View data if the ECT is set up correctly, you will see data since you are a logged in user.

I followed these steps. however, every time I went to the list it either logged me in or prompted for credentials. How do I truly get an anonymous user access? After a bit of testing I discovered that I could create a DVWP with this list and view the data as an anonymous user. So what was the issue?

Well turns out I was working in a publishing site, as you might have guessed by the second step in creating the external list. In SharePoint 2010 the publishing site comes with the ViewFormPagesLockdown feature activated as well as all sorts of other security features to prevent anonymous users from seeing things they shouldn’t. Since this was truly a public facing site, I could not turn off this feature.

If you run into this same issue, just create a page and add a DVWP web part to the page to be able to view it anonymously. It works, I promise.

Posted at 9:03 PM by Lori Gowin | Permalink | Email this Post | Comments (0)

3/24/2011

Video Killed the Web Master

Ok so, I was never a radio star, although I often wanted to be! Instead I’m just a SharePoint admin who happens to help run a website. OK, now on with the story…

The new Wiki Page layout in 2010 was a great thing to see in demos and is really a cool feature. The Wiki Page allows you to edit SharePoint content directly within the page and even add pictures without uploading them first. No content editor web parts are needed. Adding web parts to the page is easy, as well.

My initial thought on this was “About time!” and I still feel that way most of the time. However, recently I came across an issue. I am very honored to be part of the Women in SharePoint organization. I have been helping run the website and I serve as secretary on the national board of directors with some amazing women. We have our site hosted through fpweb, and as part of shared hosting, you only get foundation. This is fine for most of our needs, and we are grateful that they have donated this service to us.

We had some video interviews that we had done at an event and wanted to post these videos on our site. The videos had been uploaded to YouTube for hosting. I thought, cool, we have 2010, we can use the Media Web Part… What I forgot was that it does not come in SharePoint Foundation! Whoops. So I thought ok. We should be able to embed it like we did in 2007, right? Not so much.

When you change to source view of the wiki page, and try to add the source code to it, it will show it successfully in the editor, but as soon as you close that and view your page, it isn’t there… and when you open the source editor it is gone! Uh oh, now what? You can’t add it to a Content Editor Web Part easily either!

Now what? How do I get this to work? Here was my solution, it was a pretty easy workaround, and for those of you who have SharePoint Foundation 2010, you might want to consider this as a no-code option. I opened SharePoint Designer and created a new, blank html page in the site pages library. On this page, I added the html code that you can pull from YouTube to display the video.

It is important to use a blank HTML page so that no master page or any other graphics or styles are displayed on the page. You’ll see why in a moment!

Once I had done that and confirmed that the page worked in the browser, I went back to my home page and added the page view web part to the page. I configured the page viewer web part to use the URL of the html page I had created and then resized the web part so no scrolling was necessary. Now, I chose to remove the chrome completely from the web part when the page is displayed, however, I could have left this and changed the title to “Women in SharePoint Video” or something like that if I wanted.

Now I have a fully functioning video embedded in my Wiki Page layout! I hope this helps someone who may have had similar frustrations!

Happy SharePointing

Posted at 11:11 AM by Lori Gowin | Permalink | Email this Post | Comments (3)

2/4/2011

Pete and Repeat Sitting on Log

So you know that one right? Pete fell of and who was left? And on and on and on it goes. Some great childhood memories that could have been were probably wasted by doing that stupid joke over and over instead of being out having fun. Sorry, where was I?

Oh yeah, I was going to talk about InfoPath and repeating tables. Repeating tables are nothing new to InfoPath, I remember working with them in 2003, again, probably wasted some good time that could have been used doing something others might consider fun… but I did truly enjoy working with InfoPath, I know, I’m sick. OK back to the real reason for typing this up…

I have been using repeating tables within InfoPath forms for quite some time. They are great for entering the same information in a repeated fashion into a form. There are a couple of gotchas here and there. When you go to promote the fields, you have to make sure you select to merge them so that all values will promote up into the column in your library, and sometimes reusing that information later on in your form can be tricky, but they are great tools for collecting and displaying information.

I came across a requirement recently that would utilize a repeating table within a form, however, one of the fields in the repeating table was going to be a drop-down list that was populated from a SharePoint list, and then the items, once selected, could not be used again within the repeating table.

This was a bit of a challenge. I hadn’t had this requirement before, but knew it had to be done. So I talked to a few people that I trust with InfoPath stuff. Laura Rogers came up with a way using a static list within the form, but I needed it to be dynamic, and there may be more or fewer entries at any time. I thought about using the concatenate function and then comparing to see if the value was contained in a concatenated hidden field. However, when I started doing this, it would compare even the first value, which obviously had to exist in the concatenated field. So that was no good. On the InfoPath dev site, I found where someone had done it in InfoPath 2007, and thought, well… it should work the same, right? Um… since when has that been the case in something that was not readily available? This was no exception.

In InfoPath 2007, the following XPath expression was used as formatting validation for repeating fields:

count(/my:myFields/my:group1/my:group2[my:field2 != ""]) > 1 and (. = ../preceding-sibling::my:group2/my:field2 or . = ../following-sibling::my:group2/my:field2)

However, when I attempted to use this in my form… it didn’t work! No surprise there… something has to be a little different, right? So here’s how to do it. In this form, you will see that there are 3 fields in the repeating table. One field is connected to a SharePoint list, in this case, I simply connected it to the posts lists of my blog for data purposes only. Below is a screen shot showing the design of the form, the data connection, and the structure of the data source.

Now comes the fun part… how to make it work. I started with breaking down the formula that was used in InfoPath 2007. Basically it says, if there is a count of at least 1 entry and the field is not blank, then if it is equal to an entry above it or below it, then put up the validation warning (which you can set). Now how to make it work. Well, after some fiddling and getting some things to work and not work, I figured out it what the “count” that was not working well for whatever reason. So here’s my down and dirty solution. Create a hidden field, In this case I called it CountListItem. Be sure you make this field OUTSIDE your groups. Then set the default value of this hidden field to be a count of the ListItem field.

Now you will create a validation rule on the ListItem field that contains 3 conditions: CountListItem is greater than 1, ListItem is not blank, and then the rule for comparing to see if the field is the same as preceding entries or following entries. It looks like this:

The last item is a single expression. You would create it with the following expression, parenthesis are required:

(. = ../preceding-sibling::my:RepeatingGroup/my:ListItem or . = ../following-sibling::my:RepeatingGroup/my:ListItem)

The results of these conditions give you a red dashed line around duplicate entries and if you are using a browser form, when the mouse is hovered over these warning lines, then your screen tip will appear.

I don’t know if many of you will have this requirement, but now you know how to make it work for you!

Happy SharePointing!

Posted at 8:00 AM by Lori Gowin | Permalink | Email this Post | Comments (0)

1/5/2011

Get back, get back, get back to where you once belonged

During the course of development and testing, there are always some fun and unexpected things that happen. This is why everyone should always have at minimum a test environment and a production environment. Personally I prefer more than that… I like personal dev VMs for those devs who just HAVE to write code, then Dev/Integration where solutions, including 3rd party code are all running like in production to see if anything collides, a test/QA environment where users can log in and test the functionality of the solutions, and then of course production where everything can live once we have made sure that it can co-exist in peaceful harmony with everything. Maybe it can even buy the world a Coke… but I digress.

I have the privilege of working with Cathy Dew, who is a talented designer and brander. During the course of testing branding solutions, Cathy had an issue. She had added, deployed, retracted, and deleted solutions so many times that suddenly her site in her VM was giving her a “cannot connect to configuration database” error. We quickly determined that it could connect and that was a bogus error message, however, the site wouldn’t open in order for her to get everything back to normal for more testing.

I asked my friend Todd Klindt, quickly, if you could reset to site definition from PowerShell. His response, was I’m sure you can, but I haven’t tried it… and he gave me a couple of pointers on where to possibly find the information. I also did some searching (I google-d with bing) and found a couple of other people who had posted these scripts that would do it for many sites all at once. Well, that was great, but we needed it for just one site.

After some trial and error I found that the following 2 lines worked wonderfully to reset the site back to the site definition.

If you want to copy and paste, here they are in text form:

$web = Get-SPSite <url>

$web.RevertAllDocumentContentStreams

Hopefully this is simple and helpful enough for the next person who runs across this issue!

Happy SharePointing!

Posted at 4:27 PM by Lori Gowin | Permalink | Email this Post | Comments (2)

10/22/2010

Begin at the Beginning

To quote Maria Von Trapp, “Let’s start at the very beginning, a very good place to start.” In case there is anyone who doesn’t know, SharePoint heavily relies on SQL. In fact, a SharePoint farm, isn’t really the servers, its the configuration database that lives in SharePoint. The two are so closely related, it is no surprise that in order to utilize Kerberos for SharePoint, you have to set up Kerberos for SQL first.

Now, if the SQL service account is a domain admin account when SQL is first configured, then Kerberos is set up for you, which saves you a lot of trouble, but you will still want to verify that it is configured.

Here is an easy way to verify the SPNs and delegation for SQL:

1. On a machine connected to the domain, open ADSIEdit. In Windows Server 2008, on a domain controller, this is now available by clicking Start>Administrative Tools>ADSIEdit. On other machines, you may have to install the utility and access it by running ADSIEdit.msc.

2. Once ADSIEdit is open, expand the tree down to where you see the service account SQL is utilizing to run.

3. Right click on that account and click Properties.

4. Scroll down through the properties until you see servicePrincipalName. Double click the property.

Here you should see at least 2 entries they should be in this format: MSSQLSvc/servername.domain.com and MSSQLSvc/Servername.domain.com:1433. You may see more entries if other SQL servers are utilizing this same account as the service account.

If the entries are there, you will need to verify that you can connect using Kerberos. Open SQL Server Management Studio on a separate machine and connect to the SQL instance you are testing. When you connect, you can then go to the server where SQL is running and you should see an entry in the Windows Security Logs where there is a logon from the account name you are using to connect and the Logon Process will say Kerberos.

If you can see all of this, then you are ready to move on, you can stop reading if you really want to, but I suggest you read on, just so you know how to handle it at that time when you have to manually set the SPNs and delegation. Believe me, that time will come!

So there are a couple of ways to set up Kerberos manually for SQL. An easy way is to use ADSIEdit as we did to check for the SPN and enter the SPNs as shown above, the other is a little bit better way, and that is using SetSPN.

SetSPN is a command line tool, so you will have to open a command prompt. You can also verify the SPNs using this method as well by typing the following: SetSPN –Q servername.domain.com. This will return both entries for SQL as well as the account the SPN is registered to.

To add the SPNs, you will need to type: SetSPN –U –A mssqlsvc/servername.domain.com sqlserviceaccount, be sure you replace servername.domain.com with YOUR server name and sqlserviceaccount with the actual service account you are using. You will need to do this both with and without the port.

Once you have that done, you will need to make sure you set the machine up for delegation. Now, if you are using SQL on a cluster, you will need to set up the cluster name that is used, not the individual nodes of the cluster for the delegation.

To trust the machine for delegation, open up Active Directory Users and Computer (ADUC) and expand the tree until you can see the machine name in the right hand pane. Open the properties for that machine and click on the Delegation tab. Then ensure the radio button is selected for “Trust this computer for delegation to any service (Kerberos only).

Once you have set up the SPN and the delegation, you can test using the same method as suggested above with SQL Server Management Studio.

The next installment in my Kerberos series will be setting up Kerberos for SharePoint. Hope you learned something from this one!

Happy SharePointing!

Posted at 10:56 AM by Lori Gowin | Permalink | Email this Post | Comments (0)

9/10/2010

What is Kerberos and Why Use it with SharePoint?

Kerberos is an authentication method developed at MIT. The idea behind it is that a third party that is trusted will grant a “ticket” that is utilized to prove the identity of users. Kerberos is the default authentication method used by Windows Servers since Windows 2000. The purpose behind Kerberos is to ensure that the identity of users is secured as well as to protect the network resources that will be accessed by these users. The use of the keys and tickets provides additional security to interactions across the network. The key is used to secure the interactions while the ticket is used to prove the identity.

Why do we need Kerberos with SharePoint? SharePoint is a server technology, however, not all data that is utilized in SharePoint lives in SharePoint. By that, I mean that you have the capabilities, especially with SharePoint 2010 to access data that is stored in other applications and databases. Accessing this data often requires credentials to be passed across from one server to another server. This is known as a “double-hop.” Another time you see this out of the box is with RSS. Because the RSS feeds within SharePoint can be used to aggregate data from other SharePoint sites, you may want to make use of these, however, often in load balanced situations, you may see the same double-hop issue. There are also some third party tools that may be used to access this data that will also require Kerberos to be implemented.

The good news? You can implement Kerberos when you set up SharePoint. It is best if this is done from the initial deployment. However, if you already have your SharePoint farm in production, you can still go back and change the authentication methods for your web applications to Kerberos and set the SPNs appropriately and your sites will still be accessible, and more than likely, your users will never know the difference. The username and password will still be collected from the user the same way. One thing it is important to note here is that the users may have to ensure that the sites are in the trusted or local intranet zones in order for the authentication to work correctly. This can easily be set via group policy and pushed out to your users, or you can send instructions on how to manually add the sites to these zones.

I am working on a series of articles around Kerberos and will include the technical details of moving an existing SharePoint 2010 implementation from NTLM to Kerberos. Keep an eye out for the next posting!

Happy SharePointing!

Posted at 10:54 AM by Lori Gowin | Permalink | Email this Post | Comments (0)

8/10/2010

Where are you going?

I had the privilege last year of attending the Best Practices Conference in Washington, DC. It was a great conference, full of good information delivered by some of the most dynamic speakers I’ve ever heard. The next Best Practices Conference in Washington, DC is August 24-26. This year, I have been given the opportunity to speak. I am both honored and humbled to be presenting alongside some of the world renowned experts that will be speaking there.

To be certain, if you have never attended the Best Practices Conference, it is very similar to other conferences in that you have a ton of knowledge crammed into a short time. The difference that I found with the Best Practices Conference was that it was more about WHY something should or should not be done than what you can or can’t do or even how. Let’s be honest, those of us who work with SharePoint know that if we really wanted to, we could probably make SharePoint do most anything we want it to do, especially with some duct tape and maybe (and its a BIG maybe!) a developer or two at our disposal! However, what you will learn at this conference is that while you CAN, you may or may not WANT to do just anything with SharePoint.

I have a mental “wish list” of conferences that I like to attend and have attended. The Best Practices Conference is one of the ones that I have on the top of my list. I am looking forward to this year’s conference, not only because I’m speaking, but also because I will be working with an amazing group of people. I have been given a very special opportunity to help organize a group called “Women in SharePoint.”

Women in SharePoint is an organization that is working to encourage women who work in IT, specifically with SharePoint. One goal is to encourage more young women to consider IT fields and mentor those who wish to do so. The organization, while focused on encouraging women does not exclude men from the efforts, but encourages them to get involved as well by mentoring, encouraging, and supporting women in the same field. Women in SharePoint is working with Bamboo Nation to coordinate the live-blogging of the Best Practices Conference. Anyone can live blog!

If you haven’t, I encourage you to register for the Best Practices Conference. It will be a great event and you won’t want to miss it. If you are already registered for the conference, register to help live blog! Above all, be sure to have a great time there, and come find me if you can!

Happy SharePointing!

Posted at 12:50 PM by Lori Gowin | Category: Community | Permalink | Email this Post | Comments (1)

RSS Feed