How to setup Windows Server? Part 3: SQL Server

If you are a non-techy or do not want the headache, I can offer to install it for you. You can just put a comment on this post or reach me out on Twitter at @ghulamostafa.

We have our Windows Server up and running (Part 1) with IIS (Part 2). It is time to install SQL Server for the database. For this article, we would go for MS SQL Server 2019 Express.

You should be able to download it from the following link and I would suggest you should download it in the Server Directly so you do not have to copy and paste it from your local computer again.

I would recommend going for Basic Installation if you do not want any complexities. It would install all what you would need.

Once the installation is completed, you will be presented with the information about the installation that includes the folders where it was installed and the default connection string. Please copy the default connection string and keep it somewhere with you. You may click Connect Now to check the connection. We will need to install SQL Server Management Studio (SSMS) to use the GUI for connecting to our database server. The button for installation of SSMS can be found on the same screen.

Otherwise, you can download SSMS from this link. Once downloaded, install SSMS with the provided wizard.

Once the installation has been completed for SSMS, you can find it in Start Menu under Microsoft SQL Server Tools as Microsoft SQL Server Management Studio.

After opening MS SQL Server for this first time, you can login using the Windows Credentials.

But to be able to connect remotely (from desktop), we need to setup the Service Account (sa). After logging in the SQL Server, click New Query and paste the following script.

ALTER LOGIN sa ENABLE ;  
GO  
ALTER LOGIN sa WITH PASSWORD = \'<enterStrongPasswordHere>\' ;  
GO

Now that we have the sa account enabled, we need to allow Mixed Mode Authentication for SQL Server. We can achieve that by going to the properties of SQL Server.

In the security tab, under Server authentication, choose SQL Server and Windows Authentication mode and click okay.

We need to open the ports for remote connection. In your server, run the following command as an Administrator.

netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT

After enabling the port for SQL Server, we need to enable the port for the Program. Open up Windows Firewall and under Inbound Rules select New Rule and then New Inbound Rule Wizard.

Keep the rule type as Program and click next.

In Program path, paste where the InstanceName would be the one on your server. For this article, we have SQLEXPRESS.

C:\\Program Files\\Microsoft SQL Server\\MSSQL15.<InstanceName>\\MSSQL\\Binn\\sqlservr.exe

In Actions, choose keep Allow the connection selected and click next.

In Profile, keep all 3 (Domain, Private and Public) selected. Go next and give it a name (something you can recognize it with) and click finish.

Now we have to update the Port inside SQL Server configurations. You can find SQL Server configurations under SQL Server in Start Menu.

Expand SQL Server Network Configuration and then select Protocols for SQLEXPRESS. The TCP/IP is disabled by default, double click and enable from properties window.

In the second tab, IP Address, scroll down and remove the value from TCP Dynamic Ports and in TCP Port keep 1433. Click okay.

We need to restart SQL Server service after doing all those changes.

You should be able to login to the server remotely from your desktop machine or development machine. Just enter the IP address in the server Server name and use the sa credentials.

And you are now connected.

For installing IIS, please click this link.

For deploy Windows Server, please click this link.