Logging Into Linked Server

[Neil]

After creating a linked server to a remote server, I needed to log in using
sp_addlinkedsrv login 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_addlinkedsrv login.

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_addlinkedsrv login again? Or is there a point at which one would have to
re-log-in to a linked server?

Thanks.

[Erland Sommarskog]

Re: Logging Into Linked Server

Neil (nospam@nospam. net) writes:[color=blue]
> After creating a linked server to a remote server, I needed to log in
> using sp_addlinkedsrv login 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_addlinkedsrv login.
>
> 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_addlinkedsrv login again? Or is there a point at which one would have
> to re-log-in to a linked server?[/color]

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

What sp_addlinkedsrv login 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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Neil]

Re: Logging Into Linked Server

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" <esquel@sommars kog.se> wrote in message
news:Xns97C4816 19B195Yazorman@ 127.0.0.1...[color=blue]
> Neil (nospam@nospam. net) writes:[color=green]
>> After creating a linked server to a remote server, I needed to log in
>> using sp_addlinkedsrv login 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_addlinkedsrv login.
>>
>> 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_addlinkedsrv login again? Or is there a point at which one would have
>> to re-log-in to a linked server?[/color]
>
> You appears to have missunderstood the purpose of sp_addlinkedsrv login.
> The
> procedure does not login into the remote server. I have not checked, but I
> would assume that you can run sp_addlinkedsrv login without the linked
> server
> being available.
>
> What sp_addlinkedsrv login 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, esquel@sommarsk og.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[/color]

[Erland Sommarskog]

Re: Logging Into Linked Server

Neil (nospam@nospam. net) writes:[color=blue]
> 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?[/color]

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_addlinkedser ver. You see, what you define with sp_addlinkedser ver is
really an alias.

In its simplest form, you just say:

sp_addlinksedse rver 'THATSERVER'

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

sp_addlinkedser ver '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_addlinksedse rver myself. But
it usually sorts out when I've been looking at the topic for
sp_addlinksedse rver in Books Online for a while.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Neil]

Re: Logging Into Linked Server

> In its simplest form, you just say:[color=blue]
>
> sp_addlinksedse rver 'THATSERVER'
>
> and THATSERVER will refer to a server with that name. However, you
> can also say:
>
> sp_addlinkedser ver 'MYSERVERNAME', '', 'SQLOLEDB', 'THATSERVER'
>[/color]

So, basically:

sp_addlinkedser ver @server = 'MYSERVERNAME', @provider = 'SQLOLEDB',
@datasrc = 'THATSERVER'

I'll give that a shot. Thanks.

Neil

[Erland Sommarskog]

Re: Logging Into Linked Server

Neil (nospam@nospam. net) writes:[color=blue]
> So, basically:
>
> sp_addlinkedser ver @server = 'MYSERVERNAME', @provider = 'SQLOLEDB',
> @datasrc = 'THATSERVER'
>
> I'll give that a shot. Thanks.[/color]

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



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Neil]

Re: Logging Into Linked Server

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

"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97C55D3 51422Yazorman@1 27.0.0.1...[color=blue]
> Neil (nospam@nospam. net) writes:[color=green]
>> So, basically:
>>
>> sp_addlinkedser ver @server = 'MYSERVERNAME', @provider = 'SQLOLEDB',
>> @datasrc = 'THATSERVER'
>>
>> I'll give that a shot. Thanks.[/color]
>
> Yeah, but as I recall the second parameter, @srvproduct, may not be NULL.
> But you'll find out. :-)
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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[/color]