none
The test connection to the linked server failed - Microsoft SQL Server 2012 RRS feed

  • Question

  • Hello

    I try create a Linked Server from SQL Server 2012 to SQL Server 2000  using a DNS  with this points:

    Create an ODBC Data Source:
    - Open Control Panel, go to Administrative Tools, then “Data Sources (ODBC)”.
    - On “ODBC Data Source Administrator” window go to “System DSN” Tab.
    - Here click on Add to create a new DSN.
    - Choose “SQL Server” and click Finish.
    - On the new window, give a proper name for the Source DSN (like: NorthWind2000DSN), we will use this name while creating our Linked Server. Provide the Server name which is on SQL Server 2000, here “NorthWind”. Click Next.
    - Choose the Authentication Type, either Windows or SQL Server auth. Click Next.
    - Change the default database, not necessary. Click Next.
    - Click Finish. You will see a new DSN created under System DSN tab.

    Now, create Linked Server and provide this DSN in the @datasrc param and provide the @provider param “MSDASQL”.
    You can use the below query to create the same:

    -- Drop Existing LinkedServer [NorthWind2000]:
    EXEC master.dbo.sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
    GO
     
    -- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
    EXEC master.dbo.sp_addlinkedserver @server = N'NorthWind2000', @srvproduct=N'MSDASQL', @provider=N'MSDASQL', @datasrc = N'NorthWind2000DSN', @location=N'System';
     
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
    GO

    But when I perform the Test Connection  in SQL Sever 2012 ,Show the Next error


    The OLE DB provider "MSDASQL" for linked server "SERVER1" reported an error. Authentication failed.
    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVER1".
    OLE DB provider "MSDASQL" for linked server "SERVER1" returned message "[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ABC\SERVERTEST$'.". (Microsoft SQL Server, Error: 7399)

    anyone know one solution to resolve this issue ?

    thank you

    Tuesday, April 29, 2014 9:51 PM

Answers

  • [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ABC\SERVERTEST$'.".


    Login failed = the used account don't have permissions to logon to the other SQL Server.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, April 30, 2014 5:23 AM
  • Can you connect "SERVER1" from your SSMS using "ABC\SERVERTEST$" account ?

    If not add the users in "Server1" and try again.


    Regards, RSingh

    Wednesday, April 30, 2014 6:09 AM

All replies

  • Buenas Tardes

    Mi problema es en la creacion de Linked Server De una DB SQL Server 2012 a SQL Server 2000 , Se de antemano que el SQL Server Native Client 11.0 No tiene soporta las Bases de Datos de SQL Server 2000 pero en la web me encontre  un metodo que es creando un DNS del servidor que requiero hacer el Linked Server pero no he tenido Exito.

    Existe una alternativa para crear un Linked Server de SQL Server 2012 a SQL Server 2000 ?

    De antemano Gracias.

    Tuesday, April 29, 2014 9:22 PM
  • [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ABC\SERVERTEST$'.".


    Login failed = the used account don't have permissions to logon to the other SQL Server.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, April 30, 2014 5:23 AM
  • Can you connect "SERVER1" from your SSMS using "ABC\SERVERTEST$" account ?

    If not add the users in "Server1" and try again.


    Regards, RSingh

    Wednesday, April 30, 2014 6:09 AM