Re: Linked Server to MS Access DB: Login Mapping Error

From: Mark Johnson (no-reply@nospam.com)
Date: 08/27/02 

From: "Mark Johnson" <no-reply@nospam.com>
Date: Tue, 27 Aug 2002 15:02:40 -0400

OK, once again I pulled a severe newbie mistake for the final leg of my
unecessarily long journey in figuring out linked tables. I was actually
connected to a different SQL server in Query Analyzer. Once I changed my
context to the master database of the correct server, and made sure I was
also in the right spot in Enterprise Manager, I redid all of the following,
and it worked:

==============================================

Note: The following is included only for the benefit of dumb newbies like me
who have used Access and are familiar with "linked tables", but are very
unfamiliar with SQL Server, or at least how to make "linked tables" (via
"linked server" connections) in SQL Server 7.0+. I just would hate to have
someone go through the same 4-day headache I went through beating my head
into the wall.

==============================================

1.) Went into "Security" section under the server I was working with in
Enterprise manager

2.) Right-clicked "Linked Servers", clicked on "New Linked Server"

3.) Under "Linked Server" I put the name I wanted to reference it by. In
this case, "MYDB"

4.) Product name I could have left blank, but put in Access 97 so other
developers will easily know what version of Access DB was used if that info
is needed.

5.) Put path to location of database in "Data Source" (in this case,
"d:\test\mydb.mdb")

6.) Under "Security" tab in the Linked Server Properties, made sure the "Be
made without using a security context" option was checked.

7.) Went into Query Analyzer, logged in as the "sa" user, and chose the
server I was working on (in this case, it was the local server "WS105"). I
don't think you have to log in as this user, but I did to avoid any other
security issues I might run into. So it would just "work for now" until I
figured out a less-privileged account I could use for this (Note: This is
very bad security practice. Yes, I am aware of this.)

8.) Made sure I was in the "master" database context (dropdown at the top of
the window in Query Analyzer. I don't know if this is actually necessary to
be in this database context, but I've read articles lately that seem to
indicate that this is necessary when doing a linked server query like this.
I have not tried this from any other contexts yet, so it might work under
some other random database's context.

9.) Ran a "SELECT *" query on a table in the linked server database, to test
if I had view permissions to the data in the table I needed to access. The
query in my case was: "SELECT * FROM MYDB...[Customers]". Ran the query
(F5), and it returned all the rows in that table in the linked server
database. The end : )

==============================================

Thanks to all of you for your help, and especially John Bell for pointing me
to the 'OLE DB Provider for Jet' article on SQL Server Books Online.

Links I found useful in figuring this out:

*
http://www.fawcette.com/Archives/premier/mgznarch/vbpj/2001/sqlpro0107/rj010
7/rj0107-2.asp

* http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

- Mark

"Mary Chipman" <mchip@nomail.please> wrote in message
news:hibnmuc5b3phpkpt0ojd2686d7jg83naa2@4ax.com...
> You should be able to connect without providing security context as
> long as the Access database is unsecured and you have full access to
> the folder it lives in. Make sure you have service pack 2 for SQLS,
> and make sure the Access database is truly unsecured. If you go into
> the security dialogs in Access, look for <unknown> as the owner of the
> database or any of the tables. If this is the case, then the mdb is
> NOT unsecured--it's just missing the system.mdw it was secured with.
> If you log on to access using your current system.mdw and import all
> of the objects into a new mdb, then you should have no trouble
> accessing it as a linked server using the steps you've outlined below.
>
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
>
> On Tue, 27 Aug 2002 11:43:56 -0400, "Mark Johnson"
> <no-reply@nospam.com> wrote:
>
> >I have a linked server connection set up right now in enterprise manager
> >called MYDB. It is pointing to an access database stored in
> >d:\test\mydb.mdb. The D:\ drive is a local drive on the server, not
mapped
> >anywhere remote. The access database is not set to require any login
> >information to access the data.
> >
> >In the [security/linked servers] section of Enterprise manager, for this
> >linked server, in the "Security" tab, I have the "Be made without using a
> >security context" option checked, and I have no usernames or passwords
> >listed at the top.
> >
> >I am trying to access a table called "Customers" with this Query in Query
> >Analyzer:
> >
> >===============================
> >SELECT * FROM MYDB...Customers
> >===============================
> >
> >I get this error:
> >
> >===============================
> >Server: Msg 7416, Level 16, State 2, Line 1
> >Access to the remote server is denied because no login-mapping exists.
> >===============================
> >
> >Please forgive me if this is a trivial solution. I am rather new to MS
SQL
> >Server. Thank-you inadvance for your help!
> >
> >- Mark
> >
> >
>

Relevant Pages

  • Re: How to do this?
    ... move the backend tables to the SQL Server and link the sql server tables in ... > typically one things of an Access database as either a relational database ... when you speak of splitting a frontend to an IIS ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Linked Server to MS Access DB: Login Mapping Error
    ... You should be able to connect without providing security context as ... and make sure the Access database is truly unsecured. ... accessing it as a linked server using the steps you've outlined below. ... >I am trying to access a table called "Customers" with this Query in Query ...
    (microsoft.public.sqlserver.security)
  • Re: How to do this?
    ... would return to questioning if a basic IIS server facing the public really ... to backaway from the technology a little bit to look at the business plan ... conditions on trying to front a website on an Access database that you have ... You would have IIS load of the SBS, ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: JDBC Applets - MS Access
    ... > I am working on a Applet at the moment which must be able to access ... > I need to be able to setup a jdbc connection to an Access database. ... JDataConnect also uses a server side JDBC server. ...
    (comp.lang.java.databases)
  • Re: Slow response from Windows 2003 web server
    ... I am not sure which of these entries in the HTTPERR Logfile concerns my ... This is my simple test ASP script, ... What excatly do you mean by "Access Database - change in Windows Server ...
    (microsoft.public.inetserver.iis)