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',@locallogi n=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

ron paii (none (AT) nospam (DOT) com) writes:
That is probably the problem. If SQL Server runs under LocalSystem or
somesuch, there is usually not access rights to remote shares.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Is there any issues with running it under a Domain account?

ron paii schreef op 05-05-2011 13:56:

"Henk van den Berg" <hvandenberg (AT) xs4all (DOT) nl> wrote

Thank you, I'll give that a try.

"ron paii" <none (AT) nospam (DOT) com> wrote

Got it to work by executing the MSSQLSERVER service under a Domain account
with rights to the folder where the MDB file is stored.