Avoiding password prompt when reading and writing via ODBC

For questions regarding the import, export and manipulation of data in EViews, including graphing and basic statistics.

Moderators: EViews Gareth, EViews Jason, EViews Steve, EViews Moderator

EViews Steve
EViews Developer
Posts: 789
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Avoiding password prompt when reading and writing via ODBC

Postby EViews Steve » Thu Apr 29, 2010 11:12 am

EViews has support for reading data from and writing data to an ODBC datasource. This is done through the creation of an ODBC DSN defined on the local machine. This DSN specifies which ODBC driver to use, and where the database server is, and which database is the default one.

Many of these databases require a username and password to login. Many of the ODBC wizards written by these database developers support remembering the username used to login to their database. However, they usually don't provide a way to save the password, most likely due to security concerns.

If the security issue is not of great concern to you, there is a way to store the password inside the DSN that will allow you to use it in EViews without the need for a password prompt every time (this is usually important for those of you trying to automate a database refresh process of some kind).

First, you need to know that DSNs saved in the registry do not allow you to save the password. So if your DSN is stored there (if it shows up under the User DSN or System DSN tab in the ODBC Data Sources wizard, it's being stored in the registry), you'll need to create a new one under the File DSN tab.

Once you create it, open the dsn file using Notepad. You should see something like this:

SQL Server DSN Sample:

Code: Select all

[ODBC]
DRIVER=SQL Server
UID=user
PWD=password
Network=DBMSSOCN
DATABASE=Database1
WSID=WORKSTATION
APP=Microsoft® Windows® Operating System
SERVER=Server1


Note the UID and PWD lines. The UID is the UserID and PWD is the password. UID should already be in there (added by the wizard) but the PWD is probably missing. Add the PWD line and set it to your password for that user. Save the modified DSN.

And that's it. The next time you use this DSN (e.g. from EViews) the password prompt should not appear.

Since the password is stored in the clear in this file, you should NOT use this method if you don't want your users knowing what the password is. One suggestion is that if you only need read capability thru this ODBC connection, you should setup a new database user with read-only access and then use that in this DSN file. On some database servers, you can also control which tables can be read and what time of day this user account is active.

Steve

oleviasharbaugh
Posts: 17
Joined: Thu Aug 24, 2023 9:42 am

Re: Avoiding password prompt when reading and writing via ODBC

Postby oleviasharbaugh » Tue Dec 05, 2023 12:33 pm

Hi Steve,

Thanks for this post! It has been super helpful in getting rid of the password request, however, I now have to "select data source" each time I make the connection to the database which is still not ideal. I am currently making the connection with the below code:

Code: Select all

pageload(type=odbc) OracleConnection.dsn " SQL code to pull the data I want here" @keep *


Is there a way to add something to these commands so that the program automatically knows to pull from the OracleConnection.dsn that is located on my local desktop?

EViews Steve
EViews Developer
Posts: 789
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: Avoiding password prompt when reading and writing via ODBC

Postby EViews Steve » Tue Dec 05, 2023 4:11 pm

If your DSN file is at a certain path, just specify that path in the command:

Code: Select all

pageload(type=odbc) C:\Users\username\Desktop\OracleConnection.dsn " SQL code to pull the data I want here" @keep *


Or maybe even put the DSN on the local network somewhere so it's available to everyone...

Code: Select all

pageload(type=odbc) \\server\folder\OracleConnection.dsn " SQL code to pull the data I want here" @keep *


Return to “Data Manipulation”

Who is online

Users browsing this forum: No registered users and 10 guests