How to enable permissions in a FileTable shared folder in SQL Server 2012?

Question

After successfully creating a FileTable, I tried to view the file, but my rights were denied. In Management Studio, right-clicking on FileTable, then "Examine the FilteTable Directory", you will receive the following error message:

File location cannot be opened. Either access is not enabled, or you do not have rights to the same.

If I try to manually reach the share using \ mycomputer \ sqlexpress ..., I still refuse access.

This is SQL Express running on my local machine. I access this part from the same machine. What am I missing?

Answer 1

For me, since my server is a network server, the resolution was:

• Step in SQL Server Configuration Manager
• Open SQL Server Services
• Right click on SQL Server (MSSQLSERVER) and go to Properties
• Go to the FILESTREAM tab and make sure Allow remote clients access to FILESTEAM data

Answer 2

The Windows user you are trying to access the file archive with has access to the database for SQL Server SQL Server files? Windows sharing permissions do not apply to shared resources, so make sure you have permissions in the SQL database.

The other things that you need to check to make sure that you have access are the settings in the configuration manager to provide access to Transact-SQL, as well as access to file I / O (you can also specify whether clients can connect to the share also deleted).

To access these options, open SQL Server Configuration Manager in SQL Server Services, right-click on SQL Server Service for your instance, and select properties. On the filter tab you will see the options.

In the next place you need to check the settings (yes, you must enable this function in 3 separate places!) - this is the access level on the SQL server itself.

Open SQL Server Management Studio, connect to the SQL instance and right-click on your server and select properties. Click on the extended section, and there is a section for the stream, you need to select full access if you want to use the table file.

I really found this article after entering all that explains how to enable pre-requisites for file tables:

Enable Prerequisites for FileTable

Hope this helps.

Answer 3

Can you check a few things?

Can you, using Windows Explorer, try to move on to the following:

 \\YOURCOMPUTERNAME \\YOURCOMPUTERNAME\[FILESTREAM Share Name]\ eg MSSQLSERVER \\YOURCOMPUTERNAME\[FILESTREAM Share Name]\[FILESTREAM Directory Name] \\YOURCOMPUTERNAME\[FILESTREAM Share Name]\[FILESTREAM Directory Name]\[FILETABLE Table Name] 

[FILESTREAM File Name]

• this is the name specified at the Server Insance level when you set access to FILESTREAM up
• to check it, right-click on the server connection in SSMS and select "Properties"
• then go to Advanced → FILESTREAM → FILESTREAM Share Name

[FILESTREAM Directory Name]

• this is the name defined in the database when installing FILESTREAM access up
• to check it, right-click on the database in SSMS and select "Properties"
• then go to Options → FILESTREAM → Directory name FILESTREAM

[FILTERED TABLE NAME]

• Remember, when you name your table, it must conform to the Windows Folder naming conventions (ie, avoid special characters), for example, "TABLE | WEIRDCHARACTER"

Note (1): I found that if you give your user only “ALTER” permission in the file table table, it will be available for viewing in

\\ YOURCOMPUTER \ MSSQLSERVER \ FILESTREAM_DIRECTORY_NAME \

, but in fact you won’t be able to view the contents of the “directory”

Note. (2): if you give the user SELECT, UPDATE, DELETE, INSERT permission in the file table table, he will be able to access the folder with the full path and see the contents and act on them. create new files, modify existing files

\\ Yourcomputer \ MSSQLSERVER \ FILESTREAM_DIRECTORY_NAME \ FILETABLE_NAME \

but do not view it at the FILESTREAM_DIRECTORY_NAME level - it essentially becomes a "hidden" directory in which you must know all the way to find it (unless you guessed it, of course, in a rough attack)

Answer 4

Other posters deal with setting FILESTREAM.

To view and access files in the file table, at a minimum, you need VIEW DEFINTION and SELECT permissions. ALTER permission specified in dmc2005 message is not required.

Example (providing access to a user or group of Windows):

 GRANT VIEW DEFINITION,SELECT ON FileTableName TO [MYDOMAIN\MYGROUPNAME] 

Assigning a user / group to one of the fixed database roles (for example, db_datareader) will not provide the required permissions to access the filters. Rights must be explicitly granted.