Azure, Installation, SQL, Windows

SQL Server VC++ Installation voes

I’ve installed SQL Server any number of times over any number of versions, but I have never had this problem before, and I am not sure why I got it now. However, since searching the web gave me very little in the way of a direct, working, solution I thought I’d write mine down. I was using Windows Server 2008 R2 Datacenter edition, SP1, patched to May 2012 standard, aka The Windows Server 2008 R2 image available in the Windows Azure Virtual Machine preview and installing SQL Server 2008 R2 Developer edition onto it. Now, I don’t think they are related, but I am not ruling it out that there is an issue in some way with that image. Since I am not doing anything but starting the image and running the installation which I have previous downloaded and un-packed from its ISO on a separately attached data drive.

The error I am getting is this:

The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail. (Exception from HRESULT: 0x800736B1).

I tried several times, and often this error would occur during install, but on the fifth (or so) attempt the install was successful and all looked to have installed fine, until I tried opening SQL Server Management Studio (SSMS). And got the exception there instead.

Now following the instructions in the exception text I did two thing, first – check the event log, where I found this:

Activation context generation failed for "C:Program Files (x86)Microsoft SQL Server100ToolsBinnVSShellCommon7IDESsms.exe".Error in manifest or policy file "C:WindowsWinSxSmanifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest" on line 0. Invalid Xml syntax.

Now looking through the event log I could see that I got this error for a number of other applications and services as well, and that ssms wasn’t alone in this.

Next, I ran sxstrace, ie (from an elevated command prompt):

sxstrace trace –logfile:trace.log

The I tried to start ssms to produce the error, which it did. So then I ran:

sxstrace parse –logfile:trace.log –outfile:trace.txt

(More on the sxstrace tool here).

The trace file, among other things, gave me this (similar) information:

INFO: Parsing Manifest File C:WindowsWinSxSmanifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest.
    INFO: Manifest Definition Identity is (null).
    ERROR: Line 0: XML Syntax error.
ERROR: Activation Context generation failed.

This file is from the Visual Studio C++ 2005 Service Pack (SP) 1 Redistributable Package. So I proceeded to download and install both the original 2005 Redistributable (x86, x64) and SP1 (x86, x64), hoping that would fix the problem and correct the manifest file. Not so for me.

I still wanted to see if the error could be fixed by “normal” procedures so I ran System File Checker (SFC). It produced the following result:

sfc /scannow

Beginning system scan.  This process will take some time.

Beginning verification phase of system scan.
Verification 100% complete.
Windows Resource Protection found corrupt files but was unable to fix some of th
em.
Details are included in the CBS.Log windirLogsCBSCBS.log. For example
C:WindowsLogsCBSCBS.log

The log file contain this (snipped somewhat for readability):

Manifest hash for component [ml:280{140},l:152{76}]"x86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1" does not match expected value.
Expected:{l:32 b:43e8b1d9f404eb67105ab15282fd01f5bf4cd30f7f0c5d1250d11e9384ae9cc5}
Found:{l:32 b:d47fec989a9ad0351d4effd5984343181925f15919245da2a0609e1c5d68f280}.
Unable to load manifest for component [ml:280{140},l:152{76}]"x86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1"
[SR] Cannot verify component files for Microsoft.VC80.ATL, Version = 8.0.50727.4053, pA = PROCESSOR_ARCHITECTURE_INTEL (0), Culture neutral, VersionScope neutral, PublicKeyToken = {l:8 b:1fc8b3b9a1e18e3b}, Type = [l:10{5}]"win32", TypeName neutral, PublicKey neutral, manifest is damaged (TRUE)

At this point I gave up on any form of allowing installers or the system to fix the problem for me and went at the file myself using Advanced guidelines for diagnosing and fixing servicing corruption. The file is readable (although empty), but I cannot edit it (even if I am an administrator). Only SYSTEM has access to the file. So to be able to edit it I must first take ownership of it and grant ACLs:

>takeown /f C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.1833_none_d1c5318643596706.manifest

SUCCESS: The file (or folder): "C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.1833_none_d1c5318643596706.manifest" now owned by user "JEHBTS5Administrator".

>icacls C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.1833_none_d1c5318643596706.manifest /grant administrators:F
processed file: C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.1833_none_d1c5318643596706.manifest
Successfully processed 1 files; Failed processing 0 files

>takeown /f C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest

SUCCESS: The file (or folder): "C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest" now owned by user "JEHBTS5Administrator".

>icacls C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest /grant administrators:F
processed file: C:WindowswinsxsManifestsx86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1.manifest
Successfully processed 1 files; Failed processing 0 files

Now I can edit the file. As for the content I simply took it of another machine in which it existed and did not seem to have any issues. The content is this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<!-- Copyright © 1981-2001 Microsoft Corporation -->
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
    <noInheritable/>
    <assemblyIdentity type="win32" name="Microsoft.VC80.ATL" version="8.0.50727.4053" processorArchitecture="x86" publicKeyToken="1fc8b3b9a1e18e3b"/>
    <file name="ATL80.dll" hash="6d7ce37b5753aa3f8b6c2c8170011b000bbed2e9" hashalg="SHA1"/>
</assembly>

After saving the file I am (at least seemingly to this point) rid of the problems.

BAM, BizTalk, Installation

BizTalk Server and named SQL Server Analysis Services instances

The documentation for BizTalk Server 200X is very clear, and has been since BizTalk Server 2006. See the 2010 versions of technet wiki and download. It states:

Named instances of SQL Server 200X Analysis Services are not supported

But why is that? I set out to investigate if I could find a reason for it still being true.

Examining the history

In the beginning, there was a limitation with SQL Server 2000 Analysis Services (SSAS). It was not instance or cluster aware (much like SSIS today). Some time later with the help of service packs (if I can recall and use my search engine correctly) SSAS became cluster aware, but only supported running on the default instance. Thus the requirements and the reason that was true with BizTalk Server 2006 is crystal – due to the support of the underlying technology, namely SQL Server 2000, named instance of Analysis Service was not supported.

Present

I later versions of SQL Server, and the latest of SQL Server 2008 R2 specifically, there is no such limitation. Analysis Services is both cluster and instance aware and can be installed in a named instance. So the underlying limitation just isn’t there. But what about BizTalk? Does it have some built in limitation to how it uses Analysis Services that doesn’t allow working against a named instance?

The scenario

Two BizTalk Server Group installations – connected to their respective database instance – side-by-side. Both SQL Server instances has the database, agent and analysis services services installed. In the default instance I have a BizTalk Server Group configured with BAM tracking and Analysis, and a deployed activity and view when we start our journey. For the named instance I wanted to configure the same thing to see that there were no issue with

  • running Analysis Service in a named instance, and
  • running it side by side with the default instance and its Analysis Services on the same SQL Server machine.

Configuring BizTalk Server against a named instance of SQL Server 2008 R2 Analysis Services

First configuring the feature using BizTalk Server Configuration.

BTS2_BAM_config_seperateAS_2

Then applying the configuration and view the result.

BTS2_BAM_config_seperateAS_2_configDone

No issues.

Deploy BAM activities and views

Using bm.exe to deploy my BAM activity and view to my named instance.

BTS2_BAM_config_seperateAS_2_deployOK

No issues.

Using Tracking Profile Editor to connect my activity to an orchestration.

BTS2_BAM_config_seperateAS_btt

No issues.

Running some data through the integration

BizTalk Server behaves as expected. After running some sample data through the integration I could open my BAMPrimaryImportDb and run a Select against it to view my data.

BTS2_BAM_config_seperateAS_Select

Running my BAM_AN_<View> SSIS package

This was one of my prime candidates beforehand of where it could possibly go wrong. However as I used Business Intelligence Development Studio to inspect the package I could see that it was configured with datasources for the databases that it worked with, including the Analysis Services database, and it clearly points to the correct configured instance.

BTS2_BAM_config_seperateAS_BIstudio_BAM_AN_OrderView

Running it caused no incidents that I could detect. After having run the package and processed the cube I was ready to look at the data in the cube to see if it got there as expected.

Viewing the Live Workbook

The first thing to check was the live workbook (useless as I find this feature I wanted to see if it could read the data as expected).

BTS2_BAM_config_seperateAS_ExcelLiveWoorkbook

It could. And again, if we view the data connection properties here it point to my named instance.

BTS2_BAM_config_seperateAS_ExcelLiveWoorkbook_ConnectionProperties

(sorry about the swedish locale here, but you get the point)

Viewing data in the BAM portal

Next up, what about the BAM portal – would it be able to understand the named instance of SSAS and retrieve my data?

BTS2_BAM_config_seperateAS_BAMPortalView BTS2_BAM_config_seperateAS_BAMPortalActivitySearch

Yes. Both the Activity Search and the Aggregations show the data expected.

Conclusion

I can find no reason why SQL Server 2008 R2 Analysis Service cannot be used on a named instance with BizTalk Server 2010. Granted, this was just a small test and I certainly haven’t investigated this through every aspect (as per the usual disclaimer), but it “Works on my Machine”.

Do you know a reason that this will not work? Are you running this configuration yourself and can confirm further that it does work? Let me know…

HTH
/Johan

BizTalk, Configuration, Installation

(Not) Using Built-in Service Accounts for BizTalk Server

Recently I tried installing BizTalk Server 2010 in a pretty locked down environment – as in no accounts except a few domain accounts were given the “log on as a service” right. Thus as a first go I was left trying to use the default accounts on the machine.

These are my short summarized findings trying to run BizTalk using built-in accounts:

  • SQL Server – Can run as Local System or Network Service. NOT as Local Service.
  • SQL Server Agent – Can run as Local System or Network Service. NOT as Local Service.
  • For more SQL Server account info see this link.
  • The SSO service – can NOT run as Local Service or as Local System.
    imageimage
    It can run as Network Service, although there are some special requirements – namely: the SSO Administrators group must be pre-created, the Network Service account added to it and the computer restarted.
    image
  • BizTalk Server Runtime – Can NOT run as a any form of local built-in account.
    image
  • At this point I guess I could have gone on to try the other sub-services as well, like BRE, but why bother… Lesson learned. You cannot configure BizTalk Server using only the built-in accounts. Also this link from the BizTalk documentation clearly states that these accounts are not supported, though it is non-specific.

Quoted info on what these built-in accounts mean:

Local Service Account

The Local Service account is a built-in account that has the same level of access to resources and objects as members of the Users group. This limited access helps safeguard the system if individual services or processes are compromised. Services that run as the Local Service account access network resources as a null session without credentials. Be aware that the Local Service account is not supported for the SQL Server or SQL Server Agent services. The actual name of the account is "NT AUTHORITYLOCAL SERVICE".

Network Service Account

The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. Services that run as the Network Service account access network resources by using the credentials of the computer account. The actual name of the account is "NT AUTHORITYNETWORK SERVICE".

Local System Account

Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is "NT AUTHORITYSYSTEM".

BAM, BizTalk, Configuration, Installation

BAM and SSIS notes worth repeating

SQL Server Integration Services (SSIS) is not a clustered resource. Connecting to SSIS generally means connecting to any of the SQL Server servers in your cluster environment directly, not to a virtual cluster address. Configuring SSIS for a named BAM instance still involves configuring it against your clustered BAM database instance. The BizTalk install documentation (Multicomputer) also claims that SSIS needs to be installed on the BizTalk Servers. That’s incorrect. You could install SSIS and that’d be good, but I much rather install the management tools, as explained and outlined here.

While talking about BAM I can’t help but to mention an excellent article recently written by Saravana Kumar here.

64-bit, Administration, BizTalk Server 2009, Installation

BizTalk Server 2009 Installation Errors

I just thought I’d mention these install errors since I’ve gotten them now on several occasion when installing BizTalk Server 2009 Enterprise 64-bit Single Sign On Master Secret Server on Windows Server 2008 and SQL Server 2008 (although I doubt the last one has any role in these errors).

This page describes the procedure of Installing and Clustering the Enterprise SSO Master Secret Server, although it mentions nothing about these specific peculiarities.

Error1

Log

[10:03:05 Error] Setup runtime files for AMD64 platform: Fatal error during installation.
[10:03:05 Info] Marking Stage: Install/Installing components as Completed in TaskInfo XML
[10:03:05 Info] ReadTaskInfoXML: Attempting to load TaskInfo xml from string
[10:03:15 Info] Showing MessageBox with text: The following platform components failed to install and will need to be manually installed before setup can proceed: Setup runtime files for AMD64 platform: Fatal error during installation. Check the log for details. Return Code: 1

MSI (s) (8C:FC) [10:03:04:688]: Product: Microsoft BizTalk Server Setup Bootstrap Files (x64) — Error 1310. Error writing to file: atl90.dll. System error 0. Verify that you have access to that directory.
Error 1310. Error writing to file: atl90.dll. System error 0. Verify that you have access to that directory.
Action ended 10:03:04: InstallFinalize. Return value 3.
Action ended 10:03:05: INSTALL. Return value 3.
MSI (s) (8C:FC) [10:03:05:867]: Product: Microsoft BizTalk Server Setup Bootstrap Files (x64) — Installation failed.
MSI (s) (8C:FC) [10:03:05:868]: Windows Installer installed the product. Product Name: Microsoft BizTalk Server Setup Bootstrap Files (x64). Product Version: 3.8.368.0. Product Language: 1033. Installation success or error status: 1603.

Resolution

I have received this error if I connected to the server using Remote Desktop/Microsoft Terminal Service Client without using the /admin flag (former /console flag). Read more here.

Error2

Log

[10:34:48 Info] Showing MessageBox with text: The following platform components failed to install and will need to be manually installed before setup can proceed: Enterprise Single Sign-On Server: Unspecified error Check the log for details. Return Code: 1

[10:34:35 Info] Error 1928.Error registering COM+ application. Please ensure that DTC is enabled. Contact your support personnel for more information.
[10:34:35 Info] Showing MessageBox with text: Error 1928.Error registering COM+ application. Please ensure that DTC is enabled. Contact your support personnel for more information. Return Code: 0
[10:34:35 Info] Action 10:34:35: Rollback. Rolling back action:
[10:34:39 Info] Detailed Log information for product D:InstallBiztalk 2009PlatformSSO64SSO64.msi is available at <a href="C:UsersxxxAppDataLocalSetup(072909 103411).log">DetailedLog</a>
[10:34:39 Info] MSI installation returned 1603 – Fatal error during installation.
[10:34:39 Error] Error 1928 occurred during MSI installation.
[10:34:39 Error] Action 10:34:30: InstallComplus.8E665C9D_5561_45FC_AAA0_3878B87F3B86. Installing COM+ components
[10:34:39 Error] Error 1928.Error registering COM+ application. Please ensure that DTC is enabled. Contact your support personnel for more information.
[10:34:39 Error] c:depotsetupv2privatecommonsetupwizardexesetup.cpp(1670): FAILED hr = 80004005

Resolution

This error has occurred in clustered environments when the clustered instance of MSDTC was not active on the node on which the installation was taking place. Failover the MSDTC to the current node that installation is to be run on and try again.