April 22, 2017 at 3:53 am
Hi,
I am running SS2008R2, SS2012 and SS2014 on a home PC for development purposes, read this also as I am not a DBA. 🙂 While I have SQL Server installed on the C drive I have always put the data on my D drive into to version specific subfolders.
I upgraded my D drive disk so copied all the from old to new disk. I then changed the Drive letters, old disk to H and new disk to D. All works well with everything except my 2012 and 2014 SQL Server Services. When I try to start them I get the following error. I have checked the files are there and they are accesible.
FCB::Open failed: Could not open file D:\Databases\SQLServer2012\MSSQL11.TESTSQLSRVR2012\MSSQL\DATA\master.mdf for file number 1. OS error: 5(Access is denied.).
Is there anyway to fix this?
Thanks much,
Steve
April 22, 2017 at 4:05 am
SteveD SQL - Saturday, April 22, 2017 3:53 AMHi,I am running SS2008R2, SS2012 and SS2014 on a home PC for development purposes, read this also as I am not a DBA. 🙂 While I have SQL Server installed on the C drive I have always put the data on my D drive into to version specific subfolders.
I upgraded my D drive disk so copied all the from old to new disk. I then changed the Drive letters, old disk to H and new disk to D. All works well with everything except my 2012 and 2014 SQL Server Services. When I try to start them I get the following error. I have checked the files are there and they are accesible.
FCB::Open failed: Could not open file D:\Databases\SQLServer2012\MSSQL11.TESTSQLSRVR2012\MSSQL\DATA\master.mdf for file number 1. OS error: 5(Access is denied.).
Is there anyway to fix this?
Thanks much,
Steve
Open the folder D:\Databases\SQLServer2012\MSSQL11.TESTSQLSRVR2012\MSSQL\ and give the SQL Server Service Account full control of the DATA folder.
😎
April 22, 2017 at 4:58 am
thanks for your quick reply. Looking on configuration manager that 'Log on as' is an NT service account which i can find to provide permission to the folder. Should I change that log in as account and can I do that from inside configuration manager or do I need to have access to SQL Server first. Or is there anyweay top move the permission which are now on my H drive ovcer to my D drive?
Thanks again
Steve
April 22, 2017 at 5:22 am
SteveD SQL - Saturday, April 22, 2017 4:58 AMHi Eirikur Eiriksson,thanks for your quick reply. Looking on configuration manager that 'Log on as' is an NT service account which i can find to provide permission to the folder. Should I change that log in as account and can I do that from inside configuration manager or do I need to have access to SQL Server first. Or is there anyweay top move the permission which are now on my H drive ovcer to my D drive?
Thanks again
Steve
You can use the command line utility ICACLS to list the permissions on the original folder and apply them to the destination folder
😎ICACLS [folder path]
April 22, 2017 at 6:52 am
sorry, being dense here and maybe trying to do this all wrong.
I went to my old drive (H), right-clicked, properties , Group or User Name. I can see the account that is under 'Log in as' in the SQL Server Configuration Manager. It is NT Service\MSSQL$[servername].
When I try to add that 'group/user/account' to the new hard drive folder (D) I can't. It doesn't show up as a valid user. I have googled this and it says I can't do it that way because NT Services is a services, not an account. The solution provide there was to reinstall SQL Server however the original problem was different so I am still hopeful there might be an alternative.
Thank again for your time and patience
Steve
April 23, 2017 at 6:34 am
SteveD SQL - Saturday, April 22, 2017 6:52 AMsorry, being dense here and maybe trying to do this all wrong.I went to my old drive (H), right-clicked, properties , Group or User Name. I can see the account that is under 'Log in as' in the SQL Server Configuration Manager. It is NT Service\MSSQL$[servername].
When I try to add that 'group/user/account' to the new hard drive folder (D) I can't. It doesn't show up as a valid user. I have googled this and it says I can't do it that way because NT Services is a services, not an account. The solution provide there was to reinstall SQL Server however the original problem was different so I am still hopeful there might be an alternative.
Thank again for your time and patience
Steve
What does the ICACLS return on the original folder?
😎
You should be able to add the account by going to Folder->Properties->Security->Advanced->Find Now
April 24, 2017 at 12:00 am
I tried to add a response yesterday but SQL central wasn't responding well.
ICACLS was returning what I was seeing in the security properties window. I did find out what my problem was in add the NT Services account. I needed to change the Location to my local PC from my Server, then I was able to add the account.
however that didn't solve my problem as I was still getting the access denied error so I ended up changing the 'Log In As' in SQL Configuration Manager to my login account which is an administrator account. This at least allowed me to start the SQL Server and get some work done.
As a long term solution I am not sure if this is the right approach though. Should I be creating a Windows account specifically for signing into SQL Server? If so, what rights do I need to give it.
Thanks again for your help
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply