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 connection troubleshooting

Cannot connect to SQL Server: network, TLS, authentication and permissions

Investigate SQL Server connection failures by matching the target, client and observed error from one attempt. Separate TCP reachability, TLS certificate validation, login, database access and SQL permissions before deciding what to change.

What you will learn

Identify the failed and unverified stages and choose a next check for the same source, target, identity and time.

Prerequisite

A login identifies access at connection time; a database user acts within a database, with permissions for operations such as SELECT. Wording varies by client/version. The observations below are fictional; no probe or login is performed.

First identify the attempt being investigated

A different client success is not evidence for this attempt
RecordFictional valuePurpose
Source/timeclient-A / T1Distinguish other clients, paths and times
Targettcp:sql-lab.example.invalid,1433Fix name, protocol and port
ClientSSMS release and settingsDistinguish the application driver
Identity/databaseSQL authentication / reader_demo / CorpDocsRecord method and target, not secret values
ObservationTCP succeeded; certificate name validation failedLogin and SELECT remain unverified

example.invalid is a non-working documentation name; 1433 is stipulated, not a claim that every SQL Server uses that port. Even on one computer, SSMS and an application may use different drivers, identities and encryption settings.

Read what an error does and does not establish

From transport to SQL: arrows separate checks, not completed successes
From transport to SQL: arrows separate checks, not completed successes1: Name and reachability — Reaching the target leaves later checks separate — TLS and login. 2: TLS and login — Authentication does not grant every SQL action — Database and action12Name andreachabilityTLS and loginDatabase and actionFrom transport to SQL: arrows separate checks, not completed successes1: Name and reachability — Reaching the target leaves later checks separate — TLS and login. 2: TLS and login — Authentication does not grant every SQL action — Database and action12Name andreachabilityTLS and loginDatabase andaction
  1. Name and reachability → TLS and loginReaching the target leaves later checks separate
  2. TLS and login → Database and actionAuthentication does not grant every SQL action
Name and reachability
Target name/port, listener and path.
TLS and login
Certificate trust/name and the selected authentication method.
Database and action
Target DB state, principal, object and permission.
Choose a next investigation from the symptom
Observed symptomInvestigateNot yet established
Server/instance not foundName, target, protocol, listener port and pathPassword validity
Certificate trust/name failureConnection name, presented certificate, trust chain and client settingsSuccessful DB login or SELECT
18456: Login failedContemporaneous server error/state, method, identity and DBThat the password alone caused it
SELECT permission error after connectingDatabase context, user, object and required permissionThat opening the network will fix it
TimeoutWhether opening a connection or executing a command, and the waiting stageIdentical wording means an identical cause

T1: TCP succeeds, then certificate name validation fails

Use only the stipulated observations
StageT1 observationNext check
TCPStipulated as established to the targetVerify the intended name and destination
TLSStipulated certificate-name mismatchMatch connection name with certificate identities
LoginNo evidence the attempt reached this stageDo not invent success or failure
SELECTNot performedDo not infer database permissions or rows

Trust in the issuing chain and matching the connection name are separate checks. Correct certificate and connection identities through the appropriate management process. Do not substitute TrustServerCertificate or disabling encryption for a lasting diagnosis. A connection that bypasses validation does not establish the expected server identity.

Separate 18456 from a rejected action after login

18456 classifies a login failure, with possible differences in method, identity, expiry and access conditions. Do not infer a bad password from a short client message alone. An authorized investigator should correlate the server-side reason and state for the same attempt.

Independent observations, separate from T1
AttemptStipulated observationConclusion
T2A login attempt returns 18456Login failed; inspect details and target
T3After authentication/CorpDocs access, SELECT on app.Documents is deniedInspect DB principal, object and action permissions
T4Different client-B connectsNot evidence of success under client-A conditions

Match observations and changes before deciding the fix

  1. Record the full failure, stage, time, source, target and method, excluding secrets.
  2. Identify the real listener port; distinguish named-instance discovery from connecting to that TCP port.
  3. For TLS, inspect trust and names; for login, method and server reason; after connection, DB context and permissions.
  4. After one justified change, repeat under equivalent conditions and identify which boundary changed.
  5. Verify the required SQL result in the intended database, rather than treating login alone as recovery.

Logs and connection strings can contain secrets, personal data and internal names. This site requests no such input or transmission; handle investigation material under the applicable organizational policy.

For RDS, add the AWS-specific checks

For RDS, correlate source placement, VPC routes/SGs/NACLs and the RDS endpoint/port. Do not apply a self-managed OS-login/listener-change procedure to ordinary RDS. AWS API permission still does not establish a database login or SELECT authorization.

Choose a next check from the evidence

Choose an option to see the result and explanation.

T1 establishes TCP, then fails certificate-name validation. What about SELECT?
Read the answer and reason

Answer: Unverified; first correlate connection and certificate names. · Investigate the observed TLS failure and leave later stages unverified.

Does a client 18456 message alone prove the password is wrong?
Read the answer and reason

Answer: No; correlate reason/state, method and identity for that attempt. · 18456 has multiple possible causes; inspect the correlated server record.

T3 authenticates, then SELECT is denied. What next?
Read the answer and reason

Answer: Inspect target DB, user, object and required action permission. · Authentication and database action authorization are separate checks.

SSMS connects from another client. Is the original application fixed?
Read the answer and reason

Answer: Still unverified; repeat using the original connection conditions. · Do not relabel another attempt’s success as recovery of the original failure.

Check, then explain in your own words

  1. Classify T1 stages as observed, failed or unverified.
  2. Separate certificate trust/names, login rejection and SQL denial.
  3. Do not close an incident using another client success.
  4. Explain the change and expected evidence before relaxing controls.
Check your understanding: Bypassing certificate validation allows connection. Is that a lasting resolution?

No. Establish a configuration that validates the expected server.

Successful connection without validation does not establish that trust or name problems were corrected.

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.