Powershell and dbatools - Get-DbaAvailabilityGroup

[TrondSO]

Hi, I'm new to powershell and dbatools. Did get a recomondation on dbatools from my MS PFE. So started to play with it, and so happy I found this. Made my days :).
But I get a problem with the command: Get-DbaAvailabilityGroup
When i run it with -SqlInstance Server01, I get the following error:

[Get-DbaAvailabilityGroup] Failure | The value's length for key 'data source' exceeds it's limit of '128'.
I have tryed to move the dbatools to another disk and folder, have remembere to update the enviroment path for PS.
Nothing helps, anyone know how to fix this problem. It's not the dbatool module because it works fine in my test enviroment. So has to be somthing on my prod admin server.
That server is: Windows Server 2016 with all current windows update.
Powershell versjon:
Name Value

---

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

Tnx
Trond

[wsmelton]

That looks like it would be on the attempt to connect to the server. If you execute the command in a fresh PowerShell session, after the error run $error[0] | select * it will retrieve the full error stack message that may give you more details on why it won't connect.

[potatoqualitee]

@wsmelton I also added Get-DbatoolsError which grabs the last dbatools error

[wsmelton]

Based on the exemption that you provided the error is coming from a library that our module should not be using. Please create a new PowerShell session and load only our module into it using Import-Module command.

[wsmelton]

Our module does not use System.Data.SqlClient, we are using Microsoft.Data.SqlClient as of a handful of versions back (6+ months ago).

Loading assemblies prior to importing our module will cause unexpected results as this for our commands because if an assembly is already loaded PowerShell will not load a second version of it. Only one can exist in a given PowerShell session.

[TrondSO]

Did run Get-DbaAvailabilityGroup Server, then $error[0] 1 select *. Did get this:

writeErrorStream : True
PSMessageDetails :
Exception : System.Exception: The value's length for key 'data source' exceeds it's limit of '128'. ---> System.Exception: Error connecting to [Server]: The value's length
for key 'data source' exceeds it's limit of '128'. ---> System.Management.Automation.MethodInvocationException: Exception calling "ExecuteWithResults" with "1" argum
ent(s): "Failed to connect to server Data Source=Server;Integrated Security=True;Multiple Active Result Sets=False;Encrypt=False;Trust Server Certificate=False
;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io"." ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to conne
ct to server Data Source=Server;Integrated Security=True;Multiple Active Result Sets=False;Encrypt=False;Trust Server Certificate=False;Packet Size=4096;Applic
ation Name="dbatools PowerShell module - dbatools.io". ---> System.ArgumentException: The value's length for key 'data source' exceeds it's limit of '128'.
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& user
ConnectionOptions)
at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at Microsoft.SqlServer.Management.Common.ConnectionManager.get_SqlConnectionObject()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)
at CallSite.Target(Closure , CallSite , Object , String )
--- End of inner exception stack trace ---
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
TargetObject : Server
CategoryInfo : ConnectionError: (Server:PSObject) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Get-DbaAvailabilityGroup,Stop-Function
ErrorDetails : The value's length for key 'data source' exceeds it's limit of '128'.
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at Stop-Function, D:\Power_shell_modules\dbatools\allcommands.ps1: line 97479
at Get-DbaAvailabilityGroup, D:\Power_shell_modules\dbatools\allcommands.ps1: line 20983
at , : line 1
PipelineIterationInfo : {0, 1}

Strugling to understand this, so if someone has anything here would be very grateful :).

BrF
Trond

[TrondSO]

Did see this: "Failed to connect to server Data Source=Server;Integrated Security=True
Have checked SPN is correct, and i have SYSadmin right on the server.

[TrondSO]

Ok been digging into this. And it's my fult :(. I did only load the following Assembly's:
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",

When i did load the rest of them:

"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.Instapi ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.SqlTDiagM ",
"Microsoft.SqlServer.SString ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.Dac",
"Microsoft.SqlServer.Management.DacEnum",
"Microsoft.SqlServer.Management.Utility"

There was no problem, the Get-DbaAvailabilityGroup worked like a charme :)
Thank you so much for the replay here.
Here are the links that I did use:
https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc281962(v=sql.105)?redirectedfrom=MSDN
https://learn.microsoft.com/en-us/sql/powershell/load-the-smo-assemblies-in-windows-powershell?view=sql-server-ver16

Hope this can help othere that get's this problem :).

[wsmelton]

You should not load any assemblies for SMO when using our module. Upon import of the module into your PowerShell session we import a specific version that we have verified and tested against prior to publishing.

[TrondSO]

You are correct, did take away all the loading of assembly's. And it worked. Sorry for make my post abow, that is not correct. Tnx

[fgill-fortifieddata]

dbatools commands started throwing this error today:
Error connecting to [MyInstance]: The value's length for key 'Data Source' exceeds its limit of '128'.
The error is generating from ConnectDba-Instance.
I am running version 2.0.4 of the dbatools module.

I was running the commands from this version of PowerShell:
Name Value

---

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

I've tried uninstalling and reinstalling the module.
It was working on Friday.

A restart of the PowerShell window resolved the issue. Let me know if you need additional information.

[fgill-fortifieddata]

Name Version

---

dbatools 2.0.4
dbatools.library 2023.5.5
ISE 1.0.0.0
Microsoft.PowerShell.Management 3.1.0.0
Microsoft.PowerShell.Security 3.0.0.0
Microsoft.PowerShell.Utility 3.1.0.0
Microsoft.WSMan.Management 3.0.0.0
SQLPS 15.0

[fgill-fortifieddata]

I just hit the error again. Here is a list a loaded modules:
Name Version

---

dbatools 2.0.4
dbatools.library 2023.5.5
ISE 1.0.0.0
Microsoft.PowerShell.Management 3.1.0.0
Microsoft.PowerShell.Utility 3.1.0.0
SQLPS 15.0

[potatoqualitee]

Looks like SQLPS could be the problem. That is an unsupported scenario (using both in one session) because super weird things happen when both are loaded.

[fgill-fortifieddata]

Shawn, Thanks. Frank [Logo Description automatically generated] Frank Gill Senior Consultant Fortified Data ***@***.******@***.***> O – 980.353.3572 Emergency Production Support 1.844.282.DATA(3282) DATABASE MANAGED SERVICES | DATABASE CONSULTING SERVICES | DATA TIER ASSESSMENTS From: Shawn Melton ***@***.***> Sent: Monday, June 19, 2023 10:18 PM To: dataplat/dbatools ***@***.***> Cc: Frank Gill ***@***.***>; Comment ***@***.***> Subject: Re: [dataplat/dbatools] Powershell and dbatools - Get-DbaAvailabilityGroup (Discussion #8783) CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe. SQLPS is your issue. Can't load that module. — Reply to this email directly, view it on GitHub<#8783 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AXUZASXM45BMBINLWNMVGZLXMEI7FANCNFSM6AAAAAAVNCQZPA>. You are receiving this because you commented.Message ID: ***@***.******@***.***>>

[fgill-fortifieddata]

Chrissy, Thanks. This issue just bit me in another scenario, so I was able to knock out a longstanding problem last night. Frank [Logo Description automatically generated] Frank Gill Senior Consultant Fortified Data ***@***.******@***.***> O – 980.353.3572 Emergency Production Support 1.844.282.DATA(3282) DATABASE MANAGED SERVICES | DATABASE CONSULTING SERVICES | DATA TIER ASSESSMENTS From: Chrissy LeMaire ***@***.***> Sent: Tuesday, June 20, 2023 7:12 AM To: dataplat/dbatools ***@***.***> Cc: Frank Gill ***@***.***>; Comment ***@***.***> Subject: Re: [dataplat/dbatools] Powershell and dbatools - Get-DbaAvailabilityGroup (Discussion #8783) CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe. Looks like SQLPS could be the problem. That is an unsupported scenario (using both in one session) because super weird things happen when both are loaded. — Reply to this email directly, view it on GitHub<#8783 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AXUZASSHJFWB5ONPEH754MLXMGHRJANCNFSM6AAAAAAVNCQZPA>. You are receiving this because you commented.Message ID: ***@***.******@***.***>>