How to fix missing Microsoft.SqlServer.Management.CloudAdapter.Data for SQL Server PowerShell Module?

0
votes

When I use the SQL Server PowerShell module, I get an error that an assembly is missing.

I installed SqlServer version 21.0.17240 and SQL Server Management Studio v17.6.

Import-Module SqlServer
Get-SqlInstance -MachineName $serverInstance -Credential $credential

Error:

Get-SqlInstance : Die Datei oder Assembly "Microsoft.SqlServer.Management.CloudAdapter.Data, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" oder eine Abhängigkeit davon wurde nicht gefunden. Das System kann die angegebene Datei nicht finden.
In Zeile:2 Zeichen:9  
+         Get-SqlInstance -MachineName $serverInstance -Credential $cre ...  
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
+ CategoryInfo          : NotSpecified: (:) [Get-SqlInstance], FileNotFoundException  
+ FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.SqlServer.Management.PowerShell.IaaS.GetSqlInstanceCommand

What do I need to do to fulfill the requirements?

1
sql-serverpowershellssms

1 Answers

3
votes

NOTE: I'm reading 21.0.17240 as the version of the SqlServer module not your instance of "SQL Server".

Get-SqlInstance requires the SQL Server Cloud Adapter be installed. According to MSDN:

The Get-SqlInstance cmdlet gets a SQL Instance object for each instance of SQL Server that is present on the target computer. If the name of the instance of SQL Server is provided, the cmdlet will only get this specific instance of SQL Server.

You must ensure that you run SQL Server Cloud Adapter on the computer that hosts the instance of SQL Server.

The service was built to support SQL Server hosted on Azure Virtual Machines. Even if your machines are on-prem., you'll need this service or the commandlet won't work.

My two cents, consider using SMO if you want instance information about a SQL Server instance. Google and you'll find tons of material on using SMO to pull data from SQL Server:

powershell smo

Now, to your problem, I don't believe Microsoft shipped the SCA for SQL Server 2016 or 2017 either with the engine or in a feature pack, which would mean this commandlet is effectively broken for those releases as stated here. If you're using 2014, you can download the SCA with a feature pack. Maybe you can use the 2014 SCA with a later version of SQL Server, but I don't see that supported anywhere.