Success Stories

Generate MSSQL Linked Server Scripts with Remote Users Password

When migrating SQL Server instances to the new Servers or taking backups of the Linked Servers creation scripts, it is not possible generating passwords of users which are using on Linked Server definition with usual methods.  Sometimes Linked Servers may be created a long time ago and Nobody knows the Linked user’s passwords. Changing user/passwords causes serious problems on production environments. So I want to create scripts for generating all linked server definition for eliminating these problems;

For decrypting the passwords of Linked users I used  Antti Rantasaari’s PowerShell script;

https://github.com/NetSPI/Powershell-Modules/blob/master/Get-MSSQLLinkPasswords.psm1

I saved the PowerShell script with the name: GetLinkedServerPasswords.psm1

Creating Tables for using on generating Linked Server definitions;

CREATE TABLE [dbo].[LinkedServerDetail](
 [InstanceName] [varchar](100) NULL,
 [LinkedServerName] [varchar](100) NULL,
 [LinkedUser] [varchar](100) NULL,
 [LinkedPassword] [varchar](100) NULL
) ;

CREATE TABLE [dbo].[LinkedScriptOutput](
 [cmd] [varchar](max) NULL
);

Creating Stored Procedure for decrypting Linked users with using Powershell Script;

CREATE procedure [dbo].[GetLinkedServerPasswords]
as
declare @String varchar(1000)
CREATE TABLE #cmdhell(ScriptOutput varchar(1000) null);

Insert into #cmdhell (ScriptOutput) 
EXECUTE master..xp_cmdshell 'powershell -command "import-module "D:\Blog\LinkedServerWithPassword\GetLinkedServerPasswords.psm1"; Get-MSSQLLinkPasswords "'

delete from #cmdhell where ScriptOutput is null

create table #CmdOuput(ID int identity(1,1) ,ScriptOutput varchar(1000) null)
insert into #CmdOuput(ScriptOutput) select ScriptOutput from #cmdhell

select ID,replace(replace(replace(ScriptOutput,' ','<>'),'><',''),'<>',' ') ScriptOutput
into #CmdOutput2
from #CmdOuput
where ID > 2

truncate table LinkedServerDetail

;with mycte as (
SELECT DISTINCT ID
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS LinkedPassword
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS LinkedUser
, S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS LinkedServerName
, S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS InstanceName
FROM
(
SELECT *,CAST (N'' + REPLACE(ScriptOutput, ' ', '') + '' AS XML) AS [vals]
FROM #CmdOutput2) d 
CROSS APPLY d.[vals].nodes('/H/r') S(a)
) 
 
insert into LinkedServerDetail
select InstanceName,LinkedServerName,LinkedUser,LinkedPassword from mycte;

drop table #cmdhell
drop table #CmdOuput
drop table #CmdOutput2

Create Stored Procedure for generating All Linked Servers definitions with Remote Users Password;

CREATE procedure [dbo].[GenerateLinkedServerScriptsWithPassword]
as
set nocount on;
declare @cmd varchar(MAX)
declare @ID int
declare @ID2 int
declare @name sysname, 
 @product nvarchar(128) = NULL, 
 @providername nvarchar(128) = NULL, 
 @data_source nvarchar(4000) = NULL, 
 @locationname nvarchar(4000) = NULL, 
 @provider_string nvarchar(4000) = NULL, 
 @catalogname sysname = NULL

declare @rmtsrvname_ sysname, 
 @useself_ varchar(8) = 'true', 
 @locallogin_ sysname = NULL, 
 @rmtuser_ sysname = NULL, 
 @rmtpassword_ sysname = NULL 
 
 declare @PropertyName nvarchar(100) = NULL,
 @PropertyValue nvarchar(100) = NULL

exec [dbo].[GetLinkedServerPasswords]
create table #LinkedScriptOutput(cmd varchar(MAX) NULL)

;WITH cte AS (
SELECT a.*,
 c.name as locallogin,
 b.remote_name,
 b.uses_self_credential, 
 b.local_principal_id
FROM sys.Servers a
LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id
LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id
), unp AS (

SELECT server_id,
 name,
 product,
 [provider],
 [data_source],
 [location],
 [provider_string],
 [catalog],
 CASE WHEN remote_name IS NULL THEN NULL ELSE remote_name END as rmtuser,
 CASE WHEN uses_self_credential = 0 THEN 'False' ELSE 'True' END as useself,
 CASE WHEN local_principal_id = 0 THEN NULL ELSE locallogin END as locallogin,
 Prop as PropertyName,
 CASE WHEN Props = 0 THEN 'false' ELSE 'true' END as PropertyValue
FROM (
 SELECT server_id,
 name,
 product,
 [provider],
 [data_source],
 [location],
 [provider_string],
 [catalog],
 locallogin,
 remote_name,
 uses_self_credential,
 local_principal_id,
 CAST([is_collation_compatible] as int) as [collation compatible],
 CAST([is_data_access_enabled] as int) as [data access],
 CAST([is_distributor] as int) as [dist],
 CAST([is_publisher] as int) as [pub],
 CAST([is_remote_login_enabled] as int) as [rpc],
 CAST([is_rpc_out_enabled] as int) as [rpc out],
 CAST([is_subscriber] as int) as [sub],
 CAST([connect_timeout] as int) as [connect timeout],
 CAST([collation_name] as int) as [collation name],
 CAST([lazy_schema_validation] as int) as [lazy schema validation],
 CAST([query_timeout] as int) as [query timeout],
 CAST([uses_remote_collation] as int) as [use remote collation],
 CAST([is_remote_proc_transaction_promotion_enabled] as int) as [remote proc transaction promotion]
 FROM cte
) as p
UNPIVOT (
 Props FOR Prop IN (
 [collation compatible],
 [data access],
 [dist],
 [pub],
 [rpc],
 [rpc out],
 [sub],
 [connect timeout],
 [collation name],
 [lazy schema validation],
 [query timeout],
 [use remote collation],
 [remote proc transaction promotion]
 )
) as unpvt
)

select * into #tmp1 from unp

select identity(int,1,1) as ID,T.* into #tmp2 from 
(select distinct name,product,provider,data_source,location,provider_string,catalog
from #tmp1
where name <> @@SERVERNAME) T

select identity(int,1,1) as ID,T.* into #tmp3 from 
(select distinct name,product,provider,data_source,location,provider_string,catalog,rmtuser,useself,locallogin
 from #tmp1
where name <> @@SERVERNAME) T

select identity(int,1,1) as ID,T.* into #tmp4 from 
(select distinct name,PropertyName,PropertyValue
 from #tmp1
where name <> @@SERVERNAME) T

truncate table #LinkedScriptOutput
insert into #LinkedScriptOutput values ('USE [master]')
insert into #LinkedScriptOutput values ('GO')

while exists (select * from #tmp2)
 begin
 select top (1) 
 @name = name,
 @product = product,
 @providername = provider,
 @data_source = data_source,
 @locationname = location,
 @provider_string = provider_string, 
 @catalogname = catalog
 from #tmp2

IF @product = N'SQL Server'
 select @cmd='EXEC master.dbo.sp_addlinkedserver @server = '+'N'''[email protected]+''',@srvproduct = '+ CASE WHEN @product IS NULL THEN 'NULL' ELSE 'N'''[email protected]+'''' END+ ''
 ELSE
 select @cmd='EXEC master.dbo.sp_addlinkedserver @server = '+'N'''[email protected]+''',@srvproduct = '+ CASE WHEN @product IS NULL THEN 'NULL' ELSE 'N'''[email protected]+'''' END+ '' 
 +',@provider='+CASE WHEN @providername IS NULL THEN 'NULL' ELSE 'N'''[email protected]+'''' END+ ''
 +',@datasrc='+CASE WHEN @data_source IS NULL THEN 'NULL' ELSE 'N'''[email protected]_source+'''' END+ ''
 +',@location='+CASE WHEN @locationname IS NULL THEN 'NULL' ELSE 'N'''[email protected]+'''' END+ ''
 +',@provstr='+CASE WHEN @provider_string IS NULL THEN 'NULL' ELSE 'N'''[email protected]_string+'''' END+ ''
 +',@catalog='+CASE WHEN @catalogname IS NULL THEN 'NULL' ELSE 'N'''[email protected]+'''' END+ ''

insert into #LinkedScriptOutput values (@cmd)

while exists (select * from #tmp3 where name = @name)
 begin
 select top (1) @ID = ID,
 @rmtsrvname_ = name,
 @useself_ = useself,
 @locallogin_ = locallogin,
 @rmtuser_ = rmtuser
 from #tmp3 where name = @name

IF EXISTS(select * from LinkedServerDetail where LinkedServerName = @name and LinkedUser = @rmtuser_)
 select TOP 1 @rmtpassword_ = LinkedPassword from LinkedServerDetail where LinkedServerName = @name and LinkedUser = @rmtuser_
 ELSE 
 set @rmtpassword_ = NULL

select @cmd='EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = '+'N'''[email protected]+''',@useself = '+ CASE WHEN @useself_ IS NULL THEN 'NULL' ELSE 'N'''[email protected]_+'''' END+ ''
 +',@locallogin='+CASE WHEN @locallogin_ IS NULL THEN 'NULL' ELSE 'N'''[email protected]_+'''' END+ ''
 +',@rmtuser='+CASE WHEN @rmtuser_ IS NULL THEN 'NULL' ELSE 'N'''[email protected]_+'''' END+ ''
 +',@rmtpassword='+CASE WHEN @rmtpassword_ IS NULL THEN 'NULL' ELSE 'N'''[email protected]_+'''' END+ ''
 insert into #LinkedScriptOutput values (@cmd)
 delete from #tmp3 where ID = @ID
 end
 insert into #LinkedScriptOutput values ('GO')
 while exists(select * from #tmp4 where name = @name)
 begin
 select top (1) @ID2 = ID, 
 @PropertyName = PropertyName,
 @PropertyValue = PropertyValue
 from #tmp4 where name = @name

select @cmd='EXEC master.dbo.sp_serveroption @server = '+'N'''[email protected]+''',@optname = '+ CASE WHEN @PropertyName IS NULL THEN 'NULL' ELSE 'N'''[email protected]+'''' END+ ''
 +',@optvalue='+CASE WHEN @PropertyValue IS NULL THEN 'NULL' ELSE 'N'''[email protected]+'''' END+ ''
 insert into #LinkedScriptOutput values (@cmd)
 insert into #LinkedScriptOutput values ('GO')
 delete from #tmp4 where ID = @ID2
 end

delete from #tmp2 where name = @name
 end

select * from #LinkedScriptOutput

drop table #tmp1
drop table #tmp2
drop table #tmp3
drop table #tmp4
drop table #LinkedScriptOutput

For running these scripts you have to enable DAC and xp_cmdshell on MSSQL Instance;

Use master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure 'remote admin connections', 1 
GO
sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

Also, Your Windows Account has to be Administrator on Server and sysadmin on MSSQL Instance.

Sample Output: