Problem with linked server

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Alexander Szigetvary (rsa-nospam-admin_at_gmx.at)
Date: 01/15/05 

Date: Sat, 15 Jan 2005 11:27:11 +0100

Hi NG!

Sql-Server: W2K3 SQL 2000 SP3
Domain: W2K3
I am having several problems using a linked server:

I am using a linked server to import data from a excel-file that is stored
on the sqlserver (is there another way?). I have a local windows user
(member of a special group to improve security) that has
windows permission to read the excel-file.
When I run select * from linkedsrv...Sheet1$ then I get the following error
(I translated it from german, it might not be matching the english error):
Server: Mess.-No. 7399, severity 16, Status 1, Row 1
The OLE DB-Provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: unknown error]
OLE DB-errortrace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The error does not appear when the windows-user has admin-rights (which I
will not grant him).

I have set up the windows user as remote user in the linked server and my
vb-application's sql-user as local user, because as I understand the online
help this would translate the access to the linked server to the rights that
are granted to the windows-user. So doing this I don't even get to the same
error as above I get the following:
Server: Mess.-No. 7399, severity 16, Status 1, row 1
The OLE DB-Provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Authentication failed.
[OLE/DB provider returned message: No valid username or no valid password.]
OLE DB-error [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
This is the way I have added the login to the linked server using query
analyzer:
exec sp_addlinkedsrvlogin
'linkedsrv','false','sqluser','servername\windowsuser','pwd'

Any ideas on this?

I have granted setupadmin and securityadmin roles to my sql-user in order to
create/drop the linked server and to add a login. Is there a way to change
the user 'on the way'? My vb-application's source code holds the password in
plain text (I know ...), which is ok, if it was only for accessing my
database, but the server roles I obviously need to have do bother me a lot.
Any suggestions on this?

All answers and hints are very welcome.

TIA

Alex

Relevant Pages

  • Update
    ... Seems to have something to do with security. ... Windows NT user activated by SETUSER. ... I gather that the SQL Agent Job ... >linked server, and these work via Query Analyzer. ...
    (microsoft.public.sqlserver.security)
  • Re: Linked server - from 2008 to a 2005 box.
    ... Ok - so there are no windows authentication involced in the Linked Server ... The SA account you are using to connect with, has to exists on the target ... From my local machine I connect to a SQL 2008 Server from the management ...
    (microsoft.public.sqlserver.server)
  • Alternative to Account Delegation?
    ... I want to run distributed queries via a linked server using a windows ... autheniticated login. ...
    (microsoft.public.sqlserver.security)
  • linked servers and windows authentication
    ... I have same windows nt sql server logins on both servers. ... Linked server security is configured in a way that all ...
    (microsoft.public.sqlserver.server)
  • Update statement using a linked server...
    ... [OLE/DB provider returned message: New transaction cannot ... OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ... When I run the select statement the linked server it ...
    (microsoft.public.sqlserver.programming)