Difference between revisions of "BI Views access"

SQL Client Setup

Encryption and Checksum

Data provide via SQL to FPDS-NG Views will be excrypted using Oracle provided industry standard utilities. Encryption and checksum rules are encforced to disallow any kidn of data manipulation.

Requirement

The client installation should have Oracle Advanced Networking Option.

Configure TNSNAMES.ORA and SQLNET.ORA

Changes to tnsnames.ora:

Open the sqlnet.ora file on the client with a editor of choice.

Beta access

Add the following lines to the TNSNAMES.ORA

##############################################################################
#SQLNET.ORA Network Configuration
##############################################################################
FPDSNG_BETA =
  (DESCRIPTION =
    (ADDRESS_LIST=
      (ADDRESS = (PROTOCOL = TCP)(HOST=38.118.10.153)(PORT=1525))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME=fpdsbi)
    )
  )

Production access

Add the following lines to the TNSNAMES.ORA

##############################################################################
#SQLNET.ORA Network Configuration
##############################################################################
FPDSNG_PROD_BI =
  (DESCRIPTION =
    (ADDRESS_LIST=
      (ADDRESS = (PROTOCOL = TCP)(HOST=63.236.106.79)(PORT=1525))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME=fpdsbi)
    )
  )

Changes to SQLNET.ORA

Open the sqlnet.ora file on the client with a text editor.

Add the following lines to sqlnet.ora. However, before doing this, make sure that these entries do not already exist in the file mentioned.

############################################################################################
#SQLNET.ORA Network Configuration
############################################################################################
sqlnet.crypto_seed="encryptionandchecksummingforbisqlakasdfjheiiwefhfpdsngcomfigurationano"
sqlnet.ecryption_client=accepted
sqlnet.encryption_types_client=(RC4_128)
sqlnet.crypto_checksum_client=accepted
sqlnet.crypto_types_client=(MD5)

All entries are mandatory. The entry for crypto_seed is very important and it can be any character string of upto maximum of 70 bytes. It can be any combination of alphabetic characters. A sample value is provided here for your reference.

TEST THE FUNCTIONALITY

Test the SQL Connection

Now that all the configurations are complete, try to make a SQLPLUS connection to the database that has been just configured. If the connection is successful, you have a successfull TCP/IP with encrypted and checksumed connection to the server from the client.

Common problems

1. ORA-12645:Parameter does not exist
   The setting are not done properly, please start from the beginning of this document again. Please see below section on "how to enable trace"? and follow steps accordingly. Check the log/trace files generated to see if the error can be resolved.
   If you need further assistance, please contact FPDS-NG help desk at 703 390 5360 or send email to fpdssupport@gcefederal.com.
2. ORA-12154:TNS:could not resolve service name or TNS-03505: Failed to resolve name
   This is a client side setup problem. Please check for more than one oracle home. If you plan on using all of them, please ensure that you have the above configured entry in all the TNSNAMES.ORA and the SQLNET.ORA files on the client machine.

How to enable trace?

Add the following lines to the SQLNET.ORa and retest the connection. However, before doing this, make sure that these entries do not already exist in the file mentioned.

# Following parameters should be used only when you want to trace a problem
LOG_DIRECTORY_CLIENT=c:\oracle\ora92\network\log
LOG_FILE_CLIENT=sqlnet.log
TRACE_DIRECTORY_CLIENT=c:\oracle\ora92\network\trace
TRACE_fILE_CLIENT=sqlnet.trc
TRACE_LEVEL_CLIENT=16

If you have the following lines in your sqlnet.ora; please comment it with a has (#)

# SQLNET.AUTHENTICATION_SERVICES = (NTS)

How to disable trace?

Comment out the following lines to the sqlnet.ora and retest the connection.

# Following parameters should be used only when you want to trace a problem
# LOG_DIRECTORY_CLIENT=c:\oracle\ora92\network\log
# LOG_FILE_CLIENT=sqlnet.log
# TRACE_DIRECTORY_CLIENT=c:\oracle\ora92\network\trace
# TRACE_fILE_CLIENT=sqlnet.trc
# TRACE_LEVEL_CLIENT=16

Summary

Steps for User:

• Open the firewall for your computer's IP address
• User enters TNS Entry on their computer into the "TNSNAMES.ORA" file
• User enters USERID and Password provided by GCE at the login to the database
• User enters "FPDSBI" as the SID entry for the connection