SQL Server - SQL Server/ bcp 工具如何通信

阅读目录(Content)

问题-BCP通讯
解答-BCP通讯
扩展-SQL Server / BCP通讯协议种类

问题-BCP通讯

ref: https://stackoverflow.com/questions/40664708/bcp-cannot-connect-to-aws-sql-server-but-ssms-can

Anyone know a good reason why bcp cannot connect to a sql server hosted by AWS while SSMS can? I have double checked the server and user account details and they both match.

I'm using the generic command to import a csv file:

bcp DB_Name.dbo.Table in "somefile_file.csv" -c -S xxx.rds.amazonaws.com -U username -P xxx -b 1000

The error is:

SQLState = 08001, NativeError = 53
Error = [Microsoft][ODBC Driver 13 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. 
SQLState = 08001, NativeError = 53
Error = [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired

回到顶部(go to top)

解答-BCP通讯

The first error shows that bcp uses the Named Pipes protocol to connect to the server. Apparently, this protocol is inaccessible for network connections.

On the client workstation where bcp is run, check SQL Server Configuration Manager and ensure that, in the client configuration list TCP/IP is enabled and prioritised. Strangely enough, bcp doesn't have a command line switch for protocol selection, so this is the only way to manage it I can think of.

P.S. Another option is to specify the server address in the form ip_address,port_number, but I don't think you will find this option particularly attractive.

Other comments:

In SSMS the network protocol is default. I don't have a local SQL Server. May it be that I need to enable trust server certificate? – chhenning Nov 18 '16 at 3:10

You should have SQL Server client tools on this machine, otherwise how the bcp.exe appeared there, in the first place? And no, this doesn't look like a certificated-related issue. – Roger Wolf Nov 18 '16 at 9:47
Finally got SQL Server Configuration Manger to run. The order is Shared Memory (1), TCP/IP (2), and Named Pipes (3). Still, even when I disable Named Pipes I get the same error message. – chhenning Nov 19 '16 at 3:48
Put TCP/IP into first position. Also, note that there are 2 separate client configurations, for 32-bit and 64-bit clients, and I'm not sure which one bcp uses. Make sure you have set both of them properly. – Roger Wolf Nov 19 '16 at 4:17