SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Permission Issue ( I think) - Help urgently
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/26/2007 :  14:02:23  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 02/26/2007 :  14:23:57  Show Profile  Reply with Quote
It has to have permission for the account that xp_cmdshell is using.




CODO ERGO SUM
Go to Top of Page

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/26/2007 :  14:31:03  Show Profile  Reply with Quote
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?
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 02/26/2007 :  15:13:11  Show Profile  Reply with Quote
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.
Go to Top of Page

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/26/2007 :  15:31:28  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/26/2007 :  15:42:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 02/26/2007 15:43:22
Go to Top of Page

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/26/2007 :  15:52:40  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/26/2007 :  16:06:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 02/26/2007 16:08:56
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/26/2007 :  16:34:26  Show Profile  Reply with Quote
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
Go to Top of Page

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/26/2007 :  16:40:48  Show Profile  Reply with Quote
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?

Go to Top of Page

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/26/2007 :  16:46:59  Show Profile  Reply with Quote
Michael,

even with that, I still get:

The network path was not found.
NULL
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/26/2007 :  16:51:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 02/26/2007 17:00:36
Go to Top of Page

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/26/2007 :  22:50:07  Show Profile  Reply with Quote
Ok, Tara,

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

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/27/2007 :  00:45:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/27/2007 :  13:08:44  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/27/2007 :  13:36:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/27/2007 :  13:51:17  Show Profile  Reply with Quote
ok, I have changed the permission to a domain account.

I have stopped and restarted sql server service but still no luck.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/27/2007 :  13:53:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/27/2007 :  14:00:30  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 02/27/2007 :  14:13:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

simflex
Constraint Violating Yak Guru

323 Posts

Posted - 02/27/2007 :  21:13:27  Show Profile  Reply with Quote
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.
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000