The NAMRS Pilot system was designed and developed as an online web-based application that would collect data about adult maltreatment from states and allow access to that data by analysts. It collected data for the Agency Component, the Case Component, and the Key Indicators Component. Data for the Agency Component and Key Indicators Component were entered into the system through electronic forms on the website. The Case Component data were submitted in a standardized XML file, created by each state and uploaded through the website.
The major architectural elements of the system, shown in Figure 5.1, were:
The NAMRS Pilot Website--Users interacted with this website. This website enabled users to enter or upload their data components, view data about the components, and perform all the other activities required to administer the NAMRS Pilot system.
The NAMRS Pilot Database--All the data components entered or uploaded through the website were stored in the database. The database also contained other data such as user account data, and value lookups.
The NAMRS Pilot Data Warehouse--The data components were loaded into the data warehouse from the database so that they were easy to query by analysts. The data warehouse was accessible from the Internet and users could use the analytical tools of their choice.
|FIGURE 5.1. NAMRS Pilot Data Flow|
This chapter discusses the following topics:
The NAMRS Pilot Data Model section discusses the main conceptual entities and their relationships.
The NAMRS Pilot Functionality section discusses each of the three major components from a user perspective.
The NAMRS Pilot Architecture section provides a general overview of the architecture of the NAMRS Pilot and discusses each component.
The NAMRS Pilot Security section discusses the maintenance of security of the three major components.
The NAMRS Pilot Data Model
|FIGURE 5.2. NAMRS Pilot Data Model|
There were eight components of the data model:
The Agency Component captured key features of the state agency that collected and reported APS data. There was one Agency record for each year/state pair.
The Key Indicators Component collected aggregated data based on computations from each state agency that did not provide Case Component data. There was one Key Indicators record for each year/state pair. States that provided Case Component data did not have a Key Indicators Component record.
The Case Component collected data on all APS cases that were closed during the reporting period (the FFY). The Case Component consisted of the Case Component Dataset, along with all its descendants (described below). There was one Case Component Dataset record for each year/state pair. Each Case Component Dataset had one or more investigations associated with it. This entity served as a container for all the investigations for one year/state.
An investigation record represented a single investigation within a Case Component Dataset. An investigation was associated with a single Case Component Dataset and there could be one or more investigations for each Case Component Dataset.
A client record represented a single client within a particular investigation. A client was associated with a single investigation and there could be one or more clients per investigation. A client could be associated with zero or more relationships (to perpetrator) and had to be associated with one or more maltreatment (at least one substantiated maltreatment).
A perpetrator record represented a single perpetrator within a particular investigation. A perpetrator would be associated with a single investigation and there could be zero or more perpetrators per investigation. A perpetrator could be associated with zero or more relationships (to client) and must be associated with at least one substantiated maltreatment. Perpetrators could not be associated with unsubstantiated maltreatments.
A maltreatment record represented a single maltreatment of a client (and might be associated with perpetrator(s)). A maltreatment must be associated with one client and could be associated with zero or more perpetrators. There must be at least one substantiated maltreatment associated with each client if there was a perpetrator.
A relationship record represented a relationship (e.g., kinship) between a client and perpetrator. A relationship must be associated with a one client and one perpetrator (although a relationship was not required between any particular client/perpetrator pair).
The NAMRS Pilot Functionality
This section discusses the user functionality of each of the three main components.
The NAMRS Pilot Website
The NAMRS Pilot website provided a straightforward user interface that allowed users to access the NAMRS Pilot system. Each of the pages in the NAMRS Pilot Website was designed to be clean and simple. On the pages that were used to enter and upload either Agency Component or Key Indicators data (the most frequently used pages in the website), there were helpful tooltips on most entry fields to provide guidance to users. On long report pages with a large number of items (e.g., frequency counts), the report sections were contracted so that the user could easily view only the items they wanted to view. Reports could also be downloaded as PDF files, so that they could be easily printed. There was a page on the website that displayed announcements to all users to keep them informed about the NAMRS Pilot system. Finally, there was a resource section of the website that provided helpful text and files for users.
Website User Roles
Every NAMRS Website user was assigned a single role on the website. Each role, described below, allowed the user to access different pages or perform different functions on pages.
State users could enter/upload data and view reports for their own state. They could not access any data for a state other than their own.
Federal users could view data and reports for all states. They could not change any state data.
Technical users (including liaisons) could enter/upload data and view reports for all states. Additionally, they could accept/reject data components for any state. Technical users managed resources and announcements displayed on the website to all users.
Administrator users had the same access as a technical user. Additionally, administrators could manage (create/edit/disable) users in the website.
As mentioned above, the main function of the NAMRS Pilot Website was to enable states to input data into the system. Figure 5.3 illustrates the general workflow for data input.
|FIGURE 5.3. General Workflow for Data Input|
Explanations for the workflow steps follow:
Save Data Component--A state user could access the NAMRS Pilot Website and enter data for the Agency Component and Key Indicators Component. The data were entered into easy-to-use forms on the website. State users did not have to enter all the data in one session. They could enter some of the data for one or more data components, save the data, and return later to continue.
Case Component data was uploaded to the NAMRS Pilot Website as an XML file that conformed to the NAMRS Pilot XML schema. To upload the XML file, the user would browse to the Case Component page on the website. This page provided the ability to upload the XML file.
Validate the Data Component--When the user filled out the forms for the Agency Component or Key Indicator Component data, all the data entered into the forms were validated before being saved by the system. Some validation happened in real time as the user was typing (i.e., the user could not enter alphabetic data into fields that only accepted numeric data), and some validation happened after the user clicked the save button (e.g., the user was notified that they had forgotten to enter a required field). The user must have fixed the data before it could be saved into the system.
There was a different validation process for the Case Component data after an XML file was uploaded. The XML file could be quite large and contain many thousands of investigations in it, or the system might be validating a different state's data, therefore validation might have taken a few minutes to complete. After the users uploaded the Case XML file, they saw a message on the NAMRS Pilot Website informing them that validation might take some time, and that they would receive an email when validation was complete so that they could return to the site and view the results.
Review Data Component--In the case of the Agency or Key Indicators Components, the users could enter the data on the website over several sessions, and it was validated each time they saved their data. When they were finished entering their Agency or Key Indicators Component data, they were encouraged to review the entries in the forms one last time before actually submitting it for approval. The users could change the data on the forms as desired until submitting it for approval.
For the Case Component, after the Case Component XML file had been uploaded and validated, the users would return to the website and could review different counts, error/warning messages, and other statistics about their Case Component data so that they could make a decision about whether they wanted to submit their data. If they decided that the data were not acceptable, they could upload a new Case Component XML file and repeat the process. If the data were acceptable, they would continue to the next step.
Submit Data Component--After state users finished reviewing their data component, they could submit the data for approval. Submitting the data involved simply clicking a submit for approval button.
Technical Review of Data Submission--A technical user reviewed the data component after a data component submission for approval. For Agency and Key Indicators Components, the technical user would review the same forms that the user filled out and make a decision about whether to accept the data. For Case Component data, the technical user would review the same counts, error/warning messages, and other statistics about the Case Component data to determine whether to accept the data. When the technical user entered a decision about whether to accept the data, the system would send an email to the state users advising them of the decision and giving some further instructions. In addition, the technical user would follow up with the state users to explain why the data were or were not accepted into the system, and provide assistance. Significant TA was provided at this juncture to discuss potential corrections that might be appropriate to the data submission, based either on the automated review or on the review of mapping forms or earlier discussions with the state users.
As the steps above have illustrated, there were two very similar workflows, one for the Agency and Key Indicators Components, and one for the Case Component. Figure 5.4 and Figure 5.5 illustrate these two workflows.
|FIGURE 5.4. Agency and Key Indicators Component Workflow|
|FIGURE 5.5. Case Component Workflow|
All NAMRS component data were validated before being saved into the system. The Agency and Key Indicators Components data were entered into the system by the state user through forms in the website, and were validated instantly when the user clicked the save button. The Case Component data were uploaded as an XML file, and could take several minutes to validate.
Agency and Key Indicators Components Validation
Data Type--All input fields only accepted the proper data type. Numeric fields (such as the FTE counts) only allowed the user to type numbers. Fields that accepted general text accepted any character. As a second level of security, the user repeated the same validation after the form had been submitted.
Data Length--All comment fields were checked for length and must be less than or equal to 5000 characters in length. As a second level of security, the user repeated the same validation after the form had been submitted.
Required Fields--Each of the two data components had some required fields, and there was logic to validate that the user had entered data into those fields.
Data Rules--The Key Indicators Component had several rules that applied logic to determine validity of the data entered. The Agency Component data had no data rules.
Appendix F lists all validation criteria for the Agency Component and the Key Indicators Component.
Case Component Validation
After a user uploaded a Case Component XML file, two types of validation happened. Figure 5.6 illustrates the validations and shows the validation status as well as the error/warning messages.
|FIGURE 5.6. Case Component Validation Steps|
XML Validation--The XML file was validated against the NAMRS XML schema definition (XSD). Validating the XML against the XSD ensured that the XML file could be read correctly. The XSD ensured that the XML had the proper XML tags, XML tag nesting structure, data types, and unique identifiers (IDs). If there were no XML errors then the file presented as valid and moved on to data rule validation. If there were any errors at all, then the file was invalid, and validation stopped.
Data Rule Validation
If the file passed XML validation, then several rules applied logic to the data in the file to determine the validity of the data. One data validation rule is that each client must have one or more associated maltreatments. Invalid data (as defined by the data rules) were removed from the XML. After validation was completed and invalid data were removed from the XML, a check was done to see if there were any remaining investigations in the XML data. If so, then validation was deemed a success, and all remaining valid data were imported into the database. However, if all investigations had been removed from the file, then validation was deemed to have failed and no data were imported into the database. Appendix F lists the data rules.14
After validation was complete, an email was sent to the state users informing them whether the XML had passed or failed validation, and instructing them to return to the NAMRS Pilot Website to get more information, including the following:
Validation Status--The validation status was a message indicating whether the validation succeeded or failed.
Validation Errors--If there were errors from the XML validation, then the XML errors displayed. These XML errors gave the state users enough information to correct their XML structure. The state users received these same XML errors if they used an XML validation application before uploading.
Validation Warnings--These were warnings about the data rules. The content of the warning messages are detailed below:
- Rule Number--The rule number is a number for the rule and can be used to reference the particular violated rule. In the example on the next page, the rule number is N1040.
- Rule Explanation--The rule explanation was a readable explanation of the rule. In the example on the next page, it states that the report date must be less than or equal to the investigation start date. The report date and the investigation date were part of the Investigation entity.
- Path Information--The error contained path information that enabled a user to locate the XML element that contained the data rule violation. In the example on the next page, it says that the error resides in the investigation XML tag, which has an investigationId of abc1234def. This information makes it very straightforward for a user or developer to locate the actual error within the XML file using an XML Editor application. (It also makes it easy for the state users to look up their data in their own database.)
- Action--The action specified what occurred to resolve this data rule violation. The example on the next page states, "This element was removed." It is referring to the XML element in the Path portion of the message, meaning that the entire investigation identified in the path (i.e., investigation with ID of abc1234def) was removed from the XML (including all the clients, maltreatments, and other data element tags, which were part of the indicated investigation).
Entity Counts--The entity counts were counts of the XML tags for the investigation, client, maltreatment, perpetrator, and relationship tags. Each count showed the number of tags that were found before and after the data rule validation step. This helped the state users determine whether the NAMRS Pilot system actually imported their XML accurately, as well as how much of their data were removed due to data rule violations.
Identifier Counts--The Identifier Counts were counts of distinct IDs from the XML. The Identifier Counts displayed the number of unique investigation, client, facility, and perpetrator IDs. (Note that each ID must be encrypted and hashed.)
Case Data Reports--The case data reports were various viewable data reports. These included frequency counts, key indicators, and additional information.
It was expected (and observed in the pilot) that all Case XML files would have some data rule violations that display as warnings. A typical error message display is shown below.
Rule N1040 had errors.
Rule: If the Report Date and Investigation Start Date are both not empty, then the Report Date must be less than or equal to the Investigation Start Date.
Error: Path: investigation/Ids: investigationId=abc1234def Action: This element was removed.
The information above gave the state users enough information to correct their data, if there were actual errors. If there were no errors, but rather just data warnings, then the state users had enough information to determine if they could submit their Case Component for approval. If the state users decided that there were too many warnings they would make changes to their data (or to their software that builds their XML file), re-upload the file, and go through validation again.
NAMRS Pilot Database
The NAMRS Pilot Database was a SQL Server database. The database contained 104 tables split into the functional groups below. State users entered/uploaded the vast majority of the data, which were the NAMRS Components. The NAMRS Pilot System Documentation, Volume 2, includes an Entity Relationship Diagram for the database.
Announcements and Resources
The NAMRS Website displayed announcements on the home page. It also had a resources section which displayed files and text resources to assist state users with using the NAMRS Pilot. All the data required for the announcements and resources was contained within these tables. These tables were part of the core schema.
Case Component XML Validation Rules
The Case Component Loader executed validation rules against the Case XML files. Validation rules were LINQs performed against the XML. All the data required for validation, including the actual LINQs, were contained within these tables. These tables were part of the core schema.
The NAMRS Website used an open-source component for Error Logging Modules and Handlers (ELMAH) for error logging. This component required a single table in the database for saving the log messages. This table was part of the DBO schema.
All the tables in the lookup functional group contained rows that were text values for different tables within the system. Each table only had a few rows, generally less than 10-20 rows. These tables were part of the Lookup schema.
The tables in this functional group held all the actual NAMRS component data (the data represented by the conceptual model above), as well as some data used for reporting that was calculated from the component data. These tables were part of the NAMRS schema.
Users and Identity
These tables were used for website authentication, authorization, and user information.
The overall database schema was quite straightforward, and changes were able to be made at any time. Over time, the NAMRS data model is expected to change as requirements evolve, and the database structure will need to be modified. Changes to the data components would require changes to: (1) the database; (2) the forms on the website or the software that imports data from the Case Component XML files into the database; (3) the validation functions; and (4) any reports or extracts that use the affected data.
NAMRS Pilot Warehouse
The NAMRS Pilot Data Warehouse provided an easy-to-use database that could be queried using any statistical analysis application, data visualization application, or SQL query application. The data warehouse was tested with SPSS, SAS, Tableau, and SSMS, but there are many different applications that fall into each of these categories. The NAMRS Pilot Data Warehouse was designed to be accessible over the Internet. Any application that can establish an Open Database Connectivity (ODBC) connection could access it, as illustrated in Figure 5.7. ODBC, supported by most applications that are designed to work with data, is a mature protocol for accessing databases.
|FIGURE 5.7. Analyst Connects Directly to the Data Warehouse|
The structure of the NAMRS Pilot Data Warehouse closely matched the NAMRS data model shown in Figure 5.2. It contained the NAMRS Pilot data from the Case Component tables in the NAMRS data model. Data that were not part of the Case Component, such as the announcements and resources, Case Component XML validation rules, error logging, and user and identity tables were not contained in the data warehouse. The NAMRS Pilot Data Warehouse was not designed to include the Agency or Key Indicators data, which were available in Excel format extracts. The structure of the data warehouse was denormalized to the point where all the Lookup data were stored directly in the main data tables so that it was easy to query using any tools.
NAMRS Pilot Architecture
The NAMRS Pilot system was hosted in the Microsoft Azure Cloud environment. This section discusses the advantages of the cloud environment, which are pertinent to both the NAMRS Pilot and the future NAMRS, the high-level system architecture within the cloud, components used by the NAMRS Pilot, and security features available through the cloud environment.
There are several levels of hosting environments, which provide different advantages. Figure 5.8 illustrates these levels.
|FIGURE 5.8. Comparison of Hosting Models|
On-Premises Hosting--This is not cloud hosting, but rather consists of purchasing physical hardware and hosting it in a data center.
Infrastructure as a Service (IaaS)--In this model, the cloud provides a virtual hardware environment that includes virtual network services and virtual server hardware. This eliminates the sunk cost and management of physical hardware.
- The NAMRS Pilot system was a hybrid of Platform as a Service (PaaS) and IaaS. (In Chapter 7, we recommend moving to a fully PaaS model.) PaaS and IaaS models provide several advantages over an On-Premises model.
- The NAMRS Pilot system was a hybrid of Platform as a Service (PaaS) and IaaS. (In Chapter 7, we recommend moving to a fully PaaS model.) PaaS and IaaS models provide several advantages over an On-Premises model.
PaaS--In this model, the cloud provides services that can be used by applications. This eliminates all the management of any hardware (physical or virtual), operating systems, and runtime software.
Software as a Service (SaaS)--In this model, everything is provided and the user simply configures their applications. Examples of these systems are Salesforce, Constant Contact, and NetSuite.
Scalability and Flexibility--In a cloud environment, it is easy to scale a system up from supporting ten to thousands to even millions by easily adding virtual hardware or services. Scaling can be done manually or automatically. For example, in the case of the NAMRS Pilot, it gives the option to scale the system up during parts of the year that state users will be submitting yearly data, and scaling it back down during less busy parts of the year to minimize unneccessary expenses. The system can be configured to scale up automatically as the system becomes busy and scaled back down as traffic decreases.
Cost Benefits--In a cloud environment, billing includes only the services being used. There is no need to buy and maintain expensive hardware, and keep that hardware running while it is idle. In the example above, as the system scales down, costs go down; there is no "sunk cost" for underutilized servers.
Significantly Lower Maintenance Costs--The cloud gives the option of using services rather than hardware (or virtual hardware) in a PaaS model. This means that there is no requirement to apply operating system service packs or monitor hardware for physical errors. This happens in the fabric of the cloud.
Decreased Downtime--Hardware upgrade and maintenance is mandatory, even the physical hardware that powers the cloud. However, in the cloud, when physical hardware is in maintenance mode, virtual services are relocated, resulting in no downtime.
Security and Compliance--Cloud services are extremely secure. The Microsoft Azure environment is compliant with most industry compliance standards. (See the section on Security for more information.)
NAMRS Pilot System and Azure Cloud Services
As mentioned above, the NAMRS Pilot system was hosted in the Microsoft Azure Cloud. It was able to take advantage of different services provided by the cloud environment.
Azure Web Apps (PaaS)--This is a managed, secure, scalable, highly-available, load-balanced, and geographically load-balanced web application hosting service. It removes the need to implement a web server or virtual machine (VM). This service was used for hosting the NAMRS Pilot Website.
Azure Redis Cache (PaaS)--This is a managed implementation of the Redis Cache service. This provides secure, scalable, caching inside the Azure Cloud.
Azure Virtual Machine (IaaS)--This is a scalable, highly-available, secure, virtual server located inside the Azure Cloud. The NAMRS Pilot Database and the NAMRS Pilot Data Warehouse used these virtual servers for hosting.
Azure Blob Storage (PaaS)--This is a scalable, highly-available, geographically redundant data storage service for files inside the Azure Cloud. This service was used for storing various files, such as the state's Case Component XML files.
SendGrid Email (PaaS)--This is an email service that is provided through the Azure Marketplace. This service was used for sending emails to various users.
There are many other services available inside the Microsoft Azure Cloud, but the NAMRS Pilot used the ones above.
The following diagram, Figure 5.9, illustrates the different services used and the relationships between them.
|FIGURE 5.9. Azure Services used by NAMRS Pilot|
As seen in Figure 5.9, the NAMRS Pilot system had several different components. The architecture of each is described in detail below.
NAMRS Website Architecture
The NAMRS Pilot Website was the web interface. This website allowed state users, federal users, technical users, and administrators to log in, view/edit data, upload XML files, and download reports. Previous sections in this document described the functionality of the website.
As further discussed below, the Web App accesses several other components in the NAMRS environment.
There was some user-based session data used in the NAMRS Pilot Website. An example of these data is that when state users logged in, their assigned state was stored into session data for convenience. Session data are generally used for performance and convenience--the NAMRS Pilot website used very little session data. Session data were stored in the Azure Redis Cache service and was very fast and secure. All data were encrypted as the data moved across the network between the Web App and the Azure Redis Cache service, and all data inside Azure Redis were encrypted.
NAMRS Pilot Database Architecture
All data for the NAMRS Pilot Website were stored in the NAMRS Pilot Database. These included the state's Agency, Key Indicators, and Case Components, announcements and resources that were displayed on the website, and login data. This database used Microsoft SQL Server running on an Azure Virtual Machine.
NAMRS Pilot Data Warehouse
The NAMRS Pilot Data Warehouse provided a way for analysts to query the Case Component data using statistical analysis applications, data visualization applications, query applications, and any other type of application that is capable of querying a database. The data warehouse used Microsoft SQL Server running on an Azure Virtual Machine.
NAMRS Pilot Storage Architecture
The website needed to store and access various files, such as state's Case Component XML files, different PDF files that were pre-generated, and resource files. All these files were stored using an Azure Blob Storage Container.
NAMRS Pilot Email Architecture
The NAMRS Pilot Website sent emails to state users and technical users as the status of state data was updated, and for the forgot password function. For example, when state users submitted their Agency Component, a technical user received an email to inform them that they needed to inspect this data. Then when the technical user approved or rejected the data, the state users received an email to let them know the updated status. Emails were sent through the SendGrid service in the Azure environment. The service allows sending up to 25,000 emails a month in the Azure Cloud at no cost. Additional plans are available for sending more emails for minimal cost.
NAMRS Pilot Case Loader Architecture
The NAMRS Pilot Case Component Loader was an application that ran all the validation on the Case Component XML files, loaded the Case Component data into the database, and did some other processing on the Case Component data. The future Case Component XML files may be a bit large (although not observed in the NAMRS Pilot), so the Case Component Loader will accept the Case XML files as XML files or as zipped XML files.
The uploaded XML files were validated against the NAMRS Case Component XSD file. It was expected that state users validated the file against the XSD before they uploaded it to the NAMRS Pilot Website. Validation was also performed in the NAMRS Pilot Case Component Loader so that if a state user uploaded an invalid XML file, the Loader would identify it as invalid.
The Case Loader performed the functions on all files waiting to be processed:
Downloaded the file from NAMRS Pilot Storage to the local Temp space.
Unzipped the file (if the file was zipped).
Validated the file against the XSD.
If the file passed the XSD validation, then ran the data validation rules against the file (and performed actions on rule violations) and built a list of warning messages for the user. After validation, a new version of the file without any invalid tags/data was saved.
Loaded the data from the valid case XML file produced by the previous step into the database.
Calculated basic counts, computed Key Indicators, and other summary data that was displayed on the website.
Built the PDF files for the summary counts and frequency counts.
The Case XML Loader was built as a console application (an executable). The application was written in C# and used Entity Framework and LINQ for all database access. The site used several open-source components and frameworks, including DotNetZip, iTextSharp, log4net, and RazorEngine.
This application ran on an Azure Virtual Machine. It was executed as a scheduled task on the VM, running once every minute. The NAMRS Pilot Case Loader accessed many of the same services as the NAMRS Pilot Website.
The Case Loader loaded data from the XML file into the database. It then performed many queries against the database to summarize and calculate counts and other information that were stored back into the database. The Case Loader read Case XML files from the Azure Blob Storage. It also created PDF files and other files that were stored back into the storage containers. The Case Loader sent emails to state users and technical users as the status of state data updates.
NAMRS Pilot Security
Each of the components had different levels of security, which are described briefly below.
The Azure environment itself has multiple levels of security:
Physical Security--Azure data centers have excellent physical security.
Network Security--Data moving across the cloud network is separated for security. Every service and VM has virtual firewall capability available (and VM) to control data moving in and out of the cloud network. Data inside the cloud is all encrypted as it moves across the internal cloud networks.
Host Security--Physical hardware runs a specialized version of Windows Server and HyperV.
Application Security--Azure Web applications run in managed VM's (internally) that are all isolated from each other. As a result, there is no possibility of data leaking between different web applications.
Data Security--Strong storage keys are required for access control. SSL support for data transfers.
Multifactor Authentication--Multifactor authentication controls logging into the Azure Console to do administration and deployments, among other things.
There is additional information available about Azure Security in the Microsoft Azure Trust Center at http://azure.microsoft.com/en-us/support/trust-center/security/.
Microsoft Azure is also compliant with most American and European industry certifications such as CJIS, EU Model Clauses, FDA 21 CFR Part 11, FedRAMP, HIPAA/HITECH, SOC1/SOC2, and PCI DSS Level 1. More information about Azure Standards Compliance is available in the Microsoft Trust Center at http://azure.microsoft.com/en-us/support/trust-center/compliance/.
NAMRS Pilot Website
The NAMRS Pilot Website employed the following security measures:
Login--All pages in the site, including the home page, required users to login. (The NAMRS XSD file was available without login, but it was just a public file, not a web page.)
Encryption--All pages in the site had SSL encryption. (The lock icon was displayed in the user's browser.) If a user attempted to access the site without SSL (HTTP), the site would immediately switch to use SSL encryption (HTTPS) so that no information was ever transported across the web without encryption. (The NAMRS XSD file is available using HTTP because some XML Editors/Validators cannot handle HTTPS or logins. This is an industry standard for XSD files.)
Hashed Passwords--The site did not store user passwords in the database. Instead, only password hashes were stored. The password hash algorithm used in Microsoft Identity 2 is SHA1. More specifically, it runs 1,000 iterations of a salted SHA1 (where the salt becomes part of the actual hash). This is industry standard, and highly secure.
Firewall--The Azure Web App service is firewalled from the Internet, and Azure handles all physical and network security. Only ports 80 (HTTP) and 443 (HTTPS) are open to the Internet.
PII--The NAMRS Pilot system did not store any PII, so there was no way to link any particular piece of data to any individual person.
Encrypted IDs--States were required to encrypt all IDs in the Case Component using a secure encryption algorithm of their choice. Additionally, the data must have been hashed after encryption. This included the investigation, facility, client, and perpetrator IDs. This could have been done either by the database query that pulled the data from the state's database or by the application that produced the XML file. Most databases (like Microsoft SQL Server, Oracle, and MySQL) have easy-to-use functions for the popular encryption and hashing algorithms. The MD5 hash method was recommended. No recommendation was made for the encryption method, but at least one state used 3DES.
NAMRS Case Loader, Database, and Data Warehouse
The NAMRS Case Loader ran on the same VM as the NAMRS Database. Both the NAMRS Database (with Case Loader) and the NAMRS Data Warehouse employed the following security measures:
Endpoint Security--A (virtual) firewall protected these VMs. This firewall blocked all ports except the:
- Powershell (Port 5986)--This allowed Powershell scripting against the VM.
- Remote Desktop (Port 3389)--This allowed a system administrator to get remote access to the server. Note that this port was mapped to a different public-facing port on each server.
- SQL Server (Port 1433)--This allowed the SQL to be queried and managed.
ACL Security--Each of the Endpoints above could be individually limited to an IP address (or range) using an Access Control List (ACL). The ACL on port 1433 was set so that it was only accessible from inside the Azure Cloud so that the NAMRS Website could get to the database. Additionally, all three ports were accessible from the WRMA office. None of these ports (or any other ports) could be accessed from any other IP address on the Internet, so there was no way for anyone on the Internet to attempt to break into the servers.
Database Login Security--Each user in the data warehouse had unique SQL login credentials.
NAMRS Pilot Database and Data Warehouse Virtual Machines
Since the database and data warehouse ran on VMs, normal maintenance was performed at the operating system level and database server level. In addition to manual service pack updates, there were two scheduled maintenance plans on each SQL Server.
Nightly--The nightly maintenance plan ran at 1:00 a.m. every morning and backed up all databases on the server (to Azure Storage), checked database integrity, reorganized indexes, and updated statistics.
Weekly--The weekly maintenance plan ran at 3:00 a.m. every Saturday and rebuilt all indexes on all databases on the server.