Re: Setting up Linked server to MsAccess

I'm running SQL Server\Express in "Windows Authenication Mode" (Do I have to
switch to the Combined SQL/Windows Authenication?)
All the documentation recommends using Windows Authenication Mode for
security reasons, and since I'm working in a LAN of all windows and a common
user base it seemed the logical answer.

Since it at least sounds the simplest and most likely solution to the
problem.

Lets try to get to step one:
How do I :

Map a windows usergroup Win_DatabaseUsers to use Admin (no password) when
they are accessing my MS Access linked server?

When I go the linked servers security page, the only logons I can 'map' are
the local sql server logons (I'm not even sure this is the right place to do
this).

Do I have to resort to SQL code to do this?

(I'm trying to learn SQL server, I have used MS Access and this is my first
foray into using SQL Server)

Again, thanks for your patience

Ed Warren.


"Sue Hoegemeier" <Sue_H@xxxxxxxxxxxxx> wrote in message
news:danb829ph20305k165v4oohvq03idhr823@xxxxxxxxxx
All users should typically be accessing the Access database
with the user Admin and no password. That's the default for
an Access database and it sounds like all users are not
mapped the same. That could be part of the problem.
You'll need to dig around and find error messages. Having
something fail or not work just isn't enough information to
go on. You said that "any views based on the linked Access
databases fail". Whatever that may mean is what you need to
capture the error information on. If nothing else, you could
have one of those users try to access everything while you
run a trace to capture the statements and errors.

-Sue

On Tue, 6 Jun 2006 00:08:49 -0500, "Ed Warren"
<eowarren@xxxxxxxxxxxxxxx> wrote:

Thanks much for your response: I will try to explain the 'lashup'.

All of this is on a LAN

Box1: Win 2003 Server
Running SQLServer\Express
Containing folders with MSAccess Database X.mdb

SQl Express has linked server X mapped via UNC to Database X.mdb
Under the secrity tab for linked server X, I have selected
"For
a login not defined in the list above, connections will:
ckbox, Be made using the login's current context.

Box2: Win 2000
Accessed via a RemoteDeskTop Connection by the User (logon/password)
With a WinApplication installed App1.exe
With a WebService installed in IIS WebService.amsx
App1.exe --> Calls the WebService for Data --> Passed to Box1
(SqlServerExpress)

If the user has full admin rights and opens IE, navigates to
http://localhost/WebService/WebService.asmx all the data calls work
fine.
If the user does not have full admin rights .. same site.. then all the
data from the SQL server Database Tables work fine, but any views based on
the linked Access databases fail.

So to the best of my knowledge I'm failing to get proper Authencation
passed
from the WebService -->SQL express --> the Mdb File.

The good news is I only need to 'read' the data from the Access Files, but
I
need to do it in conjunction with a view based on the data in the Native
SQL
Server database.
Something like "if its not in the SQLServer database then return a row
from
the Access Database",

I hope some of this makes some sense, but there are just too many 'players
on the field' for me to figure this thing out.

Any help or ideas of where to look would be greatly appreciated.

Again, thanks for the help.

Ed Warren.






"Sue Hoegemeier" <Sue_H@xxxxxxxxxxxxx> wrote in message
news:7i298211tprekhgqu7mtirt1p893dga6lv@xxxxxxxxxx
It's difficult to figure out what the possible issues are as
"only works for some users" say much.
What are the specific error messages and what application
are the users using when they get the messages? Where is the
access file located - on a UNC share, on the SQL Server box,
etc. Do you have any security implemented for the Access
database?

-Sue

On Sun, 4 Jun 2006 13:24:15 -0500, "Ed Warren"
<eowarren@xxxxxxxxxxxxxxx> wrote:

I'm stumped by the security issues around setting up a microsoft access
database as a linked server.
I only need to read the data, I don't need to write back to the
database.

I've followed the wizards in the Management Studio and have set up the
Access File as a Linked server, however, it only works for 'some' of the
users.
I've checked the folder permissions and the 'failed' users are memebers
of
a
group with full permissions to to folder, so the problem does not appear
to
be at the folder level.

So, I need a simple step by step on how to walk through the SQL Server
Management Studio and set up the permissions as required to read the
database files.

Thanks,

Ed Warren.






.

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)