Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #281 > unrolled thread

Linked server to Access MDB

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Linked server to Access MDB "ron paii" <none@nospam.com> - 2011-05-04 12:36 -0500
    Re: Linked server to Access MDB Erland Sommarskog <esquel@sommarskog.se> - 2011-05-04 23:37 +0200
      Re: Linked server to Access MDB "ron paii" <none@nospam.com> - 2011-05-05 06:56 -0500
        Re: Linked server to Access MDB Henk van den Berg <hvandenberg@xs4all.nl> - 2011-05-05 16:37 +0200
          Re: Linked server to Access MDB "ron paii" <none@nospam.com> - 2011-05-05 13:30 -0500
            Re: Linked server to Access MDB "ron paii" <none@nospam.com> - 2011-05-06 14:51 -0500

#281 — Linked server to Access MDB

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

 

[toc] | [next] | [standalone]

#282

ron paii (none@nospam.com) writes:
> 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.
 
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@sommarskog.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

[toc] | [prev] | [next] | [standalone]

#283

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9EDBF05F66BAYazorman@127.0.0.1...
> ron paii (none@nospam.com) writes:
>> 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.
>
> 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@sommarskog.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
>

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

 

[toc] | [prev] | [next] | [standalone]

#285

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

>
> Is there any issues with running it under a Domain account?
>
>
>
It's actually best practice to run it under a standard Domain user account.

[toc] | [prev] | [next] | [standalone]

#287

"Henk van den Berg" <hvandenberg@xs4all.nl> wrote in message 
news:4dc2b673$0$34849$e4fe514c@news.xs4all.nl...
> ron paii schreef op 05-05-2011 13:56:
>
>>
>> Is there any issues with running it under a Domain account?
>>
>>
>>
> It's actually best practice to run it under a standard Domain user 
> account.

Thank you, I'll give that a try. 

[toc] | [prev] | [next] | [standalone]

#294

"ron paii" <none@nospam.com> wrote in message 
news:ipuqc1$arg$1@dont-email.me...
>
>
> "Henk van den Berg" <hvandenberg@xs4all.nl> wrote in message 
> news:4dc2b673$0$34849$e4fe514c@news.xs4all.nl...
>> ron paii schreef op 05-05-2011 13:56:
>>
>>>
>>> Is there any issues with running it under a Domain account?
>>>
>>>
>>>
>> It's actually best practice to run it under a standard Domain user 
>> account.
>
> Thank you, I'll give that a try.

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web