• SQL Server Cast

    Running a Maintenance Plan Using dtexec.exe Command Prompt Utility Does Not Perform Any Action

    SQL Server Maintenance Plans bring an easy way to DBAs to quickly setup routine maintenance activities in the database. Starting SQL Server 2005 maintenance plans are created as SQL Server Integration Services packages and could be executed in the same way as any other SSIS package:

    • Using Business Intelligence Development Studio (BIDS)
    • Using the SQL Server Import and Export Wizard
    • Using SQL Server Agent jobs
    • Using the graphical tool DTExecUI
    • Using the command prompt tool dtExec.exe

    In the case I want to discuss now I was trying to run a maintenance plan package using the dtExec.exe utility. My goal was to execute a backup task using this maintenance plan and running this from command prompt did not show any error but did not perform any backup either. This was the syntax I was using and the results:

    C:\>dtexec /SQL "Maintenance Plans\Backup MP" /Server SQL2005\YUKON
    Microsoft (R) SQL Server Execute Package Utility
    Version 9.00.3042.00 for 32-bit
    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    Started: 15:21:20
    DTExec: The package execution returned DTSER_SUCCESS (0).
    Started: 15:21:20
    Finished: 15:21:51
    Elapsed: 31.046 seconds

    My first thought was, "do I need to have SSIS service running in order to execute this maintenance plan package"? but Microsoft Knowledge Base article KB942176 clearly states that SSIS service is not required for this:

    "If you only want to design and to execute SSIS packages, you do not have to start the SSIS service. When the SSIS service is stopped, you can run SSIS packages by using the following utilities:

      - The SQL Server Import and Export Wizard
      - The SSIS designer
      - The Execute Package utility (DTExecUI.exe)
      - The DTExec.exe command prompt utility"

    When you create a maintenance plan using SQL Server Management Studio you will see that the corresponding task or tasks associated with that maintenance plan are created as a SQL Server Agent job:

    image

     

    You can easily find what specific dtexec.exe parameters are provided on the maintenance plan just by looking into the SQL Server Agent job properties, on the "Command line" option for the subplan definition:

    image

     

    I checked that by running this sentence as the dtexec.exe parameter successfully run the backup job, in my case this is the command prompt instruction I used:

    DTEXEC.EXE /SQL "Maintenance Plans\Backup MP" /Server SQL2005\YUKON /SET "\Package\Subplan_1.Disable";false

    The "tricky" part here refers to the /SET switch. As you can find in the maintenance plan GUI each maintenance plan has one or more subplans associated and this/theses are disabled by default so you need to enable them by issuing the "false" option for the "Disable" property of this subplan. Remember that each subplan defined in the maintenance plan creates a job under SQL Server Agent.

    We can perform the same action right-into the SSIS package associated to the maintenance plan using SQL Server Business Intelligence Development Studio (BIDS):

      - Open BIDS
      - Create a new empty SSIS project
      - Add the existing maintenance plan package to the Project files list by using Solution Explorer; the maintenance plan will be added as a new SSIS Package
      - Modify the "Disable" property of the subplan so it is equal to "false"
      - Save the SSIS package as a maintenance plan in SQL Server
      - Run the maintenance plan without the need of specifying the /SET "\Package\Subplan_1.Disable";false switch

  • SQL Server Cast

    “The domain group cannot be validated for the service SQL Server” SQL Server 2005 cluster SP3 setup error

    Here again I am trying to shed some light on another SQL Server 2005 setup error that you may encounter during your Service Pack or Hotfix deployment.

    As you may know, one of the differences between installing a SQL Server instance on a standalone server vs. a cluster server is the service accounts required to run the different SQL Server services. While we are free to use local Windows accounts to run a non-clustered SQL Server instance, we are required to use a Windows Domain account to run a clustered SQL Server instance. This is better explained in the following KB article (KB910708, You must specify the domain groups for the clustered SQL Server services when you install a SQL Server failover cluster):

    When you install a stand-alone instance of SQL Server, the SQL Server Installation Wizard creates a set of local groups. The wizard also adds service accounts to the groups. When you install a SQL Server failover cluster, SQL Server requires domain accounts to start the services. The domain accounts must be added to a domain group.

    So it is not only important to have domain accounts in place to run the different SQL Server services but also to have a Domain group where these accounts are placed. It is also highly recommended to do not use this group to store other application’s accounts.

    In this case the domain group and accounts were already in place but the installation of SQL Server 2005 Service Pack 3 (SP3) was failing with the following error message on the Summary.txt setup log file:

    Product : Database Services (MSSQLSERVER)
    Product Version (Previous): 4035
    Product Version (Final) :
    Status : Failure
    Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log
    Error Number : 28130
    Error Description : MSP Error: 28130 A domain group is missing for one or more services. To install SQL Server 2005 as a failover cluster, domain groups must be specified for all the clustered services being installed .To proceed, enter the missing domain group information.
    The domain group cannot be validated for the service SQL Server.

    What was wrong then? To better understand this issue we need to take into account what information for these groups is stored on Windows registry. The setup program writes this information in the registry during the initial SQL Server installation, here you can find a description of these keys, found under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup:

    12Nov09A

    As you can see, there are three different registry keys called AGTGroup, FTSGroup and SQLGroup that store the SID information for the SQL Server Agent, Fulltext Search and SQL Server groups in Active Directory respectively. These entries are used by the SQL Server setup during subsequent Hotfix or Service Pack updates.

    In our case we could verify that these three registry keys were missing in both cluster nodes.

    Ok, so I just need to add the missing registry keys for the Domain group but, how can I get the SQL Server group SID? You can use different techniques to get this information but in my case I found getsid.exe utility to be extremely easy to use. You can get getsid.exe on the Windows Support Tools; after downloading the tool you just need to type the following instruction on any server or PC that belongs to the same domain:

    getsid.exe \\localhost CONTOSO\SQLGroup \\localhost CONSOTSO\SQLGroup

    Where CONTOSO refers to the Windows Domain name and SQLGroup refers to the group used to store the SQL Server service accounts. You should get an output similar to the following one:

    getsid \\localhost CONTOSO\SQLGROUP \\localhost CONTOSO\SQLGROUP
    The SID for account CONTOSO\SQLGROUP matches account CONTOSO\SQLGROUP
    The SID for account CONTOSO\SQLGROUP is S-1-5-21-3321254763-463695806-1538882281-2787112
    The SID for account CONTOSO\SQLGROUP is S-1-5-21-3321254763-463695806-1538882281-2787112

    Sysinternals PsGetSid will be also of help if you cannot make getsid running for some reason. After you get this information, you can add the missing registry keys into HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup as follows:

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]
    "AGTGroup"="S-1-5-21-2777104479-2157580501-946951792-1110"
    "SQLGroup"="S-1-5-21-2777104479-2157580501-946951792-1110"
    "FTSGroup"="S-1-5-21-2777104479-2157580501-946951792-1110"
    ...

    Important: You probably do not want to end up with your cluster down just because you incorrectly tampered the registry to fix a setup problem right? Please, take extra-ordinary caution when modifying the registry and always make sure you have a backup of it in place. For more information please, refer to KB310516.

    Note: If your server is a 64-bits server you do not need to take into account any registry below HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node.

    Another Note: The registry information for these keys is not replicated during cluster failover so you will need to add the same information on each one of the clustered nodes.

    Disclaimer Remainder: Please, be aware that according to Microsoft Knowledge Base article KB925976 the recommended workaround to fix this issue is to reinstall the clustered instance of SQL Server 2005. The information described here is provided with no warranty and it may not work on your environment.

  • SQL Server Cast

    “Failed to find a cluster group that owned shared disk” SQL Server 2005 setup error

    I think it could be interesting to share this SQL Server 2005 setup error since the message written in the setup logs was not clearly exposing the root-cause of the issue.

    In this case my customer was trying to update to Service Pack 3 (SP3) a SQL Server 2005 clustered instance. The error that we could see on the Hotfix.log SP3 setup log:

    10/20/2009 12:05:18.655 MSP Error: 29527 The setup has encountered an unexpected error in datastore. The action is RestoreSetupParams. The error is : Source File Name: datastore\cachedpropertycollection.cpp
    Compiler Timestamp: Mon Nov 17 17:05:55 2008
    Function Name: CachedPropertyCollection::findProperty
    Source Line Number: 130
    ----------------------------------------------------------
    Failed to find property "OwningGroup" {"VirtualServerInfo", "", "CONTOSOSQL"} in cache
    Source File Name: datastore\clusterinfocollector.cpp
    Compiler Timestamp: Mon Nov 17 17:05:55 2008
    Function Name: ClusterInfoCollector::collectClusterVSInfo
    Source Line Number: 888
    ----------------------------------------------------------
    Failed to detect VS info due to datastore exception.
    Source File Name: datastore\clustergroupsproperties.cpp
    Compiler Timestamp: Mon Nov 17 17:05:55 2008
    Function Name: ClusterGroupScope.SharedDisks
    Source Line Number: 56
    ----------------------------------------------------------
    Failed to fin
    10/20/2009 12:05:18.843 Attempting to continue the 32 bit ngen queue
    10/20/2009 12:05:18.905 Attempting to continue the 64 bit ngen queue

    Please, pay attention to the button of the log highlighted in blue color; as you can see the latest paragraph seems to be incomplete. You can find this situation with some error messages in setup log files and the best option it to look up the same error on the Application Event Log, where we will usually see the complete message; in our case this was the error:

    Failed to find property "OwningGroup" {"VirtualServerInfo", "", "CENSVXISQL"} in cache
    Source File Name: datastore\clusterinfocollector.cpp
    Compiler Timestamp: Mon Nov 17 17:05:55 2008
    Function Name: ClusterInfoCollector::collectClusterVSInfo
    Source Line Number: 888
    ----------------------------------------------------------
    Failed to detect VS info due to datastore exception.
    Source File Name: datastore\clustergroupsproperties.cpp
    Compiler Timestamp: Mon Nov 17 17:05:55 2008
    Function Name: ClusterGroupScope.SharedDisks
    Source Line Number: 56
    ----------------------------------------------------------
    Failed to find a cluster group that owned shared disk: D:

    Clearly, the next step was to check what was wrong with server’s D: drive. In our case this drive was a clustered shared disk, not a local one and it was part of the SQL Server 2005 cluster group. At first sight, everything was fine with this resource, but looking in more detail into the cluster configuration we could see what was wrong:

    Resource:
    ---------
    Disk D: { Physical Disk }
        +(1)-----Depends On-> SQL Server Fulltext  { Generic Service }

     

    For some reason, the SQL Server Fulltext Search service (FTS) was configured as a dependency for "Disk D:" resource. This is not correct, as you can verify on one of my previous post and in Microsoft Knowledge Base article KB835185, Failover cluster resource dependencies in SQL Server:

    The SQL IP address resource and the physical disk resources do not depend on any resources

    After eliminating this dependency on cluster resource "Disk D:" we were able to successfully install SQL Server 2005 SP3.

  • SQL Server Cast

    Lesson Learnt while Configuring Security on a Job Running through Linked Server

    • 1 Comments

    This case taught me a very useful lesson about how impersonation works when executing a SQL Server Job using a linked server. If this scenario is new for you too, I hope you enjoy learning as much as I did.

    In this case my customer was trying to run a stored procedure using a linked server from a local SQL Server 2005 instance to a remote SQL Server 2000. The stored procedure ran just fine when executed from SQL Server Management Studio (SSMS) but failed when ran as a SQL Server Agent Job.

    These kind of mistakes are common in SQL Server when the SQL Server Agent account does not have enough permissions to execute the required Job. For example, when running a T-SQL statement that performs some kind of action on a remote server’s share, the credentials used to logon on the remote share are the ones of the user that opens SSMS; in other words, the user we have used to logon in our Windows session. However, when the statement is executed within a SQL Server Job, the credentials being used are the ones for the SQL Server Agent account, which is the account in charge of running jobs. This can be found on the following link:

    […] if you create a job that requires a drive letter or a universal naming convention (UNC) path, the job may run under your Microsoft Windows NT® 4.0 or Windows® 2000 user account while testing it. When SQL Server Agent then runs the job, it may not have the necessary permissions, drive letter configurations, or access to the required drive to execute the job.

    There is additional information that needs to be taken into account when running a statement in SQL Server Management Studio vs. running the T-SQL code within a SQL Server Agent Job, this is described in Knowledge Base article KB269074:

    The security context in which the job is run is determined by the owner of the job […] If the job is owned by an account (either a SQL Server login or a Windows NT authenticated login) that is a member of the Sysadmin role, the SQL Agent job runs under the context of the account used to start the SQL Agent service.

    When the job is not owned by an account with sysadmin privileges, we can find a problem similar to the following one when running the job:

    Remote access not allowed for Windows NT user activated by SETUSER. [SQLSTATE 42000] (Error 7410). The step failed.

    There is a Knowledge Base article that explains how to workaround this error (see KB811031). In our case, the error was quite different though:

    Error: 7416, Severity: 16, State: 1
    Access to the remote server is denied because no login-mapping exists

    I did a basic review to understand what was the configuration of both the SQL Server Agent Job and Linked Server, in our case this was what we had:

    • Job owner was the sa SQL Server account
    • Job was made up of a single step with a simple stored procedure execution that took care of copying several rows from a remote table (2000) into a local table (2005)
    • A Windows Domain account was used to run SQL Server Agent service in both the SQL Server 2000 and 2005
    • Both servers was located on the same Windows Domain and the Agent service account was the same.
    • In both SQL Server 2000 and 2005 there was a SQL Server account called "elearning" associated to an "elearning" login. "elearning” login had “datareader" and "public" roles

    Concerning the linked server security configuration, my customer only had the "elearning" login on the security properties for the Linked Server, any other login attempt was denied:

    19102009B

     

    In this case we discovered that adding the SQL Server Agent account to the list of authorized users in the linked server security properties allowed us to run the job successfully. This makes sense because the SQL Server Account credentials are the ones used to execute the job in this case however, this lead to a security problem since a non-authorized user on the remote server could use the linked server connection to perform actions he has no rights to perform by default. In other words, having the SQL Server Agent account as authorized on the linked server allows the use of that linked server connection by any job where the owner is part of the sysadmin group.

    My first thought was to use a SQL Server Agent Proxy account to work around the issue but T-SQL statements cannot make use of Proxies:

    Job steps that execute Transact-SQL do not use SQL Server Agent proxies. Transact-SQL job steps run in the security context of the owner of the job.

    In our case the job owner was sa account so the Job was being run under the credentials of SQL Server Agent account however, the linked server only granted remote access to the "elearning" login. In addition to this, when configuring the advanced Job step properties using SSMS, the GUI does not allow to specify an alternate SQL Server login, only allows to specify an alternate SQL Server user:

    19102009A

    However, the EXECUTE AS documentation clearly states that using both SLQ Server user accounts and logins are a valid approach:

    SQL Server 2005 supports the ability to impersonate another principal either explicitly by using the stand-alone EXECUTE AS statement […]. The stand-alone EXECUTE AS statement can be used to impersonate server-level principals, or logins, by using the EXECUTE AS LOGIN statement. The stand-alone EXECUTE AS statement can also be used to impersonate database level principals, or users, by using the EXECUTE AS USER statement.

    The solution for the customer’s requirements was as easy as using the EXECUTE AS LOGIN instruction in the T-SQL code for the stored procedure:

    19102009C

    How the SQL Server Agent and linked server security work is not difficult once you make sense of all the pieces in the puzzle. Knowing where these pieces fit will help you to better asset your SQL Server’s security.

  • SQL Server Cast

    SQL Server 2005 Service Pack does not Update all the Installed Components

    Some weeks ago I received a call from one of our customers concerning the SP3 installation on SQL Server 2005. The customer was choosing to install the Service Pack from Microsoft Update (as you probably know, Microsoft Update is a superset of Windows Update that takes care of updating not only Windows OS components but other Microsoft software as Office or SQL Server) and the installation completed successfully as far as he can see however, Windows Update site was still reporting SP3 for SQL Server as a required update. No matter how many times he tried to install SP3, the installation from Microsoft Update always completed successfully but always offered SP3 as required afterwards.

    In our case the original SQL Server build was on SP2 (build 2005.90.3042) so it was clear that the error was not on the Microsoft Update detection engine. I asked the customer to download the SP3 from the Microsoft Download site and check what were the results in this case, installing the SP this way we have more control on each one of the steps performed by the setup. The customer reported that again the SP installation completed without errors but the SQL Server version was still build 3042.

    We looked into the Service Pack summary.txt installation file, located by default under the C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\Log directory, and found the following information:

    Products Detected Language Level Patch Level Platform Edition
    Setup Support Files ENU 9.3.4035 x86
    SQL Server Native Client ENU 9.00.4035.00 x86
    MSXML 6.0 Parser ENU x86
    SQLXML4 ENU 9.00.4035.00 x86
    Microsoft SQL Server VSS Writer ENU 9.00.4035.00 x86

    As you may have noticed there is a missing component on this list: Database Services. The SQL Server number that we can see in SQL Server Management Studio or by querying the built-in function @@version corresponds with the version of this component (the same version shown by the sqlservr.exe executable under the SQL Server program directory). The SP3 (build 4035) setup was therefore reporting a correct installation for all the shared SQL Server components, that were the only components detected in our case were.

    Running a Process Monitor while executing the SP3 setup process we discovered several ACCESS DENIED processes that I could not reproduce on my test environment, most of this events were related to the Wmiprvse.exe component. We decided to check the different WMI (Windows Management Instrumentation) repositories status with the help of WMI Diagnostics Utility WMIDiag; as expected, WMIDiag reported several errors with the WMI service configuration:

    34153 10:34:38 (0) ** WMIDiag v2.0 started on dinsdag 21 juli 2009 at 10:25.
    34154 10:34:38 (0) **
    34155 10:34:38 (0) ** Copyright (c) Microsoft Corporation. All rights reserved - January 2007.
    [...]
    35235 10:34:39 (0) ** DCOM security warning(s) detected: ........... 0.
    35236 10:34:39 (0) ** DCOM security error(s) detected: ............. 2.
    35237 10:34:39 (0) ** WMI security warning(s) detected: ............ 0.
    35238 10:34:39 (0) ** WMI security error(s) detected: .............. 52.
    35239 10:34:39 (0) **
    35240 10:34:39 (1) !! ERROR: Overall DCOM security status: ......... ERROR!
    35241 10:34:39 (1) !! ERROR: Overall WMI security status: .......... ERROR!
    [...]
    35401 10:34:39 (0) ** ERROR: WMIDiag detected issues that could prevent WMI to work properly!.  Check 'C:\DOCUMENTS AND SETTINGS\ADMINISTRATOR\LOCAL SETTINGS\TEMP\WMIDIAG-V2.0_2003_.SRV.RTM.32_NF04_2009.07.21_10.24.40.LOG' for details.
    35402 10:34:39 (0) **
    35403 10:34:39 (0) ** WMIDiag v2.0 ended on dinsdag 21 juli 2009 at 10:34 (W:181 E:57 S:1).

    At this point we were pretty sure the error was in the WMI service configuration. Thanks to the help of Carlos Carrolo from the Microsoft Platform Support Team we were able to trace down the problem to a known issue in SQL Server:

    KB941823, Some or all SQL Server 2005 services are not listed in SQL Server Configuration Manager, or you receive a "No SQL Server 2005 components were found" error message when you perform operations in SQL Server 2005 Surface Area Configuration

    The fact that none of the SQL Server service components was listed under the SQL Server Configuration Manager console confirmed our finding (this is described as one of the Symptoms on the KB).

    This Knowledge Base article did not mention our setup scenario, but the workaround provided worked in our case. This KB goes very specific explaining the root-cause of the issue: during the very beginning of the Service Pack setup process, the existing SQL Server services are discovered via WMI queries by the means of Wmiprvse.exe process; without the proper permissions, the process will fail to discover the installed components. This is further discussed at the end of this Knowledge Base article:

    […] These tools iterate through the services collection to obtain the information about the SQL Server 2005 services. When these tools iterate through the services collection, these tools generate the following Windows Management Instrumentation (WMI) queries:
    SELECT * FROM RegServices
    SELECT * FROM SqlService
    When these tools generate the WMI queries, the SQL Server Web-Based Enterprise Management (WBEM) provider (Sqlmgmprovider.dll) is loaded into the Wmiprvse.exe process. Then, the SQL Server WBEM provider extracts and processes the information about the services of every instance of SQL Server 2005. […] The Wmiprvse.exe process in which the Sqlmgmprovider.dll file is loaded runs under the security context of the NETWORK SERVICE account. […]

    It seemed that at some point after the SQL Server SP2 installation, the server was updated with a new Security Policy that broke the Discretionary Access Control List (DACL) for SQL Server leading to a broken WMI detection mechanism.

  • SQL Server Cast

    Open Windows Firewall Ports for SQL Server the Easy Way

    • 12 Comments

    After installing a new SQL Server 2008 instance on my Windows 7 laptop I find myself unable to logon locally into the instance by using SQL Server Management Studio (SSMS). I know that due to the changes introduced in Windows Server 2008 and Windows Vista Firewall this was possibly due to an open port missing.

    The Configuring the Windows Firewall to Allow SQL Server Access Books Online entry contains the information to open the required ports in Windows Firewall for each one of the SQL Server services. However, I recently discover a much easier way to open the required Windows Firewall ports for SQL Server on the following Knowledge Base Article (KB968872):

    25082009A

    This article includes the Microsoft "Fix it" troubleshooter that allows to quickly fix the related problem using an easy-to-follow setup. The "Fix it" button is designed for Windows Server 2008 but the script that is executed by the setup is included on the same KB article and can be executed on Windows Vista or Windows 7 as well:

    @echo =========  SQL Server Ports  ===================
    @echo Enabling SQLServer default instance port 1433
    netsh firewall set portopening TCP 1433 "SQLServer"
    @echo Enabling Dedicated Admin Connection port 1434
    netsh firewall set portopening TCP 1434 "SQL Admin Connection"
    @echo Enabling conventional SQL Server Service Broker port 4022 
    netsh firewall set portopening TCP 4022 "SQL Service Broker"
    @echo Enabling Transact-SQL Debugger/RPC port 135
    netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
    @echo =========  Analysis Services Ports  ==============
    @echo Enabling SSAS Default Instance port 2383
    netsh firewall set portopening TCP 2383 "Analysis Services"
    @echo Enabling SQL Server Browser Service port 2382
    netsh firewall set portopening TCP 2382 "SQL Browser"
    @echo =========  Misc Applications  ==============
    @echo Enabling HTTP port 80
    netsh firewall set portopening TCP 80 "HTTP"
    @echo Enabling SSL port 443
    netsh firewall set portopening TCP 443 "SSL"
    @echo Enabling port for SQL Server Browser Service's 'Browse' Button
    netsh firewall set portopening UDP 1434 "SQL Browser"
    @echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
    netsh firewall set multicastbroadcastresponse ENABLE

     

    Executing this on Windows 7 will show several warnings since the netsh firewall command in deprecated on this Windows version (netsh advfirewall firewall is the recommended method) but the script will perform its function nevertheless.

    Kudos for the Microsoft "Fix it" team!

  • SQL Server Cast

    Do Not Use Cluster Server Recovery Utility on 64-bits Windows Server

    You probably have heard about Cluster Server Recovery Utility before, a nice Windows utility that alleviate some of the burden associated with Cluster changes (restoring checkpoint files, changing disk signatures, storage migration, etc.).

    The Microsoft download page clearly states this utility is not suited for Windows Server 64-bits systems but I have recently found several SQL Server issues that are caused by the use of the utility on 64-bits SQL Server clustered installations.

    Using this tool on 64-bits SQL Server clustered installations could potentially lead to hard-to-fix and time-consuming troubleshooting scenarios. For this reason I think it is worth the time to highlight here the importance of not using this tool on 64-bits.

    What errors could I find when using this tool on 64-bits cluster?

    Some of the SQL Server errors that can result from the use of Cluster Server Recovery Utility under 64-bits are:

    SQLBrowser service was unable to establish SQL instance and connectivity discovery (Application Event Log)

    2009-05-13 08:11:13.04 Server Error: 17182, Severity: 16, State: 1.
    2009-05-13 08:11:13.04 Server TDSSNIClient initialization failed with error 0x2, status code 0x35.
    (SQL Server Errorlog)

    Please, keep in mind these errors could have a different root-cause, not necessarily Cluster Server Recovery.

    How do I know if I have used Cluster Server Recovery Utility on my 64-bits cluster?

    You will see that one ore more entries like this are created under the Wow6432Node registry entry:

    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL.x]

    Oh-oh, I think I have use this on my 64-bit Cluster, what should I do?

    Fixing these errors requires several registry changes and it is not a straightforward process. Please, contact Microsoft PSS for help on this case.

  • SQL Server Cast

    “SQLBrowser start request failed” Error Installing SQL Server 2008

    • 6 Comments

    I worked last day on an very tricky SQL Server 2008 setup problem and think it is worth the time sharing here what I discovered. In this case my customer was trying to install SQL Server 2008 on a Windows XP SP3 computer to support a locally installed ERP software package.

    The SQL Server 2008 setup was working fine at the beginning but failing in the middle of the process with the following error message:

    SQLBrowser start request failed. Click Retry to retry the failed action, or click Cancel to cancel this action and continue setup

    Clicking on the Retry button did retry the start operation but failed again with the same error so we had no other option but cancel the setup process.

    Checking the Windows System Event log did not provide any hint about the root-cause of the problem so I decide to check for the SQL Server 2008 setup log that can be found in C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log folder; there are different files included in this folder and each one of them provide information about each one of the components installed (SSIS, AS, Engine, Client Tools, etc.). The Detail.txt log file, as the name implies, includes very detailed information of the the overall setup process and it is usually the first thing I look into when dealing with setup issues; here is what we found in our case:

    2009-05-13 17:25:13 SQLBrowser: The last attempted operation: Starting the SQL Server Browser
    service 'SQLBrowser', and waiting for up to '900' seconds for the process to complete.
    2009-05-13 17:25:13 Slp: Prompting user if they want to retry this action due to the following failure:
    2009-05-13 17:25:13 Slp: --------------------------------------
    2009-05-13 17:25:13 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
    2009-05-13 17:25:13 Slp: Inner exceptions are being indented
    2009-05-13 17:25:13 Slp:
    2009-05-13 17:25:13 Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException 2009-05-13 17:25:13 Slp: Message:
    2009-05-13 17:25:13 Slp: Service ‘SQLBrowser’ start request failed.
    2009-05-13 17:25:13 Slp: Data:
    2009-05-13 17:25:13 Slp: Feature = SQL_Browser_Redist_SqlBrowser_Cpu32
    2009-05-13 17:25:13 Slp: Timing = Startup
    2009-05-13 17:25:13 Slp: DisableRetry = true

    So I could see that indeed the SQLBrowser service was being created but could not be started for some reason. I decided then to check the Summary.txt setup log file for a more comprehensive list of what components were and were not installed successfully; this file can be also located under the \LOG folder and contains a brief summary of the actions performed by the SQL Server setup. Interestingly enough, the error on this summary setup log file was not pointing to the Browser service but to MSXML 6.0 (Microsoft XML engine):

    Detailed results:
    Feature:                       Database Services
    Status:                        Failure                    
    MSI status:                    Failure
      MSI error code:                0x5EBE5729
      MSI log file location:         C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20090514_170659\Msxml6_Cpu32_1.log

    So I looked up into this setup log file (Msxml6_Cpu32_1.log):

    MSI (s) (30:58) [17:22:37:661]: Note: 1: 1708
    MSI (s) (30:58) [17:22:37:661]: Product: MSXML 6.0 Parser (KB933579) – Installation error.

    MSI (s) (30:58) [17:22:37:661]: Windows Installer installed the product. Product Name: MSXML 6.0 Parser (KB933579). Product Version: 6.10.1200.0. Product Language: 3082. Installation success or error status: 1603.

    MSI (s) (30:58) [17:22:37:661]: Cleaning up uninstalled install packages, if any exist
    MSI (s) (30:58) [17:22:37:661]: MainEngineThread is returning 1603
    MSI (s) (30:68) [17:22:37:770]: No System Restore sequence number for this installation.

    As you can see, SQL Server Browser was not the only component failing during the setup, the MSXML 6.0 engine was failing too. Comparing time from both Detail.txt and Msxml6_Cpu32_1.log files I could see the later was newer; in other words, the MSXML setup error was happening before the SQL Server Browser error. I went back to the Detail.txt file to confirm this point:

    2009-05-13 17:22:36 Slp: Running Action: Install_Msxml6_Cpu32_Action
    2009-05-13 17:22:36 Slp: Target package: "D:\x86\setup\x86\msxml6.msi"
    2009-05-13 17:22:37 Slp: InstallPackage: MsiInstallProduct returned the result code 1603.
    2009-05-13 17:22:38 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\20090514_170659\Registry_SOFTWARE_Microsoft_Microsoft SQL Server.reg_
    2009-05-13 17:22:38 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\20090514_170659\Registry_SOFTWARE_Microsoft_Windows_CurrentVersion_Uninstall.reg_
    2009-05-13 17:22:38 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\MSSQLServer to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20090514_170659\Registry_SOFTWARE_Microsoft_MSSQLServer.reg_
    2009-05-13 17:22:43 Slp:
    2009-05-13 17:22:43 Slp: Watson bucket for Msi based failure has been created
    2009-05-13 17:22:43 Slp: No retry-able MSI return code detected.
    2009-05-13 17:22:43 Slp: Checkpoint: INSTALL_MSXML6_CPU32_ACTION
    2009-05-13 17:22:43 Slp: Completed Action: Install_Msxml6_Cpu32_Action, returned False
    2009-05-13 17:22:43 Slp: Error: Action "Install_Msxml6_Cpu32_Action" failed during execution.

    Checking into Add/Remove Programs in Control Panel I could see the MSXML 6.0 engine listed as an installed. This is one of the shared component installed by SQL Server 2008 but in my case the customer was unsure if this component was previously installed on the Windows XP computer. I decided to remove the MSXML 6.0 engine from the PC using the Add/Remove Programs Control Panel applet and try the SQL Server 2008 setup again. This time the installation completed with no errors.

    In this case the initial SQL Server Browser error was misleading because it was the first error that was shown by the Setup GUI. Only by looking inside the SQL Server setup log files we were able to discover the MSXML 6.0 installation issue the took place before.

    Another possible way of tracing down this error is by looking into the Watson Bucket log file that exists on the same setup “Log” directory:

    In our case this was the content of this file:

    Watson bucket data:
      Bucket param 1: 10.0.1600.22
      Bucket param 2: 6.10.1200.0
      Bucket param 3: msxml6.msi
      Bucket param 4: 0x2D2816FE
      Bucket param 5: 0x5EBE5729
      Bucket param 6: Install_Msxml6_Cpu32_Action
      Bucket param 7:
      Bucket param 8:
      Bucket param 9:
      Bucket param 10:

    I have reviewed the SQL Server 2008 SP1 notes and this issue has been solved there so another way to avoid this problem would be by installing a slipstreamed SQL Server 2008 media, slipstreaming is a new SQL Server functionality that allows the database administrator to integrate Service Packs into the installation media.

  • SQL Server Cast

    How to Quickly Review your SQL Server Cluster Configuration on Windows Server 2003

    Working at Microsoft Customer Support Services (CSS), we are frequently asked by our customers to check their SQL Server cluster configuration.  Event though the task of installing and configuring a SQL Server cluster have been simplified every new Windows Server version, it is not strange to find clusters with non-optimal configurations. Part of this problem relies on the amount of settings the IT professional has to take into account when setting up a new cluster. Fortunately, Windows Server 2008 has introduced significant changes into the cluster setup, configuration and maintenance with the aim of simplifying most of the hard work for the IT professional.

    The goal of this post is to provide a quick guide for checking for some of the most common mistakes when configuring SQL Server on a Windows Server 2003 failover cluster. For this task we will use the CLUSMPS.EXE utility included in MPSReports.

    MPSReports to the Rescue

    Microsoft Product Support’s Reporting Tools (aka MPSReports) is probably one of the most popular tools among Microsoft Support professionals. This tool is used in most of the support incidents to gather SQL Server and general Windows logs and settings, including the failover cluster ones.  When executing MPSReports (for SQL Server you will have to download and run MPSRPT_SQL.exe executable file) we are actually executing a group of scripts and executables behind the scenes; one of these executables, CLUSMPS.EXE, will dump the SQL Server cluster configuration to a text file in the form of SERVERNAME_CLUSTER_MPS_INFORMATION.txt.

    The setup of MPSReports is pretty straightforward but is always recommended you check the ReadMe.txt file to have a better understanding of what test are performed and what log files are generated. You can find this ReadMe.txt here and additional information about this tool here.

     

    After running this tool we will find the cluster configuration file into the resultant CAB file in the MPSReports folder (default path for this folder is %WINDIR%\MPSReports\). We can optionally execute CLUSMPS.EXE directly by opening a Command Prompt windows on the active cluster node and running the tool from %WINDIR%\MPSReports\SQLServer\BIN\x86 path. The following picture describes the command-line options available:

     

    It is important to note that neither CLUSMPS.EXE nor the rest of the tests performed by MPSReports will change the Windows or SQL Server configuration in any way.

    General Cluster Configuration

    The first section of the resultant SERVERNAME_CLUSTER_MPS_INFORMATION.txt file provides a general overview of what nodes are part of the cluster and what cluster groups and resources are located on these nodes. This is an at-a-glance view of the information we can find by using Windows Cluster Administrator (cluadmin.exe) tool.

    This section includes Nodes Information, Group and Resource Information, which will be useful if you need to dig deep for cluster events into cluster.log, (see my previous post for more information on this) and Resource to Guid Information.

    Network Configuration

    The Network Configuration section includes Cluster Networks Information, Cluster Networks Priority, Network Interfaces, and Network Interfaces Binding Order. It is important to do not overlook these settings. In most of the cases not configuring network settings correctly will cause your SQL Server instance to do not perform optimally.

    Cluster Networks Information will show a list of the networks recognized by Microsoft Cluster Services (MCS). On a typical cluster configuration, each one of the cluster nodes will include at least two Network Interface Cards (NICs): One is used for internal cluster communications between nodes (commonly referred as "Heartbeat” network) and the other is used for external communications (“public” network).

    Pay attention to the Network Role column, by default you will see that both the Heartbeat and Public network are configured to support All Communications (“All Comm”) but it is a best practice to modify the Heartbeat configuration so it listens to Internal Communications only. To do so open the Heartbeat network properties on the Cluster Administrator utility (cluadmin.exe) and select the option Internal Cluster Communications Only (Private Network):

     

    Cluster Networks Priority shows the priority of each one of this networks. As a best practice, ensure that the private network is listed with the highest priority. This setting can be changed by accessing the cluster properties on Cluster Administrator utility:

     

    The Network Interfaces Binding Order determines how each network protocol is bound to each services that make use of it. As a general rule, the most-used protocols should be listed first. The recommended binding order for a Windows cluster is as follows:

            • External public network
            • Internal private network (Heartbeat)
            • Other connections, if any

    Network bindings can be configured using the Advanced > Advanced Settings option in Control Panel > Network and Dial-up Connections.

    The following Knowledge Base article provides specific guidelines to configure network components on a Windows cluster: Recommended private “Heartbeat” configuration on a cluster server, Server Clusters: Network Configuration Best Practices for Windows 2000 and Windows Server 2003.

    You can also find best practices to configure the different network components on a cluster on the following TechNet link: Network Configuration Best Practices for Windows 2000 and Windows Server 2003.

    Cluster Resources Dependencies

    On each one of the cluster groups, it is frequent to find resources that depend on others to function properly. These dependencies are depicted on the Dependency Tree List and Dependency List sections of the SERVERNAME_CLUSTER_MPS_INFORMATION.txt file. We usually do not need to take SQL Server cluster dependencies into account, these are automatically configured at setup time and should not be changed from defaults but it is worth the time to check our current dependencies tree against the recommended settings. You can find this information and other special considerations on Knowledge Base article KB835185.

    Here you can find default dependencies for the SQL Server 2000 failover cluster components:

    SQL Server (SHILOH) { SQL Server }
        +(1)-----Depends On-> SQL Network Name(SQL2000)  { Network Name }
            +(2)-----Depends On-> SQL IP Address1(SQL2000)  { IP Address }
        +(1)-----Depends On-> Disk S:  { Physical Disk }


    SQL Server Agent (SHILOH) { SQL Server Agent }
        +(1)-----Depends On-> SQL Server (SHILOH)  { SQL Server }
            +(2)-----Depends On-> SQL Network Name(SQL2000)  { Network Name }
                +(3)-----Depends On-> SQL IP Address1(SQL2000)  { IP Address }
            +(2)-----Depends On-> Disk S:  { Physical Disk }


    SQL Server Fulltext (SHILOH) { Microsoft Search Service Instance }
        +(1)-----Depends On-> SQL Server (SHILOH)  { SQL Server }
            +(2)-----Depends On-> SQL Network Name(SQL2000)  { Network Name }
                +(3)-----Depends On-> SQL IP Address1(SQL2000)  { IP Address }
            +(2)-----Depends On-> Disk S:  { Physical Disk }

    And here are the default dependencies for the SQL Server 2005 failover cluster components:

    SQL Server (YUKON) { SQL Server }
        +(1)-----Depends On-> Disk Y:  { Physical Disk }
        +(1)-----Depends On-> SQL Network Name (SQL2005)  { Network Name }
            +(2)-----Depends On-> SQL IP Address 1 (SQL2005)  { IP Address }


    SQL Server Agent (YUKON) { SQL Server Agent }
        +(1)-----Depends On-> SQL Server (YUKON)  { SQL Server }
            +(2)-----Depends On-> Disk Y:  { Physical Disk }
            +(2)-----Depends On-> SQL Network Name (SQL2005)  { Network Name }
                +(3)-----Depends On-> SQL IP Address 1 (SQL2005)  { IP Address }


    SQL Server Fulltext (YUKON) { Generic Service }
        +(1)-----Depends On-> Disk Y:  { Physical Disk }

    Cluster Service Account Rights

    The Account Privileges section includes a list of the different right required by the Microsoft Cluster Service account as well as any missing permissions. The following example illustrates an scenario where two of the permissions are missing for this account:

                  _____________________________________________
                 |/////////////////////////////////////////////|
                 |//         Current Effective Rights        //|
                 |/////////////////////////////////////////////|

    Act as part of the operating system.
    Back up files and directories.
    Restore files and directories.
    Adjust memory quotas for a process.
    Increase scheduling priority.
    Log on as Service.
    Debug programs.
    Manage auditing and security log.
    Access this computer from the network.

                  _____________________________________________
                 |/////////////////////////////////////////////|
                 |//     Missing Current Effective Rights    //|
                 |/////////////////////////////////////////////|

    Load and unload device drivers.
    Impersonate a client after authentication.

    The required permissions should be configured on each one of the cluster nodes. Failure in configuring proper rights for the Cluster Services account will cause you SQL Server cluster to show permissions error message or to fail completely during startup or failover operations. For a list of what permissions are required by this account in Windows Server 2003 please, refer to the Knowledge Base article KB269229.

    Other Information

    If you plan to make use of Distributed Transaction Services within SQL Server you should plan way ahead how to install the Microsoft Distributed Transaction Coordinator (MSDTC) cluster resource. The Knowledge Base article How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster is the best place to start. In case you need to revert or modify an existing MSDTC clustered installation so it can be used by SQL Server, refer to this link.

    For a more in-depth analysis of the general SQL Server cluster does and don’ts I highly recommended to check the following Microsoft Knowledge Base article: Clustered SQL Server do’s, don’ts, and basic warnings.

    The following TechNet link contains a quick reference guide to install and configuring new Microsoft cluster in Windows Server 2003: Quick Start Guide for Server Clusters. For specific SQL Server guidelines refer to SQL Server Books Online.

    During this post I have tried to summarize the key points to look for when checking SQL Server cluster configuration on Windows Server 2003. This is not a complete checklist and working over the different links provided in the post will give you a more complete information. The new Cluster Validation Wizard introduced in Windows Server 2008 cluster simplifies and reduced significantly the overhead associated with cluster configuration in previous Windows versions. If you haven’t had the opportunity to work with Server 2008 cluster, I encourage you to setup your lab and get familiar with this new cluster model.

  • SQL Server Cast

    Import Export Wizard is not Copy Database Wizard

    • 1 Comments

    During a recent work with one of our customers I did notice that it is not always clear what tools are available and required to copy SQL Server database objects, understanding “object” as a whole database or only part of it (table, view, user, etc.)

    As part of this work, my customer was trying to copy several databases between two different SQL Server instances but after several tests he was finding that although the tables were correctly copied, other objects as views and primary keys were not correctly transferred. It come to my attention that he was using the SQL Server Import Export Wizard to transfer the databases between one instance and another. Import Export Wizard can be launched from within SQL Server Management Studio (SSMS) or directly from a command prompt window typing DTSWizard.exe (further information about how to start Import Export Wizard can be found on this MSDN link)

     

    As indicated on the startup page on the Import Export Wizard, this tool can be used to copy data between different data sources and we have the option to recreate the destination database and tables, however this is not the right tool to use if we want to copy the whole database (not only tables, but all other objects) from one instance to another. The following Books Online link tries to clarify this difference:

    The purpose of the SQL Server Import and Export Wizard is to copy data from a source to a destination. The wizard can also create a destination database and destination tables for you. However, if you have to copy multiple databases or tables, or other kinds of database objects, you should use the Copy Database Wizard instead

    Copy Database Wizard will allow you to copy entire databases from one SQL Server instance into another (or the same, if you desire to do so) retaining the full database structure and objects. You can use two different methods to copy the databases and it is possible to keep the source database online while the copy is taking place, the following image shows these options:

     

    You can launch Copy Database Wizard directly from SSMS right-clicking on any database > Tasks > Copy database. You will see that this option (red) is side-by-side with the Import Export Wizard (green):

     

    Copy Database Wizard support the copy of databases between SQL Server 2000 and SQL Server 2005 providing an effective and simple way of upgrading databases between these two versions. For this wizard to work, SQL Server 2005 Service Pack 2 should be installed not only on the server where we are executing the tool but also on the destination server. For more information about this option please, refer to SQL Server Books Online.

    In summary, if you want to copy entire databases between different SQL Server instances, Copy Database Wizard is your tool. If you want to export data or import data from/into SQL Server or other data sources or just want to copy some tables, use Import Export Wizard. If you need to copy data and perform modifications during this process with a fine-grain control you will find a better solution by building and Integration Services package in Business Intelligence Development Studio.

Page 2 of 3 (30 items) 123