Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: "ron paii" <none@nospam.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Linked server to Access MDB
Date: Wed, 4 May 2011 12:36:04 -0500
Organization: A noiseless patient Spider
Lines: 2
Message-ID: <ips2q7$1ot$1@dont-email.me>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
Injection-Date: Wed, 4 May 2011 17:36:07 +0000 (UTC)
Injection-Info: mx01.eternal-september.org; posting-host="CzRErDNbRazHAY/a5dIFZQ"; logging-data="1821"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18dWFQoh/i4EXMJot6Qeu3I"
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
Importance: Normal
Cancel-Lock: sha1:sy4HJVZOAkeyKDtsQwdnzlgk/hA=
X-Priority: 3
X-MSMail-Priority: Normal
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:281

I am trying to create a Linked Server from MS SQL server Management Studio
to a MDB file on a 2nd Server.
Using Windows authentication I can query a remote table in the Studio but
not from a remote application. Using SQL authentication I get the following
error "Cannot start your application. The workgroup information file is
missing or opened exclusively by another user." If the MDB file is moved to
the C: drive of the SQL server, everything works, using both authentication
methods. There is no security setup on the MDB file.
SQL server is NOT using a Domain account.

SQL server 2008, 64Bit.
Provider: Microsoft.ACE.OLEDB.12.0
                 Allow inprocess = True

msjet40.dll version is 4.0.9755.0

I got Access is denied from
exec xp_cmdshell 'dir \\pack01\data\paisys\Data-V10\Backup\X4\*.*';
go

The script generated by management studio is as follows
/****** Object:  LinkedServer [PARTSDB]    Script Date: 05/04/2011 09:15:41
******/
EXEC master.dbo.sp_addlinkedserver @server = N'PARTSDB',
@srvproduct=N'PARTSDB', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'\\pack01\Data\PaiSys\Data-V10\backup\X4\CS.mdb'
 /* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'PARTSDB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'collation
compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'data access',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'pub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'rpc',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'rpc out',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'sub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'connect
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'collation
name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'lazy schema
validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'query
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'use remote
collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'remote proc
transaction promotion', @optvalue=N'true'
GO