BDC Crawl Missing Security Identifier

SOLUTION - We rebuild the SSP

Error: I ran across the interesting little error with the BDC which I did not expect. You may know that in the Share Service Provider (SSP) you need to give user permissions to the BDC. Well you may come across this error when you index a content source that is using the BDC:

The parameter is incorrect. (Could not create a security identifier for the identity 'XXX\YYY'. This identity may have been deleted.)

Symptoms: If you view the BDC content source crawl log there will be no errors however this error will be listed as an error in the Crawl Settings for search itself. This error will be created for every record that was indexed.

Cause: Well I found out that the user account in question had been deleted from Active Directory.

Resolution: To resolve the issue I had to remove the user account from the Business Data Catalog permissions as well as from all the BDC applications and entities permissions. Once I did that, run a full index, and everything will work.

Background: The first question I had was why are there no errors on the BDC content source? Knowing what I know about the BDC I suspect this is what occurred. First, there are connection errors to the BDC data source (SQL Server in this case); for instance there were no access issues to the database or the database objects that were being used in the Application Definition File (ADF). Second, knowing this I can only assume there is an error with the way the index is being built. Basically the error is in reference to the validation of Active Directory account that has permissions to the BDC application and its entities.

Deeper Dive: Digging a little deeper the following is the root of the issue. Many developers create an ADF using a tool or by hand and may not add an AccessControlList node. These nodes get added to the ADF when permissions are added in Central Administration. The AccessControlList can be added to both the LobSystem and Entity nodes.

The AccessControlList nodes must be added to the ADF prior to it being crawled. This is why if an administrator goes to Central Administration and gives a user or a group permission to the BDC, they must do a new full index of the BDC content source.

The following is an example the AccessControlList. In this example, account XXX\YYYY is being given full rights. This XML can be added to both the LobSystem and Entity node.

<AccessControlList>
  <AccessControlEntry Principal="XXX\YYYY">
    <Right BdcRight="Edit" />
    <Right BdcRight="Execute" />
    <Right BdcRight="SetPermissions" />
    <Right BdcRight="SelectableInClients" />
  </AccessControlEntry>
</AccessControlList>

Avoiding this Error: The error is occurring because the account XXX\YYYY does not exist in Active Directory account. It is recommend that permissions through a Active Directory group to minimize the occurrence of this error.

K2 blackpoint Questions and Answers

Here are some common questions that you may ask about K2 blackpoint.

Q: We already have blackpearl does blackpoint fit into the picture?

A: All the functionality in blackpoint will be rolled into blackpearl. Blackpoint is targeted toward organizations that want workflow beyond what SharePoint workflow can provide but may not need all the capabilities, such as Visual Studio and SmartObjects that blackpearl includes.

Q: Can I use blackpoint with WSS, Microsoft Forms Server and MOSS?

A: K2 blackpoint can be used with either Windows SharePoint Services v3 or Microsoft Office SharePoint Server. Microsoft Office Forms Server to provided for web-enabled InfoPath forms.

Q: Can I use SharePoint 2003?

A: SharePoint 2003 is not supported.

Q: Can I use blackpoint on a load balanced SharePoint farm?

A: Yes, K2 blackpoint can be used to provide workflow capabilities for any number of SharePoint web front-ends in a SharePoint farm.

Q: What servers in my SharePoint farm do I have to install this on?

A: The K2 blackpoint server components can be installed on a non-SharePoint server or on any of the SharePoint servers depending on how the farm is being scaled. On each SharePoint server, a set of web services is installed to facilitate communication between the SharePoint server and the K2 blackpoint server.

Q: Will I need administrative rights to install?

A: Yes, the install requires a user with local administrator rights on the server.

Q: Will this install anything on my SharePoint central administration site?

A: Yes, the K2 blackpoint install adds a tab to the Central administration site that provides access to administrative pages and tasks for managing the integration between K2 and SharePoint.

Q: Do you do solution or feature deployment?

A: Components such as our K2 web parts are deployed via solution deployment. Other components, such as our SmartObject creation capability are deployed as features and can be activated on a per site collection basis. K2 blackpoint processes are deployed to the K2 blackpoint server and do not reside directly on the SharePoint server.

Q: Can I extend the templates in K2 Studio?

A: No, existing templates in K2 Studio cannot be extended, but custom templates can be built and surfaced within the K2 Studio environment.

Q: Can I get to the code in K2 Studio?

A: No, code-level access is not available in K2 Studio.

Q: Do I have to use a process portal to manage my process?

A: Yes, K2 blackpoint makes use of Process Portals to provide access to all administrative and management tasks related to processes

Q: Microsoft says SharePoint has workflow, what does this do that it does not?

A: K2 blackpoint extends on the out-of-the-box SharePoint workflow capabilities by including a drag-and-drop Office-style process designer, functionality to manage all aspects of your SharePoint environment including lists, libraries and sites, a more in-depth set of reporting capabilities and improved task management capabilities such as delegation and redirection.

Q: Why wouldn't I just want to use built-in workflow in SharePoint?

A: SharePoint workflow provides basic process capabilities for managing documents and list items in SharePoint. However, more complex tasks, such as complex routing rules, escalations or managing lists, libraries and sites in SharePoint can be very difficult and in most cases requires a developer to write code to provide the functionality. K2 blackpoint provides all of these capabilities and more out of the box, without the need to write any code.

Q: Can't I do all this in SharePoint Designer?

A: No, many more complex tasks require a developer to write code to provide the same functionality that is included out of the box in K2 blackpoint. The K2 blackpoint comparison document compares features that are available in SharePoint Designer compared to K2 blackpoint.

Q: How does K2 blackpoint scale?

A: K2 blackpoint can be scaled to support multiple SharePoint servers. K2 blackpoint does not have to be installed no every SharePoint web front end of the farm, it is server unto itself. They can be clustered on their own dedicated resources, load-balanced, and be a highly available workflow environment.

Q: Can I upgrade to K2 blackpearl later?

A: Yes, when your workflow needs become more complex over time K2 provides the ability to easily upgrade to K2 blackpearl.

June K2 blackpoint Community Webcasts

K2 is providing some community web casts on K2 blackpoint. It is open to anyone. If you want to see it in action and ask some questions here is a place you can do it in. No upfront information needs to be provided. Just attend and check it out!

Tuesday June 17, 2008 11:00am-12:00PM CST (GMT-06)

Subject: blackpoint Webcast
Start Time: Tuesday, Jun 17, 2008 9:00 AM PDT
End Time: Tuesday, Jun 17, 2008 10:00 AM PDT
Attendee URL: https://www.livemeeting.com/cc/scna1/join?id=7CND7W&role=attend&pw=w%3B26H9%26Pd
Meeting ID: 7CND7W
Attendee Entry Code: w;26H9&Pd
Location: Live Meeting
Toll free: +1 (888) 233-7876
Toll: +1 (719) 234-7876
Participant code: 733479

June 19th meeting from 6pm – 7pm Central Time. (GMT -06)

Subject: blackpoint Webcast
Start Time: Thursday, Jun 19, 2008 4:00 PM PDT
End Time: Thursday, Jun 19, 2008 5:00 PM PDT
Attendee URL: https://www.livemeeting.com/cc/scna1/join?id=CK67B6&role=attend&pw=w%3B26H9%26Pd
Meeting ID: CK67B6
Attendee Entry Code: w;26H9&Pd
Location: Live Meeting
Toll free: +1 (888) 233-4650
Toll: +1 (719) 234-7876
Participant code: 733479

K2 blackpoint Released for SharePoint Workflow

K2 blackpoint has now been released. I have known about it but was told to keep quiet about it. Now it is out and I am extremely excited. I will make time soon to try out the beta bits that I have access to and get some information out there.

So what is K2 blackpoint? It is K2 response to providing workflow environment that power business and technical users can author workflows. It is skinned in a MS office look and feel environment. Check out the blackpoint website to get some more information. K2 blackpoint provides this perfect middle ground between K2 blackpearl and SharePoint Designer/WF workflows (comparing blackpearl to WF).

The deal is this. Building workflow in SharePoint Designer is flat out limited in scope and scalability. Building in WF requires a significant effort. Building in K2 blackpearl gets you past many of the challenges of WF however it still requires some effort and knowledge with Visual Studio. K2 blackpoint provides SharePoint users the ability to build workflows that can pretty much do anything with MOSS.

The cost of K2 blackpoint is awesome.

• $5,000 for 200 users
• $10,000 for 500 users
• $15,000 for unlimited
  

Pretty much a no brainer. I am so excited about this!

So what is the real difference between K2 blackpoint and blackpearl? I equate it to the difference between WSS 3.0 and MOSS. The comparison worksheet on blackpoint website spells it all out.

So what's in K2 blackpoint?

• A studio environment.
• Deployable on WSS 3.0 and MOSS.
• Many usability enhancements not available in Visual Studio.
• ALL of their SharePoint events (way more than what WF or SharePoint designer provide)
• InfoPath client events
• ASP.net client events
• Mail events
• Web Service events
• Process Portals (totally awesome)
• User specific task list
• Escalations, delegations
• SharePoint and workflow only SmartObjects (Workflow SmartObjects are all of the SmartObjects you get for free as part of the process definition. SharePoint SmartObjects allow you to get access to much of the data stored in SharePoint).
• New SharePoint Service Object with server side filtering and dynamic features
• ADO.net data provider for custom reports and ASP.net pages.
• Out of Office
• Extendibility
• Create custom event wizards
• Create your own reports
• Out of the box reports

So what's not in K2 blackpoint?

• Visual Studio integration (ok – had to say it)
• SmartObject Designer (You cannot create your own SmartObjects. You will have to build up or utilize existing services. The web services template or custom event wizards can be written to satisfy that. If you have data integration requirements, I would not discount want SmartObject bring to the table from the ability to create workflows in a rapid fashion.).
• K2 Workspace (this is a valuable tool and some of the functionality it provides is available by blackpoint)
• Custom report design tool that is in the K2 Workspace
• SmartForms
• SmartFunctions
• Simulation
• Code editing (Reality as workflows get complicated it is very hard to avoid writing custom code. With K2 blackpoint you are forced to create custom event wizards to implement custom code. I would not cheat and implement custom code in a web service just so you can call it from a blackpoint. As well, there are times you need to have custom code events that work with process and activity instance data. Now there is more effort to do custom code and the this should be a consideration of the scalability of the process.).
• K2 connect

I will be working up some things soon on K2 blackpoint…

Deploy Reporting Services (SSRS) Solution

Background

I needed to deploy some SSRS reports for an application we are writing and I wanted to make sure the entire application is deployable through build scripts. When working with SSRS reports I know many get accustomed to deploying using Visual Studio and that is just not an acceptable method for a production deployment. I did some research and with the help of my colleague Ray Barley I was able to the following.

The solution I have is based off the RS Utility for Reporting Services. Go here to get some good background
on it. As well, all of the code is based off of these sample scripts.

My scenario is simple I need to create a folder, then create a shared data connection and then deploy five reports. The challenge I ran into was how to deploy the .rds data connection file that in Visual Studio because I had become accustomed to that being deployed for me. We could not find anything at the moment that would deploy the file however the RS Utility allows for one to be created on the fly.

The way this works is some VB.net code is written that will call the Reporting Services web service to complete the operations that I need. The VB.net code is placed into a .rss file which can be called via command line. What we did is create three .rss files for each of the major operations we need to complete and then created a little master .cmd file that drives the whole deployment. This should be pretty re-usable for future projects.

Deploy Folder

The following code will create a folder in Reporting Services and is in a file named deploy_folder.rss.

'
' Deploy SSRS Folder
'
' Sample Command Line:
' 
' rs -i deploy_report.rss 
'    -s http://<server>/reportserver
'    -v FOLDER="Test Deploy"
'
' FOLDER name of the folder
'

Dim parentFolder As String = "AdventureWorks Sample Reports"

Public Sub Main()

    rs.Credentials = System.Net.CredentialCache.DefaultCredentials

    'Create the parent folder
    Try
        rs.CreateFolder(FOLDER, "/", Nothing)
        Console.WriteLine("Parent folder {0} created successfully", FOLDER)
    Catch e As Exception
        Console.WriteLine(e.Message)
    End Try

End Sub

Deploy Data Source Integrated
Here is the code to deploy a data source that is using Windows Integrated Security and is in a file called deploy_datasource_integrated.rss.

'
' Deploy SSRS DataSource Integrated
'
' Sample Command Line:
' 
' rs -i deploy_datasource.rss 
'    -s http://<server>/reportserver
'    -v EXTENSION="SQL"
'    -v CONN="Data Source=localhost;Initial Catalog=operationsdatamart;Connect Timeout=60"
'    -v NAME="OperationsDataMart"
'    -v FOLDER="/dev/Data Sources"
'
' CONN is the connection string
'
' FOLDER is the folder name to deploy to on the report server; it must already exist
'

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing

Public Sub Main()

  Try
    Dim definition As New DataSourceDefinition()
    definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
    definition.ConnectString = CONN
    definition.Enabled = True
    definition.EnabledSpecified = True
    definition.Extension = EXTENSION
    definition.ImpersonateUser = False
    definition.ImpersonateUserSpecified = True
    definition.Prompt = Nothing
    definition.WindowsCredentials = False

    rs.CreateDataSource(NAME, FOLDER, True, definition, Nothing)
    Console.WriteLine("Data source {0} created successfully", NAME)

  Catch e As Exception
    Console.WriteLine(e.Message)
  End Try
End Sub 

Deploy Data Source Stored

The following code is for doing a deployment of a datasource where the credentials are stored. This will support using a Windows or SQL Server account. The code is put in a file called deploy_datasource_stored.rss.

'
' Deploy SSRS DataSource Stored
'
' Sample Command Line:
' 
' rs -i deploy_datasource.rss 
'    -s http://<server>/reportserver
'    -v EXTENSION="SQL"
'    -v CONN="Data Source=localhost;Initial Catalog=operationsdatamart;Connect Timeout=60"
'    -v NAME="OperationsDataMart"
'    -v FOLDER="/dev/Data Sources"
'    -v ISWINDOWS=True/False
'    -v USER="CORPORATE\rbarley"
'    -v PASSWORD="**********"
'
' CONN is the connection string
'
' FOLDER is the folder name to deploy to on the report server; it must already exist
'

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing

Public Sub Main()

  Try
    Dim definition As New DataSourceDefinition()
    definition.CredentialRetrieval = CredentialRetrievalEnum.Store
    definition.ConnectString = CONN
    definition.Enabled = True
    definition.EnabledSpecified = True
    definition.Extension = EXTENSION
    definition.ImpersonateUser = False
    definition.ImpersonateUserSpecified = True
    definition.Prompt = Nothing
    definition.WindowsCredentials = ISWINDOWS
    definition.UserName = USER
    definition.Password = PASSWORD

    rs.CreateDataSource(NAME, FOLDER, True, definition, Nothing)
    Console.WriteLine("Data source {0} created successfully", NAME)

  Catch e As Exception
    Console.WriteLine(e.Message)
  End Try
End Sub 

Deploying a Report

Finally this is the code that will deploy a report. The code is in a file named deploy_folder.rss.

'
' Deploy SSRS Report
'
' Sample Command Line:
' 
' rs -i deploy_report.rss 
'    -s http://<server>/reportserver
'    -v PATH="C:\\Projects\\OperationsDataMart\\SSRS\\"
'    -v REPORT="WeeklyOperations"
'    -v FOLDER="/dev/WeeklyOperationsReports"
'
' PATH is the folder on the local hard drive where the report .rdl file exists.  You must use \\
'   for every backslash in the path; you must have a trailing backslash (to make this script
'   simpler)    
'
' REPORT is the file name of the report; do not include the .RDL extension
'
' FOLDER is the folder name to deploy to on the report server; it must already exist
'

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing

Public Sub Main()

    Try
        Dim stream As FileStream = File.OpenRead(PATH + REPORT + ".rdl")
        definition = New [Byte](stream.Length) {}
        stream.Read(definition, 0, CInt(stream.Length))
        stream.Close()

        warnings = rs.CreateReport(REPORT, FOLDER, True, definition, Nothing)

        If Not (warnings Is Nothing) Then
            Dim warning As Warning
            For Each warning In warnings
                Console.WriteLine(warning.Message)
            Next warning

        Else
            Console.WriteLine("Report: {0} published successfully with no warnings", REPORT)
        End If
    Catch e As IOException
        Console.WriteLine(e.Message)
    End Try
End Sub 

Running the Code

The following is a simple cmd file will create a folder, create a stored data connection using a windows account and upload five reports. Pretty simple.

set FOLDER=DemoReports

set SSRS_URL=http://localhost/reportserver

set DATA_SRC_NAME=DemoReportsConnection

set DB_CONNECTION=Data Source=localhost;Initial Catalog=Operations;Connect Timeout=60

set DB_EXTENSION=SQL

set DB_IS_WINDOWS_CONNECTION=True

set DB_USERNAME=CORPORATE\jason

set DB_PASSWORD=Pass@word1

set RDL_PATH=C:\\Demo\\Reports\\

rs -i deploy_folder.rss -s %SSRS_URL% -v FOLDER="%FOLDER%"

rs -i deploy_datasource_stored.rss -s %SSRS_URL% -v EXTENSION="%DB_EXTENSION%" -v CONN="%DB_CONNECTION%" -v NAME="%DATA_SRC_NAME%" -v FOLDER="/%FOLDER%" -v ISWINDOWS=%DB_IS_WINDOWS_CONNECTION% -v USER="%DB_USERNAME%" -v PASSWORD="%DB_PASSWORD%"

rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="DepartmentReport"

rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="ExecutiveReport"

rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="AccountingReport"

rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="FinanceReport"

rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="RollUpReport"

Recursive SQL Query

Some time ago I was done a ton do SQL 2005 development and I tons of requirements to do recursive queries. Well I had it come up again where I wanted to return back a tree of data. Not to say I could to the recursion on in some .Net code however it is a little cleaner in my situation to return the data from a stored procedure ready to go. There are tons of various solutions out there so use it if you like… I had found this solution on some blog two years ago, I lost the link and I luckily found the code sitting around deep in the bowels of one my code directories...

Here is the table definition:

CREATE TABLE [dbo].[Reference](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [Phone] [nvarchar](50) NULL,
    [Fax] [nvarchar](50) NULL,
    [ParentID] [int] NULL,
    [SortOrder] [int] NULL,
    [Location] [nvarchar](50) NULL)

The table schema is really simple. ParentID is the ID to the record that is the parent. The SortOrder is the order specific to the level. This will make sense shortly but it is the sort for each level; an example would be:

ID    ParentID    SortOrder
1    NULL        0
2    1        0
3    1        1
4    2        0
5    2        1
6    2        2
7    3        0
8    3        1

So the SortOrder is relative to the grouping the ID is associated to. To select the data so that you get back all the data in the tree and so the data is nicely ordered just like above use this query.

    WITH Tree (id, [name], phone, fax, parentID, sortOrder, location, GroupLevel, SortKey) AS
    (
        -- Anchor query.
        SELECT    DISTINCT R.id, R.[name], R.phone, R.fax, R.parentID, R.sortOrder, R.location,
                        0, CAST(R.sortOrder AS VARBINARY(900))
        FROM    Referenceas R
        WHERE    R.parentID IS NULL
        UNION ALL

        -- Recursive query.
        SELECT    R.id, R.[name], R.phone, R.fax, R.parentID, R.sortOrder, R.location,
                    T.GroupLevel+1, CAST(T.SortKey + CAST (R.sortOrder AS BINARY(4)) AS VARBINARY(900))
        FROM    Referenceas R, Tree as T
        WHERE    R.parentID = T.id
    )
    SELECT
        T.id,
        T.[name],
        T.phone,
        T.fax,
        T.parentID,
        T.sortOrder,
        T.location,
        T.GroupLevel,
        T.SortKey
    FROM    Tree as T
    ORDER BY T.SortKey

This is how it works:

• There is an inner query referred to as the anchor; this will get the root node. One limitation of this query is that there must be a root node. In this case, it is there the parentID IS NULL. However if you do not what to return the root node, you can add WHERE T.parentID IS NOT NULL to the outer query.
• Then there is the recursive query which used the anchor query.
• Both the anchor and recursion are based on the sortOrder.
• The outer query selects data from the two inner queries and orders them based on the SortKey. The SortKey was generated by the two inner queries and will ensure that both the hierarchy and the SortOrder within the results are respected.

New SharePoint Enterprise Search Presentations

Microsoft has just published about 17 hours of new presentations on Enterprise Search. Check them out.

• Module 1: Workshop Overview
• Module 2: Enterprise Search Overview
• Module 3: SharePoint Search 2007 Walkthrough
• Module 4: Search Architecture and Deployment Scenarios
• Module 5: Crawl and Query Processes
• Module 6: Relevance Ranking
• Module 7: Customizing the End-User Experience
• Module 8: Developing Search Solutions
• Module 9: Business Data Catalog Search
• Module 10: Extensibility and Integration for Search
• Module 11: Search Administration
• Module 12: Security for Search
• Module 13: Performance Scalability and Capacity Planning for Search
• Module 14: Search Operations

If you want to check out some free virtual labs, these have been up for a long time but check them out too. There are 8 labs that focus on Enterprise Search.