Problem
I’m new to SQL Server and I would like to know how to connect to Microsoft SQL Server remotely.
Solution
Below we will walk through different ways to connect to a Microsoft SQL Server database.
SQL Server Connection
Often users new to SQL Server don’t know where to start? Maybe someone shared a query with you and suggested that you can run it yourself, but you don't know where to get started. Maybe you are ready to start learning about the SQL Server database engine and querying databases. First, you will need to know the Server name and the Database name that you want to connect to. You will also need to know your login information to connect to a database. You can connect to a SQL Server either via a windows Active Directory account which is typically your login account to your company network of servers or via a SQL standard account which is login and a password. Also, you will need to know what tool to use to connect to a SQL Server.
This tutorial will cover connecting to a SQL Server instance in 4 basic steps: Identifying the Server and Database, Getting a Login, Choosing a SQL Tool, and Bringing it all together.
Step 1 – Information Gathering
Database Name: You will need to know the Database name. Each SQL Server can host one or more databases. A database name is usually something meaningful that relates to the data stored.
Server Name: You will need to know the SQL Server name. The SQL Server may be hosted in your companies’ data center, it might be Virtual, it may be hosted externally, or it might be Microsoft Azure, Amazon Web Services (AWS), or Google Cloud (GCP). Regardless, it is important to know the Server Name, IP Address or URL.
Examples of Server names:
- CORPSQL01 (just a server name)
- CORPSQL01.mycompany.com (same as above but fully qualified with domain name)
- CORPSQL01\MyInstanceName (SQL Server can have multiple instances)
- myazure.database.windows.net (Azure SQL DB or Managed Instance)
Once you know the Server and Database names, the next step is to find out you login information.
Step 2 – Credentials
The next important information that you will need to know is your credentials or login information to connect to the SQL Server database.
IIf you already have this information, great! If not, you may need to request a login to a SQL Server database. Most companies control the access to a database! A Database Administrator or Security Administrator will need to create your account on the SQL Server. You may be required to submit a security request to get a login set up. It will be based on your companies’ policies whether your account is a Windows Active Directory account or SQL Server Authentication with a login and password. Also, when requesting security to a database you will most likely ask for READ rights to the database. Typically changes to data in a database is done via an application. However, there are exceptions to this. If you are expected to update data in SQL Server you can search for articles on MSSQLTips.com to help learn how to Insert, Update and Delete data. See links at the bottom of this tutorial. Note: your login name and password may be case sensitive!
Request your login information for the specific Server Name and Database Name. While awaiting your credentials, determine what tool you will use to connect to a SQL Database.
Choosing a Tool
Choose a Tool to connect to SQL Server to explore databases and run Queries. Microsoft offers two free tools for connecting to SQL Server: Azure Data Studio (ADS) and SQL Server Management Studio (SSMS). In this article I will go over connecting to SQL Server with both tools. There are other tools on the market, but I will only cover these two because they are free Microsoft tools. See download links at the bottom of this article.
Step 3a Tools – Azure Data Studio
TThe newest and recommended tool is Azure Data Studio. This is a free Microsoft tool. Though it has “Azure” in the name, it can be used to connect to any SQL Server or SQL in Azure. Microsoft describes ADS as a cross platform tool and it can run on Windows, Linux, and MacOS. ADS is the preferred tool if you are mostly editing and running queries.
Step 3b Tools – SQL Server Management Studio
TThe next very popular tool is Microsoft SQL Server Management Studio (SSMS). SSMS is a tool primarily used by Database Administrators and Developers. It is also used to run T-SQL queries and stored procedures, however, the install is a lot heavier than Azure Data Studio, as it is packed with features used for administering and troubleshooting SQL Server. SSMS is also a free download from Microsoft and can be used to connect to a SQL Database, SQL Server Express database or SQL databases in Azure.
Bringing it All Together
Now that you know the SQL Server and Database names, and have your Login Credentials, and have chosen and installed your Query Tool, it is time to bring it all together. I’ll show examples of connecting to SQL from both Azure Data Studio and SQL Server Management Studio.
Step 4 Tools – Login with Azure Data Studio
- FFirst launch Azure Data Studio. From the Windows Start menu, click and type “Azure Data Studio” in the dialog box, then click to launch.
- In ADS, on the Welcome screen click Create a Connection.
- TThe connection dialogue box pops up. Enter the Server Name. I’m connecting to Server THANOS01\SQLExpress, which is the instance of SQL Server. Select the Authentication Type. Windows Authentication and SQL Login are the most common. If connecting to an Azure SQL Database, you would use either a SQL Login or use your Azure Active Directory – Universal with MFA account. For this you may need to ask your Database Administrator to assist you.
- For my example I’m using SQL Login ‘Testuser’ and a password. Also, you can type in the Database name or leave as default and select the database after you connect.
Now you are ready to run a query or explore the database.
Step 4b Tools – Login with SQL Server Management Studio
Next, I show how to connect to a SQL Server Database via SQL Server Management Studio (SSMS).
- First launch SQL Server Manager Studio. From the Windows Start menu, click and type “SSMS”, then click to launch.
- Once SSMS launches (it may take several seconds) a login screen will open. In this example I will connect to an Azure Database named: mssqltips01.database.windows.net. I will use a SQL Login and password.
- Optionally, you may need to specify the database name you want to connect to. Hit the Option button shown in the above screen shot. In the image below, on the Connection Properties Tab, enter the database name. I chose AdventureWorksLT. Then hit connect at the bottom.
- SSMS will connect to the SQL Server. Now you can explore the database in Object Explorer or open a query window to access the SQL Server data.
Wrap Up
Now you know what is needed to Connect to a SQL Server. Also, you are now aware of 2 common SQL Tools: Azure Data Studio which you can use to connect to “any” SQL Server or to Azure Databases and SQL Server Management Studio, a heavier tool that can be used to explore databases and run queries. For the basics, I recommend using Azure Data Studio. Next, find articles on MSSQLTips.com to learn more about SQL Server Databases.
Next Steps
