db tools open connection.vi does not get password from dsn file

[optional_clause]

I am

[Hillary_C_E]

Hi Optional Clause,

I'm not sure exactly where the problem lies as it seems that you haven't been able to post your full situation. Until then, you might want to check out the following KnowledgeBase articles about connecting to databases with DSN files.

Connection Methods in the Enterprise Connectivity Toolkit

Creating a DSN File To Communicate with Access Database in LabVIEW?

Let us know if you have more questions!

[optional_clause]

OK, Lets try this again.  I am trying to connect to a Database using a file dsn.  The db tools open connection. vi does not seem to be able to get the password from the dsn file.  If I hard wire the password (along with the dsn file name / path) to the db tools open connection. vi, I connect to the db just fine.  If I only wire the dsn filename / path to the db tools open connection.vi, I get the following error message "

Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'eslprodsproc'. in DB Tools Open Connec (String).vi->DB Tools Open Connec (Path).vi->FindShiftNumber.vi

The file dsn connects just fine in the datasources in windows control panel.  I am trying to connect to an SQL database.  I have the same problem with a system dsn file.  My IT group does not want me to hard code passwords.  The only option I can see at this point is to read the password from a text or html file, which is not desirable.

[Hillary_C_E]

Hi Optional,

Thanks for all the details. What is the specific type of database that you are connecting to? I know you mentioned you are using SQL to interface.

If you recreate the file dsn, can you replicate the problem of receiving the error you described?

One simple workaround could be to have the password as a control on the front panel. In that situation, the password would have to be entered to run the program. You could also hardcode the password and then password protect your vi or executable. 

Can you post a screenshot of your error?

[optional_clause]

The database is a Microsoft SQL server database.  Again, my IT group does not want the password hard coded into the application.  Their preference would be for the password to be extracted from the dsn.  I have tried both a file dsn and a system dsn which were created in the data sources app in control panel --> administartive tools.  Neither dsn works (unless I wire the password to the databse tools open connection vi).  Both dsn's work fine from the windows data sources application.  I have not been able to figure out how to attach graphics to a post on the board here, so I 'm not sure how to attach the error message (or my block diagram).  The text from the error message is below.  Wiring in the password causes the error to go away.

Error -2147217843 occurred at DB Tools Open Connec (String).vi->DB Tools Open Connec (Path).vi->FindShiftNumber.vi

Possible reason(s):
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'eslprodsproc'. in DB Tools Open Connec (String).vi->DB Tools Open Connec (Path).vi->FindShiftNumber.vi

[Hillary_C_E]

Optional,

I am a bit stumped that neither your file or system dsn work in LabVIEW but they do work in Data Sources. Is it possible that the password isn't getting properly saved in the dsn? Also, you can post attachments if you look at the bottom of your reply/post message. Underneath the gray outer box and above the response you are replying to you should see Attachment with a Browse.. Button to select your attachment(s). Has anyone else on the forums been able to resolve this? I have seen some older discussion forums here and here but don't know if they really apply to your situation. They tried using UDL.

[optional_clause]

I can not really tell if the password is being saved, but when I go into control panel-->data sources and try to edit the file dsn, the password is blank.  If I enter the password and test the connection (in control panel), the connection works.  If I open the file dsn in note pad I do not see a password field.  The notepad view is below ( I have removed the login data and replaced with XXXX):

[ODBC]

DRIVER=SQL Server

UID=XXXXX

Network=XXXXX

DATABASE=XXX

WSID=XXXXXX

APP=Microsoft Data Access Components

SERVER=XXXX

Description=XXXX

DOES THIS INDICATE THAT THE PASSWORD IS NOT BEING SAVED?

[Hillary_C_E]

So, with some more investigating into the Open DB vi that you are using it appears that the only way to not have a password is if the database isn't password protected. I believe that when you are using the Data Sources in the Control Panel it prompts you for the password because it isn't saved into the File DSN. The best solution is going to be to create a control on your front panel. That way the password isn't hardcoded into your vi. The user will have to input it to use the vi.

[Ola_A]

I've seen something similar, maybe it can help you. We had our data in an access file, and the connection info in a "system-DSN" named db1. We then had a UDL-file which only specified which datasource to use, db1 in this case, and in labview we choose which udl-file to use. When we move the database to mySql server we needed to do add login info in the system-DSN "db2", which was saved, but labview couldn't use the password when we used the udl-file. When we directly wired the name "db2" to open database vi it worked!

Ola