Additional SQL Link Fails for Trusted Logon

[KitCaz]

Hi,

I have select rights on a SQL server/database via my Windows logon but when
I use the Link Table Wizard in my MS Access 2007 ADP, create my .odc data
source file with a trusted connection and attempt to link tables (i.e. create
views on same) I ultimately fail with 'Login failed for user 'NT
AUTHORITY/ANONYMOUS LOGON'.

I don't have this problem when I authenticate with a SQL domain account.

I've confirmed my trusted connection data source works via SSMS and Excel.
My institution prefers to use the Windows authorization over SQL domain
accounts, so I need to get this working.

Suggestions? I'm terribly new at ADP so please bear with me.

 

[Vadim Rapp]

When you link to sql server, it's not you who is linking, it's SQL server.
Which runs as service on your machine or on remote machine on the account
specified on the logon tab for the servvice. In this case, it probably runs
on local system account, and that account is not authorized to access
whatever you are trying to link.

For the purposes of linking, the easiest is to use sql server authentication
rather than integrated authentication; otherwise it's quite complicated and
involves nontrivial security things like delegation etc - google for
something like "sql server linked server authentication" and you will see.
Create secret sql server account just for this purpose, and it will work.

 

[KitCaz]

OK, I figured you would say that, but I will try to work with it.

On a more global level, for the linked views I created with the local SQL
account (which worked), I find that although I can open them when I open the
ADP, another user doesn't see them at all when they open it. For me, the
views appear even if I delete the .ocs file that got created when I created
the data source, so I assumed the link was embedded somewhere within the ADP.
So I'm mystified as to why they just don't appear for someone else.

Clearly I have to read up on this whole feature. Can you direct me to where
I might discover the answer to this?

 

[Sylvain Lafontaine]

See http://support.microsoft.com/kb/295255 but first, why would you want to
link tables in the first place?

Unlike MDB file with ODBC linked tables: you don't need to link tables to
connect to a local or a remote server when using ADP.

Even when you need to etablish a link between a sql-server and something
else, like an Excel spreadsheet; there are always other methods of doing so.

 

[KitCaz]

Thanks for the link, will research!

The primary server connection for my application data ADP is all set fine as
expected in the ADP file (and can be confirmed via File | Server | Connection
and Properties). But I have a need for my application to be linked to a
couple of other servers for "ancillary" information. I gathered that my only
option was to set up links to these via File | Server | Linked Tables...
which walked me through creating an Office Data Connection (.odc) file and
creating views to the selected tables. this is where I had my original
problem for my Windows account but not my local SQL account.

Should I be doing this another way? As I mentioned in a subsequent post,
this linking doesn't seem to be accessible to others who open the database;
when they open the ADP file, they don't even see the views in the object
list, which I can't figure out why.

 

[Vadim Rapp]

So I'm mystified as to why they just don't appear for someone else.

Exactly for the reasons I said before. Remember - delegation, impersonation,
trust, and many other interesting things are awaiting you on this road.
Making this work initially is just the beginning. That's why I said what you
figured I'd say

Vadim Rapp

 

[Sylvain Lafontaine]

Well, when you need to etablish a linked server on your SQL-Server, you
should always use the tools provided with the SQL-Server to do this and no
rely on the limited options provided by the Access Wizard to do it instead.

I would also give you the exact same advice for all these other things like
making a backup of the database, copying it, re-indexing it, etablishing a
maintenance plan, etc., etc.