Connect to Synapse SQL with sqlcmd

• Azure Data Studio)
• Power BI
• Visual Studio
• sqlcmd
• SSMS

You can use the sqlcmd command-line utility to connect to and query serverless SQL pool and dedicated SQL pool within Synapse SQL.

1. Connect

To get started with sqlcmd, open the command prompt and enter sqlcmd followed by the connection string for your Synapse SQL database. The connection string requires the following parameters:

• Server (-S): Server in the form <Server Name>.database.windows.net
• Database (-d): Database name
• Enable Quoted Identifiers (-I): Quoted identifiers must be enabled to connect to a Synapse SQL instance

To use SQL Server Authentication, you need to add the username and password parameters:

• User (-U): Server user in the form <User>
• Password (-P): Password associated with the user

Your connection string might look like the following example:

Serverless SQL pool

C:\>sqlcmd -S partyeunrt.database.windows.net -d demo -U Enter_Your_Username_Here -P Enter_Your_Password_Here -I

Dedicated SQL pool

C:\>sqlcmd -S MySqlDw.database.windows.net -d Adventure_Works -U myuser -P myP@ssword -I

To use Azure Active Directory Integrated authentication, you need to add the Azure Active Directory parameters:

• Azure Active Directory Authentication (-G): use Azure Active Directory for authentication

Your connection string might look like on of the following examples:

Serverless SQL pool

C:\>sqlcmd -S partyeunrt.database.windows.net -d demo -G -I

Dedicated SQL pool

C:\>sqlcmd -S MySqlDw.database.windows.net -d Adventure_Works -G -I

Note

You need to enable Azure Active Directory Authentication to authenticate using Active Directory.

2. Query

Use dedicated SQL pool

After connection, you can issue any supported Transact-SQL (T-SQL) statements against the instance. In this example, queries are submitted in interactive mode:

C:\>sqlcmd -S MySqlDw.database.windows.net -d Adventure_Works -U myuser -P myP@ssword -I
1> SELECT name FROM sys.tables;
2> GO
3> QUIT

For dedicated SQL pool, the following examples show you how to run queries in batch mode using the -Q option or piping your SQL to sqlcmd:

sqlcmd -S MySqlDw.database.windows.net -d Adventure_Works -U myuser -P myP@ssword -I -Q "SELECT name FROM sys.tables;"

"SELECT name FROM sys.tables;" | sqlcmd -S MySqlDw.database.windows.net -d Adventure_Works -U myuser -P myP@ssword -I > .\tables.out

Use serverless SQL pool

After connecting, you can issue any supported Transact-SQL (T-SQL) statements against the instance. In the following example, queries are submitted in interactive mode:

C:\>sqlcmd -S partyeunrt.database.windows.net -d demo -U Enter_Your_Username_Here -P Enter_Your_Password_Here -I
1> SELECT COUNT(*) FROM  OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet', FORMAT='PARQUET')
2> GO
3> QUIT

For serverless SQL pool, the examples that follow show you how to run queries in batch mode using the -Q option or piping your SQL to sqlcmd:

sqlcmd -S partyeunrt.database.windows.net -d demo -U Enter_Your_Username_Here -P 'Enter_Your_Password_Here' -I -Q "SELECT COUNT(*) FROM  OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet', FORMAT='PARQUET')"

"SELECT COUNT(*) FROM  OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet', FORMAT='PARQUET')" | sqlcmd -S partyeunrt.database.windows.net -d demo -U Enter_Your_Username_Here -P 'Enter_Your_Password_Here' -I > ./tables.out

Next steps

For more information about sqlcmd options, see the sqlcmd documentation.