Logging Into Linked Server

[Neil]

After creating a linked server to a remote server, I needed to log in using
sp_addlinkedsrvlogin to get my stored procedure to work. However, I noticed
that after stopping SQL Server and the DTC and then restarting both, that my
stored procedure worked without having to execute sp_addlinkedsrvlogin.

Is the log-in information stored in the machine, such that if SQL Server is
stopped or the server is rebooted, on does not have to execute
sp_addlinkedsrvlogin again? Or is there a point at which one would have to
re-log-in to a linked server?

Thanks.

[Erland Sommarskog]

Neil (no****@nospam.net) writes:

After creating a linked server to a remote server, I needed to log in
using sp_addlinkedsrvlogin to get my stored procedure to work. However,
I noticed that after stopping SQL Server and the DTC and then restarting
both, that my stored procedure worked without having to execute
sp_addlinkedsrvlogin.

Is the log-in information stored in the machine, such that if SQL Server
is stopped or the server is rebooted, on does not have to execute
sp_addlinkedsrvlogin again? Or is there a point at which one would have
to re-log-in to a linked server?

You appears to have missunderstood the purpose of sp_addlinkedsrvlogin. The
procedure does not login into the remote server. I have not checked, but I
would assume that you can run sp_addlinkedsrvlogin without the linked server
being available.

What sp_addlinkedsrvlogin does, as you already have discovered, is to store
information, so that when you issue a query to the linked server, SQL Server
can log in to that data source. So this is a configuration procedure that
you run once, or possible when you need to give a new user access to the
linked server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Neil]

Speaking of linking to a server, is it possible to assign a linked server an
alias at the time it is linked? The reason is that our linked server is a
web server, and it's possible that sometime in the not-too-distant future
its address will change, and, at that point, I'd have to go into the stored
procedures and manually change the server name. Can I assign an alias so
that if a new server has to be linked it can have the same alias?

Thanks.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Neil (no****@nospam.net) writes:

After creating a linked server to a remote server, I needed to log in
using sp_addlinkedsrvlogin to get my stored procedure to work. However,
I noticed that after stopping SQL Server and the DTC and then restarting
both, that my stored procedure worked without having to execute
sp_addlinkedsrvlogin.

Is the log-in information stored in the machine, such that if SQL Server
is stopped or the server is rebooted, on does not have to execute
sp_addlinkedsrvlogin again? Or is there a point at which one would have
to re-log-in to a linked server?

You appears to have missunderstood the purpose of sp_addlinkedsrvlogin.
The
procedure does not login into the remote server. I have not checked, but I
would assume that you can run sp_addlinkedsrvlogin without the linked
server
being available.

What sp_addlinkedsrvlogin does, as you already have discovered, is to
store
information, so that when you issue a query to the linked server, SQL
Server
can log in to that data source. So this is a configuration procedure that
you run once, or possible when you need to give a new user access to the
linked server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Neil (no****@nospam.net) writes:

Speaking of linking to a server, is it possible to assign a linked
server an alias at the time it is linked? The reason is that our linked
server is a web server, and it's possible that sometime in the
not-too-distant future its address will change, and, at that point, I'd
have to go into the stored procedures and manually change the server
name. Can I assign an alias so that if a new server has to be linked it
can have the same alias?

If you are on SQL 2005, you can always use synonyms.

If you are on some earlier version of SQL Server you can use
sp_addlinkedserver. You see, what you define with sp_addlinkedserver is
really an alias.

In its simplest form, you just say:

sp_addlinksedserver 'THATSERVER'

and THATSERVER will refer to a server with that name. However, you
can also say:

sp_addlinkedserver 'MYSERVERNAME', '', 'SQLOLEDB', 'THATSERVER'

so that you can use MYSERVERNAME as a reference to THATSERVER. Thus,
when you web server changes, you just drop the server, and recreate
it with the new information.

Note: the syntax above may not work exactly like that. I usually have
problem with more advanced usage of sp_addlinksedserver myself. But
it usually sorts out when I've been looking at the topic for
sp_addlinksedserver in Books Online for a while.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Neil]

> In its simplest form, you just say:

sp_addlinksedserver 'THATSERVER'

and THATSERVER will refer to a server with that name. However, you
can also say:

sp_addlinkedserver 'MYSERVERNAME', '', 'SQLOLEDB', 'THATSERVER'

So, basically:

sp_addlinkedserver @server = 'MYSERVERNAME', @provider = 'SQLOLEDB',
@datasrc = 'THATSERVER'

I'll give that a shot. Thanks.

Neil

[Erland Sommarskog]

Neil (no****@nospam.net) writes:

So, basically:

sp_addlinkedserver @server = 'MYSERVERNAME', @provider = 'SQLOLEDB',
@datasrc = 'THATSERVER'

I'll give that a shot. Thanks.

Yeah, but as I recall the second parameter, @srvproduct, may not be NULL.
But you'll find out. :-)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Neil]

Yup, you were right. Used '' and it worked fine. Thanks!

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Neil (no****@nospam.net) writes:

So, basically:

sp_addlinkedserver @server = 'MYSERVERNAME', @provider = 'SQLOLEDB',
@datasrc = 'THATSERVER'

I'll give that a shot. Thanks.

Yeah, but as I recall the second parameter, @srvproduct, may not be NULL.
But you'll find out. :-)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx