Link to home
Start Free TrialLog in
Avatar of midfde
midfdeFlag for United States of America

asked on

SQL Server Enterprise Edition Linked Server fails to contact Express Edition database.

I have two SQL servers:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)        Feb  9 2007 22:47:07        Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.2040.00 (Intel X86)        Mar 13 2006 11:20:51       Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I'll call the former XE-server and the latter EE-server
XE instance name is [igorinspiron\sqlexpress]  development
EE instance name is [pac4\sql2005] - production  
XE has linked Server [pac4\sql2005]
EE has linked server [igorinspiron\sqlexpress]
Scripts for both linked servers produced by SQL Management Studio are attached.

Script
select * from (
select 0 o, @@servername v union
select 1, suser_sname() union
select 2, db_name() union
select 3, convert(varchar(20),count(*)) from [pacratPatterns].[information_schema].[tables]
)t order by o

On EE-server returns the following
0      PAC4\SQL2005
1      pacrat
2      pacratPatterns
3      141

Same script on XE  server returns the following
0      IGORINSPIRON\SQLEXPRESS
1      pacrat
2      PacratPatterns
3      230

Script
select count(*) from [pac4\SQL2005].[pacratPatterns].[information_schema].[tables]
On XE server returns
141

However similar script on EE-server
select count(*) from [IGORINSPIRON\SQLEXPRESS].[pacratPatterns].[information_schema].[tables]
returns error message:
OLE DB provider "SQLNCLI" for linked server "IGORINSPIRON\SQLEXPRESS" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "IGORINSPIRON\SQLEXPRESS" 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 65535, Level 16, State 1, Line 0
SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF]

PacratPatterns databases on both servers have [pacrat] users with (I believe so!) sufficient privileges.
[dbo] user of [pacratPatterns] database on EE server has login name [pacrat]

[dbo] user of [pacratPatterns] database on XE server has login name [sa] but [pacrat] user is its owner.
Please help me make this statement work:
select count(*) from [IGORINSPIRON\SQLEXPRESS].[pacratPatterns].[information_schema].[tables]
Script produced on EE server:
/****** Object:  LinkedServer [IGORINSPIRON\SQLEXPRESS]    Script Date: 02/08/2008 10:15:09 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'IGORINSPIRON\SQLEXPRESS', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'IGORINSPIRON\SQLEXPRESS',@useself=N'False',@locallogin=NULL,@rmtuser=N'pacrat',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'IGORINSPIRON\SQLEXPRESS',@useself=N'True',@locallogin=N'pacrat',@rmtuser=NULL,@rmtpassword=NULL
 
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'IGORINSPIRON\SQLEXPRESS', @optname=N'use remote collation', @optvalue=N'true'
 
==================================================
 
Script produced on XE server:
 
/****** Object:  LinkedServer [PAC4\SQL2005]    Script Date: 02/08/2008 10:14:45 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'PAC4\SQL2005', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PAC4\SQL2005',@useself=N'False',@locallogin=NULL,@rmtuser=N'pacrat',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PAC4\SQL2005',@useself=N'True',@locallogin=N'pacrat',@rmtuser=NULL,@rmtpassword=NULL
 
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PAC4\SQL2005', @optname=N'use remote collation', @optvalue=N'true'

Open in new window

Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

You can't create a remote connection TO a SQL Express instance.  It's a free program for local application use.  You can however create a remote connection FROM SQL Express to SQL Standard/Enterprise.
Avatar of midfde

ASKER

Thank you, BrandonGalderisi.
Could you help me with a reference to an appropriate document on the Web?
I "googled" this topic but was unable to find this limitation on the Web.
Regards,

--Igor
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial