Re: Linked Server to MS Access DB: Login Mapping Error
From: Mary Chipman (mchip@nomail.please)
Date: 08/28/02
- Next message: Joshua Heslinga: "permission to review jobs and settings"
- Previous message: Ing. Juan Manuel Alegrķa B.: "How to access to SQL Server from VB6"
- In reply to: Mark Johnson: "Re: Linked Server to MS Access DB: Login Mapping Error"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: Mary Chipman <mchip@nomail.please> Date: Wed, 28 Aug 2002 13:07:43 -0400
I'm glad you got it working -- thanks for posting the steps you took.
I'm sure someone will find it useful.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Tue, 27 Aug 2002 15:02:40 -0400, "Mark Johnson"
<no-reply@nospam.com> wrote:
>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
>> >
>> >
>>
>
- Next message: Joshua Heslinga: "permission to review jobs and settings"
- Previous message: Ing. Juan Manuel Alegrķa B.: "How to access to SQL Server from VB6"
- In reply to: Mark Johnson: "Re: Linked Server to MS Access DB: Login Mapping Error"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
