LAST UPDATED: 6 June 2024

How to configure Azure SQL Database

This is a guide is a basic configuration for Azure SQL Database to connect either the 27k1 RMS or the 27k1 ISMS applications.

  • Please be aware that configuration may differ between regions.

It is important to ensure the correct pricing structure in accordance with your business needs. SQL Database pricing starts around 5USD per month.

STEP 1 - Select SQL Databases from the menu of your Azure Dashboard

Image

STEP 2 - Create a new database

Image

STEP 3 - Configure database (basics)

  • Create a new resource group if one does not exist and provide a suitable name.
  • Enter a new database name with no spaces e.g. 27k1RMS
  • Create a new server if it does not exist instance and enter the credentials.
  • Choose the default Location where your data will be stored.
Image

STEP 4 - Create SQL database server (if required)

If not configured then configure a new SQL database server.

  • Select a suitable location
  • Select an Authentication method in accordance with your policies.
  • Ensure the admin login password is very string with at least 15 characters of various types.
  • Please note that the admin account must not be used for access from the 27k1 RMS application.
Image

STEP 5 - Configure database (basics)

  • Continue to configure the setting on the basics page.
  • Important: Compute + storage > Select "Configure database"
Image

STEP 5.1 - Set your monthly fee

  • Click the “Service tier” drop down.
  • Select Basic for the lowest price (Standard is recommended for improved performance)
  • Click the Apply button.
Image

STEP 6 - Configure database: Networking

Image

STEP 7 - Configure database: Security

  • WARNING of Cross-Sale: Select "Not now" to Disable Microsoft defender.
Image

STEP 8 - Configure database: Additional settings

Image

STEP 9 - Configure database: Tags

Image

STEP 10 - Review and create your database

Image

STEP 11 - Deploy the database

After clicking "Create" the database is created and deployed, this can take several minutes.
Image

STEP 12 - Gather the credentials

Return to the dashboard and select the RMS database, or the name of the database you have chosen.
"Server name" is the server name required in the 27k1 RMS.
"Connection strings" shows the user name which also is entered in the app.
For the most economical pricing plan "Basic" should be shown.

Image

STEP 13 - Set the Azure firewall for your IP Addresses

Click "Set server firewall" as shown in the step above.

The IP Address of your location is shown, copy paste this IP Address or your known IP Address range to the Start and End IP with a suitable reference.

If this is not configured, the 27k1 RMS will display a message to advise you when you try to connect.

Image

STEP 14 - Download and install SQL Server Management Studio (SSMS)

To setup an SQL Database user account download SQL Server Management Studio from the following link

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

If SSMS can access the database then the 27k1 RMS should also be able to access the database,

STEP 15 - Guidelines to allow a 27kl RMS user access to the database using MFA.

The 27k1 RMS application initially builds the database structure with tables and content. App updates may also require database changes from time to time. It it therefore important to allow the correct permissions for the Azure User. The following procedure will assist in configuring these permissions.

STEP 14.1 Connect to your SQL Database with SSMS as an Administrator

STEP 14.2 Create an SQL login from master (Select New Query then 'master' in the drop down).

-- create SQL auth login from master
CREATE LOGIN rmslogin 
WITH PASSWORD = 'VeryStrongPassword!'

STEP 14.2 Create a User mapped to a Login (Select New Query then your database name in the drop down).

-- create a user mapped to a login
CREATE USER [rmslogin] 
FOR LOGIN [rmslogin] 
WITH DEFAULT_SCHEMA = dbo; 
  
-- add user to role(s) in db 
ALTER ROLE db_datareader ADD MEMBER [rmslogin]; 
ALTER ROLE db_datawriter ADD MEMBER [rmslogin];

STEP 14.3 Create a Contained User (Select New Query then your database name in the drop down).

-- select your db in the dropdown and create a contained user
CREATE USER [rmslogin] 
WITH PASSWORD = 'VeryStrongPassword!', 
DEFAULT_SCHEMA = dbo; 
  
-- add user to role(s) in db 
ALTER ROLE db_datareader ADD MEMBER [rmslogin]; 
ALTER ROLE db_datawriter ADD MEMBER [rmslogin];

STEP 14.4 Create a Contained Microsoft Entra User (Select New Query then your database name in the drop down).

-- select your db in the dropdown and create a contained Microsoft Entra (Microsoft 365 user name)
CREATE USER [usersname@mydomain.com] 
FROM EXTERNAL PROVIDER 
WITH DEFAULT_SCHEMA = dbo;  
  
-- add user to role(s) in db 
ALTER ROLE db_datareader ADD MEMBER [rmslogin]; 
ALTER ROLE db_datawriter ADD MEMBER [rmslogin];

STEP 14.5 Assign permissions to the User (Select New Query then your database name in the drop down).

-- select your db in the dropdown
EXEC sp_addrolemember 'db_owner', [usersname@mydomain.com];