none
Logon Failure: unknown user name or bad password - using xp_cmdshell or bulk insert

    Question

  • We had some SQL Server automated web routines that stopped working when some security patches were applied either to SQL Server 2000 or Windows Server 2000.  The Web users would upload a file to a directory on the web server.  Then a Stored Procedure would be executed that would do a bulk insert into our SQL Server table.  The bulk insert now fails giving this error #1326:  Logon Failure: unknown user name or bad password.  It used to work flawlessly.  The share that is the upload folder on the Web server allows proper permissions for access.

    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>\filename.txt  \\<SQLServerbox>\<directorypath>\'
    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.
    Thursday, March 16, 2006 10:55 PM

Answers

  • My problem is resolved.  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.

    Monday, March 20, 2006 5:37 PM

All replies

  • This article might help:

    http://support.microsoft.com/?kbid=892426

    Let us know if it doesn't.

    Thanks
    Laurentiu

    Friday, March 17, 2006 8:19 AM
  • The article you referred me to I had already printed and didn't find it helpful.  It is regarding replication and we are not replicating anything.  We are only trying to access a shared folder on the web server from SQL Server on the database server using xp_cmdshell or bulk insert.
    Friday, March 17, 2006 4:14 PM
  • The article is not about database replication, it's about replication of domain information. You may want to check with your network administrator.

    The reason why you probably can execute the commands from the command prompt but not from xp_cmdshell is that you are running under different contexts. xp_cmdshell will execute as the SQL Server service, unless you set a different proxy account. Have you tried running the copy command from a command prompt that is running under the same credentials as the SQL Server service (using runas)?

    Thanks
    Laurentiu

    Friday, March 17, 2006 7:12 PM
  • to copy from a unc share to another your sqlserver service acct (if you login to sql as sysadmin) or agent proxy acct (if you login to sql as non-sysadmin) needs proper permissions to access the network resources.

    This kb should help:

    http://support.microsoft.com/?id=264155

     

    Friday, March 17, 2006 8:35 PM
  • I had already tried using an agent proxy acct.  Also, the account running the SQL Server has sysadmin rights.
    Friday, March 17, 2006 10:59 PM
  • My problem is resolved.  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.

    Monday, March 20, 2006 5:37 PM