Connect to SQL Server

Problem

SQL Server has many different components that you need to connect to so you can manage different aspects of the SQL Server environment. In this article we look at how to connect to the SQL Server database engine, Analysis Services (SSAS), Integration Services (SSIS), Reporting Services (SSRS) and Power BI with some commonly used tools (SSMS, Visual Studio, Azure Data Studio, SQLCMD, BCP and PowerShell).

Solution

In this article, we’ll walk through making connections with each of the following tools:

1. SQL Server Management Studio (SSMS)
   • SQL Server database engine
   • Analysis Services (SSAS)
   • Integration Services (SSIS)
   • Reporting Services (SSRS)
2. Azure Data Studio (ADS)
3. Sqlcmd Utility
4. Bcp Utility
5. PowerShell Invoke-SqlCmd cmdlet
6. Power BI Desktop
   • SQL Server
   • Analysis Services (SSAS)
7. Visual Studio (VS)
   • Analysis Services (SSAS) from Integration Services (SSIS)
   • Analysis Services (SSAS) from Reporting Services (SSRS)

SQL Server Management Studio (SSMS)

SQL Server

Object Explorer (View > Object Explorer or F8 if it doesn’t automatically open).

1. Connect
2. Database Engine…

The Connect to Server box opens and Authentication defaults to Windows Authentication. The ‘User name’ field is automatically populated with the Windows domain\account you’re logged in as.

1. Server name
2. Connect

Connect with SQL Server Authentication

1. Expand Authentication dropdown and select SQL Server Authentication
2. Login
3. Password
4. Connect

Analysis Services (SSAS)

1. Connect
2. Analysis Services…

1. Server Name
2. Connect

Integrations Services (SSIS)

1. Connect
2. Integration Services…

1. Server Name
2. Connect

Reporting Services (SSRS)

1. Connect
2. Reporting Services

1. Server name
2. Connect

Azure Data Studio (ADS)

1. Connections
2. Add Connection
3. Fill in server name
4. Connect

Connect with SQL Server Authentication

1. Enter the SQL Server name
2. Select SQL Login in the Authentication type dropdown
3. Enter login name
4. Enter password
5. Connect

Sqlcmd Utility

Run cmd to open up a Windows Command Prompt. To see all of the sqlcmd options, simply type sqlcmd with a ? switch sqlcmd -?

This is an example of the following

• Connecting with the Windows account that you are logged in with using sqlcmd -E -S ServerName which brings us to the to the sqlcmd prompt where we can interactively enter T-SQL commands
• Connecting SQL Server Authentication login using sqlcmd -U login -P password -S ServerName
• Connecting with Windows authentication and adding the -i switch to call a T-SQL script that contains SELECT @@SERVERNAME and GO
• Connecting with Windows authentication and using the ‘-Q’ switch to run SELECT @@SERVERNAME directly from the command line

Bcp Utility

Just as with sqlcmd, we can see all our options with a -? switch like this bcp -?

Here we are connecting to the SQL Server with Windows and SQL Server authentication and exporting all of the records out of a table called MyTable in the database MyDatabase to file C:\bcpout\MyTable.bcp.

bcp MyTable out C:\bcpout\MyTable.bcp -T -S WIN-MBEN3T4BDIM -d MyDatabase
 
bcp MyTable out C:\bcpout\MyTable.bcp -U MySqlAuthLogin -P SuperSecret -S WIN-MBEN3T4BDIM -d MyDatabase

PowerShell Invoke-SqlCmd cmdlet

Similar to the sqlcmd Utility, we can authenticate with Windows or SQL Server authentication and T-SQL from an input file or run on the command line. Run Get-Help Invoke-Sqlcmd to show all of the cmdlet’s switches.

We’ll see three examples executing the same T-SQL using Windows and SQL Server authentication from the command line with the -Query switch and from an input file using the -InputFile switch.

# connect with Invoke-Sqlcmd using Windows authentication and run query from the command line using the -Query switch   
Invoke-Sqlcmd -ServerInstance WIN-MBEN3T4BDIM -Query "SELECT @@SERVERNAME"
 
# connect with Invoke-Sqlcmd using Windows authentication and run query by calling a SQL script using the -InputFile switch
Invoke-Sqlcmd -ServerInstance WIN-MBEN3T4BDIM -InputFile "C:\scripts\GetServerName.sql"
 
# connect with Invoke-Sqlcmd using SQL Server authentication and run query from the command line using the -Query switch   
Invoke-Sqlcmd -Username MySqlAuthLogin -Password SuperSecret -ServerInstance WIN-MBEN3T4BDIM -Query "SELECT @@SERVERNAME"

Power BI Desktop

SQL Server

To connect to SQL Server

1. Expand Get data dropdown
2. SQL Server

1. Enter SQL Server name
2. Optionally enter database name
3. OK

Connect with Windows Authentication

1. Connect

Connect with SQL Server Authentication

1. Select ‘Use alternate credentials’
2. Login name
3. Password
4. Connect

Analysis Services

Go to Analysis Services

1. Get data
2. Analysis Services

1. Server name
2. Optionally enter Database name
3. OK

Visual Studio

SQL Server

1. Data Connections
2. Add Connection…

1. Microsoft SQL Server
2. Continue

1. Server name
2. Database name
3. Test connection
4. OK
5. OK

To connect with SQL Server authentication

1. Server name
2. Choose SQL Server Authentication in the dropdown
3. User name
4. Password
5. Database name
6. Test connection
7. OK
8. OK

Analysis Services (SSAS)

Analysis Services (SSAS) connection from Reporting Services (SSRS)

1. Edit

1. Server name
2. Test Connection
3. OK
4. OK

1. OK

Analysis Services (SSAS) connection from Reporting Services (SSRS)

1. Name
2. Credentials to change any credential settings

1. General

1. Build

1. Server name
2. Test connection
3. OK
4. OK

1. OK

Next Steps

We have learned how to connect with SQL Server Management Studio (SSMS), Azure Data Studio (ADS), sqlcmd Utility, bcp Utility, PowerShell Invoke-SqlCmd cmdlet, Power BI Desktop, and Visual Studio (VS). The following links have further information available on each of these tools.

SQL Server Management Studio (SSMS)

• SQL Server Management Studio Overview (SSMS)
• Customizing the Status Bar for each SQL Server SSMS Connection
• Download, Install and Update SQL Server Management Studio
• Who Uses SQL Server Management Studio and Why
• Maximizing work space with SSMS by using separate results tabs

Azure Data Studio (ADS)

• Customize Azure Data Studio with Dashboard Widgets
• Create SQL Server Notebooks in Azure Data Studio
• Azure Data Studio Step By Step Tutorial
• Create custom T-SQL code snippets with Azure Data Studio
• Creating Dashboard Extensions for Azure Data Studio

Sqlcmd Utility

• Introduction to SQL Server's sqlcmd utility
• Connecting to SQL Server Using SQLCMD Utility
• Execute SQL Server Script Files with the sqlcmd Utility
• Installing SQL Server Tools to make a Database Connection from Redhat Linux Server
• Using SQL Server sqlcmd scripting variables

Bcp Utility

• Dynamically Generate SQL Server BCP Format Files
• Adding more functionality to SQL Server BCP with PowerShell
• Different Options for Importing Data into SQL Server
• Simple way to export SQL Server data to Text Files
• Minimally Logging Bulk Load Inserts into SQL Server

PowerShell Invoke-SqlCmd cmdlet

• PowerShell Invoke-SQLCmd outputs DataTables you can INSERT into SQL Server
• Invoking SQL Server Database Backups with PowerShell
• Execute SQL Server Stored Procedures from PowerShell
• Introduction into handling errors in PowerShell for SQL Server tasks
• Create SQL Server Database with PowerShell

Power BI Desktop

• Getting Started with Power BI Designer
• Querying SQL Server Data with Power BI Desktop
• Install and configure Power BI Report Server and Power BI Desktop
• Power BI Desktop Data Source Considerations
• Power BI Desktop Custom Visualization

Visual Studio

• Install SQL Server Integration Services in Visual Studio 2019
• Install Visual Studio
• SSIS Toolbox is not visible in SQL Server Data Tools
• SQL Server Download Quick Links
• Visual Studio 2019 Configuration for the Production DBA