How To : SQL 2012 Filetable Setup and Usage

re: How To : SQL 2012 Filetable Setup and Usage

HarshDeep_Singh — Mon, 22 Apr 2013 02:50:54 GMT

Hi skan2dan,

Thanks for getting back. In that case, I need you to confirm on 2 things:

1. Please ensure that the SQL login has sysadmin privileges (direct, not through a credential).

2. Please make sure that you test this with both Local System and domain accounts as SQL Server and SQL Agent service accounts.

Regards,

Harsh

re: How To : SQL 2012 Filetable Setup and Usage

skan2dan — Thu, 18 Apr 2013 11:18:05 GMT

Harshdeep_Singh,

If I add the Windows OS Log in with SysAdmin privileges to the SQL Server instance then I am able to open the File table directory in either way. i.e. 1. by selecting the "Explore File Table Directory" option from SSMS or 2. accessing the Filetable share first, here it is

\\MYSERVERNAME\mssqlserver\DocumentStore\MyDocumentStore.

So, it is confirmed that the Filetable share has been successfully created.

My problem here is we need to use a SQL Server authentication with least privileges to access the Filetable share as we need to provide the credentials to the end users.

re: How To : SQL 2012 Filetable Setup and Usage

HarshDeep_Singh — Wed, 17 Apr 2013 16:16:13 GMT

Hi Skan2dan,

Thanks for getting back on this. Can you please try using a windows login to access the Filetable share first, to confirm that the share has been created successfully and is accessible?

Regards,

Harsh

re: How To : SQL 2012 Filetable Setup and Usage

skan2dan — Wed, 17 Apr 2013 13:59:11 GMT

Harshdeep Singh,

Thanks for the reply.

As you advised, I assigned SysAdmin privilege to the SQL Server Log in. Still the same error.

Then, I have done the impersonation by following steps.

- Created a Local Windows Log in account namely "OSLOGIN" on the Windows Server with admin privileges.

- Changed & assigned this Windows Log in as the service account of SQL Server & Agent Services.

- Impersonated the SQL Login namely "SQLLOGIN" with SQL Server's Service Account by the following command.

use [master]

GRANT IMPERSONATE ON LOGIN::[OSSERVER\OSLOGIN] TO [SQLLOGIN]

But, still the issue is persisting and the error is : " The File location cannot be opened. Either access is not enabled or you do not have permissions for the same".

Kindly review and suggest to overcome this issue. Thanks in advance.

re: How To : SQL 2012 Filetable Setup and Usage

xudong125 — Thu, 14 Mar 2013 05:53:30 GMT

@skan2dan maybe you need check you firewall.make sure the TCP port 139,445 is allow in.

re: How To : SQL 2012 Filetable Setup and Usage

HarshDeep_Singh — Fri, 08 Mar 2013 01:15:45 GMT

Hi skan2dan,

Thanks for showing interest in the blog. To use Filetables with a SQL server login (as opposed to windows one), you need to give it the sysadmin role in SQL Server. To do this, you have to right on the login in the object explorer in SQL Server Management Studio, navigate to the roles tab, and check the sysadmin role.

Hope this helps.

Regards,

Harsh

re: How To : SQL 2012 Filetable Setup and Usage

skan2dan — Thu, 07 Mar 2013 12:03:54 GMT

Hi,

While clicking on "Explore File Table Directory" we receive the following error:

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

On you blog post you mentioned that "The only exception is using a SQL Server login that has sysadmin privileges (in which case it will impersonate the SQL Server Service account). "

Please suggest how can we achieve this ?