BCP connection failed

Apr 26, 2010 at 1:45 PM

Hi,

I tried for the first time the MW to "download" locally an Azure database. On the dev computer, everything works fine (VS 2010, SSMS 2008 R2...) but the MW fails to get data from my tables.

After tracing the source code, I found that BCP fails to connect to the database and this post http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/3504cfcd-dd62-4ffd-a8d3-152d29ed4ad1 seems to indicate that BCP actually does not work with SQL Azure.

Does anyone know if I missed something?

Coordinator
Apr 26, 2010 at 6:46 PM

Hi,

BCP out works well with SQL Azure.  The post link that you listed above says that BCP queryout is currently not supported.  For example:

1. BCP out --> bcp Northwind.dbo.Customers out Customer.dat -E -q -n -S jr9kadhyma.database.windows.net -U UserSA@jr9kadhyma -P MyPwd
2. BCP queryout --> bcp "Select * From Northwind.dbo.Customers" queryout customer2.dat -S jr9kadhyma.database.windows.net -U UserSA@jr9kadhyma -P MyPwd

SQLAzureMW uses BCP as in option 1.  I tried option 2, but it does not seem to work for me.  So, it looks as if the article is correct in that BCP queryout is not supported.

But, BCP out is supported and that is what SQLAzureMW uses.  What I have found is that BCP requires username (-U) to specify @servername (see above example 1).  If you don’t have that, then it will fail (Server name cannot be determined).  So, be sure that when using SQLAzureMW that you specify your user name with the @servername.

Anyway, if you have an SQL Azure database, try example 1 above from a command window and see if it works for you.  If that works, then SQLAzureMW should work.  If you get an error, let me know and I will see if I can help you figure out what is wrong.  Let me know what the error message is.

Regards,
George

 

Apr 27, 2010 at 8:14 AM
Hi, Thank you for your quick reply. My problem seems to be that the default "admin" account does have access to my production database from SSMS but not from BCP. So I created a new login, user, gave it dbo rights on my production database, then on "master" and it worked. Thanks again for your help!
Aug 10, 2010 at 4:19 AM

Having same issue, however how does one grant dbo rights for master to new login? Strange that the default server admin account dosn't have access, since it seems creating a user for it isn't allowed either.

Coordinator
Aug 10, 2010 at 9:04 PM

What kind of error are you getting?

Aug 11, 2010 at 4:07 AM

Hello this only occurs for BCP commands. For example scripting the database in analyze only mode works fine but choosing migrate which I suppose actually tries to get the data fails when tried with multiple databases on different servers like this:

 
Using BCP to get data from table dbo.aspnet_Applications

*

SQLState = 28000, NativeError = 18456

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'roostersqlprod'.

This is logging in as the default server admin account, and attempting to grand roles or permissions for this account on the db in question gives permission errors. 

Thanks.

Coordinator
Aug 11, 2010 at 2:09 PM

Are you trying to export from SQL Server or SQL Azure?  Since BCP is what SQLAzureMW uses, lets take SQLAzureMW out of the equation for now.  Let’s just use BCP.

The below command uses Northwind database.  You can change out the database, table name (Customers), -U (username) and –P (password) to match your setup.  Then try the BCP command and see what  you get:

BCP Northwind.dbo.Customers out Customers.dat -E -q -n -S localhost -U sa -P password

If you are exporting data from SQL Azure, try this BCP command:

BCP Northwind.dbo.Customers out Customers.dat -E -q -n -S jb8kcdhrma.database.windows.net -U sa@jb8kcdhrma -P password

You can see that they are the same commands with just the SQL Azure server name and the @ jb8kcdhrma and the end of the user name.

Try this and let me know how it goes.

Regards,
George 


Aug 11, 2010 at 5:05 PM
Exact same thing, so it is coming out of the bcp command within SQLAzureMW: SQLState = 28000, NativeError = 18456 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for u ser 'roostersqlprod'.
Coordinator
Aug 11, 2010 at 10:21 PM

Yes, I get the exact same error if I type my username or password wrong. So, it looks like a username / password error. Just out of curiosity, can you connect with SQL Server Management Studio with no problem?

Aug 12, 2010 at 3:22 AM

Works with passowrd changes. Sql Server Managment connects no problems. However I did playground with the password cause I know the username was fine. Turns out for BCP command the passwords can't be strings that contain outer {}. Hah kinda funny since passwords are usually any ANSI char, this is about the last thing I would have though causing the problem.

Unless this is some command line param escape char this is a bug in BCP. Couldn't find any way to encode the char for it to work tried {{ and outer ".  So a few hours wasted due to same old, age old problem of character encodings, at least it wasn't new line.