通过PowerShellPlus示例脚本学习PowerShell-连接SQLServer数据库

## =====================================================================
## Title       : Connect-MSSQL-IPSQLAuth
## Description : Connect to $ServerName using SQL Server authentication.
##               This connection is not encrypted. 
##               User ID and Password are transmitted in plain text.
## Author      : Idera
## Date        : 1/28/2009
## Input       : -ipAddress < xxx.xxx.xxx.xxx | xxx.xxx.xxx.xxx\instance >
##               -verbose 
##               -debug	
## Output      : Database names and owners
## Usage			: PS> .\Connect-MSSQL-IPSQLAuth -ipAddress 127.0.0.1 -verbose -debug
## Notes 		: In SQL Server 2008, you must enable SQL Authentication and
##               the SQL account you are using (sa is disabled by default)
##	Tag			: MSSQL, connect, IP, SQL Authentication
## Change Log  :
##  4/1/2009 - Revised SMO Assemblies 
##  4/1/2009 - Added input prompts and write-verbose statements
## =====================================================================
 
param
(
  	[string]$ipAddress = "$(Read-Host 'IP Address' [e.g. 127.0.0.1])",
	[switch]$verbose = $true,
	[switch]$debug = $false
)

function main()
{
	if ($verbose) {$VerbosePreference = "Continue"}
	if ($debug) {$DebugPreference = "Continue"}

	Write-Verbose "Connect to $ServerName using SQL Server authentication..."
	Connect-MSSQL-IPSQLAuth $ipAddress
}

function Connect-MSSQL-IPSQLAuth($ipAddress)
{
	trap [Exception] 
	{
		write-error $("TRAPPED: " + $_.Exception.Message);
		continue;
	}
	
	# Load SMO assemblies
	[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Management.Common" );
	[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.SmoEnum" );
	[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" );
	[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.SmoExtended " );
	
	$smoServer = new-object( 'Microsoft.SqlServer.Management.Smo.Server' ) ($ipAddress)

	# The connection will use SQL Authentication, so set LoginSecure to FALSE
	$smoServer.ConnectionContext.set_LoginSecure($FALSE)
	
	# Pop a credentials box to get User Name and Password
	$LoginCredentials = Get-Credential
	
	# If the user does not specify a domain, UserName will begin with a slash.
	# Remove leading slash from UserName
	$Login = $LoginCredentials.UserName -replace("\\","")
	
	# Set properties of ConnectionContext
	$smoServer.ConnectionContext.set_EncryptConnection($FALSE)
	$smoServer.ConnectionContext.set_Login($Login)
	$smoServer.ConnectionContext.set_SecurePassword($LoginCredentials.Password)
	
	# The connection is established the first time you access the server's properties.
	cls
	Write-Host Your connection string contains these values:
	Write-Host
	Write-Host $smoServer.ConnectionContext.ConnectionString.Split(";")
	Write-Host
	
	# List info about databases on the instance.
	Write-Host "Databases on $ipAddress "
	Write-Host
	foreach ($Database in $smoServer.Databases) 
	{
		write-host "Database Name : " $Database.Name
		write-host "Owner         : "	$Database.Owner
		write-host
	}
}

main

本脚本使用IP地址，使用SQL验证登录到SQLServer服务器。

57行有一个新命令Get-Credential，命令主要是弹出框输入用户名和密码进行验证。信息如下：

SYNTAX
    Get-Credential [[-Credential] <pscredential>] [<CommonParameters>]

    Get-Credential [[-UserName] <string>] [-Message <string>] [-Title <string>] [<CommonParameters>]


PARAMETERS
    -Credential <pscredential>

        Required?                    false
        Position?                    0
        Accept pipeline input?       false
        Parameter set name           CredentialSet
        Aliases                      None
        Dynamic?                     false
        Accept wildcard characters?  false

    -Message <string>

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           MessageSet
        Aliases                      None
        Dynamic?                     false
        Accept wildcard characters?  false

    -Title <string>

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           MessageSet
        Aliases                      None
        Dynamic?                     false
        Accept wildcard characters?  false

    -UserName <string>

        Required?                    false
        Position?                    0
        Accept pipeline input?       false
        Parameter set name           MessageSet
        Aliases                      None
        Dynamic?                     false
        Accept wildcard characters?  false

    <CommonParameters>
        This cmdlet supports the common parameters: Verbose, Debug,
        ErrorAction, ErrorVariable, WarningAction, WarningVariable,
        OutBuffer, PipelineVariable, and OutVariable. For more information, see
        about_CommonParameters (https://go.microsoft.com/fwlink/?LinkID=113216).


INPUTS
    None


OUTPUTS
    System.Management.Automation.PSCredential


ALIASES
    None