BryansGeekSpeak

SQL Server 2008R2 Linked Server to Oracle 11.2.0.2 Instance

2011-12-08T13:05:00.001-08:00

My notes on doing this particular setup using SQL Server 2008R2 and Oracle 11.2.0.2 (64bit versions) on Windows Server 2008R2 64bit Enterprise edition.

Install Oracle 11.2.02 64 bit client

Install the oracle client on the SQL Server machine where you want to add the linked server. Install the following components only using the custom installation option.

Oracle Database Utilities
SQL*Plus
Oracle Net
Oracle Provider for OLE DB
Oracle Data Provider for .NET (I dont think this is needed, but I always install it out of habit!)

Reboot the SQL Server box

Reboot the server so that SQL Server 2008R2 can pick up the installed Oracle provider. When the server comes back up, you should see the new oracle provider appear in the linked server providers section in SQL Server Management Studio.

Configure Oracle Link Provider Properties

Right click on the OraOLEDB.Oracle provider and select properties. Check the following options and save changes.

Dynamic parameter
Allow inprocess

Configure TNS Names

At this point, you need to configure TNS entries for connecting to your Oracle instance. The easiest thing to do is take an existing tnsnames.ora file from a working server and copy it to the "network\admin" directory in your root oracle install path. ( in my case, my TNS names file is in the path "c:\app\product\11.2.0\client_1\network\admin\tnsnames.org")

Create Oracle Linked Server

Go back into SQL Server Management Studio and browse to the Linked Servers folder. Right click that folder and select "New Linked Server...".

In the general tab...

Type in the name of the linked server (whatever you want) into the "Linked server:" field
Select "Oracle Provider for OLE DB" as the Provider
Put in any value you want for the Product name - something like "Oracle" will do.
Set the Data source: to the TNS name of the connection you want to use from your TNS file.
Leave the provider string empty

In the security tab...

Select the last radio button "Be made using this security context"
Enter the oracle account username/password in the fields under the checked radio button

...and click "OK" to finish up.

SQL Server 2008 - Dumping VARBINARY data to a file

2011-12-01T15:11:00.001-08:00

I couldn't find a way to convert varbinary column data to a file using SQL Server 2008 Management Studio, so I did some googling and found info on using the bcp.exe command to do it (I believe this comes with the Management Studio install). In my case, I was extracting a PDF file stored as varbinary:

c:\temp>bcp "select MYVARBINARYCOL from MYTABLE where id = 1234" queryout "c:\filename.pdf" -S MYSQLSERVER\MYINSTANCE -T

Enter the file storage type of field filedata [varbinary(max)]:
Enter prefix-length of field filedata [8]: 0
Enter length of field filedata [0]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] n

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 15 Average : (66.67 rows per sec.)

I used the -T option to use windows authentication to connect to the DB. If you use password auth, you'll need to use the -U and -P switches to specify a username and password.

Understanding REST-ful Web Services

2011-11-14T13:04:00.001-08:00

Links to more info on REST-ful web service design. Most of my focus will be using .NET WCF data services for building web services.

Another Painful Day At Work - Database Column Widths

2011-11-09T14:01:00.000-08:00

Another painful day at work trying to justify to my project managers why we should be using generic long varchar2 width/size specifications in my DB schema, for columns storing things like user entered addresses and phone numbers. Things that our web application will constrain and validate (like phone number format) - things that our database does not need to care about. For these "generic" columns, we just want to accept whatever data the web app passes to us and persist it. Nothing more.

There is an IBM article that explains my point much more clearly than I can manage in a meeting with my co-workers:http://www.ibm.com/developerworks/web/library/wa-dbdsgn1/index.html

Strings and numbers

In general, numerical types pose few problems -- just select one that is large enough to support the necessary range of values.

The attempt to find the optimal width of a string column is usually not worth the effort. You can avoid a lot of confusion later on by making all text messages of type varchar(n) and limiting yourself to a few standard string lengths and introducing aliases for them, such as: 32 bytes ("Label"), 256 bytes ("Note"), and 4k ("Text").

Even if other business requirements restrict the maximum length of certain fields to specific values, the DB schema is arguably not the best place to enforce these rules. By the time the data reaches the DB, it is too late to do anything about it (except reject it). Individual restrictions, stemming from business rules and requirements, should be enforced by the business logic layer, which handles user interaction and input validation. On the other hand, maintenance of the DB schema is considerably simplified if it is restricted to a handful of different string attributes.

Limit the use of fixed-width strings to codes of all sorts (as opposed to variable-length strings for real text). Keep in mind however, that many seemingly fixed-length codes do actually become wider over time. The prudent DB designer tries to avoid anything similar to the Y2K problem for new development work.

iOS5 iCloud - Not as open as I had hoped!

2011-10-14T10:53:00.000-07:00

I have been using dropbox for a year or two now and when I heard that iOS5 would have iCloud features baked into it, I got all excited! Finally I can get rid of all my 3rd party cloud services and manage everything in iCloud! It made sense at first, knowing that all my music would be in the cloud anyway (for me thats about 30gig of space right there!) but now that I've played with iOS5 for a bit with cloud features enabled...not as awesome as I had hoped. Dropbox is still the champ in the cloud storage department, if you ask me.

Lets just jump right to the kicker. All the cloud services sync up to any ios device you own, and appleTV. thats fine, but still very limited. How can I access icloud without ios? Say, with a pc or a linux laptop? The only option I've found is the icloud web interface...which is rather dissapointing.

And my first visit to icloud.com turned into an "um...what?" moment.

Dude, where are my pictures? Photostream isnt even viewable from here? Wacky! I'm guessing you might be able to sync up photostream with itunes on pc, but havent even looked yet - kind of pointless thing for me anyway, I'd rather have it available on the web.
Contacts and calendar? Ok, sure, thats neat but not something I use since all that is on my iphone already.
Mail and FindMyPhone are old services rolled into the new icloud domain. The mail is just me.com mail (if you setup a me account) and the find my phone stuff isnt cloud storage related.
iWorks is the only service they expose that is an actual cloud based service, but its locked down to folks who paid for iworks apps on ios. Since I dont have iworks, I cant truly test out what this service even does. I assume it renders a web app version of your files, but my guess is they wont allow file downloads (that seems to be the key point for all their icloud stuff - no direct file access, you must use ios x_x)

..but the good stuff about icloud so far for me

Cloud backups. No more syncing my phone with itunes! Hooray! I really hope we can do iphone updates over the air in the future too so I could remove itunes from my pc if I really wanted to.
Sync music/video from itunes over the air. Again, nice to be able to keep my device up to date without plugging in to sync with itunes. I could start using Linux now that I dont need itunes to keep my iphone in sync! yay!

And thats really. But those two things, cloud backup and music/video sync over the cloud are really huge! That alone makes the service worth wile in ios5, but I really wish they would take the time to open up the icloud service to expose more data to non ios devices, even if only via web apps. Something that I can use my pc, mac or linux box to access without itunes.

Something Neat - P2PU Beginning Game Development with HTML 5

2011-10-13T11:52:00.000-07:00

I just randomly found this site the other day and decided to give this tutorial a try since I really need a good Javascrip/HTML5 refresher ... and wow, the videos are great! (and the instructor is just ... lol)
It covers Javascript basics, building/implementing javascript objects, HTML5 canvas, jQuery and a bunch of maths for simple game physics x_x

So here you go, a link to the course for building games using pure HTML and Javascript.

http://p2pu.org/en/groups/beginning-game-development-with-html-5/

SharePoint 2010 - Removing Orphaned WebParts

2011-10-04T17:25:00.000-07:00

While troubleshooting a completely different issue in one of our SharePoint 2010 test farms, I noticed an error reported in the "Health Report" in the central admin. The error "Missing server side dependencies" came up with an explanation saying "WebPart class [GUID] is referenced [x] times in the database [ContentDB], but is not installed on the current farm."

In my case this was a web part we used back before the site had been upgraded to sharepoint 2010. Amazingly, the web part was still working even though SharePoint 2010 was flagging it as an orphan. In this case that's fine, I wanted to remove the web part from all pages anyway.

...so how in gods name do I find what pages the WebPart is on?

Thanks to Phil's blog post "Diagnose MissingWebPart and MissingAssembly issues from the SharePoint Health Analyzer using PowerShell" I was able to figure this out. Here is my rehash of Phil's info, connecting to SQL server directly rather than using powershell.

Query Against the WebPart GUID
Snag the WebPart GUID from the error message, then use SQL server management studio to connect to your SharePoint 2010 content database. Run the following query, replacing the GUID with your WebPart GUID.

SELECT Id, SiteId, DirName, LeafName, WebId, ListId, tp_ZoneID, tp_DisplayName

from AllDocs

inner joinAllWebParts on AllDocs.Id= AllWebParts.tp_PageUrlID

whereAllWebParts.tp_WebPartTypeID = '8a860dca-4061-d270-a67b-f6bde7fc3e0a';

The DirName gives you the path to the page you want ("/MySubSite/Pages") and the LeafName gives you the name of the page ("default.aspx").

Map The SiteId GUID To URL
If you have more than one site collection in your farm, the SiteID tells you what collection you are dealing with. You'll want to map the SiteID to a website URL, so bust out the sharepoint powershell prompt on your central admin server and run the following command:

PS c:\> Get-SPSite

it should output a list that looks something like this:

Url                                                         ID

---                                                         --
http://MySite1                                         707f838a-56ab-475b-9a52-fdf05a8f3e7a
http://MySite2                                         11c04ca8-ad58-4147-8344-8132e5788090

The ID in the above table maps back to the SiteId from the SQL query - and there you have your URL to SiteId mapping! Hooray!

Now you can put it all together to get the full URL of the pages you need to fix.

Removing Bad WebParts
Sadly, this can be tricky part. When you start deleting the bad WebPart, keep these things in mind:

DELETE the web part from the page. Do not use the "Remove" option, which only hides the web part instance rather than delete it.
You can quickly view all WebParts assigned to a page and delete them by appending "contents=1" to the url. Ex: "http://mysite/Pages/default.aspx?contents=1" (Another great tip from Phil, thanks!)

If you have the publishing feature enabled, you could have instances of the web part linked to old versions of your page. Be sure to delete the version history of the page if necessary ... and if you do that, you will need to also empty the SharePoint recycle bin ... and -then- empty the "Second Stage" SharePoint recycle bin if you have it enabled in the central admin, which only the Farm Admin can do from the site collection root settings page. To get to the Second Stage recycle bin, you can login to the front end site as a farm admin account and hit the url "/_layouts/AdminRecycleBin.aspx". Then pick the option "Deleted from end user Recycle Bin".

Verify All Instances Are Gone
Once you've deleted the web part from all pages, do a sanity check - run the SQL query again to confirm no records are returned. You can also run the "Health Check" again by hand to confirm SharePoint agrees that the web part is gone. Fire up the SharePoint PowerShell command prompt and run the command "Test-SPContentDatabase" shown below, but of course swap out the content database and web application names for your own.

PS c:\> Test-SPContentDatabase -name MYSP2010-Content -WebApplication http://MyApp

Notes On Migrating Oracle 11g Tables to SQL Server 2008R2

2011-09-30T08:50:00.000-07:00

Assuming you have 3 boxes total:

- Oracle Severs 11g with tables you want to copy

- A SQL Server 2008 server that allows remoteconnections via windows auth (password auth should work fine too)

- A workstation with Oracle 11g client and SQLServer 2008 client

Install Clients on Workstation

- Install Oracle 11 client

o Make sure you install the same version of theOracle client on your workstation that is being used on the Oracle DBserver.

o Select the Administrator option when you performthe client install. ( The oracle .NET provider and the OLE DB provider arerequired )

o After the client install, make sure you updatethe TNSNAMES file. The import process will use this to make the connection tothe oracle server.

- Install the SQL Server 2008 management toolsincluding the SSIS option (the SSIS part might not be required)

Run the SQL Server 2008R2 import/export tool

My New Blogger Account...

2011-09-15T13:08:00.000-07:00

So ok, I created this blog using one of my old google accounts a long time ago. Later I registered a google apps account and bought a domain name through google apps for it - bryansgeekspeak.com. Google setup all the DNS for me through godaddy just fine ... but I could never get emails working properly. The MX records I tried to register with godaddy would save in their crappy domain management tool, but their DNS records would never reflect those changes. I eventually gave up and decided to transfer the domain to dotster, which hosts all of my other domains.

What a royal pain. To transfer the domain, I had to jump through a bunch of hoops for google/godaddy to release the domain ... the service to make your contact info private was killing my domain transfer...documentation about that would have been nice google! *shakes a fist!*

But now it's all done, and I am back to managing DNS on my own server using bind on my linux box. Finally, I have valid MX records for mail through bryansgeekspeak.com :-D

So now I have moved ownership of this blog to my new google apps account "Bryan" for the domain "bryansgeekspeak.com" and the profile info on the right column here on the blog reflects that.

With all that garbage out of the way, I'm ready to get back to posting new stuff I've been working on - Like tricks to updating custom SharePoint 2007 masterpage/page layouts so they work with SharePoint 2010's visual upgrade...and maybe something about Google's Fusion Tables too!

A Quick Look At Cloud Storage Security

2011-06-30T10:32:00.000-07:00

I was curious about free cloud services and their security practices with transfering/storing data, so I did some research and figured I'd post it. All of these services provide a free account with 1 gig or more of online storage.

Dropox (https://www.dropbox.com)

From the official Dropbox blog.

http://blog.dropbox.com/?p=735
The files you store on Dropbox’s servers are encrypted using an industry standard, AES-256. We manage the encryption keys on our users’ behalf. This encryption protects against a variety of security threats, particularly when your data is at rest.

All data that goes from your client to the dropbox server is over SSL, which handles encryption during transit (the same stuff that encrypts secure websites). Files are encrypted on the server using a single key that dropbox employees could use to unencrypt your data. So you need to have a little bit of faith here, that the employees are trustworthy enough to handle your data responsibly.

Ubuntu One (https://one.ubuntu.com)

From the Ubuntu One website.

https://wiki.ubuntu.com/UbuntuOne/FAQ/AreMyFilesStoredOnTheServerEncrypted
Q: Are my files stored on the server encrypted?
A: No. If you are interested in having your files encrypted both on your local machine and in the cloud, you could use Ubuntu's Encrypted Home Directory feature, and only synchronize your $HOME/.Private folder...

Wuala (http://www.wuala.com)

From the Wuala website.

http://www.wuala.com/en/learn/technology
All your files get encrypted on your computer, so that no one - including the employees at Wuala and LaCie - can access your private files. Your password never leaves your computer.

This sounds like the most secure cloud storage around. Encrypting files on your client before they are transferred to the server (I believe over an encrypted SSL connection) and then stored in fragments across multiple servers.

Google Docs (http://docs.google.com)

An explanation from a user (not a google employee) taken from google docs support forum.

http://www.google.com/support/forum/p/Google%20Docs/thread?tid=34631f3d6c627514&hl=en
Google Apps does not encrypt data at-rest. Instead they are using data sharding with obscured file names/identities. Basically any one email or document has parts of it on different servers and they are all pulled together when you request the file. If I was to compromise their server and copy all the data, it is rather unlikely to find a complete file and even less likely to find the file in you are particularly looking for. Security though obscurity.

What if I Need Better Control Over Security?
Even with a service like Ubuntu One where files are not encrypted on the cloud, you can choose to do the file encryption yourself before hand. This way you are the only one who has a key to unlock the data and you can still access the data from the cloud. Take a look at something like TrueCrypt for creating encrypted volumes.

Unfortunately TrueCrypt is not available for Droid or iOS yet, so you wont be able to open your encrypted volumes from those devices even if your cloud service supports those platforms.

Web Project Deployment - Deploying Oracle DB Changes Including CLOBs!

2011-06-17T12:42:00.000-07:00

So in a recent project, I ended up scripting out database changes (data only, no schema changes) from our dev environment to all our other environments for one of our projects. This is a incremental update, so I want to append my new data to any existing data for the tables I'm working with.

Without CLOB column types, it would be pretty easy - I could create some pretty standard SQL scripts to run in SQLPlus or SQL Developer on the target database. But this time we had change to CLOB data - some of the data being inserted being over 10,000 characters long. It took me a while to figure out how to make a deployment package, and this is what I came up with:

A set of standard SQL scripts for tables that do not have CLOB columns
Oracle sqlldr scripts for CLOB columns

Both those things I usually do via the "unload" command in SQL Developer, so I started with the format SQL developer uses and modified the results to get my final deployment package.

Solving the CLOB problem!
Keep in mind, this may not be the best way to do it (I hope it isnt, cause its painful!) but this was the best reliable way I could find to do the task without writing a custom application to do it.

So in this case I am stuck using sqlldr to load CLOB data, which unfortunately requires that we have the oracle client installed AND configured to use TNS Names properly. I find that to be a pain, but whatever - it is doable. Just be sure to use the same oracle client version as your target server oracle version (I ran into the problem of trying to use oracle 10 client tools to import data into oracle 11 - errors everywhere!).

Creating The Deployment Package!
Once I had all the Oracle client tools installed and configured properly, I ran through a process that looked something like the following to build a deployment package.

Create SQL scripts for non CLOB tables (used SQL Developer to unload query results). Keep in mind to order the script to run in a way that will work with constraints enabled (I chose not to disable constraints during deployment)
Edit SQL scripts generated in last step. Add "COMMIT;" then "EXIT;" commands at the end of the files in prep for running from the command line.
Create oracle Loader scripts for CLOB tables (again used SQL Developer to unload query results). Again, keep in mind the order in which the loader scripts need to be run along with the SQL scripts created earlier.
Edit Loader control files to APPEND (rather than truncate, so in this case I don't loose existing data in my destination) and fixed table names to be proper case. Each Loader results set went into its own directory.
Placed all SQL scripts in a deploy directory, added loader directories as sub directories.
Finally, I ended up creating a .bat script to run -all- the deployment scripts, including the SQL scripts. In my case, I need to edit the .bat file to set TNS Names info for whatever environment I want to deploy to.

My final database deployment package for this release looks something like this:

From here, I can then edit one .bat file to change my destination database settings, and I only need to execute that file to perform the DB deployment.

Going through this process is a lot of extra up front work but it should result in a much more simplified, repeatable and reliable deployment process.

Exporting Oracle Table Data Using SQL Developer and SQLLDR

2011-06-08T17:12:00.000-07:00

Following up on my last post about trying to export table data from oracle as SQL insert commands, I've found a way to "Unload" query results in a format that works with large CLOB column data - the "Loader" format.

Unfortunately, you cant import loader format data using SQL Developer ... just another odd quirk of Oracle that drives me nutty. To import the loader results, you have to use the command line tool "sqlldr", which should be installed as part of the oracle client.

So lets take a look at exporting the Category table data again, this time using the Loader method.

Unloading Results into Loader Format

1) Launch SQL Developer and open a new worksheet connected to the database you want to use as the source.

2) Build a SQL statement including all the columns you want to export and execute it.

3) Right click on the results pane and choose "Unload" from the right click menu to launch the "Unload Wizard".

4) On the first page of the Unload Wizard, set "Format:" to "loader" and change the "Table Name:" value to match the name of the table you are exporting. The table name must be in ALL CAPS. Keep the "Save As" option set to "Single File", then change the path to point to a new empty folder on your system (this process may create more than one file). In this case, I named the file "category.ldr". Click "Next >", Then click "Finish".

5) Browse to the path you set in step 4 and you should see the loader control file (the filename you set in step 4) and possibly a bunch of data files. Open the control file with your favorite text editor and modify as needed.

By default the Unload to loader format process creates a control file with the TRUNCATE keyword. This tells the loader to DELETE all rows before adding the loader data. If you want to keep existing data in the table, use the APPEND keyword instead.

See the SQLLDR documentation on Loading Data into Nonempty Tables for more info.

6) Open a command prompt and change the working directory to be the path you set in step #4, so you are in the same directory as the loader files.

7) Assuming you have the oracle client installed on your box, run the sqlldr command line utility to import the data back into oracle.

NOTE: Unlike SQL Developer, the sqlldr command uses your TNS Names file to make the database connection.

The sqlldr command format is:

sqlldr <username>/<password>@<TNSName> control=<ControlFile>

Loader Results

After loading the data back in via the sqlldr command, we can see all the data made it in tact, including the huge CLOB data column (14000 characters long!). So, even though its a little bit of a pain to go outside SQL developer for the import process, this appears to be the better approach to exporting and importing table data compared to the "insert" option in the Unload Wizard.

Exporting Oracle Table Data Using SQL Developer - SQL Insert Results

2011-06-08T15:51:00.000-07:00

I've been trying to figure out the best way to export data from a query result as INSERT statements using the SQL Developer gui (I'm on version 3.0.03). Here's what I've got so far:

Unloading Results To Insert Statements
1) Launch SQL Developer and open a new worksheet connected to the database you want to use as the source.

2) Build a SQL statement including all the columns you want to export and execute it.

NOTE: In most cases you can use "SELECT * FROM MyTable" but if you need to do any special character escaping you would do that here in the select statement. For example, the Unloading process escapes single quotes for varchar2 columns, but not CLOB columns. If you want to unload a CLOB (with less than 4000 characters) and use it in an insert statement, you need to escape the single quotes with something like "REPLACE(MyClobCol,'''','''''') AS MyClobCol". Clobs over 4000 characters need to be handled using bind params ... something for a later discussion!

3) Right click on the results pane and choose "Unload" from the right click menu to launch the "Unload Wizard". (and yes, I agree, unload is a horrible name for an export tool!)

4) On the first page of the Unload Wizard, set "Format:" to "Insert" and change the "Table Name:" value to match the name of the table you are exporting. The table name must be in ALL CAPS (I'll explain why when we look at the Unload results). Change the "Save As" value to "Worksheet" and click "Next >", Then click "Finish".

5)The Unload results appear in a new worksheet for you to review.

Review of Unload Results
If you set the table name in the Unload Wizard, you'll see something similar to the following, which is what you want.

We can test out our results by clearing out our test table and running this script to re-create our records.

So in most cases this is all you need to do to move select rows of data from a given table on one database server to another database server (with the same schema on the destination database server, etc).

Unloading Gotcha - Using Mixed Case Table Name in Unload Wizard
If you unintentionally typed the table name in lower or mixed case in the Unload Wizard, you'll probably end up with insert statements that will throw errors.

Check this out, the unload process double quotes the table names in the results - which means now you are using case sensitive table name matching. By default, oracle table names are stored in all uppercase format, so you'll throw a "table or view does not exist" error. Its just another one of those strange Oracle quirks that had me going crazy for a while.

Unloading Gotcha - CLOB data larger than 4000 characters

Yeah. In this case, straight SQL wont work for you. The only way I know to move clob data using SQL is via a bind param, which means you need to manually fix the Unloading results to do this. Its a pain. I don't like it. When you run into this problem, you should probably start looking at alternatives to using the Unload Wizard ...

... try the "Loader" format as described in my next post Exporting Oracle Table Data Using SQL Developer and SQLLDR.

Web Application Deployment - Deploying Database Changes

2011-06-08T12:53:00.000-07:00

this article is another work in progress. Comments and feedback are always welcome.

This question keeps coming up at work over and over again. So here we go - deploying database changes 101. Buckle down and do the upfront work, look sexy and awesome when it comes time for deployment >_>

Synopsis
Here it is, this whole post summed up into one key idea:

Always create SQL scripts (insert/update/alter commands) to implement any and all database changes! Add those SQL scripts to your deployment package!

Curious as to what that means? Not sure if you know how to create SQL scripts for deployment? Looking for some tips and tricks on how to do a better job of that? OK! Lets dive in!

Example SQL Script for INITIAL Deployment of a Web Application

Here is an example of a single table "Categories" and its data. Keep in mind that this script is designed to be used only for the initial web application deployment.

NOTE: I'm following the Oracle DB Design Style Guide I wrote up a while back as a guide for my SQL scripts here.

--- Create the Category table
CREATE TABLE Category (
Id INT NOT NULL,
Name VARCHAR2(200) NULL,
Description VARCHAR2(4000) NULL
);

--- Primary Key Constraint
CREATE INDEX CategoryIdIdx ON Category(Id);
ALTER TABLE Category ADD CONSTRAINT CategoryIdPk PRIMARY KEY (Id);

COMMENT ON TABLE Category IS 'A list of Categories for use in a TODO list.';
COMMENT ON COLUMN Category.Id IS 'Primary Key';
COMMENT ON COLUMN Category.Name IS 'A common name for this category.';
COMMENT ON COLUMN Category.Description IS 'A longer description of this category.';

--- Create the oracle sequence for the primary key
CREATE SEQUENCE CategoryIdSeq INCREMENT BY 1 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;

--- Create the oracle trigger for populating the primary key
CREATE OR REPLACE TRIGGER SetCategoryIdPk
BEFORE INSERT ON Category REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN
IF :new.Id IS NULL THEN
SELECT CategoryIdSeq.Nextval INTO :new.Id FROM dual;
END IF;
END;
/

--- insert initial categories
INSERT INTO Category (Name, Description)VALUES('Groceries','My grocery list items');
INSERT INTO Category (Name, Description)VALUES('Work','My work related items');
COMMIT;

So what does this deployment script do?
1. Creates a table for us to store categories
2. Creates a primary key column
3. Sets comments on the table and the columns (Comments?! Madness!!)
4. Creates a sequence to drive the primary key value (oracle specific goo)
5. Creates a trigger to populate the primary key value based on the sequence
6. inserts 2 new categories: Groceries and Work.

Basically, it does literally everything that needs to be done to get the Category table setup in the database for my initial web application release to work.

So now, every time we deploy our web application to a new environment, we can run this SQL script to guarantee that a category table is created exactly how we expect it to be, along with its initial data.

Cant I just reverse engineer the database when I'm done my project?
Yes! ... Maybe! >_> just be careful that you don't reverse engineer throw-away testing data. I've seen that happen a lot just because people don't like spending the up front time building SQL scripts.

Example SQL Script for Deployment of a Web Application Update

After deploying our initial version of the web application, we start working on the next version which requires updates to the database. New requirements state that the categories now need to be sorted in an arbitrary order -and- we have additional categories being added to this release. Awesome! Lets script out the updates to our category table so that again we will have a reliable, repeatable process for deploying our changes.

--- Give our table a SortOrder column
ALTER TABLE Category ADD SortOrder INT NULL;
COMMENT ON COLUMN Category.SortOrder IS 'The default order in which categories should be displayed to the user.';

--- Update existing data with sort order values
UPDATE Category SET SortOrder = 1 WHERE Name = 'Work';
UPDATE Category SET SortOrder = 2 WHERE Name = 'Groceries';

--- Insert new data, include sort order values
INSERT INTO Category (Name, Description,SortOrder)VALUES('Errands','Errand that I need to do',3);
COMMIT;

Cool? So this SQL script, to be used for the next deployment of this application, is completely different from the initial deployment SQL Script - We only care about things that changed from the initial deployment script. This new SQL script should be bundled with the deployment package for this version of the web application.

Just to review, These are the changes we just made in english:

1. Gave the Category table a new SortOrder column
2. Updated our existing records in the Category table with a SortOrder value
3. Inserted a new category, including its SortOrder value

And the point of all this is...?

Hopefully it's obvious to you at this point - It's a good practice to Use SQL scripts to deploy ALL database changes for your application. The reward for your up front effort is a repeatable and reliable deployment script. An added bonus - you even can hand off your SQL scripts to a database admin to deploy remotely! (its a good practice to do that anyway!)

K2 BlackPearl 4.5 Server Event Code Snips

2011-05-19T12:45:00.000-07:00

OK - I'm still new to K2, and slowly figuring out how to work with K2 in C#. This post will be my little scrapbook of helpful code chunks for later use.

Get count of a specific user's worklist items for a given process - within a K2 Server Code event.
Use a new workflow client connection to the server to do all the work.

// create a new workflow client connection
using (Connection c = new Connection())
{

    // Using ConnectionSetup as connection string
    ConnectionSetup cs = new ConnectionSetup();
    // From a server code event in a K2 process we can use K2 environment variables for connection strings
    cs.ConnectionString = K2.StringTable["Workflow Server"].ToString();
    // Open server connection using ConnectionSetup
    c.Open(cs);

    // Impersonate Bryan so we can get at his worklist >_>
    c.ImpersonateUser(@"BryansDomain\Bryan");

    // Build worklist filter criteria
    // We only want to return work items that match a given process
    WorklistCriteria wc = new WorklistCriteria();
    wc.Platform = "ASP";
    wc.AddFilterField(SourceCode.Workflow.Client.WCField.ProcessName, WCCompare.Equal, "BryansTestProcess");

    // dump resulting count of Bryans worklist items for the BryanTestProcess to a K2 Data Field
    int itemCount = c.OpenWorklist(wc).TotalCount;
    K2.ProcessInstance.DataFields["BryansWorklistCount"].Value = itemCount;
}

Get distinct count of process instances for a given process pending a specific activity - within a K2 Server Code event.
Use a K2 SmartObject to do all the work.

// Use SmartObjectClientServer for accessing SmartObjects
SmartObjectClientServer sos = new SmartObjectClientServer();

// Prep the connection
sos.CreateConnection();

// From within a K2 process Server Code event, use K2 environment variables for connection strings
sos.Connection.Open(K2.StringTable["SmartObject Server"]);

// Get an "Activity Instance Destination" SmartObject (an OotB K2 SmartObject)
SmartObject so = sos.GetSmartObject("Activity_Instance_Destination");

// Tell the SmartObject to return a list of results
so.MethodToExecute = "List";

// Set filter for results - return active items pending "Bryans First Activity" for "BryansTestProcess"
so.Properties["ActivityName"].Value = "Bryans First Activity";
so.Properties["ProcessName"].Value = "BryansTestProcess";
so.Properties["Status"].Value = "Active";

// Execute - return the results as a DataTable
DataTable dt = sos.ExecuteListDataTable(so);

// If you use groups for destinations by using advanced mode to split groups to users...
// you can get duplicate Process Instance IDs - one for every user in the group
// to get a distinct list of process instances, use a datatable view to return distinct results
DataTable DistinctDT = dt.DefaultView.ToTable(true, "Process Instance ID");

// copy the resulting count to a K2 process data field
K2.ProcessInstance.DataFields["WorklistCount"].Value = DistinctDT.Rows.Count;

// and do cleanup
sos.Connection.Close()

Dear Microsoft ASP.NET Team: Thank you for Razor!

2011-05-05T10:00:00.000-07:00

Since day one of using traditional ASP.NET to work with web applications, I've hated it >_> The ASP page life cycle just feels like a huge pain to me. The methodology for page processing that they invented is so wacky compared to every other web development platform I've ever used. I cried and gnashed my teeth...and just lived with it for a while.

Then, from out of nowhere, they released the MVC toolkit for ASP.NET - and I loved it. At least compared to the traditional ASP.NET page life cycle. It still seemed a bit rough when I looked at MVC2 a while back, and I never really found a project to test it out on. I ended up working more on workflow projects in Lombardi Teamworks until recently, so my research into MVC went on the back burner for while.

Now that I'm about to dive into a new ASP.NET project, I've been looking at MVC3 and the new Razor syntax - wow! Its like they actually realized that just about everything in traditional ASP.NET page development is garbage and they decided to build MVC3 and Razor to sit over top of the garbage framework and actually act like a real web development toolkit! Yay! (I know, I know, I'm being too harsh, but thats how I truly feel about it! xD )

Whatever the reasons were for making the move, I thank you Microsoft ASP.NET development team. You've made me very happy by giving me a straight forward development option. Now its time to explore the guts of how it works, and figure out how to hook into the datamodel from razor :D

For some awesome intro and tutorials for programming in Razor with ASP.NET 4:
http://www.asp.net/web-pages

IIS 6.0 Default Document Not Working After .NET Framework 4.0 Install/Update

2011-04-20T10:51:00.000-07:00

We have seen this problem happen on a windows 2003R2 server running IIS 6.0 with SiteMinder enabled (an ISAPI filter on a web site). In our last round of server patching, after the server was rebooted, the default documents for all our sites no longer worked. All requests that would return the default document (http://mysite/test/) would instead return a 404 error. In addition to the 404 we noticed the url showing in the location bar of the browser would contain "/eurl.axd/GUID" somewhere in the string. We could still go directly to a page in the browser (http://mysite/test/default.aspx) and the page would load properly.

Google found us an article that identifies the problem in detail and explains how to apply a fix for it:
http://blogs.msdn.com/b/tmarq/archive/2010/06/18/how-to-disable-the-asp-net-v4-0-extensionless-url-feature-on-iis-6-0.aspx

Just add a DWORD registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ASP.NET\4.0.30319.0\EnableExtensionlessUrls" with a value of "0" then restart IIS to fix the issue.

Mobile Web Application Frameworks

2011-04-15T11:34:00.000-07:00

I started looking into ways to build mobile web apps for some of my work applications and found a ton of mobile web app frameworks. Most of them seem to be HTML5 with AJAX frameworks, which works great for me as a web app dev. The last thing I want to do is manage the whole compile and submit to device vendor for review lifecycle *shudder*

jQuery Mobile (for iOS, android, windows mobile, etc)
jQTouch (for iOS)
PhoneGap(Complied for specific device - not a web app tookit)

VS2010 deployments using TFS2010 Build Server

2011-03-04T15:24:00.000-08:00

Placeholder for TFS2010 Build notes

My TFS2010 Build Server Config

Windows Server 2008R2 x64
Installed Application Server role, including .NET 3.5.1 and .NET 4
Installed TFS2010 Controller service and 1 Build Agent Service
Configured TFS Controller service to run under custom domain account, no special privileges assigned in advance ([domain]\TFSBuildSVC)
Installed VisualStudio 2010 Ultimate (with C#, Web Developer and SharePoint tools)
Put Oracle.DataAccess.dll (2.112.1.0) into GAC to support oracle connections (used in our web projects)
Installed SharePoint 2010 resources for building SharePoint 2010 projects in TFS2010 (http://msdn.microsoft.com/en-us/library/ff622991.aspx). I used the powershell script provided by microsoft to copy over/setup the TFS server (http://blogs.msdn.com/b/vssharepointtoolsblog/archive/2010/04/14/building-visual-studio-sharepoint-projects-using-team-foundation-build.aspx)
Installed ASP.NET MVC 3 RTM

Enable web.config Transforms for TFS 2010 Builds

Found this solution on Kevin Daly's Blog. Unfortunately it involves editing the project/solution files to make it work.

Make a master list of build targets for the projects in your solution. Assuming all build targets will be used in at least one web.config transform, you'll need to keep track of them for use later in the TFS build definition.
In your TFS build definition, go to the Process section and add the build configurations from step 1 to the "Items to build->Configurations to Build" section. Be sure to define the configurations exactly as you have then in your solution.
In the solution explorer, unload the project you want to add the transform to, then right click the project name and edit the project file. Add the following XML chunk just before the last </Project> tag:
<Target Name="AfterBuild" Condition="$(IsAutoBuild)=='True'">
<ItemGroup>
<DeleteAfterBuild Include="$(WebProjectOutputDir)\Web.*.config" />
</ItemGroup>
<TransformXml Source="Web.config" Transform="$(ProjectConfigTransformFileName)" Destination="$(WebProjectOutputDir)\Web.config"/>
<Delete Files="@(DeleteAfterBuild)" />
</Target>
Edit the TFS build definition again. In the "process" section, go to "Advanced->MSBuild Arguments" and add the following parameter
/p:IsAutoBuild="True"

That should do the trick. Run the build and if it succeeds, open the drop folder and browse into the "_PublishedWebsites" folder. Find your project and check the web.config to confirm it produced the right results.

Create Web Deploy Package with TFS

To create a Web Deploy package for your project in the drop folder, you need to throw a few more MSBuild parameters into the TFS build definition.

/P:CreatePackageOnPublish=true /P:DeployOnBuild=true

These switches just create the package on the filesystem. Also, your project "Package/Publish Web" settings should be configured in VS2010 (usually the defaults are fine).

Build Server Errors

... The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\WebApplications\Microsoft.WebApplication.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.
This error means that the build box is missing some files required to perform the project MSBuild. In this case, I was trying to perform a web.config xml transform for a VisualStudio 2010 web application project. I was able to fix the issue by installing VS2010 on the build box. I'm guessing its a safe bet to install your typical workstation development tools on the build box to make sure that all your build requirements are met.

Resources

Web deployment switches for TFS2010 builds:
http://weblogs.asp.net/jdanforth/archive/2010/04/24/package-and-publish-web-sites-with-tfs-2010-build-server.aspx
How to build SharePoint Projects with TFS Team Build
TFS Web Deployment Packages (and other info about continuous integration)
More TFS2010 MSBuild deployment switches: Team Build + Web Deployment + web Deploy + VS2010 = Goodness

SharePoint 2010 on Windows Server 2008 R2 with Kerberos Troubleshooting

2011-02-21T11:07:00.000-08:00

re-posted again with additional resource links at the botttom.

Wow. Going through some massive headaches getting kerberos setup in SharePoint 2010 on Windows Server 2008 R2. I was following my old guide from SharePoint 2007 (http://www.bryansgeekspeak.com/2010/01/enabling-kerberos-in-windows-domain-for.html) until I realized all the IIS 7.5 settings are completely different...

The first thing google turned up for me was an blog post that explains a lot of Windows 2008R2 specific gotchas for SharePoint 2010. It goes into detail about how to turn off kernel mode authentication and how to enable IIS delegation using the application pool account credentials.

http://www.harbar.net/archive/2010/03/31/sharepoint-2010-and-kerberos.aspx

With those changes and some SETSPN commands I was able to get successful kerberos logon events to the SharePoint 2010 web front end. A good start. On to the double hop delegation from a SharePoint 2010 web part to Teamworks 6...

When I hit the Teamworks webpart in SharePoint 2010, I got the usual authentication error that tells me kerberos is not working. The Teamworks server is already working in a SharePoint 2007 farm though, so I know the Teamworks piece is good - but I can see with wireshark that only NTLM2 requests are being sent from the SharePoint 2010 WFE to the jboss server - no kerberos traffic at all.

Dec 22, 2010 Update! Limited Success!
In one of my isolated SharePoint 2010 dev farms (an 8 server farm, isolated in its own domain), I was able to get kerberos working with Teamworks after working around the kinit/kerberos encryption issue (see the kinit link in the resources section). I have not had any success in larger, more complex domains. I'm now exploring the MS whitepaper on implementing kerberos (see link in resources section) hoping a solution will leap out at me *crosses fingers!*

LINKS TO KERBEROS RESOURCES

How to enable Kerberos event logging
http://www.bryansgeekspeak.com/2010/12/kinit-fails-to-generate-keytab.html - Due to the Windows 2008R2 PDCs no longer supporting older DES encryption types used by Teamworks/jboss, kinit fails to generate keytab file for the Teamworks service account.
http://www.bryansgeekspeak.com/2010/12/debugging-windows-server-2008r2-event.html for if you find the error in the event logs "Event ID 6037 - The target name used is not valid" on the SharePoint 2010 WFE (possible SPN error)
Microsoft's Configuring Kerberos authentication for SharePoint 2010 Products (white paper)
K2 [blackpearl] HF2.01 Distributed Installations (and also SP1) - Even though its specific to K2, its a good reference for farm configuration with kerberos/spns

Windows 2008R2 MSDTC Errors

2011-02-14T13:42:00.000-08:00

So, having moved away from using IBM/Lombardi Teamworks as a 3rd party workflow platform, our client decided to go with K2 blackpearl, mostly because of the close Microsoft .NET/SharePoint integration. As with any new 3rd party software toolkit, we ran into a number of strange deployment issues. Here is one of the big ones that had us stumped for a few weeks - MSDTC generating errors when creating a K2 "SmartObject" instance on the K2 server in a distributed farm environment (all Windows 2008R2 servers).

The MSDTC transaction manager was unable to pull the transaction from the source transaction manager due to communication problems. Possible causes are: a firewall is present and it doesn't have an exception for the MSDTC process, the two machines cannot find each other by their NetBIOS names, or the support for network transactions is not enabled for one of the two transaction managers.

As it turns out, K2 SmartObjects rely on the MSDTC service to do their data abstraction magic. I had no idea. As a matter of fact, I had never even heard of MSDTC until this error cropped up. It seems MSDTC is actually the Distributed Transaction Coordinator (which shows up in the services applet in windows 2008R2), which I understand to be a SQL server specific service for performing reliable "application transactions" across multiple windows servers to a SQL server instance.

Anyway, we tried changing DTC security settings on all the servers first (Component Services-Computers-My Computer-Distributed Transaction Coordinator-Local DTC-Properties-Security tab) but no luck there. After a lot of trial and error, the support guys at K2 had me completely uninstall the MSDTC service, remove all its registry entries (eek!), reboot, then add all the services back in ... and after doing that on all the servers in my farm, it solved the problem.

Why did this work? Well, one thing that leaps to mind is the error we were seeing using the DTCPing tool from microsoft:

WARNING:the CID values for both test machines are the same while this problem won't stop DTCping test, MSDTC will fail for this

The recommended approach to fix this is to uninstall the MSDTC service on one of the servers involved in the DTCPing test. Then I realized the MSDTC service is installed by default in Windows Server 2008R2 ... and all of our servers are VMWare instances cloned from a single Windows Server 2008R2 image. So my best guess is if you are using cloned VM images of Windows Server 2008R2, you could also run into this problem.

Anyway, to "reinstall" MSDTC, I went though the following process which worked on my farm (though, this may be overkill):

On each server, do the following:

From Server Manager, Click "Roles", scroll down to the "Application Server" section, click "Remove Role Services" and uncheck/remove "Distributed Transactions"
From powershell in "Run as administrator" mode, issue the MSDTC uninstall command "msdtc -uninstall" (http://msdn.microsoft.com/en-us/library/aa561924%28v=bts.70%29.aspx)
Using regedt32.exe, delete all the MSDTC registry entries (My Computer\HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC)
Restart the server.
Reinstall the "Distributed Transactions" role service from step #1. In addition, I also made sure to install the MSMQ role/feature, and I enabled TCP Port sharing, all the "Windows Process Activation Service Support" features (I dont know if all of that is necessary)
Run the MSDTC install command from a powershell window as administrator "msdtc -install"
Reset all the DTS security settings (Component Services-Computers-My Computer-Distributed Transaction Coordinator-Local DTC-Properties-Security tab), and also update the "Distributed Transaction Coordinator" service to start automatically on boot
Reboot

Troubleshooting GSSException: Channel binding mismatch (Mechanism level: ChannelBinding not provided!)

2011-01-25T08:57:00.000-08:00

Ran into another interesting error in one of my SharePoint 2007 farms with kerberos enabled. Our Teamworks web part, which uses kerberos to authenticate to the Teamworks (jboss) workflow server suddenly stopped working after a series of patches were applied to all the servers in the farm. Nothing obvious was showing up in the windows event logs, but I did find this error in the Teamworks logs

java.lang.RuntimeException: Exception verifying Kerberos token
at org.jboss.security.auth.AdvancedWebCallbackHandler$1.run(AdvancedWebCallbackHandler.java:236)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:337)
at org.jboss.security.auth.CommonKerberosUtils.doAsKerberosSubject(CommonKerberosUtils.java:64)
at org.jboss.security.auth.AdvancedWebCallbackHandler.handleNegotiateCallback(AdvancedWebCallbackHandler.java:211)
at org.jboss.security.auth.AdvancedWebCallbackHandler.handleCallbacks(AdvancedWebCallbackHandler.java:151)
at org.jboss.security.auth.AdvancedWebCallbackHandler.handle(AdvancedWebCallbackHandler.java:107)
at org.jboss.security.auth.HttpServletRequestResponseValve.doNegotiate(HttpServletRequestResponseValve.java:382)
at org.jboss.security.auth.HttpServletRequestResponseValve.authenticate(HttpServletRequestResponseValve.java:263)
at org.jboss.security.auth.HttpServletRequestResponseValve.authenticate(HttpServletRequestResponseValve.java:237)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
at org.jboss.security.auth.HttpServletRequestResponseValve.invoke(HttpServletRequestResponseValve.java:128)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:74)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:392)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:595)
Caused by: GSSException: Channel binding mismatch (Mechanism level: ChannelBinding not provided!)
at sun.security.jgss.krb5.InitialToken$OverloadedChecksum.(InitialToken.java:225)
at sun.security.jgss.krb5.InitSecContextToken.(InitSecContextToken.java:102)
at sun.security.jgss.krb5.Krb5Context.acceptSecContext(Krb5Context.java:715)
at sun.security.jgss.GSSContextImpl.acceptSecContext(GSSContextImpl.java:300)
at sun.security.jgss.GSSContextImpl.acceptSecContext(GSSContextImpl.java:246)
at org.jboss.security.auth.AdvancedWebCallbackHandler$1.run(AdvancedWebCallbackHandler.java:226)
... 22 more

Google lead me to this forum thread:

http://forums.sdn.sap.com/thread.jspa?threadID=1504390&start=15&tstart=0

Apparently one of the server patches we applied changed the SharePoint server's "Extended Protection for Authentication" behavior (http://support.microsoft.com/?scid=kb;en-us;968389&x=10&y=18), which was causing kerberos to fail for our Teamworks Web Part.

We ended up adding the registry key "SuppressExtendedProtection" as a DWORD type with a value of "2" to the node "HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\LSA" on the SharePoint web front end where the Web Part was running. After a reboot, it fixed our problem.

Verifying SSL Certificate Request Details

2011-01-21T09:52:00.000-08:00

Certificate Details

Now that I manage a lot of SSL certificates and generate requests/renewals, I've gotten used to using OpenSSL for double checking all my cert request info. Right now, I use Cygwin (a linux emulator for windows) to run OpenSSL on my windows box. From there I can open a Cygwin prompt and run OpenSSL commands directly on a cert request

$openssl req -in mycertrequest.txt -noout -text

SSL-TLS Testing
OWASP Testing Guide - "Testing for SSL-TLS"

VS2010 + VIM

2011-01-12T12:53:00.000-08:00

Finally! Free VIM emulation for VS2010! (its a VS2010 extension, you can install it using the extensions manager)

http://blogs.msdn.com/b/timng/archive/2010/05/07/vs2010-vim.aspx

I just started using it ... and I totally squealed with glee just now. You get a colon command prompt and everything! I dont have to bother with launching an external vim instance anymore! Yay!

Playing Around With HTML5 Video

2011-01-06T15:45:00.000-08:00

An example of what you can do with embedding video in a webpage using just a few lines of HTML5 markup, no javascript: http://www.moopoint.com/Video.html

Again, you'll need an HTML5 compliant browser to actually see the video embedded in the page.

In related news - Google will drop H.264 support from Chrome, herd the masses towards WebM and Theora O.o;

Playing Around With HTML5 Client Side Forms Validation

2011-01-04T09:06:00.000-08:00

Whoops! The link below is broken - I'm trimming down the number of domains I own and moopoint.com was one I let go. I'll bring the code up under the bryansgeekspeak.com domain at some point in the future!

So far, I love the FireFox4 beta. I had completely switched to Chrome on my laptop at home (I have to use IE at work since its our client's standard browser -.-) but now I'm slowly switching back to FireFox - mostly for the neat HTML5 features they're implementing.

One of the cool new things for web developers that they just added to the FireFox beta is built in Forms Validation. In your input tag markup, you can now add the attributes "required" and "pattern=''" and FireFox will now automagically require the field to be not empty and/or the value must pass the regex supplied in the pattern attribute of the input tag. Sweet! That means less custom javascript code in your web applications and cleaner HTML markup :-D

I've put up a simple example of the validation in action here - http://www.moopoint.com/FormsValidation.html.

Windows 2008R2 (IIS 7.5) ASP.NET 4.0 Configuration For Impersonation

2011-01-03T09:20:00.000-08:00

Admittedly I am still learning a lot about .NET - and recently I was stumped on how to configure an IIS website properly to allow ASP.NET code to impersonate the user hitting the website. Impersonation doesnt seem to be a very common thing to do - often we hard code account credentials to access things like database repositories - but this case, I was using a workflow product called K2 which allows you to use their C# API to access their workflow engine so that the user hitting your application can access and manipulate their tasks on the K2 workflow server. To allow my web application to allow you to manipulate workflow tasks on that remote K2 server, I need impersonation.

In my first attempt at building a custom web form for workflow tasks I used the default settings in IIS 7.5 for a new website. In my web application, my C# calls to connect to the workflow server threw errors saying the IIS application pool account did not have rights to access the task I was trying to run. That makes sense, I needed to use the logged in user account rather than the application pool. It took a bit of guess-n-test, but I found a configuration that allows this kind of impersonation to happen - and thankfully in this case kerberos is not required (single hop impersonation works with NTLM).

The tricky part was enabling support for the .NET 4.0 framework on Windows Server 2008R2 - I still cant believe microsoft disables their latest and greatest ASP.NET version on a windows 2008 product -.- Anyway, here's what I did:

Install server Features required for IIS integrated authentication and impersonation. From the Server Manager tool, confirm the following:

Confirm the "Web Server (IIS)" Role is installed.
Confirm the "Windows Authentication" feature is installed, along with any other features required for your web application (ASP.NET, ISAPI Extension/Filters, etc).

Enable .NET 4.0 framework (this took me forever to figure out!)

Launch the "Internet Information Services (IIS) Manager" tool.
Click on the root IIS server in the "Connections" pane, then open the "ISAPI and CGI Restrictions" tool from the Features View pane.
Set both versions of "ASP.NET v4.0.xxxxx" to "Allowed"

Disable Anonymous Authentication for the site. Enable Windows authentication and Impersonation.

Launch the "Internet Information Services (IIS) Manager" tool.
Click on your website in the "Connections" pane, then open the "Authentication" tool from the Features View pane.
Disable "Anonymous Authentication". Enable both "Windows Authentication" and ASP.NET Impersonation.
Click on "Windows Authentication", then click "Advanced Settings" and confirm that "Extended Protection" is turned off and that "Enable Kernel-mode authentication" is not checked.

Modify the web application web.config to support impersonation. Here is an example:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.webServer>
        <security>
            <authentication>
                <anonymousAuthentication enabled="false" />
                <windowsAuthentication enabled="true" />
            </authentication>
        </security>
    </system.webServer>
    <system.web>
        <identity impersonate="true" />
    </system.web>
</configuration>

Kinit Fails to Generate Keytab - Exception: krb_error 14 - in Windows 2008R2 Domain

2010-12-20T14:48:00.001-08:00

I ran into this issue specifically when setting up a kerberos ticket for the Teamworks 6.2.2 (jboss) service account running on windows 2003R2 in a windows 2008R2 domain. The reason I need kerberos here is so that a Web Part on a SharePoint 2010 server can pass through authentication from the user (the infamous doublehop delegation issue).

Windows 2008R2 domain controllers no longer support the older DES encryption used by Teamworks 6.x for Kerberos ticket encryption (DES-CBC-CRC). Bamf. Roadblock.

Here is the error I get when I try to run the kinit command to setup the Kerberos ticket for the Teamworks service account:

C:\Program Files (x86)\Support Tools>C:\Teamworks\java_x64\bin\kinit -k -t C:\t
amworks\sso.keytab HTTP/TWServer@MYDOMAIN.LOCAL
Exception: krb_error 14 KDC has no support for encryption type (14) KDC has no
upport for encryption type
KrbException: KDC has no support for encryption type (14)
at sun.security.krb5.KrbAsRep.(KrbAsRep.java:66)
at sun.security.krb5.KrbAsReq.getReply(KrbAsReq.java:486)
at sun.security.krb5.internal.tools.Kinit.sendASRequest(Kinit.java:308)
at sun.security.krb5.internal.tools.Kinit.(Kinit.java:239)
at sun.security.krb5.internal.tools.Kinit.main(Kinit.java:106)
Caused by: KrbException: Identifier doesn't match expected value (906)
at sun.security.krb5.internal.KDCRep.init(KDCRep.java:133)
at sun.security.krb5.internal.ASRep.init(ASRep.java:58)
at sun.security.krb5.internal.ASRep.(ASRep.java:53)
at sun.security.krb5.KrbAsRep.(KrbAsRep.java:50)
... 4 more

krb_error 14 points to an issue with the encryption types. Googling led me to the fact that Windows 2008R2 no longer supports DES encryption types used by Teamworks/jboss.

From there I tried enabling a workaround posted by Microsoft but it doesn’t seem to have any effect, even after rebooting all servers in the domain:

http://support.microsoft.com/kb/977321

As a last ditch effort, I ended up adding a windows 2003R2 BDC to my domain and tried pointing kinit to the new BDC. It worked. When I ran the kinit command, it automatically picked up the BDC instead of the PDC and generated the ticket successfully. After that, everything came up fine - Kerberos authentication worked from IIS/SharePoint 2010 to Teamworks, giving me SSO from my teamworks inbox webpart.

Debugging Windows Server 2008R2 Event ID 6037 - "The target name used is not valid"

2010-12-20T12:12:00.000-08:00

I found this error in the event logs on my WFE after trying to configure kerberos for a SharePoint 2010 farm (in a small farm configuration, with specified service accounts for application pools)

Source: LsaSrv
Event ID: 6037
The program w3wp.exe, with the assigned process ID 1234, could not authenticate locally by using the target name HTTP/devsharepoint.mydomain.com. The target name used is not valid. A target name should refer to one of the local computer names, for example, the DNS host name.

Try a different target name.

The curious thing is that the target name is a Kerberos SPN >_>

the URL listed in the error is registered in DNS correctly, and I can ping it from the WFE. I found a blog post that has a potential fix via a registry hack (http://www.dotnetpulse.com/?p=70). I'm listing it again here for reference - I implemented the fix and so far (for at least an hour) no more of these errors are showing in the event log *keeps fingers crossed!*

Go to REGEDIT > HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
Right click MSV1_0 > New > Multi-String Value
Type 'BackConnectionHostNames'
Right click & select ‘Modify’
Enter the Hostname of the site: WEBSITENAME (and on a new line enter the FQDN, WEBSITENAME.domain.com as well)
Restart IIS

Rediscovering HTTP Headers

2010-12-17T12:57:00.000-08:00

I havent used raw HTTP Headers in so long, when I needed to troubleshoot them again it turned into a small research project. So here it is, where I can hopefully find it again when I draw a blank next time around.
---

What are HTTP Headers?

HTTP Headers are explained in more detail here - http://en.wikipedia.org/wiki/HTTP_headers

HTTP headers are plain text pieces of data (string name/value pairs) passed back and forth between web server and browser with each web request.

The browser typically sends request headers to the server which can include things like the browser version being used to make the request. Here is an example set of headers for a GET request:

GET / HTTP/1.1
Host: www.bryansgeekspeak.com
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.2.13) Gecko/20101203 Firefox/3.6.13 ( .NET CLR 3.5.30729; .NET4.0E)
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Language: en-us,en;q=0.5
Accept-Encoding: gzip,deflate
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 115
Connection: keep-alive
Cookie: __utmz=7506145.1291670597.279.23.utmcsr=blogger.com|utmccn=(referral)|utmcmd=referral|utmcct=/navbar.g; __utma=7506145.1224262850.1253640086.1292536031.1292536661.294; __utmb=7506145.3.10.1292536661; __utmc=7506145
If-Modified-Since: Tue, 07 Dec 2010 16:18:07 GMT
If-None-Match: "bb02d252-a672-46a3-a001-96c62017ec35"
Cache-Control: max-age=0

The server typically sends response headers which can include things like the web server software version. Here is an example of a 200 OK response header from the server back to the browser

HTTP/1.1 200 OK
Content-Type: text/html; charset=UTF-8
Expires: Thu, 16 Dec 2010 22:02:54 GMT
Date: Thu, 16 Dec 2010 22:02:54 GMT
Last-Modified: Tue, 07 Dec 2010 16:18:07 GMT
Etag: "bb02d252-a672-46a3-a001-96c62017ec35"
Content-Encoding: gzip
X-Content-Type-Options: nosniff
X-XSS-Protection: 1; mode=block
Content-Length: 15991
Server: GSE
Cache-Control: public, max-age=0, must-revalidate, proxy-revalidate
Age: 0

HTTP headers are sent before the content of the request is sent - the html part from web server to browser in a response, or form/url data in a request - and HTTP headers are not displayed/rendered by your browser.

HTTP Headers and Browser Cookies
Cookie data, when needed, is encrypted and transmitted via HTTP headers too - but HTTP headers themselves are not cookies.

more info on HTTP Cookies - http://en.wikipedia.org/wiki/HTTP_cookie

Custom HTTP Headers and IIS
HTTP Header data is just one big string formatted in a way that the browser/web server can parse and understand. ISAPI filters in IIS can be used to intercept all HTTP request traffic between the server and client, modifying the standard IIS server request/response header string as needed to provide additional custom header information from your own ISAPI filter to the webserver and client browser.

Tools For Viewing Raw HTTP Headers From Your Browser

http://www.blunck.info/iehttpheaders.html a tool for viewing HTTP headers in IE
http://livehttpheaders.mozdev.org/ a firefox add on for viewing HTTP headers

Teamworks LDAP Authentication to AD on Windows Server 2008 R2 Quirk?

2010-12-06T16:51:00.000-08:00

I just ran into the following error when pointing an existing known good Teamworks 6.2.2 instance from a Windows Server 2003R2 PDC to a Windows Server 2008R2 PDC for domain authentication:

LDAP: error code 49 - 80090308

did some googling and found out that sometimes windows AD requires you to use windows style bind credentials in the format "username@mydomain.com" rather than the traditional ldap DN of the user "CN=username,DC=mydomain,DC=com". Annoying -.-

.NET SQL Query Results to a DataTable Type

2010-11-04T12:39:00.000-07:00

So, all I want to do is execute an SQL statement against oracle and have it return a recordset that I can use in my .NET code, just like what I'm used to seeing in Coldfusion. What's the best way to do that?

I spent hours experimenting with DataAdapter and DataReader (The Oracle ODAC112012 version), and I was able to get useable results using several different techniques, all with their own pros and cons. DataAdaper is huge and provides an amazing amount of extra functionality for Insert/update/delete functionality that we really don't need in this case. DataReader on the other hand seems to be optimized for forward read access of sql select results, but only while actively connected to the database. I really want a combination of the two in this case – the speed/optimization of the DataReader with the Functionality of a DataTable (which to me, is very similar to a Coldfusion recordset), which is returned as part of a DataSet by a DataAdapter.

My favorite solution so far? A DataReader filling a newly initialized DataTable. Another requirement for me is the support of SQL Parameters, which automatically prevents SQL injection attacks and improves DB server performance with query plan caching (you get that for free just by using sql parameters for ALL your variables rather than relying on string parsing! Yay!). Anywho, here is a fairly generic method that allows executing a SQL statement along with its list of OracleParameters, returning only one DataTable variable with the results of the query:

public DataTable executeSqlDataTable(string sql, OracleParameter[] sqlParameters)
{
    // Get the connection string
    ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["OracleConnectionStringCouncil"];
    string constr = connectionStringSettings.ConnectionString;
    DataTable dt = new DataTable();

    // Create an oracle connection
    using (OracleConnection conn = new OracleConnection(constr))
    {
        // Create an oracle command object so we can pass parameters to it
        OracleCommand command = new OracleCommand(sql, conn);

        // Add parameters, if any
        for (int i = 0; i < sqlParameters.Length; i++)
        {
            command.Parameters.Add(sqlParameters[i]);
        }

        // execute the sql statement
        conn.Open();
        OracleDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
        // dump the results to a DataTable type
        dt.Load(dr);
    }
    return dt;
}

And here is an example method that executes a sql statement (with oracle SQL parameters) and returns one column value from the first returned row as a result:

public string getFullName(string ntUsername)
{
    string sql = "SELECT FULL_NAME FROM MyUserTable WHERE username = :ntUsername";

    // Set SQL parameters
    OracleParameter[] sqlParameters = { new OracleParameter("ntUsername",OracleDbType.NVarchar2) };
    sqlParameters[0].Value = ntUsername;

    // Execute the SQL, return a DataTable
    DataTable tt = executeSqlDataTable(sql, sqlParameters );

    return tt.Rows[0]["FULL_NAME"].ToString();
}

Migrate TFS 2008 data to TFS 2010 Project Collection

2010-10-15T16:56:00.000-07:00

Its fairly easy to do once you know the tricks. These instructions assume you have a running TFS 2008 instance with data to be migrated and a new TFS 2010 instance running with data. You want to merge the TFS 2008 data into TFS2010 as a new collection. Here goes!

1) Stop the TFS 2008 IIS application pool and web site on the old TFS server instance. This makes sure no one is actively changing data while we do the migration.

2) do a SQL server backup of all tfs* databases from the old TFS 2008 instance. In most cases this will be SQL server 2005 - the TFS 2010 upgrade commands will not work running against databases in SQL server 2005.

TfsActivityLogging
TfsBuild
TfsIntegration
TfsVersionControl
TfsWarehouse
TfsWorkItemTracking
TfsWorkItemTrackingAttachments

3) restore these databases to the SQL server 2008 instance used by the TFS 2010 instance.

4) From the TFS 2010 server, run a command similar to the following ( you can run TfsConfig.exe import /help for more details)

C:\Program Files\Microsoft Team Foundation Server 2010\Tools> .\TfsConfig.exe import /sqlinstance:MySQLServer2008Instance /collectionName:tfs2008_import /confirmed

Where "MySQLServer2008Instance" is the SQL server 2008 instance used by your TFS 2010 instance, and "tfs2008_import" is the name of the collection to create in TFS2010 for your imported data.

Once the import/upgrade is complete, all the old databases listed in step 2 are removed for you, leaving a database named tfs_tfs2008_import which TFS 2010 will use from now on to manage your collection in SQL server.

SharePoint 2007 Approval Workflow missing from SharePoint 2010?

2010-09-13T08:09:00.000-07:00

6/23/2011 update: Thanks to Pham Van Trung for pointing out a MS KB article that addresses this issue - http://support.microsoft.com/kb/2535850

In one of our projects, we were using the MOSS publishing feature on a SharePoint 2007 farm, with the standard "Approval" publishing workflow on all our page libraries. We recently did an upgrade on that farm to SharePoint 2010, and everything seemed to work the same - the existing sites still had the old approval workflow assigned to the Pages library, and they worked as expected.

When we tried to make a copy of one of our sites, using the Content and Structure view to copy the entire site structure, the workflow assigned to the pages library doesnt copy over properly, which is normal. But now when we try to assign the approval workflow to the pages library after the fact, we simply cant. The old Approval workflow isnt available as a workflow option to pick from, and I've found no way to bring it back so that its selectable in the SharePoint 2010 web interface. There are some blogs out there that mention activating a feature called "SharePoint 2007 workflows", which I tried, but that still did not fix the problem for me.

To work around the problem, one of our C# gurus built a console app that can make the old SharePoint 2007 workflow available in a SharePoint site.

SharePoint 2010 CQWP Modified Date

2010-08-30T15:41:00.000-07:00

Pulling out your hair trying to find the field name for the "Last Modified Date" SharePoint 2010 field for use in ItemStyle.xsl? Here it is, with an example of how to format the date using xslt functions available to ItemStyle.xsl:

Modified On: <xsl:value-of select="ddwrt:FormatDate(string(@_x007B_28cf69c5_x002D_fa48_x002D_462a_x002D_b5cd_x002D_27b6f9d2bd5f_x007D_) ,1033 ,5)" /><br /><br />

I found the field name by dumping the names of all properties available to my xsl template using the Debug code below in ItemStyle.xsl. Once you've updated ItemStyle.xsl, create a test page in your SharePoint portal, drop in the CQWP and pick the following Debug template from the list.

<xsl:template name="Debug" match="Row[@Style='Debug']" mode="itemstyle">
        <div>
            <xsl:for-each select="@*">
                P:<xsl:value-of select="name()" /><br/>
            </xsl:for-each>
        </div>
</xsl:template>

A Good ASP.NET MVC Tutorial

2010-08-13T09:00:00.000-07:00

I just started experimenting with ASP.NET MVC web applications and found this online tutorial to be a great place to start:

http://www.asp.net/mvc/tutorials/mvc-music-store-part-1

Found another good tutorial, this one using LINQ to SQL rather than the Data Entity Framework. Also, this example talks about a Data Repository pattern for accessing a database, which is probably the way I'll end up going.

http://www.asp.net/mvc/tutorials/creating-model-classes-with-linq-to-sql-cs

Custom Workflow Architecture: Table of Contents

2010-07-27T13:02:00.000-07:00

The Disclaimer: Please keep in mind, these articles are in the works and currently in a rough draft state. And as with anything on my blog, these are just my own thoughts, ideas and examples worked out into rough blog posts for my own reference. Hopefully others will find them useful too :) I don't provide any kind of warranty or support for the information given here, take it as is.

Custom Workflow Architecture: Examining Workflow Data

2010-07-27T12:45:00.000-07:00

!!ROUGH DRAFT!!

This post expands on a previous post: Custom Workflow Architecture: Workflow Steps and Transitions

From the previous post, we should already have the following database tables created and populated with the data shown:

Database Tables

Records

SELECT *
FROM Workflow;

ID	NAME	DESCRIPTION
1	Vacation Request Workflow	A workflow to allow employees to request vacation time.

SELECT *
FROM WorkflowStep;

ID	WORKFLOWID	NAME	DESCRIPTION
1	1	Workflow Instance Instantiated	This step marks the point where a workflow instance was created.
2	1	Request Creation/Submission	A vacation request is created and submitted for approval at this step.
3	1	Manager Review/Approval	The vacation request is reviewed and approved/rejected by a manager at this step.
4	1	Workflow Completed	The vacation request was finalized (Either approved or rejected).

SELECT *
FROM WorkflowTrans;

ID	WORKFLOWID	FROMSTEPID	TOSTEPID	NAME	DESCRIPTION
1	1	1	2	Instantiate Workflow	The initial transition that fires off for every new workflow.
2	1	2	3	Submit For Manager Approval	Submit the request for approval.
3	1	3	4	Approved by Manager	Approve Request.

Queries

Now lets start extracting some more meaningful data. The above queries give us raw dumps of all our tables, so we can at least see all our workflows, steps and transitions. But it does not give us the details our users and managers will want to see. So here are some common questions and solutions for querying data from our workflow data repository.

I need to see a list of all steps ordered by their logical flow within the workflow, starting with the step "Workflow Instance Instantiated" and ending with whatever the final workflow step happens to be, for this workflow instance. How can I do that?

The trick is to know what your starting transition is. Our transitions map the workflow from one step to another, so if we know what the first step is, we can follow our transitions to the end point. Writing the SQL to walk workflow transitions is trickier than it sounds. Its really a parent/child relationship. In my examples here, I use the built in oracle functions for traversing hierarchical relationships - START WITH and CONNECT BY.

In these examples, for simplicity, I'm hardcoding the starting step ID into my queries. In a more complete example, I would want to store the starting step ID somewhere in the database as part of the workflow scheme. For example, we could track the start step ID as part of the workflow record by creating a new column: Workflow.StartStepID.

SELECT w.Name AS WorkflowName,
ws.Name AS StepName,
wt.Name AS TransName,
LEVEL
FROM Workflow w
INNER JOIN WorkflowStep ws ON ws.WorkflowID = w.ID
INNER JOIN WorkflowTrans wt ON wt.FromStepID = ws.ID AND wt.WorkflowID = w.ID
WHERE w.ID = 1
START WITH wt.FromStepID = 1
CONNECT BY PRIOR wt.ToStepID = wt.FromStepID
ORDER BY LEVEL;

WORKFLOWNAME	STEPNAME	TRANSNAME	LEVEL
Vacation Request Workflow	Workflow Instance Instantiated	Instantiate Workflow	1
Vacation Request Workflow	Request Creation/Submission	Submit For Manager Approval	2
Vacation Request Workflow	Manager Review/Approval	Approved by Manager	3

I use the START BY statement to tell oracle what the starting point is for traversing the workflow I've designed. The table that represents the workflow hierarchy is really only the WorkflowTrans table, where the FromStepID and ToStepId represent our parent/child keys. So, we tell oracle to walk the transition hierarchy, and we use oracle's built in LEVEL keyword to sort our results correctly. This gives us a tabular representation of how our workflow is linked by transitions.

Custom Workflow Architecture: Workflow Steps and Transitions

2010-07-27T09:28:00.000-07:00

!!ROUGH DRAFT!!

This post expands on a previous post: Custom Workflow Architecture: The Routing Table

In my previous post we looked at a simple one step approval workflow and talked about how to store workflow activity to a routing table. In this post we'll look at one possible way to define a workflow's steps and transitions in a set of database tables, and how a workflow engine might utilize that information for use in a web application.

In designing our new system, we'll continue using the simple one step approval workflow at first, but I know that we will want to support more than just that one workflow in our new system. So first I'll define a table in the database to identify unique workflows available in our web application:

--- Create table Workflow
CREATE TABLE Workflow (
ID INT NOT NULL,
Name VARCHAR2(128) NULL,
Description VARCHAR2(2000) NULL
);
--- Set column ID to be Primary Key for table Workflow
ALTER TABLE Workflow ADD CONSTRAINT Workflow_PK1 PRIMARY KEY (ID);

Keeping things simple, this table tags each available workflow with a unique id (Workflow.ID). In addition to the unique id, we can also assign a human readable name and description to help us identify the workflow. Lets insert a new record that will define the one step workflow we defined in the previous post (we will recreate that workflow from scratch in this example):

INSERT INTO Workflow (
ID,
Name,
Description
)VALUES(
1,
'Vacation Request Workflow',
'A workflow to allow employees to request vacation time.'
);
COMMIT;

Now that we have a workflow record in the system, we can go about creating a list of workflow steps (sometimes called Workflow States) we want to display in our application. We'll create a new table to hold our steps and link them back to the workflow they belong to.

--- Create table WorkflowStep
CREATE TABLE WorkflowStep (
ID INT NOT NULL,
WorkflowID INT NOT NULL,
Name VARCHAR2(128) NULL,
Description VARCHAR2(2000) NULL
);
--- Set column ID to be Primary Key for table WorkflowStep
ALTER TABLE WorkflowStep ADD CONSTRAINT WorkflowStep_PK1 PRIMARY KEY (ID);
--- Set foreign key pointing back to the Workflow table
ALTER TABLE WorkflowStep ADD CONSTRAINT WorkflowStep_FK1 FOREIGN KEY (WorkflowID) REFERENCES Workflow(ID);

Now that we have a table to hold the steps, we need to define what steps exist in our workflow. Even though we are calling this a one step approval workflow, there are really at least 2 steps involved - When the employee is working on his request, thats the first step of the workflow. Then, when the manager works on the request, after it's been submitted for approval by the Employee, thats the second step.

In addition, I'm going to add two extra steps - a step to represent the start of the workflow and another to represent the end (as shown in the flowchart diagram). Start and end point steps are not steps that users get to see, or act on, but it allows our application to see workflow instances from start to finish.

So lets go ahead and define all 4 of those steps in our new table with a few insert statements:

INSERT INTO WorkflowStep (
ID,
WorkflowID,
Name,
Description
)VALUES(
1,
1,
'Workflow Instance Instantiated',
'This step marks the point where a workflow instance was created.'
);

INSERT INTO WorkflowStep (
ID,
WorkflowID,
Name,
Description
)VALUES(
2,
1,
'Request Creation/Submission',
'A vacation request is created and submitted for approval at this step.'
);

INSERT INTO WorkflowStep (
ID,
WorkflowID,
Name,
Description
)VALUES(
3,
1,
'Manager Review/Approval',
'The vacation request is reviewed and approved/rejected by a manager at this step.'
);

INSERT INTO WorkflowStep (
ID,
WorkflowID,
Name,
Description
)VALUES(
4,
1,
'Workflow Completed',
'The vacation request was finalized (Either approved or rejected).'
);

COMMIT;

Notice I havent defined any way to order these steps yet >_> We'll do that now using Transitions!

If we look at the example workflow flowchart, we can see that steps are represented as squares. The transitions are the lines with arrows that connect steps in the workflow. Transitions define how you could get from one step to another.

In our example workflow the transitions are pretty simple. We've got at least 3 transitions to get us started:

A Transition from "Instantiate Workflow Instance" to "Request Creation/Submission", which happens when a user creates a new Vacation request. We'll call this transition "Instantiate Workflow"
A Transition from "Request Creation/Submission" to "Manager Review/Approval", which fires off when the employee submits his vacation request for review by a manager. We'll call this transition "Submit For Manager Approval"
A final Transition from "Manager Review/Approval" to "Workflow Completed". We'll call this "Approved by Manager"

Lets slap together a table to store information about all the available transitions in the system:

--- Create table WorkflowTrans
CREATE TABLE WorkflowTrans (
ID INT NOT NULL,
WorkflowID INT NOT NULL,
FromStepID INT NOT NULL,
ToStepID INT NOT NULL,
Name VARCHAR2(128) NULL,
Description VARCHAR2(2000) NULL
);
--- Set column ID to be Primary Key for table WorkflowTrans
ALTER TABLE WorkflowTrans ADD CONSTRAINT WorkflowTrans_PK1 PRIMARY KEY (ID);
--- Set foreign key pointing back to the Workflow table
ALTER TABLE WorkflowTrans ADD CONSTRAINT WorkflowTrans_FK1 FOREIGN KEY (WorkflowID) REFERENCES Workflow(ID);
--- Set foreign key pointing back to the starting WorkflowStep record for this trans
ALTER TABLE WorkflowTrans ADD CONSTRAINT WorkflowTrans_FK2 FOREIGN KEY (FromStepID) REFERENCES WorkflowStep(ID);
--- Set foreign key pointing back to the ending WorkflowStep record for this trans
ALTER TABLE WorkflowTrans ADD CONSTRAINT WorkflowTrans_FK3 FOREIGN KEY (ToStepID) REFERENCES WorkflowStep(ID);

Since a transition links one step to another, we need to link our transition to both the staring step and ending step. Working with this table relationship, we can setup transitions to every possible step and back if we wanted to...for now, we'll stick to the 3 transitions we've already defined above. Here are the insert statements for them:

INSERT INTO WorkflowTrans (
ID,
WorkflowID,
FromStepID,
ToStepID,
Name,
Description
)VALUES(
1,
1,
1,
2,
'Instantiate Workflow',
'The initial transition that fires off for every new workflow.'
);

INSERT INTO WorkflowTrans (
ID,
WorkflowID,
FromStepID,
ToStepID,
Name,
Description
)VALUES(
2,
1,
2,
3,
'Submit For Manager Approval',
'Submit the request for approval.'
);

INSERT INTO WorkflowTrans (
ID,
WorkflowID,
FromStepID,
ToStepID,
Name,
Description
)VALUES(
3,
1,
3,
4,
'Approved by Manager',
'Approve Request.'
);

COMMIT;

That's enough to get us started. With all the above SQL run, you should have a table layout that looks something like this in your DB:

In the next article in the series, Custom Workflow Architecture: Examining Workflow Data , I'll take a closer look at the workflow we've defined here to see how we might generate some meaningful SQL queries to generate lists of steps and transitions that will be the basis of our workflow application.

Custom Workflow Architecture: The Routing Table

2010-07-16T16:07:00.000-07:00

!!ROUGH DRAFT!!

note to self...maybe instead of calling this the "Routing Table" it should be something like "Workflow Activity Logging". I'm actually describing how to log user actions that route an item from one step to another in the workflow instance. Hmm...

Introduction
Looking back on just about every web application I’ve ever built, they all have one thing in common - they are all workflow based applications. Every system I’ve designed has the concept of an item to be acted on - be it a web form, a file attachment, or some other set of business data - that requires set of actions to happen in order to "finalize" the item.

Just think of any workflow as a simple flowchart - a start point with one or more steps drawn as boxes, each box connected to another by one or more lines with arrows showing direction (transitions) until you finally reach an end point. In any workflow, there is always a start point, an end point and at least 1 or more steps all connected by transitions.

A Simple Workflow Example
Lets take a look at an example workflow and examine a simple method for tracking workflow activity.

Consider a single approval step workflow. There are only two steps in this workflow and a handful of transitions that can take place. Imagine we have a web form that controls the workflow - the web form allows employees at our company generate vacation requests, and allows managers to view and approve them. A flowchart diagram of our simple workflow might look like this:

Now lets think about how workflow activity will be recorded in a persistent database (in this example, we'll assume an Oracle 10g database). Lets not worry about persisting the business data the moment (data about the vacation request and the user input via web forms), we'll just focus on the workflow and how we will store the routing history of an item - the transitions that took place, when they were performed and by whom.

If we want to be able to look back and see what happened to a specific item processed by our workflow, at a minimum we need to record every action/transition that takes place in the workflow, staring with when the item was created. We'll start with a single table to record all the transitions that take place in our workflow. Keep in mind that the table is oversimplified on purpose, so we dont have to waste time on reference tables and the like.

CREATE TABLE WorkflowRouting (
ID INT NOT NULL,
ItemName VARCHAR2(128) NULL,
NTUsername VARCHAR2(128) NULL,
StepName VARCHAR(128) NULL,
ActionTaken NOT NULL,
CreateDate TIMESTAMP DEFAULT SYSDATE
);

Now lets talk about a simple user case. Bob Smith, an employee at our company, decides to create a vacation request using our workflow application. His manger is Jane Doe, so she will need to approve his request, as per the specifications we've already collected.

Lets assume Bob will use a web form to create his request in our system. We give Bob a link to the web form in order to start the request. We wont get into the details of the code, but lets imagine the first thing we do in the web form is ask bob what kind of request he wants to make - we present him a drop-down list of possible request types to pick from and a Submit button to create the request. Bob picks "Vacation Request" and submits the web form. When Bob clicks "Submit" prepossessing will take place before bob actually enters in any business data about his request. Later, we will use more pre and post processing for each “step” in the workflow in order to track workflow activity. Below is an example of the kind of insert we might expect to see when Bob creates his request (again, oversimplified for ease of reading).

--- Assume that the ID column is populated by a trigger/sequence
INSERT INTO WorkflowRouting (
ItemName,
NTUsername,
StepName,
ActionTaken,
CreateDate
)VALUES(
‘Bob Smith vacation request’,
‘bsmith’,
‘Initial Data Entry and Submission’,
‘Created’,
SYSDATE
);
COMMIT;

So, the first routing record for Bob's request now exists in our database, so the system knows about it even though Bob still hasnt completed entering in his business data or sent the item for approval. Having that initial route record about when the item was created will become very important later when we want to build reports for the system.

Next, Bob finalizes his vacation request and submits it for approval. He submits the request by clicking a button in the web form that he’s been using to enter his vacation request data. The web form commits the business data to the database (which we wont worry about) and then does post processing for that step, committing another record to our WorkflowRouting table as shown in the insert statement below.

Note that the StepName is the same as our first route record - This is because we are still on the same step where bob is working on his request, but now we are recording that the action taken was “Submitted for Approval” rather than “Created”, and this record gets a new timestamp. With these two database records for the item “Bob Smith vacation request”, we can now see when the item was created and when it was approved, which allows us to determine how long it took Bob to submit the action for approval.

Now now that bob has submitted the item for approval, our workflow engine (more code we wont get into yet) can use the existing routing records to see that the vacation requester, Bob, completed his step and that the item should now be pending Jane’s approval, since she is his manager. Just assume that somehow the workflow engine knows the item is pending Jane’s approval based on what we've recorded in the routing table so far.

Note: Our routing table only records information about things that have already taken place, so you wont see who the action is pending approval by in this table. That information should be controlled by the workflow engine, which determines how one step flows to the next in the workflow.

Now Jane can start working on the item. She sees that an item is waiting for her approval, so she logs in and clicks on a link to access a web form where she can work on the item. As soon as Jane opens the web form to work on the item, we -could- record that action to the route table. Lets not worry about that yet, just know we could do that if we wanted to, just as we did for when Bob created the item.

Jane completes her review of Bob’s vacation request and she decides to approve it. She clicks a button in the web form, “Approve”, and the web form then writes another record to the database as shown in the insert statement below.

--- Assume that the ID column is populated by a trigger/sequence
INSERT INTO WorkflowRouting (
ItemName,
NTUsername,
StepName,
ActionTaken,
CreateDate
)VALUES(
‘Bob Smith vacation request’,
‘jdoe’,
‘Manager Review’,
‘Approved’,
SYSDATE
);
COMMIT;

We are still working on Bob’s vacation request, as you can see by the ItemName value which is the same as our previous two records. Jane is now performing the action though, so we use her Active Directory username “jdoe” to identify her as the actor in this record. The StepName value also changed because the action is now pending the manager’s approval. Finally, we record that the action that she took was “Approved” - this way we can tell the difference between a manager’s approval and the requester’s item submission for approval.

At this point, we’re actually done with this example. We’ve recorded all the actions that took place in the system that we care about, and now we have enough information to report back what happened to Bob’s vacation request. We could then write some simple SQL queries to give us basic reports about whats happening in our system.

The next post in this series, Custom Workflow Architecture: Workflow Steps and Transitions, looks at a way to define the workflow completely in the database, including all of its steps and transitions, rather than using hardcoded strings.

Team Foundation Server 2010 - Install and Upgrade Info

2010-06-18T10:50:00.000-07:00

Starting point for install/config guide

Resources:

Refresh a Materialized View in Oracle

2010-05-10T12:55:00.001-07:00

To refresh an oracle materialized view, run the following command:

execute DBMS_SNAPSHOT.REFRESH( 'MV','C');

where "MV" is the view name

(this is a re-post - trying to get it to show up in google search >_<;)

SharePoint 2007 - Troubleshooting error "Unable to add selected web part(s)"

2010-03-17T09:46:00.000-07:00

Recently one of my users ran into an issue where even with "Full Access" permissions to a SharePoint site they were seeing the error message below when trying to add a content editor web part to a SharePoint page they had created from the web interface.

At first, I was thinking it was a permissions issue. Then my buddy Shawn pointed me to the url. I had had not been able to reproduce the issue via the HTTP url, but the user was having the problem while accessing the site via HTTPS. I gave it a try using an admin account via HTTPS and ended up seeing the same problem.

Adding an alternate access mapping for the HTTPS url fixed the issue. I went with an "internet" zone access mapping for the HTTPS url, but I dont think it matters what zone you use in this case. So when you decide to allow HTTPS, dont forget to add an alternate mapping for it in the SharePoint central admin!

SharePoint 2007 - Import Fails With Message "Cannot import site. The exported site is based on the template SPS#0 but the destination site is based on the template STS#0"

2010-02-18T14:19:00.000-08:00

Cannot import site. The exported site is based on the template SPS#0 but the destination site is based on the template STS#0. You can import sites only into sites that are based on same template as the exported site.

If you are using stsadm.exe to do an export/import and you run into an error similar to that shown above, take a look at the template you used to create the destination site collection. If you used something other than the "Blank Site" template, that's probably the problem. Recreate your Site Collection using the Blank Site template and give the import another go.

HTML5 Notes

2010-02-05T11:04:00.000-08:00

HTML5 References

http://www.smashingmagazine.com/2009/08/04/designing-a-html-5-layout-from-scratch/ - the blog post for HTML5 ideas
http://www.ibm.com/developerworks/library/x-html5/

Enabling Kerberos in a Windows Domain for SharePoint 2007 and Teamworks

2010-01-11T13:40:00.000-08:00

Notes for configuring Kerberos on a windows 2000/2003 domain for SharePoint 2007 farms.

For a list of gotchas in a SharePoint 2010/Windows server 2008 setup, check out this post: http://www.bryansgeekspeak.com/2010/11/sharepoint-2010-on-windows-server-2008.html

This post explains how I setup a sandbox network configured specifically for testing SharePoint 2007 and Teamworks 6.2.2 using Kerberos authentication on a windows domain.

I used a 5 server setup in this example, including a SharePoint farm (a web application server and a sql server), a teamworks server farm (a teamworks application server and an oracle server) and a primary domain controller. The goal, as shown in the diagram below, is allow domain users to auto-login to to the SharePoint portal. From the SharePoint portal we want to be able to drop the Teamworks for SharePoint web part into a SharePoint page, set it to use integrated authentication in order to pass along the user's SharePoint credentials along to the Teamworks server. The end result is a SSO auto login for the user. They should only need to login to their workstation and not see a login prompt in their browser for access to the portal or Teamworks.

To start, you'll need to get to a point where you have all the servers up and running as shown in the model. The server configuration is outlined below, before I began configuring Kerberos.

Server Software Configuration Prerequisite

KDCPDC

Microsoft Windows 2003 R2 Enterprise Edition
Configured as Primary Domain Controller

KDCSQL2008

Microsoft Windows 2003 R2 Enterprise Edition
MS SQL Server 2008 (2005 works just as well) patched to the latest version, configured for use in your sharepoint farm.

KDCMOSS

Microsoft Windows 2003 R2 Enterprise Edition
MOSS SharePoint 2007 patched with SP2, configured using custom user accounts. (note that I've heard kerberos is much easier to configure if you use the default service accounts rather than creating custom accounts)

KDCOracle

Microsoft Windows 2003 R2 Enterprise Edition
Oracle Server 10.2.0.4 configured for use with Teamworks 6.2.2

KDCTW

Microsoft Windows 2003 R2 Enterprise Edition
Teamworks 6.2.2 Enterprise Edition

With the above server configuration, we then need the names of the service accounts for Teamworks and the SharePoint web application pool where the teamworks web part will be used.

Account Name	Description
KRB\MOSSAppPool	SharePoint Web Application Pool Account
KRB\Teamworks	Teamworks Service Account

Finally, we need to know the fully qualified URLs for each of our web sites - the SharePoint URL and the Teamworks Portal url. In this case, I added DNS records on the PDC for these URLS:

URL	Description
www.krb.com	The sharepoint site (remember, only use the PDC as the DNS server!)
krbtw.krb.com	Teamworks Portal URL

With all the above information, we can now start configuring kerberos.

Part 1 - Service Principal Names (SPN)

With delegation enabled, we need to map SPNs for the service accounts, to show kerberos that we allow trust those accounts to perform trusted authentication. You need to have the windows 2003 server support tools installed in order to run this command.

c:>setspn -A HTTP/www.krb.com KRB\MossAppPool

c:>setspn -A HTTP/krbtw.krb.com@KRB.COM KRB\Teamworks

When you are done, you can use setspn to list spn mappings for machines and accounts on your domain, which should look similar to what is shown below.

Part 2 - AD Account Delegation

Enable Delegation rights in AD for the SharePoint computer account, the SharePoint application pool account and the Teamworks service account.

Computer Accounts

Note that these examples are for a domain set to a functional level of windows 2000. If your domain functional level is set to windows 2003, delegation works differently.

We only need to configure the SharePoint computer account. The Teamworks server uses a java server application, which will require a different method for enabling kerberos trust/delegation, so dont enable trust delegation for the Teamworks computer account.

With Domain Functional Level Set to Windows 2000

For the computer account KRBMOSS, check the option "Trust computer for delegation".

With Domain Functional Level Set to Windows 2003

For the computer account KRBMOSS, goto the "Delegation" tab, select the option "Trust this computer for delegation to the specified services only" and "User kerberos only". Then add the HTTP service, with a computer account of the sharepoint web site. To add the url of the sharepoint web site, you need to click "Add.." and select the SharePoint application pool user account, where we mapped an SPN for that account to the SharePoint site URL.

Enable Delegation for Service Accounts

Note that constrained delegation works differently. Changes need to be made under the "Delegation" tab in the account properties.

With Domain Functional Level Set to Windows 2000

Check the option "Account is trusted for delegation" for both service accounts. In addition, the Teamworks service account needs the option "Use DES encryption types for this account" checked in order to support Teamworks (I believe this is a java server kerberos requirement).

With Domain Functional Level Set to Windows 2003

Part 3 - Enable Kerberos in SharePoint

Now the easy part! Log into the SharePoint Central Administration site. Under "Application Management" goto the "Authentication Providers" page.

Select correct web application from teh "Web Application" drop down, then click the "Default" zone. Check "Negotiate (kerberos)" under the heading "IIS Authentication Settings" and click "Save".

You'll get a confirmation windows telling you that there are other steps required as domain administrator to enable kerberos. You can ignore that, we did that in the previous steps by setting the SPNs and configuring delegation. Be sure to execute an "iisreset" command at the command prompt after enabling negotiate.

c:\>iisreset /noforce

And that's all there is to it for the SharePoint side of things. You can confirm Kerberos is working by logging into the SharePoint site (http://www.krb.com). Then check the event viewer's security logs on the SharePoint server. You should see a 540 event for the user you hit the site as, with a message indicating kerberos authentication was used, similar to that shown below.

Part 4 - Enable Kerberos in Teamworks

Start by shutting down your Teamworks Process Server. It's also a good idea to delete all the log files before you start making changes. In this example, log files are in "c:\Teamworks\process-server\logs\".

Create Kerberos Config File for Teamworks

First, we need to create a kerberos config file on the windows server which Teamworks/Jboss can find, in order contact the KDC properly. The Teamworks SSO documentation explains that Teamworks/Jboss is already configured to look for a file called krb5.ini in the path "C:\winnt\krb5.ini". The content of that text file should look like this, for our example KRB domain:

[libdefaults]
default_realm = KRB.COM
default_tkt_enctypes = des-cbc-crc
default_tgs_enctypes = des-cbc-crc
ticket_lifetime = 600
[realms]
KRB.COM = {
kdc = KRBPDC
admin_server = KRBPDC
default_domain = krb.com
}
[domain_realm]
.domain.com = KRB.COM
[appdefaults]
autologin = true
forward = true
forwardable = true
encrypt = true

Once you've created the file "C:\winnt\krb5.ini" with the above text, you can continue to the next step.

Create Kerberos Token with KTPASS

As the domain administrator, login to the Teamworks server (KRBTW) and run the following command from the command prompt:

ktpass -out sso.keytab -princ HTTP/krbtw.krb.com@KRB.COM -pass MYPASSWORD -mapuser KRB\Teamworks -crypto DES-CBC-CRC -ptype KRB5_NT_PRINCIPAL

Be sure to replace "MYPASSWORD" with the password of your KRB\Teamworks account.

Note that the "-princ" value matches the SPN we created earlier for the Teamworks service account.

If the command is successful, the file "sso.keytab" will be created in your current directory. Copy that file to your Teamworks installation directory. In this example, I copied the file to "c:\Teamworks\sso.keytab".

Generate Kerberos Ticket for Teamworks Account

Login to the Teamworks server (KRBTW) as the Teamworks service account (KRB\Teamworks) and run the following command from a command prompt, again assuming that you installed Teamworks to "c:\Teamworks":

c:\Teamworks\java\bin\kinit.exe -k -t c:\Teamworks\sso.keytab HTTP/krbtw.krb.com@KRB.COM

If the kinit command is successful, it will create a kerberos ticket in the following path which will then be used for kerberos authentication by the Teamworks service from now on.

C:\Documents and Settings\Teamworks\krb5cc_sso

Turn On Kerberos in Teamworks

As described in the Teamworks SSO documentation, we now need to enable kerberos in Teamworks. In this example, we also enable KRB domain users and groups within Teamworks, so that we can manage Teamworks accounts through AD.

c:\Teamworks\process-server\conf\login-config.xml

Change the <application-policy name="other"> element to match the following. Be sure to change "MYPASSWORD" and "ENCRYPTEDPASSWORD" to values that work for your accounts. For information on how to encrypt passwords for Teamworks, refer to the Teamworks install documentation from Lombardi.

<application-policy name="other">
    <authentication>
        
        <login-module code="org.jboss.security.auth.NegotiateLoginModule" flag="optional">
            <module-option name="loadBalance">false</module-option>
            <module-option name="domainController">KRBPDC</module-option>
            <module-option name="defaultDomain">KRB</module-option>
            <module-option name="java.security.krb5.realm">KRB.COM</module-option>
            <module-option name="jcifs.http.enableNegotiate">true</module-option>
            <module-option name="jcifs.spnego.servicePrincipal">HTTP/krbtw.krb.com@KRB.COM</module-option>
            <module-option name="jcifs.spnego.servicePassword">ENCRYPTEDPASSWORD</module-option>
        </login-module>

        <login-module code="com.lombardisoftware.security.provider.authentication.jboss.TWInternalLoginModuleImpl" flag="optional">
            <module-option name="password-stacking">useFirstPass</module-option>
        </login-module>
        
           <login-module code="org.jboss.security.auth.spi.LdapExtLoginModule" flag="optional" >
               <module-option name="java.naming.provider.url">ldap://KRBPDC:389/</module-option>
               <module-option name="bindDN">cn=Teamworks,CN=Users,DC=KRB,DC=com</module-option>
               <module-option name="bindCredential">MYPASSWORD</module-option>
               <module-option name="baseCtxDN">cn=Users,DC=KRB,DC=com</module-option>
               <module-option name="baseFilter">(sAMAccountName={0})</module-option>
               <module-option name="roleFilter">(member={1})</module-option>
               <module-option name="rolesCtxDN">cn=Users,DC=KRB,DC=com</module-option>
               <module-option name="roleAttributeID">cn</module-option>
               <module-option name="roleAttributeIsDN">false</module-option>
               <module-option name="roleNameAttributeID">cn</module-option>
               <module-option name="password-stacking">useFirstPass</module-option>
               <module-option name="allowEmptyPasswords">false</module-option>
            
            
            <module-option name="twUserFilter">(objectClass=person)</module-option>
            
            <module-option name="twGroupFilter">(objectClass=group)</module-option>
            
            <module-option name="twUserNameAttribute">sAMAccountName</module-option>
            
            <module-option name="twUserPrimaryGroupAttribute">primaryGroupID</module-option>
            
            <module-option name="twGroupNameAttribute">cn</module-option>
            
            <module-option name="twGroupDNAttribute">distinguishedName</module-option>
            
            <module-option name="twGroupPrimaryGroupTokenAttribute">primaryGroupToken</module-option>
            
            <module-option name="twUserDescriptionAttribute">cn</module-option>
            
            <module-option name="twGroupDescriptionAttribute">description</module-option>
            
            <module-option name="twGroupMemberAttribute">member</module-option>
        </login-module>
        <login-module code="com.lombardisoftware.security.provider.authentication.jboss.TWAuthenticatedRoleLoginModuleImpl" flag="required">
            <module-option name="authenticatedRole">twuser</module-option>
        </login-module>
        <login-module code="org.jboss.security.ClientLoginModule" flag="required">
            <module-option name="password-stacking">useFirstPass</module-option>
            <module-option name="restore-login-identity">true</module-option>
        </login-module>
    </authentication>
</application-policy>

c:\Teamworks\process-server\conf\jboss-service.xml

Uncomment the AdvancedWebCallbackHandler as shown.

<attribute name="CallbackHandlerClassName">org.jboss.security.auth.AdvancedWebCallbackHandler</attribute>

c:\Teamworks\process-server\applications\portal.war\WEB-INF\context.xml

Modify to match the following code.

c:\Teamworks\process-server\applications\teamworks.ear\teamworks.war\WEB-INF\context.xml

Modify to match the following code.

c:\Teamworks\process-server\applications\webapi.war\WEB-INF\context.xml

Modify to match the following code.

Thats the last of the Teamworks configuration changes. Restart your process server and check the logs to confirm there are no errors. Next, you will want to login to the teamworks admin site (http:\\krbtw.krb.com\teamworks) as tw_admin to confirm AD groups are visible to Teamworks. From the Teamworks admin portal, go to the "Maintain Logical Roles" page. You should see your domain groups populating the "Available Roles" box as shown below.

Once you've confirmed groups are showing up properly, try logging in to the portal site from another computer as a domain user using IE. Be sure to add the url "krbtw.krb.com" to the browser's Local Intranet zone first, to confirm that you are logging in via SSO\Kerberos. You'll know it's working if you see the "Attempting auto-login" screen flash briefly (shown in the screenshot below) before dumping you into the Teamworks portal. If you get a windows password prompt, of if you get the Teamworks form password prompt, you'll know you've hit a problem.

Part 5 - Install Teamworks Web Part

For this example, we will assume the IIS directory path for the web application is located here "c:\Inetpub\wwwroot\wss\VirtualDirectories\80\"

Run Teamworks for SharePoint Installer

I ran the installer as the SharePoint farm administrator. I typically use the same account that was used to run the SharePoint installer and the SharePoint configuration wizard.

I haven't confirmed this, but there seem to be cases where the Teamworks Site Templates in SharePoint are not available, and I think it may have to do with the account used to run the installer.

The SharePoint for Teamworks installer is fairly self explanatory. You need to know the URL of your teamworks server (and the port used for web access, if it is not port 80). I always choose -not- to let the installer modify any configuration files because of several possible bugs with the installer. I feel its safer to manually perform these steps, as shown in this post.

Move Teamworks Web Part DLLs to Web Application Bin

On the SharePoint server (KRBMOSS), search for the Teamworks Web Part assemblies and copy them to the web application bin directory, so that your web application can actually use them. The version of Teamworks for SharePoint I am using installs the assemblies to c:\windows. Both files that need to be copied from

c:\windows\Lombardi.Server.WebParts.dll
c:\windows\Lombardi.Server.WebParts.Portal.dll

c:\Inetpub\wwwroot\wss\VirtualDirectories\80\bin\Lombardi.Server.WebParts.dll
c:\Inetpub\wwwroot\wss\VirtualDirectories\80\bin\Lombardi.Server.WebParts.Portal.dll

This step should not be necessary. This seems to be a bug with the version of the Teamworks for SharePoint installer that I'm using. One of these days I'll get around to asking Lombardi Teamworks support about it.

Modify Web Application web.config File

For the next few steps in this example, we are editing the following file for our web application.

c:\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config

Add Teamworks Web Part Assemblies to Safe Controls

In web.config, add the Teamworks assemblies as safe controls.

This may change as new versions of the Teamworks for Sharepoint assemblies are released. You may need to confirm the version number and public key token for yourself to get this part working. Check with Lombardi support if you get stuck on this.

I believe this is also a bug with the installer. I would think the installer should create these SafeControl entries for you in web.config since the installer does not tell you the version number or PublicKeyToken of the assemblies.

Set Trust Level

In web.config, you will need to set the trust level to something that will work for the Teamworks assemblies. For testing, I simply set the trust level to "full" as shown below.

Configure SharePoint Site to Allow Teamworks Web Parts

Login to the sharepoint site as an administrator, then go to the site collection root and add the Lombardi Teamworks Web Parts to the Web Part gallery.

From the site collection settings page, click "Web Parts" under "Galleries".

Click the "New" button in the Web Part Gallery list page.

Check the box next to each Lombardi.* Web Part, then click "Populate Gallery" to make them available in the site collection for use.

Confirm Integrated Authentication Works for Web Part

From a page of your site, edit the page and add the TeamworksSavedSearch Web Part to the page.

Configure the Web Part for Integrated Authentication Mode.

If kerberos is working properly, you should see results similar to the following when you click "Apply" or "Ok" after configuring the Web Part.

The End!

If you were successful, pat yourself on the back and go buy yourself a mocha! Honestly, many people have trouble configuring kerberos on small server farms about this size, and once you get into things like reporting services and load balancing, it gets much more complicated.

When planning and testing kerberos configurations, I suggest always starting with a small example like this one, using all VMs (I use VMWare ESXi 4.0 currently for all my VMs). Once you get a working sandbox network, make a copy of your VM's so you can restore to a known good configuration for kerberos. Then you can safely start your experimenting. Build your more complex scenarios from this known good working example, without fear of losing all that initial leg work.

Resources

A list of links to more kerberos resources can be found here:
http://www.bryansgeekspeak.com/2009/07/teamworks-621-moss-2007-kerberos-sso.html

SharePoint 2007 - The file xyz is checked out or locked for editing by someone else

2010-01-07T09:51:00.000-08:00

I ran into this annoying error today, when trying to check in a word 2007 document into a SharePoint document library with versioning enabled:

"The file xyz is checked out or locked for editing by someone else"

I was logged into the sharepoint site as the correct user, and I had opened the file in word 2007 and authenticated again as the same user, and I still get that message.

To fix the problem, I had to disable the web client service on my local machine as shown in the screenshot below. Once I did that, closed word 2007, then made my changes again and tried to do a checkin, everything worked as it should.

Another thing to note is that this site was in my trusted zone, not my local intranet zone, and the sharepoint server is on a different domain than the one my workstation is logged into. I'm not sure if those are factors at this point.

SSO Authentication Problems for Windows Clients Against Java Kerberos Servers

2009-12-30T12:32:00.000-08:00

Quote from an MS Support Case

Cause
+++++++
Hotfix 968389 makes XP machines to support Extended Protection for Integrated Authentication which consists of Service Binding information (SPN) and a Channel Binding Token (CBT). This is ON by default and results in authentication requests to be bound to both the Service Principal Names (SPN) of the server the client attempts to connect to and to the outer Transport Layer Security (TLS) channel over which the Integrated Windows Authentication (IWA) authentication takes place.

Resolution
+++++++++
If you conclude that you are affected by this issue and are required to turn off CBT, enable the following registry key to disable CBT on Windows XP client:Create a reg DWORD (SuppressExtendedProtection) with a value of 0x03 under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\LSANote: This feature enhances the protection and handling of credentials when authenticating network connections using Integrated Windows Authentication (IWA). Disabling CBT can result in security implications protected by this feature.

More Information
+++++++++++++

What is CBT?

CBT is a part of Extended Protection for Integrated Authentication which is a mechanism to bind an outer secure channel such as SSL to inner channel authentication protocols such as Kerberos-APREQ and HTTP header authentication. CBT is a property of the outer secure channel (such as TLS) used to tie (bind) it to a conversation over an inner, client-authenticated channel. Channel binding is accomplished by the client transferring the SPN and the CBT to the server in a tamperproof fashion. The server validates the channel binding information in accordance with its policy and rejects authentication attempts for which it does not believe itself to have been the intended target. This way, the two channels become cryptographically bound together. • If the client is updated to support the extended protection, applications should supply a channel binding and service binding information to all supported authentication protocols. Channel binding information can only be supplied when there is a channel (TLS) to bind to. Service binding information should always be supplied.

- Updated servers which are properly configured may verify the channel and service binding information when it is present in the client authentication token and reject the authentication attempt if the channel bindings do not match. Depending on the deployment scenario, servers may verify channel binding, service binding or both.
- Updated servers have the ability to accept or reject down-level client requests that do not contain the channel binding information based on policy.

Channel Binding Token values:
• 0x01 - This makes the client appear unpatched to remote servers except in cases where caller of SSPI on the client provides both a channel binding token and a target SPN. The security implication of setting this flag is this: it makes
clients that do not use channel binding correctly, and clients that do not go over SSL vulnerable to authentication relay, even to partially hardened servers.

• 0x02 - This makes the client set Kerberos channel binding value to zero even if calling application correctly supplies the value. If set without the previous 0x01 value, this value will cause the client to stop working against hardened servers with SSL.

• 0x03 - Combination of 0x01 and 0x02. It disables channel binding always for Kerberos (0x02) and suppresses both channel binding and service bindings for those NTLM callers that do not supply channel binding (0x01). So, 0x02 has no effect on NTLM.

Note: As per PG, Windows 7 adds support for an IETF security standard called channel binding. There is a known application compatibility issue between Windows 7 clients and Java Kerberos servers (such as Jetty). The setting 0x3 (discussed later in this article) on the client overcomes this application compatibility issue at the expense of suppressing channel binding. This setting should only be used on client computers that have to authenticate to Java erberos servers:

Example

1. Jetty - Jetty 6.1.7
2. SAP Portal (Java J2EE Web Server -
)

Symptoms
++++++++++

1. Windows clients that support Channel Binding Tokens fail to be authenticated by non-CBT capable Kerberos server such as Java Kerberos.

Sample customer experience:

Accessing Federated Web sites using PingIdentity Federated Services from Internet Explorer 8.0 on Windows 7 clients. The same operation works from IE 8.0 on Windows XP and Windows Vista computers. The error text on Windows 7 reads:

Title: Error - Single Sign-On
Message text: Unexpected Runtime Authn Adapter Integration Problem.
Please contact your system administrator for assistance regarding this error.
Adapter: CorpIWA

Teamworks 6.2: Calling Oracle Storec Procedures

2009-12-18T12:20:00.000-08:00

First, lets create a table with sample data to work with:

CREATE TABLE BryanTestTable (
ID INT NOT NULL,
Name VARCHAR2(128) NULL
);

INSERT INTO BryanTestTable VALUES(1, 'Bryan');
INSERT INTO BryanTestTable VALUES(2, 'Josh');
INSERT INTO BryanTestTable VALUES(3, 'April');
COMMIT;

>SELECT * FROM BryanTestTable;

ID                     NAME
-------------------------
1                      Bryan
2                      Josh
3                      April

Now lets create a stored procedure that simply returns all rows from our test table as a refcursor

CREATE OR REPLACE
PROCEDURE GetBryanTestRecord (p_recordset1 OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_recordset1 FOR
SELECT *
FROM BryanTestTable;

END GetBryanTestRecord;

and test it from oracle before we move on to teamworks.

>VARIABLE r1 REFCURSOR;
>execute GetBryanTestRecord(:r1);
>print r1;

ID                     NAME
-------------------------
1                      Bryan
2                      Josh
3                      April

Now that we have a working procedure, lets jump into Teamworks. Lets create a simple service to test calling a stored procedure using an OotB sql service provided by Teamworks. Start by creating a new service in teamworks.

Next, from the teamworks library pane, navigate to "Teamworks->System->SQL Connector->Services->Call" and drag the service "SQL Call Stored Procedure (SQLResult)" into your new service. Then drag a "Server Script" widget from the Palette into your new service and link them as shown below.

Now check the data mapping pane tab of the "SQL Call Stored Procedure" service to identify the variables we need (for you, they will be empty for now, and thats okay).

Now lets define our variables based on the above data mapping requirements. To simplify our variables, I'll use an sqlStatement variable type (found in "Teamworks->System->SQL Connector->Variable Types") to handle most of the input, a string to handle the datasource name and an sqlResult list type for the output as shown below.

Once the variables are defined, we can initialize them with values in the Server Script object as shown below. You will need to change the tw.local.ds value to match your datasource.

Finally, go back to the data mapping tab ofthe SQL call service and make sure that you map all your variables as shown.

...and we're done! Now all we need to do is run the service in debug mode, step through the service and confirm the debug output shows what we expect to see from the stored procedure. When you reach Item Type "Exit Point", if you didnt hit an oracle/teamworks error, you should see the following XML output for the sqlResult value.

Create Oracle User and Grant Permissions to User Tablespace

2009-11-24T17:45:00.000-08:00

Notes for setting up new users/tablespaces in oracle 10g on windows 2003 server.

Install Oracle 10g (no example/starter database, use simple install option). No services/databases are installed by default
Configure/Install new database

Run oracle database configuration wizard
Create a new general purpose Database (use all default options)

Configure/Install default listener

Run the Oracle Net Configuration Assistant
Add a new listener
Select all defaults

Create new tablespace in newly crated database

From the oracle home start menu folder, launch the database control (loads the configuration web site in a browser, add the site to the "trusted sites" list in IE).
Login as the SYS account using the password you set in the database configuration wizard. Connect as SYSDBA.
Click on the "Administration" tab
Click "Tablespaces" then the "Create" button.
Typically, you'll want to add a tablespace specifically for a new oracle use account. I tend to make the name of the tablespace match the oracle user I intend to use with it.
The database datafile name needs to end in .DBF. If you use a tablespace name of "Bryan", a database file name of "Bryan.DBF" would make sense.

Create new user, assign to newly created tablespace

Return to the database "Administration" screen and select "Users", then click "Create"
Set the name (again, matching the tablespace is a good idea) and set the default tablespace to what we created in the previous step. Set the temp tablespace to TEMP.

Grant new user permissions to user tablespace. Run the following command as SYS as the SYSDBA role after editing the user acount name. I find it best to use sqldeveloper to run SQL commands rather than SQLPlus, because you can connect directly to the database via the SID. That way you dont need to configure a tnsnames file for your database (if you use JDBC, you wont ever need to use it).

GRANT create procedure, create session,create table,create type,create view,create synonym,resource TO myuser;
Finally, to prove your new oracle user account works properly, use sqldeveloper again to connect to the database as your new oracle user account using role "Default". Run the following commands to make sure you have create and drop rights.

--- create a test table
CREATE TABLE test (
id INT
);

--- drop the test table
DROP TABLE test;