We are unable to get linked servers from 2005 to 2000 working. I was on the phone with Microsoft for 4 hours Friday with no luck.

Here are the configurations.

Server1 - Windows Server 2003 - SP1 & SQL Server 2000 SP3a (MSDTC enabled)
Server2 - Windows Server 2003 - SP1 & SQL Server 2005 SP1 (MSDTC enabled)

Ran instcat.sql on SQL2000 server.


Here are the problems.

- Linked servers created mapping SA on local and remote servers work as expected
- Can only create linked server if using the actual server name. Giving it a different name will not work.
- Can't connect to SQL 2000 servers mappings login ids other then SA
- Can't create linked servers through GUI without my options being changed by the GUI. Server name and Database name disappears.
- User receives permission error about xp_prop_oledb_provider when connecting remotely, however when connecting locally (on the server) the user receives an error about needing sysadmin permission.
- Can link to another 2005 instance, but not using GUI
- Surface Area Configuration shows only TCP/IP, not named a combination of TCP/IP and named pipes. Chanaged to use the combination, but problems still occur.


Any ideas?

Thanks, Dave

Tried registering the linked server using IP Address?
http://sqlserver2000.databases.aspfaq.com/how-do-i-prevent-linked-server-errors.html a good one to under linked server issues.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Same result. If I select the security option "For a login not defined in the list above, connections will: Not be Made", I receive the error below.

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?"
------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)

If I don't map local and remote login ids and instead select "Be made using this security context:" I get this error...

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?"
------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI" for linked server "TEST_LINK" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "TEST_LINK" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 53)


Microsoft is still trying to reassign my case to another technician.

Its a security issue to allow the logins, goto the Security page (still on Linked Server Properties). Select 'Be made using this security context'. Type a login and password that exists on the remote server.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

to solve linked server issues sql2005 to sql2000 sp3/sp4.

KB ref athttp://support.microsoft.com/default.aspx?scid=kb;en-us;906954

Alzdba,

Microsoft and I saw that and tried running instcat.sql despite the problem occuring on both 32-bit and 64-bit machines. It didn't fix the problem.

Satya,

Using 'Be made using this security context' means that any valid SQL Server login id on the local server can then utilize the linked server. I need to map a local login to a remote login to restrict who can access the remote server.

Microsoft and I determined the main issue is with xp_prop_oledb_provider, but they do not know why I am receiving Error 229 - "EXECUTE permssion denied... on this proc. If I grant the local user dbo of master the problem goes away, but I don't want to do that.

My comment "-Can only create linked server if using the actual server name. Giving it a different name will not work." is not exactly correct. The linked server gets created, but an error occurs during the creation indicating that the connection could not be tested. I believe in SQL 2000 the connection was not tested by SQL Serer during creation of the linked server, however in 2005 SQL Server tries to test the connection by running sp_testlinkedserver. If the sysadmin account creating the linked server is not listed in the "local server login to remote serer login mappings:" list, the following message appears:

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

Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)

Thanks, Dave

Here's an update. There appears to be a bug with Windows XP - SP2 and SP2 of SQL Server 2005. I requested a PC be built with nothing but the O/S to rule out Anti-virus and other software. I installed SQL Server 2005 tools, with no SP, and successfully created and executed a linked server between SQL Server 2005 and SQL Server 2000. Applying SP1 made no difference, however applying SP2 caused the GUI to display linked servers incorrectly by showing a plus sign before the linked server and if clicked, displaying a folder named "catalogs" in the linked server tree below the linked server. This behavior is similar to that of SQL 2000, but according to Microsoft should not occur in 2005. The other problem that occurs involves creating a linked server. When giving the linked server a name other then the actual server name, selecting the "SQL Native Client" provider, mapping a local (non-sysadmin) login to a remote (non-sysadmin) login and then selecting "For a login not defined...connections will:" "Not be Made", an error occurs when SQL Server tries to test the linked server during the behind-the-scenes creation process. If chosing to ignore the error and create the linked server it will work when using Query Analyzer, but don't try to click on "Catalogs" in the GUI since that will produce a permissions error pertaining to an extended stored procedure.

Microsoft confirmed the problem and continues to research the issue, however the engineer is leaving for a short vacation and won't get back to me until Monday.

This isn't the first bug I've found in SQL 2005. Needless to say it's a bit frustrating debugging this product.

Hope this information helps someone.

Dave

Hi Dave, I can confirm this linked server issue, but not with sql2005 to sql2000.[?]
We still have an issue connection using an ODBC dsn to oracle RDB on vms.[xx(]

My SQL2005 server are at level :
Microsoft SQL Server 2005 - 9.00.3152.00 (X64)
Mar 3 2007 05:22:48
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

So they also have Cumulative HofFix for sp2(build 3152).

I tried to open up the linked server dialog by clicking New Linked Server and got the following error msg:

"Cannot show reauested dialog
Additional information: cannot find table 0. (System Data)"

Any ideas?

Dennis

Dennis
I suggest to create a new thread for your problem, not hijacking from the problem above.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

sorry for that. I created a new one already.

Dennis

Hi Dave - yep your post has certainly helped as I am hitting the exact same error message however its on setup of a SQL 2005 to 2005 linked server connection (no SQL 2000 in the mix at all).

My servers are running SQL 2005 x86 EE SP2 Build 3054.

In my situation the error occurs with the linked server defined to use "current security context" and the login that is using the linked connection is a standard SQL login (not Windows auth).

Using the linked server works when making a T-SQL call with a standard user - but not when the standard used is logged in with SSMS - which is where I get the same error as you have described.

Thanks for your post!

Rolf

 

hmm interesting thread (So it works in run in query but then user cannot click the catalog as you get this xp_prop_oledb_provider error).

Any cure for this.

I am having the same problem trying to link a SQL 2005 cluster to a remote SQL 2000 cluster.  So far the only thing that I've gotten to work is mapping a sysadmin account on one server to a sysadmin account on the other, but only the master database can be queried.

Have you gotten any more info on this?

my problem:

create linked server from SQL Server 2005 (64bit) to SQL Server 2000 (32bit)

to avoid the error:

"The EXECUTE permission was denied on the object 'xp_prop_oledb_provider'"

you can try this on the SQL Server 2005:

GRANT EXECUTE ON [dbo].[xp_prop_oledb_provider] TO [public]

now, if I try to use the linked server, I get the error:

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server

you can fix it with the "instcat.sql" on the SQL Server 2000 on the cmd-shell (you need admin rights):

osql -E -i "C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql"

feel free to modify the path to "instcat.sql"

now the linked server works well

Sandor