How to enable permissions in SQL Server 2012 FileTable folder share?

Question

After successfully creating a FileTable, I tried viewing the fileshare but my permissions are denied. In Management Studio, right-clicking on FileTable then "Explore FilteTable Directory" gives me the following error message:

The File location cannot be opened. Either access is not enabled or your do not have permissions for the same.

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

This is SQL Express running on my local machine. I'm accessing this share from the same machine. What am I missing?

Freytag

did you get the solution for this? – Figuration

Answer 1

Does the windows user you are trying to access the fileshare as have SQL server access to the filetable database? Windows share permissions don't apply to filestream shares so, make sure you have permissions in the SQL database.

The other things you need to check to make sure you have access are the setting in configuration manager to ensure that Transact-SQL access is enabled as well as file I/O access (you can also set here whether clients can connect to the share remotely too).

To access these settings, open SQL Server configuration manager, in SQL server services, right click on the SQL Server Service for your instance and select properties, on the filestream tab you will see the options.

The next place you need to check for settings (yes, you have to enable this feature in 3 separate places!) is the level of access on the SQL server itself.

Open SQL Server Management Studio, connect to your SQL instance and right click on your server and select properties. Click on the advanced section and there is a section for filestream, you need to select full access enabled if you want to use filetable.

I actually found this article after typing all of that which explains how to enable the pre-requisites for filetables:

Enable the Prerequisites for FileTable

Hope this helps.

Huppah

I've done all of that. I tried everything, even adding my user account as the dbo or dbowner explicitly with full permissions on that database. Also, the user account I sign in with to my computer is the administrator account. I use it to sign in to SSMS and create/destroy anything I want. – Freytag

Did you restart the server after doing the pre-requisites? – Armington

Answer 2

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

1. Step into SQL Server Configuration Manager
2. Open SQL Server Services
3. Right click on SQL Server (MSSQLSERVER) and go to properties
4. Go to the FILESTREAM tab and make sure Allow remote clients access to FILESTEAM data is checked

Cofsky

I had a user that had dbo rights to the database, but couldn't connect. This was all I was missing! – Sulfur

Answer 3

Can you check a few things?

Can you via Windows Explorer try to browse to each of the following:

\\YOURCOMPUTERNAME

\\YOURCOMPUTERNAME\[FILESTREAM Share Name]\  e.g MSSQLSERVER

\\YOURCOMPUTERNAME\[FILESTREAM Share Name]\[FILESTREAM Directory Name]

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

[FILESTREAM Share Name]

• this is the name as defined at the Server Insance level when you set FILESTREAM access up
• to check it, right click on the Server Connection in SSMS and choose Properties
• then look in Advanced --> FILESTREAM --> FILESTREAM Share Name

[FILESTREAM Directory Name]

• this is the name as defined in the database when you set FILESTREAM access up
• to check it, right click on the Database in SSMS and choose Properties
• then look in Options --> FILESTREAM --> FILESTREAM Directory Name

[FILETABLE Table Name]

• Remember when you name your table it has to conform with Windows Folder naming rules (ie avoid special characters) e.g "TABLE|WEIRDCHARACTER"

Note(1): I have found that if you give your user only "ALTER" permission on the Filetable Table it will be browseable at the

\\YOURCOMPUTER\MSSQLSERVER\FILESTREAM_DIRECTORY_NAME\

level but you wont actually be able to browse the contents of the "directory"

Note(2): if you only give SELECT, UPDATE, DELETE, INSERT permission to a user on a filetable Table it will be able to access the folder with the fullpath and see the contents and act on them - create new files, modify existing files

\\YOURCOMPUTER\MSSQLSERVER\FILESTREAM_DIRECTORY_NAME\FILETABLE_NAME\

but not browse it at the FILESTREAM_DIRECTORY_NAME level - it in essence becomes a "hidden" directory that you must know the entire pathname to find (unless you guess it of course in a brute attack)

Armington

Answer 4

Other posters have dealt with the FILESTREAM set up.

To browse and access files within a filetable, VIEW DEFINTION and SELECT permissions will be required as a minimum. The ALTER permission referenced in dmc2005's post is not required.

Example (granting access to a Windows user or group):

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

Assigning the User / Group to one of the fixed database roles (for example db_datareader) will not grant the required permissions for filestream access. The permissions must be explicitly granted.

Blacking