Qia's LabVIEW Station Virry Test & Control

We talk about LabVIEW and HVAC

Problem of security using ADO from Labview to connect to Access 2000 Data Base

Options    Options  
Peter Mst
Member


Peter Mst

Reply 1 of 7

Viewed 243 times
I have a problem using ADO from Labview to connect to Access 2000 Data Base.

First I open a ADO_DB_Engine and create a Workspace as an ODBCDirect. After I execute the open_data_base using the workspace. Here you can specify the parameter connect with /type of connection / user / password.

The problem is the following:

Although you configure a password for the Administrator user in Access, you can read/writte to data base from Labview using ADO without any security and without putting the user and password in the parameter connect. I need to access with security to data base using users and permissions but it seems that the parameter connect does not operate.

I attach
you a example of the vis and database. Remember create a ODBC connect with BD3.mdb

Thanks a lot

Peter Mst

Attachment SECURITY.zip (70 kb)
Attachment BD3.mdb (112 kb)

03-21-2003 01:59 AM
  Reply   Reply  

 Re: Problem of security using ADO from Labview to connect to Access 2000 Data Base
Options    Options  
C. Hahn
Member


C. Hahn

Reply 2 of 7

Viewed 243 times
Microsoft Access defines two types of database security, the first of these is to configure usernames and passwords (tools >> Security >> User and Group Accounts).
This type of security is somewhat misleading because it only applies to the MS Access application itself not the databases opened with Access, which means that they can be opened in LabVIEW without any usernames or passwords (they could be opened on another computer with MS Access too, so it isn't really a security feature).
In order to set a password for a particular database (.mdb file) go to: Tools >> Security >> Set Database Password. (See the help file "Protect a database by adding a database password" for more information on this feature). After this you will need to provide the passwo
rd that you set in Access in order read/write to the database in LabVIEW.
Essentially, MS Access does not provide username/password security for .mdb files but only allows you to set a password.
03-24-2003 12:58 PM
  Reply   Reply  

 Re: Problem of security using ADO from Labview to connect to Access 2000 Data Base
Options    Options  
Peter Mst
Member


Peter Mst

Reply 3 of 7

Viewed 243 times
Thank C.Hahn for the response.

I have observed that if you use tools>>security>>Assistance to user security, you can create a new owner and users of the .mdb file and eliminate the Administrator user. Using this way is possible to protect the database file from Access. If you don't have the password and the corresponding user it's not possible to open the database from Access and from any PC. From this point of view it's solved yet.

The problem is when you want to connect to database from Labview using ADO. From the OPEN Method of an ADODB_connection is possible indicate the connectionstring /user ID / password but then the application returns error. It seems that the only way to enter to Database file from ADO is using the Administrator user but so you always can access to Database without security.

The other way is to use the tools>>security>>set Database Password as Hahn indicated. Using this you can enter the password in OPEN Method but the user parameter doesn’t act. For my application this is a problem because from Access you will have to enter 2 passwords: 1.- The general of the Database file and 2.- The user password if you want to protect objects of the database file using permissions.

It’s incredible not be able to use users and passwords for Access from ADO. I don’t understand it . From SQL Server you can do it. Some solution would have to exist !!

I saw a similar subject in the discussion forums of AIR Tech Autom 11/24/2000. They answered:

“I’ve had this problem using SQL Server. I’ve wired the DSN normally, to the connection parameters, I’ve had to wire:

Trusted_connection=NO;
DSN=DSN_NAME;
UID=user_id;
PWD=user_password;

With SQLServer, if you do not add Trusted_connection=NO, the system will automatically connect using windows account. It will ignore the UID and PWD.”

I have put this in connectionstring parameter but it didn’t work.

I thank you beforehand for your help.
03-25-2003 05:02 AM
  Reply   Reply  

 Re: Problem of security using ADO from Labview to connect to Access 2000 Data Base
Options    Options  
C. Hahn
Member


C. Hahn

Reply 4 of 7

Viewed 243 times
When using Users/passwords (Tools >> Security >> User and Group Accounts) the database CAN in fact be opened on another computer. I verified this by creating a database, setting an Admin password (at this point the db cannot be opened in Access on my computer without the username/password) and then tried to open this same db in MS Access on another computer without supplying a username/password, which I did successfully. This means that the database itself is not protected, only the MS Access application. Because of this the only way to protect the database is to set a database password (Tools >> Security >> Set Database Password). You could implement username/password security within LabVIEW (by having a login subVI that stores usernames/passwo
rds) but this would not protect the database from being opened in another application.
It might be possible to use third-party software to encrypt/decrypt the database and invoke that from LabVIEW. Let me know if there is anything else I can do to help.

Regards,
C. Hahn
Applications Engineer
National Instruments
http://www.ni.com/ask
03-25-2003 04:02 PM
  Reply   Reply  

 Re: Problem of security using ADO from Labview to connect to Access 2000 Data Base
Options    Options  
Peter Mst
Member


Peter Mst

Reply 5 of 7

Viewed 243 times
Hahn,

I have created a new owner for sample1.mdb using the Herramientas>>Seguridad>>Asistente para seguridad para usuarios in Spanish Access. In English access may be Tools>> Security>>Assistance for user security.
This assistance creates a protected copy of the .mdb file with a new owner/password and new users/passwords deleting the default Administrator user. Using this way you have security in any PC with Access without having to set database password.
I attach you sample1.mdb. This database file is protected with users/passwords and you can try to open it. I attach you Protegida.mdw file too. Before to open Access you use the wrkadm.exe(search from explorer in office folder)and load the Protegida.mdw file whic
h has the encrypted user counts. This file has been created from Access with the assistance. Access always compare with .mdw file to begin.

If you set :

user:jose
password:123

you will be able to enter to Database file as Administrator with all permissions.

If you set:

user:lane
password: 893

you will be to enter as user with only read permission.

This would be a good solution but the problem is when you want to enter from Labview using ADO and DSN. The connect parameter with user name and password does not act. I attach you the sample_ADO_W.vi to connect to sample1.mdb. Remember create a ODBC connection with a DSN before.

We will be in contact.

Attachment sample1.mdb (124 kb)
Attachment sample_ADO_W.vi (46 kb)
Attachment Protegida.mdw (112 kb)

03-26-2003 03:02 AM
  Reply   Reply  

 Re: Problem of security using ADO from Labview to connect to Access 2000 Data Base
Options    Options  
C. Hahn
Member


C. Hahn

Reply 6 of 7

Viewed 243 times
As far as I can determine, MS does not allow us to connect to a secured database through LabVIEW. See the following links:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dao360/html/damthopendatabase.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate_topic4.asp

This link explains how to open a database using DAO. However when I do this by setting the "SystemDB" property to the .mdw file I get the following error:

"Error -2146824865 ...You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database.."

Which makes me think that it is not allowed. Let me know if you have any luck using this method
.


C. Hahn
Applications Engineer
National Instrument
http://www.ni.com/ask
03-26-2003 02:25 PM
  Reply   Reply  

 Re: Problem of security using ADO from Labview to connect to Access 2000 Data Base
Options    Options  
Peter Mst
Member


Peter Mst

Reply 7 of 7

Viewed 243 times
Hi Kahn,

If you use ADODB_connection -> Connection.Open and set in connection string :

"Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:System Database=c:\.....\Protegida5.mdw; Password=pepe;user ID=jose; Data Source:c:\......\BD5.mdb";

and after connection.execute ->

INSERT INTO tabla1 VALUES (5,'XXX'

you will be able to access to the database with security using the provider=Microsoft.Jet.OLEDB.4.0. With this provider you have to use the path and the name of the database file c:\.....\mdb file. From this way the problem is solved. (SEE SAMPLE_ADO_W2_JET.VI).
BD5.mdb have the following administrator user: user:jose / passwordepe (Protegida5.mdw)


The problem is when you want to
use ODBC and DSN. The name of this ODBC Provider is MSDASQL;. If you use this type of connection you can not enter to the database I attach you.
This database has a new administrator owner. I deleted the default administrator. If you support the default administrator you can connect to database by ODBC / DSN but the security disappear because you can always enter to database with any password, user o PC.

FINALLY I HAVE 3 QUESTIONS FOR Application Engineers of National Instruments or someone who knows these subjects before deciding the best way to connect locally o remotely with security to Access from Labview:

1.- Is it possible to enter to Database BD5.mdb from ODBC and DSN with this owner?

2.- I want to connect remotely to Access database too. Is it possible to use Microsoft.Jet.OLEDB.4.0 using the path (.mdb file) without DSN?

3. Which is the optimal Provider for Microsoft Access?



I thank you beforehand for your technical support.



Peter Mst.

Attachment BD5.mdb (116 kb)
Attachment Protegida5.mdw (112 kb)
Attachment sample_ADO_W2_JET.vi (45 kb)

posted on 2006-01-09 12:16  LabVIEW开发者  阅读(533)  评论(0)    收藏  举报

导航