BCP via Linux Centos 7, cannot connect to Azure SQL

Question

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

score 1 · Accepted Answer · answered Jul 07 '18 at 11:50

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

BCP via Linux Centos 7, cannot connect to Azure SQL

1 Answers1