locked
SQL Server Permissions, restoration after computer crash RRS feed

  • Question

  • So,

    A week ago my system's hard drive suffered a rather tedious hard drive failure, which I was able to resolve but has created some other issues after the fact.  The Hard drive developed a fault, and in order to restore my system, I was forced to use BartPE as a live cd, and transfer the data from the "defective" drive to the new drive.  for some reason the defect caused a fault which prevented most Imaging software from being able to image the drive over so I was forced to use XCOPY.

    Yes, this actually worked, and as I am writing this post, it is my exact same system that I used last week, with the minor problem of having to reactivate windows when I finally was finished.

    However, there apparently is a minor issue with the NTFS security descriptors.  I am not surprised, since I was using the BartPE loaded windows system to transfer the files I expected a little "renaming" of security descriptors to occur in the process.  But when faced with the prospect of rebuilding the entire system from scratch, this was a reasonable cost.  However, this tragically confused Sql Server. 

    As we know SQL Server Installs Several Groups into the User And Groups of the system:

    • SQLServer2005SQLBrowserUser$COMPUTERNAME
    • SQLServerFDHostUser$COMPUTERNAME$SQLEXPRESS
    • SQLServerMSSQLServerADHelperUser$COMPUTERNAME
    • SQLServerMSSQLUser$COMPUTERNAME$SQLEXPRESS
    • SQLServerReportServerUser$COMPUTERNAME$MSRS10.SQLEXPRESS
    • SQLServerSQLAgentUser$COMPUTERNAME$SQLEXPRESS

    As for the Members of these groups I am not entirely versed, however I do know that I primarily set up my Server / Agent to utilize the NETWORK SERVICE account, where the Reporting Services, and Browser are on the Local Service account. 

    Attempting to "Start" the "SQL Server(SQLEXPRESS)" service yields the following error: 

    Source: MSSQL$SQLEXPRESS
    Event ID: 17058
    Description:initerrlog: Could not open error log file 'C:\SQL_DB\MSSQL\Log\ERRORLOG'. Operating system error = 5(Access is denied.).

    Now, my DATA Path is C:\SQL_DB\MSSQL, which is all of the master, temp, model and other system databases, and I use subdirectory structures underneath that path for the actual user/application databases.  ..\MSSQL\LOG is of course my LOG path used in similar design as the DATA path.

    However, my security descriptor for the SQL_DB folder and inherited by all its children is:

    • Administrators : Full
    • CREATOR OWNER : Special
    • SYSTEM: Full
    • Users : Read/Special (create)

    Upon looking at this I am checked the "Effective" permissions, and where the SYSTEM account did have effective permissions of ALL possible, NETWORK SERVICE had none.  Which I'm expecting to be the problem. 

    SO My question is: What is the default setup of Security Descriptors on the primary SQL Data Folder:

    1. Should I just Add Network Service & Local Service to the C:\SQL_DB folder and let the inheritance filter down to the children?
    2. Should I use one or all of the above SQLServer * groups defined during the install?
    3. Tragically the "Repair Installation" failed, (without providing a GUI reason why, though it may have created an error log somewhere) so I guess I need someone to look at their "Data" path and tell me what the Security permissions are set to for the SQL SErver Usage.

    <Edit>

    Actually I did receive an error from the attempted repair installation:

    SQL Server Setup has encountered the following error:
    Attributes do not match. Present attributes (Directory, Archive) , included attributes (0), excluded attributes (Archive, Compressed, Encrypted).
    Error code 0x84B40000.

    THanks for the help

    Jaeden "Sifo Dyas" al'Raec Ruiner

     

     


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Sunday, May 22, 2011 3:36 PM

Answers

  • Well, SUCCESS!

    basically, I performed a complete rebuild of the WMI Repository, which did not work, but after this process I found several sites indicating some manual fixes that were microsoft "workarounds" for this problem.  After running a SP3 reinstall, I manually affected the permissions of the appropriate registry keys and WMI Root nodes, and after a reboot all installed and worked as it should.  Thank you all very much for helping me narrow down the issue and resolve the problem.

    J"SD"a'RR


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    • Marked as answer by Peja Tao Tuesday, June 7, 2011 1:32 AM
    Monday, June 6, 2011 4:51 PM

All replies

  • Do you have database backups?  If so, you'd be better of reinsltalling SQL Server and then restoring your databases from your backups.
    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Monday, May 23, 2011 1:33 AM
  • Description:initerrlog: Could not open error log file 'C:\SQL_DB\MSSQL\Log\ERRORLOG'. Operating system error = 5(Access is denied.).

    This can easily be trooubleshooted by using ProcMon (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) This would actually telll you the account (which would be service account)
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, May 23, 2011 2:00 AM
  • Well to add insult to injury I decided I would just uninstall SQL Server and try again. 

    However, the Uninstall gives me the exact same error as the Repair, so I can't even uninstall SQL Server to reinstall.  I don't know what that Attribute conflict is on the harddrive but it is really frustrating my attempts to restore my system. 

    Last night I ran a COMPACT /U forced on the entire hard drive to ensure nothing on the system is "compressed" and I will try to uninstall again tonight, but if anyone has other suggestions they would be most appreciated.

    Thanks

    J"SD"a'RR


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Monday, May 23, 2011 1:43 PM
  • Okay,

    So Whether I try "Repair" or "Uninstall" I get this error:

    TITLE: SQL Server Setup failure.
    ------------------------------
    SQL Server Setup has encountered the following error:
    Attributes do not match. Present attributes (Directory, Archive) , included attributes (0), excluded attributes (Archive, Compressed, Encrypted).
    Error code 0x84B40000.
    ------------------------------

    I can guarantee the drive and nothing on my system is "Compressed" nor "encrypted".  So, How can I force the "uninstallation" of Sql Server in order to start trying to install a fresh copy.  Also, if this glitch is occurring at the repair/uninstall point is it wise to assume it will it also pop up during an install?  if so how do I resolve whatever issue the setup is having on my computer?

    THanks

    J"SD"a'RR


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Monday, May 23, 2011 11:23 PM
  • Hello ,

    Can you try the workaround specified in following article

    http://support.microsoft.com/kb/957453

    To work around this problem, remove the Archive attribute from the installation folder. To do this, follow these steps:

    1. Open the folder that contains the folder that has the Archive attribute.
    2. Right-click the folder that you want to remove the Archive attribute from, and then click Properties.
    3. On the General tab, click Advanced.
    4. On the Advanced Attributes tab, click to clear the Folder is ready for archiving check box.

    If the installation folder does not exist, remove the Archive attribute from the closest parent folder. For example, you install SQL Server 2008 into the following folder:

    C:\FolderA\FolderB\FolderC

    The FolderC folder does not exist. The FolderB folder has the Archive attribute set. In this situation, you must remove the Archive attribute from the FolderB folder.

     

    Thank You

    Jayaprakash JO - MSFT

    • Proposed as answer by Peja Tao Friday, May 27, 2011 3:08 PM
    Friday, May 27, 2011 2:24 PM
    Answerer
  • Excellent.

    This helped me over my initial hurdle, however another error cropped up during the "Repair" process.

    ------------------------------
    The following error has occurred:
    Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
    ------------------------------

    So I proceeded to review the files in the "C:\Program Files\Microsoft SQL Server\100\Setup BootStrap\Log" folder structure, and for the most recent folder named today'd date and time I found the most recent "SQLServer_ERRORLOG*.txt" file, which is included in the link below.  upon suggestion i reviewed the Event Viewer warnings and errors, and frankfly found quite a few too many to list, but I will describe them in summarized detail.

    First there was some "Information" events from the MsiInstaller, and then a whole Slew of warnings regarding invalid Visual Studio - VsTemplate's.  Saying some of the Zip file templates in Micorosft Visual Studio 9.0\Common7\IDE\ItemTemplates folder paths were corrupted or had invalid attributes or tags.  After oh, say 50 or more of these warnings there were some Information messages from MSSQL$SQLEXPRESS, regarding what it was doing until the first Error:

    Error occurred during decryption.

    More information events regarding the start of the 'model' database until another bank of errors:

    • MSSQL$SQLEXPRESS: Fallback certificate initialization failed with error code 1
    • MSSQL$SQLEXPRESS: Unable to Initialize SSL encryption because a valid certificate could not be found.
    • MSSQL$SQLEXPRESS: TDSSNIClient initialize failed with error 0x80092004 ... unable to initialize SSL support
    • MSSQL$SQLEXPRESS: Could not start the network library because of an internal error in the network library
    • MSSQL$SQLEXPRESS: SQL Server could not spawn FRunCM thread.
    • .Net Runtime Optimization Service: .Net runtime Optimization server (clr_opimization_v2.0.50727_32) Failed to compiler <SQL SErver Intall>\100\Tools\Binn\VSShell\Common7\Tools\VDT\DataDesigners.dll
    • .Net Runtime Optimization Service: Same as previous but for DataProjects.Dll
    • Warning - WinMgmt: WMI ADAP was unable to load the MSSQL$SQLEXPRESS performance library because it returned invalid data: 0x80041001
    • Warning - WinMgmt: WMI ADAP was unable to create the object Win32_PerfFormattedData_SQLAgentSQLEXPRESS_SQLAgentSQLEXPRESSJobs for Performance Library SQLAgent$SQLEXPRESS because error 0x80041002 was returned

    So, this all occured during a "Repair" installation of SQL Server 2008 Express.  I went through the SQL Server Installation Center, Selected Maintainence main root node, and chose the "Repair" option. 

    These above errors seem to be more related some other underlying windows issues, but knowing these errors might help someone point me in the right direction for resolving the issue.  Granted there were some flaws in the windows installation that led up to the hard drive crash, and though i've managed to get my system back up and limping along, perhaps some of these errors corrupted necessary system files.   Suggestions on how to repair them?  Possibly a reinstallation of .Net 3.51, or other such maintenance tasks which could resolve the problems and allow the "repairing" of my sql server installation. 

    If possible I would prefer to avoid running a full "Windows XP Repair Install', though if it appears that may be the only thing which will resolve the issue, in completely understand. 

    Thanks

    Jaeden "Sifo Dyas" al'Raec Ruiner

     

     

     

     

     

     


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Saturday, May 28, 2011 10:23 PM
  • Hello ,

     

    The error suggest we have WMI issues. Do you have WMI service running.

     

    You can test WMI using the sample as in article http://technet.microsoft.com/en-us/library/dd353297(SQL.90).aspx

     

    Thank You

     

    Jayaprakash JO - MSFT

    • Proposed as answer by Peja Tao Wednesday, June 1, 2011 10:55 PM
    Wednesday, June 1, 2011 8:01 PM
    Answerer
  • So,

    The Problem is much worse that before.  First, I decided that since the "repair" was not beeing to cooperative, that I would try a full clean install.  So, i succesfully was able to uninstall the entirety of SQL Server 2008 Express w/ Adv. Services.  I proceeded to reboot.  Once rebooted, I proceeded to clean all vestiges of the application from the Program Files directory, so that the computer would be as if SQL Server had never been installed. 
    When I attempted to run the install at this point, it proceed past the EULA, to the screen where it request to install the setup support files.  It completes this step but the next screen never displays.  When checking active processes, the setup100.exe file is in memory, utilizing about 39-50 mb, fluctuating, but no window ever displays on screen.

    I have since then proceeded to do the following:
    -Reinstalled .Net 3.51
    -Repair installation of Visual Studio 2008 Pro - which failed on installing the Microsoft ReportPublishing Services (wizard or something).

    So I rebooted again and proceeded to follow up with the investigation into the WMI.  I found several sites indicating how to repair for XP and for XP SP2, however I am currently running SP3.  i tried the the command: rundll32 wbemupgd, UpdateRepository, which apparently did nothing, so I attempting using the "for" command at the command prompt to re-register all the dll's and exe's in the %windir%\System32\wbem folder.  after completing that step, I proceeded to run a complicated rundll32 setupapi command that was indicated to reinitialize the WMI repository, which apparently seemed to reinstall aspects of SP3.  It had one minor issue in the process where a file napcomp??.mof (not positive on the filename) was missing but I discovered from reports that indeed the file had already been installed to the system32\wbem directory so the install process completed and executed a reboot.
    After the reboot I again went in to attempt reinstallation of SQL Server 2008 Express w/ Adv. Services, and the same issue occurred.  It proceeded to install the "Setup Support Files" and then spawned the setup100.exe file at which point nothing else occured.  No window, to install options, nothing.  I believe that something occurred when I forcibly deleted the Program Files\Microsoft SQl Server\ foler from my system, regardless of the fact that I uninstalled the application first. 

    Currently I am now attempting to verify complete uninstallation of everything SQL Server, from the Management Objects, Native CLient, Suetup Support Files, Compact for Devices, COmpact Design Tools, and Database Publishing wizard, which after that is completed I will attempt to reboot and try again with the installation of
    .Net 3.51 - just in case
    SQL Server 2008
    If the successful or at the very least a different error occurrs I will notify immediately, but if there are any suggestion I might take I would be most appreciative.  This is my only development system and I'm already two weeks overdue on completing a contract, since I have no version of SQL Server to run final development and debugging on.  I REALLY need to get this resolved and installed, and if this fails I will most like attempt a "Repair" install of XP, which hopefully is the 2nd last resort, unto the last resort of completely erasing the current XP and installing everything from scratch, (which i really do NOT want to do...hehehe).

    Thanks so much for your help
    Jaeden "Sifo Dyas" al'Raec Ruiner
    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Saturday, June 4, 2011 10:41 PM
  • Well, SUCCESS!

    basically, I performed a complete rebuild of the WMI Repository, which did not work, but after this process I found several sites indicating some manual fixes that were microsoft "workarounds" for this problem.  After running a SP3 reinstall, I manually affected the permissions of the appropriate registry keys and WMI Root nodes, and after a reboot all installed and worked as it should.  Thank you all very much for helping me narrow down the issue and resolve the problem.

    J"SD"a'RR


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    • Marked as answer by Peja Tao Tuesday, June 7, 2011 1:32 AM
    Monday, June 6, 2011 4:51 PM