Re: Setting up Linked server to MsAccess

I was trying to force your suggestion:

"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."

but alas, that did not fix the problem. So I'm still stuck. Intersting,
all the examples I have been able to find do not use integrated windows
authenication (although they recommend using it).
The whole problem has to be tied up with how SQL Server\EXPRESS, 'opens' the
access file. The Access file is co-located on the same 'box' as the sql
server\Express. I have tried mapping the linked access files via a UNC and
via a regular file route, neither work. I have added the NETWORK SERVICE
account (the account the instance of SQL server is running under) to the
list of users of the folder containing the access files (again no joy).

From my prespective, the ability to link to an access database is damn sure
SECURE. The only thing more secure would be to delete the Access file!!!!
;>

Thanks in advance for any additional insight you could provide.

Ed Warren.


"Sue Hoegemeier" <Sue_H@xxxxxxxxxxxxx> wrote in message
news:2uqr82drb3kcftu79sna7davtodddok9rb@xxxxxxxxxx
sp_helplinkedsrvlogin will return all linked server logins.

I'm confused as to what you are doing with the logins,
groups, etc. Try executing something like:
EXEC sp_addlinkedsrvlogin 'YourAccessLinkedServerName',
'false', NULL, 'Admin', NULL
This will set the security so anyone using the linked server
would be mapped to Admin and a null password for the
security context.

-Sue

On Thu, 8 Jun 2006 09:47:15 -0500, "Ed Warren"
<eowarren@xxxxxxxxxxxxxxx> wrote:

I found code that should resolve the issue but fails:
(it should 'map' all users to use Admin with no password for the linked
serve DB2)

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'DB2',

@useself = false,

@locallogin = NULL,

@rmtuser = 'Admin',

@rmtpassword = NULL



is there a sp_?? that will list all the linkedsrvlogins with their
properties?



Thanks.



Ed Warren.









"Ed Warren" <eowarren@xxxxxxxxxxxxxxx> wrote in message
news:ugYIczliGHA.4512@xxxxxxxxxxxxxxxxxxxxxxx
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: Setting up Linked server to MsAccess
    ... (it should 'map' all users to use Admin with no password for the linked ... that will list all the linkedsrvlogins with their ... At the database level I have added my Server login ...
    (microsoft.public.sqlserver.security)
  • Re: Restored Server but SharePoint refusing admin access
    ... > SID/BID or remove the user from the database and add it again. ... >, In SQL Configuration Manager go to SQL> Server ... > you had) you cannot access the database from that account. ... > newly added administrator account (for me, since I added a new admin ...
    (microsoft.public.windows.server.sbs)
  • Create SharePoint Portal failed.
    ... One mentioned ensuring that SQL Server uses a case ... 13:55:40 Service database server is 'USDC-JOHRIV'. ... Update dbo.propertylist set DisplayName = N'Last name' ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Linked Server to MS Access DB: Login Mapping Error
    ... >connected to a different SQL server in Query Analyzer. ... Under "Linked Server" I put the name I wanted to reference it by. ... Put path to location of database in "Data Source" (in this case, ... >made without using a security context" option was checked. ...
    (microsoft.public.sqlserver.security)
  • Re: Users Cant Open Access Database Security Or Corruption ??
    ... Why did you copy the FE to the server? ... This Database has been converted from a prior version of microsoft ... The admin group was stripped of the usual design change permissions ...
    (microsoft.public.access.security)