server using default login credentials

I have to access data on a separate server, so I created a linked
server using this syntax (some names changed to be generic);
EXEC sp_addlinkedserver
@.server = N'LinkedServerName',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'MyServerName',
@.provstr = 'DRIVER={SQL
Server};SERVER=N'MyServerName';UID=MyAppUser;PWD=MyAppPW;',
@.catalog = N'MyTable'
This link is created, but when I try to do a simple select statement
on 'MyTable', it returns the error:
Login failed for user 'Domain\KirkH'
Therefore, it is trying to connect using my login credentials, INSTEAD
of the ones I specified when I created the linked server. Am I using
this incorrectly, or is my syntax wrong?
I would greatly appreciate any suggestions or comments.
Thank you!."Kirk" <loki70@.hotmail.com> wrote in message
news:53688ba6-ce89-4f28-a02a-c486e8253a24@.2g2000hsn.googlegroups.com...
>I have to access data on a separate server, so I created a linked
> server using this syntax (some names changed to be generic);
> EXEC sp_addlinkedserver
> @.server = N'LinkedServerName',
> @.srvproduct = N' ',
> @.provider = N'SQLOLEDB',
> @.datasrc = N'MyServerName',
> @.provstr = 'DRIVER={SQL
> Server};SERVER=N'MyServerName';UID=MyAppUser;PWD=MyAppPW;',
> @.catalog = N'MyTable'
> This link is created, but when I try to do a simple select statement
> on 'MyTable', it returns the error:
> Login failed for user 'Domain\KirkH'
> Therefore, it is trying to connect using my login credentials, INSTEAD
> of the ones I specified when I created the linked server. Am I using
> this incorrectly, or is my syntax wrong?
> I would greatly appreciate any suggestions or comments.
> Thank you!.
Hi
I would have expected your parameter to be
@.provstr = 'DRIVER={SQL
Server};SERVER=MyServerName;UID=MyAppUser;PWD=MyAppPW;'
i.e N'MyServerName' is not correct!
Have you tried sp_addlinkedsrvlogin e.g.
EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'false', NULL, 'MyAppUser',
'MyAppPW'
John|||On Apr 16, 3:47=A0pm, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Kirk" <lok...@.hotmail.com> wrote in message
> news:53688ba6-ce89-4f28-a02a-c486e8253a24@.2g2000hsn.googlegroups.com...
>
>
> >I have to access data on a separate server, so I created a linked
> > server using this syntax (some names changed to be generic);
> > EXEC sp_addlinkedserver
> > @.server =3D N'LinkedServerName',
> > @.srvproduct =3D N' ',
> > @.provider =3D N'SQLOLEDB',
> > @.datasrc =3D N'MyServerName',
> > @.provstr =3D 'DRIVER=3D{SQL
> > Server};SERVER=3DN'MyServerName';UID=3DMyAppUser;PWD=3DMyAppPW;',
> > @.catalog =3D N'MyTable'
> > This link is created, but when I try to do a simple select statement
> > on 'MyTable', it returns the error:
> > =A0 Login failed for user 'Domain\KirkH'
> > Therefore, it is trying to connect using my login credentials, INSTEAD
> > of the ones I specified when I created the linked server. =A0Am I using
> > this incorrectly, or is my syntax wrong?
> > I would greatly appreciate any suggestions or comments.
> > Thank you!.
> Hi
> I would have expected your parameter to be
> @.provstr =3D 'DRIVER=3D{SQL
> Server};SERVER=3DMyServerName;UID=3DMyAppUser;PWD=3DMyAppPW;'
> i.e N'MyServerName' is not correct!
> Have you tried sp_addlinkedsrvlogin e.g.
> EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'false', NULL, 'MyAppUser',
> 'MyAppPW'
> John- Hide quoted text -
> - Show quoted text -
John,
You were correct - once I added the login using sp_addlinkedsrvlogin,
everything worked great.
Thank you very much for your help!