Cannot Connect To 2000 From 2005 Via Linked Server
Dec 29, 2006
Interesting issue.. I think
I have 2 linked servers on my dev 2005 instance (sp1-win 2003). My 2005 instance shares the physical server with a 2000 instance. This linked server works fine. The second linked server is on a separate server running sql 2000 sp3. I cannot connect to this server at all. I can connect the other way -- from the 2000 to the 2005. I have tried all kinds of combinations of security and options but nothing works. The only resolutions I have found referenced installing Management Studio on the 2000 server or creating an ODBC connection from the 2005 instance. Any ideas???
Here is the error I get:
OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" 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.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??
I am trying to Query text file into SQL Server 2005 using linked server. In SQL Server 2000 here is what I have. Linked server configured to use following parameters Product Name : Customer_file Provider Name: Customer_File ( System DSN)
Hello, I have been searching the archives for information on problems that occur with creating a linked server to SQL Server 2000 from SQL Server 2005 x64, but the problem I am having seems to be slightly different.
I was able to create the linked server from the 2005 server this way;
I receive 'Command(s) completed successfully' for both. When I attempt to run either of the following queries from a connection to the 2005 server in Management Studio;
select top 100 * from 2kServer.database.dbo.table select * from Openquery(2kServer,'select top 10 * from database.dbo.table')
I get the following error
OLE DB provider "SQLNCLI" for linked server "2kServer" 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 18456, Level 14, State 1, Line 0 Login failed for user 'domaincdun2'.
I've seen a couple of ideas on this. One states the following; *************************************** When running 4 part reference query like this: select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server: exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:
create procedure sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null as declare @Result int set @Result = 0 exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference. ************************************** I put this proc on the 2kServer and tried again, but I got the same error. I've also seen this:
I went into the Sql Server Configuration Manager, Sql Native Client Configuration -> Protocols and disabled Shared Memory and made the TCP/IP protocol #1 in order. Then just restarted the Sql Service and it all started working!
I don't have access to the server to do this, but I wonder if it would solve the problem. I am a sys admin on both servers. The servers are in two different domains. Could this be a firewall issue?
Can anyone tell me if there's a recommended way to link from a SQL 2000 SP4 DB through a firewall to SQL 2005? I have tried with SQLOLEDB but this uses NETBios which is not secure. Or is there a way to tell it not to use NETBios? SET @provdetail = 'DRIVER={SQL SERVER};SERVER=<IP address>;Trusted_connection=Yes' EXEC Master..sp_addlinkedserver @server = ,'<IP address>', @srvproduct = '', @provider = 'SQLOLEDB', @provstr = @provdetail , @catalog = 'TestDB', @datasrc ='<IP address>DB1'
I'm trying to connect to another slq server db through a linked server (and synonyms) but I get the following error.
Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. ,Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "BURT". A nested transaction was required because the XACT_ABORT option was set to OFF. , at Westfalia.TDMN.TxObjects.TxBurtOCIInterface.BurtOCIPalletChangedRecords_Get() , --- End of inner exception stack
I've set up MSDTC and executed the procedures noted in MS's article ID 873160. I am using 2005, but the Burt server is 2000. I can run the stored procedure from my server, which queries theirs. But when I execute the sp from a service, I get the error. Any help would be greatly appreciated.
I plan to migrate a SQL 2000 instance with everything in it to another SQL2000 server, and eventually in the future to SQL 2005.
I've pretty much figured out how to migrate everything execept the linked server configuration (there're many linked servers configured in the old server with different security credentials). I'm wondering if there's way I can save the linked server configuration and restore it to the other server.
I am using VS .net 2005 to develop a web application with sql server 2000, but the SQL servrer 2000 is setup on other computer,so, my web server is setup on A machine and SQL servr is setup on B machine.I got a problem when i try to connect to SQL server through the Web form as following error."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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" I had try to setup the MDAC 2.8 on my Web Server, but i got a setup error sth like "MDAC 2.8 cannot install on your machine because it is not support your windows version.........." Why i can't connect to SQL 2000 through the Web server??? It is a urgent case, Please help thx a lot.
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success. Any help is appreciated.
I recently upgraded from SQL Server 2000 to SQL Server 2005. I have Oracle databases I need to share data with. I was using the Heterogeneous Services (hsodbc) from Oracle and connected to SS 2000 fine. Since I've upgraded, I can't use hsodbc to connect to my SS 2005 database. I get the following error:
ORACLE GENERIC GATEWAY Log File Started at 2006-09-06T09:40:07
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint SET @Value = 22 SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
Hi I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" 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 18456, Level 14, State 1, Line 0 Login failed for user 'abc'. ------ Error Message Ends
Consider login name is abc. Now this login abc has sysadmin rights on sql server 2005. The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options; 1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 . 2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help. Regards, Salman Shehbaz.
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong. Thank you
Hi all.I am working on asp.net 2.0 with back end as sqlserver 2000. but when trying to connect to the database i am getting the following error displayed 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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: 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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): 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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +115 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +1093 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +1083 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +272 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +351 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +82 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +558 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +126 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +651 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +160 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +122 System.Data.SqlClient.SqlConnection.Open() +229 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +114 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +225 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +157 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +68 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +100 System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +100 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +113 System.Web.UI.WebControls.Login.AttemptLogin() +178 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +134 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +107 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +178 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3837
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42 can anyone help??????????? regardspravallika
i Update SQL Server 2005 SP1 to SQL Server 2005 SP2 but cannot connect SQL Server 2000 SP4. Please Help Me Please
===================================
Cannot connect to sia09-cxdb01cxdb01.
===================================
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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
Hi All!!! In my Computer has MS SQL Server 2000(with default Instance) and now I Install MS SQL server 2005(with Instance name: SQL2005 ). Now I want to write Web Application to connect to the both databases (SQL Server 2000 and MS SQL Server 2005), How to Add connection string in my web.config to do it. Help me ??? Thanks & Regards,
Hi,i have SQL 2000 and 2005 on same machine(with different intance names,of course), my laptop - XP with SP2. The 2005 works fine but i can'tconnect on SQL 2000. All the the SQL services are started.Any idea? Have i to reinstall 2000?Tks,Lourival
select name As Name, sAMAccountName As UserName, title As Title, physicalDeliveryOfficeName As Office, telephoneNumber As PhNumber, mobile As CellPhone, facsimileTelephoneNumber As FaxNumber, mail As Email,employeeid as emp_no from openquery ( ADSI,'SELECT name, sAMAccountName, title, physicalDeliveryOfficeName, mobile, facsimileTelephoneNumber, telephonenumber, mail ,employeeid FROM ''LDAP://fp1-srvr.sidf.gov.sa'' WHERE objectCategory = ''Person''AND objectClass = ''user'' ')
on sql server 2000 it was working but when migration to sql server2005 I got the following error when trying to excute on query analyzer:
Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT name, sAMAccountName, title, physicalDeliveryOfficeName, mobile, facsimileTelephoneNumber, telephonenumber, mail ,employeeid FROM 'LDAP://fp1-srvr.sidf.gov.sa' WHERE objectCategory = 'Person'AND objectClass = 'user' " for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
I have a SQL 2005 server (SQLA) on which I have created a linked server to a microsoft Access DB (LinkedAccessDB)
When I login (or remote desktop) directly into SQLA and run the following query, it works just fine:
select * from LinkedAccessDB...Table1
I also have SQL server 2005 installed on my workstation (WKSTA) and when I bring up Management Studio and connect to SQLA and try to run the previous query, I get the following error message:
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LinkedAccessDB".
I've researched articles for the better part of a day, and haven't found anything that would help. Any ideas would be much appreciated.
NOTE: My workstation, the SQL Server (SQLA) and the file server where the AccessDB is located are all in the same Domain.
I have a utility server that I am running SS2K5 SP2 w/ the latest patches.
It has numerous Linked Server to both SS2K and SS2K5 servera already in place and working great.
I scripted out (numerous times) a Link Server create statement for a SS2K5 server that is working great and then changed the server name in the script to reflect the new server name and executed it.
It DID created the linked server BUT when it finished up it generated the following message:
================ ERROR TEXT BEGIN ======================
TITLE: Microsoft SQL Server Management Studio ------------------------------ "The test connection to the linked server failed." ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).". OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.". (Microsoft SQL Server, Error: 7303) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
============= ERROR TEXT END ==============
Now when I try to open the Catalogs object under the newly created Linked Server, I get the following message each time I try to open it:
================ ERROR TEXT BEGIN ======================
TITLE: Microsoft SQL Server Management Studio ------------------------------ Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". (Microsoft SQL Server, Error: 7303) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
============= ERROR TEXT END ==============
Here is the code that I used as a template (and which is from a SS2K5 server that is working fine)
Now I have masked the real values in this post of the @provstr string for obvious reasons and the real Linked Server object has all the correct parameters set.
I have 2 severs. Server A uses sql 2005 and Server B uses sql 2000. I want to create sql sever registration on Server B connecting to Server A. But it saied 'To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)' Any idea to solve it? thanks!
I have a dts package on SS2000 which loops through connections to a list of databases using activex:
set srv = createobject("SQLDMO.SQLServer") srv.LoginSecure = True set gpkg = DTSGlobalVariables.parent set tserv = gpkg.GlobalVariables ("Server") -- where "Server" contains the name of an instance. srv.Connect(tserv.value)
This works fine when connecting to ss2000 instances, but not with ss2005 instances. How can I connect to ss2005 instances too??? There must be a way! I don't have time to rewrite the package to IS.
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides. But when i try and run a sql statement on the sql 2000 side i get this error: [DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation. If i try and run a swl statment on the sql server 2005 side the procedure works. Has anyone experienced the same problem?Any advice?
We have two sql servers using Windows authentication. One is sql 2000 the other sql 2005. When logged in as "sa", I can link these servers and run a SELECT distributed query without any problem.
But when one of our developers runs the same query (he is "dbo" of each database on each of the servers) he gets the error:
"OLE DB Provider "SQLNCLI" for linked server <Servername> returned message "Communication Link Failure" ... Login failed for user '(null'). reason: Not associated with a trusted SQL Server connection"
Despite that, I, as "sa" equivalent, have no problem so it appears to be a permisisons issue. Any ideas what is going on here?
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a sql statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Domain = dm Windows User = dmTestUser, member of sysadmin on SQLFL and SQLNY SQL Server 2012 = SQLFL SQL Server 2012 = SQLNY Table = Product (exists in SQLNY and SQLFL) SP = spGetProduct() (resides on SQLFL) SQL Login = dbuser (on SQLNY, member of dbo on Product table)
I created a linked server on SQLFL pointing to SQLNY. The local server login to remote login mapping is as follows:
Local Login = dmTestUser Remote Login = dbuser
with "Be made without security context" option selected. Run spGetProduct() to get Product records from SQLNY and populate Product table on SQLFL via the linked server.Here are tests I did:I connected to SQLFL using dmTestUser in SSMS. I manually executed the stored procedure spGetProduct() without any issues. Procedure gets Product records from SQLNY and populates the Product table on SQLFL. Everything is good.
I created a SQL Agent job called "Get NY Product" on SQLFL to execute spGetProduct(). I invoked this agent job and it executed successfully.However no data was loaded to the Product table on SQLFL. My stored procedure caught this error: Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "SQLNY".
1. When sql agent job runs, which account does it use? Is the SQL Server Service or SQL Agent?I believe it's the sql agent. 2. What do I need to do to make this work?
