Var denne side nyttig?
Din feedback på dette indhold er vigtigt. Giv os din mening.
Mere feedback?
1500 tegn tilbage
How to: Connect to Azure SQL Database by using sqlcmd
EN
Dette indhold er ikke tilgængeligt på dit sprog, men her er den engelske version.

How to: Connect to Azure SQL Database by using sqlcmd

Updated: July 15, 2015

This topic describes how connecting with the sqlcmd.exe utility to Microsoft Azure SQL Database differs from connecting to the traditional Microsoft SQL Server on-premises. SQL Server Management Studio (SSMS) is also discussed.

The following table displays connection strings for specific uses. To improve the display here, each string is split into multiple lines.

 

Database product Connection string Comments

A.
Microsoft SQL Server, on-premises

sqlcmd.exe -P MyPassword
    -U MyLogin
    -S myserver

Defaults to master database.

B.
Azure SQL Database, full format

sqlcmd.exe -P MyPassword
    -U MyLogin@myserver
    -S tcp:myserver.database.windows.net,1433
    -d AdventureWorks
    -l 30

This format is recommended.

C.
Azure SQL Database, possible shorter format

sqlcmd.exe -P MyPassword
    -U MyLogin
    -S myserver.database.windows.net
    -d AdventureWorks
    -l 30

This shorter format is not recommended. This short format works in some cases, but not in all cases.

This format could be even shorter by discarding the -d and -l parameters, which again is not recommended.

For the parameters that differ between connecting to SQL Server versus connecting to Azure SQL Database, the following table describes the differences as they apply to Azure SQL Database.

 

Parameter Example value Description for Azure SQL Database

-U

User name.

-U MyLogin@myserver

The following bad value might work, but is bad because every dot-delimited node after @myserver is ignored:
-U MyLogin@myserver.database.windows.net

Even the following silly value would work:
-U MyLogin@myserver.silly

The following wrong value for the server name portion would cause a failure:
-U MyLogin@wrongsvr

Inclusion of the @ and the single-node server name provides a nice confirmation that the intended server is being connected to. However, this inclusion is not always strictly necessary.

-S

Server name and extension.

-S tcp:myserver.database.windows.net,1433

The dot-delimited literal .database.windows.net is always required.

The leading tcp: portion identifies the necessary communication protocol. Sometimes optional, but recommended.

The trailing ,1433 is the communcation port number that Azure SQL Database uses. Sometimes optional, but recommended.

-d

Database name.

-d AdventureWorks

You rarely want to connect to the master database on Azure SQL Database. Yet master is the default database.

Azure SQL Database does not support the Transact-SQL command USE. You cannot switch out of the master database or any database.

Therefore you usually need to connect by using the -d parameter.

-l

Login timout.

-l 30

Microsoft recommends that you give more time to a connect attempt when targeting over the web. The default login timeout value is 15 seconds, but over the web a more patient 30 seconds is sometimes helpful.

The cmd.exe console contents from a run of sqlcmd.exe against Azure SQL Database is displayed next.

[C:\\]
>> sqlcmd.exe -U MyLogin@myserver -S tcp:myserver.database.windows.net,1433 -d AdventureWorks -P MyPassword -l 30
1> SET nocount on;  SELECT getdate();
2> SELECT top 3 o.name from sys.objects as o where o.type='U' order by 1;
3> go

-----------------------
2015-05-12 23:17:32.740
name

---------------------------------------------------------------------------------------------------------------------------

Address

BuildVersion

Customer

1> quit

[C:\\]
>>

The connection string advice that applies to sqlcmd.exe also applies to SSMS, which is ssms.exe.

For SSMS, in its Connect to Server dialog, you should enter the following values:

  • Set the control labeled Authentication to the value SQL Server Authentication.

    • Azure SQL Database does not support Windows Authentication.

  • Click Options > Connection Properties. There enter the following settings:

    • Connect to database:    MyDatabaseName

    • Connection time-out:    30

Vis:
© 2015 Microsoft