locked
linked server between 2 sql servers security RRS feed

  • Question

  • Hello all,

    I have 2 sql server A and Server B

    i have SA access on Server A and just read access on Server B(that to i am in a group account) i mean our group is added to the Server B which is in same domain but this server is used by different team so they just gave us read access on this server by adding our group there.

     Server A is our team server we have full rights on it.

    nOW WE want to create a linked server on Server A, can i create it if so what security context i should use could any one please help me

    say our group added on Server B is domain\teamA_readaccess and on server A we have same account with admin access.

    Monday, June 4, 2012 4:38 PM

Answers

All replies

  • On [ServerA], create your linked server to [ServerB] using the login on [ServerB].  Such as:

    USE [master] 
    GO 
    EXEC [master].[dbo].[sp_addlinkedserver] @server = N'ServerB', @srvproduct=N'SQL Server' 
    GO 
    EXEC [master].[dbo].[sp_addlinkedsrvlogin] 
    	@rmtsrvname = N'ServerB', @locallogin = NULL
    	,@useself = N'False', @rmtuser = N'YourLogin'
    	,@rmtpassword = N'YourLogin_sPassword'
    GO 
    HTH.

    Monday, June 4, 2012 6:21 PM
  • USE [master] GO EXEC [master].[dbo].[sp_addlinkedserver] @server = N'ServerB', @srvproduct=N'SQL Server' GO EXEC [master].[dbo].[sp_addlinkedsrvlogin] @rmtsrvname = N'ServerB', @locallogin = NULL ,@useself = N'False', @rmtuser = N'YourLogin' --if i use my login other people in my group cannot access it right?

    moreover i cannot use the domain group here as no passwords for groups. ,@rmtpassword = N'YourLogin_sPassword' GO

    i think only turnaround will be to create a sql account with same permissions and use it to connect with that login.

    Monday, June 4, 2012 7:03 PM
  • From what you say, this should be sufficient:

    EXEC sp_addlinkedserver 'ServerB'

    If you login on ServerA with Windows authentication, you should be able to use this linked server to access ServerB with the same Windows login. Of course, you will still only have read access. This will not always work; if there is a double-hop and insufficient trust, it will not work out.

    If you use SQL logins on ServerA, you will need the solution that Don_Don posted.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, June 4, 2012 9:50 PM
  • I wrote some articles on Linked server, you can check it out. In your case you have mentioned your group (I assume its AD group) has read access on server B, so create the linked server as shown in the first link and in security tab choose "Be made using the logins current security context" so that it will use the users credential

    http://sql-articles.com/articles/dba/linked-server-part-1/

    http://sql-articles.com/articles/dba/linked-server-part-2/


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    • Marked as answer by SQLKiller Wednesday, June 6, 2012 1:16 PM
    Tuesday, June 5, 2012 11:52 AM
  • In the SSMS go to >server objects > New Linked Server >select security>in the radio button list select to be made with security context>key in the crederntials of the server you want to connect
    • Proposed as answer by vijaysingare Tuesday, June 5, 2012 3:38 PM
    Tuesday, June 5, 2012 3:38 PM
  • Thanks everyone,

    @VidhyaSagar your article is good. But when i choose "Be made using the logins current security context" it works fine for me but there are other members in the AD group on server B who cannot connect it give anonymous login error.

    Any how i requested the Server B owners to create a sql account with read access and then used this account to remote login by this way it worked for me.

    I am not sure is there any way around other than using a sql account.

    Thanks

    Wednesday, June 6, 2012 1:16 PM
  • The error about anonymous login suggests that there is a double-hop issue and lack of delegation. This is a Windows configuration and not an SQL Server thing, so I will have to admit that I don't know exactly what I'm talking about.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 6, 2012 1:39 PM