Kerberos and SharePoint

Great link on Kerberos and SharePoint. Probably one of the best descriptions of how Kerberos really works.
http://www.sharepointjoel.com/Lists/Posts/Post.aspx?List=0cd1a63d%2D183c%2D4fc2%2D8320%2Dba5369008acb&ID=2

Quickly adding data to SharePoint List

I was working on a SharePoint project where I needed to quickly add 100’s of list items to a list. The list was not your ordinary list. The list had the following column types.

Text
Person or Group (Show Name field)
Person or Group (Show Account)
Yes/No (default Yes)
Lookup (based on a “division” list)

This list was to store a list of approvers for a workflow application I was working on. The Person columns were used to get the Display Name and the account name of the approvers (keep in mind we use Active Directory for our user store). The Yes/No column indicated whether or not to email the user. And the Lookup column was for identifying what division the person worked for. When it came time to populate this list, the users did not want to have to type in 100’s of people into this list. So they asked me to put them in. Lucky me.

Since I’m lazy, I wanted a quick way of doing this. First, I thought about doing it programmatically, but again, I’m lazy. So, there had to be a better way. Why can’t I just use SharePoint’s native functionality? Please know that what I’m about to describe took me the better part of half a day to figure out. But once I had the process down, it only takes a few minutes to accomplish what I needed to do.

Step 1: Clean Data
The users gave me a spreadsheet of the people they wanted added. And if your users are like mine, the spreadsheet contains data that is not very conducive to “importing”. It is always dirty and never contains all the data you really need. Since I knew that I would be adding users that are in our Active Directory, I did tell them I needed to have a unique way of identifying people. Luckily, we store our employee numbers in Active Directory, therefore, I was able to bounce the list against AD to obtain the approvers’ Display Name and Account ID. Hopefully, you have similar environment where you can easily do this. Once I had the AD data I needed, I continued in Excel and removed duplicates and so forth. Tip: learn about Excel’s VLOOKUP function. Its great!

Step 2: Build an “Import Spreadsheet”
Once I had clean data to work with, I turned to how I was going to get the data into the SharePoint list. I knew the list had the following columns.

Title
DisplayName
Account
EmailBackup
Division

So I went into Excel and created a sheet that had the same column names, mapping the clean data accordingly. For the Title column, I just entered “Approver” for all the rows. For the EmailBackup column, the users indicated which approvers should get an email. Therefore, using some Excel functions (IF and LEN functions), I populated this column with either a value of “Yes” or a blank value. For the Division column, I had a value of the Division for the approvers I was importing. The key here is to make sure the value matches that of one in the list the defines the lookup column. Once done, I had what looked like a clean and complete Excel spreadsheet that matched my SharePoint list.

Step 3: Importing data into SharePoint
When you look at a list, you have under the Actions menu a option called Edit in Datasheet. This is the key to doing what I needed to do quickly. But there are some problems with doing this. To actually get your data from the Excel spreadsheet into the list, all you really have to do is copy all columns/rows from the spreadsheet and paste them into the Edit in Datasheet view. Simple, simple! But, because of the column types, this becomes very problematic. Most notably, the Person or Group column is the biggest problem! When you try to paste, you may get an error similar to the following.

Cannot paste the copied data due to data type mismatches or invalid data. Some source rows which would have created new rows contained invalid values in required fields. These rows were skipped.

Say what? Okay, to save a lot of Googling, what this really means is you have data that cannot be used. And as I already clued you into, the columns that are defined as People or Group are the problem. The reason is because of how the Edit in Datasheet view works with these columns. I don’t have the 100% accurate explanation of this, but it appears that SharePoint gets from its “user store” all possible users and puts them into a dropdown list. If you are in the Edit in Datasheet view and dropdown a Person or Group column, you will see what I mean. So, what is happening is you have users in your spreadsheet data that is not in the SharePoint “user store”. Hence, invalid data.

Step 4: Fixing the Invalid Data error
So, how do you fix this? Easy, return to Excel for a quick moment. Create a new column in your spreadsheet. Using the CONCATENATE function, append a semicolon onto the end of either the user Display Name or Account (keep in mind, this is the data I got from AD). Now that you have a list of users with a semicolon at the end, copy that column and return to SharePoint.

Go to the Site Settings of the site where your list is and go to People and Groups. Create a new group, naming it so that it sticks out like a sore thumb. Now, add users to this group. In the Users/Group textbox, do a paste. What should happen is everything you copied from the spreadsheet (users with an semicolon appended to the end) should have pasted. Uncheck the “Send email” option and click Ok. The group should be populated with all of the users. Great. Now, delete the group.

This is the magic operation to get Edit in Datasheet People or Group dropdown list populated with the values you need!

Step 5: Finally, Populating the List
Return to your list and change back into Edit in Datasheet mode. Now you should be able to copy and paste your Excel data directly into the Datasheet, without error. It is as simple as that!

Validation of viewstate MAC failed as a result of 3.5 SP1

On the eve of installing the 3.5 SP1 framework on our production IIS servers, I uncovered a problem on our development server. Today I went to use a rather old application 2.0 on DHSIISD1 for a totally unrelated note and found that it was getting a runtime error. The error that was being produced was as follows.

Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.

After spending the past 4 hours trying to determine the root cause, I found that it has to do with the installation of the 3.5 SP1 Framework. How I determined it is what introduced the problem is because I could run the ASP.NET app without error locally on my PC (it also runs in production right now). I then installed the 3.5 SP1 Framework on my PC. After the installation the application running on my PC begin exhibiting the same symptoms. Then I began reviewing what changes were being addressed in the 3.5 SP1. There was a feature put in that allows you to set a form’s Action (HTMLForm.Action) during runtime for example. It is my belief that this is what broke the app. Actually, “broke” is rather subjective. There are a few things to make this stop working.

First, the page in question had controls that caused post backs to occur. The page also had a HTML form defined and the form has an action set.

Because of the HTMLForm.Action change, the form’s action was now being honored where as before, it was not, it was just ignored. As a result, when a control on the form caused a post back, the post back caused the framework to honor the form’s action and route to the confrimpage.aspx. As a result, that page tried to process the viewstate and it was not valid because it came from a different page. Thus, the error being generated. I have read people reporting this as a bug to Microsoft. In their case the symptoms were different. I added to the bug report our experience.

Now, you have to go back to the question of why did a 3.5 SP1 affect a 2.0 application. The bottom line is it should not have (the whole reasoning behind frameworks). But it would appear that 3.5 SP1 does more than just a service pack for the 3.5 framework. It also installs a SP2 for the 2.0 framework. If you look at the MSDN article for the HTMLForm.Action, it says that it is supported in 3.5 SP1, 3.0 SP2, 2.0 SP2. So, it looks to me that they also introduced this feature into the 2.0 SP2 as well. Which by the way is not a distributable you can just download.

Understanding Kerberos and Service Principal Names (SPN)

The best explanation and tool for troubleshooting problems with Kerberos and delegation in an IIS environment.

http://blogs.iis.net/brian-murphy-booth/archive/2007/03/09/the-biggest-mistake-serviceprincipalname-s.aspx

Nice Free Geocoding Web Site

I needed to be able to quickly geocode some addresses and this site did just that AND it was free.

http://www.batchgeocode.com/

Method to pass multiple "parameters" to a stored procedure using XML

I want to share with you a technique I have devised. I had a case where there was a stored procedure in a database that allowed you to look something up based on a parameter. The problem I had was that I needed to do this lookup anywhere between 1 – 200 times depending on the circumstances. I really didn’t like the idea of calling the stored procedure 200 times, so I looked into another way. Here is what I came up with, use XML.

To start off with, if you have never messed with XML or even XML in relation to SQL Server, then you typically don’t think of using XML when we are talking about SQL Server. But, with advances in SQL Server and its usage of XML data, you might find more ways to take advantage of it. And this method does just that.

To begin this exercise, we need to build a few things to setup a very simple scenario. Granted, the solution I’m going to illustrate is overkill for this scenario, but I’ll explain how you could use this method in other places.

Create some objects
CREATE TABLE [dbo].[tblDepartment](
[DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblEmployee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[DepartmentID] [int] NOT NULL
) ON [PRIMARY]

Insert some data
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Human Resources')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Sales')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Marketing')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Communications')
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('John', 'Smith', 2)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Nancy', 'Holder', 3)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Craig', 'Jones', 1)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Susan', 'Henderson', 1)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Robert', 'Craft', 4)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Jimmy', 'Foldgers', 2)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Max', 'Eilliot', 3)

Now that we have a couple of tables and some data, let’s talk about what we are wanting to do. Let’s say we have a stored procedure that will allow you to pass it a DepartmentID and it will return all of the employees who work in that department. But let’s say you need to get this information for all departments. In the example data above, you would have to call this stored procedure 4 times and then combine the 4 separate results into one result set. Seems inefficient to me. There has to be a better and easier way. This is where the XML part comes into play.

To start off, we need to first create a stored procedure that will give me what I want to look up. Consider the following.
CREATE PROCEDURE dbo.spGetDepartmentIDs
@RtnDeptID VARCHAR(MAX) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
WITH MyCTE (I) AS
(
SELECT DepartmentID
FROM tblDepartment
FOR XML RAW('item'), ROOT('items')
)

SELECT @RtnDeptID = MyCTE.[I] FROM MyCTE

SET NOCOUNT OFF;
END

So, what does spGetDepartmentIDs do? It returns all the department IDs as XML. So if I execute the following I will get some neat results.
DECLARE @xmlDeptID AS VARCHAR(max)
EXEC dbo.spGetDepartmentIDs @xmlDeptID OUTPUT
SELECT @xmlDeptID AS MyDeptIDs

Results (formatted for easy reading)







You are probably wondering why I’m using a CTE in this procedure. The reason is so that I can get back a defined column name. If you just issue the SELECT statement, the column name returned is nasty and you may not want to deal with it. So now you asking yourself, “So what, you can get back an XML string?” Now the cool part.

What if I pass this XML string onto a procedure that had a incoming parameter of an XML type? And then what if I could join to that XML data? It would be excellent, right? Here is how.
CREATE PROCEDURE dbo.spGetEmployeesForDeptByXML
@xmlDeptString XML
AS
BEGIN
SET NOCOUNT ON;

SELECT E.DepartmentID, E.EmployeeID, E.FirstName, E.LastName
FROM dbo.tblEmployee E
INNER JOIN @xmlDeptString.nodes('//items/item') AS x(item)
ON E.DepartmentID = x.item.value('@DepartmentID[1]', 'INT')

SET NOCOUNT OFF;
END
GO

Now, if I execute the following bit of code, I have my solution.
DECLARE @xmlDeptID AS VARCHAR(max)
EXEC dbo.spGetDepartmentIDs @xmlDeptID OUTPUT
EXEC spGetEmployeesForDeptByXML @xmlDeptID

Again, this is a very simple scenario and there is a much better way of doing this scenario. But, consider this technique if you were integrating with another system. Let’s say you needed to pass a list of some IDs to another system’s stored procedure and have it return the data back to you. Or say you need to insert data into another system. You could build an XML string that you could pass in one call. Again, you could call the other system’s stored procedure x times, or you would have a similar implementation as I have described above.

In terms of performance, I’m sure there is some gotchas with this. In my case, I was dealing with a small amount of data (200 records or less). So the XML string was small. I’m sure if you passed a string that had 1000 items, the join being done could be expensive. Therefore, use this method at your own risk and test it well. To learn more about the XML-type code used above, check out BOL or Google it. It is very powerful.

Running SSIS Package gives: error failed validation and returned validation status "VS_NEEDSNEWMETADATA"

When running a SSIS package with a data pump task, if you receive an error similar to failed validation and returned validation status "VS_NEEDSNEWMETADATA" the problem could be that the security for the account running the package is not setup for the object being called within the data pump task source task. In the case this was found, the data pump was calling a stored proc in another database and that stored proc joined to data in another databases. The AD account for the system that was running the package needed to have rights to execute the store proc in the first database as well as the rights on the view the stored proc was joining to. The best way to troubleshoot this problem is to create a new package and have it execute a SQL task that calls the stored proc. Then run the package on the server and this will help pin point where the problem occurs.