Create Link Server SQL 2005

Posted on 2011-10-04
Last Modified: 2012-05-12
Does anyone have a good reference for creating link servers in SQL 2005?  I'm getting the following error and don't understand why.

TITLE: Microsoft SQL Server Management Studio

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


The OLE DB provider "SQLNCLI" for linked server "MYServer" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "MyServer".
OLE DB provider "SQLNCLI" for linked server "MyServer" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)

For help, click:

Question by:Annette Wilson, MSIS
    LVL 21

    Accepted Solution

    This is a security issue.  When you create the linked server, on the security tab, you define how you are going to connect to the remote server.  You can either setup mappings in the top portion of the screen or define how all connection will behave at the bottom.  I usually select the latter because it's less to maintain.  If you are using Windows accounts that have access to both servers, use "Be made using the login's current security context".  If you are using SQL accounts or using a Windows account that doesn't have access to the remote server, use "Be made using this security context" and define a SQL user name and password on the remote server.  



    Author Closing Comment

    by:Annette Wilson, MSIS
    Thank you GestersGrind!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now