Re: Linked server from SQL Server to Access db on network share?

• Previous message: Jim Young: "Re: 2 GB for each db and 5 db for each instance"
• Next in thread: McK: "Re: Linked server from SQL Server to Access db on network share?"
• Reply: McK: "Re: Linked server from SQL Server to Access db on network share?"
• Messages sorted by: [ date ] [ thread ]

Date: Fri, 9 Apr 2004 13:31:05 -0700

Server: Msg 7399, Level 16, State 1 --- Let the comiseration begin...
I'm experiencing the same issue and have checked everything in Rob's list and it all checks out, but provides no insight into the problem.
What it looks like is this: SQL can create a linked server to a file on a remote share, but you can't query against it using Query Analyzer from any system other than the one hosting SQL... lotta good that does...
What follows is a VERY long, detailed explanation of all the testing I did before I threw my hands up in frustration and swore never again would I submit my soul to the torments of working in a heterogeneous data environment... then came to my senses and realized I would probably just have to copy all the data from the Access database to tables into my SQL database every time I needed to query across that and another data source... Someone, please, tell me there is a better way.

Background:
I have inherited an old application to support, and eventually replace. The application currently consists of a Microsoft Access 2000 database (including the GUI and several tables) with some tables linked from a SQL Server database. The application generates a report, the records from which are entered into a separate system which is being retired. My current task is to build a new database that replaces the functionality of the system being retired and queries the existing Access and SQL databases for the data that is manually entered from the above mentioned report, eliminating the need for the manual data entry step. I have elected to build the new database using SQL Server 2000 (SP3a).

Problem:
While I can easily create a linked server to the old SQL Server, the linked server to the Access database has been problematic. I discovered, however, that the problem extends to setting up text files and Excel files as linked servers, as well. The problem doesn’t seem to be just that the files are on a shared drive, but that the query is being executed against the server from a workstation AND that the files are located on a shared drive. SQL seems able to handle either one, but not both.
(Why?!? I could see a problem with files on shares, though that would be a pretty tough blow for the whole linked server concept. What I don’t get is how Query Analyzer being run from a workstation versus on the server makes a difference. The SQL is supposed to be executed on the server either way, right?)

Environment:
The Access 2000 database (database.mdb) is unsecured and resides in a shared directory (FILE_SHARE) on a Windows 2000 server (hereafter, W2KFS). The instance of SQL Server hosting the existing SQL database (hereafter, olddb) is on a separate Windows 2000 server (hereafter, OLDSQL). The instance of SQL server (SP3a) hosting the new SQL database (hereafter, newdb) is on a third Windows 2000 server (hereafter W2KSQL). I am connecting to SQL Server on W2KSQL using Windows Authentication. My user is a domain user (hereafter, DOMAIN\myuser) and a member of the Administrators group on both W2KSQL and W2KFS and a member of the System Administrators role for SQL Server and of both the public and db_owner roles for the master and newdb databases, and is a member of a user group on W2KFS which is granted Full Control in both Share Permissions and Security on FILE_SHARE. The instance of SQL Server on W2KSQL is set to run as a domain user (hereafter, DOMAIN\sqlsrvuser). This user is also a member of the Administrators
group on W2KSQL, a member of the System Administrators role for SQL Server on W2KSQL and a member of the public and db_owner roles for the master and newdb databases, and a member of the same user group on W2KFS which is granted Full Control in both Share Permissions and Security on FILE_SHARE.

Troubleshooting:
I executed the following SQL from QA on my workstation to link OLDSQL:
IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'OLDSQL')
  EXEC sp_dropserver @server = 'OLDSQL', @droplogins ='droplogins'
GO
EXEC sp_addlinkedserver @server = 'OLDSQL', @srvproduct = 'SQL Server'
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SQLSRV01', @useself = FALSE,
  @locallogin = NULL, @rmtuser = 'user', @rmtpassword = 'password'
GO
EXEC sp_tables_ex 'OLDSQL'
GO
This was successful, returning a list of the tables in the default database for the user.
I executed the following SQL from QA on my workstation to link database.mdb as MSADB:
IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'MSADB')
  EXEC sp_dropserver @server = 'MSADB', @droplogins ='droplogins'
GO
EXEC sp_addlinkedserver
    @server = 'MSADB',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @srvproduct = 'OLE DB Provider for Jet',
    @datasrc = '\\W2KFS\FILE_SHARE\database.mdb'
GO
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'MSADB',
    @useself = false,
    @locallogin = NULL,
    @rmtuser = 'Admin',
    @rmtpassword = NULL
GO
EXEC sp_tables_ex 'MSADB'
GO
This successfully created the linked server and mapped all logins to the Admin user with a blank password. However, the sp_tables_ex procedure returns the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
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 '\\W2KFS\FILE_SHARE\database.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: ].
I have confirmed that I can access the file on the share from my workstation, and that if I connect to W2KSQL with terminal services as DOMAIN\myuser or DOMAIN\sqlsrvuser I can access the file. No other user is accessing the file. Also, I used Filemon and was able to see that the file was accessed when the query succeeded, but not when it failed. This result was consistent for all the testing listed below.
This is where it gets strange…
If I connect to W2KSQL using terminal services (as either DOMAIN\myuser or DOMAIN\sqlsrvuser) and open QA to connect to the database server (as either (local) or W2KSQL) and execute “EXEC sp_tables_ex ‘MSADB’” it succeeds, returning a list of the tables, views, etc. in database.mbd.
Just for kicks I tried mapping \\W2KFS\FILE_SHARE\ to a drive (H:\ on W2KSQL and my workstation for both DOMAIN\myuser and DOMAIN\sqlsrvuser) and rerunning the script to link the server with the following change:
-- @datasrc = '\\W2KFS\FILE_SHARE\database.mdb'
    @datasrc = 'H:\database.mdb'
Predictably, when I executed “EXEC sp_tables_ex ‘MSADB’” from my workstation or over terminal services on W2KSQL using QA I received the error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'H:\database.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
I also tried creating a System DSN on W2KSQL as follows:
Data Source Name: MSADB
Database: H:\database.mdb
(ODBC setup will not allow UNC paths, so I had to use the mapped drive.)
System Database: None
Page Timeout: 5
Buffer Size: 2048
Exclusive: No
Read Only: No
Login Name: Admin
Password:
Default Dir:
Driver:
FIL: MS Access;
…
The version of the Microsoft Access Driver on W2KSQL is 4.00.6200.00
I then attempted to create the linked server using OLE DB for ODBC as follows:
IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'OldMSAccessCMS')
  EXEC sp_dropserver @server = 'OldMSAccessCMS', @droplogins ='droplogins'
GO
EXEC sp_addlinkedserver
    @server = 'MSADB',
    @provider = 'MSDASQL',
    @srvproduct = 'MS Access 2000',
    @datasrc = 'MSADB'
GO
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'MSADB',
    @useself = false,
    @locallogin = NULL,
    @rmtuser = 'Admin',
    @rmtpassword = NULL
GO
EXEC sp_tables_ex 'MSADB'
GO
Again, the linked server and the login were created, but sp_tables_ex returned the following error message:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
I receive this message regardless of what user I am logged in as and whether I execute the query in QA on my workstation or over terminal services.
(Cont.)

• Previous message: Jim Young: "Re: 2 GB for each db and 5 db for each instance"
• Next in thread: McK: "Re: Linked server from SQL Server to Access db on network share?"
• Reply: McK: "Re: Linked server from SQL Server to Access db on network share?"
• Messages sorted by: [ date ] [ thread ]