Baza znanja

Microsoft SQL Server is the standard relational database for Windows-based applications. This guide covers installing SQL Server Express (free) or Developer edition, and connecting with SQL Server Management Studio.

Prerequisites

• Windows Server VPS with Administrator access
• At least 2 GB RAM (4 GB+ recommended for production)

Step 1 — Download SQL Server

Download SQL Server Express (free, suitable for smaller databases) or Developer edition (free, full features, non-production licensing) from Microsoft's official SQL Server downloads page directly on the VPS, or transfer the installer via RDP.

Step 2 — Run the Installer

1. Launch the downloaded installer
2. Choose Basic installation for a quick default setup, or Custom for more control
3. Accept the license terms
4. Let the installer complete — this can take several minutes

Step 3 — Install SQL Server Management Studio (SSMS)

SSMS is a separate download — get it from Microsoft's official SSMS download page and install it on the server (or your local machine, connecting remotely).

Step 4 — Enable SQL Server Authentication (If Needed)

By default, SQL Server uses Windows Authentication only. To also enable SQL logins (useful for application connections):

1. Open SSMS, connect to the instance
2. Right-click the server → Properties → Security
3. Select SQL Server and Windows Authentication mode
4. Restart the SQL Server service for the change to take effect

Step 5 — Create a Database

CREATE DATABASE MyApp;

Step 6 — Create a Dedicated Login and User

CREATE LOGIN appuser WITH PASSWORD = 'CHANGE_ME_STRONG_PASSWORD';
USE MyApp;
CREATE USER appuser FOR LOGIN appuser;
ALTER ROLE db_owner ADD MEMBER appuser;

Step 7 — Allow Remote Connections (If Needed)

Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols, enable TCP/IP, then restart the SQL Server service.

Allow the port through the firewall, restricted to trusted IPs:

New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -RemoteAddress YOUR_APP_SERVER_IP -Action Allow

Step 8 — Test the Connection

In SSMS, connect using: Server name = YOUR_SERVER_IP, Authentication = SQL Server Authentication, with the credentials created above.

Common Errors

"A network-related or instance-specific error" — verify TCP/IP protocol is enabled and the SQL Server Browser service is running if using a named instance.

"Login failed for user" — confirm SQL Server Authentication mode is enabled (not Windows-only) and the login has correct database permissions.

Best Practices

• Never expose SQL Server's port (1433) directly to the public internet — restrict to specific trusted IPs or keep it internal-only
• Use dedicated logins per application, not the sa account
• Schedule regular backups — SQL Server Agent (available in non-Express editions) can automate this

FAQ

Is SQL Server Express free for production use?
Yes, SQL Server Express is free with database size and resource limits suitable for smaller applications; larger workloads require a paid edition.

Related Articles

• How to Deploy an ASP.NET Application on IIS
• How to Configure Windows Firewall on a Windows VPS
• Windows Server Security Checklist for a New VPS