OpsanBlog

Enterprise Single Sign-on (ENTSSO) – Back up the Master Secret

This is part of a series of posts devoted to responding to Enterprise Single Sign-on issues.

Event Type: Error
Event Source: ENTSSO
Event Category: Enterprise Single Sign-On
Event ID: 10589
Date:  26-4-2004
Time:  13:03:37
User:  N/A
Computer:
Description:
The master secret has not been backed up. If you lose the master secret all the information stored in the SSO system will be lost permanently and your systems may fail to work correctly. Please use the SSO admin tools to back up your master secret.

Scary, I’ll agree.  Simple fix, however.  This step is part of BizTalk installation on this blog (not published yet; write me for an advance copy), but I’ve broken it out here:

• On the Start menu, click Run.
• In the Run dialog box, type “cmd”, and then click OK.
• At the command line, go to the Enterprise Single Sign-On installation directory.
• The default installation directory is <drive>:\Program Files\Common Files\Enterprise Single Sign-On.
• Type “ssoconfig -backupsecret <backup file>”, where <backup file> is the path and name of the file where the master secret will be backed up. For example, “A:\ssobackup.bak” to back up to a floppy disk, or “C:\mastersecret_servername.bak” to be moved to VSS or some other secure repository.
• Provide a password to protect this file. You will be prompted to confirm the password and to provide a password hint to help you remember this password.
• Store the backup file in a secure location.

The backup file can be used to restore a Master Secret Server or promote a group member to master secret server, as needed.

Microsoft SQL Maintenance Jobs for BizTalk Server 2004

This article covers some observations and challenges with the MSSQL maintenance jobs installed with BizTalk Server 2004.  For error messages I’ve seen associated with other MSSQL maintenance jobs, please see my blog post: Microsoft SQL Maintenance Jobs for BizTalk Server 2004.  I will update this article as I continue to resolve issues and receive feedback.

To refresh; BizTalk Server 2004 creates 8 maintenance tasks to keep the MessageBox and other BizTalk databases running smoothly.  The jobs include:

• Backup BizTalk Server
• CleanupBTFExpiredEntriesJob_BizTalkMgmtDb
• MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb
• MessageBox_Message_Cleanup_BizTalkMsgBoxDb
• MessageBox_Parts_Cleanup_BizTalkMsgBoxDb
• PurgeSubscriptionsJob_BizTalkMsgBoxDb
• TrackedMessages_Copy_BizTalkMsgBoxDb
• TrackingSpool_Cleanup_BizTalkMsgBoxDb

More detail on these jobs is available in the BizTalk Database Appendix (in progress; contact me for an advance copy) and in the BizTalk documentation on MSDN.  These jobs are set up to run at default intervals ranging from once a minute to once a week and, for the most part, require little tweaking.

You must edit and enable the first and last jobs on the list in order for them to function.  For more detail, see the BizTalk Database Appendix (in progress; contact me for an advance copy).  If you see the jobs failing, view the job history; some interesting error messages can arise:

The following error occurs if you don’t install all the features of BizTalk Server 2004 (HWS, BRE, etc.):

“Warning: The table ‘#catalogs’ has been created but its maximum row size (8281) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. [SQLSTATE 01000] (Message 1708)  Invalid object name ‘adm_OtherBackupDatabases’. [SQLSTATE 42S02] (Error 208)  Warning: The table ‘#catalogs’ has been created but its maximum row size (8281) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. [SQLSTATE 01000] (Error 1708).  The step failed.“

To resolve this issue, create the ‘adm_OtherBackupDatabases’ table in the BizTalkMgmtDb (or it’s equivalent in your installation) in SQL Query Analyzer with the following script:

CREATE TABLE [dbo].[adm_OtherBackupDatabases] (
[DefaultDatabaseName] [nvarchar] (128) NOT NULL ,
[DatabaseName] [nvarchar] (128) NOT NULL ,
[ServerName] [nvarchar] (80) NOT NULL ,
[BTSServerName] [nvarchar] (80) NOT NULL
)
GO

For more detail, please see “The Backup BizTalk Server SQL Job Fails ..“ article in MSDN.

Jeff Lynch has a useful article on these jobs in BizTalk Server 2004 – Database Growth.  It’s well worth a look.

Testing Distributed Transaction Coordinator on a Remote MSSQL Server using SQL Query Analyzer

When using transactions in SQL or BizTalk, the Distributed Transaction Coordinator (DTC) must be configured to allow network clients (typically referred to “Network DTC Access”)  This step is part of BizTalk installation on this blog (found in My Reference Posts), but I’ve broken it out here.

On the SQL Server that will allow remote transaction access:

• Navigate to Administrative Tools, Component Services.
• Click Component Services.
• Expand Computers.
• Select the local computer, right-click for Properties.
• Select the MSDTC tab and click the Security Configuration button.
• Check the “Network DTC Access” checkbox.
• Check the “Network Clients” checkbox.
• Click “OK” until the Component Services window is closed (DTC will stop and restart).

Once complete, perform this simple test to ensure you can execute transactions on a remote MSSQL client (requires SQL Client Tools):

• Open SQL Enterprise Manager and connect to the server to test.  Local or SA crendentials are suitable for this test.
• In the left pane, select the desired server.
• Open SQL Query Analyzer.
• Paste the following code in the query pane, modifying the [‘servername’] attribute with the server name you’re testing (square brackets are required).
• Execute the query.

If DTC is properly configured, the query should execute and return a single field.

begin tran

create table #temp (columname varchar(20))
insert #temp select top 1 name from [xprtm-michaelco].master.dbo.sysobjects
select * from #temp
drop table #temp

commit tran

I’ve tested this in multiple environments where DTC operations have failed, but I have yet to see it fail in a previously-working environment under the following conditions:

• A previously-working DTC environment, removing “Network Clients” or “Network DTC access”.
• A previously-working DTC environment, stopping the DTC service on the SQL Box.

I’d appreciate any insight or observations from experts.

MSMQ and MSMQT Coexistence?

This document references other documents in the installation/configuration document set.

While I see it ideal to have single queuing support on a system, there may be a time where your situation requires both protocols.  Maybe your BizTalk implementation needs to access MSMQ elsewhere in the enterprise for a different purpose.  Whatever the situation, I’ve tested these steps to add MSMQ to a system:

• Add two static IP addresses to the system; one for the existing MSMQT and the other for MSMQ. If you already have one protocol installed, make it static and add the second.
• Register the IP addresses in DNS with different names.
• Install MSMQ (message queuing) from ‘Add/Remove Windows Components’.
• In the registry, create a new key (string) under “HKLM\SOFTWARE\Microsoft\MSMQ\Parameters“ called “BindInterfaceIP“ and set the value to the IP address for MSMQ.
• Configure BizTalk and at the Message Queuing screen, set the IP and name for MSMQT.
• Enable the MSMQT adapter per post-configuration tasks.

Once enabled, give each queue a unique address and you should be good to go.

Microsoft SQL Maintenance Plans – Part One

I’m collecting a series of notes on MSSQL maintenance plans.  There may be some bits herein that can assist; please comment liberally and provide pointers if you think I’ve left something out.

My primary contact with these plans is through BizTalk Server 2004.  More detail on these jobs is available in the BizTalk Database Appendix (not yet published in this BLOG), my reference posts and in the BizTalk documentation on MSDN.

I mention these specifically because 1) I work with them the most and 2) I recently resolved issues with these jobs on a system;  my memory is still fresh.  Some maintenance notes:

• When creating a maintenance plan, group databases with transactional logging (full recovery mode) together. Mixing with other transaction types (simple) will generate errors.  You’ll get ‘(SQLSTATE 42000)’ and ‘(ERROR 22029)’ error messages in the job history and error number 208 in the Windows Event Log.  Look at each database and ensure the modes are the same for the databases selected for this operation.
• The above error will also arise if you attempt an operation that requires the database to be in single-user mode (rebuilding an index, for example).  To look for this issue, click “Management“ and right-click “Database Maintenance Plans” , selecting “Maintenance Plan History“.  Sort for the failed job and click “Properties”.   Troubleshooting Database Maintenance Plan Failures on MSDN provides some hints on the above issues.
• If you see the ever-present (for me, anyway) “The table ‘#catalogs’ has been created but its maximum row size (8281) exceeds the maximum number of bytes per row (8060)“ error, on a BizTalk server the cause is a missing adm_OtherBackupDatabases table in the BizTalkMgmtDb database.  Create this table per the Microsoft SQL Maintenance Jobs for BizTalk Server article on this blog.

More to follow.

BizTalk Server 2004 Technical Guides and Resources

Microsoft has made available a variety of technical guides for deployment, migration, security and performance/capacity planning.  They are aimed primarily at BizTalk Administrators and developers, and cover a wide range of the develop-deploy life cycle. The guides are available on MSDN at:
http://msdn.microsoft.com/BPI/TechGuides/default.aspx

In various travels, I’ve come across a series of other BizTalk resources:

Main Public Website
http://www.microsoft.com/biztalk/

Product Documentation
http://www.microsoft.com/biztalk/techinfo/productdoc/2004/default.asp

BizTalk Server 2004 Installation Guide
http://www.microsoft.com/downloads/details.aspx?FamilyId=32FF7D19-9E25-44CE-8A8C-03E3EB5D726F&displaylang=en

MSDN Product Documentation
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/def/htm/ebiz_def_portal_page.asp

Adapters – General
http://www.microsoft.com/biztalk/evaluation/adapter/default.asp

Security Recommendations for BizTalk Server Components
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/deploying/htm/ebiz_depl_secure_wtti.asp

Case Studies
http://www.microsoft.com/biztalk/evaluation/casestudies/default_2004.asp

White Papers
http://www.microsoft.com/biztalk/techinfo/whitepapers/2004/default.asp

Microsoft BizTalk Server Newsgroups
http://communities.microsoft.com/newsgroups/default.asp?icp=Biztalk&slcid=us

Scott Woodgate – Microsoft Product Manager
http://blogs.msdn.com/scottwoo

QuickStart Guide to Learning BizTalk Server 2004
http://blogs.msdn.com/luke/articles/380519.aspx

BizTalk Server 2004 Tutorials
http://www.microsoft.com/downloads/details.aspx?familyid=9C64562C-3FA7-49BA-885E-82213D00776E&displaylang=en

Modifying File Names in BizTalk Send Adapters

There are times you’ll want to control the name of a message as it’s saved to disk (default is GUID).  The question arose if promoted properties were allowed; a link to this is presented below the bullets.  Thanks to Todd Uhl for providing this.

• %datetime_bts2000%: UTC date time in format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (e.g. 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds)
• %datetime%: UTC date time in format YYYY-MM-DDThhmmss (e.g. 1997-07-12T103508)
• %datetime.tz%: Local date time + time zone from GMT in format YYYY-MM-DDThhmmssTZD, (e.g. 1997-07-12T103508+800)
• %time%: UTC time in format hhmmss
• %time.tz%: Local time + time zone from GMT in format hhmmssTZD (e.g. 124525+530)%DestinationParty%: Name of the destination party. The value comes from message the context property BTS.DestinationParty.
• %DestinationPartyID%: Identifier of the destination party (GUID). The value comes from the message context property BTS.DestinationPartyID.
• %DestinationPartyQualifier%: Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier
• %SourceFileName%: Name of the file from where the File adapter read the message. The file name includes extension and excludes the file path, for example, foo.xml. When substituting this property, the File adapter extracts the file name from the absolute file path stored in the FILE.ReceivedFileName context property. If the context property does not have a value, for example, if message was received on an adapter other than File adapter, then the macro will not be substituted and will remain in the file name as is (for example, C:\Drop\%SourceFileName%).
• %SourceParty%: Name of the source party from which the File adapter received the message.
• %SourcePartyID%: Identifier of the source party (GUID). The value comes from the message context property BTS.SourcePartyID
• %SourcePartyQualifier%: Qualifier of the source party from which the File adapter received the message.

The following link points to a blog presenting two ways to use a promoted property as a file name: http://geekswithblogs.net/sthomas/archive/2004/11/13/14949.aspx

Promote a group member to master secret server

If your MSS should fail, here are the steps to promote a member server to the MSS:

• Change the master secret server name from the original to the new using ssomanage: “ssomanage -updatedb”.
• Stop the ENTSSO service on the new master secret server.
• Start the ENTSSO service on the new master secret server. It will recognize that it is the master secret server and that it has no secrets.
• On the new master secret server, restore the backed up master secret file using ssoconfig: “ssoconfig -restoresecret BackupFile”

The new server is now the master of the group.

Slow performance and large MessageBox

This was a tricky one.  We’d seen performance dropping over a period of days.  Symptoms were as follows:

• Constant SQL churning.
• Large MessageBox Database size.
• Large MessageBox backups for system volume.
• Horrendously slow response times.

I decided at first it was the lack of the “Back up BizTalk Databases” job (this job archives completed messages in the MB database as it runs).  Once enabled, performance got worse as the large MessageBox created large backup files and lots of processing.  Of course, running out of space on the backup drive didn’t help, but that’s another discussion.

In digging (using SQL Query Analyzer), I found thousands of messages (140,000 in our case), all dehydrated, in the following tables:

• SELECT Count(*) AS BizTalkServerIsolatedHostQ FROM BizTalkServerIsolatedHostQ
• SELECT Count(*) AS DynamicStateInfo_BizTalkServerIsolatedHost FROM DynamicStateInfo_BizTalkServerIsolatedHost
• SELECT Count(*) AS Instances FROM Instances
• SELECT Count(*) AS MessageParts FROM MessageParts
• SELECT Count(*) AS MessageRefCountLogTotals FROM MessageRefCountLogTotals
• SELECT Count(*) AS Spool FROM Spool

So, I identified two potential causes:

• Long-running scope of STP orchestrations (I don’t think this caused it, but it’s something we should try to resolve, time allowing).
• BizTalk defect as described in MSKB 867449.  Hotfix released on 6/11/2004; but I don’t see it in any patches as of this writing.  This is an actual defect, relating to a BizTalk server that loses connection spamming the message tables once connection is re-established.  For the REALLY sleepy, read this article:
  http://support.microsoft.com/default.aspx?scid=kb;en-us;867449&Product=bizt2002

As it turns out, In this environment, the BizTalk and SQL servers are on Virtual Server .VHD files and undergo a nightly backup.  If the BizTalk server starts sooner than the SQL server, BizTalk frantically (and correctly) attempts to reconnect.  However, as the defect article describes, it pushes large numbers of messages into the MB.

The manual fix:

• Use HAT (Operations, Messages with no filter) and delete dehydrated messages.  The downside: HAT can only delete 2047 at a time .. and is S-L-O-W.
• Write something in WMI (I found some incomplete examples, but couldn’t use them).

I settled on backing up the MessageBox database and execute the following SQL statements in SQL Query Analyzer:

• DELETE FROM BizTalkServerIsolatedHostQ
• DELETE FROM DynamicStateInfo_BizTalkServerIsolatedHost
• DELETE FROM Instances
• DELETE FROM MessageRefCountLogTotals
• DELETE FROM MessageParts
• DELETE FROM Spool

This has the effect of orphaning all the dehydrated message parts.  After this, run all the maintenance jobs in SQL Enterprise Manager (Management, SQL Server Agent, Jobs) to tidy up the orphan references. Restart both servers so they’ll recover, and watch performance on each as they get to know each other again.

I’d appreciate comments as to the veracity of this solution; did I get all the tables, am I causing other damage, etc.

BizTalk 2004 Configuration Failures

I started this post to list suspect issues that may impact loading a BizTalk configuration (i.e., running configframework.exe in either a silent or interactive mode).  I may eventually split it into separate posts (WMI, security, permissions, etc.).  I am not trying to offer specific solutions to all failures in this post, although I do provide some suggestions and pointers to solutioins where I’ve seen these issues.  This really is a collection of things you can confirm during the configuration steps.

The application of a BizTalk configuration file can be your delight or downfall, and differs for most every environment in which I’ve installed.  Kudos to the big brains at MS for spending their resources on all that’s great about BizTalk; I’m confident they’ll come around to my wish list of easier/faster/better configuration options.

So, that said, let’s look at what makes a configuration fail, easy to hard (note: these are from a multi-server deployment on a domain; some items will not apply to single-server deployments).

Easy (failures in this group typically roll back and do not require restoring a server to vanilla or running configframework with the /u switch):

• Unreadable configuration file (immediate; confirm you can open the XML file in IE before attempting to apply it).
• Missing configuration file or bad path.
• Non-existent user accounts (immediate; confirm domain and account names).
• Non-existent group accounts (immediate; confirm domain and group names).
• Bad passwords (the wizard checks these as you enter them).
• Non-existent SQL server -or- databases/MDFs on the SQL server.
• SQL Connectivity (this can be easy, medium and very hard, if connection is lost during the configuration step).

With these issues (for the most part), you either cannot complete the wizard, or configframework will fail on invocation with a pointer to your configuration file.  You will realize and resolve most of them while the wizard is running (confirm/create accounts, check passwords, remove databases or MDFs, etc.).  Note: BizTalk wants to run over TCP/IP and Kerberos connection to SQL, so save some time: ensure you can connect to the BizTalk server over port 1433 in SQL Enterprise Manager before you run the configuration wizard.

Medium (will likely require restoration to vanilla, and removal of the present configuration):

• IIS Application Pool identity failure (if installing BAS, ensure you log on with the same account that the BAS application pool runs under).
• Inadequate permissions on the SQL Server for the WSS (BAS) administration account (be sure this user has database creator and security administrator roles).
• Improper group membership (if configuring using domain accounts, forgetting to include the domain name for a particular service, causing the configuration to look for a local group that will not exist).

The result of these errors is a rollback, where BizTalk removes the configured components from the server, leaving behind the SQL databases and other artifacts.  See the post titled “Restoring BizTalk to a Vanilla State“ to recover from these errors.

Hard (cannot remove present configuration, or configuration times out):

• SQL Connection loss during configuration (has the result of putting the server in a half-configured state); look to network or WMI issues.
• WMI recycle failure (I’ve seen some services refuse to stop and prevent WMI from recycling during configuration; the configuration times out and the process must be killed).

When configuration must be terminated, the server is in a non-configured state; cannot be unconfigured or configured.  See the post titled “When Good Configurations go Bad“ for the fix to this state, then restore the server to vanilla.  Confirm SQL connectivity and do test recycles of WMI-related services with the account under which you’re installing; some environments have a Group Policy (GPO) to prevent local administrators from managing services (ceding this responsibility to domain admins).

I am keen to receive any feedback and other posts.  Please submit comments.