+ Reply to Thread
Results 1 to 7 of 7

Logging Into Linked Server

  1. Logging Into Linked Server

    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.




  2. Re: Logging Into Linked Server

    Neil (nospam@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, esquel@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

  3. 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" wrote in message
    news:Xns97C481619B195Yazorman@127.0.0.1...
    > Neil (nospam@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, esquel@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




  4. Re: Logging Into Linked Server

    Neil (nospam@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, esquel@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

  5. Re: Logging Into Linked Server

    > 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



  6. Re: Logging Into Linked Server

    Neil (nospam@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, esquel@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

  7. Re: Logging Into Linked Server

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

    "Erland Sommarskog" wrote in message
    news:Xns97C55D351422Yazorman@127.0.0.1...
    > Neil (nospam@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, esquel@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




+ Reply to Thread