locked
about linked server DB access RRS feed

  • Question

  • Hi all, I have encoutered a linked server related question here which may need your help. Below is the my steps:

    1. Set up two sql server 2008 sp1 servers A and B in a private domain with the same domain account
    2. Add server B as linked server to server A
    3. Create a new DB TestDB on server B
    4. On server A, expand server objects\linked server, and find server B is there, then expand all the sub folders on server B
    5. Find TestDB is not there

    Is there anything wrong with my steps or what should I do to get it correctly configured?
    Thursday, January 28, 2010 5:19 AM

Answers

  • when u r creating or making any changes to linked servers, u have to login to that particular server , connect to sql server and then create or update the linked server.
    Tuesday, February 2, 2010 1:44 PM

All replies

  • Hi,

    Try this

    Expand Linked Servers
    Right Click on Server B
    In Linked Server Properties window click on Server Options
    In Server Options make "Data Access" to True
    Rajesh Jonnalagadda http://www.ggktech.com
    Thursday, January 28, 2010 5:44 AM
  • Hi Rajesh,

    I have tried your solution, and the problem hasn't been resolved.
    Do you have any other solutions?
    Thursday, January 28, 2010 8:43 AM
  • Hi,

    Try this

    You should have Admin Permissions on Server B.

    /****** Object:  LinkedServer [ServerB]    Script Date: 01/28/2010 14:52:35 ******/
    EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'SQL Server'
     /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'True',@locallogin=N'GGK\Rajesh',@rmtuser=NULL,@rmtpassword=NULL
    
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation compatible', @optvalue=N'false'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'data access', @optvalue=N'true'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'dist', @optvalue=N'false'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'pub', @optvalue=N'false'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc', @optvalue=N'true'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc out', @optvalue=N'true'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'sub', @optvalue=N'true'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'connect timeout', @optvalue=N'0'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation name', @optvalue=null
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'lazy schema validation', @optvalue=N'false'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'query timeout', @optvalue=N'0'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'use remote collation', @optvalue=N'true'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'remote proc transaction promotion', @optvalue=N'false'
    GO
    
    
    



    Rajesh Jonnalagadda http://www.ggktech.com
    Thursday, January 28, 2010 9:27 AM
  • I have also tried the commands you supplied with Admin Permissions on Server B, but seems the issue is still not resolved.
    Any other suggestions?Thanks
    Tuesday, February 2, 2010 8:30 AM
  • when u r creating or making any changes to linked servers, u have to login to that particular server , connect to sql server and then create or update the linked server.
    Tuesday, February 2, 2010 1:44 PM