November 10, 2017 at 11:56 am
I have two SQL 2014 Enterprise machines that have an AG set up. DBASQLTST02/03. I created a linked server on DBASQLTEST03 to connect to the 02 machine. Below is how it is configured.
/* ==Scripting Parameters==
Source Server Version : SQL Server 2014 (12.0.5207)
Source Database Engine Edition : Microsoft SQL Server Enterprise Edition
Source Database Engine Type : Standalone SQL Server
Target Server Version : SQL Server 2014
Target Database Engine Edition : Microsoft SQL Server Enterprise Edition
Target Database Engine Type : Standalone SQL Server
*/
USE [master]
GO
/****** Object: LinkedServer [DBASQLTST02] Script Date: 11/10/2017 12:51:14 PM ******/
IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'DBASQLTST02')
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = N'DBASQLTST02', @srvproduct=N'', @provider=N'SQLNCLI'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBASQLTST02',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'
END
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBASQLTST02', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
I have a query that I run while connected to the 03 machine that is supposed to query the loginname from 03 and 02, but returns loginname from 03 if not on 02.
SELECT sp1.[name] AS LoginName
FROM sys.server_principals AS sp1
LEFT JOIN sys.server_role_members AS srm ON sp1.principal_id = srm.member_principal_id
LEFT JOIN sys.server_principals AS sp2 ON srm.role_principal_id = sp2.principal_id
WHERE sp1.is_fixed_role = 0 --not a server role like securityadmin, diskadmin, etc.
AND sp1.type IN('S', 'G', 'U') --SQL login, Windows Group or Windows Login
AND sp1.is_disabled = 0
EXCEPT
SELECT sp1.[name] AS LoginName
FROM DBASQLTST02.master.sys.server_principals AS sp1
LEFT JOIN DBASQLTST02.master.sys.server_role_members AS srm ON sp1.principal_id = srm.member_principal_id
LEFT JOIN DBASQLTST02.master.sys.server_principals AS sp2 ON srm.role_principal_id = sp2.principal_id
WHERE sp1.is_fixed_role = 0 --not a server role like securityadmin, diskadmin, etc.
AND sp1.type IN('S', 'G', 'U') --SQL login, Windows Group or Windows Login
AND sp1.is_disabled = 0
ORDER BY sp1.[name];
When I run this I get no results even though there is a login on 03 that is not on 02. When I run just the bottom portion, which uses the linked server to 02, it returns the data from 03 for some reason, which is why my query returns no results. When I run SELECT * FROM OPENQUERY(DBASQLTST02, 'select @@SERVERNAME' ) while on 03 it returns DBASQLTST03 and not the expected DBASQLTST02. Any ideas on what's going on?
November 10, 2017 at 1:08 pm
One guess would be maybe an alias or a host file entry pointing to the wrong IP for that server.
You could try creating the Linked server using IP of the server for the data source. You could keep the name and just use something like: EXEC master.dbo.sp_addlinkedserver
@server = N'DBASQLTST02',
@srvproduct=N''
@provider=N'SQLNCLI',
@datasrc=N'nn.nnn.nnn.nnn'
Sue
November 11, 2017 at 4:36 am
Quick question, have you tried nslookup DBASQLTST02 in command prompt?
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply