SQL Server Faq

Salient Features of Sql Server 2008 (Katmai) Setup

Minimize the MSI code for setup and use MSI code for what it does the best.

In Sql 2005 entire setup process was coded using MSI code which uses the Windows Installer code and was found to be root cause of many setup failures in sql 2005.

However in sql 2008 setup process has been optimized such that MSI code is mainly used to lay down the support files locally and then spawn the setup process locally. There after depending the different scenario (viz install, repair, uninstall, add node to cluster  …etc) various different workflows are select and the customized code is executed.

 

Same Media can be used for the installation irrespective of the CPU architecture i.e x86, x64 and IA64bit

The initial setup.exe which is an unmanaged code detects the CPU architecture of the system and calls the appropriate setup100.exe from the respective folder (x86, x64, IA64) on the DVD

 

Edition of the Sql Server 2008 is decided by the Product Key Code

In sql server 2008, same media is used for the installation of the entire edition and the edition installed is mainly decided by the Product code

 

Setup.exe can be patched dynamically on the fly

One of the drawbacks of the sql server 2005 setup was that if there was any bug detected in the code of the setup.exe , the patching of the setup.exe was difficult and at times next to impossible.

With Katmai, it is possible to patch the setup.exe on the fly due to the following property of the bootstrapping code (setup.exe).

When the setup.exe is called it checks for the presence of the local copy of the setup.exe, In other words it checks if the support files are already installed or not. If it finds the local copy of the setup.exe of the same or greater version, the local setup.exe is launched.

So there are 2 ways to patch the setup process

1)      If you have a local share of the sql server 2008 installation media on the disk, you can simply replace the old sqlsupport.msi with the new sqlsupport.msi. This can be useful for enterprise customers which uses the single share of the copy of the media for multiple installation

2)      If the setup has been installed then in that case sql support files are already been installed. So in that case we will have to download an addition patch from Microsoft site and install the support files of the version higher than the version of setup.exe. So when the setup.exe of the RTM version is called, it finds a local copy of the support files of the version higher than RTM and as explained previously, it  launches the setup.exe of the local copy which is the patched setup.exe.

 

 

RULES AND VALIDATION CHECKS

With sql 2005, setup performs System Configuration checks (SCC) however with katmai the SCC checks have been further enhanced, there are various rules defined which needs to be met for the successful installation of the sql server.

There are Global Rules checks which apply to all the scenarios (viz install, upgrade, uninstall  …etc) however there are additional scenario specific rule checks which need to be met for the specific scenario to succeed.

 

Discovery Report

In sql 2005, there were many failure observed since the uninstall of the previous install was not clean and cannot be detected. In Katmai, Discovery report in the setup process can be used to check for the previous failed installation and which should be cleaned up so that they are not the cause of the failure in the new instance. In addition it can also be used to see the existing sql installation on the system and their version.

Generation of the Configuration File

In an enterprise when we need to perform multiple identical installations of the sql server on different system. It can be much beneficial, faster and efficient to use silent quiet installation of the sql server, wherein the entire configuration option are captured in the single configuration file and can be reused multiple time with minimum user intervention.

Each installation of sql server 2008 creates a configuration file which can be used for silent installation in future. In addition we can specifically run the setup process to create the configuration file. The default location of the configuration file which is created is

C:Program FilesMicrosoft SQL Server100Setup BootstrapLog<session _ folder>.

 

There are few new changes which I am not able to cover it now but I will try to cover them in my future posts.

 

BRIEF DESCRIPTION OF KATMAI SETUP PROCESS

In order to install, upgrade, uninstall, and patch the sql server 2008 instance on the system we need to call the setup.exe from the installation media.

The setup.exe is an unmanaged code which checks the basic pre-requisites to launch the actual Setup process. The basic pre-requisites check performed by setup.exe is

1)      It checks for the presence of the .Net Framework with SP1. If not present, it will initiate the installation of the .Net Framework with SP1.

 

Note:  The setup during the installation of .Net Framework 3.5 might say “Downloading from Internet”. However it actually loads the setup from local copy and does not connect to Internet.

 

2)      It checks for the presence of the Windows Installer 4.5 and if it is not present it will install it.

After installing these pre-requisites, it asks for reboot. And if there is any pending reboot operation from previous installation, it will ask for reboot and exit with error code 3010.

The Logs for the setup.exe (unmanaged code) is located in %temp%sqlsetup*.log.

After the initial bootstrapping performed by setup.exe it calls the setup100.exe which is managed code which performs the actual setup process

 

Error 11032 Unable to install Windows Installer MS while installing security patch KB 948109 on sql 2005 instance

While installing security patch KB 948109 on the sql server 2005, the installation of the patch may fail. In order to find the cause of the failure we need to look into the setup bootstrap logs which are located in the following location

C:Progarm FilesMicrosoft Sql Server90Setup BootstrapLOGHotfixSummary.txt

One of the cause of the failure can be Error 11032 which is reported in the summary.txt as shown

***********************************************************************

Product Installation Status

Product : SQL Server Database Services 2005 (SQLCMSP6)

Product Version (Previous): 3042

Product Version (Final) :

Status : Failure

Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB948109_sqlrun_sql.msp.log

Error Number : 11032

Error Description : Unable to install Windows Installer MSP file

———————————————————————–

Product : SQL Server Analysis Services 2005 (SQLCMSP6)

Product Version (Previous): 3042

Product Version (Final) : 3068

Status : Success

Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixOLAP9_Hotfix_KB948109_sqlrun_as.msp.log

Error Number : 0

Error Description :

———————————————————————————-

Product : SQL Server Reporting Services 2005 (SQLCMSP6)

Product Version (Previous): 3042

Product Version (Final) : 3068

Status : Success

Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRS9_Hotfix_KB948109_sqlrun_rs.msp.log

Error Number : 0

Error Description :

———————————————————————–

CAUSE:

====================

During the installation of the Hotfix (948109 in my case) the Windows installer looks for the folder FTData in the location c:Program filesMicrosoft sql serverMSSQL.XMSSQLFTdata.

However if the FTData folder has been willingly or unknowingly deleted from the location c:Program filesMicrosoft sql serverMSSQL.XMSSQL. then the setup of the HotFix may fail with error 11032 and following error will reported in the HotFix.log and SQL9_Hotfix_KB948109_sqlrun_sql.msp.log.

HotFix.log

=====

18/2008 18:08:30.614 Registry: Opened registry key “SoftwarePoliciesMicrosoftWindowsInstaller”

08/18/2008 18:08:30.632 Registry: Cannot read registry key value “Debug”

08/18/2008 18:10:02.595 MSP returned 1603: A fatal error occurred during installation.

08/18/2008 18:10:03.057 Registry: Opened registry key “SoftwarePoliciesMicrosoftWindowsInstaller”

08/18/2008 18:10:03.075 Registry: Cannot read registry key value “Debug”

08/18/2008 18:10:04.205 Copy Engine: Error, unable to install MSP file: e:73ee7eecf2f2f0aa67706f3eHotFixSQLFilessqlrun_sql.msp

08/18/2008 18:10:04.222 The following exception occurred: Unable to install Windows Installer MSP file Date: 08/18/2008 18:10:04.222 File: depotsqlvaultstablesetupmainl1setupsqlsesqlsedllcopyengine.cpp Line: 807

08/18/2008 18:10:09.275 Watson: Param1 = Unknown

08/18/2008 18:10:09.344 Watson: Param2 = 0x2b18

08/18/2008 18:10:09.378 Watson: Param3 = Unknown

08/18/2008 18:10:09.446 Watson: Param4 = 0×0

08/18/2008 18:10:09.481 Watson: Param5 = copyengine.cpp@807

08/18/2008 18:10:09.498 Watson: Param6 = Unknown

08/18/2008 18:10:09.515 Watson: Param7 = SQL9

08/18/2008 18:10:09.532 Watson: Param8 = @

08/18/2008 18:10:09.566 Watson: Param9 = x86

08/18/2008 18:10:09.583 Watson: Param10 = 3068

08/18/2008 18:10:09.601 Installed product: SQL9

08/18/2008 18:10:09.618 Installing product: OLAP9

08/18/2008 18:10:09.875 Registry: Opened registry key “SoftwareMicrosoftWindowsCurrentVersionUninstall”

08/18/2008 18:10:09.926 Installing instance: SQLCMSP6

08/18/2008 18:10:09.960 Installing target: KDESQLDBOP6

08/18/2008 18:10:09.995 Stopping service: MSOLAP$SQLCMSP6

08/18/2008 18:10:17.103 Stopped service: MSOLAP$SQLCMSP6

08/18/2008 18:10:17.137 Installing file: sqlrun_as.msp

08/18/2008 18:10:17.154 Copy Engine: Creating MSP install log file at: C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixOLAP9_Hotfix_KB948109_sqlrun_as.msp.log

08/18/2008 18:10:17.171 Registry: Opened registry key “SoftwarePoliciesMicrosoftWindowsInstaller”

08/18/2008 18:10:17.189 Registry: Cannot read registry key value “Debug”

08/18/2008 18:11:23.909 MSP returned 0: The action completed successfully.

SQL9_Hotfix_KB948109_sqlrun_sql.msp.log.

==================================

MSI (s) (48:50) [18:24:03:651]: Executing op: CustomActionSchedule(Action=CAFTEInstallFTERef.68C6D15C_77E0_11D5_8528_00C04F68155C,ActionType=1025,Source=BinaryData,Target=InstallFTERef,CustomActionData=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnFTERef|C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData)

MSI (s) (48:9C) [18:24:03:667]: Invoking remote custom action. DLL: C:WINDOWSInstallerMSI416.tmp, Entrypoint: InstallFTERef

FTECa.DLL: INFO: FTE: InstallFTERef(), Entering…

FTECa.DLL: INFO: FTE: GetFTERefInstallParams: FTERef : C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTDataFTECa.DLL: ERROR: FTE: InstallFTERef: Fail to create FTERef file: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTDatanoiseCHS.txt, Err=3

MSI (s) (48:50) [18:24:04:763]: User policy value ‘DisableRollback’ is 0

MSI (s) (48:50) [18:24:04:763]: Machine policy value ‘DisableRollback’ is 0

Action ended 18:24:04: InstallFinalize. Return value 3.

RESOLUTION/WORKAROUND:

====================

We need to create empty FTData folder in the location C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData (in my case) and rerun the setup and the setup runs fine without any errors

 

How to change the keys for the Encryption for an already encrypted database

Have you ever wondered how to change the keys for the encryption for an already encrypted database. For this you can use “Key Rotation”. Key rotation is the process of decrypting data with the old encryption key and encrypting the data with the new encryption key.

But how to use the Key Rotation with SQL Server 2005??

Below are specified a few details as to how you can go ahead and use the Key Rotation to change the key with which the data has been encrypted.

  1. You can add an encryption by a temporary new certificate.
  2. Drop the old encryption and the old certificate.
  3. Create a new certificate with the old name, add an encryption by it.
  4. Finally drop the encryption made with the temporary certificate and drop the temporary certificate as well.

And with this you have the data encrypted with a new key and the old key has been dropped.

You can refer to this blog by Laurentiu Cristofor of the Microsoft SQL Server Security team for more details and the alternatives as well.

 

Error 29527 while installing security patch 948109

While installing security patch KB 948109 on the sql server 2005, the installation of the patch may fail. In order to find the cause of the failure we need to look into the setup bootstrap logs which are located in the following location

C:Progarm FilesMicrosoft Sql Server90Setup BootstrapLOGHotfixSummary.txt

One of the cause of the failure can be Error 29527 which is reported in the summary.txt as shown

Following error reported in the sumary.txt

 

·          **********************************************************************************

·          Product Installation Status

·          Product                   : SQL Server Database Services 2005 (ACT7)

·          Product Version (Previous): 3042

·          Product Version (Final)   :

·          Status                    : In Progress

·          Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB948109_sqlrun_sql.msp.log

·          SQL Express Features      :

·          Error Number              : 29527

 

CAUSE:

====================

 

The issue is caused due to improper installation of support files as there may be missing files and dll’s in the 90 folder located in

 

c:program filesMicrosoft sql server90

 

 

RESOLUTION/WORKAROUND:

====================

 

We need to install a new instance of the sql server of the same edition and same built on the same system on which the original instance which need to be patched lies (i.e with required Service pack ) side by side which populates the 90 folder with correct files and dll’s.

 

Alternatively, we can also reinstall the support files  by running the SqlSupport.msi file located in the media for sql server 2005 setup.

 

The SqlSupport.msi is generally located in <cd-drive>:serversSetupSqlSupport.msi

 

After installing the new instance or reinstalling the Support files by running SqlSupport.msi

We need to install security patch 948109 on the original instance on which it was failing previously, the installation of the security patch will succeed.

 

Regeneration of the Master Key fails when we try to take the backup of the original database and restore it on other instance of sql server on some other server

 

We have a production database in which the data is encrypted. The encryption follows the following hierarchy

 

1) The data is encrypted using Symmetric key

2) The symmetric key was encrypted using asymmetric key

3) The asymmetric key was encrypted using the master key of the database

4) The master key of the database was encrypted using user defined password

 

 We created a MASTER KEY for the test database which encrypted using password

 

o    Use test

o    create master key encryption by password=’password’

 

·          We then created a asymmetric key which is encrypted by MASTER KEY of the database

 

o    Use test

o    open master key decryption by password=’password’

o    create asymmetric key MSSQL with algorithm = RSA_2048

o    go

 

·          In a test database with data, we then create a  symmetric key which  is used to encrypt the data

 

o    Use test

o    create symmetric key MSSQLSYM WITH ALGORITHM = AES_256 encryption by asymmetric key MSSQL.

o    go

 

 

·          We can check the presence of the keys

 

o    Select * from sys.symmetric_keys

o    Select * from sys.asymmetric_keys

 

·           We took the backup of the production database test and wanted to restore the database in the test environment.

 

·         However we wanted to change the password with which the master key is encrypted on the test environment so that password of the original database is not available to the developer who use the database in test environment.

 

·          We then restored the backup of the database test on other instance for the first time. By creating a new database and not overwriting on the existing database

 

·          When we restore the backup in the new instance we want to regenerate the master key of the database so we fire the following commands

 

o    open master key decryption by password=’password’

o    alter master key regenerate with encryption by password = ‘password’

o    close master key

 

 

·          However when we take a backup again from the original server and restore it on the new server by overwriting on the existing database when we fire the same set commands to regenerate the master key we received the following commands

 

o    open master key decryption by password=’password’

o    alter master key regenerate with encryption by password = ‘password’

 

o    Msg 15320, Level 16, State 2, Line 1

o    An error occurred while decrypting asymmetric key ‘MSSQL’ that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.

 

 

·          When we query sys.databases table in the source database and checked the column is_master_key_encrypted_by_server. We found that it was set 1 which means the Master key was encrypted using password along with the service master key of the sql server.

 

·          When we restored the database to the new server for the first time. When we checked the is_master_key_encrypted_by_server column from the sys.database we found the column value was 0 which indicates that MASTER KEY was encrypted only using password and not the SERVICE MASTER KEY of the sql server.

 

·          Therefore we regenerated the MASTER KEY it worked fine without any error. Also after regenerating the master key when we check the table sys.databases again we find that column is_master_key_encrypted_by_server is set to 1 which means that the regeneration encrypts the master key by password and the SERVICE MASTER KEY of the TEST sql server instance

 

·          However when we restore the database on the existing database by overwrting on it. The master key is encrypted using password and SERVICE MASTER KEY so the regeneration fails.

 

·         So in order to regenerate the master key of the database we first need to drop the encryption of the service master key using the following command

 

·          alter master key drop encryption by service master key

 

·          and after dropping the encryption of service master key explicitly when we regenerate the master key using the same command which is used by cx it works fine

 

·          alter master key regenerate with encryption by password = ‘password’

 

INSTALLATION OF SQL EXPRESS EDITION FAILS ON WINDOWS VISTA WHILE INSTALLING MICROSOFT OFFICE ACCOUNTING 2008

 

There have been a number of instances where we observed that the installation of the sql express edition on Windows Vista which also gets installed along with Microsoft Office Accounting 2008 fails which ultimately causes the installation of the Office Accounting 2008 on Windows Vista to fail.

So in order to successfully install Sql Express Edition on Windows Vista we need to take the following precautions before starting the installation so as to ensure that successful install of Sql Express On Windows Vista

 

1)   Disable User Access Control(UAC) Feature of the Windows Vista

            User Account Control (UAC) is a new security feature in Windows Vista that requires all users to log on and run in standard user privileges mode instead of as administrator with full administrative rights, thus prevent unauthorized or accidental changes that could destabilize the computers or allows virus and malware to exploit the system-level privileges provided to the local administrator to attack the network security, compromise computer safety and privacy, and damage files and settings in the network. However, in a lot of cases, administrator rights are needed by end-users to perform certain tasks such as install or update programs and perform typical system-level task. Beside, many software applications also need administrator privileges to run properly without conflicts, as they are designed to write to system locations during normal operation, and computer in locked-down state in which users operate in standard user mode severely limits user productivity.

 

·        In order to disable UAC we need to follow the following steps

·        Click Start and then open Control Panel.

·        In the Control Panel, click User Accounts and Family Safety.

·        Click User Accounts.

·        Click Turn User Account Control on or off.

·        Clear the tick or check mark on the box beside the Use User Account Control (UAC) to help protect your computer option.

·        Click OK.

·        When prompted, restart the computer. Note that the changes will affect all users on the computer.

·        To enable the UAC, simply tick or select the checkbox again.

 

2)   Disable all the Non-Microsoft Services

There are certain services which can prevent or hinder in the installation of the sql server on Windows system. Some of these services which are observed to cause problems while installation of the sql server are Antivirus, Malware and even Citrix Virtual Memory Optimization service. So in order to prevent any third party service from the installation of the sql server we would preferably disable all the Non-Microsoft services which can re enabled after the installation

In order to disable the Non-Microsoft services.

·        Go to Start->Run->type msconfig

·        Click on the services tab in the Dialog box

·        Click the check box Hide all Microsoft Services

·        Click On Disable ALL

·        Click On Ok

·        We now need to restart the system for the changes to take effect

 

 

 

3)   Check whether WMI repository is INCONSISTENT

If during the installation of the sql server on Windows vista fails with the following error

 

TITLE: Microsoft SQL Server 2005 Setup

——————————

 

SQL Server Setup Failed to compile the Managed Object Format (MOF) file c:Program FilesMicrosoft SQL Server90Sharedsqlmgmproviderxpsp2up.mof. To proceed, see “Troubleshooting an Installation of SQL Server 2005″ or “How to: View SQL Server 2005 Setup Log Files” in SQL Server 2005 Setup Help documentation.

 

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29513&EvtType=sqlca%5csqlmofca.cpp%40Do_sqlMofcomp%40Do_sqlMofcomp%40×7349

 

 The above error indicates that the WMI repository might be inconsistent so to resolve the error we need to fire the following command

 

C:UsersOwner>winmgmt /verifyrepository

WMI repository is INCONSISTENT

If the output shows that WMI repository is INCONSISTENT we need to fire the following command to salvage the repository

C:UsersOwner>winmgmt /salvagerepository

WMI repository salvage failed

Error code:     0x8007041B

Facility:       Win32

Description:    A stop control has been sent to a service that other running ser

vices are dependent on.

 

It may throw the following error when you fire the above command for the first time so run the command again as shown

C:UsersOwner>winmgmt /salvagerepository

WMI repository has been salvaged

 

Once the above precaution is being taken we can start with the installation of the sql server express and it should go fine.

If still the error occurs while installation we might have to troubleshoot further by looking at the setup bootstrap logs. But precaution can be helpful in the installation of the sql express edition on Windows Vista

 

 

 

 

HOW TO CHANGE THE DYNAMIC PORT OF THE SQL SERVER NAMED INSTANCE TO THE STATIC PORT IN A SQL SERVER 2005 CLUSTERED INSTANCE

 

 

In sql server the default instance has a listener which listens on the fixed port which is TCP port 1433. However for the named instance the port on which the sql server listens is random and is dynamically selected when the named instance of the sql server starts.
So when we connect to the default instance of the sql server the connections goes to the TCP port 1433 while when we connect to the named instance of the sql server the connections goes to the sql browser service which listens on the UDP port 1434. The sql browser service replies to the client with port no. on which the named instance of the sql server listens at that point of time and then the client is redirected and connects to the port no. provided by sql server service.
You may require to change the dynamic port of the named instance to the static port no. so to implement Firewall in your organization so that clients connect to the sql server through the firewall.
For Standalone instance of the sql server we can change the dynamic port of the named instance to the static port by using sql server configuration manager in sql 2005. In order to change the port we need to the sql server configuration manager.
Start->Program Files-> Microsoft sql server 2005->Configuration Tools->sql server configuration manager.

In configuration manager console, on the left hand panel, expand the sql server 2005 Network Configuration.

Click on the”Protocols for <Named-Instance-Name>”. On the right hand panel we need to right click and go to properties on the TCP/IP.

Click on the Ip Addresses tab on the top and keep the “Dynamic ports” row BLANK and write the desired port no. on which you want the named instance to listen on “TCP ports” row in the “IP ALL “section.

After performing these activities we need to restart the sql server services for the changes to take affects.

After restarting the service you can confirm that whether changes has taken affect by checking the following registry key
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server<Instance Name> MSSQLServerSuperSocketNetLibTcp
This key should contain the port no. defined by you.

However in case of the cluster, many a times (In particular when sql server 2005 32 bit is installed on the 64 bit system) it is observed that when we change the port no. of the named instance to the static port using the method described above the port no. again changes back to the dynamic port after you restart the services.

This is behavior can be explained as follows

When we changed the dynamic port of the sql server to the static port and stopped the sql server service the local copy of the registry which contains the dynamic port no. gets checkpointed to the quorum.

Now when the service is started the Checkpointed value from quorum are copied back to the registry and hence in spite of changing the value to static port. The value was replaced by the dynamic port from quorum.

So in order to change the dynamic port of the server to the static port in the clustered named instance of the sql server 2005 we need to follow the following steps

1) Take the sqlserver service offline from cluster administrator

2)  Disable the checkpointing to the quorum using the following command

Cluster res “SQL Server (TEST2005)” /removecheck: “SoftwareMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLSERVER”

3) Change the Dynamic port of the sql server to static port  on all the nodes using the method described above.

4) Enable the checkpointing to the quorum using the following command

cluster res ” SQL Server (TEST2005)” /addcheck: “SoftwareMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLSERVER”

5) Bring the sql server service online

In the above example “TEST2005” is the name of my sql server instance
                                          MSSQL.2  is since it is second sql server 2005 instance installed on the system

You will observe that the dynamic port is changed to the static port.

 

ERROR 29506 while Installing the SP2 on the sql server 2005

 

While installing SP2 on the sql server 2005, the installation may fail. Whenever the sql server installation fails it creates a log in the following location.
C:Program FilesMicrosoft Sql Server90Setup BootstrapLOG

The above location generally contains following folders and file.
1) Files                (Contains Logs for the installation of the RTM (Release to Market) version of the sql server)
2) HotFix             (Contains Logs for the installation of the Hotfixes and service packs)
3) Summary.txt   (Contains summary of all the sql server products which were installed and                               whether they  Successfully installed or Failed) 

                                  
These logs are created for any sql server 2005 setup installation which applies to the installation of the RTM version or any of the service packs and even all the editions of the sql server 2005 viz Standard Edition, Express Edition, Enterprise Edition.
Whenever the sql server 2005 setup fails we need to check the summary.txt located in the above location to get the information for cause of the failure and to know which products were unsable to install.
So during the installing of the sql server 2005 sp2 if the installation fails and when you look at the summary.txt, you may find that one of the cause of the failure is ERROR 29506.
The summary.txt may have the following information
———————————————————————————-
Product                   : Database Services (MSSQLSERVER)
Product Version (Previous): 1399
Product Version (Final)   :
Status                    : Failure
Log File                  : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number              : 29506
Error Description         : MSP Error: 29506  SQL Server Setup failed to modify security permissions on file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that  exists on the destination drive.

 

When you look into the file C:Program FilesMicrosoft SQL Server90Setup  BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log you will see the following error

 

Configuring ACL:
 Object: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData   ACL: (A;OICI;FA;;;[SQLServer2005MSSQLUser$CORPDBPRO$MSSQLSERVER])
Action: 0×103
Failed ACL:
  SetSecurityFileDescriptor is failed at the error code 5; Converted SDDL: ‘(A;OICI;FA;;;S-1-5-21-3714694515-4057322299-1779769068-1013)’
        Error Code: 0×80077342 (29506)
Windows Error Text:   Source File Name: sqlcasqlsddlca.cpp
Compiler Timestamp: Wed Jun 14 16:27:11 2006
     Function Name: ExceptionInSDDL
Source Line Number: 65

 

The main cause of the above error is that there is improper permissions of the DATA and LOG folder of the sql server 2005. In other words the DATA and LOG folders didn’t have Full Control permissions for the user Administrator and as a result the error message says
MSP Error: 29506  SQL Server Setup failed to modify security permissions on file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData for user Administrator

In order to avoid the error we need
1) To start the SP2 installation using an administrator or a user with administrative privileges.

2) All the files and the folder contained in the DATA and LOG must have Administrative privileges with FULL CONTROL permissions.
In order to achieve this right click on the DATA and LOG folder go to sharing and security. Click on the security tab and make sure Adminstrator is present and it has FULL CONTROL. Also go to the advanced tab and click on inherit the permissions to the child objects.
Finally confirm that all the folders and files located inside DATA and LOG folder have Full Administrator priviledge.
Once the permission issue is resolved we can restart with the installation and it will work fine

Reference
http://support.microsoft.com/kb/916766/en-us