Search code examples
sql-servert-sqlssisfile-permissions

Unable to open the file .mdf/.ldf. Operating system error 5: 5"(Access is denied.)" - Trying to replace .mdf/.ldf file with new one

I have the ability to move .mdf/.ldf files at will from the default \DATA folder location. I can set the database online and offline whenever I move these files manually.

We get a new set of .mdf/.ldf file every month to replace the one that we have. I am trying to automate this process with SSIS. I have managed to create a package that will handle replacing the old .mdf/.ldf files with the new ones.

BUT!

The SQL task to turn on the database with the new .mdf/.ldf files does not run.

I get this error:

Error: 0xC002F210 at Take Sandbox Online, Execute SQL Task: Executing the query "ALTER DATABASE Sandbox SET ONLINE" failed with the following error: "Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL13.SERVER\MSSQL\DATA\SANDBOX.mdf". Operating system error 5: "5(Access is denied.)".

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL13.SERVER\MSSQL\DATA\SANDBOX_LOG.ldf". Operating system error 5: "5(Access is denied.)".

I am able to do this process manually, but for some reason SSIS is not able to turn the database back on with a SQL Task. I clearly have permissions to perform this task, but I am ignorant of how running through SSIS would be an issue.

Please help!

enter image description here

Thank you

Solution

  • SQL Server uses a service account, usually called NT SERVICE\MSSQLSERVER, which needs to have permissions on the mdf/ldf files.

    Right click the files, click properties / security /edit / add, and add the account to permissions.