My quest to bring Best Practices to Identity Management especially with Microsoft FIM / ILM

Saturday, December 6, 2008

Live@edu Partner Airlift and SQL PASS, Flat Tires, and Thanksgiving

As for me why no posts since Nov 11th -- well, I have attended the Live@edu Partner Airlift in Redmond, SQL PASS, had a flat tire, and enjoyed Thanksgiving. In this post

I attended the Live@edu Partner Airlift in Redmond to see what's new under the sun for schools and universities. Exchange Labs is now available on a widespread basis (see fellow MVP Almero Steyn's blog posts on Live@edu and on Exchange Labs) ! Students and alumni can now have school domain based exchange hosted email accounts for life at no cost to their schools. While this program has offered hotmail accounts now you can have hosted exchange accounts. I had a great time at the Airlift, thanks to Michael Wegman, Richard Wakeman, Andy Hoag, Steve Winfield (not Dave Winfield, nor Steve Winwood) and Anna Kinney and everyone else for putting it on.

I was privileged enough to attend SQL PASS for the first time. This year was in Seattle. So that meant two straight weeks in the Puget Sound area. It was fun to return and visit, see old friends, see my old house (where we lived for 9 months), see some beautiful wet countryside, experience more of downtown Seattle, but I sure was glad to get back to the warmth of the Arizona Sun! I did sneak my wife up for the weekend in between events and we did some of the tourist events we didn't have the chance to do while living there. We ate dinner at the space needle, took a cruise in the bay, saw some glass blowing, rode the monorail and visited pikes place fish market (the famous one featured in Fish! as well as the other two lesser known fish markets).

200811151323_00361  I took this photo on the cruise.


I greatly enjoyed SQL PASS, making and renewing acquaintances with many of the SQL Server MVP's. Thanks for letting me hang out and participate in all of the SQL MVP stuff without feeling like too much of an outsider! Saw lots of great sessions. Unfortunately I had to exit early from Gail Shaw's Dirty Dozen presentation on the twelve things not to do in your SQL code, but it seemed like it was going quite well.

After returning from Seattle we discovered that our 1 yr old Honda Odyssey had a flat. Out came the jack. Ouch went the back! But at least it prompted me to look at my other car and realize that I needed two new tires (an ounce of prevention is worth a pound of cure)!

I would like to remember this Thanksgiving as relaxing, fun, filled with family and friends and this year I can ;)

Labels: , , , ,

Wednesday, October 22, 2008

SQL Server Agent should be running or install of ILM 2 Services fails

I posted the following to the Community Content Section of the ILM 2 Beta 3 Installation Guide

The SQL Agent Service account must be a sql sysadmin and the SQL Agent Service must be running or during install you may get "error -2147217900

Failed to execute sql string addtemporaleventsjobtoSQLServer" while trying to install ILM 2 Beta 3 Identity Management Platform Services. Apparently, the install routine needs to create a SQL Agent Job and with SQL 2005 the Agent must be running to create a job.

The job it creates is called ILM_TemporalEventsJob and according to its description it "Periodically identify workflows to be run on objects that have transitioned to or from temporal sets." It is scheduled to be run every day at 1 AM.

It has only one step of type T-SQL: EXEC dbo.TriggerTemporalEvents. So later on if you find that objects are not getting transitioned to and from temporal sets you might need to come and check this job's history, and ensure that the SQL Agent is running.


Labels: , ,

Wednesday, October 15, 2008

Installing a Multi-Instance SQL 2005 Cluster

Some of you may run into a problem when installing a multi-instance SQL Server Cluster, in particular when you install the second or third instance in your cluster.

Like this one:


Microsoft SQI Server 2005 Setup
SQL server Setup has determined that the Following account properties are not specified: 
‘SQLBROWSERACCOUNT’. The properties specify the startup account for the services that are installed. To proceed, refer to the template.ini and set the properties to valid account names. If you are specifying a windows user account, you must also specify the password for the account.

This may happen if you install the second instance (virtual server) on a node that is part of the first instance (virtual server). This occurs because the browser service is running on that node.  So SQL setup detects the existence of the browser service and does not prompt you for the credentials for all three services, only for SQL Server and SQL Agent leaving out the SQL Browser. You will then see the above error.

Normally during the install you have the ability to customize all three services:


But when installing the second instance in the cluster on a node that is part of the first virtual server (instance) all you get is this:


If you use the same account for all of the services you may not see this error. If you use the same accounts across instances you may not see this error.

That is one other thing that sets this multi-instance SQL cluster apart from others; we tried to follow best practices for security by using separate accounts for each service for each instance. See the two tables below showing the user accounts and global groups created in Active Directory (domain local groups would work too). All of these user and group objects should exist in the same domain as the computer accounts for the Nodes.

SQL Instance 1


Value (filled in by Client)

Service account for SQL Server Database Engine


Group for Service Account for SQL Server Database


Members of Group Above


Service account for SQL Server Agent


Group for SQL Server Agent


Members of Group Above


Service account for SQL Server Full Text Engine (FTE)


Group Service account for SQL Server Full Text Engine (FTE)


Members of Group Above




SQL Instance 2


Value (filled in by Client)

Service account for SQL Server Database Engine


Group for Service Account for SQL Server Database


Members of Group Above


Service account for SQL Server Agent


Group for SQL Server Agent


Members of Group Above


Service account for SQL Server Full Text Engine (FTE)


Group Service account for SQL Server Full Text Engine (FTE)


Members of Group Above



According to Microsoft CSS (or PSS or whatever you want to call the boys and girls on the other end of the 800 number) the SQL Server Product group is aware of this and has declared that this is an "Expected Program Behavior" (notice the absence of the words bug and feature) that just isn't documented, yet and won't be changed in the future.

However, CSS was kind enough to discuss the workarounds, and help us through them.

There are two workarounds: the first is to install SQL from the command line. You can try to use the command line options or configure an ini file.

Start /wait <CD or DVD Drive>\servers\setup.exe /qn




IP=,Public 15.13.15.x Interface" ADMINPASSWORD=<StrongPassword>


INSTALLSQLDIR="d:\Program Files\Microsoft SQL Server\" INSTALLSQLDATADIR=”k:\Microsoft SQL Server” SQLACCOUNT=theDomain\moss_sql_ser_2 SQLPASSWORD=<DomainUserPassword> AGTACCOUNT=theDomain\moss_sql_age_2 AGTPASSWORD=<DomainUserPassword> SQLBROWSERACCOUNT=theDomain\moss_sql_ser_2  SQLBROWSERPASSWORD=<DomainUserPassword> SQLCLUSTERGROUP="theDomain\gmoss_sql_ser_2" AGTCLUSTERGROUP="theDomain\gmoss_sql_age_2" FTSCLUSTERGROUP="theDomain\gmoss_sql_ser_2" ERRORREPORTING=1, SQMREPORTING=1 SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

The pure command line options approach did not work for me and Ramana Akula (Satyam Computer Services), the DBA at the client. If you can find the error in the use of the command line options please let me know.

We did not attempt the ini file method -- perhaps that would have worked.

Since this was a new cluster we took the second workaround: remove one node from the virtual server (SQL instance), which removes the SQL Browser Service, and then run the install on that node. This worked.

To remove a node from an instance or virtual server

1) logon to the node that owns the SQL Instance from which you wish to remove a node.

2) Go to Control Panel -> Add/Remove Programs -> Microsoft SQL Server and click change.

3) Select the instance and click next (ok we cheated on this screen shot this one is actually after we have done everything successfully):


4) Then select Database Engine (Clustered) and click Next


5) Then click Next on the Welcome Wizard.


6) Click Next after the System Consistency Checker or is it System Configuration Check (the SQL documentation vacillates between these two titles) is done


7) Then click next


8) Then click Maintain the Virtual Server. (Do not click Remove Microsoft SQL Server as this will uninstall the instance -- the virtual server).


9) Then in the list of Selected Nodes select the node you want to remove from the Instance/Virtual Server. Click Remove and then click Next.


10) After the uninstall is complete log off from the Node where you ran this and connect to the node you removed from the Instance/Virtual Server.

11) Then run the Install creating a new Failover Cluster.

When that is done, and before applying your SQL service packs, readd this node to the Instance/Virtual Server.

If this were a car repair manual I would simply say installation is the reverse of removal and no one would bat an eye. Instead I will give you a little more help:

Repeat steps 1-8

Then in the list of Available Nodes Select the node to be added and click Add. Then click Next.


As the installation completes you will then receive a warning about needing to reapply service packs to the node you just added to the Instance/Virtual Server.

A reboot may be required on the node to be re-added. But if you wish to avoid it


Additional Links and Articles

Failover Cluster Troubleshooting (I added some Community Content to this page to see it go to the Failover article and scroll to the bottom)

SQL Service Account needs to be in the group for Full Text Searching

Troubleshooting Task Scheduler for your SQL Cluster Install

Labels: ,

Saturday, August 16, 2008

SQL 2008: Processor or Server/CAL

Congrats to the SQL Server team for shipping 2008. It looks like a great product. Congrats as well for keeping the licensing costs the same and adding a new option with the web edition.

One question that many still have in mind is how to license SQL server. Processor licensing allows unlimited users and devices, whereas a server license allows unlimited users or devices as long as they have a CAL. Server CAL can be much cheaper than Processor license or it can become much more expensive.

Many ILM customers also have this question, and while the product team pushes you to processor licenses and says that if you want to go Server/CAL you need 1 CAL for each user that will connect directly to the SQL server (in case it does other stuff, or your ILM admins like to run unsupported queries under the hood) for each Management agent (how in the world did 1 device -- the ILM server -- become multiple devices and need many CALs? -- Maybe under some sort of multiplexing scenario since the data is getting pushed to other places) each situation will be different.

So I have developed a formula and a table to help you figure this out

Per Processor Licensing Costs =
Cost Per Processor * # of Servers * Avg CPUs per Server

Server + CAL Licensing Cost =
Cost Per Server * # of Servers + Cost Per CAL * # of CALs

Break Even Formula
# of CALs = (Cost Per Processor * Avg CPU per Server - Cost Per Server) * # of Servers/Cost Per CAL

As you can see the break even point between Server/CAL and Processor comes down to a ratio between CALs divded by the number of servers vs Processors (not cores, but physical packages) divded by the number of Servers. (Remember that one CAL allows one user or device to access an unlimited number of licensed servers -- you don't have to buy a CAL for each server you want to access. See

Using the sample licensing costs from Microsoft's site

Edition Workgroup* Standard Enterprise
Per Processor  $         3,700  $       5,737  $     23,911
Per Server  $            730  $         885  $       8,487
Per CAL  $            146  $         162  $          162


Using these sample numbers (and your costs maybe different)


Break Even Points in terms of CALs per Server vs CPU/Server
Workgroup Standard Enterprise
CPUs/Server CALs/Server CALs/Server CALs/Server
1                  25               30               95
2                  51               65             243
4               136             538
6                 833
8               1,128
10               1,424
12               1,719
14               2,014
16               2,309
18               2,604
20               2,900
32               4,671
64               9,394


10 SQL Enterprise Edition servers with an average of 2 quad core processors (that is still just two processors for licensing purposes) and I have less than 2430 (243x10) devices or users to license and am likely to maintain these ratios than Server/CAL should be cheaper than processor licensing. 20 processor licenses at $478,220 and 10 server licenses $84,870 plus $393,350 cost the same. Hiring more people and/or acquiring more devices might tip the balance, but acquiring more SQL servers or adding processors to existing boxes could counter that.

As you can see this is really an enterprise wide decision how will I license SQL servers for my organization.

What to do about users coming in from the web, well you can use processor licensing for those SQL servers or you could go with the web edition for $15 per processor per month. What about data on internal servers? Replicate it to the web edition server! SQL to SQL communication does not require a CAL.

Remember that "SQL Server 2008 Web may be used only to support public and Internet-accessible Web pages, sites, applications, and services."

In terms of features it is comparable to Workgroup edition, so none of the high availability features like clustering or mirroring are supported, only log shipping. There are several minor differences in the functionality of Web vs Workgroup

No ad hoc reporting through report builder, in service broker it can only be a client and its development tools come from SQL Express Management studio.

Labels: , , ,

Wednesday, July 30, 2008

Pending Exports Report in ILM

Hopefully this topic will stir up some excitement among those wondering how to query objects in the connector space. The technique I am about to explicate for you works for both exports and imports.

As many of you aware, my colleague and fellow ILM MVP Brad Turner created the community reporting pack for MIIS/ILM some time ago. This is a package of reports written in SQL Server Reporting Services (SSRS).

Most of you are also aware that you can tell an import or export run profile step to drop an audit file. The audit file is in DSML format (an XML format). You can use XML files as the source for SSRS reports, they can also be used.

A later report he created was for Pending Exports, to show clients what records are about to be exported (drop audit file and stop) or what records were just exported (drop audit file).

1) Turn on the drop audit file for the export run profile step.

2) Create a virtual directory in IIS that points to the MAData subfolder location and allows you to see the file

3) Create a data source in SSRS for that file and only that file. This means you have to create a data source for each audit file. Wow -- doable but painful!

A short while ago I took this process and made it even slicker. I present the background of all this to show why Brad and I form the nucleus of a great team. I had not thought of creating a report based on the audit file. I viewed the audit file as a troubleshooting technique, not as a great way to be able to report on exports or imports. My inspiration was how to make this more flexible.

I created a stored procedure (only works on SQL 2005) that uses SQLXML (specifically the sp_xml_preparedocument and OpenXML to shred the XML data to relational data). 

Additionally, I created the stored procedure so that it can accept a lot of parameters, allowing us to report the pending exports (or just exported) for any of the MAs).

That means that we only need one data source -- pointing to the database that houses the stored procedure.

First allow me to demonstrate the basic technique:



SELECT @ADXMLData = BulkColumn 
FROM OPENROWSET(BULK 'C:\Ensynch_projects\Reports\ILMReports\copy of admaexports.xml',SINGLE_NCLOB) AS AD

DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @ADXMLData, '<mmsml xmlns:a=""/>'

            SELECT * 
            FROM OPENXML(@docHandle, N'//a:mmsml/a:directory-entries/a:delta/a:dn-attr/a:dn-value/a:dn',2) 
             With (
                    DeltaOp varchar(100) '../../../@operation'
                    ,DNAOp varchar(100) '../../@operation'
                    ,DNVOp varchar(100) '../@operation'
                    --,ObjType varchar(50) '../../../primary-objectclass'
                    ,ObjectDN varchar(1000) '../../../@dn'
                    ,AttrName varchar(100) '../../@name'
                    ,dn        varchar(1000) '.'
            ) Export

EXEC sp_xml_removedocument @docHandle

For this query I was first focused on some group updates. I need to show the client how we were going to update their distribution lists.

This query takes the XML from the DSML file and shreds it back to relational data like so


update add   CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org member CN=MontyHALL, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update add   CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org member CN=Joe Montana, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update add   CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org member CN=Steve Young, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update add   CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org member CN=Fred Idaho, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update add add CN=Group2, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org member CN=Fred Idaho, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update add add CN=Group2, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org member CN=MontyHALL, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update add add CN=Group2, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org member CN=Joe Montana, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org


Next week I will show how to add the parameters and then I will show how to make the report. If you are lucky I might even make a video and post it!

Labels: , , ,

Thursday, May 15, 2008

Processing Actions Asynchronously outside of ILM MA's

For years developers have had access to the Microsoft Message Queue (MSMQ) as a way to be able to queue up actions for processing later or on a remote machine. With the release of SQL 2005 back in well 2005, developers with access to SQL 2005 could replace these MSMQ apps with SQL Service Broker Queues (SSB). With ILM 2007 /MIIS 2003 SP 2 supporting SQL 2005 the use of SQL Service Broker Queues became much more accessible to ILM Developers.

Here is an article comparing MSMQ with SQL Service Broker Queues

Here it is another discussion on the matter in an MSDN forum:

Here is an interesting blog post about working with both SQL Service Broker Queues (SSB) and

Windows Workflow Foundation:

The DevHawk has many good insights into SSB.

One huge advantage to me in dealing with SSB is that I can query a view just like a table to examine its current contents. SSB is also very easy to scale out.

At DEC 2008 Craig Martin and Marshall Hamilton (both from OCG North America) spoke on the SSH MA (originally developed by Patrick Rempel from OCG Germany) which is being used to manage thousands of Unix servers (well together multiple instances of the MA are managing several thousand servers) through SSH connections and issuing he command line commands to create users etc.

One problem they had to solve was what if one server is down during import? Regurgitate data for systems that are down which necesitates caching the data somewhere. Another problem I envision in such scenarios is that performance is probably an issue. Imagine a slightly different design.

What if an intermediate database was used to hold the results from the servers. Importing data from the servers on separate schedules, then importing to ILM on yet another separate schedule. The requests to import could be popped into a queue for multiple processes to implement. For executing exports have the XMA pop the requests into a SSB queue and then have a process that pulls info from the queue. If performance on export becomes an issue it is child's play to scale out with SSB, add another process that pulls import request messages from the queue.

Another use case for SSB is to replace anytime MSMQ has been used. Benefits: SSB can be clustered for failover, back up is as simple as backing up the database hosting the SSB queues.

For a Gentle Intro to SSB

Labels: , , , , ,