INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

Topic lessonTopic: SQL Server and database foundations

Getting started with SSMS: connecting to SQL Server

SQL Server Management Studio (SSMS) is a tool for connecting to SQL Server, inspecting its configuration and writing SQL. SSMS is the client; SQL Server manages the data and processes queries. Follow one document query while separating the server, authentication, encryption, current database and permitted operation.

What you will learn

Interpret connection settings and explain what DB_NAME() and a COUNT establish. Distinguish an established connection from permission to read the intended table.

Prerequisite

A basic idea of instances, databases, schemas and tables, briefly reviewed here. All conditions are fictional; no server or credentials are needed.

From the SSMS client to an engine and a database table

The client that connects and the engine that processes a query
CLIENT
SSMS / client-A

A Windows client that selects the connection and writes a query.

Queries and resultsCommunication / TLS
ENGINE
SQL Server / sql-demo-1

The target instance manages its databases.

CorpDocs

The database selected for this query tab.

app

A schema grouping objects within the database.

Documents

The table containing five document rows.

A fictional teaching example. Containment, communication and permission relationships have different meanings.

Installing SSMS alone does not install a running database engine. Applications can also connect, using different identities or paths. The nested boxes represent containment; the line between client and engine represents communication.

Read five connection settings separately

Fictional read conditions: fix the target, identity and database
SettingQuestionOur example
Server nameWhich instance is intended?sql-demo-1, a fictional label
AuthenticationWhich method and identity?SQL Server authentication / reader_demo
Encryption and certificateProtected traffic and expected server?Both stipulated for the success case
Current databaseWhich database does this tab use?CorpDocs / app schema
Object permissionWhich operations are allowed?SELECT on Documents and People only

Authentication establishes identity; authorization determines permitted work. reader_demo is read-only in this example. Neither updates nor access to another database are stipulated.

The SSMS version is independent of the SQL Server version. We reference Microsoft’s documented SSMS 20+ fields and use SQL Server 2022 as an engine example. Check actual layout and saved profiles in the version in use.

Encryption and certificate validation answer different questions

Microsoft documents Mandatory as the default Encryption value and an unchecked Trust server certificate setting. Encryption protects traffic; certificate validation helps identify the expected server. Strict has different prerequisites and does not offer the Trust server certificate option.

Read the current database and count under fixed conditions

Suppose sql-demo-1 has separate CorpDocs and TrainingDocs databases, each with app.Documents. CorpDocs has five rows; TrainingDocs has two. Case S0 permits reader_demo to SELECT the required CorpDocs tables and holds the committed data unchanged at T0.

Read-only T-SQL: the current database
SELECT DB_NAME() AS current_database;
What DB_NAME() does and does not establish
Query contextExpected valueNot established
Current DB is CorpDocsCorpDocsSELECT on every table
Current DB is TrainingDocsTrainingDocsThe context in another tab or connection
Reason about a schema-qualified table count
SELECT COUNT(*) AS document_count
FROM app.Documents;
Separate defined rows from permission to obtain a result
Target and principalStated conditionsConclusion
S0: CorpDocs / reader_demoSELECT permitted; five unchanged T0 rowsExpected count: 5
S1: TrainingDocs / reader_demoTwo defined rows; access and SELECT unspecifiedCannot assert a two-row result

These values are predictions from fixed conditions, not live measurements. The same SQL can have a different target or outcome when the instance, current database, principal or data state changes.

Limit each conclusion to the checkpoint established

Avoid one success indicator for connection and reading
Established factStill separate
TCP reaches the destination portTLS and certificate validation
TLS requirements are metDatabase authentication and object rights
reader_demo authenticatesSELECT on Documents
Current DB is CorpDocsTable contents and count
S0 stipulates a five-row countA result in another database or time

The table separates reasoning checkpoints; it is not an exact database protocol trace. “Not established” describes missing evidence, distinct from the SQL truth value UNKNOWN in NULL comparisons.

Check what the connection establishes

Choose an option to see the result and explanation.

SSMS shows a connection. What must you verify before querying?
Read the answer and reason

Answer: Server/database context and principal · A connection does not prove the right target database or principal.

If DB_NAME() returns CorpDocs, what does it establish?
Read the answer and reason

Answer: The current database context of that query · It says nothing about other tabs or sessions.

In SSMS 20+, encryption is configured but the certificate cannot be validated. What is the preferred path?
Read the answer and reason

Answer: Check the trusted CA, host name and client settings · Preserve encryption and server identity validation.

Check, then explain in your own words

  1. Identify the client and target instance.
  2. Check principal, current database and schema-qualified object separately.
  3. Separate encryption from certificate validation, and authentication from authorization.
  4. Fix permissions and data state before interpreting a count.
Check your understanding: If TrainingDocs has two rows, can reader_demo necessarily obtain them?

No. Database access and SELECT permissions must also be established.

Defined rows do not establish authorization for a principal. Match database context, identity, object and data state.

Sources and conditions checked

Primary references are collected here with their scope, conditions and corresponding sections. Independent expert review and live-environment validation have not been performed.