SQL Server Q&A

Symptoms
When you try to add a configuration to a Microsoft SQL Server 2005 Integration Services .dtsx package, you receive the following error message:

An error occurred while a new configuration was being added.
Additional information
The path is not of a legal form. (mscorlib)
Resolution
This problem occurs because the .dtsx package is not associated with a business intelligence solution.

Symptoms
When you adjust the maximum worker threads configuration by using code similar to the following:

sp_configure ‘max worker threads’ The run value is updated immediately and you do not have to stop and restart the SQL Server service. However, the User Mode Scheduler picks up the newly configured max worker threads only after you stop and then restart the SQL Server service.
Resolution
Stop and restart SQL Server so that the new configuration settings can take effect.

Symptoms
After you install a 64-bit version of Microsoft SQL Server 2005 on a 64-bit version of a Microsoft Windows operating system, the 32-bit entries for SQL Native Client Configuration and for SQL Server 2005 Network Configuration appear in SQL Server Configuration Manager.
Resolution
Some tools that are installed together with a 64-bit version of SQL Server 2005 are available only as 32-bit versions. When you install a 64-bit version of SQL Server 2005, the 32-bit versions of the following tools are installed:SQL Server Management StudioSQL Server Configuration ManagerDatabase Engine Tuning AdvisorThe following entries appear in SQL Server Configuration Manager:SQL Server 2005 Network Configuration (32bit)SQL Native Client Configuration (32bit)SQL Server 2005 Network ConfigurationSQL Native Client ConfigurationThe following table contains information about each entry.
Note Client Network Utility (Cliconfg.exe) is available in SQL Server 2000.In SQL Server 2005, the functionality of this tool is included in SQL Server Configuration Manager. However, to address backward compatibility issues, Client Network Utility is included in SQL Server 2005.
Collapse this tableExpand this table
Entry in SQL Server Configuration ManagerVersionResidence of aliases that are created in SQL Server Configuration ManagerCorresponding subkey in the registryApplications that are affected by the protocol settingsAvailability of aliases to applicationsResidence of aliases that are created by Client Network UtilitySQL Native Client Configuration (32bit)32-bitAliases that are created in this entry reside only in this entryHKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectToNot applicableOnly 32-bit applications can use aliases in this entryNot applicableSQL Native Client Configuration64-bitAliases that are created in this entry reside only in this entryHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectToNot applicableOnly 64-bit applications can use aliases in this entryResides only in this entrySQL Server 2005 Network Configuration (32bit)32-bitNot applicableHKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNI9.0Only 32-bit applications are affectedNot applicableNot applicableSQL Server 2005 Network Configuration64-bitNot applicableHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0Only 64-bit applications are affectedNot applicableNot applicable

Symptoms
Consider the following scenario:You have a computer whose System locale is configured as Arabic and User locale is set to English or a different language.On this computer you try to install a named instance of SQL Server 2008 and the instance name contains Arabic characters.
In this scenario, the setup program fails with the following error message, though the name conforms with the guidelines documented in Books Online.
The following error has occurred:
The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.0.1600.22&EvtType=0×429FDFA3%25400×0A2FBD17%25401211%25401
The Detail.txt setup log will have an error message similar to the following:
2009-02-28 19:03:56 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigNonRC and scenario ConfigNonRC.
2009-02-28 19:03:57 Slp: Microsoft.SqlServer.Configuration.Sco.ScoException: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.
2009-02-28 19:03:57 Slp:    at Microsoft.SqlServer.Configuration.Sco.WmiProvider.InstallMof()
2009-02-28 19:03:57 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.MofRegistration.RegisterMof()
2009-02-28 19:03:57 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.CreateWMIProvider(EffectiveProperties properties)
2009-02-28 19:03:57 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.Install_ConfigNonRC_Prepare(EffectiveProperties properties)
2009-02-28 19:03:57 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.Install(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
2009-02-28 19:03:57 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.PrivateConfigurationBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcbCurrent)
2009-02-28 19:03:57 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SqlFeatureConfigBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcbCurrent)
2009-02-28 19:03:57 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
2009-02-28 19:03:57 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
2009-02-28 19:03:57 Slp: Exception: Microsoft.SqlServer.Configuration.Sco.ScoException.
Additionally an event similar to the following gets logged to the Windows Application event log:
The SQL Server (<Arabic instance name>) service terminated with service-specific error 17058 (0×42A2).
 
The file ‘\\?\C:\Program Files\Microsoft SQL Server\MSAS10.<Arabic instance name>\OLAP\Data\CryptKey.bin’ could not be opened. Please check the file for permissions or see if other applications locked it.
 
Failed to initialize SQLSQM timer. One of the following can be the source of the problem: registry doesn’t contain all necessary information, server instance name can not be retrieved correctly, failure during timer creation, etc.
 
.NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) – Failed to compile: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\Tools\VDT\DataProjects.dll . Error code = 0×8007000b
 
.NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) – Failed to compile: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\Tools\VDT\DataDesigners.dll . Error code = 0×8007000b
 Note: The same issue also exists in the CTP versions of SQL Server 2008 R2.
Resolution
 Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Symptoms
Consider the following scenario:
As part of tightening security, you remove some of the default user rights to local administrators group on a Windows operating system. In prepartion for setting up SQL server on this system, you add setup account to the local administrators group.
In this scenario, if you either install or upgrade to SQL Server 2008,  the installation process may fail with various error messages as noted below.
Scenario 1: For a new installation, the setup program will fail with the following error message
Access is denied
Additionally, you may notice error messages similar to the following in the Detail.txt file
2009-01-02 13:00:17 SQLEngine: –SqlServerServiceSCM: Waiting for nt event ‘Global\sqlserverRecComplete$NIIT’ to be created
2009-01-02 13:00:20 SQLEngine: –SqlServerServiceSCM: Waiting for nt event ‘Global\sqlserverRecComplete$NIIT’ or sql process handle to be signaled
2009-01-02 13:00:20 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
2009-01-02 13:00:20 Slp: Access is denied
2009-01-02 13:00:20 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
2009-01-02 13:00:20 Slp: System.ComponentModel.Win32Exception: Access is denied
2009-01-02 13:00:20 Slp:    at System.Diagnostics.ProcessManager.OpenProcess(Int32 processId, Int32 access, Boolean throwIfExited)
2009-01-02 13:00:20 Slp:    at System.Diagnostics.Process.GetProcessHandle(Int32 access, Boolean throwIfExited)
2009-01-02 13:00:20 Slp:    at System.Diagnostics.Process.OpenProcessHandle()
2009-01-02 13:00:20 Slp:    at System.Diagnostics.Process.get_Handle()
2009-01-02 13:00:20 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceBase.WaitSqlServerStart(Process processSql)
2009-01-02 13:00:20 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceSCM.StartSqlServer(String[] parameters)
2009-01-02 13:00:20 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerStartup.StartSQLServerForInstall(String sqlCollation, String masterFullPath, Boolean isConfiguringTemplateDBs)
2009-01-02 13:00:20 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.ConfigSQLServerSystemDatabases(EffectiveProperties properties, Boolean isConfiguringTemplateDBs, Boolean useInstallInputs)
2009-01-02 13:00:20 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.DoCommonDBStartConfig(ConfigActionTiming timing)
2009-01-02 13:00:20 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.Install(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
2009-01-02 13:00:20 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.PrivateConfigurationBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcbCurrent)
2009-01-02 13:00:20 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SqlFeatureConfigBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcbCurrent)
2009-01-02 13:00:20 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
2009-01-02 13:00:20 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
2009-01-02 13:00:20 Slp: Exception: System.ComponentModel.Win32Exception.
2009-01-02 13:00:20 Slp: Source: System.
2009-01-02 13:00:20 Slp: Message: Access is denied.

Scenario 2: Upgrades to SQL Server 2008 will report the following error message on Engine_SqlEngineHealthCheck  rule:
Rule name:Engine_SqlEngineHealthCheck
Rule description: Checks whether the SQL Server service can be restarted; or for a clustered instance, whether the SQL Server resource is online.
Result: Failed
Message/Corrective Action: The SQL Server service cannot be restarted; or for a clustered instance, the SQL Server resource is not online
Additionally, you may notice error messages similar to the following in the Detail.txt file
2009-05-27 17:50:20 SQLEngine: : Checking Engine checkpoint ‘GetSqlServerProcessHandle_1′
2009-05-27 17:50:20 SQLEngine: –SqlServerServiceSCM: Waiting for nt event ‘Global\sqlserverRecComplete$SQL10′ to be created
2009-05-27 17:50:22 SQLEngine: –SqlServerServiceSCM: Waiting for nt event ‘Global\sqlserverRecComplete$SQL10′ or sql process handle to be signaled
2009-05-27 17:50:22 SQLEngine: –FacetSqlEngineHealthCheck: Engine_SqlEngineHealthCheck: Error: Access is denied

Resolution
This behavior is by design. In addition to adding the user account that is running the setup as a local administrator as documented in How to: Install SQL Server 2008 (Setup), the setup user account requires the following additional rights for the setup to complete successfully.
 
Local Policy Object Display Name
User Right
Backup files and directories
SeBackupPrivilege
Debug Programs
SeDebugPrivilege
Manage auditing and security log
SeSecurityPrivilege
 

Symptoms
This article describes behavior of the SQL Configuration Manager when it is running on a Windows Server 2008-based domain controller and you change a Microsoft SQL Server 2008 engine service account to either a Network Service or a Local Service.
Note The Network Service account and the Local Service account are not supported service accounts for the engine service on a domain controller.
Resolution
When you change theSQL Server 2008 engine service account to either a Network Service account or a Local Service account, the SQL Configuration Manager that is running on a Windows Server 2008-based domain controller does not restrict the change.
You cannot use the built-in accounts as SQL Server service accounts on a domain controller. Typically, when you try to add the built-in accounts to a service account group by using the SQL Configuration Manager, you receive an error message. The SQL Configuration Manager does not specifically check this scenario.
On Windows Server 2008 domain controller, the Service security ID (SID) is enabled for the SQL Server service. Therefore, the service account is not added to the service account group. Additionally, the SQL Configuration Manager does not return an error message.