Allow remote SQL Server access

Before you start#

StackRegent installs Microsoft SQL Server Express as the instance SQLEXPRESS on a fixed port, 1433, and by default only the server itself connects to it. Customers who want to use SQL Server Management Studio (SSMS) or another client from their own computer need the port opened. Three panel features keep that safe:

• Tools & Settings › Firewall can limit SQL Server to listed addresses.
• IP Address Banning bans addresses that keep failing to log in (the Microsoft SQL Server source, which reads Windows Application event 18456).
• Hide other clients' databases makes each customer see only the databases their login owns.

The steps are for the server administrator. Database users are SQL Server logins created per database by the customer or by you.

Step 1: Decide who may connect#

If your customers connect from fixed addresses, or you only need access from your office or an application server, limit the service. Go to Tools & Settings › Firewall, set Microsoft SQL Server under Access to services to Only these addresses, enter the addresses, click Activate and then Keep these settings. If anyone may connect, leave it at Everyone. A server set to Nobody cannot be opened in the next step.

Step 2: Open the port#

Go to Tools & Settings › Database Servers. In the row of the local SQL Server, tick Open port 1433 in the Remote connections column. The panel creates the Windows Firewall rule. Open TCP 1433 in any provider firewall too.

Step 3: Keep other clients' databases hidden#

In the same column, make sure Hide other clients' databases is ticked; it is on by default for the SQL Server that StackRegent installed. A task makes each database's first read-write user its owner and denies VIEW ANY DATABASE, so in SSMS a customer lists only their own databases. The task log says how many databases got their client as owner. The panel's own login must be in the sysadmin role for this.

Step 4: Turn on the banning source#

Open Tools & Settings › IP Address Banning › Settings and check that Microsoft SQL Server is on, with sensible Ban after and within values. Add your own networks under Trusted Addresses.

Step 5: Connect from SSMS#

In SSMS, open Connect to Server and enter:

• Server name: the server's host name followed by ,1433, for example server1.example.com,1433. The website's Connection Info tool shows the address to use.
• Authentication: SQL Server Authentication, with the database user's Login and Password.

If the SQL Server uses a self-signed certificate, tick the option in SSMS to trust the server certificate. A user who is not a database's owner does not see it in Object Explorer; they click Options, open Connection Properties and type the database name in Connect to database.

MariaDB works the same way#

For MariaDB, tick Open port 3306 on Database Servers, and set who each user may connect from: Tools & Settings › Database Hosting Preferences sets the server default (This server only, Any host or These hosts), and each database user can override it. The MariaDB banning source watches "Access denied" in its error log.

The databases chapter covers database servers, users and remote servers in full; customers find their side in the customer guide.