Re: Setting up Linked server to MsAccess

I've spent some more time with this.

At the server level I have added my windows user group
"WARRENASSOC\databaseUsers" as a login.
At the database (DB1) level I have added my Server login
"WARRENASSOC\databaseUsers" as a User with database role membersips::
db_datareader and db_datawriter

DB1 has a view which is a distributed query (it uses tables from DB1 and
DB2(linked server to MS Access file)

When I click DB2's properties under security I have a window "local Login"
which is blank.
and a set of checkboxes one of which is checked "Be made using the login's
current security context"

When I try to add a login to the "Local Login" my User Group
"WARRENASSOC\databaseUsers" is not a choice, since it is not a local login,
but a windows login.


The usergroup (WARRENASSOC\databaseUsers) has full control in the folders
containing the Access database (under windows).


The issue is where does one 'map' the windows login
WARRENASSOC\databaseUsers to use the User "Admin" with no password when
communicating with DB2?


Thanks,

Ed Warren.


"Ed Warren" <eowarren@xxxxxxxxxxxxxxx> wrote in message
news:uoEGTwdiGHA.836@xxxxxxxxxxxxxxxxxxxxxxx
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: Install MSDE w/ MSDE Depl.Toolkit. What permissions when using Win Auth?
    ... I created a login using Enterprise Manager on SQL Server on my server. ... Database Access tab I ticked the tick box for the database that I ... Now when my user installed the MSDE database on his machine locally, ...
    (microsoft.public.sqlserver.msde)
  • Re: ASP.NET User.Identity.Name value after a domain username chang
    ... Since .NET is just calling into Windows API calls to resolve the user name, ... I have an ASP.NET application that is on a Win2000 server in a domain ... I have a very confusing issue when the domain login of a user is ... I thought this might have been some caching issue on the client so I ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: CAN WE LOGIN TO A WINDOWS 2003 ACTIVE DIRECTORY DOMAIN OVER TH
    ... Having a user login to a windows 2003 server over the internet is not a good ...
    (microsoft.public.windows.server.active_directory)
  • Re: Installation OK, but cant connect
    ... > created an access database. ... SQL Server authentication is "client" independent.... ... connections or SQL Server authenticated connections... ... which authenticate user's login at the windows login step... ...
    (microsoft.public.sqlserver.msde)
  • Re: help with 2000i on win2k server (sp3 on database)
    ... See Leonard's post for the config options. ... If the server is mangling itself, then ANYTHING running on the server ... that I work with will support the database AND the application. ... As for Windows vs. Linux/NetWare, I can agree, but I also see MANY ...
    (comp.databases.btrieve)