Install SQL Server and SSMS/Client Tools, Configure an Instance, and Verify Connectivity

Learning outcomes

An installer reporting “success” proves only that setup reached a completion state. ServiceHub needs a usable and diagnosable instance: the expected build/edition is running, clients reach the intended endpoint, authentication behaves as designed, encryption is understood, and the operator can explain which evidence is instance-side versus client-side.

1. Install using the platform’s supported path

On Windows, use current SQL Server 2025 Setup media and select a free Developer edition or Express for the course. Install the Database Engine; add other features only when a later lesson requires them. Install current SSMS separately for administration. On Linux, use Microsoft’s repository/package instructions for your supported distribution and configure the instance with the documented mssql-conf flow. In containers, use the official 2025 image and persistent storage strategy from Lesson 3.

Do not copy an unattended production setup command from a blog into a new environment. Service accounts, directories, collation, authentication, endpoints, certificates, and feature selection are architecture decisions. Start with a minimal learning instance, then verify the effective state.

2. Verify the process you actually reached

SELECT    @@VERSION AS version_string,    SERVERPROPERTY('MachineName') AS machine_name,    SERVERPROPERTY('ServerName') AS server_name,    SERVERPROPERTY('InstanceName') AS instance_name,    SERVERPROPERTY('Edition') AS edition,    SERVERPROPERTY('ProductVersion') AS product_version,    SERVERPROPERTY('ProductLevel') AS product_level,    SERVERPROPERTY('ProductUpdateLevel') AS product_update_level,    SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;SELECT    name,    state_desc,    user_access_desc,    recovery_model_desc,    compatibility_levelFROM sys.databasesORDER BY database_id;

ProductUpdateLevel can report a CU label when that metadata is available. Record the numeric ProductVersion as the primary build evidence. The latest CU today is not a permanent course constant; future chapter work must re-check Microsoft’s servicing pages and known issues before upgrades.

3. Verify the connection path, not just query success

SELECT    c.session_id,    c.net_transport,    c.protocol_type,    c.encrypt_option,    c.auth_scheme,    c.local_net_address,    c.local_tcp_port,    c.client_net_address,    s.host_name,    s.program_name,    s.client_interface_name,    s.login_name,    s.original_login_nameFROM sys.dm_exec_connections AS cJOIN sys.dm_exec_sessions AS s  ON s.session_id = c.session_idWHERE c.session_id = @@SPID;

If a local SSMS connection uses Shared Memory, a TCP port is not involved. To test TCP deliberately, connect using a TCP server specification such as tcp:localhost,14333 for the container mapping used in Lesson 3 or the configured local TCP port. A successful Shared Memory query does not prove that a remote TCP client can pass the firewall and reach the listener.

The default SQL Server TCP port is commonly 1433 for a default instance, but named instances and hardened deployments can use other ports. Never make firewall rules from memory; inspect the effective endpoint and connect to the explicit test target.

4. Authentication mode and authorization are different

Windows authentication delegates identity to Windows/Active Directory mechanisms where supported. SQL authentication uses SQL Server logins with password policy options. Mixed mode allows both. SERVERPROPERTY('IsIntegratedSecurityOnly') helps identify a Windows instance configured for Windows-only authentication, but a successful login still says nothing about whether the principal has appropriate permissions inside every database.

Authentication answers “who are you?” Authorization answers “what may this principal do?” Lesson 5 creates a deliberately narrow application login/user. Avoid using sa or a sysadmin identity for application traffic; an administrative login is only a bootstrap mechanism in a disposable lab.

5. Encryption: encrypted is not the same as authenticated peer identity

SQL Server 2025 expands TDS 8.0 support and strict connection encryption. sys.dm_exec_connections.encrypt_option tells you whether the current connection is encrypted, but it does not tell you that the client validated the certificate chain and hostname exactly as your production policy requires.

Modern Microsoft drivers support encryption settings such as mandatory and strict. Strict encryption uses TDS 8.0 and requires certificate validation; TrustServerCertificate=true bypasses normal certificate trust validation in non-strict modes and should not become the default “fix” for certificate errors.

6. Deliberately wrong repair: TrustServerCertificate=true everywhere

A developer upgrades a driver and receives a certificate validation error. They add TrustServerCertificate=true to every environment and declare TLS fixed. The symptom disappears because validation was bypassed, not because the certificate became correct.

A production repair starts by identifying the server DNS name, certificate subject/subject-alternative names, issuing certificate authority, expiration, private-key permissions, and driver encryption mode. Configure a certificate that clients trust and connect using a name the certificate validates. Use a temporary trust bypass only in a controlled local lab when you explicitly understand the loss of peer verification.

7. Hands-on post-install verification

Run the following as an administrative lab identity. It changes no server configuration; it creates one disposable database to prove basic DDL and write/read behavior.

IF DB_ID(N'ServiceHubConnectivityProbe') IS NULL    CREATE DATABASE ServiceHubConnectivityProbe;GOUSE ServiceHubConnectivityProbe;GOCREATE TABLE dbo.ConnectionProbe(    probe_id       int IDENTITY(1,1) PRIMARY KEY,    observed_at    datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    login_name     sysname NOT NULL DEFAULT ORIGINAL_LOGIN(),    host_name      nvarchar(128) NULL DEFAULT HOST_NAME(),    app_name       nvarchar(128) NULL DEFAULT APP_NAME());GOINSERT dbo.ConnectionProbe DEFAULT VALUES;SELECT * FROM dbo.ConnectionProbe;GO

Then collect connection evidence with the DMV query above and save it alongside the engine build. If you need to test TCP, reconnect explicitly over TCP and confirm net_transport, local_tcp_port, and the client address.

USE master;GOALTER DATABASE ServiceHubConnectivityProbe SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DROP DATABASE ServiceHubConnectivityProbe;GO

Verification checklist:

• Engine build and edition match the intended lab baseline.
• System databases are online; the probe database can be created/read/dropped.
• The intended client tool reaches the intended instance.
• If TCP is required, a TCP connection—not Shared Memory—has been tested.
• Authentication mode is understood and the application will not use sysadmin.
• Connection encryption is observed; certificate validation assumptions are documented separately.
• Firewall exposure is limited to the authorized lab boundary.

8. Production judgment

“Server is up” should become a layered health statement: process/service is running, endpoint is listening, network path is reachable, TLS negotiation and certificate validation satisfy policy, authentication succeeds, authorization is least-privilege, database state is online, and a representative query works. Monitoring should check the layer it claims to monitor. A TCP socket probe is not a transaction probe; a successful SELECT 1 is not proof that backups or Agent jobs work.

Any change to Force Encryption/Force Strict Encryption, network protocols, ports, certificates, or authentication mode can affect clients and may require service restart/maintenance. Verify current Microsoft documentation and driver support before enforcing new settings.

9. Summary and next step

A completed installer is only the first checkpoint. You now know how to prove the exact SQL Server instance/build, inspect databases, trace the current connection transport, distinguish authentication from authorization, and treat TLS encryption separately from certificate identity validation. Lesson 5 turns this verified instance into a reusable course lab with deterministic data, least-privilege application access, backup conventions, and reset/cleanup rules.

Authoritative references

• Install SQL Server using the Windows installation wizard — supported setup flow
• SERVERPROPERTY — edition/build/authentication-mode evidence
• sys.dm_exec_connections — transport and encryption evidence
• TDS 8.0 — SQL Server 2025 protocol/encryption context
• Connect with strict encryption — strict-mode and certificate validation guidance