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":
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?
-
Doesn't the lack of a plus sign just indicate that there are no further subdirectories to drill into?Doug Deden– Doug Deden2024年10月23日 14:48:39 +00:00Commented Oct 23, 2024 at 14:48
-
@DougDeden: The subdirectories are there.Dominique– Dominique2024年10月23日 15:01:03 +00:00Commented Oct 23, 2024 at 15:01
1 Answer 1
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.
-
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?Dominique– Dominique2024年10月24日 06:27:21 +00:00Commented 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?)Dominique– Dominique2024年10月24日 06:33:41 +00:00Commented 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.vonPryz– vonPryz2024年10月24日 07:43:46 +00:00Commented Oct 24, 2024 at 7:43
-
@vonPryz: Ok. Now I have confirmation of the "ACCESS DENIED", but now what?Dominique– Dominique2024年10月24日 08:22:40 +00:00Commented 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.Sean Gallardy– Sean Gallardy2024年10月24日 12:56:22 +00:00Commented Oct 24, 2024 at 12:56
Explore related questions
See similar questions with these tags.