Windows 10 下PowerShell安装 SqlServer module的步骤及问题解决

微软于今年发布了SQL Server 2017 on Linux的版本，在Linux上安装好了SQL Server 2017，微软的官方提供了如下几种方式远程连接到数据库实例：
SQL Server Management Studio (SSMS)
Windows PowerShell
SQL Server Data Tools (SSDT)

重点来谈一下如果通过PowerShell连接(后面简称PS)。

下载并安装SSMS

PowerShell 默认是包括在SSMS中的，所以我们仍然要下载SSMS并安装它(注意选择语种平台，否则下来后安装会提示语种不兼容，导致安装失败)。

导入SQL Server module到PS

按照微软的官方说法，从SSMS的17.0版本开始，SQL Server PowerShell module 不再包含在SSMS中，而是迁移在了PowerShell Gallery。那么我们需要从PS module库中导入SQL Server module。需要做如下一些事情

确认NuGet 是否在PackageProvider中

PS C:\windows\system32> Get-PackageProvider -ListAvailable

Name                     Version          DynamicOptions
----                     -------          --------------
msi                      3.0.0.0          AdditionalArguments
msu                      3.0.0.0
PowerShellGet            1.0.0.1          PackageManagementProvider, Type, Scope, AllowClobber, SkipPublisherCheck, ...
Programs                 3.0.0.0          IncludeWindowsInstaller, IncludeSystemComponent

如果不在其中，那么需要获取NuGet

PS C:\windows\system32> Install-PackageProvider NuGet -Verbose
VERBOSE: Using the provider 'Bootstrap' for searching packages.
VERBOSE: Finding the package 'Bootstrap::FindPackage' 'NuGet','','','''.
VERBOSE: Performing the operation "Install Package" on target "Package 'nuget' version '2.8.5.208' from
'https://oneget.org/nuget-2.8.5.208.package.swidtag'.".

会提示是否同意安装非信任的包，输入‘yes’

The package(s) come(s) from a package source that is not marked as trusted.
Are you sure you want to install software from 'https://oneget.org/nuget-2.8.5.208.package.swidtag'?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): yes
VERBOSE: Installing the package 'https://oneget.org/nuget-2.8.5.208.package.swidtag'.
VERBOSE: Installed the package 'nuget' to 'C:\Program
Files\PackageManagement\ProviderAssemblies\nuget\2.8.5.208\Microsoft.PackageManagement.NuGetProvider.dll'.

Name                           Version          Source           Summary
----                           -------          ------           -------
nuget                          2.8.5.208        https://onege... NuGet provider for the OneGet meta-package manager

安装SQL Server module

PS C:\windows\system32> Install-Module -Name SqlServer

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its
InstallationPolicy value by running the Set-PSRepository cmdlet. Are you sure you want to install the modules from
'PSGallery'?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): yes

导入SQL Server module

PS C:\windows\system32> Import-Module SqlServer

如果在导入的过程中，出现了如下的错误，提示UnauthorizedAccess

Import-Module : File C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17199\SqlServerPostScript.ps1 cannot be
loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at
https:/go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ Import-Module SqlServer
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : SecurityError: (:) [Import-Module], PSSecurityException
    + FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand

执行如下命令，并在提示时输入yes

PS C:\windows\system32> Set-ExecutionPolicy RemoteSigned

Execution Policy Change
The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose
you to the security risks described in the about_Execution_Policies help topic at
https:/go.microsoft.com/fwlink/?LinkID=135170. 

Do you want to change the execution policy?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): yes

再次执行导入命令，应该会成功了，可以查看导入结果

PS C:\windows\system32> Import-Module SqlServer
PS C:\windows\system32> Get-Module -Name SqlServer

ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Manifest   21.0.17199 SqlServer                           {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvail...

测试导入结果

按照官网的提示，连接远程数据库实例

PS C:\windows\system32> $serverInstance = 10.213.22.186
PS C:\windows\system32> $credential = Get-Credential

点击回车后，会弹出登录窗口，输入远程数据库的用户名和密码，ps会自动保存到session中

cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential

之后创建数据库连接对象并访问

# Load the SMO assembly and create a Server object
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $serverInstance

# Set credentials
$server.ConnectionContext.LoginSecure=$false
$server.ConnectionContext.set_Login($credential.UserName)
$server.ConnectionContext.set_SecurePassword($credential.Password)

# Connect to the Server and get a few properties
$server.Information | Select-Object Edition, HostPlatform, HostDistribution | Format-List
# done

会返回选择的结果信息

Edition          : Express Edition (64-bit)
HostPlatform     : Linux
HostDistribution : Ubuntu

参考连接

https://docs.microsoft.com/en...
https://docs.microsoft.com/en...
https://docs.microsoft.com/en...