login fails for linked server to text file

Question

login fails for linked server to text file

Amrita bhatia

Right there with Babe

Points: 718
More actions
August 22, 2005 at 3:57 am

#90783

hi
following is the query for creating a linked server to text file
EXEC sp_addlinkedserver txtsrv1, 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'C:\CSV',
   NULL,
   'Text'
GO
login is created as
EXEC sp_addlinkedsrvlogin txtsrv1, FALSE,'admin', NULL
above line fails ,
'admin' is not a local user. Remote login denied.
any idea why is it failing
above statement works if we give sql server login is given
thanks
Amrita

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Max Mulawa SSC Enthusiast Points: 174 More actions · Answer 1

if u check sp_addlinkedsrvlogin syntax then u will see that your parameter 'admin' should be a SQL Server login or a Windows NT user and Windows NT user must have been granted access to SQL Server.

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 2

this statement is working

EXEC sp_addlinkedsrvlogin txtsrv1, FALSE,null,'admin', NULL

but can anybody point me to the difference between them ??

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 3

I'm being obvious here ()....but the "EXEC sp_addlinkedsrvlogin txtsrv1, FALSE,null,'admin', NULL" statement has 1 extra 'null' after the false.

If you read the sp_addlinkedsrvlogin stored procedure, and follow the logic of the IF conditions contained therein, the latter statement must go down a different execution path from the former.

Max Mulawa SSC Enthusiast Points: 174 More actions · Answer 4

In this case 'admin' is a remote login and last parameter is remote password.

Setting null for 3rd parameter:

"NULL specifies that this entry applies to all local logins that connect to " txtsrv1 - BOL

Max

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 5

books online suggested for adding a linked server to text file

--Create a linked server

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'c:\data\distqry',

NULL,

'Text'

GO

--Set up login mappings

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL

GO

and it didnt work,does that mean , BOL is incorrect in here or m i missing something here?

Max Mulawa SSC Enthusiast Points: 174 More actions · Answer 6

'Admin' login probably doesn't exist on your SQL server. Try replacing 'Admin'->'sa' or with any login from SQL Server.

Read again BOL docs about sp_addlinkedsrvlogin.

Max

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 7

yes 'sa' works , my question is y i need a sql login to access a text file .

also the example i gave was for sp_addlinkedserver

Max Mulawa SSC Enthusiast Points: 174 More actions · Answer 8

u don't need SQL login, put NULL instead of 'sa' and then all SQL Server logins will have access to linked server.

Go to Enterprise Manager,select properties for added linked server, and view 'Security' tab. This maybe give u some clues.

Try this link

http://www.users.drew.edu/skass/sql/TextDriver.htm

Max