Creating an Azure Database
Guides the user through the steps necessary to create an SQL server database and ICDS (Innova Central Database Server) web server via the Microsoft Azure cloud-based platform.
1.0 - Introduction
The purpose of this document is to guide the user through the steps necessary to create an SQL server database and ICDS (Innova Central Database Server) web server via the Microsoft Azure cloud-based platform. The system diagram below details how the SQL server database and ICDS Web Server fit in to the structure of data flow within Well Seeker Pro.
This is a guide only and where necessary a company may deviate from the specific settings of the Azure account, SQL database and virtual machine to align with their financial and IT requirements.
2.0 - Getting Started
Before you start, to save time, there are some programs which should be downloaded and installed, as access to these will be required at various points throughout the document.
You will also require credit card details while setting up the Azure account, so have these to hand.
These programs and the relevant links are detailed below.
2.1 - PuTTY
Download the 32-bit MSI.
2.2 - WINSCP
During installation, select the Commander interface style.
2.3 - SQL server management studio (SSMS)
Installation of SQL Management Studio can take a while, which is normal and expected.
3.0 - Login Details and Passwords
Throughout the setup process, the user will be required to enter numerous different login details and passwords. All of these will be unique to the user. The below table lists the various details the user will be required to supply, along with a blank cell, where they can keep track of these details during the process. It is recommended that this table is completed as the user follows the steps keeping all details in one place for easy reference during and after the setup has been completed.
4.0 - Create a Microsoft Azure Account
1. If you already have a Microsoft Azure account, ignore this section and go directly to section 5
2. Go to www.azure.microsoft.com
3. Click “Try Azure for free”
4. Click “Start for free”
5. A suitable email address should be used, and the password should be very secure
a. Note, it is necessary to add payment details to allow Microsoft to verify your identity and if the “Azure Free Tier Limits” are exceeded your card will be charged.
b. You then need to confirm your identity via phone so are asked to enter your phone number for text or voice call verification
c. Once the user has successfully signed up they will be sent a welcome email by Azure, within which there is a link “Go to the portal”. Select this link and it will open the portal, where you can access the Azure account settings and functionality.
6. As this is the first time logging in, the user must enter their email and password. The Email address and Password required here are the ones entered while creating the Azure account
5.0 – Create an SQL Database
This guide does not cover user setup and login credentials within the Azure account. If required this should be looked in to by the users IT department based upon company IT security guidelines.
It is now time to create your database on Azure. The following section will guide you through database creation steps and the relevant server firewall rules which need to be added to allow the ICDS to communicate with the database.
7. Click on Home page and select SQL databases
8. Select Create SQL database
9. Select subscription as Free Trial
10. Under resource group, select Create new, and name the group appropriately
11. Enter an appropriate database name.
12. Under Server select Create new. Input a server name, admin login name and password (note these two as they will be used later). Select a location for the server that is geographically as close to the office location as possible, as this will directly affect lag time when communication with the server.
13. Select No for the elastic pool option.
14. Under Compute + Storage select Configure database. Select 10 DTUs and 250GB database max size. Select Apply. (This is a user choice and will directly affect the monthly cost of the Azure services)
15. Select Next : Networking >.Select Public endpoint. Select No regarding Azure services access and Yes to add current client IP address (this allows the current computer that you are using access through the firewall). Select Next : Additional settings >
16. For Use existing data select None. For Collation select SQL_Latin1_General_CP1_CI_AS. For Enable advanced data security select Not now. Select Next : Tags >
17. Add tag names for reference if desired
18. Select Review + create.
19. Select Create. The SQL Database will now be created and you will be taken back to the Azure Portal Home Page.
20. On the home page select your newly created SQL database, by clicking on its name
21. Once on the SQL database page select Show database connection strings and then select the JDBC tab.
User Name
DB Name
Endpoint
Take note of the endpoint, database name (what you chose to call it during setup) and user name (that was used when creating the Azure account). The port number will be 1433 by default.
6.0 - Connect to the Database
6.1 – Well Seeker Pro
Now that the database has been created in Azure, you need to connect to it remotely, to finalise the setup. This involves using Well Seeker Pro and SSMS.
22. Open Well Seeker
23. Go to File - SQL server databases - connect to remote database
24. Enter the relevant information to connect:
a. Database IP/URL: Enter the database Endpoint here
b. Database Name: Database name as created in the previous section
c. Port: 1433
d. DB Username: Server admin login
e. DB Password: Server password
f. WS Username: Master Database Username (first time use is not pre-defined)
g. WS Password: Master Database Username (first time use is not pre-defined)
25. Click connect
h. It’s likely it will say the Db schema is out of date. Select yes to update
i. It will say there are no admins and ask if you want to make this user an admin. Select yes
26. On first connecting, Well Seeker will create all the relevant tables in the SQL database
27. You can now close Well Seeker
6.2 - SQL Server Management studio (SSMS)
28. Open SQL Server Management studio
j. Server Type: Database Engine
k. Server Name: This is the Endpoint followed by a comma, followed by the Port number.
l. The Login and Password for the database are the server admin login and password
29. In the Object Explorer Tree, navigate to the tables folder under your database
30. Scroll down till you get to the DB_SETTINGS table
31. Right click on the table and select Edit Top 200 Rows
32. Insert a single record in to the table: ./icdsFileDir/
This sets the field IMAGE_PATH for the remote Db, which is where any logos and attached files will be stored.
7.0 – Create a Virtual Machine
A virtual machine instance is a virtual server in Azure’s Compute Cloud for running applications on the Azure infrastructure. Azure is a comprehensive, evolving cloud computing platform; virtual machine is a service that allows business subscribers to run application programs in the computing environment. This is where the ICDS will be installed.
33. Click on the home page, select Virtual machines
34. Select Add and Virtual Machine
35. In the Basics section.
a. Select your subscription and the same resource group used for the SQL database.
b. Name the virtual machine accordingly.
c. Select a location for the virtual machine that is geographically as close to the office location as possible, as this will directly affect lag time when communication with the virtual machine.
d. Select the desired availability options.
e. Select the Ubuntu Server 18.04 LTS – Gen1 image.
f. Select No for Azure Spot instance
g. Select required size. Standard B1s is recommended. This can be changed based upon requirement
h. Authentication type as SSH public key
i. Set a suitable user name for the virtual machine. Take note of this name as you will require it later
j. SSH public key source, select Generate new key pair
k. Set a suitable key pair name
l. Public inbound ports, select Allow selected ports
m. Select inbound ports, select SSH (22)
n. Select Next : Disks >
36. In the Disks section
a. OS disk type, select Standard SSD
b. Encryption type, select Default
c. Select Next : Networking >.
37. In the Networking section
a. Virtual network, select new default option
b. Subnet, select new default option
c. Public IP, select new default option
d. NIC network security group, select Advanced
e. Configure network security group, select new Create new
i. Add temporary rules inbound and outbound that allow all IP addresses on all Ports to pass through the fire wall. This is only for the setup phase and can be adjusted when the setup and testing is complete
ii. Take note of the network security group name, as you will require this later
f. Accelerated networking, select Off
g. Load balancing, select No
h. Select Next : Management >
38. In the Management section
a. Enable basic plan for free, select Yes
b. Boot diagnostics, select Enabled with managed storage account
c. OS guest diagnostics, select Off
d. System assigned managed identity, select Off
e. Login with AAD credentials, select Off
f. Enable auto-shutdown, select Off
g. Enable backup, select On
h. Recovery Services vault, select new default option
i. Backup policy, select new default option
j. Select Next : Advanced >
39. In the Advanced section
a. Leave all as default and select Next : Tags >
40. The user can add tags if desired
a. Select Next : Review + create >
41. Select Create
42. The below window will pop up. Select Download private key and create resource
43. This will create a .pem file. Store this .pem file in a safe location, as it is critical for a later stage.
44. Wait while the virtual machine is deployed
45. Once it is deployed, go to the Home page, select All resources and select the virtual machine. Take note of the public IP address, as you will require this later.
8.0 – Update SQL Server Firewall and Virtual Network Settings
Now that the virtual machine and a virtual network has been created, the user must update the SQL server security rules, to allow communication between:
The ICDS (located on the virtual machine) and the SQL database
Well Seeker and the SQL database
SQL Server Management Studio and the SQL database
The admins PC and the virtual machine when using WinSCP and PuTTY
46. In the Portal, select Home then All Resources. Select the resource with Type SQL server
47. Select Show firewall settings
48. At the bottom of the page select Add existing virtual network
49. Give the rule a suitable name and select the virtual network (-vnet) from the dropdown list, that was created during the setup of the virtual machine (there should only be one)
The SQL server now has security rules that only allow the SQL database to communicate with any other device on your virtual network i.e., the virtual machine and any IP addresses or ranges that we have specified during the SQL database setup, on port 1433.
9.0 – puTTYgen
50. Open puTTYgen.
51. Select load and select the PEM file (you will have to change the file type filter to all files (*.*)
52. Click OK to the notice.
53. Click save private key, it is ok not to use a passphrase. Again, keep this file in a safe place as it is very important.
54. Close puTTYgen.
10.0 - WinSPC
55. Open win SCP.
56. Select protocol as SFTP.
57. Enter the virtual machines public IP address in the hostname.
58. Username is the virtual machine username created during the virtual machine setup
59. Password is blank
60. Port is 22
61. Click on advanced -> SSH -> Authentication and select the browse option to the right of the private key input cell and select the private key that was created with puTTY gen. Select OK.
62. Click login and click yes to the security notice.
63. You will now have the below, where you have created a drag and drop file explorer interface to the virtual machine. If you do not see the virtual machine username folder on the right, its likely because you are already in it. Just select the only folder showing and it should then display the virtual machine username folder.
10.1 – ICDS files
64. Download the ICDS files via the link provided by Innova.
65. Remove the .linux extension from the icdsServer.linux file.
66. Drag this Linux executable icdsServer file to the virtual machine username folder below right.
67. You will get the below left popup. Select OK and you get the below right.
68. Open the attached config .JSON in a text editor e.g. Notepad
a) CompanyID: Type Company Name (this will be supplied by Innova and must be written in EXACTLY the same way as its supplied). Make sure Company Name is inside double quotes
b) Port: Usually enter the port field as 42000 (no quotes) – this is the port which will be entered into the Well Seeker RT Data exchange for communication with the ICDS
c) devmode: Leave as false (no quotes)
d) sqluser: Set to the SQL Server master username used to access the database. Make sure this is inside double quotes
e) sqlpass: Set to the SQL Server master password used to access the database. Make sure this is inside double quotes
f) sqlsvr: Set to the SQL database Endpoint. Make sure this is inside double quotes.
g) sqldbname: Set to the name of the database you wish the ICDS to connect to. This is the database name that you see when logged in to SQL server Management Studio. Make sure this is inside double quotes
h) sqlport: Set to the port used to connect to the SQL server database (no quotes), usually this is 41433
i) externalPath: Specifies where external files are stored. Input useDatabase (stores files on the virtual machine)
j) sqldbnamewits: If a user is pushing and storing WITs data to a database separate to the Well Seeker database, insert the name of the database here. Make sure this is inside double quotes. If this function is not used then leave a the doubles quotes empty
k) transferWits: If pushing WITs data to a second database then enter true, if not enter false
l) allowActivePush: If false is entered then a computer can push data back to a server database regardless of the well status. If true is entered then a computer can push data back to a server database only when the well status is Upcoming, Active or Standby. The well status is selected in the Daily Reports dialog
m) awsAccessKeyID: This refers to an AWS feature. Leave blank
n) awsSecretKey: : This refers to an AWS feature. Leave blank
o) awsRegion: : This refers to an AWS feature. Leave blank
p) s3Bucket: This refers to an AWS feature. Leave blank
q) Leave all other fields as they are
69. Once completed, drag the config.JSON file to the same directory as the icdsServer file
70. In the same sub directory as the icdsServer executable is located, create a directory called icdsFileDir.
a. To do this, right click under the icdsServer file and select New – Directory.
b. Your folder should now look like the below
c. This folder is where the logos and external files added to the remote database will be saved.
71. Close WinSCP. You will get the below. Selected No, so that the workspace is saved.
11.0 – puTTY
72. Open puTTY
73. In the Host Name Cell: Type virtual machine username@virtual machine public IP , both of which we took note of earlier. Add this information to the login details section of this document.
a. Select the port as 22 and name the session in the saved sessions box.
74. Then click on Connection - SSH – Auth.
75. Click Browse and select the ppk file created earlier.
76. Click open.
77. Click Yes to the security alert message. You then get the below.
78. Follow the instructions on the following link to install node on the virtual machine: https://docs.aws.amazon.com/sdk-for-javascript/v2/developer-guide/setting-up-node-on-ec2-instance.html
a. There are a set of commands which need to be entered into Putty. At the end of each line there is a copy button which copies the code. If you then right click on the bottom line in the putty interface, it pastes the code in for you.
b. Hit enter and then move onto the next code.
c. Your putty interface should look like the below right by the end.
79. Enter the following into the command line to install PM2: npm install pm2 -g
d. You should get the below after entering this.
e. PM2 is just a program which allows you to run a file as a service i.e. if it crashes or the computer restarts it will restart the program automatically.
80. Check pm2 is running by typing pm2 ls
f. You should get below after entering this.
81. Update node. Type the following command: nvm install 4.4.5 check version number from node.js website
a. You should get the below after entering this.
b. Node.js is a java script library for linux which is required to run the program PM2.
82. Type: sudo apt-get update
a. You should get the below after entering this.
b. At this point close and reopen putty. If you do not do this, when you get to the point where you type a pm2 command, putty will tell you it is not recognised. Not sure why this happens, but it is a consistent error. Closing and reopening puTTY allows you to proceed without any issues.
83. You now need to change the file permissions using the following “Change Mode” command: chmod 777 icdsServer
a. This makes the file readable, writable, and executable by everyone.
b. If you do not do this step, then in the next step you will likely get an errored message like the one below.
84. Now setup default instance using config file: pm2 start icdsServer
c. You should get the below.
d. Note that the “icdsServer” is the name of the linux file which was placed in the virtual machine via WinSPC. If you have changed the name for any reason, then you will need to adjust the command by replacing “icdsServer” with whatever the name of the file you added is.
85. Save the current pm2 settings so it starts correctly after a server restart: pm2 save
11.1 - Useful puTTY Commands
The following is a list of useful commands which can be used in puTTY and relate to pm2. These may be helpful if there are any issues / errors when starting the pm2 application.
86. Stop any current pm2 icds processes: pm2 stop icdsServer
87. Delete old pm2 config params: pm2 delete icdsServer
88. Kill any pm2 process which is running: pm2 kill
89. View list of pm2 running apps: pm2 ls
90. View logs: pm2 logs
12.0 – Well Permissions
Well permissions have been put in place to give the admin control over what computers can pull what wells via the remote data fetch functionality from the SQL server database, if required. This might be the case when a company employs consultant rig personnel, who they do not want to have access to their historical well data. In order to access and setup well permissions follow the below steps:
91. Log on to the SQL server database via Well Seeker Pro
92. Once connected, select Tools > Well Permissions. This will open the Well Permissions dialog. Note that only Well Seeker users with Admin permissions can access the Well Permissions dialog
93. Well Permissions can be set to two states:
a. Enable Individual Well Permissions disabled. To select this option uncheck the Enable Individual Well Permissions check box. This allows any company computer with Well Seeker installed on and a user who has the virtual machine IP address and port number details to pull any and all well data from the SQL server database via the Remote Data Fetch dialog
b. Enable Individual Well Permissions enabled. To select this option check the Enable Individual Well Permissions check box. When this is selected no computers are able to pull down well data via the Remote Data Fetch dialog, even if the user has a company computer with Well Seeker installed and the user has the virtual machine IP address and port number details. Only computers entered in the table will be able to pull down a specified well or job number, when the active check box is selected.
The Computer Name for a computer can be found by right clicking on This PC and selecting Properties. Depending on the windows version the user has, the computer name will come under one of a number of different titles. These details will need to be updated on a well by well basis in order to allow the rig personnel access to the required wells
13.0 – Testing ICDS Server Connection
With the ICDS Server now running it is important to test if this is working correctly. This can be done as follows:
94. Open the RT data exchange in WS and push an operator to the remote Db.
95. Create a new Db and then do a remote data fetch to pull in the well you have just added to the remote Db.
96. Open the RT data exchange and connect to the well you have added on the remote Db. Add some new data to the local Db and ensure that it pulls into the remote Db correctly.
97. Add a new plan to the remote Db and check to see if you receive the relevant notification in the messages window.
98. Add a couple of logos and external files to the remote database to ensure that these save correctly.
a. NOTE: For the logos and external files to work correctly, the user must have the Push External Files option selected in Well Seeker
14.0 – Setting up Final Network Security Rules
Now that everything has been setup and tested, the final step is to adjust the network security rules of the virtual machine to make them more secure. If there is an issue connecting after this stage, you can then be sure that the issue is related to the setup of these rules and not any of the other previous steps.
The below settings are a suggestion, which should be adjusted where required by the user’s IT department.
The virtual machine has inbound and outbound communications from/to:
WS - data fetch and exchange
WinSCP and PuTTY (during setup and troubleshooting)
SQL database
When creating a network security rules, the 2 main pieces of information required are the Port and the IP address. Set the virtual machine Incoming and Outgoing Security rules as displayed in the below sub-section:
14.1 – Virtual Machine Inbound Security Rules
99. Select the All Resources page
100. Select the Network security group
101. Under settings select Inbound security rules
102. By default the network security group will have three rules in place, which cannot be deleted or edited. These allow all virtual network comms, all azure load balancer comms and deny all other comms coming in to the virtual network.
103. During setup of the virtual machines network we added in a rule that allowed all inbound comms via any port from source to any destination. This allowed us to test the setup without interference from the security settings. Now that setup has been tested successfully we require to put in place tighter security settings, that only allow through the required comms from the Well Seeker Data Fetch and Data Exchange functions and the SQL database. As the SQL database is on the virtual network, the AllowVnetInBound rule covers these comms.
104. Select Add. Input a rule for port 22, used for WinSCP comms to the virtual machine
Public IP of WinSCP / PuTTY user
105. Select Add. Input a second rule for port 42000, used for Well Seeker Pro’s Remote Data Exchange and Data Fetch utilities
106. Your inbound rules should now resemble the below
Public IP of WinSCP user
14.2 – Virtual Machine Outbound Security Rules
107. Select the All Resources page
108. Select the Network security group
109. Under settings select Outbound security rules
110. By default the network security group will have three rules in place, which cannot be deleted or edited. These allow all virtual network comms, all internet comms and deny all other comms coming in to the virtual network.
111. During setup of the virtual machines network we added in a rule that allowed all outbound comms via any port from source to any destination. This allowed us to test the setup without interference from the security settings. Now that setup has been tested successfully we require to put in place tighter security settings, that only allow through the required comms from the Well Seeker Data Fetch and Data Exchange functions and the SQL database. As the SQL database is on the virtual network, the AllowVnetOutBound rule covers these comms.
112. Select Add. Input a rule for port 22, used for WinSCP comms to the virtual machine
Public IP of WinSCP / PuTTY user
113. Select Add. Input a second rule for any port, used for Well Seeker Pro’s Remote Data Exchange and Data Fetch utilities
114. Select Add. Add a third rule that denies outbound internet traffic, which has a larger priority value than the previous two rules
115. Your outbound rules should now resemble the below
Public IP of WinSCP user
15.0 – Troubleshooting Connectivity Issues
There may be times when a user is experiencing issues with logging in to the SQL server database via SSMS or Well Seeker, or where the external files / data push and pull functions are not working. The below sections will guide the user through some problem solving steps that will help isolate and solve the issue in question.
15.1 – SQL Server Management Studio (SSMS) Login
If the user receives an error message and cannot log in to the SQL database via SSMS then follow the below steps:
1. Check the user’s computer has internet connectivity
2. Check the Server type is Database Engine
3. Check the Server name is the SQL database endpoint, immediately followed by a comma, then a space and then the SQL database port number, which is usually 41433. For example:
a. SQL database Endpoint = exampleendpoint
b. SQL database Port = 41433
c. Server name entered = exampleendpoint, 41433
4. Check the authentication type is SQL Server Authentication
5. Check the Login entered is the SQL database Username the user created when setting up the SQL database
6. Check the Password is the SQL database password the user created when setting up the SQL database
7. If the above checks still haven’t fixed the problem the remaining potential cause is the SQL database security settings. Log on to the Azure account, select the SQL database in question and review the security settings.
Note that if individual IP addresses have been specified in the security rules, it is not uncommon for a network router provided by a third party to change a computers public IP address. To see a computers public IP go to ip4.me in your web browser
15.2 – Well Seeker Pro Database Login
If the user receives an error message and cannot log in to the SQL server database via Well Seeker Pro then follow the below steps:
1. Check the user’s computer has internet connectivity
2. Check the Database IP / URL is the SQL database endpoint
3. Check the Database Name is the name of the SQL server database as it appears when viewed in SSMS
4. Check the Port is the port number of the SQL database, usually 41433
5. Check the DB Username is the SQL database Username the user created when setting up the SQL database. If the user has since created an additional user in SSMS, this user name can also be used, as long as they have the clearance to access the database entered in Database Name cell
6. Check the DB Password is the SQL database password the user created when setting up the SQL database. If the user has since created an additional user in SSMS, this user password can also be used, as long as they have the clearance to access the database entered in Database Name cell
7. Check the WS Username is correct. Each WS username has been created in the User Permissions dialog by a user with administrator permissions.
If the administrator cannot log on to the SQL server database via Well Seeker then they can log on to the SQL database via SSMS and look in the dbo.USER_PERMISSIONS table to see if the WS username exists
8. Check the WS Password is correct. If the user has forgotten their password then the admin can delete the users password from the User Permissions dialog. The next time the user logs in they will be asked to enter a new password
9. If the above checks still haven’t fixed the problem the remaining potential cause is the SQL database security settings. Log on to the Azure account, select the SQL database in question and review the security settings.
Note that if individual IP addresses have been specified in the security rules, it is not uncommon for a network router provided by a third party to change a computers public IP address. To see a computers public IP go to ip4.me in your web browser
15.3 – Real Time Data Exchange / Remote Data Fetch
The Real Time Data Exchange is design to give a user the ability to push data for a specific well from their Well Seeker database, back to the SQL server database. The Remote Data Fetch is designed to give a user the ability to pull data for specific wells from the SQL server database to their Well Seeker database. In order to achieve this there are many things that have to be set up correctly
For both the Real Time Data Exchange and the Remote Data Fetch:
Correct IP and port number input in Data Exchange / Data Fetch dialog. These are the IP of the virtual machine and the port number specified in the .json file on the virtual machine
Correct credentials entered in the .json file on the virtual machine. See 10.1 – ICDS files, step 68 for details
The ICDS server has to be running without error on the virtual machine. See 11.0 – PuTTY for details
The virtual machine security settings have to be correct within the Azure account. See 14.2 virtual machine for details
The SQL database security settings have to be correct within the Azure account. See 14.1 SQL database for details
For the Real Time Data Exchange only:
Correct well selected in the Data Exchange dialog. This well must already exist on the server data base
If the .json file on the virtual machine has allowActivePush as false then a computer can push data back to a server database regardless of the well status. If true is entered then a computer can push data back to a server database only when the well status is Upcoming, Active or Standby. The well status is selected in the Daily Reports dialog. See 10.1 – ICDS files, step 68 for details
For the Remote Data Fetch only:
If the Well Permissions dialog on the SQL server database has Enable Individual Well Permissions selected, then only wells included in the Well Permissions table will be able to fetch specific wells. In the case that the user does not have permission to download well data, the user will be able to connect to the SQL server database and see the database tree, but there will be no structures below facility level to select, as below.
See 12.0 – Well Permissions for details
15.4 – External Files Not Saving
External files for the SQL database are stored on the virtual machine and rely upon a similar data transmission method as the real time data exchange and remote data fetch functions. If you are experiencing issues saving or accessing external files and logos, either from the SQL database, or when the data has been pulled down to a local database, follow the below steps:
1. Ensure the real time data exchange and remote data fetch functions are working correctly. See 15.3 – Real Time Data Exchange / Remote Data Fetch for details. If they are then it rules out a number of potential causes for the issue. If both these functions are working, but you are still experiencing issues with the external files functions then proceed to the next step
2. Ensure that during the database creation the step was followed that created the external files image path in the dbo.DB_SETTINGS table. See 6.2 - SQL Server Management studio (SSMS), step 32 for details
3. Ensure that the option Push External Files is selected on all computers trying to use the functionality
Last updated
