0

I installed sqlcmd, bcp and odbc 17 for microsoft.

then I connected to Azure SQL with SQLCMD - it worked OK

then I tried to import some data with BCP and i got an error

I use:

 bcp schema.table_name in "path to csv file" -S example.database.windows.net -U user_name -P "password" -d schema -c -t ','

SQLState = 28000, NativeError = 18456
Error = [unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'user_name'.
SQLState = 01S00, NativeError = 0
Warning = [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute

My passwords contaains ] % ; , is it could be a problem?

Update:

The issue is that when I use SQLCMD unixODBC uses {} to escape password and it works OK, but when BCP uses unixODBC password is not escaped.

SQL CMD

[ODBC][12517][1530961370.439037][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x1b56e20
            Window Hdl = (nil)
            Str In = [DRIVER={ODBC Driver 17 for SQL Server};SERVER={example.database.windows.net};UID={user_name};PWD={password};WSID={ADCSrv...][length = 145 (SQL_NTS)]
            Str Out = (nil)
            Str Out Max = 0
            Str Out Ptr = (nil)
            Completion = 0
        UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

BCP 

[ODBC][19984][1530962482.429621][SQLDriverConnect.c][726]
        Entry:
            Connection = 0xcb4ef0
            Window Hdl = (nil)
            Str In = [DRIVER={ODBC Driver 17 for SQL Server};server=example.database.windows.net;UID=user_name;PWD=password][length = 109 (SQL_NTS)]
            Str Out = 0x7fff5397ce70
            Str Out Max = 2048
            Str Out Ptr = (nil)
            Completion = 0
        UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

How to fix that? I think about creating a DNS

Vadzim Nemchenko
  • 88
  • 5

1 Answers1

1

For Azure SQL Database, include the database name on the command line:

bcp schema.table_name in "path to csv file" -S example.database.windows.net -U user_name -P "password" -d schema -c -t ',' -d YourDatabase

EDIT:

If your password contains characters which must be escaped (e.g. ';' but not '%'), you need to enclose the password string in braces:

bcp schema.table_name in "path to csv file" -S example.database.windows.net -U user_name -P "{pass;word}" -d schema -c -t ',' -d YourDatabase
Dan Guzman
  • 23,595
  • 2
  • 37
  • 64
  • I defined -d schema (in other words database) – Vadzim Nemchenko Jul 07 '18 at 12:09
  • @VadzimNemchenko, can you confirm the "schema" specified with `-d` is actually the Azure SQL Database name? This is probably not the same as the schema qualifying the table name unless you happened to name both the same. FYI, I did test this BCP command on Ubuntu and I get a login failed when the -d parameter value is incorrect. It worked as expected when the database name is valid even with a password containing "%" characters. – Dan Guzman Jul 07 '18 at 12:23
  • @VadzimNemchenko, I edited my answer for the semi-colon case. – Dan Guzman Jul 07 '18 at 12:30
  • yes, schema in -d is Auzre SQL Database . schema.table_name I mean dbo.table_name – Vadzim Nemchenko Jul 07 '18 at 12:30
  • my password also includes ']' character, this raises an error, i think. " " does not help, see PWD=***]fsdfsdfsa]fdsf , IT does not intreprete the incoming password correctly – Vadzim Nemchenko Jul 07 '18 at 12:32
  • "{pass[;[ik]@]}" it worked, GOSH, i tried {""} and it DID NOT. thanks to you, 4 hours wasted – Vadzim Nemchenko Jul 07 '18 at 12:38
  • @VadzimNemchenko, I had no problems with "]" but found that "}" needed to be doubled up when the password is enclosed with curly braces. – Dan Guzman Jul 07 '18 at 12:43