Bcp Error 18456
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss
Bcp Native Error 18456
the workings and policies of this site About Us Learn more about sqlstate 28000 native error 18456 sql server Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions
Bcp Sqlstate 28000 Native Error 18452
Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. sqlstate 28000 native error 18456 login failed for user Join them; it only takes a minute: Sign up SQL 2012 bcp call returns SQLState = 28000. NativeError = 18456 Login failed for user up vote 2 down vote favorite I’m working with a SQL stored procedure that makes a call to xp_cmdshell. xp_cmdshell has been enabled, and has a proxy account set to ‘vpexporter’. This sproc was bcp unable to open bcp host data-file designed to write out a data file to disk. This sproc had been working when it was on a SQL 2005 server. The environment has been upgraded to SQL 2012 and the sproc no longer runs. The line making the call is: set @sql1 = 'bcp "SELECT * FROM dbo.udPayrollOutput" queryout "D:\Repository\Exports\' + @fileunique -Uvpexporter -Ppassword -c -t,' exec master..xp_cmdshell @sql1 Running this in SSMS gives me the following: SQLState = 28000. NativeError = 18456 Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘vpexporter’. I have tried this with both a SQL login and with a domain account. Both return the same error. The ‘vpexporter’ has been added as a login, and has been set up as a user of the master db with execute permissions on xp_cmdshell. I feel something must have changed in how xp_cmdshell is called with SQL version 2012, but I haven’t found anything when googling that. I tried running "exec xp_cmdshell 'whoami.exe'" which returned 'nt authority\network service' which is the account SQL Server is running u
C:...\Test_Export.xls -c -U UserName@ServerName.database.windows.net -S tcp:ServerName.database.windows.net -P xxxxxxxx --> It Works BUT, when i want do the same with SQL Query in the command, like this : bcp
Native Error 18456 Crystal Reports
"Select field1, Field2 FROM [dbo].[ForecastTrialDisag]" QUERYOUT C:..\Test_Export.xls -d [DataBaseName] -c -U UserName@ServerName.database.windows.net -S tcp:ServerName.database.windows.net error = [microsoft][odbc driver 11 for sql server]unable to open bcp host data-file -P xxxxxx I have errors : SQLState = 37000, NativeError = 4060 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot
Error = [microsoft][sql Server Native Client 10.0]unable To Open Bcp Host Data-file
open database "[DataBaseName]" requested by the login. The login failed. SQLState = 28000, NativeError = 18456 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'UserName'. I don't understand why it doesn't http://stackoverflow.com/questions/29611600/sql-2012-bcp-call-returns-sqlstate-28000-nativeerror-18456-login-failed-for work with a SQL query. UserName, databaseName and password are OK. Thank you for your help, Simon Moved by Jambor yao - MSFTMicrosoft contingent staff, Moderator Tuesday, December 17, 2013 7:00 AM December 16th, 2013 12:26pm Hi, This thread seems like SQL Azure issue, I will move this thread to SQL Azure forum for a better support. Best Reagrds Free Windows Admin Tool Kit Click here and download http://www.networksteve.com/forum/topic.php/Export_Using_BCP_with_Query_(QUERYOUT)/?TopicId=41541&Posts=4 it now December 17th, 2013 10:00am Hello, I try to reproduce the issue in my test environment, however, it is works well. Based on your description, it seems that you use-d [databasename], how about just -d databasename ? If the database name contains a space, based on the BCP utility: to specify a database name that contains a space or quotation mark, you must use the -q option. Regards, Fanny Liu December 17th, 2013 2:07pm Hello, Thanks for your help. I tri using the option -q, but it didn't work. C:\Users\... > bcp "SELECT TimeId,Quantity FROM [dbo].[Table]" QUERYOUT C:\Users\...\Desktop\Test_t.xls -q -d [XXXX.YYYY.ZZ] -c -U NameUser@ServerName -S tcp:SerName.database.windows.net -P xxxxxx I have the error : ------------------------------------------------ SQLState = 37000, NativeError = 4060 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "[XXXX.YYYY.ZZ]" requested by the login. The login failed. ------------------------------------------------------ SQLState = 28000, NativeError = 18456 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NameUser'. ------------------------------------------------------- SQLState = 01000, NativeError = 40608 Warning = [Microsoft][SQL Server Native Client 10.0][SQL Server]This session has been assigned a tracing ID of 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx'. Provide this tracing ID to customer support when you need assistance. Any idea ? Thanks for your help, Simon Free Wind
sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. http://www.dbforums.com/showthread.php?1673450-BCP-Password-Issue To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 10 of 10 Thread: BCP Password Issue Tweet Thread Tools Show Printable Version https://katieandemil.academy/microsoft-sql-server-error-18456-login-failed-for-user Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 01-12-12,05:12 #1 buzmay View Profile View Forum Posts Registered User Join error 18456 Date Jan 2012 Posts 52 Unanswered: BCP Password Issue Hi guys, I desperately need help with my BCP script everything works good so far I do not add in the script my password as -P actualpassword However I would like to know how to do this in the script since I need to import plenty of files into mysql server. The script I have native error 18456 looks like this @echo off bcp TestDB3.dbo.xxx_data in "Z:\path\data\XXX.txt" -F 2 -S servername -U MY_USER_NAME -f "Z:\path\data\format-data.fmt" pause the above script works fine but, asks me all the time to prompt my password. However if if I try to add the password as in the following scrip it will complain that the login has failed: @echo off bcp TestDB3.dbo.xxx_data in "Z:\path\data\XXX.txt" -F 2 -S servername -U MY_USER_NAME -P xxxxxxx -f "Z:\path\data\format-data.fmt" pause Any ideas how to fix this? Thanks in advance. Reply With Quote 01-12-12,05:25 #2 gvee View Profile View Forum Posts Visit Homepage www.gvee.co.uk Join Date Jan 2007 Location UK Posts 11,422 Provided Answers: 10 SQL login or Windows authentication? George Home | Blog Reply With Quote 01-12-12,05:27 #3 buzmay View Profile View Forum Posts Registered User Join Date Jan 2012 Posts 52 sql server authentication Reply With Quote 01-12-12,06:35 #4 gvee View Profile View Forum Posts Visit Homepage www.gvee.co.uk Join Date Jan 2007 Location UK Posts 11,422 Provided Answers: 10 I wouldn't have thought having a command file with both the username and password to an SQL account that has permissions to modify data is a good id