Access Security When called from .NET

[Guest]

I am trying to open a secured database via .NET. The project worked fine
until I implemented the database security.

(This is in Office 2003 and .NET 2003 on a machine running XP, sp2)

Code:
Public Sub New()
Dim objAppSettings As Specialized.NameValueCollection
objAppSettings = ConfigurationSettings.AppSettings()
Connection = New OleDbConnection( _
"Provider=" & objAppSettings.Item("Provider") & ";" & _
"Data Source=" & objAppSettings.Item("Data Source") & ";" & _
"User ID=" & objAppSettings.Item("User ID") & ";" & _
"Password=" & objAppSettings.Item("Password") & ";" & _
"System database=" & objAppSettings.Item("System database") & ";" & _
"Mode=" & objAppSettings.Item("Mode") & ";" & _
"Persist Security Info=" & objAppSettings.Item("Persist Security Info")
& ";" & _
"Database Locking Mode=" & objAppSettings.Item("Database Locking Mode")
& ";")
objAppSettings = Nothing
End Sub

Where the app.config file has: (password will be encrypted once I get it
working)

<appSettings>
<add key="Provider" value="Microsoft.Jet.OLEDB.4.0" />
<add key="Data Source"
value="C:\Projects\InsVerification\VerifyIns2.mdb" />
<add key="Initial Catalog" value="Nothing" />
<add key="User ID" value="SysAdmin" />
<add key="Password" value="SA37~JF48q" />
<add key="Mode" value="Share Deny None" />
<add key="System database"
value="C:\Projects\InsVerification\Security.mdw" />
<add key="Persist Security Info" value="False" />
<add key="Database Locking Mode" value="1" />
</appSettings>

Now I get the error that the project cannot start because the workgroup file
can't be found or someone else has it open in exclusive mode.

This project is currently on a non-networked pc. No one else has it open. I
have added several users to the workgroup and created shortcuts to the db
with them, then opened several instances at the same time. Multiple users
can't be the problem. The path to the security file is correct. I copied it
from a working shortcut.

What parameter(s) am I missing?

Also, now I get the same error when attempting to run the project but
connected to a non-secured database. Do I have to reinstall office to get rid
of the workgroup?

Thanks for any advice you can provide.

 

[TC]

Access /always/ uses a workgroup information file. So, you need to fix
the problem, not "get rid of" that file.

Also, thge correct first step in securing an Access database, is to
create a new workgroup informatin file for that purpose. So if you
don't know where that file is, it sounds to me as if you have not done
the security properly.

Did you implement the security yourself, and if so, why do you not know
the name & location of the proper workgroup file?

HTH,
TC

 

[Guest]

I used Microsoft's security wizard to implement the workgroup. I used the
workgroup information file name from the wizard report in creating the
shortcuts, which, as I said, work. And I copied that file path and name from
a working shortcut to the app.config file. Also, I am now getting the same
error message when attempting to connect to an unsecurerd db. Both of which
lead me to believe the workgroup file is not the issue and the error verbiage
is misleading.

Before I implemented the security on this db, a search of the entire
computer found no files with the .mdw extension. So, if Access always uses a
workgroup information file, where is the default file? And why is it not
being used for the unsecured db?

 

[Brendan Reynolds]

I'm not able to test this right now, but shouldn't that be "Jet OLEDB:System
database" rather than just "System database"?

 

[TC]

Good questions. I can only repeat that Access (or really, the
underlying database engine, MS Jet) *always* uses a workgroup
information file - no exceptions, ever. The extension does not have/ to
be MDW, but the default workgroup file does use that extensions.

What is the exact text of the error message you are getting?

TC

 

[Brendan Reynolds]

OK - *now* I can test it ...

Module Module1

Sub Main()

Dim strConnection As String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=test;Data Source=c:\usenet\test.mdb;" & _
"Persist Security Info=True;" & _
"Jet OLEDB:System database=c:\usenet\test.mdw"
Dim objConnection As System.Data.OleDb.OleDbConnection
objConnection = New System.Data.OleDb.OleDbConnection(strConnection)
Try
objConnection.Open()
Console.WriteLine("With 'Jet OLEDB:' - OK!")
Console.ReadLine()
objConnection.Close()
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=test;Data Source=c:\usenet\test.mdb;" & _
"Persist Security Info=True;" & _
"System database=c:\usenet\test.mdw"
objConnection = New
System.Data.OleDb.OleDbConnection(strConnection)
objConnection.Open()
Console.WriteLine("If this gets displayed, I was wrong - without
" & _
"'Jet OLEDB:' - OK!")
Console.ReadLine()
objConnection.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
Console.ReadLine()
Finally
If Not objConnection Is Nothing Then
If objConnection.State <> ConnectionState.Closed Then
objConnection.Close()
End If
End If
End Try
End Sub

End Module

Sure enough, it fails on the attempt to use the connection string without
'Jet OLEDB:', with the error message 'Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.'

Apparently, without the 'Jet OLEDB:' prefix, ADO.NET doesn't understand this
part of the connection string, therefore ignores it, and therefore doesn't
know where to look for the workgroup file.

 

[david epsom dot com dot au]

I think that my experience was that Jet could run
without a workgroup file from a VB application.

I don't think you can normally get that from
the Access IDE, because Access always creates
a default login as Admin. (but note that I
reported a problem some years ago where a developer
got the wrong security context when opening a
db object in Access before Access had completed
loading)

When you think you might have this kind of problem,
explicitly create a dbEngine object, then a workspace
object (with the correct credentials), then a database
object.

(david)

PS: Without a workgroup, the dbEngine still has the
"Engine" account. Also, it is possible to implement
the "Admin" account without looking at a workgroup.

A full install of VB/C++/Access design environment
provided a licensed copy of Jet with a system
workgroup. Jet could be used without the design
environment, using the Engine licence instead of
a design licence.

I think that you could get that by using the
default dbEngine instance created by opening
a database object without specifying a dbEngine
or Workspace object.

Access normally uses the system workgroup defined
in the Access registry key.

Of course it may be all a bit different now that
Jet is a part of the operating system installation.

(david)

 

[TC]

Gosh, I think you're right! I seem to remember reading about VB getting
an automatic Admin login. I've never done VB myuself, so I do not know
the details of it.

Thanks for the info, I will read & absorb it further

TC

 

[Guest]

Thanks. This is exactly what I needed.

OK - *now* I can test it ...

Module Module1

Sub Main()

Dim strConnection As String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=test;Data Source=c:\usenet\test.mdb;" & _
"Persist Security Info=True;" & _
"Jet OLEDB:System database=c:\usenet\test.mdw"
Dim objConnection As System.Data.OleDb.OleDbConnection
objConnection = New System.Data.OleDb.OleDbConnection(strConnection)
Try
objConnection.Open()
Console.WriteLine("With 'Jet OLEDB:' - OK!")
Console.ReadLine()
objConnection.Close()
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=test;Data Source=c:\usenet\test.mdb;" & _
"Persist Security Info=True;" & _
"System database=c:\usenet\test.mdw"
objConnection = New
System.Data.OleDb.OleDbConnection(strConnection)
objConnection.Open()
Console.WriteLine("If this gets displayed, I was wrong - without
" & _
"'Jet OLEDB:' - OK!")
Console.ReadLine()
objConnection.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
Console.ReadLine()
Finally
If Not objConnection Is Nothing Then
If objConnection.State <> ConnectionState.Closed Then
objConnection.Close()
End If
End If
End Try
End Sub

End Module

Sure enough, it fails on the attempt to use the connection string without
'Jet OLEDB:', with the error message 'Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.'

Apparently, without the 'Jet OLEDB:' prefix, ADO.NET doesn't understand this
part of the connection string, therefore ignores it, and therefore doesn't
know where to look for the workgroup file.