LAST UPDATED: 20 July 2020
This is a guide to help you configure Azure SQL database, configuration may differ between regions.
STEP 1. Select SQL Databases from the menu of your Azure Dashboard
STEP 2. Add a new database
STEP 3. Create resources
Create a new resource group if required and provide a suitable name.
The database name should be set to ISMSRiskManager
Create a new server instance and enter the credentials.
FREE ACCOUNT: If you have a one month free account please choose the default US Location.
STEP 4. Configure database to set your monthly fee.
For Consultants and for app evaluation choose the cheapest SQL Database configuration.
Click “Configure database”
Select “looking for basic….”
Click apply to set your subscription to the lowest available.
PLEASE NOTE: To configure the lowest cost Azure SQL Database option, ensure the configuration shows as below
STEP 5. Gather the credentials
Return to the dashboard and select the ISMSRiskManager database.
STEP 6. SQL Server Credentials
The server name is displayed for later use with your user name and password.
Click through the Next buttons, all these settings can remain as default.
STEP 7. Create the database
Review and Create the database and wait until is initialised.
STEP 8. Set the Azure firewall for your location
Click Set server firewall
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, 27k1 ISMS will display a message to advise you.
STEP 9. Set the Azure firewall for any location
Although it is not good practice, Consultants may wish to access the app from any location.
- Ensure a strong password is used to access your azure administrator account and the SQL Database has also a very strong password.
- Set the rule name as an example to Any Location
- Set the Start IP to 220.127.116.11 and the End IP to 255.255.255.255
STEP 10. 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.
- Connect to your Azure SQL Database server with SSMS as an admin in master.
Create a SQL authentication login called ‘test’ with a password of ‘SuperSecret!’, create a user mapped to the login called ‘test’ in a database, and then add it to the db_datareader and db_datawriter roles.
Select Master from the dropdown menu in SSMS and click “New Query”, then enter the commands.
-- create SQL auth login from master CREATE LOGIN test WITH PASSWORD = 'SuperSecret!'
Open another query window and choose the “ISMSRiskManager” database in the dropdown.
-- select your db in the dropdown and create a user mapped to a login CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA = dbo; -- add user to role(s) in db ALTER ROLE db_datareader ADD MEMBER [test]; ALTER ROLE db_datawriter ADD MEMBER [test];
ALTER ROLE db_owner ADD MEMBER [test];
2. You can now access the SQL Database from the 27k1 ISMS App using these credentials, which have no administrator privileges.