SQL Server account delegation setup

Question

I am try to run bulk insert command on SQL Server 2008 but I am having issues with the security. after researching via internet, the problem has something to do with account delegation and impersonation.

However all solutions seems to point to an Active Directory setup which my setup is not.

My setup is SQL Server 2008 Express on Windows Server 2008 Standard configured as a workgroup.

Despite a SQL Server user account, assigned all the user mappings and server roles and ensuring security set to SQL, I am getting this error:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\server_name\file_name.txt" could not be opened. Operating system error code 5 (Access is denied.).

So, how does one setup SQL Server account delegation and impersonation on a workgroup environment?

Thanks in advance

Answer 1

the error means that your os denied access to the file, you can try running your code after running ssms as administrator, or you can login to ssms using win authentication with the computers admin user.

Answer 2

the following is my code:

If object_id('tempdb..#POSSales') is not null DROP TABLE #POSSales;
CREATE TABLE #POSSales (InvoiceNumber varChar(11) COLLATE database_default, SalesDate varchar(11), CustomerNumber varchar(20), ItemNumber varchar(30), Qty real, [Description] varcHar(255),
ExTaxPrice Money Default 0 Not Null, IncTaxPrice Money Default 0 Not Null, TaxCode Varchar(3), Inclusive Varchar(1), Location Varchar(10), ExportDate VarcHar(11),
TotalInvAmt Money Default 0 Not Null  );

BULK INSERT #POSSales FROM 'E:\epoint\MYOB\V1K10121349_SLS.txt' WITH (FIRSTROW = 1);

The line at "BULK INSERT..." is causing the error. It runs fine if i am at there server.

regards rashid.

Answer 3

Based on the information you provided in the SQL script, the NT account which the SQL Server service is running under doesn't have access to the folder which the file is sitting in. You'll need to see which account the SQL Service is running under, then grant that account rights to the folder.