Linked Server Logon

Here is my setup, I'm setting up several Access .mdb's as linked servers.
The .mdb's are not password protected and they reside on our network. I can
get them in Sql server fine, and when I logon using my domain
username/password, I can get to them without any problem.
What I'm trying to do is setup a generic sql user who can access the linked
servers, but I keep on getting the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.]
I would like to use just a sql user to access the files like "sa", but I'll
set them up using my domain username/password if need be.
What am I missing?
Thanks,
BarryAs long as the "MS SQL Server" service's logon account has access to the UNC
where your files are located, you will make the connection using the linked
server security option "Be made without using a security context." Also,
just in case, check the default open mode and default record locking mode
for both the access db's.
They should be Shared, No Locks respectively.
Tools > Optons > Advanced Tab
Christopher Winn
Database Administrator
Fort Bend ISD
"Barry" <no_one@.home.net> wrote in message
news:OvAmixmFEHA.628@.TK2MSFTNGP10.phx.gbl...
> Here is my setup, I'm setting up several Access .mdb's as linked servers.
> The .mdb's are not password protected and they reside on our network. I
can
> get them in Sql server fine, and when I logon using my domain
> username/password, I can get to them without any problem.
> What I'm trying to do is setup a generic sql user who can access the
linked
> servers, but I keep on getting the error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Authentication
> failed.
> [OLE/DB provider returned message: Cannot start your application. The
> workgroup information file is missing or opened exclusively by another
> user.]
> I would like to use just a sql user to access the files like "sa", but
I'll
> set them up using my domain username/password if need be.
> What am I missing?
> Thanks,
> Barry
>|||Thanks Chris, that did it.
Barry
"Winn, Chris" <seawinn@.hotmail.com> wrote in message
news:OPiqusnFEHA.2416@.TK2MSFTNGP12.phx.gbl...
> As long as the "MS SQL Server" service's logon account has access to the
UNC
> where your files are located, you will make the connection using the
linked
> server security option "Be made without using a security context." Also,
> just in case, check the default open mode and default record locking mode
> for both the access db's.
> They should be Shared, No Locks respectively.
> Tools > Optons > Advanced Tab
> Christopher Winn
> Database Administrator
> Fort Bend ISD
>
> "Barry" <no_one@.home.net> wrote in message
> news:OvAmixmFEHA.628@.TK2MSFTNGP10.phx.gbl...
servers.
> can
> linked
> Authentication
> I'll
>

Linked server and Windows domain user.

I logon to SQL Server using windows user and I created a linked server to
another SQL Server in the same domain (My windows user can logon both server
with db_owner role). I tried to use "Be made using the login's current
security context"
However, I cannot connect to remote server
Error 18456: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I tried other options too either got the same error or the mapping windows
user says "User Domain\user cannot login". The only way I can access is to
create a log SQL Server account and mapping to it.
I also tried openrowset:
SELECT a.*
FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
Server};SERVER=server001;Database=pubs;t
rusted_connection=yes',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
and I got the following error:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server D
river][SQL
Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
Did I miss any steps to make remote linked server?The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000 and above, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation (2000)
or in the topic Configuring Linked Servers for
Delegation(2005)
-Sue
On Mon, 23 Jan 2006 11:46:03 -0800, "nick"
<nick@.discussions.microsoft.com> wrote:

>I logon to SQL Server using windows user and I created a linked server to
>another SQL Server in the same domain (My windows user can logon both serve
r
>with db_owner role). I tried to use "Be made using the login's current
>security context"
>However, I cannot connect to remote server
>Error 18456: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>I tried other options too either got the same error or the mapping windows
>user says "User Domain\user cannot login". The only way I can access is to
>create a log SQL Server account and mapping to it.
>I also tried openrowset:
>SELECT a.*
>FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> Server};SERVER=server001;Database=pubs;t
rusted_connection=yes',
> pubs.dbo.authors) AS a
>ORDER BY a.au_lname, a.au_fname
>and I got the following error:
>Server: Msg 7303, Level 16, State 2, Line 1
>Could not initialize data source object of OLE DB provider 'MSDASQL'.
>[OLE/DB provider returned message: [Microsoft][ODBC SQL Server
Driver][SQL
>Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
>
>Did I miss any steps to make remote linked server?