cancel
Showing results for 
Search instead for 
Did you mean: 
CaylorNorris
Archer Employee
Archer Employee

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. 

 

PropertiesData TypeDescription
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. 

PropertiesDescription
TargetIdThe Level ID that your connector targets. It should be the same TargetId that was used when adding a connection. 
SourceFieldIdThis is the table name from your external database. 
ArcherFieldIdThe 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

23 Comments