Permission Issue ( I think) - Help urgently

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Permission Issue ( I think) - Help urgently

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-26 : 14:02:23

Dear experts,

I am not real sure if this is where to post this but I hope someone can tell me if this isn't the right forum.

We have a web app (ASP) that has been running on our server for the past 1 year.

We recently had a perform issue ( I think I posed that question here) when the server was so slow that it was timing out.

As soon as we resolved that, a portion of the application stopped working.

The portion that stopped working is the portion that uses xp_cmdshell to copy an an image from a mapped remote server to local server.

Here is that snip:

exec master.dbo.xp_cmdshell 'copy z:\dd5\photos\thumbnails\2006\12\YY72T.JPG c:\inetpub\wwwroot\imagefolder\'

As you can see, master.dbo.xp_cmdshell copies a photo called YY72T.jpg from a folder in a remote folder called dd5\photos\thumbnails\2006\12\ and copies this image to local folder on c:\inetpub\wwwroot\imagefolder

If I just run this code in command line:

copy z:\dd5\photos\thumbnails\2006\12\YY72T.JPG c:\inetpub\wwwroot\imagefolder, it copies the image to that folder.

However, if I run this code below

exec master.dbo.xp_cmdshell 'copy z:\dd5\photos\thumbnails\2006\12\YY72T.JPG c:\inetpub\wwwroot\imagefolder\'

from query Analyzer, I get a message that system could not find path.

I am almost convinced that this is a permission issue but where should I be looking at?

Any help would very be appreciated

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-26 : 14:23:57

It has to have permission for the account that xp_cmdshell is using.

CODO ERGO SUM

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-26 : 14:31:03

hi Michael,

Thanks for a quick response.

What has to have permission, the remote that xp_cmdshell is copying from or the local server it is copying?

Also, do you by any chance know why this used to work and suddenly stopped working?

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-02-26 : 15:13:11

Presumably when the service was restarted, an environment variable (namely the mapped drive) was not reset properly. Try mapping the drive with the NET USE command first via xp_cmdshell in Query Analyzer, then as part of the job. After that, you will have to see if you can get the drive mapping to become permanent, or you will end up with another performance problem as each run of the command tries to map or re-map the drive.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-26 : 15:31:28

NET USE command first via xp_cmdshell in Query Analyzer

thanks for your response and sorry to inconvenience you further but I have not used this command before.

Can you please give me an example?

Thanks for your assistance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 15:42:44

What is the Z drive? Is it a mapped drive, a local drive, a SAN drive, ...?

What does this show:
EXEC master.dbo.xp_cmdshell 'dir z:\dd5\photos\thumbnails\2006\12'

Tara Kizer

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-26 : 15:52:40

hi Tara,

This line: \\dd5\photos\thumbnails\2006\12

is part of the the remote server. The fully qualified address is:

\\IPAddress\dd5\photos\thumbnails\2006\12\ Then image.jpg.

The Z is the letter drive on the local server that the above server is mapped to.

What does this show:
EXEC master.dbo.xp_cmdshell 'dir z:\dd5\photos\thumbnails\2006\12'

If I run it in query analyzer, it says system path not found

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 16:06:55

Then the Z drive is not mapped on the database server.

You have to remember that xp_cmdshell runs from the server, not your client.

Assuming you are a sysadmin when running xp_cmdshell, so this is not a permissions issue, but rather a Z drive issue.

What I would do is log into the database server from the console/Remote Desktops/Terminal Services/... as the account that the SQL Server service uses, then I'd check Windows Explorer for the Z drive. It probably won't be there, so you'll need to figure out why.

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-26 : 16:34:26

It would be simpler to just change your command to use the UNC path to the file, instead of using a drive mapping.

exec master.dbo.xp_cmdshell 
  'copy \\IPAddress\dd\photos\thumbnails\2006\12\YY72T.JPG c:\inetpub\wwwroot\imagefolder\'

CODO ERGO SUM

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-26 : 16:40:48

Thanks Tara,

Please bear with me as I am trying to make some sense out of what you are telling me.

When I went to services (Control Panel -> Administrative Tools -> Services) I see that SQL server is using system account.

To get in using remote desktops, you use another account (as far as I know).

Now, when I log into the server, since the server is next to me, I can see the Z drive still mapped to that remote location.

So, what am I missing?

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-26 : 16:46:59

Michael,

even with that, I still get:

The network path was not found.
NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 16:51:08

simflex, I don't think the system account will have access to the Z drive. How is the Z drive being mapped when you log into the server? Is there a login script that runs that adds that for you? Is there a startup script that runs?

For the SQL Server serice, you should be using a Domain account that has local admin privileges on the database server. This will make xp_cmdshell and other stuff so much easier!

Tara Kizer

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-26 : 22:50:07

Ok, Tara,

If you are talking about SQLServerAgent services, I have ensured that that account is a domain account but still no love.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-27 : 00:45:35

I am talking about the SQL Server service not the Agent. It would require a restart of the service if you make a change to it.

Tara Kizer

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-27 : 13:08:44

Thanks for all your help, Tara but my big concern is that since I took over the responsibility of managing this server, the Log On account for MSSQLSERVER has always been the Local System Account and yet the xp_cmdshell extended stored proc always worked.

I am afraid the change the local system account. what if it didn't work and what I can't get the local system account to work anymore?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-27 : 13:36:48

Then you need to figure out why the Z drive doesn't exist for the System Account. You need to find out how that Z drive gets mapped. There's no way for us to find that out for you, which is why I'm suggesting changing the service account. At least by changing the service account to a "real" account, then we can have you try things to get it to work again.

Tara Kizer

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-27 : 13:51:17

ok, I have changed the permission to a domain account.

I have stopped and restarted sql server service but still no luck.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-27 : 13:53:33

Now we can at least test things out. Log into the database server using the Domain account that you switched the service to. Do you see a Z drive there? If not, then that's the problem. If you do, then it's a permissions issue.

Tara Kizer

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-27 : 14:00:30

I created one yesterday before changing SQL Server service to domain account now.

After changing the domain account, I disconnected and reconnected just to be sure but still no love.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-27 : 14:13:52

But did you check if the Z drive exists when the SQL Server service account is logged into the server? This is a very important thing to check. You need to get to the console of the SQL Server and login with that account. Then you need to check Windows Explorer for the Z drive.

If it does exist, then while still logged into the console with the service account, you need to verify if you can open:
z:\dd5\photos\thumbnails\2006\12\YY72T.JPG. Just double click on the file from Windows Explorer. Does it open without errors?

Tara Kizer

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-02-27 : 21:13:27

You asked:

But did you check if the Z drive exists when the SQL Server service account is logged into the server?

This is what I understand your question to mean:

1, Login into the server with co domain account, go to Enterprise Manager, ensure that the account that is logged into sql server is the same domain account.

Then go to Windows explorer, and
(a) ensure that the Z drive exists. If yes, navigate to the file (the image I suppose) and double-click on it and see if it shows.

If this is what you meant by the question above, then yes I have done all of that but still cannot get the exec xp_cmdshell script to work because I am still getting same error and when I run the asp application, the image is still not displaying.

This is so frustrating it is difficult to explain.

I am reasonably sure there is a permission issue somewhere.

I just don't know where.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources