sqlcmd not connecting to Azure database

Question

I am trying to use the command below to access my database within Azure

sqlcmd -s tcp:DBNAME HERE.database.windows.net -U USERNAME -P PASSWORD

I get the error

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. . Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired. Sqlcmd: 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..

Has anyone ever had this? I have downloaded the latest version of sqlcmd

I need to get a lot of data into my Azure database, but I am running out of options as the Import Data option in management studio cant cope. This was the first method I tried. After processing 70000 of 250000 rows it just goes to stopped with no error message, thats what then led me to try with BCP SQLCMD

Paul

Answer 1

I just want to point out that BCP and sqlcmd are two different tools.

BCP is the bulk copy command. The later is the command line tool for querying.

If you are getting a named pipes issue, you might not have the client and/or server libraries configured.

Please go to SQL Server Configuration manager to check the settings.

By default, Azure SQL database is using TCP and you do not need to tell it on the command line.

In the image above, I am connecting to a Azure SQL database using the correct switches from books on line. Querying the sys.tables catalog returns the information that I expect. Two tables with different names. One duplicated named table under different schemas.

The BCP command will work the same way. You might even want to try a format file.

I want to clarify your statement that importing is taking a long time. Please remember that Azure SQL database is platform as a service. The database tiers are set at predetermined DTU's. That means stuffing the server with a lot of data will result in a throttled server at MAX DTU's.

I would look at the portal and see if that is your situation.

In short, sqlcmd, bcp and the import/export wizard are perfect tools for data loads a lot larger than the numbers you are talking about. Just remember, syntax is key to any successful program!

Answer 2

Your command is incorrect. The 'S' should be capitalized and you should provide the server name, not the database name.

sqlcmd -S tcp:myServer.database.windows.net -d database -U username -P password

See the documentation page for the complete syntax of sqlcmd.

Answer 3

Please make a ping to the name of your Azure SQL Database server as shown below:

C:\> ping myserver.database.windows.net

The ping command should fail but it should return the current IP of your SQL Azure Database server. If it fails to return that IP, then you cannot access to Azure server due to a DNS resolution problem.

If the ping command returned the IP address successful, then try to telnet your SQL Azure Database server as shown below.

C:\> telnet myserver.database.windows.net 1433

If the telnet command fails make sure your computer firewall or network hardware is allowing traffic to the IP returned on the previous step and make sure TCP port 1433 is open. If your are trying this from a corporate network, please contact your network administrator with this requirements.

Please note that you may need to enable the telnet command via Control Panel -> Program and Features -> Turn on/off features.