SQL Server 2008 Install – Access Denied

Install being attempted on Windows Server 2008 R2 SP1.

The user installing the RDBMS is an admin so should have all permissions.

2012-03-22 14:29:25 SQLEngine: --SqlServerServiceSCM: Waiting for nt event 'Global\sqlserverRecComplete$DBMON' to be created
2012-03-22 14:29:30 SQLEngine: --SqlServerServiceSCM: Waiting for nt event 'Global\sqlserverRecComplete$DBMON' or sql process handle to be signaled
2012-03-22 14:29:30 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
2012-03-22 14:29:30 Slp: Access is denied
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\Microsoft SQL Server to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Microsoft_Microsoft SQL Server.reg_
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Microsoft_Windows_CurrentVersion_Uninstall.reg_
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\MSSQLServer to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Microsoft_MSSQLServer.reg_
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Wow6432Node_Microsoft_Microsoft SQL Server.reg_
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Wow6432Node_Microsoft_Windows_CurrentVersion_Uninstall.reg_
2012-03-22 14:29:31 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Wow6432Node\Microsoft\MSSQLServer to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120322_142338\Registry_SOFTWARE_Wow6432Node_Microsoft_MSSQLServer.reg_
2012-03-22 14:29:32 Slp: Access is denied

The above errors are taken from the setup bootstrap. What you end up with is half an installation. The RDBMS does get installed but it starts up in Single User, Some permissions are missing, the sa password is blank. The Service accounts don’t get the permissions they need. The Agent wont start for this reason.

Attempt 1 – Remove UAC restrictions on User installing SQL Server. Reboot and Retry. No luck.

Attempt 2 – Add service accounts into Admin group. Not sure why i tried this apart from desperation to get it working. the service accounts normally would earn their permissions during the install as they would get added into the appropriate SQL Server permisson groups. Reboot Server and retry. No Luck

Solution – This failure often is caused by a system or domain policy removing the SeDebugPrivelege security privilege from the administrator account running setup. Verify that the account running has this privilege.

Open Group Policy…

Start | Run | gpedit.msc | OK |

Navigate to: Computer Configuration | Windows Settings | Security Settings | Local Policies | User Rights Assignment | Debug programs

The account through which we are trying to run the setup should be here ( besides the local admin on that machine). I included that here, restarted the server ( this is mandatory, gpupdate /force will not work) and ran the setup and it was successful this time.

NOTE: Rebooting the Server is critical in applying the permission

SQL Server 2008 setup needs this privilege to start up the SQL Server process and listen to an event that signals back to setup that SQL Server successfully started.

Debug Programs User Rights – Debug Programs is used to send signals back and forth between the OS and the SQL installation.

More info on error here http://connect.microsoft.com/SQLServer/feedback/details/390424/sql-server-2008-setup-fails-if-debug-programs-rights-are-missing

Checking Group Policy –

The AccessChk tool (http://technet.microsoft.com/en-us/sysinternals/bb664922) will print all privleges for an account by running:

accesschk.exe -a <Domain>\<Account> *

Dropping Database – Delete Backup History taking a long time

Ive found this on numerous occasions and decided not to be lazy this time around and investigate why it takes so long to drop a database when the ‘Delete backup and Restore History Information for databases’ checkbox is ticked.

When thinking about it logically, when purging historical data with a where criteria against a set of tables in any database, the task would be faster when you have indexes in place. this is exactly the scenario which causes the GUI to seem like its hanging. However, if you check sys.dm_exec_requests you will be able to see CPU and IO actively moving and a DELETE command against the msdb, in the form of:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyDB'

Running the following will show you which indexes are present on the msdb database as standard:

SELECT t.name as [Table], ind.name as [Index], col.name as [Column], ind.type_desc [Index Type]
FROM sys.indexes ind
inner join sys.index_columns ic
    on ind.object_id = ic. object_id and ind.index_id = ic. index_id
inner join sys.columns col
    on ic.object_id = col.object_id and ic.column_id = col.column_id
inner join sys.tables t
    on ind.object_id = t. object_id WHERE ind.is_unique = 0 and ind. is_unique_constraint = 0
    and t.name in ( 'backupfile', 'backupfilegroup', 'backupmediafamily', 'backupmediaset', 'backupset', 'restorefile', 'restorefilegroup', 'restorehistory' ) ORDER BY t .name, ind.name

…Only 4 indexes are present as standard in the msdb database.

This problem can be alleviated by adding a set of indexes to some key tables in the msdb database.

This script is taken from Here

/************************************************************************ * * * Title: msdb index creation * * Author: Geoff N. Hiten * * Purpose: Index msdb database * * Date: 12/12/2005 * * Modifications: * ************************************************************************/ use msdb
go

--backupset Create index IX_backupset_backup_set_id on backupset(backup_set_id) go
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) go
Create index IX_backupset_media_set_id on backupset(media_set_id) go
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date) go
Create index IX_backupset_backup_start_date on backupset(backup_start_date) go

--backupmediaset Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) go

--backupfile Create index IX_backupfile_backup_set_id on backupfile(backup_set_id) go

--backupmediafamily Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) go

--restorehistory Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) go
Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) go

--restorefile Create index IX_restorefile_restore_history_id on restorefile(restore_history_id) go

--restorefilegroup Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id) go

This small addition will speed up the deletion process completely and make sure the GUI doesnt hang waiting for a background delete against the msdb to complete.

Useful scripts

I found that deleting backup history was slowing down, so i did the following:

USE msdb; GO
UPDATE STATISTICS backupfile; GO
UPDATE STATISTICS backupmediafamily; GO
UPDATE STATISTICS backupmediaset; GO
UPDATE STATISTICS backupset; GO
UPDATE STATISTICS restorefile; GO
UPDATE STATISTICS restorefilegroup; GO
UPDATE STATISTICS restorehistory; GO

And also added the following index, after looking at the estimated execution plan:

Create index IX_backupset_database_name on backupset(database_name) go

Some general queries:

--Find count of records older than certain date select count(*) from backupset with (nolock) where backup_start_date < '8/15/2009' --with database name select * from backupset with (nolock) where backup_start_date < '08/15/2011' and database_name='MyDB' --Find databases which have history data but no longer attached to instance select distinct(database_name) from backupset with (nolock) where database_name not in (select name from sys.databases) --Purge history older than certain date sp_delete_backuphistory @oldest_date='08/15/2009' --Purge history for a particular DB EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'myTest'