Solution: PI ODBC Driver Configuration

Document created by kduffy on Sep 26, 2018Last modified by kduffy on Feb 14, 2019
Version 6Show Document
  • View in full screen mode

This document contains the answers to the Exercise: PI ODBC Driver Configuration . The screenshots below are from ODBC Data Source Administrator.

 

The key things that govern the answers to this exercise are:

  • The bitness of the application must match the bitness of the ODBC DSN
  • If connecting to a PI Data Archive directly, the DSN must use PI OLEDB Provider; for an AF Server, PI OLEDB Enterprise must be used
  • The provider level options go in the "Options" box at the bottom of the "Data Source" tab

 

1. A 32-bit application routing through a PI SQL DAS named PISQLDAS01 using explicit login, connecting to a PI Data Archive named "PISRV01" using explicit login. The PI user is "OledbUsers" and the password is "5q1i5|=v|\|"; the domain username is "DomainOledbUsers" and password "5q1i5|=v|\|".

  • Use the ODBC Data Source Administrator (32-bit) to create a 32-bit DSN
  • The name of the PI SQL Data Access Server is PISQLDAS01
  • The PI SQL DAS is being explicitly logged into by the ODBC DSN, so the DSN needs to contain domain user credentials for a user that is allowed to log into that machine
  • Since this is connecting to a PI Data Archive, the provider type is PI OLEDB Provider and the PI Data Archive name is PISRV01
  • The PI OLEDB Provider is explicitly logging into the PI Data Archive, so the User ID and Password parameters are entered into the Options box (see Solution: PI OLEDB Provider Configuration for more information)

 

 

2. A 32-bit application routing through a PI SQL DAS named PISQLDAS01, connecting to a PI AF Server named "AFSRV01" using Windows explicit login. The Windows domain username is "OledbUsers" and password is "5q1i5|=v|\|".

  • Use the ODBC Data Source Administrator (32-bit) to create a 32-bit DSN
  • The name of the PI SQL Data Access Server is PISQLDAS01
  • The PI SQL DAS is being explicitly logged into by the ODBC DSN, so the DSN needs to contain domain user credentials for a user that is allowed to log into that machine
  • Since this is connecting to an AF Server, the provider type is PI OLEDB Enterprise and the AF Server name is AFSRV01
  • The PI OLEDB Enterprise is explicitly logging into the AF Server, so the User ID and Password parameters are entered into the Options box (see Solution: PI OLEDB Enterprise Configuration for more information)

 

3. A 64-bit application routing through a PI SQL DAS named PISQLDAS02, connecting to a PI Data Archive named "PISRV02" passing the user's context from start to finish via SSPI, setting the only allowed protocol for communication from the ODBC driver to the PI SQL DAS to HTTPS.

  • Use the ODBC Data Source Administrator (64-bit) to create a 64-bit DSN
  • The name of the PI SQL Data Access Server is PISQLDAS02
  • The PI SQL DAS is being automatically logged into by the ODBC DSN using the identity provided by the application
  • Since this is connecting to a PI Data Archive, the provider type is PI OLEDB Provider and the PI Data Archive name is PISRV02
  • The PI OLEDB Provider is automatically logging into the PI Data Archive via SSPI, so the Integrated Security=SSPI parameter is entered into the Options box (see Solution: PI OLEDB Provider Configuration for more information)
  • The NetTcp protocol has been removed from the Protocols tab, which means the only allowable protocol is HTTPS

 

4. A 64-bit application routing through a PI SQL DAS named PISQLDAS02, connecting to a PI AF Server named "AFSRV02" passing the user's context from start to finish via SSPI, prioritizing HTTPS over NetTcp for communication from the ODBC driver to the PI SQL DAS to HTTPS.

  • Use the ODBC Data Source Administrator (64-bit) to create a 64-bit DSN
  • The name of the PI SQL Data Access Server is PISQLDAS02
  • The PI SQL DAS is being automatically logged into by the ODBC DSN using the identity provided by the application
  • Since this is connecting to an AF Server, the provider type is PI OLEDB Enterprise and the AF Server name is AFSRV02
  • The PI OLEDB Enterprise is automatically logging into the AF Server via SSPI, so the Integrated Security=SSPI parameter is entered into the Options box (see Solution: PI OLEDB Enterprise Configuration for more information)
  • The NetTcp protocol has been moved down to below HTTPS on the Protocols tab, which means the connection will attempt to use HTTPS first, then fall back to NetTcp if necessary

 

5. A 32-bit application routing through a PI SQL DAS named PISQLDAS03, connecting to a PI Data Archive named "PISRV03" passing the user's context from start to finish via SSPI, disabling asynchronous PISDK calls, and establishing stateless connections between the ODBC driver and the PI SQL DAS.

  • Use the ODBC Data Source Administrator (32-bit) to create a 32-bit DSN
  • The name of the PI SQL Data Access Server is PISQLDAS03
  • The PI SQL DAS is being automatically logged into by the ODBC DSN using the identity provided by the application
  • Since this is connecting to a PI Data Archive, the provider type is PI OLEDB Provider and the PI Data Archive name is PISRV03
  • The PI OLEDB Provider is automatically logging into the PI Data Archive via SSPI and the async calls are turned off, so the Integrated Security=SSPI and Sync Calls=True parameters are entered into the Options box (see Solution: PI OLEDB Provider Configuration for more information)
  • Since the connections between the ODBC driver and PI SQL DAS need to be stateless, the "Stateless connection" box needs to be checked on the Workarounds tab

 

6. A 32-bit application routing through a PI SQL DAS named PISQLDAS03, connecting to a PI AF Server named "AFSRV03" passing the user's context from start to finish via SSPI, and when an error message is encountered for a value, it's embedded into that cell in the result set instead of erroring the query. Lastly, the application cannot parse guid data types, so all guids will need to be returned as a string data type.

  • Use the ODBC Data Source Administrator (32-bit) to create a 32-bit DSN
  • The name of the PI SQL Data Access Server is PISQLDAS03
  • The PI SQL DAS is being automatically logged into by the ODBC DSN using the identity provided by the application
  • Since this is connecting to an AF Server, the provider type is PI OLEDB Enterprise and the AF Server name is AFSRV03
  • The PI OLEDB Enterprise is automatically logging into the AF Server via SSPI and the errors are embed, so the Integrated Security=SSPI and Embed Errors=True parameters are entered into the Options box (see Solution: PI OLEDB Provider Configuration for more information)
  • Since the client cannot receive guid data types from the ODBC driver, the "Guid as string" box needs to be checked on the Workarounds tab to convert all guids into string before they reach the client

Attachments

    Outcomes