June 5, 2006 at 3:43 am
Migrating SQL 2000 databases from web server to new dedicated SQL 2000 Server.
PROBLEM: OPENDATASOURCE(Microsoft.Jet.OLEDB.4.0) fails when called from one server to the other.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\server-ip\folder\Accessdatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Old installation still running and QA test WORKS on both servers, but ONLY IF QA is connected to local.
Test FAILS when QA is connected to the other server.
--TEST QUERY:
select *
from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=\\server-ip\folder\Accessdatabase.mdb;Jet OLEDB: Database Password=DBPASSWORD')... tablename
Please help.
Regards, Ian
June 5, 2006 at 11:07 am
the error is a bit self explanatory. Your ACCESS DB is in use by Another process.(does someone has Access open with that DB?)
The Fact the in QA works when local indicates that only QA is touching the DB. Also make sure that your SQL Server Service account has access to that share!
* Noel
June 5, 2006 at 11:26 am
Thank you Noel however this is not the case.
DisallowAdHocAccess = 0 and all patent accounts have access, otherwise it would not work at all i.e. when QA is connected to SQL Server on local.
The Access database is on an entirely different box/share.
Even tried running remote SQL Server on a suitable network account, no joy.
Problem is plain. OPENDATASOURCE stops working when the call comes from another box.
I.E:
QA on BOX1 connected to SQL Server on local (win authentication) = OK
QA on BOX1 connected to SQL Server on BOX2 (win authentication) = FAIL
Regards,
Ian
June 5, 2006 at 11:34 am
Ok lets narrow down the issue.
1. Do you have set up an mdw file for the MS Access DB?
2. Are you sure nobody else has the DB Opened?
3. Are there any ldb files on the access DB directory that were not deleted?
* Noel
June 5, 2006 at 11:40 am
Hi Noel,
1. NO - as seen from the provider string, this is a database password, not a workgroup.
2. YES - nobody else has the DB open.
3. NO - there are no unclosed ldb lock files.
June 5, 2006 at 11:54 am
Perhaps I should have mentioned...
These tests in QA replicate a real problem in production, when said OPENDATASOURCE had been working for a year in my application SQL report modules, using token impersonation and SQL Server on the same box as the web applications.
Unless the Access databases became corrupt or a table was locked by someone opening it in design view (unlikely in production environment) it worked fine UNTIL I migrated SQL Server backends to the new (remote) box.
Ian
June 6, 2006 at 3:36 am
Sounds like the SQL Box's MSSQLSERVER Service account does not have enough permissions to the \\server-ip\folder\Accessdatabase.mdb.
Are you using the Local System account? If so grant permissions on the the folder/share to the SQL Server machine name (Computer) account and the Local System account will be granted access to the MDB.
Andy
June 6, 2006 at 7:41 am
Hi Andy,
Previously tried SQL Server 2000 (itself) on privilaged service account, to no avail.
Probably not applicable however SQLSERVERAGENT (on both boxes) runs on domain service account with read/write to the AccessDatabase and for testing, local Windows admin rights on SQL Server boxes.
Same domain accounts (service account and my own) and windows authentication are used in all tests although SQL Servers are in dual authentication mode.
OPENDATASOURCE TEST RESULTS:
1 - QA on BOX1 connected to SQL Server on BOX1 calling AccessDatabase on BOX3 = Success.
2 - QA on BOX1 connected to SQL Server on BOX2 calling AccessDatabase on BOX3 = Failure.
3 - QA on BOX2 connected to SQL Server on BOX2 calling AccessDatabase on BOX3 = Success.
4 - QA on BOX2 connected to SQL Server on BOX1 calling AccessDatabase on BOX3 = Failure.
Permissions on BOx3 are beyond my jurisdiction however I shall look into your suggestion.
Thanks,
Ian
June 7, 2006 at 3:11 am
Hi Andy and Noel,
Really appreciated your suggestions.
New test interesting?
QA on BOX2 connected to SQL Server on BOX1 calling AccessDatabase on BOX1 = Success.
Very puzzling.
Best regards,
Ian
June 7, 2006 at 6:40 pm
Hi All,
Don't think this is a TSQL matter any longer and not sure how to move it to the correct forum.
You were both right about permissions.
Tests show that user credentials from remote server are not being passed through.
The description for Event ID ( 540 ) in Source ( Security ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: , , (0x0,0xC81584), 3, NtLmSsp , NTLM, UKRT1-WEB606, {00000000-0000-0000-0000-000000000000}.
The description for Event ID ( 538 ) in Source ( Security ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: ANONYMOUS LOGON, NT AUTHORITY, (0x0,0xC81584), 3.
Don't know any more settings to try so I'm thinking service pacs and patches?
Again, many thanks.
Ian
June 10, 2006 at 5:45 pm
The answer was here all the time, double hops not permitted.
http://qa.sqlservercentral.com/columnists/bkelley/qa2k_1.asp
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply