ILM "2" confirmHumanity="false"

I was getting ready to try out some of the various installation topologies that may be possible with ILM "2" including: separating the Portal and the Service (definitely possible), having two portals point back to the same service (I think it's possible), when I came across the most interesting item in the ILM "2" installation guide in the section on Installing the ILM Service and ILM Portal on separate servers. Let's see if you can spot it too:

On ILM Service server, edit the file

• c:\Program Files\Microsoft Identity Management\Common Services\Microsoft.ResourceManagement.Service.exe.config as follows:

  • <resourceManagementService certificateName="IdentityLifecycleManager2" confirmHumanity="false" servicePrincipalName="IdentityManagementService/computername"/>

 

What in the world can that be about? confirmHumanity="false"? Well at least the coder followed camelCasing so we may have a hint as to the perpetrator's identity -- Jerry Camel have you been doing some work for Microsoft?

Will someone please explain what this means? Is ILM "2" the Terminator? I mean it will deactivate and deprovision your accounts when you leave -- and afterwards it can show that you have been terminated!

We may never know! But comments are welcome.

Business Problems and their Technical Roots

Business Problem	Possible Underlying Business Problem	Cause	Technical Cause
Business launches a strategic initiative late	Employees don't receive communications that they should	Don't have email accounts Aren't in the right distribution lists	Lack of automated distribution list management and self service fulfillment
Employee  can't fulfill a customer order	Employees don't have access to resources	Accounts haven't been provisioned to the systems they need Aren't member of the groups or roles they need	Lack of automated security group management and self service fulfillment
Leak of Customer Information OR  stock affecting info OR Valuable data is destroyed	Employees have access to resources they shouldn't OR Former employees still have access to resources	Permissions granted too liberally Or User accounts haven't been terminated	Lack of automated security group management and self service fulfillment AND Lack of Automated deprovisioning
Customer Care Rep can't find  right person to whom they can escalate key customer problem	Employees can't find accurate, up to date  contact info for each other	Global address lists and other databases out of synch	Too many directories No IDA tool to synchronize them IDA tool hasn't matched the identities

Business Problems VS Technical Problems

A business problem is when employees can't execute their job duties in an efficient fashion. In fact sometimes they are unable to complete the tasks at all. Business problems are especially costly when they directly affect customers. These problems can cause cash flowing into the company to be delayed as a customer waits to place an order, or to receive goods (and hence to pay), they can cause revenue to be lost as a customer temporarily takes their business to a competitor or a finds a substitute, sometimes this leads to customers forming new business relationships and loss of all future revenue from that customer. Non-customer affecting business problems may result in higher costs without affecting revenue. For example a problem on the job shop floor causes workers to put in overtime to complete customer jobs on time, raising costs without directly affecting the customer.

As Rodd Wagner and James K Harter point out in their book 12: The Elements of Great Managing company profitability is highly correlated with employees knowing what is expected of them, and when having adequate tools and materials (elements 1 and 2). When these two elements are short changed business problems result, costs go up and revenue goes down.

A technical problem is often the root cause of employees not having adequate tools or materials. A more specific definition could be that a technical problem is cause of the Information Technology department (people, process and technology) not being able to adequately fulfill a need expressed by the business. This inadequacy could be a matter of accuracy, timeliness, or consistency. It could also be a matter of lacking the capability. These are technical problems, like can't provision and deprovision accounts and entitlements quickly enough, accurately enough (deleted the account for the wrong Jane Smith), consistently enough (only 10 of the user's 16 accounts deprovisioned on the average per IDC) because the Identity Management system goes down frequently, or is too complex to change and the rules it enforces are outdated. Another possible technical problem could be that requests are lost or seem to take forever to be fulfilled either because the process supported by paper or a help desk ticket doesn't move efficiently. Requests may be fulfilled incorrectly, or inconsistently because the fulfillment is not automated and/or checklists don't exist or aren't followed.

Hence good managers look for ways to provide a knowledge of expectations and the sufficient tools and materials for their employees to do their jobs. I believe you'll agree that one of those great tools is ILM "2"

Millionaire Next Door and All Your Worth

No this post isn't about my new neighbors, or my new house.

Its about the secret to wealth.

First you buy two copies of the Millionaire Next Door and then you give one to each of your next door neighbors. Suddenly your odds of getting rich will improve.

Ok hopefully that gives you a chuckle. Nonetheless, here is my prescription for improving America's financial health. While I normally write on the topics of Identity Management and SQL Server, I did also earn an MBA from the Eller College of Business at the University of Arizona, and have done some financial counseling as a volunteer through church. So I have done some deep thinking on these matters.

Reading the Millionaire Next Door affects people, and helps them realize that wealth isn't about showing it off, that the people that appear as though they live like Millionaires often aren't, and quite frequently those whose net worth truly does exceed a million dollars don't like to show it.

After one catches the appropriate philosophy, and begins to desire to save a million dollars rather than have spent a million dollars, it is time to move to the how, and that is where All Your Worth comes in. This book by Elizabeth Warren and her daughter Amanda Tyagi, teach you how to put your financial life in balance, how to be prepared for a rainy day, and they teach you without forcing you to track every penny. They have excellent suggestions and ask you to look deep and make the hard choices, such as did I buy too big of a house? Perhaps the single most powerful thing I found was how to measure your finances. Income = Needs + wants + savings. So how to calculate your wants, track every penny? No! Subtract your needs (usually big ticket items) and your savings (they have great easy formulae to help you calculate that) from your income and you are left with how much you spend on your wants. If you are 50% needs, 20% savings and 30% wants then you are in balance and are beginning to be prepared for the rainy day.

Once you have gotten your life in balance and are saving well you need to be a smart investor. While Dr Elizabeth Warren recommends indexed mutual funds with no loads and low costs, I don't always agree that the stock market is the best place for funds. So I recommend Ben Stein's Yes you can time the market. No he doesn't reveal some magic secret about how to pick individual stocks, but rather about when to buy US Gov't bonds vs when to buy the aforementioned indexed mutual funds.

Additionally, I highly recommend John Nofsinger's Psychology of Investing. Learn how to avoid making sucker's choices with your investments, learn why people often make emotional decisions about stocks.

No the preceding advice is no guarantee, nor does it mean that your author is set for life, but I think this is the way to go.

Be skeptical about anything that makes life sound too easy, and check out the gurus (especially real estate gurus) John T Reed

ILM 2 Web Services Part 1 and 1/2

A few days after my post about setting up the ILM 2 Web Service reference Joe Schulman and others from the ILM product group began a new blog designed to fill in the gaps in the knowledge in the community about how to use the web services. So far the blog looks great and is a welcome addition to my knowledge and the communities knowledge base! Great job Joe and Company and thanks for the link to my blog.

Identity Management Extensibility

I recommend starting out by reading the intro post as it gives a great overview of what to expect.

Also check out the code samples online at MSDN

Shortly I will be getting back to more technical posts.

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).

  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 ;)

CSExport -- Getting the ILM Connector Space into SQL

How can I query the ILM Connector Space?

"You can't." -- Yes you can.

"You have to WMI." --  But WMI is limited in what you can query and slow

"You have to use CSExport and then use XML tools." -- that works, but this may be better

The above are various answers you may receive. However, thanks to the power of SQLXML we can issue SQL queries against the ILM Connector Space (after it has been exported using CSExport).

The following code will only work on SQL 2005. Some of the SQLXML tricks involved here have been described previously

So I will discuss some of the new tricks.

The csexport file spits out XML that looks a lot like entity attribute value schema and this means that each attribute gets its own row when you bring it into SQL. Since that doesn't work well for me to write further queries I use a pivot operator to make the data relational.

The code below will retrieve data from the pending import holograms of an AD connectorspace and doesn't handle multi-valued attributes.

 

SET NOCOUNT ON

DECLARE @ADXML TABLE (ADID int identity (1,1), XMLFROMCS XML)

INSERT @ADXML (XMLFROMCS)

SELECT * 

FROM OPENROWSET(BULK 'C:\FuzzyLogic\ADExport\ADExport.xml',SINGLE_BLOB) AS AD

DECLARE @ADXMLData XML

SELECT @ADXMLData = XMLFROMCS

FROM @ADXML

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @ADXMLData

TRUNCATE TABLE dbo.adUsers 

INSERT dbo.adUsers (dn, [cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title])

SELECT dn, [cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title]

FROM

    (

            SELECT dn, attrname, attrvalue  

            FROM OPENXML(@docHandle, N'/cs-objects/cs-object/pending-import-hologram/entry/attr/value',2)  

             WITH 

                (dn nvarchar(450) '../../@dn'

                ,primaryobjectclass nvarchar(450) '../../primary-objectclass'

                ,attrname nvarchar(450) '../@name'

                ,attrvalue nvarchar(450) '.'

                ,multivalued nvarchar(450) '../@multivalued'

                ) adusers

            WHERE adusers.primaryobjectclass = 'user' AND adusers.multivalued = 'false'

                AND attrname in ('cn','department','displayname', 'employeeid', 'givenName', 'mail', 'sn','title') 

        ) AS ADList1

    PIVOT (MIN(attrvalue) FOR attrname in ([cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title])

 )AS ADUserPivot

EXEC sp_xml_removedocument @docHandle 

Other interesting changes in ILM RC0

From the release Notes:

http://technet.microsoft.com/en-us/library/dd239143.aspx

Announcing Identity Lifecycle Manager “2” Release Candidate

1) Don't create a multi-valued boolean attribute -- you will have to reinstall the ILM webservice

2) We can now create a required attribute binding without affecting existing objects!

3) support for separating the Portal from the database and having multiple portal servers

4) Enhancements for customizing notification and request emails

a) Request details will be included in the out of the box notifications

ILM 2 RC 0 is here!

Identity Lifecycle Manager "2" Release Candidate 0 is released.

 ILM '2' Release Candidate (RC)

Improvements at  First Glance

1. No need for "Managed:" to prefix metaverse attributes so that they can be managed by ILM 2 Web Service
2. Changes to Workflow to make it easier
3. Install routines improved
4. The confusing ARP file needed for custom activities has been replaced by an Object in the Data store
5. SQL 2008 is required

Is ILM 2 RC0 is out? Well the docs are!

Most of the online Technet docs now read "ILM "2" (Release Candidate)"

For example check out the new ILM 2 Release Candidate Installation Guide

http://technet.microsoft.com/en-us/library/cc561135.aspx

No more support for SQL 2005 from here on it is all SQL 2008

"SQL Server 2008 64-bit Standard or Enterprise Editions"

Good thing I just got back my score reports on my two SQL 2008 beta exams:

I am now an

MCTS: SQL Server 2008, Implementation and Maintenance

and

MCTS: SQL Server 2008, Business Intelligence Development and Maintenance

I guess that means I am qualified to keep working with ILM 2 ;)

ILM 2 Web Services Part 1 The Service Reference

Together, Mark Struck of Ipseity Inc and I, have figured out (after much beating of our heads against brick walls) how to use the ILM 2 Enumeration Endpoint to perform some basic reporting. (I figured out how to send the enumeration and get a response and then Mark figured out how to correctly form the pull messages so as to be able to retrieve the actual objects -- teamwork at its finest). We would also like to thank Mark Gabarra and Rob Ward for their input.

Here are some lessons we learned:

First lesson: the SDK provided with ILM 2 Beta 3 is incomplete and in some cases misleading. (Just one of those areas that hasn't been well documented yet)

Second lesson: Reading the WS-Enumeration specification is like drinking from a firehouse.

Third lesson: Case matters when specifying the endpoint.

Today's post will show you how to setup the Service Reference.

Type in http://localhost:526/ResourceManagementService/MEX/

The case of the url is important. R M S must be capitals and so must MEX.

The name you type in for name space is important as it is the name you will use in your code.

I recommend replacing the ServiceReference1 that you see in the figure with ILM_RMS.

After you click Go it shows you the various services available and operations for each service. The Search Service is the one we will want.

Once you click OK you see the following show up under service reference:

An enumeration.wsdl file is generated and your app.config file will also be populated with lots of settings such as this one.

   <binding name="ServiceMultipleTokenBinding_Search" closeTimeout="00:01:00"
                    openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
                    bypassProxyOnLocal="false" transactionFlow="false" hostNameComparisonMode="StrongWildcard"
                    maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
                    messageEncoding="Text" textEncoding="utf-8" useDefaultWebProxy="true"
                    allowCookies="false" contextProtectionLevel="Sign">
                    <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
                        maxBytesPerRead="4096" maxNameTableCharCount="16384" />
                    <reliableSession ordered="true" inactivityTimeout="00:10:00"
                        enabled="false" />
                    <security mode="Message">
                        <transport clientCredentialType="Windows" proxyCredentialType="None"
                            realm="" />
                        <message clientCredentialType="Windows" negotiateServiceCredential="true"
                            algorithmSuite="Default" establishSecurityContext="false" />
                    </security>

You can also generate this info through a command line approach using the svcutil.exe utility.

Then in your code you make use of it like this as you see in my code:

Dim scReporting As ILM_RMS.SearchClient 
scReporting = New ILM_RMS.SearchClient("ServiceMultipleTokenBinding_Search")

See how to use the Namespace that you setup when you made the service reference, and how you need  use the binding name setup in the app.config file. Instead of using the settings in the config file you can use a programmatic approach to setting up the bindings. Look at the example from Mark Struck's C# code:

WSHttpContextBinding wsBinding = new WSHttpContextBinding(); 
// Cannot use WSHttpBinding since it does not allow you to Sign the EnumerationContext element 
// WsHttpContextBinding provides a property called ContextProtectionLevel which defaults to Sign, which is 
// what is needed to communicate with the web service when the action is Pull. 
// WsHttpBinding will work if you are just calling the web service with the Enumerate action. 
//WSHttpBinding wsBinding = new WSHttpBinding(); 

// Set binding properties 
wsBinding.ReceiveTimeout = new TimeSpan( 0, 5, 0); 
wsBinding.SendTimeout = new TimeSpan( 0, 5, 0); 
wsBinding.Security.Mode = SecurityMode.Message; 
wsBinding.Security.Message.EstablishSecurityContext = false; 
wsBinding.Security.Message.NegotiateServiceCredential = true; 
wsBinding.Security.Message.ClientCredentialType = MessageCredentialType.Windows; 
wsBinding.Security.Message.AlgorithmSuite = System.ServiceModel.Security.SecurityAlgorithmSuite.Default; 

// Create EndpointAddress object and create the SearchClient object with the binding and endpointaddress objects 
EndpointAddress ep = new EndpointAddress(ILMSERVICE_URI_ENUMERATION); 
SearchClient searchClient = new SearchClient(wsBinding, ep);

Under the hood of ILM 2 -- Part 2 Read the WCF Trace!

Take a look at Part 1 to enable tracing

To view the log you need to have installed the Windows SDK and then you use the Service Trace Viewer

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\SvcTraceViewer.exe

If the file is over 50 MB you will get the partial loading screen like this one. Try and limited the estimated size, if you open too much it will be very slow. Even 20 MB can be really slow.

If you need to adjust this after you open the file you can

To view what is happening you click on Activity 00000000000000 and then browse through the  actions

Most of it is just noise -- "The Policy Application Manager is executing" or "The Policy Application Manager has finished executing" (the first two showing below.

Take a look at the next action ExecuteQuery.ExecuteReader where in it is retrieving a list of Workflow Definitions objects (See the detail information where it shows the XPath query filter inside the parenthesis

Query: QueryProcessor.ExecuteQuery.ExecuteReader(/WorkflowDefinition)

The you can see how it retrieves the objects that were returned as part of the query

The process continues to retrieve objects and then sets up a WorkflowServiceHost for the workflows

Under the hood of ILM 2 -- Part 1 Enable WCF Tracing!

Want to understand what is happening with your custom ILM 2 workflow? or your calls to the web service?

Try enabling WCF Tracing. By enabling WCF tracing for the Identity Lifecycle Manager Resource Management Service you get to track requests to the webservice. This can help you figure out if your requests are even getting to the webservice.

To enable tracing open the config file:

C:\Program Files\Microsoft Identity Management\Common Services\

Microsoft.ResourceManagement.Service.exe.config

In the <configuration> after </configsections> and before <appsettings> add the following:

<!-- Enable Tracing -->
  <system.diagnostics>
    <trace autoflush="true" />
    <sources>
      <source name="Microsoft.ResourceManagement" switchValue="All">
        <listeners>
          <add name="text" type="System.Diagnostics.TextWriterTraceListener" initializeData="c:\logs\service.txt" traceOutputOptions="Timestamp,ThreadId,DateTime"/>
          <add name="xml" type="System.Diagnostics.XmlWriterTraceListener" initializeData="c:\logs\service.svclog" traceOutputOptions="Timestamp,ThreadId,DateTime"/>
        </listeners>
      </source>
    </sources>
  </system.diagnostics>
  <!-- End Enable Tracing -->

Then you need to create a directory called c:\ILMLogs and restart the ILM Common Services. If you don't create the directory then the logging still won't work, and you'll have to restart the service.

To view the log you need to have installed the Windows SDK and then you use the Service Trace Viewer

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\SvcTraceViewer.exe

For more info on how to read and interpret the trace see Part 2.

For more info on writing your own traces from your own code see Craig Martin's post on WCF tracing.

Happy Halloween -- It's all about Identity Management

Last night as I took my children trick or treating through our neighborhood I thought about Halloween from an Identity Management Perspective:

We provision temporary identities to our children (costumes) that allow them to make a claim when they show up at neighbors' doors "Trick or Treat (I am wearing costume -- the claim; will your grant me access to candy -- the resource request)?"

At which point the neighbor will almost invariably give out some candy.

But try showing up without a costume! Most have their permissions set up so that you can't get candy without a valid claim.

Then we as parent's provide a virus scanner -- looking over the candy to ensure that it is safe.

I am still trying to figure out how the pumpkins and Jack O'Lanterns fit into the metaphor. Happy Halloween!

Live ID's are now Open ID's, Geneva supports SAML 2.0

At the PDC Microsoft's Kim Cameron and colleague Bertocci Vittorio announced that Microsoft Live is now an Open Id provider. Additionally, when signing into Live you can use Information Cards (Info Card, Card Space, Geneva Card Space).

They also demonstrated the new Geneva Framework (formerly known as Zermat) -- essentially a successor to Windows Server 2008 Active Directory Federation Services, and showed it supporting SAML 2.0 the "protocol" not just SAML 2.0 the token.

Other new announcements included the Microsoft Federation Gateway, which allows you to federate with Microsoft,  Live (including both managed domains and individual consumers -- all 400 million of them), other Geneva (ADFS) organizations, and other third party Service Token Services (STS). They also showed issuing LINQ queries against the .Net Access Control Service to retrieve roles to make authorization decisions.

Good show gentlemen! This is a tremendous step forward for interoperability. I just hope that the interoperability between Geneva and other third parties STS's is much easier to implement than the brittle, painful interoperability between ADFS and Shibboleth (that didn't support SAML 2.0). Hopefully, Shibboleth will be one of those 3rd parties!

The Semi-Automated Install of ILM 2 Beta 3

ILM 2 Beta 3 won't perform a completely automatic quiet install but we can come close. Colleague Brad Turner and I have developed the following approach to the install and the post install tasks.

Brad worked out most of the issues with the ILM 2 Services install itself and then I worked on most of the issues with the post install tasks. I will cover the install of the Metadirectory services first, then the ILM 2 Beta 3 Identity Management Platform Services including its batch files and then discuss the post install tasks and present its related files.

First up the install of the Metadirectory services. At this point I assume you have covered the prerequisites mentioned in the ILM "2" Beta 3 Installation Guide (of course we posted some of this to the community content there).

Be sure and put in your own preexisting AD groups and path to the installation folder, as well as service account and password.

InstallSync.cmd

@echo off
rem This section specifies Group names, adding the domain\ in front configures them as a domain based group
set GROUPADMINS="info\ILM Admins"
set GROUPOPERATORS="info\ILM Operators"
set GROUPACCOUNTJOINERS="info\ILM Joiners"
set GROUPBROWSE="info\ILM Browse"
set GROUPPASSWORDSET="info\ILM PasswordSet"

rem ILM or DB directory?
set DBFileLocation=SQLDefault
set DBFILEMMSLOCATION="0"

rem To Use local server and instance (Default):
set SQLServerStore=LocalMachine
set SQLServerInstance=DefaultInstance

rem Installation Folder for x64
set INSTALLDIR64="E:\Program Files\Microsoft Identity Integration Server"

rem SERVICEACCOUNT is the Sync Engine Account
set SERVICEACCOUNT=svc.ilmsync
rem SERVICEDOMAIN is the domain the Sync Engine Account is in
set SERVICEDOMAIN=info
rem SERVICEPASSWORD is the password for the Sync Engine Account
set SERVICEPASSWORD=P@$$w0rd

msiexec /i "Identity Lifecycle Manager Evaluation.msi" /norestart /log setup.txt SERVICEACCOUNT=%SERVICEACCOUNT% SERVICEDOMAIN=%SERVICEDOMAIN% SERVICEPASSWORD=%SERVICEPASSWORD% DBFILEMMSLOCATION=%DBFILEMMSLOCATION% SQLServerStore=%SQLServerStore% SQLServerInstance=%SQLServerInstance% DBFileLocation=%DBFileLocation% GROUPADMINS=%GROUPADMINS% GROUPOPERATORS=%GROUPOPERATORS% GROUPACCOUNTJOINERS=%GROUPACCOUNTJOINERS% GROUPBROWSE=%GROUPBROWSE% GROUPPASSWORDSET=%GROUPPASSWORDSET% DBFILEMMSLOCATION=%DBFILEMMSLOCATION% INSTALLDIR64=%INSTALLDIR64%

Brad and I like to use environmental variables defined in the batch file to "self-document the batch file." Since the install and the post install tend to reuse many of the same settings I moved all of the environmental variables into one batch file which is then called from the InstallSever.cmd file and the PostInstallTasks.cmd file. This file is called SetInstallVariables.bat:

@echo off

set MAIL_SERVER="mail.ensynch.info"
set SERVICE_ACCOUNT_NAME=svc.ilmws
set SERVICE_ACCOUNT_PASSWORD=P@$$w0rd
set SERVICE_ACCOUNT_DOMAIN=info
set SERVICE_ACCOUNT_EMAIL="svc.ilmws@ensynch.info"
set RMS_PORT=526
set SERVICEADDRESS=localhost
set STS_PORT=527
set SHAREPOINT_PWD_RESET_SITE_URL="http://%COMPUTERNAME%/PasswordPortal/"
set SHAREPOINT_SITE_URL="http://localhost/identitymanagement/"
set SQLSERVER_SERVER="."
set SYNCHRONIZATION_SERVER_ACCOUNTNQ=info\svc.ilmma
set SYNCHRONIZATION_SERVER_ACCOUNT="%SYNCHRONIZATION_SERVER_ACCOUNTNQ%"

SET WSSSTSADM="%commonprogramfiles%\microsoft shared\web server extensions\12\bin\stsadm"

SET INTIAL_EMAIL_ALIAS=%USERNAME%@%USERDNSDOMAIN%
SET INITIAL_DESCRIPTION="%USERNAME% Initial Admin for ILM Portal"

rem Don't work...
set SQMOPTINSETTING=0
set MAIL_SERVER_IS_EXCHANGE=0
set MAIL_SERVER_USE_SSL=0

rem Shows up in the UI, but doesn't apply...
rem set INSTALLDIR="E:\Program Files\Microsoft Identity Management\"

The installServer.cmd file:

@echo off

CALL SETINSTALLVARIABLES.bat

msiexec /i ilm-server-64bit.msi /log ilmserverx64.txt ACCEPT_EULA=1 MAIL_SERVER=%MAIL_SERVER% SERVICE_ACCOUNT_NAME=%SERVICE_ACCOUNT_NAME% SERVICE_ACCOUNT_PASSWORD=%SERVICE_ACCOUNT_PASSWORD% SERVICE_ACCOUNT_DOMAIN=%SERVICE_ACCOUNT_DOMAIN% SERVICE_ACCOUNT_EMAIL=%SERVICE_ACCOUNT_EMAIL% RUNNING_USER_EMAIL=%USERNAME%@%USERDNSDOMAIN% MAIL_SERVER_IS_EXCHANGE=%MAIL_SERVER_IS_EXCHANGE% MAIL_SERVER_USE_SSL=%MAIL_SERVER_USE_SSL% RMS_PORT=%RMS_PORT% SERVICEADDRESS=%SERVICEADDRESS% STS_PORT=%STS_PORT% SHAREPOINT_PWD_RESET_SITE_URL=%SHAREPOINT_PWD_RESET_SITE_URL% SHAREPOINT_SITE_URL=%SHAREPOINT_SITE_URL% SQLSERVER_SERVER=%SQLSERVER_SERVER% SQMOPTINSETTING=%SQMOPTINSETTING% SYNCHRONIZATION_SERVER_ACCOUNT=%SYNCHRONIZATION_SERVER_ACCOUNT%

After installation of ILM 2 Beta 3 you have several post install tasks per the ILM "2" Beta 3 Installation Guide:

1. Grant Full Control rights to the ILM "2" SharePoint site to the initial user of the site
2. Grant user rights for the ILM “2” Windows SharePoint Services site to domain users who require it
3. Configure the ILM “2” Password Management Portal for anonymous access
4. Disable SharePoint Indexing
5. Exchange Server 2007 Web Service (EWS) Configuration
6. Exchange Server 2007 Certificate installation
7. ILM MA permissions (SQL permissions)
8. Verify ILM Service account group membership
9. ILM “2” Web Portal Access

For items 1 and 2 the guide provides a command line but for steps 3-9 the guide only provides steps that must be done through the GUI.

With the help of some stsadm custom extensions written by SharePoint MVP Gary LaPointe we can easily automate step #3. We will use gl-setanonymousaccess

Step 4 could be automated by using the following standard stsadm command to stop the Search service

stsadm -o osearch -action stop -f

Or this could be handled during your WSS 3.0 install, which is how we did it. I'll have to ping another Ensynch colleague Jeff Holliday (he calls his blog the SharePoint Redemption)  to see how he did that when he created our install for WSS 3.0

Steps 5 and 6 are manual as is 9 (well 9 is pretty involved), but 7 (ILM MA user account SQL Permissions) is easy to automate with a SQL Script. (For the time being I am going to be lazy about step 8 -- which could be automated but which I leave as an exercise to the reader).

We need to create a login for the account we specified for the ILM 2 MA, grant it a user in the MSILM database and make it a member of the db_owner fixed database role.

You'll see that I took advantage of sqlcmd's ability to do some preprocessing replacement using parameters or environmental variables. In this case I used environmental variables. You can see wherever it says [$(something)] -- like this: [$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)] which is set in the SetInstallVariables.bat file

These environmental variables are set in a batch file that calls sqlcmd to execute this file: ILMMA_Permissions.sql

USE [master]

CREATE LOGIN [$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)] FROM WINDOWS WITH DEFAULT_DATABASE=[MSILM]
GO

USE [MSILM]
GO
CREATE USER [$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)] FOR LOGIN [$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)]
GO

EXEC sp_addrolemember N'db_owner', N'$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)'

GO
DECLARE @myvar int
SELECT @myvar = (SELECT CASE
WHEN 1 = (SELECT COUNT(*) FROM sys.syslogins where name = '$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)')
AND 1 = (SELECT COUNT(*) FROM sys.database_principals WHERE name ='$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)' )
AND 1 = (SELECT COUNT(*)
FROM sys.database_role_members
WHERE member_principal_id =
(SELECT top 1 principal_id FROM sys.database_principals WHERE name ='$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)' )
AND role_principal_id =
(SELECT top 1 principal_id FROM sys.database_principals WHERE name ='db_owner')
) THEN 0
WHEN 0 = (SELECT COUNT(*) FROM sys.syslogins where name = '$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)')
THEN 1 -- Couldn't create Login
WHEN 0 = (SELECT COUNT(*) FROM sys.database_principals WHERE name ='$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)' )
THEN 2 -- Couldn't map user to MSILM database
WHEN 0 = (SELECT COUNT(*)
FROM sys.database_role_members
WHERE member_principal_id =
(SELECT top 1 principal_id FROM sys.database_principals WHERE name ='$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)' )
AND role_principal_id =
(SELECT top 1 principal_id FROM sys.database_principals WHERE name ='db_owner')
)
THEN 3 -- Couldn't assign user to db_owner role
ELSE 4 -- unknown error
END)
EXIT(SELECT @myvar)

Here is the PostInstallTasks.cmd file:

@echo off

CALL SETINSTALLVARIABLES.bat

sqlcmd -S %SQLSERVER_SERVER%  -E -i ILMMA_Permissions.sql
if {%errorlevel%} == {4} (Echo  Unknown SQL Error
                goto SQLPermissionsError)
if {%errorlevel%} == {3} (Echo  Couldn't assign user %SYNCHRONIZATION_SERVER_ACCOUNTNQ% to db_owner role
                goto SQLPermissionsError)
if {%errorlevel%} == {2} (Echo  Couldn't map user %SYNCHRONIZATION_SERVER_ACCOUNTNQ% to MSILM database
                goto SQLPermissionsError)
if {%errorlevel%} == {1} (Echo  Couldn't create Login %SYNCHRONIZATION_SERVER_ACCOUNTNQ% On SQL Server
                goto SQLPermissionsError)

echo %WSSSTSADM% -o adduser -url %SHAREPOINT_SITE_URL% -userlogin %USERDOMAIN%\%USERNAME% -useremail %INTIAL_EMAIL_ALIAS% -username %INITIAL_DESCRIPTION%  -role "Full Control"
%WSSSTSADM% -o adduser -url %SHAREPOINT_SITE_URL% -userlogin %USERDOMAIN%\%USERNAME% -useremail %INTIAL_EMAIL_ALIAS% -username %INITIAL_DESCRIPTION%  -role "Full Control"
echo Done Setting access for initial user
echo %WSSSTSADM% -o adduser -url %SHAREPOINT_SITE_URL% -userlogin "%SERVICE_ACCOUNT_DOMAIN%\Domain Users" -useremail users@%USERDNSDOMAIN% -username "Domain Users" -role "Contributor"
%WSSSTSADM% -o adduser -url %SHAREPOINT_SITE_URL% -userlogin "%SERVICE_ACCOUNT_DOMAIN%\Domain Users" -useremail users@%USERDNSDOMAIN% -username "Domain Users" -role "Contributor"

REM comes from here http://stsadm.blogspot.com/2008/03/set-anonymous-access.html
echo Using This tool from http://stsadm.blogspot.com/2008/03/set-anonymous-access.html   to set anonymous access
%WSSSTSADM% -o gl-setanonymousaccess -url %SHAREPOINT_PWD_RESET_SITE_URL% -anonstate entireweb
if {%errorlevel%} NEQ {0} goto oopsNeedCustomstsadm

goto end

:SQLPermissionsError
echo please  run and troubleshoot ILMMA_Permissions.sql in SQL Management studio
echo remember to replace $(SYNCHRONIZATION_SERVER_ACCOUNTNQ) with %SYNCHRONIZATION_SERVER_ACCOUNTNQ%
goto end

:oopsNeedCustomstsadm
echo go download http://www.thelapointes.com/blog/stsadm.zip then run Package\ReleaseWSS\deploy.bat
echo if the deploy.bat doesn't work then change the first line to have the .wss.wsp like so
echo SET SOLUTION_NAME="Lapointe.SharePoint.STSADM.Commands.wss.wsp"

:end

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.

Changing SQL Service Account Passwords for a Cluster

Here is an excellent script for changing service account passwords and should work fine as long as you restart the SQL services afterwards.

However the following blog post indicates that more is going on than just a password change:

"never use the plain old Windows Service Control Manager (SCM) to manipulate SQL Services.  The SQL Server Configuration Manager does a lot more work in the background to keep security consistent across the installation. "

This next blog post points out a way to change the SQL Service Account password programmatically in a way that is equivalent to use the Configuration Manager.

So here I have combined several approaches with the help of the WMI Code Creator.

My goal is to create a script that will accept a list of computers (set at the beginning of the script -- design time) and a two command line parameters, the user account and the password and then go change the password for all SQL Services on all computers listed (nodes of the cluster) that use that service account. So here it is:

' Change SQL Service Acct Passwords
' Equivalent to using SQL Configuration Manager
' Change passwords on multiple computers for all
' sql services using the supplied username
' Execute after changing the password in Active Directory
' Ideal for Clusters
' SQL 2005 or later
' Copyright 2008 David Lundell
' dlundell@ensynch.com

'1st parameter is the username domain\user or in the case of a local user
Set objArgs = WScript.Arguments
If objArgs.Count <> 2 Then
        Wscript.Echo "Usage is:"
    Wscript.Echo "cscript SQLSvcPasswordManagement.vbs /""domain\user"" /""NewPassword"""
    WScript.Quit(1)
End IF

SvcAcct = objArgs(0)
SvcPassword = objArgs(1)

' replace the array with the list of computers in the cluster
arrComputers = Array("mbinb2")
For Each strComputer In arrComputers
   WScript.Echo
   WScript.Echo "=========================================="
   WScript.Echo "Computer: " & strComputer
   WScript.Echo "=========================================="

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")
WScript.Echo  "SELECT * FROM SqlService WHERE StartName = '" & SvcAcct & "'"
Set ServiceCol = objWMIService.ExecQuery( _
    "SELECT * FROM SqlService WHERE StartName = '" & SvcAcct & "'",,48)
For Each objItem in ServiceCol
    Wscript.Echo "-----------------------------------"
    Wscript.Echo "SqlService instance"
    Wscript.Echo "-----------------------------------"
    Wscript.Echo "StartName: " & objItem.StartName

    Wscript.Echo "-----------------------------------"
    Wscript.Echo "ServiceName: " & objItem.ServiceName
ServiceName = objItem.ServiceName
SvcType = objItem.SQLServiceType

' Obtain an instance of the the class
' using a key property value.
Set objShare = objWMIService.Get("SqlService.ServiceName='" & ServiceName & "',SQLServiceType='" & SvcType & "'")

' Obtain an InParameters object specific
' to the method.
Set objInParam = objShare.Methods_("SetServiceAccountPassword"). _
    inParameters.SpawnInstance_()

' Add the input parameters.
objInParam.Properties_.Item("AccountNewPassword") =  SvcPassword
objInParam.Properties_.Item("AccountOldPassword") =  ""

' Execute the method and obtain the return status.
' The OutParameters object in objOutParams
' is created by the provider.
Set objOutParams = objWMIService.ExecMethod("SqlService.ServiceName='" & ServiceName & "',SQLServiceType='" & SvcType & "'", "SetServiceAccountPassword", objInParam)

' List OutParams
If objOutParams.ReturnValue = 0 Then
    WScript.Echo  ServiceName & ": Successfully changed the password"
Else
    WScript.Echo  ServiceName & ": failed to change the password with error code " & objOutParams.ReturnValue
End IF
Wscript.Echo "Out Parameters: "
Wscript.echo "ReturnValue: " & objOutParams.ReturnValue

Next
Next

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

Parameter	Value (filled in by Client)
Service account for SQL Server Database Engine	DOMAIN\ MOSS_SQL_SER_1
Group for Service Account for SQL Server Database	DOMAIN\GMOSS_SQL_SER_1
Members of Group Above	DOMAIN\ MOSS_SQL_SER_1
Service account for SQL Server Agent	DOMAIN\ MOSS_SQL_AGE_1
Group for SQL Server Agent	DOMAIN\ GMOSS_SQL_AGE_1
Members of Group Above	DOMAIN\ MOSS_SQL_AGE_1
Service account for SQL Server Full Text Engine (FTE)	DOMAIN\ MOSS_SQL_FTE_1
Group Service account for SQL Server Full Text Engine (FTE)	DOMAIN\ GMOSS_SQL_FTE_1
Members of Group Above	DOMAIN\ MOSS_SQL_FTE_1 DOMAIN\ MOSS_SQL_SER_1

 

SQL Instance 2

Parameter	Value (filled in by Client)
Service account for SQL Server Database Engine	DOMAIN\ MOSS_SQL_SER_2
Group for Service Account for SQL Server Database	DOMAIN\GMOSS_SQL_SER_2
Members of Group Above	DOMAIN\ MOSS_SQL_SER_2
Service account for SQL Server Agent	DOMAIN\ MOSS_SQL_AGE_2
Group for SQL Server Agent	DOMAIN\ GMOSS_SQL_AGE_2
Members of Group Above	DOMAIN\ MOSS_SQL_AGE_2
Service account for SQL Server Full Text Engine (FTE)	DOMAIN\ MOSS_SQL_FTE_2
Group Service account for SQL Server Full Text Engine (FTE)	DOMAIN\ GMOSS_SQL_FTE_2
Members of Group Above	DOMAIN\ MOSS_SQL_FTE_2 DOMAIN\ MOSS_SQL_SER_2

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

VS=MOSS-ENT-SQL2

INSTALLVS=SQL_Engine INSTANCENAME=MOSSENTSQL2 ADDLOCAL=SQL_Engine,Client_Components ADDNODE=node1,node2,node3

GROUP=MOSS-ENT-SQL2

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

SAPWD=<StrongPassord>

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