ifference between revisions of "EMO:Securing Database Access"
| (One intermediate revision by one other user not shown) | |||
| Line 10: | Line 10: | ||
===Prompted Access Details=== | ===Prompted Access Details=== | ||
| − | |||
To reduce the security risk, access details can be prompted on program launch and embedded into the Connection String. This is achieved by replacing the plain text access details with embedded variable references, as in the following example: | To reduce the security risk, access details can be prompted on program launch and embedded into the Connection String. This is achieved by replacing the plain text access details with embedded variable references, as in the following example: | ||
| Line 32: | Line 31: | ||
:Provider=SQLOLEDB;Data Source=MarketServer1;Initial Catalog=MarketDB; Integrated Security=SSPI | :Provider=SQLOLEDB;Data Source=MarketServer1;Initial Catalog=MarketDB; Integrated Security=SSPI | ||
| − | NOTE: It is not possible to document all permutations of authenticated access control here, please contact helpdesk | + | NOTE: It is not possible to document all permutations of authenticated access control here, please contact the Energy Link [[Help:Contents|helpdesk]] for advice on authenticated access in your environment. |
| − | [[ | + | [[Main Page|Home]] |
Latest revision as of 14:09, 7 February 2014
EMarketOffer's database access parameter handling provides your system administrator with several options for configuring the program to conform with in-house IT security policies.
Embedded Access Details
The simplest option is to embed a valid username and password directly into the Connection String. The following example shows a Connection String constructed to allow John Doe access to the 'MarketDB' database hosted on a Microsoft SQL server:
- Provider=SQLOLEDB;Data Source=MarketServer1;Initial Catalog=MarketDB;User ID=JohnDoe;Password=JDPassword
John Doe must be established as a valid user of the MarketDB database and have read access to any table referenced by any EMarketOffer queries using the Connection String. The Connection String is stored as an .xml file in the Connection data folder. This file is plain text and the database access details can easily be extracted, posing a potential security risk where EMarketOffer workstations are located in unsecured areas.
Prompted Access Details
To reduce the security risk, access details can be prompted on program launch and embedded into the Connection String. This is achieved by replacing the plain text access details with embedded variable references, as in the following example:
- Provider=SQLOLEDB;Data Source=MarketServer1;Initial Catalog=MarketDB;User ID=$UserName$;Password=$Password$
On launching and finding the variable references EMarketOffer opens the following Access Parameters Dialogue (this can also be invoked using the Test Button in the Data Connection Window).
NOTES:
- This option allows you to have multiple users with different passwords using the same EMarketOffer installation
- It is possible to use either of the runtime variables separately. For instance you could set up a single read-only user in the database and embed that user ID in clear text in the .xml connection file, but embed the password using $Password$.
- EMarketOffer will fail to operate if access to vital data expected as a result of parsing the connection file fails
- The variable references "$UserName$" and "$Password$ are case sensitive and contain no spaces
- On start up a dialogue box will be displayed for every connection string containing embedded run time variable references (even in cases where different connections access the same database)
- The values of the run time variables are lost when EMarketOffer is closed down, restarting the program will cause the access dialogue to be re-displayed
Tip: When making changes to the connection string remember to press the save button before closing the Data Connection Window if you want your changes to be used the next time EMarketOffer is launched.
Authenticated Access Control
Depending on the network and workstation operating systems, and the database server and associated interface drivers you use, it may be possible to configure the connection string to use native windows authentication and active directory services to validate a user's database access rights based on their network logon ID. The following example shows the same connection string configured to access a Microsoft SQL database using windows logon authentication (Security Support Provider Interface).
- Provider=SQLOLEDB;Data Source=MarketServer1;Initial Catalog=MarketDB; Integrated Security=SSPI
NOTE: It is not possible to document all permutations of authenticated access control here, please contact the Energy Link helpdesk for advice on authenticated access in your environment.