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

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
  • I defined -d schema (in other words database) – Vadzim Nemchenko Jul 7 '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 7 '18 at 12:23
  • @VadzimNemchenko, I edited my answer for the semi-colon case. – Dan Guzman Jul 7 '18 at 12:30
  • yes, schema in -d is Auzre SQL Database . schema.table_name I mean dbo.table_name – Vadzim Nemchenko Jul 7 '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 7 '18 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.