?️ Article 4: Install Microsoft SQL Server on Windows (10/11)

This guide covers:

Picking the right edition (Express / Developer / Standard)
Installing SQL Server (GUI, easy method)
Installing SSMS (SQL Server Management Studio)
Post-install configuration (Mixed Mode, TCP 1433, firewall)
Create a test database & user
Set up ODBC (System DSN)
Common errors & quick fixes

Works on Windows 10 & 11. You need local admin rights.

0) Choose your SQL Server edition (what to download)

SQL Server 2022 Express – Free, best for lightweight apps and learning. (No SQL Agent.)
SQL Server 2022 Developer – Free for development/testing, full features (not for production).
SQL Server Standard/Enterprise – Paid licenses (for production workloads).

If unsure: Use Express for small tools/test OR Developer for internal dev work.

1) Download the installer

Go to Microsoft’s SQL Server download page.
Choose Express or Developer.
Run the small .exe bootstrapper.
Pick Basic (quick install) or Custom (lets you choose features and location).

For most users: Basic is fine.
If you need a specific path/instance name: choose Custom → Install.

Offline environments: choose “Download Media” to get an ISO. Mount ISO → run setup.exe.

2) Install SQL Server (Custom mode – recommended for IT teams)

Open installer → click Custom → Install.
SQL Server Installation Center appears → click New SQL Server stand-alone installation.
License Terms → Accept → Next.
Microsoft Update → leave default → Next.
Install Rules → fix any red items (e.g., pending restart) → Next.
Feature Selection:

✅ Database Engine Services
(Optional) Full-Text, Replication (Express supports Replication as subscriber)
Leave other features unchecked unless you know you need them.

Instance Configuration:

Default instance (MSSQLSERVER) is easiest.
Or Named instance (e.g., SQLEXPRESS) if you prefer.

Note: Named instance usually uses dynamic port, unless you set static.

Server Configuration:

Startup Type: Automatic for SQL Server service.
Keep default service accounts (NT Service*).

Database Engine Configuration:

Authentication Mode: choose Mixed Mode (SQL + Windows).
Set a strong SA password (keep it safe).
Click Add Current User to make yourself SQL admin.

Data Directories (optional):

If you have a data drive (E:), set:
- Data root: E:\MSSQL\
- Data: E:\MSSQL\Data
- Logs: E:\MSSQL\Log

TempDB (optional): leave defaults (good for small servers).

Install → wait for completion → Close.

✅ SQL Server is installed.

3) Install SSMS (SQL Server Management Studio)

Download SSMS (separate installer from Microsoft).
Run installer → Install → finish.
Open SSMS → Connect to Server:

Server: localhost (or .\SQLEXPRESS if you used a named instance)
Authentication: Windows Authentication (works if you added your user as admin)

4) Post-install configuration (so apps can connect easily)

A) Enable TCP/IP and set static port 1433

Open SQL Server Configuration Manager
(Start → search “SQL Server Configuration Manager”).
Go to SQL Server Network Configuration → Protocols for <YourInstance>.
Right-click TCP/IP → Enable → double-click TCP/IP.
IP Addresses tab:

Scroll to IPAll → set:
- TCP Dynamic Ports: (clear it; leave blank)
- TCP Port: 1433

Click OK → you will be asked to restart the service.

B) Restart SQL Service

In Configuration Manager → SQL Server Services:
- Right-click SQL Server (<YourInstance>) → Restart.

C) Allow Windows Firewall (TCP 1433 + UDP 1434)

Option 1: GUI

Windows Security → Firewall & network protection → Advanced settings.
Inbound Rules → New Rule:

Type: Port
TCP → Specific local ports: 1433
Allow the connection → apply to Domain/Private (and Public only if needed)
Name: SQL Server TCP 1433

Add one more rule for UDP 1434 (SQL Browser), if you use named instances.

Option 2: Command line (run as Admin)

netsh advfirewall firewall add rule name="SQL Server TCP 1433" dir=in action=allow protocol=TCP localport=1433

netsh advfirewall firewall add rule name="SQL Browser UDP 1434" dir=in action=allow protocol=UDP localport=1434

D) (Optional) SQL Browser service for named instances

If you used a named instance and want easy discovery:
- SQL Server Browser → Startup Type: Automatic → Start the service.

5) Verify with SSMS & create a test DB + login

A) Connect locally

Open SSMS → Server: localhost (or .\SQLEXPRESS) → Connect.

B) Create a database

In SSMS → New Query → run:

CREATE DATABASE DemoDB;

C) Create a SQL login (app user) and give rights

-- Create SQL login

CREATE LOGIN app_user WITH PASSWORD = 'Strong_Pa$$w0rd_2025';

-- Create user in DemoDB and assign role

USE DemoDB;

CREATE USER app_user FOR LOGIN app_user;

EXEC sp_addrolemember 'db_owner', 'app_user'; -- or db_datareader/db_datawriter

For tighter security, prefer db_datareader + db_datawriter, not db_owner.

D) Test sign-in with SQL Authentication

In SSMS → Connect → Authentication: SQL Server Authentication
Login: app_user, Password: (the one you set)
Change Options → Connect to database: DemoDB to confirm access.

6) Set up ODBC (System DSN) for apps (Power BI, apps, etc.)

A) Install Microsoft ODBC Driver for SQL Server

Download and install “Microsoft ODBC Driver for SQL Server (MSODBCSQL)”.

B) Create System DSN

Open ODBC Data Sources (64-bit) (search in Start).
System DSN tab → Add.
Choose ODBC Driver 18 for SQL Server (or latest) → Finish.
Name: DemoDB_DSN
Server:

Default instance: localhost or SERVERNAME
Named instance: SERVERNAME\SQLEXPRESS (or use SERVERNAME,1433 if you set static port)

Auth:

SQL Authentication: user app_user + password
Or Windows Authentication if app user is domain-based

Change default database → choose DemoDB.

Test Data Source → should say TESTS COMPLETED SUCCESSFULLY.

If you see IM002 / Data source name not found: ensure ODBC driver is installed and DSN is created in the same bitness (use 64-bit ODBC for 64-bit apps like 64-bit Power BI).

7) Common errors & quick fixes

“Cannot connect to server / timeout”

Check SQL Service is running (Configuration Manager).
Ensure TCP/IP is enabled and port is 1433 (then restart service).
Firewall allows TCP 1433 (and UDP 1434 if named instance).
If using named instance, either:
- Start SQL Browser service, OR
- Connect as SERVERNAME,1433 (comma + port).

“Login failed for user ‘sa’”

You used Windows Authentication only during install.
Fix: In SSMS, right-click server → Properties → Security → Mixed Mode → restart service.
Also ensure sa login is enabled and password is correct:

ALTER LOGIN sa ENABLE;

ALTER LOGIN sa WITH PASSWORD = 'New_Strong_Pa$$w0rd';

ODBC error IM002: Data source name not found

Create System DSN (not User DSN) for server-wide use.
Match bitness of driver and app (64-bit to 64-bit).

Named Pipes Provider / SQL Network Interfaces error

Usually means TCP/IP disabled or port blocked.
Enable TCP/IP + set 1433, open firewall, restart SQL service.

Power BI refresh fails with ODBC

Install latest MS ODBC Driver 18+.
If TLS errors: in DSN → Trust server certificate (only in secure internal networks) or set proper certs.

8) Basic maintenance (must-know)

Backups

Express has no SQL Agent. Use Task Scheduler + sqlcmd:

sqlcmd -E -S localhost -Q "BACKUP DATABASE DemoDB TO DISK='C:\Backups\DemoDB_$(Get-Date -f yyyyMMdd).bak' WITH INIT"

(Adapt path; in .bat use date variables or a fixed file name.)

Update SQL Server & SSMS

Check Cumulative Updates (CUs) periodically.
Keep SSMS updated (it’s separate).

Recommended settings (small servers)

Max Server Memory (to leave RAM for OS):

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;

EXEC sp_configure 'max server memory (MB)', 4096; -- e.g., 4 GB

RECONFIGURE;

(Adjust to your server’s RAM.)

9) Uninstall (if you need to redo cleanly)

Apps & features → uninstall SQL Server components first (DB Engine, Browser).
Uninstall SSMS.
Delete leftover folders if needed: C:\Program Files\Microsoft SQL Server\…
Reboot. Install fresh.

General Hints ????

Keep the SA password in a secure vault. Prefer Windows logins or dedicated SQL logins per app.
For remote connections, use static port 1433 and open firewall.
For named instances, prefer either SQL Browser ON or connect with SERVER,1433.
If something is odd, restart SQL Service, then check logs in SSMS → Management → SQL Server Logs.
Use SSMS for GUI tasks; use sqlcmd for scripts/automation.

Suggested knowledgebase articles: