Linked servers on an instance all pointing to the local instance.
-
We have around 4 linked servers on an instance, each one of them pointing to a remote instance. These were set up months back. Today I browsed their catalog in ssms and all the linked servers are now showing the local instances catalog. Has anyone seen anything remotely like this? Very weird.
- jcourtjr 29296 - Monday, June 18, 2018 2:24 PM
If you think browsing the catalogs changed the linked servers, you can query sys.servers. There is a last_modified date which would be updated.
Otherwise, the properties of the linked servers would be listed in sys.servers. Maybe it was a display issue with SSMS. Rely on the information in sys.servers.Sue
- Sue_H - Monday, June 18, 2018 5:09 PM
I queried sys.servers and everything looks fine. Queries and SSMS are showing the local instance databases.
-
I can't find anyone with this problem. This is a very bad bug if that is what it is. Linked servers haven't been touched in over a year. sys.servers is not showing anything other than the server name, (no datasource configured). I can perform linked server queries with sqlcmd or ssms and we still only see the local instance through the linked server. I know its not a dns issue because I can take one of the those remote servers and connect via ssms and everything seems fine. I can create new linked servers and they seem to work fine. I am sure I can delete and recreate these but I want to avoid that for my curiosity purposes.
- jcourtjr 29296 - Tuesday, June 19, 2018 6:54 AM
I've never heard of anyone browsing the catalog of a linked server and losing all provider information either - and Linked Servers have been used for over 20 years.
Uninstalling the provider being used is the only time I've seen that happen. Open a case with Microsoft if you really think it's a bug - you aren't charged if it's a bug.Sue
-
Someone hasn't put entries in the local hosts file on the server have they? We use that sometimes to override DNS when we want to do testing without affecting anything else.
- CC-597066 - Wednesday, June 20, 2018 5:35 PM
Checked that already. Here is more fun. We have another server where the same behavior is happening, but now I can recreate it. I can add a linked server via SSMS and put in a fake server name and it successfully adds. Then the local databases show up in the catalog. Here is a screenshot, I just added the linked server which obviously does not exist.
- jcourtjr 29296 - Thursday, June 21, 2018 6:58 AMCC-597066 - Wednesday, June 20, 2018 5:35 PM
Checked that already. Here is more fun. We have another server where the same behavior is happening, but now I can recreate it. I can add a linked server via SSMS and put in a fake server name and it successfully adds. Then the local databases show up in the catalog. Here is a screenshot, I just added the linked server which obviously does not exist.
Could you post the scripts so that we can try to reproduce it as well?
I have a couple of SQL Server 2014 that I can test it on...so same version. I should hit the same bugSue
- Sue_H - Thursday, June 21, 2018 7:04 AMjcourtjr 29296 - Thursday, June 21, 2018 6:58 AMCC-597066 - Wednesday, June 20, 2018 5:35 PM
Checked that already. Here is more fun. We have another server where the same behavior is happening, but now I can recreate it. I can add a linked server via SSMS and put in a fake server name and it successfully adds. Then the local databases show up in the catalog. Here is a screenshot, I just added the linked server which obviously does not exist.
Could you post the scripts so that we can try to reproduce it as well?
I have a couple of SQL Server 2014 that I can test it on...so same version. I should hit the same bugSue
USE [master]
GO
/****** Object: LinkedServer [HISQLSERVERCENTRAL] Script Date: 6/21/2018 12:21:07 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HISQLSERVERCENTRAL', @srvproduct=N'', @provider=N'SQLNCLI'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HISQLSERVERCENTRAL',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'HISQLSERVERCENTRAL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
-
Additionally if I specify the fake server name for @datasrc. It creates the linked server but when we try to browse it will error, which seems normal.
- jcourtjr 29296 - Thursday, June 21, 2018 10:48 AM
Most things default to the local instance when you don't define a data source.
Even if you use sqlcmd and don't provide the server, it defaults to local. That is the expected behavior. The issue is from not specifying any data source as it will just default to local. At least for this script.Sue
- Sue_H - Thursday, June 21, 2018 10:55 AM
Yeah, I don't like that behavior in this case. Could lead to some interesting screw-ups.
- jcourtjr 29296 - Thursday, June 21, 2018 11:14 AM
Yes I think I'd prefer an error if a data source isn't specified in any circumstance but unfortunately, it's not required. You can see that in sys.servers - data source can be null. It doesn't make a lot of sense to have it that way but I would guess it's to support some process that creates or uses linked servers.
Sue
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply