Emerson Exchange 365

Extract tables and data from the DeltaV Batch Historian by using pyodbc

Dear all, 

I'm trying to connect to the batch historian database by using pyodbc (Pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification. Using pyodbc, you can easily connect Python applications to data sources with an ODBC driver). But i am having trouble to connect to the database. 

My code looks like this:

sql_conn = pyodbc.connect("ODBC Driver 17 for SQL Server};"
                                            "SERVER=server01;"
                                            "DATABASE=DVHisDB;"
                                            "Trusted_Connection=yes;")

but i keep getting the following error:

InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I've tried connectin using the pyhon 32 bit as well as 64 bit and I get the same error for both version of python.

On the server side the driver seems to be installed. Do i have to configure a data source or is it automatically configured at installation of the Batch Historian?

Kind regards,

Jordi

 

Jordi ter Meulen

• 6 Aug 2020 5:59 PM
• Cancel

7 Replies

• Lun.Raznik

  

  • 6 Aug 2020 6:58 PM

  Can try:
  
  sql_conn = pyodbc.connect("Driver={SQL Server};"
  "SERVER=server01;"
  "DATABASE=DVHisDB;"
  "Trusted_Connection=yes;")

  • Cancel
• Lun.Raznik

  

  • 9 Aug 2020 6:36 AM

  In reply to Lun.Raznik:

  Just tried using miniconda Python 3.8 64-bit, this dumps all the table/views under DVHisDB

  In [11]: import pyodbc

  In [12]: sql_conn = pyodbc.connect("Driver={SQL Server};SERVER=localhost;DATABASE=DVHisDB;Trusted_Connection=yes;")

  In [13]: cursor = sql_conn.cursor()

  In [14]: for row in cursor.tables():
  ...: print(row.table_name)

  • Cancel
• LaurentB

  

  • 9 Aug 2020 7:26 AM

  In reply to Lun.Raznik:

  Which version of DeltaV
  I’m not sure at 100% but you probably have to open database with a right credential for an allowed user. Cyber security are enforced on last DV version
  Under which windows account your script is executed ? Did you try it to run it locally on dv historian station with a windows account having all privilege access ?

  • Cancel
• Jordi ter Meulen

  

  • 9 Aug 2020 8:10 AM

  In reply to Lun.Raznik:

  Thank you Lun, as you indicated I was using the wrong driver. 1st problem solved to jump into the next one

  • Cancel
• Jordi ter Meulen

  

  • 9 Aug 2020 8:14 AM

  In reply to LaurentB:

  This is indeed the case,

  DV version 14.3.1

  I am trying to gather the data from outside of the domain and am getting login issues.

  I'm getting the following error message: Error 28000: Login failed for user DOMAIN\\user with pyodbc.

  So I'm now looking for a solution to this problem. I came across some solutions like>

  pyodbc.connect('DRIVER={SQL Server};SERVER=server;DATABASE=database;UID=me;PWD=pass')
  
  

  but i'm still getting the same error. Besides i dont like the idea of storing a password in the code.

  I fount a solution sugesting I should run the code by using RUNAS, but i havent tried yet. 

  Regards, 

  Jordi

  • Cancel
• LaurentB

  

  • 9 Aug 2020 9:17 AM

  Try to read this interesting topics
  
  At the end do not used trusted connection and passed UID and its PWD
  
  stackoverflow.com/.../python-pyodbc-connect-to-sql-server-using-sql-server-authentication

  • Cancel
• LaurentB

  

  • 10 Aug 2020 4:43 AM

  In reply to LaurentB:

  As you said password will be store in readable python code which is a cyber hole.. So you have to create a specific user for this with the lowest privileges as necessary. If your python code is always started by a user ( not a background task) you create a form to invite user to enter its credentials and pass them as parameter to the connexion line.
  Another way consist to use a real compiled language with is a little bit stronger to read password in the compiled exe code ( you will have to recompile code each time the pwd changes)
  
  If you want to use the windows user credential, you don’ t have other choice to make relationship between the 2 domains. which is not so simple to do.

  • Cancel