March 16, 2006 at 3:46 pm
I tried to see if I could copy the file first using the xp_cmdshell because the bulk insert works fine if we manually copy the file to the SQL server directory.
Using xp_cmdshell in the query analyzer I tried:
execute master..xp_cmdshell 'copy \\<WebserverIPAddress>\<directorypath>\f
I get the same error.
I tried a simple command to access the web server folder using:
execute master..xp_cmdshell 'dir \\<WebserverIPAddress>\<directorypath>'
I get the same error. I tried adding variations adding /logon: /user: and /password in other questions/answers I found on this site with no success.
I tried explicity assigning the proxy account as suggested in some posts using Ent Mgr/Management/SQL Server Agent/Job System and it still doesn't work.
The curious thing is that if I go onto the SQL Server box and run the identical copy command from a command prompt it works fine. So I thought that rules out the firewall as being the problem, doesn't it?
I don't know if it matters but the SQL Server box is on our local domain and the Web server is a seperate workgroup. However, as I mentioned earlier I can copy from the Web Server folder to the SQL Server folder using a DOS prompt on the SQL Server box. If the SQL Server box has access to the Web Server folder from a DOS prompt, why can't I run the same command using xp_cmdshell or run bulk insert from the folder?
I am quickly approaching a deadline to get this fixed. We have been limping along running these processes manually as workarounds for months and that is no longer going to be acceptable. Thanks.
March 17, 2006 at 2:02 am
Hi,
U are using the ip address which will not work with BCP give the ServerName and give logon user permission to the folder u are copying the file.
Ex: Server.rs.com
if ur server name is like rs-it-gg.rs.com then also BCP won't work
from
Killer
March 17, 2006 at 10:54 am
Can you elaborate on how to include the logon user permissions using bulk insert or xp_cmdshell from SQL Server? I was using the IP address because that works when I enter the copy command from a DOS prompt on both my machine or from the database server. But I have tried it with the server name also and that didn't work. The web Server is not set up as a domain but as a workgroup. The database server is our local domain. We are trying to access the web server from the database server. It works from a command prompt on the database server but not from within SQL Server on the database server.
March 19, 2006 at 8:42 pm
Hi. I imagine your problem is actually due to the account that you are using to run MSSQLSERVER service. If it is LOCALSYSTEM, then it will not have access to a computer across the network - perhaps it used to have access due to a guest account being enabled on the other computer that is no longer enabled??
Try running MSSQLSERVER as a user that has rights on the other computer - easiest way to do this (since your other machine is on a workgroup) is to have a user account + password that is the same on both boxes and have MSSQLSERVER run under that account... Might not be the most secure - but it should work. You can do the copy from the command prompt because you as the user has access to the other computer - but the user account that MSSQLSERVER is using does not.
Cheers
March 20, 2006 at 2:01 am
Unlikely given the scenario. Even logged in interactively xp_cmdshell would execute under the context of the instance service.
Can you post the error message verbatim, and check the application and security logs on the SQL server as you submit the query?
March 20, 2006 at 10:05 am
My problem is fixed. I had inquired with a former colleague of mine and he knew exactly what the problem was. I thought I would post his response to assist anyone else who may come accross the same problem. I know how frustrating it is to search for a solution, only to find someone say "they fixed it", without explaining how.
Thank you also to all who replied to this post. It is our shared knowledge that keeps us all going.
It turned out, the logon on the web server that was the same logon that the SQL Server uses on the database server was messed up in some way. I just had to delete the logon from the web server and add it back using the same user name and password that the database server uses.
His reply to me:
Well as you know there is a permissions issue here, hehehe. This
sounds like an easy fix. Check local accounts on local machines to see
if the account settings got greyed out. Why??? Because for some reason
too many attempts may have expired the account, solution: reset password
and try again. I suggest you find out the password exactly as it was.
Whether on a domain or workgroup, permissions must be set. In a
workgroup, workgroup\it00228\jbob and or even domain\it00229\jbob they
can see each others computers if I add accounts locally with the exact
same password. In a domain evironment I can add domain or non domain
accounts as well.
error #1326: Logon
> Failure: unknown user name or bad password. Again time and time again
I always check local accounts on local machines to see if the account
settings get greyed out. Why??? Because for some reason too many
attempts may have expired the account, solution: reset password and try
again. I suggest you find out the password exactly as it was.
Everyone permissions means nothing but only with every account within
the domain not outside. Remember that unless you are a domain
controller you have local accounts as well, and those are workgroup
accounts. So in my script I transact a file using
workgroup\it00228\jbob to domain\it00229\jbob but if it become disabled
I get the unknown user.
If the SQL
> Server box has access to the Web Server folder from a DOS prompt, why
> can't I run the same command using xp_cmdshell or run bulk insert from
the folder? Answer: because you are taking the position of an logged
in called x, but y account is what is being used to do the process that
is either timed out and locked up, or deleted. You are logging in as a
functional account. Of course it would work, try logging as the account
doing the real work then you see reality.
March 21, 2006 at 4:09 am
Ugh. Account mirroring - you'll get this problem frequently if you use it.
Thanks for posting the resolution.
March 22, 2006 at 9:18 pm
Think you misunderstood what I was saying - I wasn't saying that the user should be logged on as an account, but the SQL Server account should be changed/examined.... Turned out that was the problem.
April 28, 2011 at 3:22 am
Thank you very much Ten.
Your solution helped me greatly.
We restarted the SQL Server service and changed the password of the domain account. Post that our bcp command worked great.
All other posts provided the solution that to make sure the service account have necessary access. In our case both domain account and service account are same. From command prompt bcp worked. But it doesn't work using xp_cmdshell. We got the error stating "Unable to open BCP host data-file"
Finally based on your suggestion we reset the password and it worked.
Thank you once again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply