Skip to content

Latest commit

 

History

History
136 lines (82 loc) · 10.5 KB

lesson-1-connecting-to-the-database-engine.md

File metadata and controls

136 lines (82 loc) · 10.5 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic ms.custom
Lesson 1: Connecting to the Database Engine
Lesson 1: Connecting to the Database Engine
MashaMSFT
mathoma
maghan
09/26/2023
sql
connectivity
quickstart
intro-quickstart

Lesson 1: Connecting to the Database Engine

[!INCLUDE sqlserver]

When you install the [!INCLUDE ssDEnoversion], the tools that are installed depend upon the edition and your setup choices. This lesson reviews the principal tools and shows how to connect and perform an essential function (authorizing more users).

This lesson contains the following tasks:

Tools for getting started

  • The [!INCLUDE ssDEnoversion] ships with various tools. This article describes the first tools you need and helps you select the right tool for the job. All tools can be accessed from the Start menu. Some tools, such as SQL Server Management Studio (SSMS), aren't installed by default. Select the tools you want as part of the client components during setup. For a complete description of the tools below, search for them in SQL Server Books Online. [!INCLUDE ssExpress] contains only a subset of the tools.

Common tools

The following table describes some of the more common client tools.

Client tool Type Operating system
SQL Server Management Studio (SSMS) GUI Windows
Azure Data Studio GUI Windows, macOS, Linux
bcp CLI Windows, macOS, Linux
sqlcmd CLI Windows, macOS, Linux

For this article, we are going to focus on connecting via SSMS. If you are interested in connecting via Azure Data Studio, see Quickstart: Use Azure Data Studio to connect and query SQL Server.

Sample database

[!INCLUDE article-uses-adventureworks]

SQL Server Management Studio (Windows only)

  • On current versions of Windows, on the Start page, type SSMS, and then select Microsoft SQL Server Management Studio.
  • When using older versions of Windows, on the Start menu, point to All Programs, point to [!INCLUDE ssCurrentUI], and then select SQL Server Management Studio.

Connect with SSMS

  • It's easy to connect to the [!INCLUDE ssDE] from tools that are running on the same computer if you know the name of the instance and if you're connecting as a member of the local Administrators group on the computer. The following procedures must be performed on the same computer that hosts SQL Server.

Note

This topic discusses connecting to an on-premises SQL Server. For Azure SQL Database, see Connect to Azure SQL Database.

Determine the name of the instance of the Database Engine

  1. Log into Windows as a member of the Administrators group, and open [!INCLUDE ssManStudio].

  2. In the Connect to Server dialog box, select Cancel.

  3. If Registered Servers isn't displayed, on the View menu, select Registered Servers.

  4. With Database Engine selected on the Registered Servers toolbar, expand Database Engine, right-click Local Server Groups, point to Tasks, and then select Register Local Servers. Expand Local Server Groups to see all the instances of the [!INCLUDE ssDE] installed on the computer displayed. The default instance is unnamed and is shown as the computer name. A named instance displays as the computer name followed by a backward slash (\) and then the instance's name. For [!INCLUDE ssExpress], the instance is named <computer_name>\sqlexpress unless the name was changed during setup.

Verify that the Database Engine is running

  1. In Registered Servers, if the name of your instance of SQL Server has a green dot with a white arrow next to the name, the [!INCLUDE ssDE] is running and no further action is necessary.

  2. If the name of your instance of SQL Server has a red dot with a white square next to the name, the [!INCLUDE ssDE] is stopped. Right-click the name of the [!INCLUDE ssDE], select Service Control, and then select Start. After a confirmation dialog box, the [!INCLUDE ssDE] should start, and the circle should turn green with a white arrow.

Connect to the Database Engine

At least one administrator account was selected when [!INCLUDE ssNoVersion_md] was being installed. Perform the following step while logged into Windows as an administrator.

  1. In [!INCLUDE ssManStudio], on the File menu, select Connect Object Explorer.

    • The Connect to Server dialog box opens. The Server type box displays the type of component that was last used.

  2. Select Database Engine.

    :::image type="content" source="media/lesson-1-connecting-to-the-database-engine/object-explorer.png" alt-text="Screenshot of the Object Explorer showing the Connect dropdown list and the Database Engine option called out." lightbox="media/lesson-1-connecting-to-the-database-engine/object-explorer.png":::

  3. In the Server name box, type the name of the instance of the Database Engine. For the default instance of SQL Server, the server name is the computer name. The server name for a named instance of SQL Server is the <computer_name>\<instance_name>, such as ACCTG_SRVR\SQLEXPRESS. The following screenshot shows connecting to the default (unnamed) instance of [!INCLUDE ssNoVersion_md] on a computer named PracticeComputer. The user logged into Windows is Mary from the Contoso domain. When using Windows Authentication, you can't change the user name.

    :::image type="content" source="media/lesson-1-connecting-to-the-database-engine/connect-to-server.png" alt-text="Screenshot of the Connect to Server dialog box with the Server name text box called out." lightbox="media/lesson-1-connecting-to-the-database-engine/connect-to-server.png":::

  4. Select Connect.

Note

This tutorial assumes you are new to [!INCLUDEssNoVersion] and have no special problems connecting. For detailed troubleshooting steps, see Troubleshooting Connecting to the SQL Server Database Engine.

Authorize extra connections

Now that you've connected to SQL Server as an administrator, one of your first tasks is authorizing other users to connect. You do this by creating a login and authorizing that login to access a database as a user. Logins can be created using Windows authentication, SQL authentication, or Microsoft Entra authentication. Windows authentication logins use credentials from Windows. SQL authentication logins store the authentication information in SQL Server and are independent of your Windows credentials. Logins from Microsoft Entra ID (formerly Azure Active Directory) use credentials from cloud-based identities. You can learn more about this method from the following article - Use Microsoft Entra authentication.

Use Windows Authentication whenever possible.

Tip

Most organizations have domain users and will use Windows Authentication. You can experiment by creating additional local users on your computer. Your computer will authenticate local users, so the domain is the computer name. For example, if your computer is named MyComputer and you create a user named Test, then the Windows description of the user is Mycomputer\Test.

Create a Windows Authentication login

  1. In the previous task, you connected to the [!INCLUDE ssDE] using [!INCLUDE ssManStudio]. In Object Explorer, expand your server instance, expand Security, right-click Logins, and then select New Login. The Login - New dialog box appears.

  2. On the General page, in the Login name box, type a Windows login in the format: <domain>\<login>

    :::image type="content" source="media/lesson-1-connecting-to-the-database-engine/new-login.png" alt-text="Screenshot of the Login - New dialog box with the Login name text box called out.":::

  3. In the Default database box, select the AdventureWorks database if available. Otherwise, select the master database.

  4. On the Server Roles page, if the new login is to be an administrator, select sysadmin. Otherwise, leave this blank.

  5. On the User Mapping page, select Map for the [!INCLUDE ssSampleDBobject] database if it's available. Otherwise, select master. The User box is populated with the login. When closed, the dialog box creates this user in the database.

  6. In the Default Schema box, type dbo to map the login to the database owner schema.

  7. Accept the default settings for the Securables and Status boxes and select OK to create the login.

Important

This is basic information to get you started. SQL Server provides a rich security environment.

Related content

Next step

[!div class="nextstepaction"] Lesson 2: Connecting from Another Computer