lccwebtosql white paper · 2018. 5. 24. · page 2 of 22: lccwebtosql-whitepaper.docx lower...
TRANSCRIPT
Page 1 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
lccWebToSQL White Paper
Contents Description ................................................................................................................................................................................ 2
Security Levels .......................................................................................................................................................................... 2
Reader ................................................................................................................................................................................... 2
Editor ..................................................................................................................................................................................... 2
Deletor ................................................................................................................................................................................... 2
Moderator .............................................................................................................................................................................. 2
Installation ................................................................................................................................................................................. 2
Setting up program for first time use, ability to delegate Settings access ............................................................................... 3
Logic File Settings ..................................................................................................................................................................... 6
All Logic File Keys, including those that are optional.............................................................................................................. 6
Specifying Encrypted Key Values ............................................................................................................................................ 18
Encrypting Key Values ............................................................................................................................................................ 18
Example Logic File ............................................................................................................................................................... 19
Syntax tor Launch lccWebToSQLCommandLine ................................................................................................................... 19
Logic File Example .................................................................................................................................................................. 20
Definitions ............................................................................................................................................................................... 21
Modifications ........................................................................................................................................................................... 22
Page 2 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
Description This document describes the lccWebToSQL program.
lccWebToSQL was developed to supply these capabilities:
• present a single Sign-On Web interface to multiple SQL sources
• control access to SQL sources for users through AD groups
• control Read, Edit, Create, Delete per connection
• auto sort ability
• auto filter ability
Note: when providing database, schema, column names, we recommend qualifying them within brackets '[]'. Though we could
have the program auto bracket names, that would remove the flexibility of add other layers, like Linked Server Connections.
You should also qualify any names used by the system, like if your column name is 'name', use '[name]'.
Security Levels
Reader
Can View Records.
Editor
Can View and Edit Records.
Deletor
Can View, Delete Records.
Moderator
Can View, Edit, Delete and Create Records.
Installation
During installation or use, enabling the Debug IP/Debug Mode is beneficial in receiving additional information.
Page 3 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
On steps that include IIS steps, IIS 7 was used.
Setting up program for first time use, ability to delegate Settings access
The 'Settings' allow persons to configure what values will be used to validate authentication, filter reports, etc..
• create a folder [aka Program Folder] on a web server to put the program
• [Program Folder] create the following sub-folders
o bin
o pics
• [Program Folder] copy the lccWebToSQL.dll into the Program\bin Folder
• [Program Folder] copy the following files into the Program\pics Folder
o lccWTSPic1.jpg
o lccWTSPic2.jpg
o lccWTSPic3.gif
o lccWTSPic4.gif
• [Program Folder] copy the following files into the Program Folder
o Default.aspx
o lccWebToSQL.css
o Web.config
• (on an IIS server) create an Application Pool
o set .NET Framework version to: No Managed Code (this is not a .NET app)
• (on an IIS server) create an Application that points to the program folder and used the Application Pool above
• (on an IIS server) Click on Server name
• (on an IIS server) Click on Application
• (Application) Double-Click on Default Document
• (Default Document) Remove all default document names, and add 'Default.aspx'
• (on an IIS server) If not using the default NETWORK SERVICE on the Application Pool, give the user rights using the
command:
aspnet_regiss.exe -ga "domain\user"
• (Web Browser) View the website in a browser. You should get a response like the example below. This verifies the
program is able to run.
[lccLoadLogic] ERROR: Could not find a part of the path '[path]:\[file] '
• Choose a location (suggest a different folder) to store a Logic File
• Create the Logic File, using any editor, with any name. Example: lccWebToSQL-logic.txt
o Note: the file can be empty at this time.
Page 4 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
• (Web.Config) Modify the 'appSetting': lcc:logicFile
o Change the Value to the Path/File where the Logic File is,
Example: E:\lccOurSecureData\lccWebToSQL-logic.txt
• (Web Browser) View the website in a browser. You should get a response like the example below. This verifies the
program is able to see the empty Logic File.
User Id ____________ Password ___________ [Submit Form]
• (Logic File) Add the following lines to the Logic File. This will allow Debug information to show. See the Logic File
Settings sections for details on Logic File fields, Debug Levels.
Note: after verifying these debug levels can be read correctly, it is suggested you turn them all off until times
where you need to debug an issue. The quickest way to 'invalidate' a Debug Level, without removing the line, is
to change field name to something unrecognized:
Example: lcc:xdebugLevel 1
lcc:debugIP IP
lcc:debugLevel #
• (Web Browser) View the website in a browser, from the Debug IP. You should get a response like the example below.
Debug Mode Enabled
--------------------------------------------------------------------------------
[lccLoadLogic] Record [lcc:debugIP 123.123.123.123]
[lccLoadLogic] Record Is Not Key
[lccLoadLogic] Record [lcc:debugLevel 1]
[lccLoadLogic] Record Is Not Key
...
• (Logic File) Add the following lines to the Logic File. This will allow Encryption on session keys and access to AD for
looking up user accounts. The Security Key is any phrase you make up and is only used for encrypting/decrypting
session keys. The LDAP User is any account that can access AD (i.e. basic user).
lcc:securityKey our fancy key
lcc:ldapUserId LDAP USER ID
lcc:ldapUserPassword LDAP USER PASSWORD
• (Logic File) Add the following lines to the Logic File. This will allow a Page Header and LDAP server(s). Two LDAP
Server lines are shown, but, you can have one to many.
lcc:pageTitle LCC: lccWebToSQLDemo
lcc:pageHeadStart
<link rel="stylesheet" href="./demo/lccWebToSQL.css" type="text/css">
lcc:pageHeadEnd
Page 5 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
lcc:LDAPServer dc1.edu/dc=ctc,dc=edu
lcc:LDAPServer dc2.edu/dc=ctc,dc=edu
• (Web Browser) You should now be able to Log-In with any validate user account. You should get the response like the
example below. Log-in sessions are valid for the current date only and are removed when the browser is closed.
Welcome DOE, JOHN [Home]
• (Logic File) Add the following lines for each Group Access you wish to define. This will set a Group Access Id, AD Group
Security, and which server has the SQL Source.
lcc:groupAccessId ourConnection1 Persons
lcc:groupAccessSecurity ourConnection1 Moderator CN=ourGroup,DC=ctc,DC=edu
lcc:groupAccessServer ourConnection1 sqlserver.ctc.edu
• (Logic File) Add the following lines for the Database for each Group Access you defined. This will set the SQL Database
and User (or Integrated if User option not used).
lcc:groupAccessDatabase ourConnection1 OurDB1
lcc:groupAccessDatabaseSecurityType ourConnection1 User
lcc:groupAccessDatabaseSecurityUserId ourConnection1 ourUser
lcc:groupAccessDatabaseSecurityUserPassword ourConnection1 ourPassword
• (Logic File) Add the following lines for each Table in the Database you want exposed.
lcc:groupAccessTableId ourConnection1 ourTable1 Our Table 1
lcc:groupAccessTableColKey ourConnection1 ourTable1 colKey Col Key YES
• (Web Browser) You should now be able to Log-In and see any Database/Tables defined. You should get the response
like the example below.
[Database: Our Database]
[Table: Our Table 1]
• (Logic File) Add the following lines for each Column in each Table in the Database you want exposed. The 'Key' column
does not need to be specifed. This shows two columns being exposed.
lcc:groupAccessTableCol ourConnection1 ourTable1 FirstName First Name
lcc:groupAccessTableCol ourConnection1 ourTable1 LastName Last Name
• (Logic File) Add the following lines for each Column in each Table in the Database you want to set how it is handled. This
shows two options on one column.
lcc:groupAccessTableColSetting ourConnection1 ourTable1 maxLength 40
lcc:groupAccessTableColSetting ourConnection1 ourTable1 numbersOnly YES
• (Web Browser) If your tables already have data, you should see those rows.
Page 6 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
Logic File Settings
All Logic File Keys, including those that are optional
All Logic File Keys have the following format:
[Key Name] [tab] [Value]
Example: lcc:key Some Value
Some keys allow for multiple settings, with each setting separated by a [Tab].
Any lines not matching a valid key are considered 'remarks' and will be ignored.
The following Keys values can be encrypted (see Specifying Encryped Key Values):
• lcc:LDAPUserId
• lcc:LDAPUserPassword
• lcc:groupAccessDatabaseSecurityUserId
• lcc:groupAccessDatabaseSecurityUserPassword
if you supply the Keys:
• lcc:globalEncryptionHash
• lcc:sourceEncryptedSaltKey
• lcc:sourceEncryptedVIKey
lcc:debugMode (optional)
Specifies whether the program will run in Debug Mode (i.e. show more information). This only affects views from
computers using a Debug IP. The only valid value is 'YES', any other value will be ignored.
Example: lcc:debugMode YES
lcc:demoMode (optional)
Specifies whether the program will run in Demo Mode. When running in Demo Mode, no changes will be saved. The
only valid value is 'YES', any other value will be ignored.
Example: lcc:demoMode YES
lcc:debugLevel (optional)
Specifies what debug information will be provided to the Debug IP. Provide a new line for each level desired.
Example: lcc:debugLevel 1
Example #2 (two levels defined):
Page 7 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
lcc:debugLevel 1
lcc:debugLevel 3
Debug Levels
1 - show SQL commands
2 - form key/value pairs
3 - User verified information
4 - user groups check
5 - connection settings
6 - deleting record
7 - connection string
8 - action type
9 - column setting
10 - log-in key processing
11 - Base16 Conversions
12 - Load Web.Config
13 - Load Logic, general
14 - Load Logic, Page Head/Title
15 - Load Logic, LDAP
16 - Load Logic, Group Access Connection Settings
17 - Load Logic, Group Access Table
18 - Load Logic, Group Access Table Column
19 - Add Group Access
20 - LDAP, general
21 - LDAP, Keys/Values bool lccBReturnVal = false;
22 - Table Access Security Flag
23 - chopOnLeft, chopOnRight, translateCompare
24 - show the password when displaying connection settings (turn on debug level 5)
25 - show email subject RAW and final
lcc:debugIP (optional)
Specifices IPs for use as Debug Machines. If more than one IP required, use multiple lines.
Example: lcc:debugIP 123.123.123.123
Example #2 (multiple IPs): lcc:debugIP 123.123.123.123
lcc:debugIP 231.231.231.231
lcc:logPath (optional)
Page 8 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
The path/root name of the Log File. The program will place the '.log' extension automatically and will also append a
Year/Month date.
Example: lcc:logPath e:\ourLogs\lccWebToSQLLog
lcc:globalEncryptionHash (optional)(one per Logic File)
Hash value supplied for encryption. Must be used with keys ‘lcc:sourceEncryptionSaltKey’ and ‘lcc:sourceEncryptionVIKey’.
Only used if at least one of the keys ‘lcc:encryptArchivedRequests’ or ‘lcc:encryptArchivedLists’ are used.
Any set of characters can be used.
Syntax: lcc: globalEncryptionHash [tab] […]
Example: lcc: globalEncryptionHash s0m3C00lPhr@$e
lcc:globalEncryptionSaltKey (optional)(one per Logic File)
Salt Key value supplied for encryption. Must be used with keys ‘lcc:sourceEncryptionPasswordHash’ and
‘lcc:sourceEncryptionVIKey’. Only used if at least one of the keys ‘lcc:encryptArchivedRequests’ or ‘lcc:encryptArchivedLists’
are used.
Any set of characters can be used.
Syntax: lcc: globalEncryptionSaltKey [tab] […]
Example: lcc: globalEncryptionSaltKey s0m3C00lPhr@$e
lcc:globalEncryptionVIKey (optional)(one per Logic File)
VI Key value supplied for encryption. Must be used with keys ‘lcc:sourceEncryptionPasswordHash’ and
‘lcc:sourceEncryptionSaltKey’. Only used if at least one of the keys ‘lcc:encryptArchivedRequests’ or ‘lcc:encryptArchivedLists’
are used.
Any set of characters can be used, but, must be exactly 16 characters long.
Syntax: lcc:globalEncryptionVIKey [tab] […]
Example: lcc:globalEncryptionVIKey s0m3C00lPhr@$e12
lcc:LDAPServer (mandatory)
Specifices LDAP servers for use on accounts marked as Type 'LDAP'. If more than one LDAP server is available, use
multiple lines.
Page 9 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
Example: lcc:ldapServer ourServer.edu
Example #2 (multiple IPs): lcc:ldapServer ourServer.edu
lcc:ldapServer ourSecondServer.edu
lcc:LDAPUserId (mandatory)
Specifices LDAP User Id for the connection.
Example: lcc:ldapUserId ourUser
lcc:LDAPUserPassword (mandatory)
Specifices LDAP User Password for the connection.
Example: lcc:ldapUserPassword ourPassword
lcc:SMTPServer (optional, none to many)
Provides an email server to send Email Notifications through. If more than one supplied, will try each until one works.
Example: lcc:smtpServer [email protected]
Example #2 (multiple):
lcc:SMTPsmtpServer [email protected]
lcc:SMTPServer [email protected]
lcc:SMTPEmailFrom (optional, one per Logic File)
When sending Email Notifications, what will the From email address be.
Example: lcc:SMTPEmailFrom [email protected]
lcc:SMTPEmailFromName (optional, one per Logic File)
When sending Email Notifications, what will the From Name be.
Example: lcc:SMTPEmailFromName lccWebToSQL Admin
lcc:SMTPEmailLinkToSite (optional, one per Logic File)
When sending Email Notifications, will place a link at the top of emails to link to the program's site (or other).
Example: lcc: SMTPEmailLinkToSite https://site.edu/lccWebToSQL
lcc:securityKey (mandatory)
A key used in encryption/decryption. If this is changed, any previous encrypted values will be unrecognized.
Currently, only the Credentials file uses this.
Example: lcc:securityKey demo key
lcc:pageTitle (optional)
Page 10 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
What Title will show on the page..
Example: lcc:pageTitle Our Title
lcc:pageHeadStart (optional) (no values on this key)
Any lines following this key will be assumed to be shown as-is to the browser until the lcc:pageHeadEnd is provided.
These lines will be placed right before the </HEAD> HTML command.
Example: lcc:pageHeaderStart
lcc:pageHeadEnd (optional) (no values on this key)
Stops reading lines into the page header.
Example: lcc:pageHeaderEnd
lcc:pageTopStart (optional) (no values on this key)
Any lines following this key will be assumed to be shown as-is to the browser until the lcc:pageTopEnd is provided.
These lines will be placed right before the <FORM> HTML command.
Example: lcc:pageTopStart
lcc:pageTopEnd (optional) (no values on this key)
Stops reading lines into the page top.
Example: lcc:pageTopEnd
lcc:pageBottomStart (optional) (no values on this key)
Any lines following this key will be assumed to be shown as-is to the browser until the lcc:pageBottomEnd is provided.
These lines will be placed right after the </FORM> HTML command.
Example: lcc:pageBottomStart
lcc:pageBottomEnd (optional) (no values on this key)
Stops reading lines into the page bottom.
Example: lcc:pageBottomEnd
lcc:groupAccessId (mandatory) (at least one per Logic File)
Id for a Connection.
Syntax: [Key Name] [tab] [Connection Id] [tab] [Display Name]
Example: lcc:groupAccessId ourConnection1 Our Connection 1
lcc:groupAccessSecurity (mandatory) (at least one per lcc:groupAccessId)
Page 11 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
AD security groups that can access this connection and at what level of permissions.
Syntax: [Key Name] [tab] [Connection Id] [tab] [Display Name]
Example: lcc:groupAccessSecurity ourConnection1 Editor CN=ourGroup1,DC=ctc,DC=edu
Example #2 (multiple groups):
lcc:groupAccessSecurity ourConnection1 Editor CN=ourGroup1,DC=ctc,DC=edu
lcc:groupAccessSecurity ourConnection1 Editor CN=ourGroup2,DC=ctc,DC=edu
lcc:groupAccessServer (mandatory) (one per lcc:groupAccessId)
LDAP server name.
Syntax: [Key Name] [tab] [Server Name]
Example: lcc:groupAccessServer ourConnection1 ourServer.edu
lcc:groupAccessDatabase (mandatory) (one per lcc:groupAccessId)
Database name to open.
Syntax: [Key Name] [tab] [Connection Id] [tab] [Database Name]
Example: lcc:groupAccessDatabase ourConnection1 ourDB1
lcc:groupAccessDatabaseSecurityType (mandatory) (one per lcc:groupAccessId)
How to connection. The only valid options are 'User' and 'Integrated'.
Syntax: [Key Name] [tab] [Connection Id] [tab] [Database Name]
Example: lcc:groupAccessDatabaseSecurityType ourConnection1 User
lcc:groupAccessDatabaseSecurityUserId (optional) (use if 'User' Security Type used)
User Id for DB connection.
Syntax: [Key Name] [tab] [Connection Id] [tab] [User Id]
Example: lcc:groupAccessDatabaseSecurityUserId ourConnection1 ourUser
lcc:groupAccessDatabaseSecurityUserPassword (optional) (use if 'User' Security Type used)
User Password for DB connection.
Syntax: [Key Name] [tab] [Connection Id] [tab] [User Password]
Example: lcc:groupAccessDatabaseSecurityUserPassword ourConnection1 ourPassword
lcc:groupAccessTableId (mandatory) (at least one per lcc:groupAccessId)
A Table to Id and expose.
Syntax: [Key Name] [tab] [Connection Id] [tab] [Id] [tab] [Table Name]
Example: lcc:groupAccessTableId ourConnection1 ourTable1 Our Table1
Page 12 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
lcc:groupAccessTableLogicFile (optional)(one per Table)
You can provide additional Logic per Table. This is beneficial if you want to have different information, like lcc:pageTop,
etc. when accessing a specific table. This logic will be appended to the main logic, i.e. you can have a lcc:pageTop in
the main logic and a lcc:pageTop in a specific Table logic and both will be displayed.
Syntax: [Key Name] [tab] [Connection Id] [tab] [Table Id] [tab] [Logic File]
Example: lcc:groupAccessTableLogicFile ourConnection1 ourTable1 F:\folder\ourTable-logic.txt
lcc:groupAccessTableSetting (optional) (none to many per lcc:groupAccessTable)
Setting to control table exposure.
Syntax: [Key Name] [tab] [Connection Id] [tab] [Table Id] [tab] [Setting Key] [tab] [Setting Value]
Example: lcc:groupAccessTableSetting ourConnection1 ourTable1 setting value
Available Table Settings
filteredOnly [YES]
If provided and the value is 'YES', will only shows records when a 'filter' value has been supplied. This is beneficial when
displaying a table with a large quantity of records.
Example: lcc:groupAccessTableSetting ourConnection1 ourTable1 filteredOnly YES
maxRows [number]
Will only display the [number] of rows.
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 maxRows 100
forcedFilter SQL 'Where' Clause
Added a forced filter to the Where clause when querying a table. This helps if you want to expose a table, but, filter out
certain records before the user can choose/filter. None to many can be supplied.
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 forcedFilter Col2='0'
Example #2 (multiple):
lcc:groupAccessTableColSetting ourConnection1 ourTable1 forcedFilter Col2='0'
lcc:groupAccessTableColSetting ourConnection1 ourTable1 forcedFilter Col3='1'
staticQuery Static SQL Query
If provided, will overwrite the SQL query portion that deals with columns. For example, if the program was provided 3
columns and normally built out:
Page 13 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
SELECT RTRIM(col1), RTRIM(col2), RTRIM(col3) FROM ourTable WHERE col2 IN ('...','...') ORDER BY col3;
This key would replaced this portion (in green):
SELECT RTRIM(col1), RTRIM(col2), RTRIM(col3) FROM ourTable WHERE col2 IN ('...','...') ORDER BY col3;
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 staticQuery
RTRIM(col1),LEFT(RTRIM(col2),4),RTRIM(col3)
defaultOrderBy SQL Column(s)
Any of the columns provided with the key 'lcc:groupAccessTableCol' can be provided. If the user has not chosen a
column to sort by, these columns will be used. Support adding 'ASC' and 'DESC' after column name(s).
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 defaultSortOrderBy Co11,Col2
overrideSecurity [new Security Level]
Security to tables are defined in the key 'groupAccessSecurity'. This applies to all tables opened. However, you may
want to change the security per table. This key will allow you to change a table's security level. See the Security Levels
section for the correct value to use.
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 overrideSecurity Reader
recordNotification
Send email notification when a record has been affected. The following variances of this key can be used to control when
to send out an email:
• recordNotification: sent if a record created, modified
• recordNotificationCreated: sent if a record created
• recordNotificationModified: sent if a record modified
• recordNotificationDeleted: sent if a record deleted
The values supplied provides the recipient(s) to be emailed. If more than one, separate with semicolon(s).
Example: groupAccessTableColSetting ourConnection1 ourTable1 recordNotification
Example #2 (multiple recipients): groupAccessTableColSetting ourConnection1 ourTable1
recordNotification [email protected];[email protected]
recordNotification[...]Subject
Add a custom Email Subject when a Record Notification is sent.:
Page 14 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
• recordNotificationCreatedSubject: sent if a record created
• recordNotificationModifiedSubject: sent if a record modified
• recordNotificationDeletedSubject: sent if a record deleted
You can embed Column Flags into the subject to place values from the record that was created/modified. To use column
value, use the following syntax. You can embed one to many, including the same column as many times as desired.
You can also use these predefined flags:
• [lccPreDefinedColValueId:User]: the user peforming the action
• [lccPreDefinedColValueId:Action]: will place what 'action' is being performed
[lccColValueId:...]
Example: [lccColValueId:ourCol1]
Example: groupAccessTableColSetting ourConnection1 ourTable1 recordNotificationCreatedSubject
Our Custom Subject
recordNotificationSetting
Settings to control Record Notifications (see 'recordNotification' key).
The available settings are:
• filterOn: enforces a filter against a column/value. If supplied and the column/value do not match, email
notification is skipped for that email address. Using a wildcard '*' as the value, means [any] value is valid.
None to many can be supplied. If more than one 'filterOn' desired, supply one on each line.
This settings expects four parameters separated by the tildy '~' character.
Syntax: filterOn~[email]~[column id]~[value]
Example: groupAccessTableColSetting ourConnection1 ourTable1 recordNotificationSetting
[email protected]~ourCol1~ourValue1
Example #2 (multiple settings):
groupAccessTableColSetting ourConnection1 ourTable1 recordNotificationSetting
[email protected]~ourCol1~ourValue1
groupAccessTableColSetting ourConnection1 ourTable1 recordNotificationSetting
[email protected]~ourCol1~ourValue2
Page 15 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
lcc:groupAccessTableColKey (mandatory) (one per lcc:groupAccessTableId)
Note: at this time, this key only support single column keys. If you connect a Table with multiple column keys, this
product is not ready for your use.
The [Show To User] column valid values are:
• NO : will not display to the user
• YES : will display to the user, but not editable
• YESEdit : will display to the user, not editable, but can create new records with user specified primary key value
The YES option is used if you want the end user to know the primary key value. NO and YES work good with a primary
key that has auto increment on it.
The YESEdit is used if you want the user to be able to create new records with user defined primary key values. This is
good for a primary key that is not auto incremented.
The column to use as the primary Key when querying and updating records.
Syntax: [Key Name] [tab] [Connection Id] [tab] [Table Id] [tab] [Column Name] [tab] [Column Display Name] [tab]
[Show To User?]
Example: lcc:groupAccessTableColKey ourConnection1 ourTable1 keyCol Rec # YES
lcc:groupAccessTableCol (mandatory) (at least one lcc:groupAccessTableId)
A column in the table you want exposed.
Syntax: [Key Name] [tab] [Connection Id] [tab] [Table Id] [tab] [Column Name] [tab] [Column Display Name]
Example: lcc:groupAccessTableCol ourConnection1 ourTable1 lastName Last Name
lcc:groupAccessTableColSetting (optional) (none to many per lcc:groupAccessTableCol)
Setting to control column exposure. The column name is not provided. These settings are assumed to be applied to the
most recent lcc:groupAccessTableCol provided, i.e. place all settings for a column after that column.
Syntax: [Key Name] [tab] [Connection Id] [tab] [Table Id] [tab] [Setting Key] [tab] [Setting Value]
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 setting value
Available Column Settings
If any of these settings affect the value supplied by the User, it will notify the User of the change.
Page 16 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
detailViewOnly [YES]
Only valid value is 'YES'. Will only show the column when the 'Detail' view has been chosen. Good to use on columns
with large data, like Notes.
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 detailViewOnly YES
maxLength [number]
Truncates the provided value is it exeeds the length of this number.
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 maxLength 15
skipEmailNotification [YES]
Only valid value is 'YES'. Does not send this columns information on email notifications. This is useful if you have
sensitive data, like SSN, and don't want that information emailed.
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 skipEmailNotificationYES
readOnly [YES]
Only valid value is 'YES'. Will not allow the User to edit the column.
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 readOnly YES
numbersOnly [YES]
Will remove any characters that are not numbers.
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 numbersOnly YES
valueFormat MM/DD/YYYY
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 valueFormat MM/DD/YYYY
Will only accept proper Date formatted values. If the value is not, it will place today's date.
width ###px
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 width 150px
Will create an in-line style for the value supplied, i.e. will place "width:[value]" on the column/cell.
selectValue [value]
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 selectValue Denied
Example #2 (multiple values):
lcc:groupAccessTableColSetting ourConnection1 ourTable1 selectValue Denied
lcc:groupAccessTableColSetting ourConnection1 ourTable1 selectValue Approved
Will force the User to choose from a Drop Down list. The list will contain all of the 'selectValue' lines.
Page 17 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
selectValueSelected [value]
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 selectValueSelected YES
Place this after a key 'selectValue' to force selecting that value in a drop down list. If the user has selected a value, their
selection trumps this key.
forcedValue [value]
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 forcedValue [type]
Will force a value for the record when editing. This is benefical if you want to record the date a record was changed,
and/or the user changing it. These are the supported types.
o lcc:yyyymmdd : will place the current date as yyyymmdd into the column.
o lcc:userId : will place the user's log-in id into the column.
o [ if a supported type is not supplied, the raw value supplied will be used ]
hidden [YES]
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 hidden YES
Will include the column in the query/sorting, but, will not show to the user.
translateValue [from]~[to]
Example: lcc:groupAccessTableColSetting ourConnection1 ourTable1 translateValue
1~Student Center
Example #2 (multiple):
lcc:groupAccessTableColSetting ourConnection1 ourTable1 translateValue 1~Student
Center
lcc:groupAccessTableColSetting ourConnection1 ourTable1 translateValue USA~United
States of America
None to many can be supplied. If a value matches a 'from' value (not case sensitive), it will auto translate to the 'to' value.
Note: searching/sorting still use the actual value stored in the database, this translation is only for viewer's benefit.
chopOnLeft [#]
Example: lcc:chopOnLeft 2
If the value of the column is at least the size of this number, it will be chopped down to the # of left characters. This
happens before 'chopOnRight' and 'translateValue'.
Page 18 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
chopOnRight [#]
Example: lcc:chopOnRight 2
If the value of the column is at least the size of this number, it will be chopped down the # of right characters. This
happens after 'chopOnLeft' and before 'translateValue'.
Specifying Encrypted Key Values
The following Keys values can be encrypted (see Encrypting Key Values):
• lcc:LDAPUserId
• lcc:LDAPUserPassword
• lcc:groupAccessDatabaseSecurityUserId
• lcc:groupAccessDatabaseSecurityUserPassword
If you supply the Keys:
• lcc:globalEncryptionHash
• lcc:sourceEncryptedSaltKey
• lcc:sourceEncryptedVIKey
To specify that the value is encrypted, precede the value with the text "[lcc:encrypted]".
For example, this would provide an encrypted value for a password key:
lcc:groupAccessDatabaseSecurityUserPassword ourDatabase [lcc:encrypted]...
Encrypting Key Values
To create an encrypted Key Value, use the accompanying lccWebToSQLCommandLine tool.
To use the lccWebToSQLCommandLine tool, a Logic File will need to be created. Though any filename will work, we suggest
the filename:
lcc:WebToSQLCommandLine-logic.txt
Page 19 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
This Logic File will follow the same format as the regular one (see Logic File Settings section), except that the only Keys used
from the regular Keys are:
• lcc:globalEncryptionHash
• lcc:globalEncryptionSaltKey
• lcc:globalEncryptionVIKey
These keys are defined in the regular Logic File Settings section.
The only other Keys are:
• lcc:encryptData
• lcc:decryptData
Though both are supported, we recommend using only one of these Keys per run.
lcc:encryptData (optional) (one per Logic File)
This key provides data you want encrypted. After encrypting, the encrypted results will be displayed to the window and
copied to your clipboard.
Syntax: [Key Name] [tab] [data]
Example: lcc:encryptData ...
lcc:decryptData (optional) (one per Logic File)
This key provides data you want decrypted. After decrypting, the decrypted results will be displayed to the window and
copied to your clipboard.
Syntax: [Key Name] [tab] [data]
Example: lcc:decryptData ...
Example Logic File
note: extra tabs used to be view friendly in this document
lcc:globalEncryptionHash 1234512345
lcc:globalEncryptionSaltKey asdfasdf
lcc:globalEncryptionVIKey 1a1a1a1a1a1a1a1a
lcc:encryptData some sensitive information
Syntax tor Launch lccWebToSQLCommandLine
lccWebToSQLCommandLine.exe lcc:logicPath .\lccWebToSQLCommandLine-logic.txt
Page 20 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
Logic File Example
lcc:debugIP 123.123.123.123
lcc:debugLevel 1
lcc:debugLevel 3
lcc:logPath E:\ourLogs\lccWebToSQL\lccWebToSQLLog
lcc:securityKey some cool key
lcc:ldapUserId ourLDAPUser
lcc:ldapUserPassword ourPassword
lcc:pageTitle LCC: lccWebToSQL
lcc:pageHeadStart
<link rel="stylesheet" href="/lccWebToSQL/lccWebToSQL.css" type="text/css">
lcc:pageHeadEnd
lcc:pageTopStart
<a href=http://college.edu>A Link To Our Homepage</a>
Some Cool Information
lcc:pageTopEnd
lcc:LDAPServer dc1.edu/dc=ctc,dc=edu
lcc:LDAPServer dc2.edu/dc=ctc,dc=edu
lcc:groupAccessId ourConnection1 Our Connection 1
lcc:groupAccessSecurity ourConnection1 Editor CN=ourEditors,DC=ctc,DC=edu
lcc:groupAccessSecurity ourConnection1 Moderator CN=ourModerators,DC=ctc,DC=edu
lcc:groupAccessServer ourConnection1 LDAPServer.ctc.edu
lcc:groupAccessDatabase ourConnection1 OurDB1
lcc:groupAccessDatabaseSecurityType ourConnection1 User
lcc:groupAccessDatabaseSecurityUserId ourConnection1 ourUser
Page 21 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
lcc:groupAccessDatabaseSecurityUserPassword ourConnection1 ourPassword
lcc:groupAccessTableId ourConnection1 ourTable1 Our Table 1
lcc:groupAccessTableColKey ourConnection1 ourTable1 keyCol Rec # YES
lcc:groupAccessTableCol ourConnection1 ourTable1 StuLastName Last Name
lcc:groupAccessTableColSetting ourConnection1 ourTable1 readOnly YES
lcc:groupAccessTableColSetting ourConnection1 ourTable1 maxLength 50
lcc:groupAccessTableCol ourConnection1 ourTable1 StuSID Student ID
lcc:groupAccessTableColSetting ourConnection1 ourTable1 readOnly YES
lcc:groupAccessTableColSetting ourConnection1 ourTable1 maxLength 9
lcc:groupAccessTableColSetting ourConnection1 ourTable1 numbersOnly YES
lcc:groupAccessTableCol ourConnection1 ourTable1 Comments Our Thoughts
lcc:groupAccessTableColSetting ourConnection1 ourTable1 maxLength 500
lcc:groupAccessTableCol ourConnection1 ourTable1 Stats Status Of Enrollment
lcc:groupAccessTableColSetting ourConnection1 ourTable1 maxLength 10
lcc:groupAccessTableColSetting ourConnection1 ourTable1 selectValue Denied
lcc:groupAccessTableColSetting ourConnection1 ourTable1 selectValue Incorrect
lcc:groupAccessTableColSetting ourConnection1 ourTable1 selectValue Recorded
lcc:groupAccessTableColSetting ourConnection1 ourTable1 selectValue Waiting
Definitions
AD
Active Directory.
Data Set
A set of data/information. In databases, this is referred to as 'tables'.
IIS
Internet Information Services, a Microsoft web server platform
LDAP
Lightweight Directory Access Protocol (usually Active Directory)
Page 22 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
Modifications
NAME DATE MODIFICATION
David Mielcarek 5/1/2013 Created
David Mielcarek 5/2/2013 Added Logging
David Mielcarek 5/10/2013 Added Top, Bottom page sections and per
Table Logic.
David Mielcarek 5/22/2013 Added Detail View
David Mielcarek 5/24/2013 Added Col Setting Width
David Mielcarek 5/30/2013 Added Table Settings, including
filteredOnly, maxRows
David Mielcarek 7/8/2014 Added encryption option to User
Id/Password values.
David Mielcarek 11/5/2014 Added keys 'forcedFilter', 'defaultOrderBy',
'hidden', 'translateValue'
David Mielcarek 11/21/2014 added SMTP keys for EmailNotifications, (all
of these keys contain 'SMTP'
David Mielcarek 1/7/2015 added key 'recordNotificationSettings'
David Mielcarek 9/2/2015 added debug level #23, additional SMTP
logging
David Mielcarek 11/24/2015 added debug level #24, show password
when displaying connection setting
David Mielcarek 6/13/2016 added
recordNotification[Created/Modified]Subject
key
David Mielcarek 10/25/2016 added Col Setting skipEmailNotification
David Mielcarek 1/23/2017 added Security Role 'Deletor', pre-defined
subject flags (see
'lccPreDefinedColValueId'')
David Mielcarek 1/25/2017 added key 'overrideSecurity' for tables
David Mielcarek 5/24/2018 added key 'selectValueSelected'
David Mielcarek 2/14/2019 added new 'YESEdit' option on key
lcc:groupAccessTableColKey
Page 23 of 23: lccWebToSQL-whitepaper.docx
Lower Columbia College
David Mielcarek 9/28/2020 added new settings key 'staticQuery'
End of document