Connect to SQL Server


By:   |   Updated: 2021-01-21   |   Comments   |   Related: More > Database Configurations


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…
management studio

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
windows authentication

Connect with SQL Server Authentication

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

Analysis Services (SSAS)

  1. Connect
  2. Analysis Services…
management studio
  1. Server Name
  2. Connect
windows authentication

Integrations Services (SSIS)

  1. Connect
  2. Integration Services…
database engine
  1. Server Name
  2. Connect
sql server

Reporting Services (SSRS)

  1. Connect
  2. Reporting Services
database engine
  1. Server name
  2. Connect
connect to server

Azure Data Studio (ADS)

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

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
recent connections

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 -?

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
command prompt

Bcp Utility

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

command prompt

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
my table

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.

Invoke SqlCmd

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"
windows authentication

Power BI Desktop

SQL Server

To connect to SQL Server

  1. Expand Get data dropdown
  2. SQL Server
Power BI
  1. Enter SQL Server name
  2. Optionally enter database name
  3. OK
Power BI

Connect with Windows Authentication

  1. Connect
Power BI

Connect with SQL Server Authentication

  1. Select 'Use alternate credentials'
  2. Login name
  3. Password
  4. Connect
Power BI

Analysis Services

Go to Analysis Services

  1. Get data
  2. Analysis Services
Power BI
  1. Server name
  2. Optionally enter Database name
  3. OK
Power BI

Visual Studio

SQL Server

  1. Data Connections
  2. Add Connection…
Visual Studio
  1. Microsoft SQL Server
  2. Continue
Visual Studio
  1. Server name
  2. Database name
  3. Test connection
  4. OK
  5. OK
Visual Studio

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
Visual Studio

Analysis Services (SSAS)

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

  1. Edit
Visual Studio
  1. Server name
  2. Test Connection
  3. OK
  4. OK
Visual Studio
  1. OK
Visual Studio

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

  1. Name
  2. Credentials to change any credential settings
Visual Studio
  1. General
Visual Studio
  1. Build
Visual Studio
  1. Server name
  2. Test connection
  3. OK
  4. OK
Visual Studio
  1. OK
Visual Studio
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)

Azure Data Studio (ADS)

Sqlcmd Utility

Bcp Utility

PowerShell Invoke-SqlCmd cmdlet

Power BI Desktop

Visual Studio



Last Updated: 2021-01-21


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

Understanding the COLLATE DATABASE_DEFAULT clause in SQL Server

Verify Connectivity to SQL Server

SQL Server Parallelism Overview

Splitting a SQL Server Table Over Multiple Files

PowerShell Commands for SQL Server Instance and Database Settings














get free sql tips
agree to terms