Microsoft SQL Server

mssql-configuration.md

Here’s a complete step-by-step guide to set up a Microsoft SQL Server and configure user access for SQL Server Management Studio (SSMS).

This guide covers:

• Installing SQL Server
• Installing SSMS
• Enabling server authentication
• Creating logins and users
• Granting permissions
• Allowing remote access (optional)

---

✅ 1. Install SQL Server

Step 1: Download SQL Server

Download Microsoft SQL Server (Developer or Express edition recommended).

Choose:

• Developer Edition (full features, free for dev)
• Express Edition (lightweight, free)

Step 2: Run Installation

1. Select New SQL Server stand-alone installation

2. Choose edition

3. Accept license terms

4. At Feature Selection, select:

   • ✔ Database Engine Services

Step 3: Configure Server Authentication (IMPORTANT)

In Database Engine Configuration → Server Configuration:

Select:

🔘 Mixed Mode (SQL Server authentication and Windows authentication)

Then:

• Set a strong sa password
• Click Add Current User (adds your Windows account as admin)

Finish installation.

---

✅ 2. Install SQL Server Management Studio (SSMS)

Download and install SQL Server Management Studio separately (it is no longer bundled with SQL Server).

After installation:

• Open SSMS

• Connect using:

  • Server type: Database Engine
  • Server name: localhost or SERVERNAME
  • Authentication: Windows Authentication

---

✅ 3. Enable SQL Server Authentication (If Not Already Enabled)

If Mixed Mode wasn't enabled during installation:

1. Open SSMS

2. Right-click server → Properties

3. Go to Security

4. Select:

   SQL Server and Windows Authentication mode

5. Click OK

6. Restart SQL Server service

Restart via:

• SQL Server Configuration Manager OR
• Services.msc → Restart “SQL Server (MSSQLSERVER)”

---

✅ 4. Create a Login (Server-Level Access)

A Login allows access to the SQL Server instance.

Method 1: Using GUI

1. In SSMS → Expand Security
2. Right-click Logins → New Login

Fill in:

• Login name: testuser
• Select: SQL Server authentication
• Enter password
• Uncheck: Enforce password policy (optional)
• Default database: Choose your database

Click OK.

---

Method 2: Using SQL Script

CREATE LOGIN testuser 
WITH PASSWORD = 'StrongPassword123';

---

✅ 5. Create a Database (If Needed)

CREATE DATABASE TestDB;

---

✅ 6. Create a Database User (Database-Level Access)

A User maps a Login to a specific database.

Using GUI

1. Expand Databases
2. Expand your database
3. Expand Security
4. Right-click Users → New User

Fill in:

• User type: SQL user with login
• Login name: testuser
• Username: testuser
• Default schema: dbo

Click OK.

---

Using SQL Script

USE TestDB;

CREATE USER testuser FOR LOGIN testuser;

---

✅ 7. Grant Permissions

You can grant:

Option A: Full Database Access

ALTER ROLE db_owner ADD MEMBER testuser;

Option B: Read & Write Only

ALTER ROLE db_datareader ADD MEMBER testuser;
ALTER ROLE db_datawriter ADD MEMBER testuser;

Option C: Read Only

ALTER ROLE db_datareader ADD MEMBER testuser;

---

✅ 8. Enable Remote Connections (If Accessing from Another Computer)

Step 1: Enable TCP/IP

1. Open SQL Server Configuration Manager

2. Go to:

   • SQL Server Network Configuration
   • Protocols for MSSQLSERVER

3. Enable: ✔ TCP/IP

4. Restart SQL Server

---

Step 2: Open Firewall Port

Default SQL Server port:

TCP 1433

Add firewall rule:

• Open Windows Defender Firewall
• Add inbound rule for TCP 1433

---

Step 3: Find Server IP

Run:

ipconfig

Use IPv4 address to connect remotely:

192.168.1.10,1433

---

✅ 9. Test Login from SSMS

Open SSMS:

• Server name: SERVER_IP,1433
• Authentication: SQL Server Authentication
• Login: testuser
• Password: ********

Click Connect.

---

🔐 Security Best Practices

• Never use sa for applications
• Use strong passwords
• Grant least privilege access
• Disable unused logins
• Consider Windows Authentication for domain environments
• Change default port in production
• Use SSL encryption for remote access

---

🏢 Production Setup Recommendations

For business/production environments consider:

• Install on Windows Server (e.g., Windows Server 2022)
• Regular backups
• Enable maintenance plans
• Configure SQL Agent jobs
• Restrict public network exposure
• Use VPN for remote access

---

🎯 Quick Summary

Step	Purpose
Install SQL Server	Database engine
Install SSMS	Management tool
Enable Mixed Mode	Allow SQL logins
Create Login	Server-level access
Create User	Database-level access
Grant Roles	Control permissions
Enable TCP/IP	Remote access

---