Create Link Server SQL 2005

Posted on 2011-10-04
Medium Priority
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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

Question by:Annette Wilson, MSIS
LVL 21

Accepted Solution

JestersGrind earned 1000 total points
ID: 36911772
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
ID: 36911953
Thank you GestersGrind!

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

862 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