0

I'm working with Microsoft SQL Server Management Studio, version v18.12.

I have shared a directory, in order for my colleagues to give me information, like database backups.
When I try to access a shared directory on my PC for restoring a database, this seems not to work, as you can see from this ±"Database restore dialog box":

Explanatory screenshot

All other directories have a "+"-sign, next to them, meaning they are accessible for reading, but my C:\To_Be_Shared directory has not, meaning that I can't read it.

This directory is just a simple directory, which other programs can access without problems, only SSMS is playing hard ball, so to speak.

What can I do in order to read the content of that directory?

Edit:
As mentioned in Sean's answer, I took a capture of events while trying to open a normal directory and the shared directory, but as this is my first encounter with Procmon, I don't know very much what to do.
I don't find the names of the directories I tried to open in the capture (C:\Temp_Folder and C:\To_Be_Shared).
There are several PATH_NOT_FOUND results for CreateFile operations, mentioning the following details: Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Random Access, Attributes: n/a, ShareMode: Read, Delete, AllocationSize: n/a.
There are several FILE LOCKED WITH ONLY READERS results for CreateFileMapping operations, mentioning the following details: SyncType: SyncTypeCreateSection, PageProtection: PAGE_EXECUTE_READWRITE|PAGE_NOCACHE.

Does this mean anything useful for my case? Is there a way to get the names of my mentioned directories in the details (or any other column) of the capture of the events?

After having done the test again, this time including the events of "sqlservr.exe" process, this is what I get:

Time of Day Process Name PID Operation Path Result Detail
11:32,6 sqlservr.exe 14548 CreateFile C:\To_Be_Shared ACCESS DENIED Desired Access: Read Data/List Directory, Synchronize, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a
11:32,6 sqlservr.exe 14548 CreateFile C:\To_Be_Shared ACCESS DENIED Desired Access: Read Data/List Directory, Synchronize, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a

This confirms that "sqlservr.exe" thinks access is denied, but what from then?

asked Oct 23, 2024 at 7:02
2
  • Doesn't the lack of a plus sign just indicate that there are no further subdirectories to drill into? Commented Oct 23, 2024 at 14:48
  • @DougDeden: The subdirectories are there. Commented Oct 23, 2024 at 15:01

1 Answer 1

6

The database engine service account needs to have both filesystem permissions to read items in the folder/list contents and share permissions. You seem to be missing one, the other, or both.

You can always use procmon to get a capture and see what is happening, but it's almost always the above mentioned issue.

answered Oct 23, 2024 at 14:43
5
  • Thanks for your quick reply. However, this is the first time I'm using procmon (I didn't even know this program existed :-) ). I've mentioned the results in the edit of my question. Can you guide me a bit further? Commented Oct 24, 2024 at 6:27
  • (Oh, I have filtered the procmon results, based on the PID of my SSMS, I hope that's correct?) Commented Oct 24, 2024 at 6:33
  • @Dominique No, it's not enough. You need to include SQL Server's PID too, as it's SQL Server that reads the files. Commented Oct 24, 2024 at 7:43
  • @vonPryz: Ok. Now I have confirmation of the "ACCESS DENIED", but now what? Commented Oct 24, 2024 at 8:22
  • 2
    @Dominique Much as I stated in the answer above, you'll need to add the service account for SQL Server into the appropriate permissions for that folder and share. This is done in the filesystem, not in SQL. Commented Oct 24, 2024 at 12:56

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.