none
Error: 17204 (FCB::Open failed) - How to set persmission correctly on a data/log file for SQL Server RRS feed

  • Question

  • Hello,

    Is there a process/procedure available in SQL Server which allows SQL Permission to be set properly for a given instance when a new drive is added? It seems like only way, that I know of, is to copy ACLs from existing data drive to this new drive to make this work, otherwise we get the following error:

    FCB::Open failed: Could not open file J:\sql\mnt\UsrDB01\Data\XX.mdf for file number 1. OS error: 5(Access is denied.).

    For a clustered drive adding it SQL Server service and applications does not seem to work either. Again I have to copy ACLs to make it work.

    I did search through Google/Bing, but could not find any meaningful resources (other than copy ACLs, add SQL Server account to the directory manually, etc.). One would hope a set defined process set permission (similar to the process when SQL Server sets permission these drives (folder) while installing it).

    Thanks,

    Vijay

    Thursday, November 15, 2012 1:58 PM

Answers

All replies

  • Below article explains about the permission requirements on folders, secpol, registry, etc

    http://msdn.microsoft.com/en-us/library/ms143504.aspx

    Thursday, November 15, 2012 2:03 PM
  • Thursday, November 15, 2012 2:03 PM
    Moderator
  • SQL service account should have full control on folder and files where data files are located.

    Please grant MSSQL$instancename group full access to new folder. It is not appeared in search window. Please look for “NT Service\MSSQL” on local machine. It will give you group name.

    Next step is to inherit folder permissions to inside files.

    http://support.microsoft.com/kb/313398?wa=wsignin1.0
    Thursday, November 15, 2012 6:27 PM
  • Hi Fedor,

    Thanks for replying to my post. I'm sure whoever gets Error: 17204 (FCB::Open failed), can bing/google to figure out what needs to be done, as you also mentioned, give full permission to SQL Server service account. Sounds like there is no process available within SQL Server to make sure this happens automatically.

    Generally what I do is use PowerShell commands to apply ACL, which I have found is much better and lot more reliable than manually adding these accounts and assigning permissions. I personally prefer PowerShell over cacls.

    Get-ACL | Set-ACL

    We can close this thread.

    Thanks,

    Vijay

    Friday, November 16, 2012 12:37 PM
  • this answer worked.

    i use mssql 2014 on windows server 2012 r2, the service works fine, but when i removed some database and run a server reboot, the sql service cannot start, system event shows master database access denied. i checked the master database file location's ACL settings, there is no MSSQLSERVER in the ACL list.

    i hav no idea why the MSSQLSERVER user is removed from the ACL list after a reboot.

    Monday, October 24, 2016 8:46 AM