[Bug] Copy-DbaLinkedServer is improperly using DAC and generating error

[Franky1522]

Verified issue does not already exist?

Yes

What error did you receive?

Logon Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

Steps to Reproduce

I have a ps1 File with this command Copy-DbaLinkedServer -Source $Source -Destination $Destination

And I execute that C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File "XXX.ps1" -Source "Server1" -Destination "Server2"

I try to execute directly from PowerShell ISE and I receive error too.

Are you running the latest release?

Yes

Other details or mentions

if I execute it a second time after a short time, the alert does not go off. And if I wait 15-20 minutes to execute again, I receive the same alert.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe), Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.14393.4583
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.4583
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Feb 9 2021 12:08:50 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.7.3920.0

[andreasjordan]

The command Copy-DbaLinkedServer does not open a DAC, it uses Get-DecryptedObject which opens a DAC.

Do get more information, I need the verbose output, so please run the command with -Verbose.

One workaround might be to open a DAC to the source server and use the returned SMO for the command:

$dac = Connect-DbaInstance -SqlInstance $Source -DedicatedAdminConnection
Copy-DbaLinkedServer -Source $dac -Destination $Destination

[Franky1522]

I try your workaround, but it fails. I use -Force too.

This is Verbose: [Connect-DbaInstance] Server object passed in, will do some checks and then return the original object [Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object [Connect-DbaInstance] authentication method is 'local integrated' [Copy-DbaLinkedServers] Collecting Linked Server logins and passwords on ADMIN:Server1\Instance1. [Get-DecryptedObject] Querying service master key [Resolve-DbaNetworkName] Resolving Server1 using .NET.Dns GetHostEntry [Resolve-DbaNetworkName] Resolving XXX.XXX.XXX.XXX using .NET.Dns GetHostByAddress [Get-DbaCmObject] Configuration loaded | Cache disabled: False [Get-DbaCmObject] [Server1] Retrieving Management Information [Get-DbaCmObject] [Server1] Accessing computer using Cim over DCOM [Get-DbaCmObject] [Server1] Accessing computer using Cim over DCOM - Success [Resolve-DbaNetworkName] Resolving Server1.domain using .NET.Dns GetHostEntry [Resolve-DbaComputerName] Resolved 'Server1\Instance1' to 'Server1' [Get-DecryptedObject] Decrypt the service master key [Get-DecryptedObject] Choose the encryption algorithm based on the SMK length - 3DES for 2008, AES for 2012 [Get-DecryptedObject] Query password information from the Db. [Get-DecryptedObject] We already have a dac, so we use it. [Get-DecryptedObject] Go through each row in results Se está realizando la operación "Dropping LinkedServer1" en el destino "Server2". [Copy-DbaLinkedServers] Attempting to migrate: LinkedServer1. Se está realizando la operación "Migrating LinkedServer1" en el destino "Server2". [Copy-DbaLinkedServers] LinkedServer1 successfully copied. Se está realizando la operación "Migrating linked server identity LinkedServerUser" en el destino "Server2".

[andreasjordan]

I don't see the failure here. "LinkedServer1 successfully copied" looks like it has copied the linked server.

[Franky1522]

The process works, the problem is that I received this error on SQL Server ERRORLOG: Error 17810, Severity 20, State 2 Logon Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1] And this error generates to me an alert.

[andreasjordan]

But this could also be a different process that wants to open the DAC while dbatools is using the DAC properly.

Let's try this:

Get-Date
$dac = Connect-DbaInstance -SqlInstance $Source -DedicatedAdminConnection
Start-Sleep -Seconds 60
Copy-DbaLinkedServer -Source $dac -Destination $Destination -Verbose
Start-Sleep -Seconds 60
$dac | Disconnect-DbaInstance
Get-Date

This way the DAC is open for about two minutes, having the Copy right in the middle.

At what time do you see the message in the SQL Server ERRORLOG?

[Franky1522]

Hi, I executed that and i saw the timestamp. It seems that the problem is in the DAC Connection by powershell, not in the copy of the linked Server. I executed that and I continue with the error. Get-Date $dac = Connect-DbaInstance -SqlInstance $Source -DedicatedAdminConnection $dac | Disconnect-DbaInstance Get-Date

[andreasjordan]

When is the error reported, while opening or while closing the DAC?

Get-Date
$dac = Connect-DbaInstance -SqlInstance $Source -DedicatedAdminConnection
Start-Sleep -Seconds 60
$dac | Disconnect-DbaInstance
Get-Date

[Franky1522]

The error occurs opening the DAC.

[andreasjordan]

Is the DAC opened successfully? What is the output of $dac?

Next we should check if there is already a DAC when we try to open one.

[andreasjordan]

To list the currently connected DAC:

Invoke-DbaQuery -SqlInstance $Source -Query "SELECT connect_time, client_net_address FROM sys.dm_exec_connections where endpoint_id IN (SELECT endpoint_id FROM sys.endpoints WHERE is_admin_endpoint = 1)"

Before opening the DAC, there should not be any result.

[Franky1522]

This is the output of this:

    Invoke-DbaQuery -SqlInstance $Source -Query "SELECT connect_time, client_net_address FROM sys.dm_exec_connections where endpoint_id IN (SELECT endpoint_id FROM sys.endpoints WHERE is_admin_endpoint = 1)"
    Get-Date
    $dac = Connect-DbaInstance -SqlInstance $Source -DedicatedAdminConnection
    Write-Output $dac
    Invoke-DbaQuery -SqlInstance $Source -Query "SELECT connect_time, client_net_address FROM sys.dm_exec_connections where endpoint_id IN (SELECT endpoint_id FROM sys.endpoints WHERE is_admin_endpoint = 1)"

    $dac | Disconnect-DbaInstance
    Invoke-DbaQuery -SqlInstance $Source -Query "SELECT connect_time, client_net_address FROM sys.dm_exec_connections where endpoint_id IN (SELECT endpoint_id FROM sys.endpoints WHERE is_admin_endpoint = 1)"
    Get-Date

jueves, 7 de abril de 2022 17:37:12

ComputerName : Server1 IsAzure : False DbaInstanceName : Instance1 NetPort : 0 ConnectedAs : domain\user [...]

connect_time : 07/04/2022 17:37:13 client_net_address : ::1

SqlInstance : ADMIN:Server1\Instance1 ConnectionType : Microsoft.SqlServer.Management.Smo.Server State : Disconnected

jueves, 7 de abril de 2022 17:37:28

[andreasjordan]

Ok, if I read this correct:

• There was no DAC at the biginning at 17:37:12
• The DAC was successfully created and was there at 17:37:13
• The DAC was successfully disconnected at 17:37:28

Did you see an error in the ERRORLOG? If yes, at what time?

[Franky1522]

Yes, I see and error on ERRORLOG at 17:37:13. Maybe "Connect-DbaInstance -SqlInstance $Source -DedicatedAdminConnection" tries to create more than 1 connection like SSMS, and generates the error?

[andreasjordan]

Sorry for the delay - was on vacation. I still can not reproduce the issue in my lab. So what can we do next?

[Franky1522]

No problem, If you try to execute "Connect-DbaInstance -SqlInstance $Source -DedicatedAdminConnection", Can you check your ERRORLOG and see if you receive an error? I try it on a SQL Server Always On Cluster too and I received the same problem.

[andreasjordan]

I have done that and I receive no error.

[Franky1522]

I dont know how i can help you to replicate the problem.

[andreasjordan]

Maybe some other contributor can help you with this. @potatoqualitee - any ideas?

[ClaudioESSilva]

hi @Franky1522

do you have more than one NIC on that server?

Can you check on the error message if the IP of the "Could not connect because...[CLIENT: XXX.XXX.XXX.XXX]" entry is the same that you can see on the "The dedicated administrator connection is in use by "ad\user" on "server". [CLIENT: XXX.XXX.XXX.XXX] ?

When connecting via dbatools I get two errors (with ::1 and the other with a different XXX.XXX.XXX.XXX IP) and only after I get the success message (from a 3rd - and the common - IP).

Also interesting, when there is already one DAC open, when I try to open a connection with "new query" using SSMS I get two more error messages. The ip's are ::1 and 127.0.0.1.

[andreasjordan]

Hi @Franky1522 - I will close this for now, but we can reopen if you still want to work on this.

[Ted-Schnieders]

hi @Franky1522

do you have more than one NIC on that server?

Can you check on the error message if the IP of the "Could not connect because...[CLIENT: XXX.XXX.XXX.XXX]" entry is the same that you can see on the "The dedicated administrator connection is in use by "ad\user" on "server". [CLIENT: XXX.XXX.XXX.XXX] ?

When connecting via dbatools I get two errors (with ::1 and the other with a different XXX.XXX.XXX.XXX IP) and only after I get the success message (from a 3rd - and the common - IP).

Also interesting, when there is already one DAC open, when I try to open a connection with "new query" using SSMS I get two more error messages. The ip's are ::1 and 127.0.0.1.

I am experiencing the exact same issue. Did you ever identify the cause of the issue? SSMS is not suppose to be used to connect to the DAC as it is known to establish multiple connections. Guessing the reason two were happening is because you using both IPV4 and IPV6,

[Ted-Schnieders]

Hi @Franky1522 - I will close this for now, but we can reopen if you still want to work on this.

Can we reopen this?

[andreasjordan]

No, we have not found the root cause. As it is not reproducable on my side, there is nothing I can do.

[Ted-Schnieders]

No, we have not found the root cause. As it is not reproducible on my side, there is nothing I can do.

When you tried to reproduce it, was it a SQL Server Availability Group Configuration? I have went through this for months trying to get rid of it and have failed. I can not figure it out. It somehow ends up trying to open another connection but I am not sure why. I have 10+ AG Groups that I keep in sync with dba tools but I get noise from alert emails because this throws errors. I also have automatic collection of errors so I can ensure there are no unexpected errors happening. But with this noise it kind of defeats that purpose and makes it hard to focus on real issues. I have been focusing on this very heavily this week and really would like help figuring out how to get rid of this issue.

[Ted-Schnieders]

I have learned that if I run the command below from the Primary I experience the error. If I run it on any machine not the Primary I do not get the DAC already established error. My hypothesis is it has something to do with the DAC running on the Loopback address and running the command locally.

$dac = Connect-DbaInstance -SqlInstance $Primary -DedicatedAdminConnection; Copy-DbaLinkedServer -Source $dac -Destination $Secondary -Verbose; $dac | Disconnect-DbaInstance | Out-Null;

[andreasjordan]

Ok, I will try to setup an AG in my lab and do a test in the next days.

When you say "run the command below from the Primary" and "run it on any machine not the Primary": You run the exact same code with the same values for $Primay and $Secondary on two different maschines? So only when $Primary is localhost you get the error?

[Ted-Schnieders]

Ok, I will try to setup an AG in my lab and do a test in the next days.

When you say "run the command below from the Primary" and "run it on any machine not the Primary": You run the exact same code with the same values for $Primay and $Secondary on two different machines? So only when $Primary is localhost you get the error?

yes.

[andreasjordan]

Thanks. That might help.

[Ted-Schnieders]

I just learned more. $dac = Connect-DbaInstance -SqlInstance $Primary -DedicatedAdminConnection; Running this locally throws the same error. Leading that it has nothing to do with this, (Copy-DbaLinkedServer -Source $dac -Destination $Secondary -Verbose;)

[Ted-Schnieders]

To me it is a fundamental issue with the DAC running on the loopback address. When running locally trying to connect to the dac it uses the loopback to connect to itself but also is being picked up by the browser and sent to the 127.0.0.1, but also picking up the loopback.