SQL Server Q&A

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
Consider the following two scenarios:
Scenario 1:You install SQL Server 2005 failover cluster on a Windows Server 2008 or Windows Server 2008 R2 failover cluster using the default configuration.On the same system you install a new instance of SQL Server 2008 failover cluster.
Scenario 2: You install two or more instances of SQL Server 2005 failover cluster on a Windows Server 2008 or Windows Server 2008 R2 failover cluster using the default configuration.You upgrade one of them to SQL Server 2008.
In either of these scenarios, you will notice that the SQL Server resource of  all the SQL Server 2005 instances that are currently active on the node where the SQL Server 2008 setup actions are performed, enters a failed state in the Failover Cluster Manager. 
Note: In the above statements, the phrase ‘default configuration’ implies that you install the SQL Server 2005 failover cluster using the default options during the setup process and did not make any changes to the failover action of the SQL Server resource after the setup has been complete. Also any stand alone (non-clustered) instances running on the node are not affected by this problem.
Resolution
The default configuration of SQL Server 2005 failover cluster does not set the following policy on SQL Server resources:
“If resource fails, attempt restart on current node.”
All the instances of SQL server running on a node will always share the highest version of SQL cluster resource dll present on that node. When installing or upgrading to SQL Server 2008 for the first time, the setup process replaces the existing version of SQL cluster resource dll with a newer and a higher version. As part of this procedure, it shuts down SQL Server resources to avoid a system reboot at the end of setup. Hence, if the policy above is not set, the cluster will not attempt to restart the failed SQL Server resources.

Symptoms
Web applications running on IIS 7.5 and that rely on SQL Server Express user instancing will fail to run using the default IIS 7.5 security configuration on both Windows 7 Client and Windows Server 2008 R2. Developers will encounter problems developing web applications using Visual Studio 2005 + SQL Server Express 2005, Visual Studio 2008 + SQL Server Express 2008, or Visual Studio 2010 + SQL Server Express 2008 on both Windows 7 Client and Windows Server 2008 R2.
Developers will encounter similar problems attempting to develop web application projects (WAP) or websites hosted under IIS6/IIS7/IIS7.5 that rely on SQL Server Express user instances where the WAP project structure or website folder structure exists in a user’s Documents folder.  This issue exists for all versions of Visual Studio regardless of the underlying operating system version.  A web application that attempts to create a database or read/write to a database using SQL Server Express user instance mode can encounter any of the following errors:
An attempt to attach an auto-named database for file c:\Users\[YourUserAccountName]\Documents\Visual Studio 20XX\Projects\[YourSolutionName]\[YourProjectnName]\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
–or–
Failed to generate a user instance of SQL Server due to failure in retrieving the user’s local application data path.  Please make sure the user has a local user profile on the computer. The connection will be closed.
 
NOTE: A web application relies on SQL Server Express’ user instance mode if either of the following is true: The application relies on the default “LocalSQLServer” connection string defined in machine.config The application uses a connection string that contains the following attributes:
            “AttachDBFilename=|DataDirectory|xxxxxx.mdf;User Instance=true”
 
 
Resolution
For Windows Server 2008 R2 and Windows 7
The default security configuration for IIS 7.5 sets application pools to run as the “application pool identity”.  Running an application pool using this special identity was first introduced as an optional setting in Vista SP2 and Windows Server 2008 SP2.  On Windows 7 Client and Windows Server 2008 R2 this special identity is now the default.
 
Web applications built with Visual Studio 2005, Visual Studio 2008, or Visual Studio 2010 and that rely on user instancing with either SQL Server Express 2005 or SQL Server Express 2008 do not work with the new application pool identity.  These products were developed and tested against application pools running with the older NETWORK SERVICE account.
 
For Web Application Projects and Websites Located in a User’s Documents Folder Hosted in IIS
Web application projects (WAP) exist in a folder structure under a user’s “Documents\Visual Studio 20XX\Projects” folder.  Website projects exist in a folder structure under a user’s “Documents\Visual Studio 20XX\Websites” folder.  SQL Server Express user instances require file access rights to the parent folders of the website or WAP project’s directory structure.  Because the IIS service account (NETWORK SERVICE) by default does not have these rights within the Visual Studio project folder structure, WAP projects and websites located in a user’s Documents folder and that are hosted in IIS will not be able to open SQL Server Express user instanced databases for read access.
 
WAPs that were originally created within a user’s Documents folder, but were subsequently changed to use IIS for a web server via the web tab of the project’s properties will encounter this file permissions problem.  Websites hosted in IIS where the website directory structure is located within a user’s Documents folder will also encounter the file permissions problem.  This behavior occurs for WAP projects and websites hosted with any IIS versions that run as NETWORK SERVICE (IIS6, IIS7 and IIS 7.5) where the project structure exists within a user’s Documents folder.
 

Symptoms
Consider the following scenario:
You move msdb database from one instance to another either using the backup and restore process or by copying over the database files (mdf and ldf). Then, on the destination server, a user who is not part of Sysadmin fixed role in SQL Server tries to do either of the following operations:View a job in SQL Server management studioCall SQL agent related stored procedures (for example xp_sqlagent_enum_jobs or sp_get_composite_job_info) directly using T-SQL.
In this scenario, the user will get anerror message similar to the following:
 An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
 
The EXECUTE permission was denied on the object ‘xp_sqlagent_enum_jobs’, database ‘mssqlsystemresource’, schema ’sys’. (Microsoft SQL Server, Error: 229)
 
Resolution
This issue occurs because the SQL Agent certificate is different on different instances.

Symptoms
Consider the following scenario:You install Microsoft SQL Server 2005 or 2008 on a Windows Server 2003 system. You apply the security template “System Cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing” on that system.
In this scenario, if you try to run the Database mirroring Wizard for SQL Server, you will get the following error message
 SQL Server cannot create the mirroring endpoint, ‘Mirroring’.
Additional information:
Create failed for Endpoint ‘Mirroring’. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The RC4 encryption algorithm is not supported when running in FIPS compliance mode.
(Microsoft SQL Server, Error: 28078)
Note: The “System Cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing” can be enabled in the SSLF security template provided by Windows Server 2003 security guide.
Resolution
To resolve this, you must note the ports that the principal, mirror, and witness are using.  Then you have to manually create the endpoints on each of the instances specifying the appropriate port for the appropriate role.
For  example, if you are configuring your Principal server on port 5022, you can run the following command on the principal server.
Create ENDPOINT Mirroring  
State = Started
as TCP (LISTENER_PORT = 5022) 
–Uses port 5022.You would change this as appropriate for each role (Principal, Mirror, or Witness)
FOR DATABASE_MIRRORING (AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE=ALL)
 
Note: You must repeat this command on each of the servers involved in the mirroring configuration.