Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Problem with the folder given

Hi,
This statement is working fine and also the image file goes to the table in its original format.
update dbo.user_master
set photo_file=(SELECT BulkColumn
FROM OPENROWSET(BULK N'C:\t93.jpg', SINGLE_BLOB) AS SRC)
where user_abbr='pc1';

But why do I get the problem for this, while "C:\setup\t93.jpg" is surely existing in the server with fine file format?
update dbo.user_master
set photo_file=(SELECT BulkColumn
FROM OPENROWSET(BULK N'C:\setup\t93.jpg', SINGLE_BLOB) AS SRC)
where user_abbr='pc1';

Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "C:\setup\t93.jpg" could not be opened. Operating system error code 5(Access is denied.).

How to ensure one DB user is able to upload whatever file within whatever folder of the server?
0
HuaMinChen
Asked:
HuaMinChen
  • 5
  • 4
3 Solutions
 
Steve WalesSenior Database AdministratorCommented:
You need to make sure that the user that is running the SQL Server service (not the user you connect to the database engine with) has access to the directory you're trying to read the file from.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
How to make sure the DB user is able to access the folders of the machine? Thanks
0
 
Steve WalesSenior Database AdministratorCommented:
I'll have to defer to someone who knows Windows level security better than me, I'm afraid.

Check out the Security section of these two documents:

http://msdn.microsoft.com/en-us/library/ms188365.aspx
http://msdn.microsoft.com/en-us/library/ms175915.aspx

I believe that the section on "Security Account Delegation (Impersonation)" is particularly relevant.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HuaMinChenBusiness AnalystAuthor Commented:
No, there can be some control in Sql server on the DB user's privileges to access the folders of the current Windows server, can't there?
0
 
Steve WalesSenior Database AdministratorCommented:
Nope - it's all about the SQL Server service owner account and the privileges it has.  Read those links from my earlier response.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
If you see the statements I've run above, one is with "c:\setup" which leads to the problem, one with "c:\" which is fine. This means the current DB user is having access to "c:\" while not to "c:\setup", as I am using the same DB user for doing the above. Then where is the control to make "c:\setup" "available/seeable" to the current DB user.
0
 
Anthony PerkinsCommented:
This means the current DB user is having access to "c:\" while not to "c:\setup", as I am using the same DB user for doing the above.
No, it does not mean that.  As indicated previously, this has nothing to do with the database user and everything to do with the SQL Server service startup account.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
I've requested that this question be deleted for the following reason:

I have not got one clear answer with satisfaction to this and this is no more needed. Thanks
0
 
Steve WalesSenior Database AdministratorCommented:
The answers given were correct - the security on the SQL Server service owner account needs to be modified to have access to whatever directories are needed for the bulk insert.  Links to Microsoft KB articles were provided.  

The remaining unanswered portion is the "how" on the Windows Security side, but that is a Windows OS issue, not a Database issue so I believe the question was satisfactorily answered.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
I've requested that this question be deleted for the following reason:

I said this is no more needed as there is no "fine" resolution given.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now