I am facing a problem with xp_read_file(), which is returning NULL in case i am trying to access a network drive path.

e.g. dbo.xp_read_file('C:db.log') works fine and return the data in binary.

but if i just try to read the file from any network path or mapped drive like dbo.xp_read_file('Z:db.log') it return NULL.

The same behavior is happening with xp_write_file() and xp_cmdshell().

Note: If i am running the database manually this is working fine i.e. without creating service, but if i create a SERVICE for the database it stop working and return null.

This is something a very strange behavior not sure if it is a problem with the permissions (i have an administrator rights on Domain) or some settings to be done at database level.

Request if anyone from you have faced the same problem can please share your thought.

This question is marked "community wiki".

asked 11 Apr '11, 11:28

Ramendra's gravatar image

Ramendra
1115
accept rate: 0%


Services typically do not have access to network drives on Windows. You need to run the service as another user - one that has the appropriate permissions - if you want your service to access the network.

permanent link

answered 11 Apr '11, 11:50

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264
accept rate: 40%

As an alternative to Mark's suggestion, you can let the database service run as before and make the database server temporarily connect as another user who has network access.

This is discussed in this answer.

permanent link

answered 11 Apr '11, 12:06

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

Ramendra, have you tried any of the options in this link above?

(12 Apr '11, 14:28) Siger Matt

Hi Siger, yes i have tried the options. Still facing the same problem.

(12 Apr '11, 15:29) Ramendra

Even while running the Service with the account who has all the permissions (Administrator), i am facing the same problem. It's returning NULL

Even no result with DOMAIN User.

Is there any specific way to configure it. I am simply "Log On" Tab for the service under Windows services...

permanent link

answered 11 Apr '11, 12:06

Ramendra's gravatar image

Ramendra
1115
accept rate: 0%

edited 11 Apr '11, 12:20

Just to assure: Does the Administrator have the according network permissions (i.e. can he read from drive Z:)? Does ist work with an UNC path? And when using a drive letter - is the network path specified as absolute path? (As the service may use a different working dir for the drive than the uínteractive user.)

(11 Apr '11, 12:25) Volker Barth

Yes the user has the permission and can read the file externally. No it's not even working with UNC path. Yes even the absolute path don't work.

(11 Apr '11, 12:28) Ramendra
Replies hidden
2

I would expect that you will need to use the UNC path, as Z: is normally just visible to the logged in user session and the database server will use a different session even if using the same credentials. Anyway the db service will run in a different session even if using the same credentials. You can use Process Monitor from Microsoft Technet and log the file access attempts, you will see what the error is and also which credentials are used to access the UNC path.

(15 Apr '11, 08:53) Martin

Accessing SMB/CIFS/NetBIOS shares as the standard SERVICE account in Windows with the standard security policy doesn't work. When you log in to a Windows box and map a network drive, the mapping only exists in the logged-in user's context. You didn't log in as SERVICE, so the SERVICE user doesn't see the share you mapped. This is correct from a security perspective. If Jane and John, for example, both log in to the same Windows box at the same time, and Jane maps a network drive, John shouldn't have access to that drive (John himself needs to authenticate himself to the SMB server).

I would suggest the following alternatives:

  1. Install a web server on the computer which has the file you want to access, and use a web service client function in the database to access the file.
  2. Use a command-line smb client via xp_cmdshell() to transfer the file to the database server before you read it. For example, https://www.leepa.io/lpackham/smbclient/ . Be sure to write a wrapper procedure that calls xp_cmdshell() then xp_read_file() and then ALTER PROCEDURE sp_whatever_you_call_it SET HIDDEN. You will be embedding a username and password in your xp_cmdshell() parameter, so you don't want to leave that visible in the SYSPROCEDURE view.
  3. Install Interactive SQL and ODBC on the computer where the file you need to read resides. Set up a scheduled task to push the file into a table or a database-scope variable using read_client_file().

Alternative 2 allows you to make no changes to the file server, so it's probably best for you.

permanent link

answered 05 Oct '16, 09:48

mmellon's gravatar image

mmellon
86117
accept rate: 0%

You didn't log in as SERVICE, so the SERVICE user doesn't see the share you mapped. This is correct from a security perspective.

Please note that the older suggestions (like mine) do respect that problem, as the service itself establishes the drive mapping, so it's done within the fitting session/user context, and therefore this certainly does work.

(05 Oct '16, 15:25) Volker Barth

Is there any way through Windows log to identify what the real problem is. If someone who faced the same kind of problem, please share.

permanent link
This answer is marked "community wiki".

answered 11 Apr '11, 14:36

Ramendra's gravatar image

Ramendra
1115
accept rate: 0%

edited 12 Apr '11, 14:22

May you show some more details, e.g. the exact call you are making? Or can you reproduce this with one of the sample SA databases?

BTW: If you raise another question on this topic instead of an answer, you may make use of comments (on existing answers or comments) rather than adding answers - that's just a useful feature in this forum:)

(12 Apr '11, 15:47) Volker Barth

Sure, i will get back to you with more details soon. Thanks

(12 Apr '11, 15:51) Ramendra
Replies hidden

That's good to know. In general, xp_read_file does work with network paths when the service has according permissions. That's the reason we cannot give more help without having more information, methinks.

(12 Apr '11, 16:02) Volker Barth
Comment Text Removed

Ramendra, I'm just a customer and no Sybase/iAnywhere staff member (in contrast to other users of this forum). Therefore I would prefer not to get involved via e-mail. I would recommend you to

  1. show (parts of) your code and/or error messages as ordinary text here (omitting the need of certain rep points) or
  2. ask iAnywhere folks (like Mark Culp) to get involved vie e-mail or
  3. use the Sybase NNTP newsgroup (i.e. sybase.public.sqlanywhere.general) to post your question (which is monitored by several iAnywhere support members and supports the upload of attachments, too).

I'd prefer the first option as it will keep the discussion here and will it make easier to look at the code. AFAIK, it's no problem to add long lines of code in text form here.

(14 Apr '11, 11:24) Volker Barth
Comment Text Removed
Comment Text Removed
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13

question asked: 11 Apr '11, 11:28

question was seen: 2,317 times

last updated: 05 Oct '16, 15:25