Permission to read a file over the network

Question

Post reply

Permission to read a file over the network

Atle Kåven

Old Hand

Points: 335
More actions
January 13, 2008 at 4:41 am

#123113

I have a SQL Server box and a IIS box. I need to run a stored proc that reads an uploaded file from the IIS box. The stored proc is like this;
DECLARE @xml xml
SELECT @xml=CAST(BulkColumn as xml) FROM OPENROWSET(BULK N'\\IISServer\Data\MyData.xml', SINGLE_BLOB) A;
Problem is that I get "Access denied" reading the MyData.xml when logged on as "sa" user (or any other login I've created). If I log into Managment Studio with Windows Authentication, there is no problem.
So question is how can I give the login the permission to read from the network drive?
Atle

Viewing 8 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

The user that the SQL Service logs in as, must have permission to "see" the share.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 2

The sql login created by you or for that matter sa would not have access to the network resource. So whenever you run the job you get the access denied error where as the windows login you use must have got access to the netwrok share.

If you want to run the job by sa or any other login then create a credential and associate the sql login to that credential and then you will not get the error.

Else you can use the Execute As option to execute te procedure.

Atle Kåven Old Hand Points: 335 More actions · Answer 3

Thanks!

But it won't work. As a test I created a CREDENTIAL using the Windows server 'administrator' login (that login shoud have all needed permissions I should believe)

CREATE CREDENTIAL AdminCred WITH IDENTITY = 'administrator',

SECRET = 'xxxzxxxxaa';

GO

I added the credential to a SQL login named IIS like this:

ALTER LOGIN IIS WITH CREDENTIAL = AdminCred;

If I log on SQL Server as IIS I still get "Access is denied". Can you see what I might have done wrong?

Atle

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

Heh... lemme say it again...

The user that the SQL Service logs in as, must have permission to "see" the share.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Atle Kåven Old Hand Points: 335 More actions · Answer 5

Yes. That did the trick, Thanks Jeff!

Sorry I didn't check out your post good enought. Second try - it worked.

Atle

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

Perfect... thanks for taking the time to post your feedback!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

einman33 SSCrazy Points: 2813 More actions · Answer 7

I am still pretty old school in regards to my SQL sevice account accessing file resources.

Now of course every environment is different, but I have seen way to much access given to the SQL Service account at the network level in recent years. I mostly blame ease of use of the sql client, but this is a big security problem in my opinion.

This is not pointed at the poster but a general observation.