Linked Server Access problems
-
Hi,
I'm having a problem accessing a linked server (MS Access) from MSDE whenever the client is not on the same computer as MSDE.
I have an Access 2000 project which connects to MSDE 2000 backend. I added a linked server to MSDE which pointed to another secured Acc2000 DB and updated the registry to point to the correct workgroup file. In my Access project, I have a combo box whose rowsource is a query based on a table in the linked server (SELECT * FROM FSW_DB...tblGSPChanges).
Everything works fine when the Acc2000 project and MSDE are on my own machine (the Acc2000 DB is on a different machine) and the combo box pulls its rows from the Acc2000 DB. I then moved the MSDE database to a server machine and got everything working there as long as I was logged into and working on the server. However, when I use my local copy of the Acc2000 project to connect to the MSDE on the server, the combo box cannot query the linked server (Acc2000 DB). I also can't do it through the osql utility running on my local machine either. When you click on the combo box dropdown, it returns the error (OLE DB Error trace [Non-interface error: CoCreate of DSO for Microsoft.Jet.OLEDB.4.0 returned 0x80040154]. Same error from osql.
Once again, everything works fine when the project file and the MSDE database are running on the same machine. It is only when the project is running on a different machine than MSDE that the problem occurs. The project never has any trouble accessing the MSDE database data, just the linked server data.
Please help!
-
What MDAC version are you using?
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
MDAC 2.8
-
This behavior your describing has my SQL Server Bat-senses tingling in the direction of an authentication issue.
Questions -
Do the users that are supposed to have access rights to the MSDE system have the appropriate rights set on that server system?
Are there SQL Server accounts defined and being used to authenticate to the server in the configuration that is working?
Is the MSDE configured for "mixed mode" authentication.
What is my reasoning -
If client and server are on the same system, and the user is the administrator of the system, then the application will likely work fine because system admins usually have admin rights to a MSDE or SQL server that resides on that system.
If the client is on one system, and the MSDE is on another system, the only way the client system will be able to access the MSDE is if the appropriate rights are in place on the MSDE server.
What would I do -
Set yourself up as an administrator for the server system where you are absolutely certain that you have full admin rights to that box based on your NT network domain account (ntdomain\username). Verify your rights by making sure you can see the C$ share from another system on the network.
Next, try to connect to the server via EM or QA on another network workstation.
- or -
Use OSQL to run a simple query against that server from another network workstation using a Trusted Connection (/E switch).
If you can now connect to the remote server, then you have won half the battle because you know that it was a rights issue and now it is a matter of tweaking the MSDE rights for the appropriate NT network domain accounts to be able to see the necessary database objects for your application.
If it doesn't work, try to configure the MSDE server for mixed mode authentication, set up a SQL server account in the MSDE server that has rights to the required database(s) and try to connect to it using the SQL server account authentication.
If this works, then you have won half the battle again because this still identifies that your issue is with the NT network domain rights to that server.
One last thing that sometimes seemed to help. Install the SQL Server Client Connectivity tools (only) on those desktops that need to connect to the MSDE server and configure the Client Connectivity with an appropriate Alias to point to the server via the appropriate protocol (named pipes, tcpip, etc). Quite often this was the quick fix I have used for those situations where a client could not connect to a SQL server or MSDE system on the network.
Gosh... I hope this makes sense.
Funny thing about people and their computers...
The computer is completing millions of processes every second, but the user still thinks it is too slow.
"I will not be taken alive!" - S. Hussein -
My shot in the dark.... Perhaps MSDE is unable to access a required file. Can it access both the local Jet dlls (not part of MDAC 2.8) and remote access db files? What account is used by MSDE? What account does your linked server map to? Do you have Microsoft Jet 4.0 Service Pack 4 (SP4) or later?
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
Thanks for the replies so far...
I think I will add my domain user account to the server admin group, and then try again.
The MSDE installation is configured for Windows auth only and the users I have tested have been granted login rights to the MSDE server and access rights to the appropriate database. I have also created a linked server login for all logons (NULL), that maps the requesting logon to the appropriate Jet logon. There is no problem connecting to the MSDE server or to the database within. All data shows up in the project's forms. Only when the query to the linked server is executed does the error occur. The error seems to say that it can't start up the Jet database engine for some reason.
It does seem like some kind of permission problem, but I just can't figure out what it is. The MSDE installation is running as a local administrator, so it should have full access to the servers resources. Why when a non-administrator account logs in to MSDE would that cause problems accessing the linked server (especially since I mapped all logons to the linked server logon)?
Let you know how giving myself administrator privileges on the server works out.
-
You could try running MSDE using the domain admin account (or your account since it worked when you ran it logged in) just to see if it would work. MSDE probably needs domain access -> domain account. I don't think MSDE uses the users login to get access to the dlls or dbs files. It'd be like using a users login to get access to authenticate the users login.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
Well, I added my domain account to the server's Administrators group and everything works perfect. No more problems accessing the linked server when I'm accessing the MSDE from a different machine. As soon as I take my domain account out of the Administrators group, I can no longer access the linked server and I get that strange error saying it couldn't start the Jet engine. Why would it matter whether or not I am an administrator? The MSDE process is running as an administrator, you sure wouldn't think the user would need to be.
Any other ideas/suggestions?
Thanks.
-
Maybe the "Allow InProcess" needs enabled. See "Configuring OLE DB Providers for Distributed Queries" in BOL.
From Q280106 - "You can set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then select the Allow InProcess check box."
Also, from "External Data and Transact-SQL" in BOL - "Columns with large object data types (such as text, ntext, or image) cannot be referenced in update or insert operations if the provider is instantiated outside the Microsoft® SQL Server™ 2000 process (provider option AllowInProcess is 0). For more information, see Configuring OLE DB Providers for Distributed Queries."
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
I just found a QOD that relates.
http://www.sqlservercentral.com/testcenter/QOD.asp
The date for the question is 9/24/3003.
The answer links to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/aghtconfig_15bm.asp which indicates that Allow InProcess should be checked. It's not the same provider, but....
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
Now the problem is figuring out how to set AllowInProcess to True for the Jet OLEDB provider. I don't have SQL Server (and thus no Enterprise Manager), only MSDE. I found some references to the registry key HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer/Providers where you were supposed to be able to add/modify the AllowInProcess key, but I don't seem to have any Providers key. Maybe I could add it, then add the ProgID for the Jet 4.0 provider, then add the AllowInProcess key?
So let's move past that, and assume we are running it out of process. So if it runs it out of process, will it run as localsystem or what account will the Jet Provider be started under? What permissions would I have to give to this account to enable it to start the Jet Provider? I tried adding a bunch of local security permissions (on the server) to all authenticated users but that didn't help. I think I'll go back and try to give the localsystem account a bunch of permissions.
Any idea what account MSDE will try to start up the the out-of-process Jet Provider as?
Thanks again.
-
I don't know what account it would use. Perhaps the security event log will have entries that will indicate what account is having access problems. It might also tell what type of access problem (e.g., registry, file, authentication, ...). Also, the Allow InProcess false is the default. Perhaps there is a system stored procedure that can set it on?
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
Hang with me because I don't really have any hands-on experience with MSDE. All I know is MSDE uses the same engine as SQL server more or less.
If this were a Full SQL Server implementation, then normally I would create user accounts in SQL Server based on the domain user accounts for a trusted domain of that server. Then I would set the rights for that account that the user needed to move forward. No twiddling with the NT rights for the system is usually necessary unless I want to make special group accounts on that system for managing rights. In those cases I would just create a SQL Server user account based on the system group account and assign the appropriate database rights to that account.
Can't you do that in MSDE using Enterprise Manager? Just wondering. Like I said, I usually work with full SQL Server systems and MSDE is not something I have dealt with yet.
Funny thing about people and their computers...
The computer is completing millions of processes every second, but the user still thinks it is too slow.
Edited by - SQLGutter on 11/17/2003 4:36:15 PM
"I will not be taken alive!" - S. Hussein -
Seems like it should be that easy, doesn't it? I have granted MSDE login to a couple user domain accounts (domain\user) and given them access to a couple databases. No problem logging in to MSDE and accessing the MSDE databases and data they have been granted access to. The problem arises when they request data from an MS Access linked server (using Microsoft.Jet.OLEDB.4.0 provider). Unless they are given Administrator rights on the server where the MSDE installation is, an error occurs and they are unable to access the MS Access DB. It seems obvious that the Administrator account has some special permission that normal domain users do not. I have tried giving the normal domain users rights (on the MSDE server machine) like "Act as part of operating system" and nearly all other local rights. Still won't work unless the user is given full Administrator rights. Then everything works fine. Something about starting up the Jet provider as an out-of-process process doesn't work. Seems like the account that MSDE uses to start up the Jet provider must be the logged in user? What special permissions (rights) do they then need to be able to start up the Jet provider on the server? And if I could figure out how to start the Jet provider as an in-process process without Enterprise Manager, that would probably work, too.
Tough one. I've looked everywhere, but I haven't really found anything beyond the help provided here.
Thanks.
-
This refers to v7 but should work,
"To mark a provider as in-process, use one of the following two processes:
Create a linked server in SQL Server 7.0 which specifies the provider. In the SQL Enterprise Manager, click Properties for the new linked server, choose Options, and then select Allow InProcess.
Add the provider ProgID to a key in the register at: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers and add a DWORD value under that key which is AllowInProcess with a value of 1."
I took a look at one of our servers (SQL Server 2000 standard edition):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.Jet.OLEDB.4.0
AllowInProcess REG_DWORD 1
Might be worth a try.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply