December 5, 2006 at 3:42 am
We run in a mixed environment of Intel 32-bit SQL 2000 and AMD 64-bit SQL 2005 servers. SQL 2000 runs on SP3a or SP4. SQL 2005 runs on RTM or SP1, and I get the same problem regardless of patch level.
I am unable to create a successful linked server *from* SQL 2005 *to* SQL 2000. I can do it the other way, plus I can link from SQL 2000 to SQL 2000 easily.
I have tried using Windows authentication, impersonation, manually entering a username and password, connection strings, and so on, and every time I get an error saying either:
- 'Msg 7399, Level 16, State 1, Line 1. The OLE DB provider "SQLNCLI" for linked server "MYSERVER" reported an error. The provider did not give any information about the error.' This is usually followed by a message about schemas not being visible, which kind of makes sense if it can't connect to the server.
OR
- OLE DB provider "SQLNCLI" for linked server "MYSERVER" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I have tried creating DSN entries, and they all work. osql works. I can ping the 2000 machine from the 2005 one. Registering the server and connecting via SQL Query Analyzer works.
The 2005 server has mixed mode enabled. No matter which user I try to connect with (sa, domain user), it doesn't work.
Basically, I can connect to the SQL 2000 server in any way EXCEPT via a linked server.
I want to pull my hair out. Please help.
December 5, 2006 at 8:00 am
Hi Randolph,
I had the same problem a while ago. Check out this link:
http://support.microsoft.com/?id=906954
Running instcat.sql which is included in SP4 for SQL 2000 solves the problem.
Markus
[font="Verdana"]Markus Bohse[/font]
December 5, 2006 at 9:43 am
Hi Markus
I will check it out tomorrow and let you know the outcome. I'm sitting in Johannesburg so it's 6:40pm right now.
Randolph
December 5, 2006 at 10:12 pm
Hi Markus
It did not work. I still get the same error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "MYSERVER" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "MYSERVER". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
This error is identical whether I use "sa" or Windows authentication.
December 6, 2006 at 1:32 am
Try using the SQL OLE DB driver, instead of the SQL Native Access Client driver. I don't know how well the native client works to SQL 2000.
Nice to see another South African here
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2006 at 1:43 am
I've tried that. It also didn't work.
December 6, 2006 at 1:49 am
ODBC? Since you say the DSN worked. Not ideal, I know, but as a work around....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2006 at 7:14 am
ODBC won't work either - Microsoft provides a 32-bit DLL for that, but SQL is running 64-bit. Like I said, everything works EXCEPT linked servers, no matter what I've tried.
Thanks though, Gail.
December 6, 2006 at 2:18 pm
How are your linked servers set up? More specifically, how is your security set up and what options are enabled?
We were running a mix like yours for a long time without any issues with linked servers.
Also, please paste in the output from Exec sp_helpserver 'LinkedserverName'
December 6, 2006 at 10:30 pm
Output for your question:
name,network_name,status,id,collation_name,connect_timeout,query_timeout
MYSERVER,MYSERVER,data access,use remote collation,1,NULL,0,0
From Management Studio, I try:
1. New Linked Server
2. Choose a name, e.g. MYSERVER. This matches the server name on the network.
3. Server Type: it doesn't matter whether I choose "SQL Server" or *any* of the other options. In SQL 2000, I can get away with "SQL Server" and it works. Any of the providers and associated settings do not work.
4. Product name, Data Source, Provider String, Location and Catalog: this can be filled in 12 different ways and it still doesn't work.
5. Security: I can use current security context (works on 2000), a specific remote username and password (also works on 2000), or with mappings (with or without impersonation) and still nothing (although it does work on 2000).
6. Server Options: Again, this can also be configured many ways. None of these options make any difference. They work in 2000, not in 2005.
7. The network itself is running AD 2003, the servers have been configured for mixed authentication, and I can try either of these without success (though, again, it works in 2000).
8. The 2000 server has been upgraded from SP3a to SP4 without any difference (except that OPENQUERY no longer works, but that's another story for my MDX forum), and yet, I can still link to other 2000 servers from that SP4 box.
9. SQL 2005 has been upgraded to SP1 as well, also with no real effect, except that I had to fix a CryptoAPI problem with Integration Services and crl.microsoft.com. Again, this is off-topic.
10. Incidentally, I can easily link 2005 to 2005, and 2000 to 2005, and 2000 to 2000, but NOT 2005 to 2000, which is my current problem.
11. Before I forget, I did play around in the Local Security Policy relating to Impersonation, but this didn't make a difference.
Thanks for your interest. I look forward to your response.
December 6, 2006 at 11:08 pm
Odd. I've got a very similar setup (Itanium 2005 connecting to x86 SQL 200) with no problems.
Are you a premier customer with MS? If so, it might be worth contacting PSS with the problem.
Can you ping from the 2005 server to the 2000?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2006 at 4:26 am
When you installed SP4, did you run the instcat.sql script that comes with the service pack? That has to be executed separately after the service pack installation. It upgrades the server's catalog. Not executing the script can cause this error.
Not running instcat.sql can also cause OpenQuery to fail.
It's explained in section 3.6 of the SP4 installation directions:
December 7, 2006 at 7:19 am
Yes - I can ping the 2000 server from 2005 (see original post).
I did also run instcat.sql yesterday already.
I think Microsoft PSS is the best option for now.
December 11, 2006 at 1:00 pm
Randolph,
I have the same issue you’re experiencing, and have tried everything mentioned in the prior posts. When you have a resolution please post your fix. Also, I will be pursuing a resolution with Microsoft PSS and will do like wise.
Thank You…
January 2, 2007 at 5:27 pm
Mann I am running into this same problem... does anyone have a solution... my last hope is to call MS... please help... thanks
Moe C
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply