permissions for a sql login to a NAS share

Question

One of the app. teams has  a requirement. I have set up a sql authentication login (testuser) for their application to access the databases. They have a stored proc that's triggered from the app. side, and when it runs it bulk inserts some records from the text files on a NAS share (I am told this is on a unix server) to the tables in a SQL database. so how can we provide access to the NAS folder/ share for the SQL login? I know we cannot directly provide the security for sql login. 

Had the NAS share been on a windows server, I think it would suffice if we provide the sql service account the necessary permissions. Your inputs are truly appreciated.

Answer 1

Hi,

Unix comes with a tool (samba) that lets you manage SMB (shares) permissions for both Windows domain and workgroup users.

So, start by identifying under what Windows account are your SQL Server services running, then ask your UNIX administrator to grant

read permissions (via samba) to SQL Windows account on this particular SMB share.

On the other hand, I'm not sure if that will work if your SQL Server is running under internal accounts like Local System Account, Network Service or others.

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 2

The SQL Database Engine service account is what is used to access shares and will need access to the NAS folder. 

Answer 3

If the SQL Server service is using Local Service, Local System or a local account then you have a problem - communication from SQL Server is limited to the box it resides on only.  If the SQL Server service is using Network Service you still have a problem as you are using a SQL Login - if this were a Windows Auth account then the Windows Account would be used to access the Windows Share.  If you are using a domain account then grant the permissions to the account that SQL Server is using - I typically grant Read/Write/Modify for these types of shares.

I hope this helps.

Noral

Answer 4

Appreciate your inputs everyone. 

In our case, giving  the SQL service account privileges to the remote NTS file shares is not an option due to the company standards.

I am thinking of using a different approach. I am planning to request a domain user be created and add it to a group that already has permissions to the remote NTS file share. Once that's done, I am planning to create a credential (in sql server) using the newly created login and then map the sql login ("sql authentication login (testuser) " that the app. team uses) with the credential created. I am wondering if the approach I am following is correct and please let me know if I missing something for it to work.

Answer 5

For the sql login 'testuser', I have already given bulkadmin privileges. Please let me know if I am missing something.

Answer 6

Can some body suggest? I created a credential as per my above post. But it's not working. When using sql server authentication (by the app. team) and doin bulk inserts, is it a must that the sql service account has the permissions to the remote NAS share. Can someone please help?

Answer 7

Can some body suggest? I created a credential as per my above post. But it's not working. When using sql server authentication (by the app. team) and doin bulk inserts, is it a must that the sql service account has the permissions to the remote NAS share. Can someone please help?

I think so. I don't think you approach with credentials is a supported on. But I have never used credentials and never really understood what to use them for.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Ask your UNIX admin to check SMB audit logs.

Are there any traces of your credential ID trying to access share resources?

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 9

For discussion of BULK INSERT rights that assume that you are using the BULK INSERT statement:
http://msdn.microsoft.com/en-us/library/ms188365.aspx

The security context of the user (e.g. DOMAIN\TheUser) running the step must have rights to the file being read by the BULK INSERT.   This does not imply the server account unless the server is running the step as itself.

You have created a credential, recording the login and password that you want to use, but the only way that I have successfully used a credential is through a SQL Server Agent Proxy.

So, what you could do is:

1. Create a SQL Server Agent proxy using the credential you created and activate it for the "Operating System (CmdExec)" subsystem.
2. Create a SQL Server Agent job.  Let's name it: Bulk Insert NAS Data Files
3. Create a Operating System step (not a Transact-SQL step) since you need to use the proxy.
4. Put into your OS step something like: 
   SQLCMD -SServerName -dDatabaseName -E -Q "EXECUTE dbo.LoadNASData"
5. Save your job and determine how you want to start it.

You could start it by

1. Creating a schedule that runs automatically.
2. Issuing the msdb.dbo.sp_start_job if you have enough rights.
3. You can set up an alert to start the job and call RAISERROR.
4. See other comments in: http://social.msdn.microsoft.com/Forums/nb-NO/sqlsecurity/thread/ca6db5b1-499d-4ecf-97f1-0daefa5c2c08

The issue of parameters to the job, if needed, need to be resolved.  But you could push the desired parameters into a 'queue' table and have the stored procedure, when run, process parameters.

FWIW,
RLF

Answer 10

Thanks everyone for your inputs, we could only get rid of this issue by finally giving the permissions to the sql service account.