none
Linked Server SQL 2012 to SQL 2008

    Question

  • Hi, we were using the plain provider called SQL Native Client from a SQL 2005 to SQL 2008 but now I need to communicate from SQL2012 to SQL2008 and that provider name is not there anymore.  So I started by installing the SQL Native Client 10.0 on the SQL2012 but I cannot get it communicating without having to use the sa password under security.  The problem is that I'd like to use the windows login by usign the option "login's current security context".

    We need to use this with windows integration and not by SQL login.

    Someone has an idea?

    Thanks in advance!

    Jas

    Friday, November 16, 2012 7:20 PM

Answers

  • Hi Jason,

    SQL Server 2012 has SQL Native Client 11.0 to be installed.

    Use this command:

    use master;
    go

    execute sp_addlinkedserver
    @server=N'My Linked Server',
    @srvproduct = N'',
    @provider=N'SQLNCLI11',
    @datasrc=N'servername'

    If you want to use windows login, you should first make sure your current windows login has valid permission in your current SQL Server to use linked server and the second is your current windows login has valid permission on remote SQL Server.


    Iric Wen

    TechNet Community Support

    Tuesday, November 20, 2012 8:29 AM
  • The SQL Server Native Client (SQLNCLI11) that ships with SQL Server 2012 does not support connections to SQL Server 2000 an earlier. I found this out during testing of our upgrade to SQL 2012. We upgraded our 2008 R2 servers in-place and it appears to have left the SQLNCLI10 provider in place but if you did a fresh install of SQL Server 2012 on a new server you may not have this provider. Does your 2012 server have the SQLOLEDB provider listed under Linked Server Providers in SSMS? If so, you could try using that.    

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Wednesday, November 21, 2012 3:49 PM

All replies

  • Hi Jason,

    SQL Server 2012 has SQL Native Client 11.0 to be installed.

    Use this command:

    use master;
    go

    execute sp_addlinkedserver
    @server=N'My Linked Server',
    @srvproduct = N'',
    @provider=N'SQLNCLI11',
    @datasrc=N'servername'

    If you want to use windows login, you should first make sure your current windows login has valid permission in your current SQL Server to use linked server and the second is your current windows login has valid permission on remote SQL Server.


    Iric Wen

    TechNet Community Support

    Tuesday, November 20, 2012 8:29 AM
  • Share the error message you are getting?

    you can use both sql and windows authentication for linked server.

    Check user you are using is having rights on target server or not?


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, November 20, 2012 7:00 PM
  • The SQL Server Native Client (SQLNCLI11) that ships with SQL Server 2012 does not support connections to SQL Server 2000 an earlier. I found this out during testing of our upgrade to SQL 2012. We upgraded our 2008 R2 servers in-place and it appears to have left the SQLNCLI10 provider in place but if you did a fresh install of SQL Server 2012 on a new server you may not have this provider. Does your 2012 server have the SQLOLEDB provider listed under Linked Server Providers in SSMS? If so, you could try using that.    

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Wednesday, November 21, 2012 3:49 PM
  • I have a fresh installation of SQL 2012 and tried SQLOLEDB the error persist. The linked server cannot connect to a SQL 2008 or below.

    I tried with the MSDASQL provider and I have added the linked server with this provider string. @provstr where do you specified the name of the server.

    EXEC master.dbo.sp_addlinkedserver@server=N'LinkedName',@srvproduct=N'''''',@provider=N'MSDASQL',@provstr=N'DRIVER={SQL Server};SERVER=SERVERNAME;Trusted_Connection=yes;'


    I don't tried with a high volume of data but it works.


    Ing. Ivan Paniagua Monroy MCP-MCSD-MCTS

    Tuesday, May 07, 2013 4:36 PM