SSRS 2005 report: Cannot bulk load Operating system error code 5(Access is denied.)

Question

Post reply

SSRS 2005 report: Cannot bulk load Operating system error code 5(Access is denied.)

nzhang6666

SSCrazy

Points: 2217
More actions
March 26, 2014 at 8:43 am

#287035

I built a SSRS 2005 report, which calls a stored proc on SQL Server 2005. The proc contains following code:
CREATE TABLE #promo (promo VARCHAR(1000))
BULK
INSERT #promo
FROM '\\aseposretail\c$z\promo_names.txt'
WITH
(
--FIELDTERMINATOR = '',
ROWTERMINATOR = ''
)
SELECT * from #promo
It's ok when I manually execute the proc in SSMS.
When I try to run the report from BIDS I got following error:
*Cannot bulk load because the file "\aseposretail\c$z\promo_names.txt" could not be opened. Operating system error code 5(Access is denied.).*
Note: I have gooled a bit and saw many questions on this but they are not relevant because I CAN run the code no problem in SSMS. It's the SSRS having the issue. I am gussing it's related to the security of SSRS but know little about this.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

When you attempt to run this in BIDS, are you using the same account that you use to run SSMS?

Many people use different accounts to launch SSMS versus to launch BIDS.

Once you confirm that, and get it working in BIDS, then we can move on to getting it to work via SSRS.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

nzhang6666 SSCrazy Points: 2217 More actions · Answer 2

SQLRNNR (3/26/2014)
When you attempt to run this in BIDS, are you using the same account that you use to run SSMS?
Many people use different accounts to launch SSMS versus to launch BIDS.
Once you confirm that, and get it working in BIDS, then we can move on to getting it to work via SSRS.

How to check the account used by BIDS? What I did was I logged on the server then start the BIDS from windows-start. And The same for SSMS and SSMS is using windows authentication.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

If you are using the same session you are probably using the same account. You didn't use a runas in the shortcut for bids or ssms did you?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

nzhang6666 SSCrazy Points: 2217 More actions · Answer 4

Yes same session and not using a run as.

--update,

Yes, I confirm bids is running as the same user as SSMS. I printed the UserID global variable in a test report.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

when you past this path into a run window (start->run), does it produce an error or open an explorer window with contents?

'\\aseposretail\c$z\

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

nzhang6666 SSCrazy Points: 2217 More actions · Answer 6

Yes, I can open the location no problem.

Btw, I forgot to mention, the BIDS and SSMS are running on a different server than aseposretail

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

I wonder if your share name is causing some issues behind the scenes. C$Z might be trying to resolve to the admin share of C

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

x SSC-Insane Points: 23660 More actions · Answer 8

You could create a test SSRS report, and just as a data source 'SELECT SYSTEM_USER' or some variation of http://technet.microsoft.com/en-us/library/ms191126(v=sql.105).aspx user id system variables to see what SQL (or windows) account is actually going to run that bulk load statement from within ssrs and then go about adjusting privileges from there.

nzhang6666 SSCrazy Points: 2217 More actions · Answer 9

SQLRNNR (3/26/2014)
When you attempt to run this in BIDS, are you using the same account that you use to run SSMS?
Many people use different accounts to launch SSMS versus to launch BIDS.
Once you confirm that, and get it working in BIDS, then we can move on to getting it to work via SSRS.

It's been a while, but I re-looked at it today and found it's working in BIDS now (used to be not working in BIDS either). Don't know the reason. So it works in BIDS but not in report server when deployed. Next step?