Access Security When called from .NET

20 Oct 2005 2:43 AM

Don Losure

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.

20 Oct 2005 3:24 AM

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

20 Oct 2005 8:29 AM

Don Losure

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?

Show quoteHide quote

"TC" wrote:

> 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
>
>

20 Oct 2005 1:56 PM

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

21 Oct 2005 1:22 AM

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)

Show quoteHide quote

"TC" <aatcbbtcc***@yahoo.com> wrote in message
news:1129816605.893333.226240@o13g2000cwo.googlegroups.com...
> 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
>

21 Oct 2005 1:43 AM

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

20 Oct 2005 9:56 AM

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"?

--
Brendan Reynolds

Show quoteHide quote

"Don Losure" <DonLos***@discussions.microsoft.com> wrote in message
news:3BEA45AC-184C-495B-9039-CD3AEE1F9951@microsoft.com...
>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.
>

20 Oct 2005 7:06 PM

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.

--
Brendan Reynolds

Show quoteHide quote

"Brendan Reynolds" <brenr***@discussions.microsoft.com> wrote in message
news:OI69AyV1FHA.2076@TK2MSFTNGP14.phx.gbl...
>
> I'm not able to test this right now, but shouldn't that be "Jet
> OLEDB:System database" rather than just "System database"?
>
> --
> Brendan Reynolds
>
> "Don Losure" <DonLos***@discussions.microsoft.com> wrote in message
> news:3BEA45AC-184C-495B-9039-CD3AEE1F9951@microsoft.com...
>>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.
>>
>
>

1 Nov 2005 8:16 AM

Don Losure

Thanks. This is exactly what I needed.

Show quoteHide quote

"Brendan Reynolds" wrote:

> 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.
>
> --
> Brendan Reynolds
>
> "Brendan Reynolds" <brenr***@discussions.microsoft.com> wrote in message
> news:OI69AyV1FHA.2076@TK2MSFTNGP14.phx.gbl...
> >
> > I'm not able to test this right now, but shouldn't that be "Jet
> > OLEDB:System database" rather than just "System database"?
> >
> > --
> > Brendan Reynolds
> >
> > "Don Losure" <DonLos***@discussions.microsoft.com> wrote in message
> > news:3BEA45AC-184C-495B-9039-CD3AEE1F9951@microsoft.com...
> >>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.
> >>
> >
> >
>
>
>