Friday, March 27, 2020

SQL Server - SMSS - Solution to .bak file not visible in any other directory in SSMS


Try to restore a database in SQL Server 2012 R2 (but this does not matter), and ran into same old issue of not being able to find a SQL Server backup (.bak) in a directory, that was not the default (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup) directory for back-up files. 

In my example, the .bak file was in C:\build







Of course it's a permissions on the file right? 
So launch SQL Server Configuration Manager (SSCM) to get the account that SLQ Server is running under and copy the Account Name.
In this case, copy Account Name which is  "NT Service\MSSQL$SQLENT2012"
Adding the service account is not as straightforward.

  1. Log into the server. (The change must be made on the actual server, not through a network share.)
  2. Change the Locations to the local server name, not an Active Directory account. 
  3. Paste in NT Service\MSSQL$SQLENT2012 in the name box. (Do not click Check Names)
  4. Click OK. You will then see a list of the matching service accounts. Select MSSQLSERVER, and click OK to accept the selection.

  5. Which then looks like the following when done. Success right?


  6. Open the directory in SQL Server Management Studio (SSMS) what blank ?



     
  7. What if I just copy the file to default directory C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup

  8. Check permission of this file in default. They are the same!

  9. Then it dawned on me,  the directory C:\build does not have any permissions assigned to "NT Service\MSSQL$SQLENT2012".

  10.  SOLUTION

    It's a file and FOLDER permission issue. All solutions I have seen on Stack Overflow/Exchange forget the fact if you have a existing folder you have to apply the service account of SQL Server (use SSCM to find) to each FOLDER in the full path.

    So you have to the cascade the account user and set permissions down each directory ;) as well.




No comments:

Post a Comment