By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,225 Members | 1,434 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,225 IT Pros & Developers. It's quick & easy.

DBEngine.CreateWorkspace Error 3002 Could Not Start Session

P: n/a
Does anyone have any idea how to recover from a run-time error 3002
which I get after creating a workspace 242 times? Or better yet, avoid
getting it in the first place?

I am creating a workspace with a blank password for different Users in
order to find out which Users do not have passwords. This works just
fine if I do it a few times, but if I want to do it periodically with a
loop, it bombs out after 242 times of finding a user with no password
(ie sucessfully opening a workspace). In my code, I do try to free the
created workspace each time, yet I get the same error totally
predictably whether or not I do the close/set to nothing or not.
Perhaps I am not freeing it correctly. Thre must be some kind of
cleanup I can do, but I don't know what. The only thing I can do now is
close the Access Application and restart it.

I am putting the code below. My test here is a tight loop, but normally
I have a timer and this gets triggered every 10 seconds or whatever the
user requests as refresh rate for the form displaying this information.
To simplify for this post, I am just doing a loop, but wanted to
indicate that it is not because it needs time between CreateWorkspace
calls. I am using Access 2000.

TIA for any help, Christine
---------- Code Start ------------------------
dim mlWkSpace as long, mlBlank as long 'module level variables

Sub TestPwds()
Dim i As Integer
For i = 1 To 1000
MonitorBlankPwds
Next
End Sub

Function MonitorBlankPwds(Optional fMsg As Boolean) As Integer
'' Cycles through all the Users in tbuUsers and returns True if any
Users are missing a Password.
'' If the optional argument fMsg is True, a message box is presented to
the User,
'' giving a count of the number of missing passwords.
On Error GoTo Err
Dim wrkdefault As DAO.Workspace
Dim wrkNew As DAO.Workspace
Dim fHasPwd As Boolean
Dim rs As New ADODB.Recordset
Dim iCountBlankPwds As Integer
Dim vbMsgBox As VbMsgBoxStyle
Dim sMsg As String

'Set wrkdefault = DBEngine.Workspaces(0) '<<<<tried it with &
without this line - dont see why I should need - but then I don't know
why I have a problem either.... :)

rs.Open "Select UserID, User, Password from tbuUsers", _
CurrentProject.Connection, adOpenForwardOnly,
adLockBatchOptimistic

While Not rs.EOF

fHasPwd = False

' Attempt to log on to new workspace with blank Pwd
Set wrkNew = DBEngine.CreateWorkspace("NewWks", rs("User"), "")
mlWkSpace = mlWkSpace + 1

' If an error occurred on last statement, then error handler
' will set flag to True because it must have a password.
' Otherwise, able to log on, so Pwd must've been blank.
If Not fHasPwd Then
'Debug.Print "Blank Password:" & rs("User")
fPwdsMissing = True
iCountBlankPwds = iCountBlankPwds + 1
Else
mlBlank = mlBlank + 1
If Not (wrkNew Is Nothing) Then
wrkNew.Close
Set wrkNew = Nothing
End If
End If
rs.MoveNext
Wend
MonitorBlankPwds = iCountBlankPwds
rs.Close
Set rs = Nothing

If fMsg Then
sMsg = iCountBlankPwds & " Users have no Password."
If iCountBlankPwds = 0 Then
vbMsgBox = vbInformation
Else
vbMsgBox = vbExclamation
End If
MsgBx sMsg, vbMsgBox, "Password Monitor"
End If
Exit Function
Err:
Select Case Err
Case 3029 'gcErrBadAccntOrPwd
'Could not log on user account with blank Pwd
fHasPwd = True
Resume Next
Case Else
Debug.Print "MonitorBlankPwds Error: " & mlWkSpace & " " &
mlBlank & vbCrLf & Err.Number & " " & Err.Description
Stop '<<<< Just here so I can look at what's going on
Err_Handler Err.Number
End Select
End Function 'MonitorBlankPwds
'----------- end of code ---------------------------

Nov 13 '05 #1
Share this Question
Share on Google+
20 Replies


P: n/a
On 25 Oct 2005 15:26:12 -0700, ct******@stny.rr.com wrote:

tbuUsers? That sounds a lot like your own table. Why not check for
nulls in that table?
select * from tbuUsers where Password is null

Perhaps you can explain why you have a tbuUsers without a Password
field, and rely on Access security at the same time.

-Tom.
Does anyone have any idea how to recover from a run-time error 3002
which I get after creating a workspace 242 times? Or better yet, avoid
getting it in the first place?

I am creating a workspace with a blank password for different Users in
order to find out which Users do not have passwords. This works just
fine if I do it a few times, but if I want to do it periodically with a
loop, it bombs out after 242 times of finding a user with no password
(ie sucessfully opening a workspace). In my code, I do try to free the
created workspace each time, yet I get the same error totally
predictably whether or not I do the close/set to nothing or not.
Perhaps I am not freeing it correctly. Thre must be some kind of
cleanup I can do, but I don't know what. The only thing I can do now is
close the Access Application and restart it.

I am putting the code below. My test here is a tight loop, but normally
I have a timer and this gets triggered every 10 seconds or whatever the
user requests as refresh rate for the form displaying this information.
To simplify for this post, I am just doing a loop, but wanted to
indicate that it is not because it needs time between CreateWorkspace
calls. I am using Access 2000.

TIA for any help, Christine
---------- Code Start ------------------------
dim mlWkSpace as long, mlBlank as long 'module level variables

Sub TestPwds()
Dim i As Integer
For i = 1 To 1000
MonitorBlankPwds
Next
End Sub

Function MonitorBlankPwds(Optional fMsg As Boolean) As Integer
'' Cycles through all the Users in tbuUsers and returns True if any
Users are missing a Password.
'' If the optional argument fMsg is True, a message box is presented to
the User,
'' giving a count of the number of missing passwords.
On Error GoTo Err
Dim wrkdefault As DAO.Workspace
Dim wrkNew As DAO.Workspace
Dim fHasPwd As Boolean
Dim rs As New ADODB.Recordset
Dim iCountBlankPwds As Integer
Dim vbMsgBox As VbMsgBoxStyle
Dim sMsg As String

'Set wrkdefault = DBEngine.Workspaces(0) '<<<<tried it with &
without this line - dont see why I should need - but then I don't know
why I have a problem either.... :)

rs.Open "Select UserID, User, Password from tbuUsers", _
CurrentProject.Connection, adOpenForwardOnly,
adLockBatchOptimistic

While Not rs.EOF

fHasPwd = False

' Attempt to log on to new workspace with blank Pwd
Set wrkNew = DBEngine.CreateWorkspace("NewWks", rs("User"), "")
mlWkSpace = mlWkSpace + 1

' If an error occurred on last statement, then error handler
' will set flag to True because it must have a password.
' Otherwise, able to log on, so Pwd must've been blank.
If Not fHasPwd Then
'Debug.Print "Blank Password:" & rs("User")
fPwdsMissing = True
iCountBlankPwds = iCountBlankPwds + 1
Else
mlBlank = mlBlank + 1
If Not (wrkNew Is Nothing) Then
wrkNew.Close
Set wrkNew = Nothing
End If
End If
rs.MoveNext
Wend
MonitorBlankPwds = iCountBlankPwds
rs.Close
Set rs = Nothing

If fMsg Then
sMsg = iCountBlankPwds & " Users have no Password."
If iCountBlankPwds = 0 Then
vbMsgBox = vbInformation
Else
vbMsgBox = vbExclamation
End If
MsgBx sMsg, vbMsgBox, "Password Monitor"
End If
Exit Function
Err:
Select Case Err
Case 3029 'gcErrBadAccntOrPwd
'Could not log on user account with blank Pwd
fHasPwd = True
Resume Next
Case Else
Debug.Print "MonitorBlankPwds Error: " & mlWkSpace & " " &
mlBlank & vbCrLf & Err.Number & " " & Err.Description
Stop '<<<< Just here so I can look at what's going on
Err_Handler Err.Number
End Select
End Function 'MonitorBlankPwds
'----------- end of code ---------------------------


Nov 13 '05 #2

P: n/a
Tom,
Yes, tbuUsers is my own table. The intention of the MonitorBlankPwds
routine is that it will update the Password field in tbuUsers, although
I had removed the line that does that in the process of troubleshooting
the 3002 run-time error, just to be sure that that was not part of the
problem. The Password field is just a True/False field which I would
normally set in this routine. I have a form for system administrators
that displays User information: who is logged on when, whether they
have a password, etc which uses this table.

I rely completely on Access security. The tbuUsers table just happens
to have a field for indicating whether or not the User has a password.
MonitorBlankPwds is the field that updates it.

Do you have any ideas about the DBEngine.Createworkspace problem I am
trying to troubleshoot?

Thanks,
Christine

Nov 13 '05 #3

P: n/a
<ct******@stny.rr.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...
Tom,
Yes, tbuUsers is my own table. The intention of the MonitorBlankPwds
routine is that it will update the Password field in tbuUsers, although
I had removed the line that does that in the process of troubleshooting
the 3002 run-time error, just to be sure that that was not part of the
problem. The Password field is just a True/False field which I would
normally set in this routine. I have a form for system administrators
that displays User information: who is logged on when, whether they
have a password, etc which uses this table.

I rely completely on Access security. The tbuUsers table just happens
to have a field for indicating whether or not the User has a password.
MonitorBlankPwds is the field that updates it.

Do you have any ideas about the DBEngine.Createworkspace problem I am
trying to troubleshoot?

Thanks,
Christine


I can't see anything immediately wrong, but you could have broken the code
into smaller chunks to make it more readable. In my example, I have split
the code into two functions - with the aim of creating a list of naughty
users who have left their passwords blank. And since you are looking at
user-level security, you could just go straight to the 'Users' group and
loop through each user - you may have an account which is not in your table.
A couple of other points you could check are that the passwords are not
blindingly obvious like set the same as the login name or set to 'password'.
and finally remember that Access user-level security is easily crackable -
so don't put too much faith in it.

Public Function ListNaughtyUsers() As String

On Error GoTo Err_Handler

Dim grp As DAO.Group
Dim usr As DAO.User
Dim strList As String

Set grp = DBEngine.Workspaces(0).Groups("Users")

For Each usr In grp.Users
If HasBlankPassword(usr.Name) Then
strList = strList & "," & usr.Name
End If
Next usr

If Len(strList) > 1 Then
strList = Mid$(strList, 2)
End If

ListNaughtyUsers = strList

Exit_Handler:
On Error Resume Next
Set usr = Nothing
Set grp = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Private Function HasBlankPassword(strUser As String) As Boolean

On Error Resume Next

Dim wks As DAO.Workspace

Set wks = DBEngine.CreateWorkspace("Jet", strUser, "", dbUseJet)

If Err.Number = 0 Then
HasBlankPassword = True
End If

Set wks = Nothing

End Function

Nov 13 '05 #4

P: n/a

<ct******@stny.rr.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...
Tom,
Yes, tbuUsers is my own table. The intention of the MonitorBlankPwds
routine is that it will update the Password field in tbuUsers, although
I had removed the line that does that in the process of troubleshooting
the 3002 run-time error, just to be sure that that was not part of the
problem. The Password field is just a True/False field which I would
normally set in this routine. I have a form for system administrators
that displays User information: who is logged on when, whether they
have a password, etc which uses this table.

I rely completely on Access security. The tbuUsers table just happens
to have a field for indicating whether or not the User has a password.
MonitorBlankPwds is the field that updates it.

Do you have any ideas about the DBEngine.Createworkspace problem I am
trying to troubleshoot?

Thanks,
Christine



Re-post as my answer doesn't seem to appear on my news server:
I can't see anything immediately wrong, but you could have broken the code
into smaller chunks to make it more readable. In my example, I have split
the code into two functions - with the aim of creating a list of naughty
users who have left their passwords blank. And since you are looking at
user-level security, you could just go straight to the 'Users' group and
loop through each user - you may have an account which is not in your table.
A couple of other points you could check are that the passwords are not
blindingly obvious like set the same as the login name or set to 'password'.
and finally remember that Access user-level security is easily crackable -
so don't put too much faith in it.

Public Function ListNaughtyUsers() As String

On Error GoTo Err_Handler

Dim grp As DAO.Group
Dim usr As DAO.User
Dim strList As String

Set grp = DBEngine.Workspaces(0).Groups("Users")

For Each usr In grp.Users
If HasBlankPassword(usr.Name) Then
strList = strList & "," & usr.Name
End If
Next usr

If Len(strList) > 1 Then
strList = Mid$(strList, 2)
End If

ListNaughtyUsers = strList

Exit_Handler:
On Error Resume Next
Set usr = Nothing
Set grp = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Private Function HasBlankPassword(strUser As String) As Boolean

On Error Resume Next

Dim wks As DAO.Workspace

Set wks = DBEngine.CreateWorkspace("Jet", strUser, "", dbUseJet)

If Err.Number = 0 Then
HasBlankPassword = True
End If

Set wks = Nothing

End Function
Nov 13 '05 #5

P: n/a
ct******@stny.rr.com wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:
While Not rs.EOF

fHasPwd = False

' Attempt to log on to new workspace with blank Pwd
Set wrkNew = DBEngine.CreateWorkspace("NewWks", rs("User"),
"") mlWkSpace = mlWkSpace + 1

' If an error occurred on last statement, then error handler
' will set flag to True because it must have a password.
' Otherwise, able to log on, so Pwd must've been blank.
If Not fHasPwd Then
'Debug.Print "Blank Password:" & rs("User")
fPwdsMissing = True
iCountBlankPwds = iCountBlankPwds + 1
Else
mlBlank = mlBlank + 1
If Not (wrkNew Is Nothing) Then
wrkNew.Close
Set wrkNew = Nothing
End If
End If
rs.MoveNext
Wend

Seems to me that this block:
Else
mlBlank = mlBlank + 1
If Not (wrkNew Is Nothing) Then
wrkNew.Close
Set wrkNew = Nothing
End If
End If
rs.MoveNext
should read:
Else
mlBlank = mlBlank + 1
End If
If Not (wrkNew Is Nothing) Then
wrkNew.Close
Set wrkNew = Nothing
End If
rs.MoveNext
And I think I'd actually change that to:
If Not (wrkNew Is Nothing) Then
wrkNew.Close
End If
Set wrkNew = Nothing


The reason for both these steps is to insure that you are closing
and dstroying the workspace before you create the next one. Your
original code was cleaning up the workstapce only when there *was* a
password, since your cleanup code was only in the ELSE block.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #6

P: n/a
"Brian Wilson" <bw*****@ease.co.uk> wrote in
news:dj*********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com:
A couple of other points you could check are that the passwords
are not blindingly obvious like set the same as the login name or
set to 'password'.


Er, how, exactly, can one read a password in code? So far as I know,
you can't, because if you could, password encryption wouldn't be
much use, would it?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #7

P: n/a
Hi Brian,
Thanks so much for sharing your thoughts and code. Your feedback has
given me a little more to play with here and now I am discovering that
it is not the number of successfully opened workspaces that triggers
the error, as I thought previously, but the number of workspaces that
fail in creation that triggers it. I took your HasBlankPassword
function with slight modification along with a control loop routine as
follows:

Sub TestPwds2()
Dim i As Integer
For i = 1 To 1000
HasBlankPassword "PS" 'blank password
'HasBlankPassword "CT" 'has password
Next
Debug.Print "TestPwds2 done"
End Sub

Function HasBlankPassword(strUser As String) As Boolean
On Error Resume Next
Dim wks As DAO.Workspace

Set wks = DBEngine.CreateWorkspace("Jet", strUser, "", dbUseJet)
If Err.Number = 0 Then
HasBlankPassword = True
Else
If Err.Number <> 3029 Then 'gcErrBadAccntOrPwd Then
Debug.Print "Pwd Error: " & Err.Number & " " & Err.Description
Stop '<<< for debugging
End If
End If
Set wks = Nothing
End Function

I can run TestPwds2 over and over again the way it is and no problem,
it continues to (apparently) be able to keep creating more workspaces.
However, if I uncomment the line "'HasBlankPassword "CT" then I get a
3002 error after 244 times. User 'CT' has a password, so this call is
resulting in failure in CreateWorkspace.

I guess what I am really looking for is a way to clean up after this
failure. I am curious as to whether you can reproduce this problem and
whether it has to do with some version of Jet or Access or what have
you. When I first saw your routine I got so excited because I thought
perhaps the dbUseJet was my missing link, but alas, problem persists.

Thanks again & hope to hear more from you,
Regards,
Christine

Nov 13 '05 #8

P: n/a
Hi David,
Thanks for joining this discussion. I incorporated your suggestion in
my new simplified test case as follows:
----- start code --------------
Sub TestPwds2()
Dim i As Integer
For i = 1 To 1000
'HasBlankPassword "PS" 'blank passowrd
HasBlankPassword "CT" 'has password
Next
Debug.Print "TestPwds2 done"
End Sub
Function HasBlankPassword(strUser As String) As Boolean
On Error Resume Next
Dim wks As DAO.Workspace

Set wks = DBEngine.CreateWorkspace("Jet", strUser, "", dbUseJet)
If Err.Number = 0 Then
HasBlankPassword = True
Else
If Err.Number <> 3029 Then 'gcErrBadAccntOrPwd Then
Debug.Print "Pwd Error: " & Err.Number & " " & Err.Description
Stop '<<< for debugging
End If
End If
If Not (wks Is Nothing) Then
wks.Close
End If
Set wks = Nothing
End Function
-- end code ------------
Unfortunately the clean up you suggest (incorporated above) does not
remedy the problem. I still get my 2003 error on i = 245. See more
explanation of test/results in my previous post to Brian.

Really hope you stay involved,
Thanks again,
Christine

Nov 13 '05 #9

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...
"Brian Wilson" <bw*****@ease.co.uk> wrote in
news:dj*********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com:
A couple of other points you could check are that the passwords
are not blindingly obvious like set the same as the login name or
set to 'password'.


Er, how, exactly, can one read a password in code? So far as I know,
you can't, because if you could, password encryption wouldn't be
much use, would it?

--
David W. Fenton


Of course you can check if a user's password is set to his username or set
to 'password' - just try and login with that username/password combination
and see if it works. You could do that via the UI or using a function like
this:

Function IsPasswordCorrect(strUser As String, strPassword As String) As
Boolean
On Error Resume Next
Dim wks As DAO.Workspace
Set wks = DBEngine.CreateWorkspace("Jet", strUser, strPassword,
dbUseJet)
If Err.Number = 0 Then
IsPasswordCorrect = True
End If
Set wks = Nothing
End Function

So you can 'read a password in code' - that is, you can check whether your
guess is correct. That is not to say I know how to write a function
WhatIsThePassword(strUser As String) As String. But since Access user-level
security has been cracked, perhaps someone else does.
Nov 13 '05 #10

P: n/a
<ct******@stny.rr.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
Hi Brian,
Thanks so much for sharing your thoughts and code. Your feedback has
given me a little more to play with here and now I am discovering that
it is not the number of successfully opened workspaces that triggers
the error, as I thought previously, but the number of workspaces that
fail in creation that triggers it. I took your HasBlankPassword
function with slight modification along with a control loop routine as
follows:

Sub TestPwds2()
Dim i As Integer
For i = 1 To 1000
HasBlankPassword "PS" 'blank password
'HasBlankPassword "CT" 'has password
Next
Debug.Print "TestPwds2 done"
End Sub

Function HasBlankPassword(strUser As String) As Boolean
On Error Resume Next
Dim wks As DAO.Workspace

Set wks = DBEngine.CreateWorkspace("Jet", strUser, "", dbUseJet)
If Err.Number = 0 Then
HasBlankPassword = True
Else
If Err.Number <> 3029 Then 'gcErrBadAccntOrPwd Then
Debug.Print "Pwd Error: " & Err.Number & " " & Err.Description
Stop '<<< for debugging
End If
End If
Set wks = Nothing
End Function

I can run TestPwds2 over and over again the way it is and no problem,
it continues to (apparently) be able to keep creating more workspaces.
However, if I uncomment the line "'HasBlankPassword "CT" then I get a
3002 error after 244 times. User 'CT' has a password, so this call is
resulting in failure in CreateWorkspace.

I guess what I am really looking for is a way to clean up after this
failure. I am curious as to whether you can reproduce this problem and
whether it has to do with some version of Jet or Access or what have
you. When I first saw your routine I got so excited because I thought
perhaps the dbUseJet was my missing link, but alas, problem persists.

Thanks again & hope to hear more from you,
Regards,
Christine

Hi Christine
I tested your example and it works perfectly on my system. Access XP
running on Windows XP machine with Pentium 4 2.6 GHz 512MB RAM. I then
tried checking 3 accounts (with password, without password and non-existant
account) and also increasing the loop by a factor of 10. 10,000 runs takes
about 15 seconds to complete - but I am unable to replicate the error:

Sub TestPwds2()
Dim i As Integer
For i = 1 To 10000
HasBlankPassword "Brian" 'blank password
HasBlankPassword "Test" 'has password
HasBlankPassword "Rubbish" 'account doesn't exist
Next
Debug.Print "TestPwds2 done"
End Sub

Perhaps soemone else can produce this. Do you know if you have the latest
Jet service pack?
Nov 13 '05 #11

P: n/a
Hi Brian,
Thanks for trying to reproduce my problem. You did use my version of
HasBlankPassword - right? The one with the Stop in it? Other wise you
would not see the problem - other than the result from HasBlankPassword
would start to be always false after 255 calls for a User who does have
a password. That might be a dumb question, but in fact I made that very
mistake yesterday when I first got your HasBlankPassword.

I am trying to confirm whether I am using the latest Jet service pack.
I have msjet40.dll version 4.0.8618 in C:\Windows\SYstem32. According
to http://support.microsoft.com/kb/239114/ this is 'Security Bulletin
MS04-014'. Since Service Pack 8 had version 4.0.8015.0, I assume that
what I have incorporates SP8. I did notice that I also have msjet35.dll
in C:\Windows\System32. I assume I am using the msjet40.dll and not the
msjet35.dll. Is there any way to confirm this through code?

Thanks again for sharing your expertise.
Christine

Nov 13 '05 #12

P: n/a
"Brian Wilson" <bw*****@ease.co.uk> wrote in
news:dj**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
[]
Er, how, exactly, can one read a password in code? So far as I
know, you can't, because if you could, password encryption
wouldn't be much use, would it?


Of course you can check if a user's password is set to his
username or set to 'password' - just try and login with that
username/password combination and see if it works. You could do
that via the UI or using a function like this:

Function IsPasswordCorrect(strUser As String, strPassword As
String) As Boolean
On Error Resume Next
Dim wks As DAO.Workspace
Set wks = DBEngine.CreateWorkspace("Jet", strUser,
strPassword,
dbUseJet)
If Err.Number = 0 Then
IsPasswordCorrect = True
End If
Set wks = Nothing
End Function

So you can 'read a password in code' - that is, you can check
whether your guess is correct. . . .


These are two completely distinct logical processes. You can't read
a password, but you can test if a password value is correct, as you
point out. Since you're looking for a particular password (a blank
one or the word "password", though you'd need to check all case
variations), you are just trying specific passwords and seeing if
they succeed.

That is not at all the same thing as "reading a password."
. . . That is not to say I know how to
write a function WhatIsThePassword(strUser As String) As String.
But since Access user-level security has been cracked, perhaps
someone else does.


You can't do it using DAO without code that cracks the encryption.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #13

P: n/a
ct******@stny.rr.com wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
Thanks for trying to reproduce my problem. You did use my version
of HasBlankPassword - right? The one with the Stop in it? Other
wise you would not see the problem - other than the result from
HasBlankPassword would start to be always false after 255 calls
for a User who does have a password. That might be a dumb
question, but in fact I made that very mistake yesterday when I
first got your HasBlankPassword.

I am trying to confirm whether I am using the latest Jet service
pack. I have msjet40.dll version 4.0.8618 in C:\Windows\SYstem32.
According to http://support.microsoft.com/kb/239114/ this is
'Security Bulletin MS04-014'. Since Service Pack 8 had version
4.0.8015.0, I assume that what I have incorporates SP8. I did
notice that I also have msjet35.dll in C:\Windows\System32. I
assume I am using the msjet40.dll and not the msjet35.dll. Is
there any way to confirm this through code?


If you can't get it fixed, you can always do it the way this
function does it, by actually querying the MSysAccounts table in the
System.mdw file.

OK, stepping aside for a minute, it turns out that there was a
change in the way passwords are stored from Jet 3.5 to Jet 4.0. In
Jet 3.5, they were stored in a Binary 255 field. Because of Unicode,
this was changed to Binary 510. The result of that is that the value
being tested is not the same.

First, let me present the methods for Jet 3.5:

Public Function ListUsersWithoutPassword() As String
' works for Jet 3.5 ONLY
Dim strSystemDB As String
Dim db As DAO.Database
Dim strSQL As String
Dim rs As DAO.Recordset
Dim strTemp As String

strSystemDB = DBEngine.SystemDB
Set db = DBEngine.OpenDatabase(strSystemDB)
strSQL = "SELECT MSysAccounts.Name FROM MSysAccounts"
strSQL = strSQL & " WHERE MSysAccounts.FGroup=0"
strSQL = strSQL & " AND MSysAccounts.Password='iR*giR*g'"
strSQL = strSQL & " ORDER BY MSysAccounts.Name;"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount <> 0 Then
rs.MoveFirst
Do While Not rs.EOF
strTemp = strTemp & ", " & rs!Name
rs.MoveNext
Loop
End If
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
ListUsersWithoutPassword = Mid(strTemp, 3)
End Function

You could use the same approach to test for a specific username:

Public Function HasBlankPassword(strUser As String) As Boolean
' works for Jet 3.5 ONLY
Dim strSystemDB As String
Dim db As DAO.Database
Dim strSQL As String
Dim rs As DAO.Recordset

strSystemDB = DBEngine.SystemDB
Set db = DBEngine.OpenDatabase(strSystemDB)
strSQL = "SELECT MSysAccounts.Name FROM MSysAccounts"
strSQL = strSQL & " WHERE MSysAccounts.Name='" & strUser & "'"
strSQL = strSQL & " AND MSysAccounts.Password='iR*giR*g';"
Set rs = db.OpenRecordset(strSQL)
HasBlankPassword = (rs.RecordCount <> 0)
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Function

Now, how to make it work in Jet 4.0? Well, I was unable to coerce
the password field values into anything that could be stored as a
string or byte value in a VBA module. But, I discovered that I could
join the MSySAccounts table to itself on the Password field and get
valid results. '
So, the method I used was to do a MakeTable to create a table that
had one record that stored the blank password value. I happened to
do this in a System.MDW file, which makes it easy to link to the
MSysAccounts file.

The result is that this SQL, using a 1-record table
(tmpBlankPassword) with the blank password stored in it, returns the
usernames with blank passwords:

SELECT MSysAccounts.Name
FROM MSysAccounts INNER JOIN tmpBlankPassword
ON MSysAccounts.Password = tmpBlankPassword.Password;

So, with this method, I don't have to know the blank password, I
just have to be able to store it so that it can be compared to the
values in your real MSysAccounts table, and creating this temp table
in your System.mdw is a one-time task. What I did was work with a
copy of the real System.mdw after I'd created a user with a blank
password, then after creating the temp table storing the blank
password value, I exported it to the real System.mdw. Then I could
easily rewrite the functions above to use the join to determine
which users had a blank password.

It's not as pretty as using the workspace test, but it's far simpler
and it will definitely be much more efficient.

Now, what about using undocumented system tables? While I have never
thought it's a good idea to rely on undocumented features of the
system tables in Access, I think this is one that will continue to
work for Jet user-level security. See this from MichKa for a
justification for this:

Can you rely on system tables? How about DAO?
http://www.trigeminal.com/usenet/usenet017.asp

MichKa's point there is that MS has a policy of maintaining complete
backward compatibility, so that anything that works in a previous
version of Jet has to work in a later version. Given that Access 12
is going to a different data format and a different security model,
it's even more unlikely that the structure of Jet system tables will
ever be changed. So, I think this method is pretty safe.

The only thing that might happen is that the encrytion could change,
which would alter the value stored for a blank password, but it
looks like Jet uses a consistent value for all blank passwords
within a version of Jet, so the temp table storing that value should
work, though the actually blank password value could itself be
different in different future versions of Jet.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #14

P: n/a
> These are two completely distinct logical processes. You can't read
a password, but you can test if a password value is correct, as you
point out. Since you're looking for a particular password (a blank
one or the word "password", though you'd need to check all case
variations), you are just trying specific passwords and seeing if
they succeed.

That is not at all the same thing as "reading a password."
. . . That is not to say I know how to
write a function WhatIsThePassword(strUser As String) As String.
But since Access user-level security has been cracked, perhaps
someone else does.


You can't do it using DAO without code that cracks the encryption.

--
David W. Fenton


'reading a password' was not my initial choice of words and when I did use
them I clarified what I meant. I pointed out that the OP could check
whether the password was set to the same as the username or to something
like "password" and it was this that seemed to prompt you to ask how you
could read a password in code - although I'm not sure why as the whole
thread had been about checking whether a guessed password (blank) was
correct.
Nov 13 '05 #15

P: n/a
<ct******@stny.rr.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Hi Brian,
Thanks for trying to reproduce my problem. You did use my version of
HasBlankPassword - right? The one with the Stop in it? Other wise you
would not see the problem - other than the result from HasBlankPassword
would start to be always false after 255 calls for a User who does have
a password. That might be a dumb question, but in fact I made that very
mistake yesterday when I first got your HasBlankPassword.

I am trying to confirm whether I am using the latest Jet service pack.
I have msjet40.dll version 4.0.8618 in C:\Windows\SYstem32. According
to http://support.microsoft.com/kb/239114/ this is 'Security Bulletin
MS04-014'. Since Service Pack 8 had version 4.0.8015.0, I assume that
what I have incorporates SP8. I did notice that I also have msjet35.dll
in C:\Windows\System32. I assume I am using the msjet40.dll and not the
msjet35.dll. Is there any way to confirm this through code?

Thanks again for sharing your expertise.
Christine

My 'expertise' is not too great in this particular area - I just tend to
click the latest downloads.
Yes, I did use your version but could not replicate the problem - and no-one
else seems to have answered one way or the other. Perhaps David's
suggestion will be of some use.
Nov 13 '05 #16

P: n/a
I think investigations and work like this has gone one "forever". For
the New Access, we'll have to make a new wheel.

http://blogs.msdn.com/access/archive...19/482845.aspx

Ananda

ct******@stny.rr.com wrote:
I am creating a workspace with a blank password for different Users in
order to find out which Users do not have passwords. This works just

Nov 13 '05 #17

P: n/a
Brian,
Thanks for confirming that you were using my version. I've tried it on
another PC with Access 2000 and have the same problem. Over the
weekend, I will have access to a PC with Access XP and see what I get
there.
Thanks for hanging in there with me,
Christine

Nov 13 '05 #18

P: n/a
Wow, I didn't realize that an mdw is really just an mdb with different
extension. I think your approach is really cool and has prompted me to
take a closer look at my mdw, which I have never done before. I guess I
could even incorporate adding a temp User with blank password and then
using an inner join on MSysAccounts with the temp User to check for
passwords, and then get rid of temp User, just to keep the mdw 'clean'
and not have an extra table lying around.

It's a little scary to me that anyone can just change the extension and
look at the tables of the mdw as I did. On the other hand, the idea of
Access losing all User level security as implied by the reference in
Amanda's post
(http://blogs.msdn.com/access/archive...19/482845.aspx) is
devastating to me!!

Thanks for your input, David, and your thorough explanation.
Christine

Nov 13 '05 #19

P: n/a
Oh my God, I can't believe they will get rid of User Level Security all
together. That would be devastating!!!!

Thanks for bringing the blog to my attention,
Christine

Nov 13 '05 #20

P: n/a
ct******@stny.rr.com wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:
Wow, I didn't realize that an mdw is really just an mdb with
different extension. I think your approach is really cool and has
prompted me to take a closer look at my mdw, which I have never
done before. I guess I could even incorporate adding a temp User
with blank password and then using an inner join on MSysAccounts
with the temp User to check for passwords, and then get rid of
temp User, just to keep the mdw 'clean' and not have an extra
table lying around.

It's a little scary to me that anyone can just change the
extension and look at the tables of the mdw as I did. . . .
Change the extension? You don't have to do that -- "Workgroup Files"
is one of the choices in the File Types dropdown of Access's FILE
OPEN dialog. You just can't open it while it's in use.
. . . On the other
hand, the idea of Access losing all User level security as implied
by the reference in Amanda's post
(http://blogs.msdn.com/access/archive...19/482845.aspx) is
devastating to me!!
I'm not certain if it's a big loss or not. I tend not to use
user-level security for anything but identifying users. I generally
don't actually apply different permissions on database objects or
tables. Since Windows logon can just as easily be used to identify a
user, I don't see much or loss. And it also means that the UI for
managing users can be built into the app and you don't have to teach
someone how to manage users (the default UI for user-level security
sucks, in my opinion, and is extremely confusing).
Thanks for your input, David, and your thorough explanation.


Just passing along information that was probably passed along to me
by someone else in this newsgroup.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.