0

I'm trying to connect to a Azure SQL server (under a different subscription) using SQLPS

Auth method is SQL server auth.

When I try to import-module SQLPS, I get this error:

Error: import-module : The specified module 'sqlps' was not loaded because no valid module file was found in any module directory.

While I'm able to connect to the same Azure SQL server from windows powershell, I have no clue why I'm not able to perform the same on Azure Powershell. I understand that appropriate .psm1 modules should exist for powershell to understand this, but can someone help me on how to do it?

I have tried exporting/importing the module from

C:\Program Files\Microsoft SQL Server\120\Tools\PowerShell\Modules

and didn't workout too...

Niranjan Raguraman
  • 120
  • 1
  • 6

2 Answers2

0

Just to answer this question on 2 possible solutions I found:

  1. You can use Azure custom script extension to inject the script into the VM that that you want to restore to. The script will then run locally on the VM which means the SQL PS will be available.

  2. You can setup a VM in Azure as a Hybrid worker which then becomes your "Azure Automation machine". You can then install SQLPS or any module that you require on that server.

Gee1983
  • 37
  • 5
-1

The error you are getting is simply PowerShell telling you that it probled all the folders specified in your $env:PSModulePath (i.e. %PSMODULEPATH% env variable) and it did not find the SQLPS module (its manifest file, basically).

I am not clear how you are "exporting/importing" the SQLPS. Could you elaborate on that?

On a clean machine without SQLPS (i.e. with no SQL Engine nor SQL Management Studio), I would just rely on the SQL Feature Pack to get SQLPS. If this is what you are trying to accomplish, you can just go to https://www.microsoft.com/en-us/download/details.aspx?id=42295 and download the following MSIs (pick the architecture of your OS): - SQLSysClrTypes.mai - SharedManagementObjects.msi - PowerShellTools.msi Install them in that order. Then, when you open a PowerShell window, the $env:PSModulePath should be set correctly and you should be able to just say "Import-Module SQLPS -DisableNameChecking" and get going with SQLPS.

Please, let me know if this is not what you are trying to do or you are running into other issues. If you give me more details, I might be able to help you out.

Thanks, -Matteo

Matteo Taveggia
  • 41
  • 4
  • Hmm, i have no issue with Windows Powershell. I currently have a powershell script that queries a Azure database and sends an email with the results of the query... I'm trying to implement the same as a azure run book and schedule it... And when I try to import sqlps module, i get the above said error – Niranjan Raguraman Apr 18 '16 at 20:40
  • Exporting/importing - I'm just copying the file from the path and import it in to azure modules – Niranjan Raguraman Apr 18 '16 at 20:41
  • Oh, so you are copying "C:\Program Files\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS" from one machine to another? If that's the case you have two problems: you need to make sure that the destination path (say X:\SQLPSModule) is added to the $env:PSModulePath env variable (this is easy). Then you have a bigger problem: all the referenced assemblies that SQLPS uses (SMO, etc) will need to be on the target machine as well. – Matteo Taveggia Apr 18 '16 at 20:57
  • Yes, exactly. If it were a machine running windows, we could have installed the feature pack. But since this is Azure Automation(powershell), I don't have a clue on how to proceed further. – Niranjan Raguraman Apr 18 '16 at 21:57