LAST UPDATED: 30th September 2022

How to configure Azure SQL Database

This is a guide to help you configure Azure SQL database, please be aware that configuration may differ between regions.

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

Image

STEP 2 - Create a new database

Image

STEP 3 - Create resources

  • 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. ISMSRiskManager.
  • 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 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 ISMS application.
Image

STEP 5 - Configure database and set your monthly fee

By default an expensive database connection is selected.

To select a lowest priced database click "Configure database" which is shown in Step 3 above.

Image

STEP 5.1 - Set your monthly fee

  • Click the “Service tier” drop down.
  • Select Basic for the lowest price.
  • Click the Apply button.
Image

STEP 6 - Configure database: Networking

Image

STEP 7 - Configure database: Additional settings

Image

STEP 8 - Configure database: Tags

Image

STEP 9 - Review and create your database

Image

STEP 10 - Deploy the database

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

STEP 11 - Gather the credentials

Return to the dashboard and select the ISMSRiskManager database, or the name of the database you have chosen.
"Server name" is the server name required in the 27k1 ISMS.
"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 12 - 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 ISMS will display a message to advise you when you try to connect.

Although it is not good practice, Consultants may wish to access the database from any location.

  • Ensure a strong password is used to access your azure administrator account and the SQL Database has a very strong password.
  • Set the rule name as an example to Any Location
  • Set the Start IP to 1.1.1.1 and the End IP to 255.255.255.255
Image

STEP 13 - Download and install SQl Server Management Studio

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

STEP 14 - Set up a User Account to securely access your Azure SQL Database from the 27k1 ISMS App

The 27k1 ISMS should not be allowed to access Azure SQL Database using your Azure user name and password. It is therefore required to setup a User or Users to access the database from the 27k1 ISMS 'Settings' (cog wheel) panel, using the secure user name and password.

There are several methods available to connect to SQL Database and the procedure which follows is the simplest method. More information is available at
https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage
and
https://www.mssqltips.com/sqlservertip/5242/adding-users-to-azure-sql-databases/

A simple procedure for creating an Azure SQL Database user login follows, adminstrators need to ensure that the User has the ability to manipulate data tables, db_owner is used in this example but a custom defined role will most likely be preferred for additional security.

  • Open  Microsoft SSMS  as "Run as administrator"
  • Connect to your Azure SQL Database server with SSMS
  • Create a SQL authentication login "myusername" with a very strong password, create a user mapped to the login called ‘myusername’ in a database, and then add it to the db_datareader and db_datawriter roles.
Image
  • Left click to select the "ISMSRiskManager" database.
  • Click New Query to open an SQLQuery tab.
  • Select Master from the dropdown menu in SSMS, then enter the commands in the SQLQuery tab, then Execute
-- create SQL auth login from master 
CREATE LOGIN myusername
WITH PASSWORD = 'mystrongpassword'
  • Open another query window and select "ISMSRiskManager" in the dropdown which previously was set to master.
  • Enter the commands below in the SQLQuery tab, then Execute.
-- select your db in the dropdown and create a user mapped to a login
-- it is preferred that myusername is inside the []
CREATE USER [myusername] FOR LOGIN [myusername] WITH DEFAULT_SCHEMA = dbo; -- add user to role(s) in db ALTER ROLE db_datareader ADD MEMBER [myusername]; ALTER ROLE db_datawriter ADD MEMBER [myusername];
ALTER ROLE db_owner ADD MEMBER [myusername];
  • Test the new user has access by signing into the database using the SSMS
  • Click "Options" and enter the database name in the "Connection Properties" tab
  • Enter the username and password in the "Login" and then "Connect"
Image
  • You should can now access the SQL Database from the 27k1 ISMS App using these credentials, which have no administrator privileges.
  • Tip! Any errors shown in the 27k1 ISMS sign-in dialog are received directly from the database.