none
Linked Server settings through SMO

    Question

  • I am trying to add Linked server(sql server) to the Sql server by using SMO. I am successful to add Linked server but I am unable to execute the Sql queries because of authentication/authorization exception. I went to Sql server by using Sql Server Management Studio (SSMS) and can see the created linked server exists.

    When I open the properties of linked server, inside the Security page (top left pane), there is the list of Login mappings (which can be created by LinkedServerLogin class). Under the list of logins, there are options as following:

    For a login not defined in the list above, connection will:
    • Not be made
    • Be made without using a security context
    • Be made using the login's surrent security context
    • Be made using the security context:
                  Remote login:     Text box
                  With password:   Text box



    When I set through SSMS the Remote login and password, I can run queries in SSMS.

    My question is, How to set these options through SMO programmatically ?

    Any help will be highly appreciated. Thanks in advance


    Wednesday, March 03, 2010 10:40 AM

All replies

  • Hi,

    For logins not defined in lthe mapping as you can see the statement generated is

    EXEC

     

    master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'linked_server_name ', @locallogin = NULL , .. other options like impersonation and passwd etc.

    So this can be generated through SMO by creating a LinkedServerLogin whose name property is string.empty.

    Regards,
    Alok Parmesh


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Friday, March 05, 2010 6:56 PM
    Answerer
  • Dear Alok Parmesh,

         Thank you for your reply. Actually I already found this way to set the remote login for Linked server, but I was looking, if there is way to set these options through SMO, not through sql statement. This way is same as we get TABLES information through sql statement rather than we use TABLES property of database.

         Anyway, thank you very much for your reply.


    Regards

    Aftab Ahmed Kalhoro
    Saturday, March 06, 2010 4:26 AM
  • You said you found an answer, can you post it?
    Saturday, January 07, 2012 12:02 AM
  • In Microsoft.SqlServer.Management.Smo.LinkedServerLogin you can set the RemoteUser property and the SetRemotePassword method.
    Tuesday, January 10, 2012 6:30 PM