Thank you to everyone that attended part two of the three-part Data Gateway Free Friday Tech Huddle series! Below is the guide for the RSA Archer Flexible SQL Connector. The VERY FIRST out-of-box Data Gateway Connector! The guide MUST be used in conjunction with our existing data gateway documentation. You can find that here and here!
SHAMELESS PLUG #1000:
Don’t forget to join the finale of the three-part Data Gateway series on January 24th! For the first time ever, we will code a Data Gateway Connector LIVE during the Free Friday Tech Huddle!
Watch part one and part two before Friday!
What is it?
The very FIRST out-of-box Data Gateway Connector! Data Gateway enables RSA Archer to connect to and interact with external data sets. Connectors enable you to create connections between external data sets and RSA Archer applications and questionnaires. The Flexible SQL Connector provides the ability to have real-time visibility into your external Microsoft SQL Server databases.
What should I know before I start?
- Make sure your external database is Microsoft SQL Server.
- Identify the tables or views from your external database that you need to use in RSA Archer.
- Note: Each table or view requires a separate connection.
- Verify that all fields needed are supported. The Flexible SQL Connector supports the following field types: Date, Text, Numeric, and IP.
- Note: Custom Data Gateway Connectors can support additional field types if your Connector code and external system provides the capability.
- Ensure the account has Data Gateway Access.
- Ensure that your network allows access from RSA Archer to the external database.
- Identify the column in the external database that is your unique identifier.
- Make sure your RSA Archer application has enough fields to the contain the external data. Use these RSA Archer fields to map to external fields.
What are the inputs for adding a new connection for the Flexible SQL Connector?
Instructions for running the needed REST API configurations can be found here. The following inputs are required for running the "Add Connection" REST API for the Flexible SQL Connector.
| Properties | Data Type | Description |
|---|
| dbProviderType | Character String Limit of 256 characters | Indicates the type of external resource. Use the following for this input: System.Data.SqlClient |
| connectionString | Character String Limit of 256 characters | Represents the string that is required to connect to external database. Use a standard MS SQL Server connection string. For example: "Data Source=SERVERNAME;Initial Catalog=DATABASE;User ID=USERNAME;Password=PASSWORD;TransparentNetworkIPResolution=FALSE" Note: There no default maximum or minimum number of connections. You can set this by adding a Min Pool Size or MAX Pool Size parameter to the connectionString. Note: Integrated Authentication is available. Use Integrated Security=true; instead of User ID and Password in your connectionString. |
| tableName | Character String Limit of 256 characters | Indicates the table or view inside the external resource. |
| idColumnName | Character String Limit of 256 characters | Corresponds to the column name. Uniquely identifies a record in the external system. |
| idColumnType | Character String Limit of 256 characters | Indicates the record identifier datatype. Supported datatypes are Integer and String |
Example Request JSON body for Add Connection REST API:
{
"TargetId": LEVELID,
"Connection": {
"LocationAlias": "EXT_SYS",
"DatagatewayConnector": {
"ConnectorType": "ArcherTech.Datasource.Content.GenericSql.GenericSqlDataSource",
"Properties": [
{
"Name": "dbProviderType",
"Value": "System.Data.SqlClient"
},
{
"Name": "connectionString",
"Value": "Data Source=SERVER;Initial Catalog=DATABASE;User ID=USERNAME;Password=PASSWORD;TransparentNetworkIPResolution=False"
},
{
"Name": "tableName",
"Value": "TABLENAME"
},
{
"Name": "idColumnName",
"Value": "UNIQUEID"
},
{
"Name": "idColumnType",
"Value": "DATATYPE"
}
]
}
}
}
How do I map fields?
Once again, instructions for running the needed REST API configurations can be found here.
Here are the descriptions of the needed inputs that are specific to the Flexible SQL Connector.
| Properties | Description |
|---|
| TargetId | The Level ID that your connector targets. It should be the same TargetId that was used when adding a connection. |
| SourceFieldId | This is the table name from your external database. |
| ArcherFieldId | The field ID of the field that you'd like the external data to map to. |
Example Request JSON body for the map fields REST API:
{
"TargetId": LEVELID,
"Connections": [{
"Name": "LOCATION ALIAS",
"FieldMapping": [{
"SourceFieldId": "EXTERNAL COLUMN NAME",
"ArcherFieldId": ARCHER FIELD ID
}]
}
Now that you've added all of your Flexible SQL Connector configurations, do an IIS reset. Run a search and experience the future with Data Gateway! It is worth noting that searches against completely external data require a search filter.
Special thanks to Kimberly Bowen for letting me copy her homework and put this out in blog format. Her determination to document Data Gateway is fierce and admirable.
SHAMELESS PLUG #1001:
Don’t forget to join the finale of the three-part Data Gateway series on January 24th! For the first time ever, we will code a Data Gateway Connector LIVE during the Free Friday Tech Huddle!
Watch part one and part two before Friday!
#DATAGATEWAYISTHEFUTURE
