dr_dudd
asked on
Class does not support Automation or does not support expected interface
I have a dB in SQL Server with Access 2000 front end. All is well, until I recently added some more users in SQL Server. Now those new users get this error when the application starts, specifically at a line that reads "Set cnnConnection = CurrentProject.Connection"
One difference is that the users affected are in a new AD child domain from previous users (although not all users in the new child domain exhibit teh fault).
Everyone runs the application via a single Terminal Server machine, so it cannot be a problem of different s/w as they are all running the same thing.
Anyone have any ideas?
Thanks. Dr_Dudd.
One difference is that the users affected are in a new AD child domain from previous users (although not all users in the new child domain exhibit teh fault).
Everyone runs the application via a single Terminal Server machine, so it cannot be a problem of different s/w as they are all running the same thing.
Anyone have any ideas?
Thanks. Dr_Dudd.
ASKER
Authentication is by AD username. I cannot find any differences in the user details between those that can use the application through TS and those that cannot. Also I have managed to log in locally to a PC here as one of the problem users and run the application locally - it has the same problem.
I am fairly certain that this is a rights issue to do with the AD user or SQL Server roles, but I can't find any differences between those that work and those that don't.
I am fairly certain that this is a rights issue to do with the AD user or SQL Server roles, but I can't find any differences between those that work and those that don't.
'Class does not support Automation or does not support expected interface'
It sounds like an application issue, I really don't think it has anything to do with the SQL Server role.
In fact since it hasn't got past the Set cnnConnection = CurrentProject.Connection line, it hasn't even connected to the database yet.
So on the non functional users local PC, after you hit debug, then hit stop, then press F2, does it brnig an object viewer?
It sounds like an application issue, I really don't think it has anything to do with the SQL Server role.
In fact since it hasn't got past the Set cnnConnection = CurrentProject.Connection line, it hasn't even connected to the database yet.
So on the non functional users local PC, after you hit debug, then hit stop, then press F2, does it brnig an object viewer?
ASKER
You are on to something there. As you say it is not connecting to the SQL Server database, it just happens that the 'Set cnnConnection = CurrentProject.Connection'
It looks like the user is not granted dB access. However, in SQL Server the user has 'Database Access' permission and all the same group memberships as other users that work ok.
It looks like the user is not granted dB access. However, in SQL Server the user has 'Database Access' permission and all the same group memberships as other users that work ok.
error initialising provider... hmmmm
try this:
1. Create a new text file
2. Rename the extension to .UDL
3. Double click it and fill in the connecton parameters
4. Hit Test
Try it for the functional and non functional users.
If you see the same behaviour then that narrows it down to the ole-db provider (ie drivers)
try this:
1. Create a new text file
2. Rename the extension to .UDL
3. Double click it and fill in the connecton parameters
4. Hit Test
Try it for the functional and non functional users.
If you see the same behaviour then that narrows it down to the ole-db provider (ie drivers)
and if thats the issue you need to register some DLL's in these folders:
C:\Program Files\Common Files\System\Ole DB
try registering these files and see if it fixes it:
msdasql.dll
oledb32.dll
sqloledb.dll
if that doesn't work then try registring this file:
C:\Program Files\Common Files\System\ado\msado15.d
C:\Program Files\Common Files\System\Ole DB
try registering these files and see if it fixes it:
msdasql.dll
oledb32.dll
sqloledb.dll
if that doesn't work then try registring this file:
C:\Program Files\Common Files\System\ado\msado15.d
PS regsvr32 is what you want to register files
ASKER
The plot thickens...
If I log in to the PC as user domain\nogood (the user that does not work) and create the UDL file, set the provider as SQL Server and fill in the details when I get to select the database on the server it says "Invalid login for domain\nogood" then a second error shows "Unable to retrieve catalog". So I can't get a connection to the SQL Server to get a list of dB's on the server.
If I log in to the PC as user domain\good (a user that works) then I can fill in all the connection details, select the dB and test it and it works fine.
If I log in to the PC as user domain\nogood (the user that does not work) and create the UDL file, set the provider as SQL Server and fill in the details when I get to select the database on the server it says "Invalid login for domain\nogood" then a second error shows "Unable to retrieve catalog". So I can't get a connection to the SQL Server to get a list of dB's on the server.
If I log in to the PC as user domain\good (a user that works) then I can fill in all the connection details, select the dB and test it and it works fine.
Do you have access to SQL Server Query Analyzer?
Cause the next thing I would do is try logging in as domain\nogood through Query Analyzer.
FYI, all that stuff I just posted prior to this does not apply as you didn't get a provider error.
Its possible that the nogoods (perhaps that name will stick with the people in real life ;) ) are members of windows groups that are denied access to SQL Server, so somehow it has inherited a 'deny' through windows groups?
But you're absolutely certain that the windows groups for these people match (as far as windows group membership) as well as their SQL Server memberships?
PS its often easier to add all the windows users to a single windows group then just add that group to the server.... this may complicate or simplify the situation for you, not sure.
Cause the next thing I would do is try logging in as domain\nogood through Query Analyzer.
FYI, all that stuff I just posted prior to this does not apply as you didn't get a provider error.
Its possible that the nogoods (perhaps that name will stick with the people in real life ;) ) are members of windows groups that are denied access to SQL Server, so somehow it has inherited a 'deny' through windows groups?
But you're absolutely certain that the windows groups for these people match (as far as windows group membership) as well as their SQL Server memberships?
PS its often easier to add all the windows users to a single windows group then just add that group to the server.... this may complicate or simplify the situation for you, not sure.
Gotta crash, I'm in Australia, good luck, maybe you'll sort it by tommorow!
ASKER
I can't log in to Query Analyser as the user as the only option are for NT or SQL Server authentication. I cannot log in to the Windows 2003 Server as users are not allowed to log in directly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the long delay in a response.
OK, I managed to install SQL Server tools on my PC and log in as the domain\nogood user. When I run QA and try to connect to the SQL Server using NT authentication I get this error....
Server: Msg 18456, level 16, state 1
[Microsoft][ODBC SQL Server driver][SQL Server] login failed for use 'domain\nogood'
I can't see any difference in access rights between a user that exhibits this problem, from a user that works fine. I have logged in to both on my PC, so it can't be a driver version issue.
OK, I managed to install SQL Server tools on my PC and log in as the domain\nogood user. When I run QA and try to connect to the SQL Server using NT authentication I get this error....
Server: Msg 18456, level 16, state 1
[Microsoft][ODBC SQL Server driver][SQL Server] login failed for use 'domain\nogood'
I can't see any difference in access rights between a user that exhibits this problem, from a user that works fine. I have logged in to both on my PC, so it can't be a driver version issue.
OK now its down to stabs in the dark
Rereading your original post, it seems the only difference could possbily be in the windows groups side of things?
>> One difference is that the users affected are in a new AD child domain from previous users (although not all users in the new child domain exhibit teh fault).
when you say access rights (in your previous post) does that include windows groups?
Rereading your original post, it seems the only difference could possbily be in the windows groups side of things?
>> One difference is that the users affected are in a new AD child domain from previous users (although not all users in the new child domain exhibit teh fault).
when you say access rights (in your previous post) does that include windows groups?
ASKER
The problem has now spread to new users in another child domain. Until Tuesday this week all users in the domain worked fine, but I have added two new ones and neither work! I have created a new user in the original problem child domain and it works fine. If I delete the domain user from SQL Server and re-create it it still does not work. I can't delete a domain user from AD as these are real users.
The annoying thing is that I can't seem to create a test user that doesn't work so I can't test things other than on a real user's account.
I could not find any differences in the permissions or group membership between users that work, and those that don't.
There is a reference on the MS websit about a bug that this error happens if there is a semi-colon in the user's password. Of course there are not in my case.
The annoying thing is that I can't seem to create a test user that doesn't work so I can't test things other than on a real user's account.
I could not find any differences in the permissions or group membership between users that work, and those that don't.
There is a reference on the MS websit about a bug that this error happens if there is a semi-colon in the user's password. Of course there are not in my case.
ASKER
I managed to get some of the users working. I have been using a VBA program that calls the system stored procedures to grant dB access, and make them members of the roles I tell it. So I deleted the users that did not work and re-created them using the following from QA...
use mydB
DECLARE @AFMUser NVARCHAR(30)
SET @AFMUser = 'gatwick\simon'
EXEC sp_grantlogin @AFMUser
EXEC sp_grantdbaccess @AFMUser
EXEC sp_addrolemember @rolename='AFM_Operations'
go
Changing the SET line for each user. Now some can log in.
BUT: When I create the users this way and then view users in SQL Server EM they all have the correct 'Name', such as 'Gatwick\Simon' but some of them have no 'Login Name' and others have a 'Login Name' the same as their 'Name'.
use mydB
DECLARE @AFMUser NVARCHAR(30)
SET @AFMUser = 'gatwick\simon'
EXEC sp_grantlogin @AFMUser
EXEC sp_grantdbaccess @AFMUser
EXEC sp_addrolemember @rolename='AFM_Operations'
go
Changing the SET line for each user. Now some can log in.
BUT: When I create the users this way and then view users in SQL Server EM they all have the correct 'Name', such as 'Gatwick\Simon' but some of them have no 'Login Name' and others have a 'Login Name' the same as their 'Name'.
ASKER
I have accepted this answer as it correctly lead me to answer for the original question as to why I was getting the error 'Class does not support Automation or does not support expected interface' - and the reason is that the user does not have login rights to the database.
Thanks nmcdermaid for your help.
I now have to work out why the user doesn't have access to the dB but that is a different question altogether...
Thanks nmcdermaid for your help.
I now have to work out why the user doesn't have access to the dB but that is a different question altogether...
Glad to help, I think it was getting to the stage where I couldn't help via newsgroup anymore. Good luck finding an answer.
What happens if you run it under an administrator user?
Is it possiblt that the user profile is slightly different on the terminal server?
If you open the ADP (I assume), go to the VBA editor and press F2, can you find all the libraries that you expect to see there?