使用 PowerShell 管理对 SQL Server 的身份验证Manage authentication to SQL Server in PowerShell

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

默认情况下, SQL ServerSQL Server PowerShell 组件在连接到 数据库引擎Database Engine实例时使用 Windows 身份验证。By default, the SQL ServerSQL Server PowerShell components use Windows Authentication when connecting to an instance of the 数据库引擎Database Engine. 你可以通过定义 PowerShell 虚拟驱动器,或者通过为 Invoke-Sqlcmd 指定 -Username 和 -Password 参数,来使用 SQL Server 身份验证 。You can use SQL Server Authentication by either defining a PowerShell virtual drive, or by specifying the -Username and -Password parameters for Invoke-Sqlcmd.

备注

SQL Server PowerShell 模块有两种;SqlServer 和 SQLPS 。There are two SQL Server PowerShell modules; SqlServer and SQLPS.

SqlServer 模块是当前要使用的 PowerShell 模块。The SqlServer module is the current PowerShell module to use.

虽然 SQL Server 安装附带了 SQLPS 模块(用于实现后向兼容性),但该模块不再更新。The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.

SqlServer 模块不仅包含 SQLPS 更新版本的 cmdlet,还包含新的 cmdlet 以支持最新的 SQL 功能 。The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.

PowerShell 库安装 SqlServer 模块。Install the SqlServer module from the PowerShell Gallery.

有关详细信息,请参阅 SQL Server PowerShellFor more information, see SQL Server PowerShell.

权限Permissions

您可以在 数据库引擎Database Engine 实例中执行的所有操作都受到授予用于连接到该实例的身份验证凭据的权限的控制。All actions you can perform in an instance of the 数据库引擎Database Engine are controlled by the permissions granted to the authentication credentials used to connect to the instance. 默认情况下, SQL ServerSQL Server PowerShell 和 cmdlet 将使用其运行所基于的 Windows 帐户来建立与 数据库引擎Database Engine的 Windows 身份验证连接。By default, the SQL ServerSQL Server provider and cmdlets use the Windows account under which it is running to make a Windows Authentication connection to the 数据库引擎Database Engine.

若要建立 SQL ServerSQL Server 身份验证连接,您必须提供 SQL Server 身份验证登录 ID 和密码。To make a SQL ServerSQL Server Authentication connection you must supply a SQL Server Authentication login ID and password. 在使用 SQL ServerSQL Server 提供程序时,必须将 SQL ServerSQL Server 登录凭据与虚拟驱动器关联起来,然后使用更改目录命令 (cd) 连接到该驱动器。When using the SQL ServerSQL Server provider, you must associate the SQL ServerSQL Server login credentials with a virtual drive, and then use the change directory command (cd) to connect to that drive. 在 Windows PowerShell 中,安全凭据只能与虚拟驱动器关联。In Windows PowerShell, security credentials can only be associated with virtual drives.

使用虚拟驱动器进行 SQL Server 身份验证SQL Server Authentication Using a Virtual Drive

创建与 SQL Server 身份验证登录相关联的虚拟驱动器To create a virtual drive associated with a SQL Server Authentication login

  1. 创建一个函数,该函数:Create a function that:

    1. 具有针对为虚拟驱动器提供的名称、登录 ID 以及要与虚拟驱动器相关联的提供程序路径的参数。Has parameters for the name to give the virtual drive, the login ID, and the provider path to associate with the virtual drive.

    2. 使用 read-host 来提示用户输入密码。Uses read-host to prompt the user for the password.

    3. 使用 new-object 来创建凭据对象。Uses new-object to create a credentials object.

    4. 使用 new-psdrive 来创建具有提供的凭据的虚拟驱动器。Uses new-psdrive to create a virtual drive with the supplied credentials.

  2. 调用函数来创建具有提供的凭据的虚拟驱动器。Invoke the function to create a virtual drive with the supplied credentials.

示例(虚拟驱动器)Example (Virtual Drive)

此示例创建名为 sqldrive 的函数,您可使用该函数来创建与指定 SQL ServerSQL Server 身份验证登录名和实例相关联的虚拟驱动器。This example creates a function named sqldrive that you can use to create a virtual drive that is associated with the specified SQL ServerSQL Server Authentication login and instance.

sqldrive 函数提示您输入登录名的密码,并在您键入密码时屏蔽密码。The sqldrive function prompts you to enter the password for your login, masking the password as you type it in. 然后,每当你使用更改目录命令 (cd) 来通过使用虚拟驱动器名称连接到路径时,所有操作都将通过使用你在创建驱动器时提供的 SQL ServerSQL Server 身份验证登录凭据来执行。Then, whenever you use the change directory command (cd) to connect to a path by using the virtual drive name, all operations are performed by using the SQL ServerSQL Server Authentication login credentials that you supplied when you created the drive.

## Create a function that specifies the login and prompts for the password.  
  
function sqldrive  
{  
    param( [string]$name, [string]$login = "MyLogin", [string]$root = "SQLSERVER:\SQL\MyComputer\MyInstance" )  
    $pwd = read-host -AsSecureString -Prompt "Password"  
    $cred = new-object System.Management.Automation.PSCredential -argumentlist $login,$pwd  
    New-PSDrive $name -PSProvider SqlServer -Root $root -Credential $cred -Scope 1  
}  
  
## Use the sqldrive function to create a SQLAuth virtual drive.  
sqldrive SQLAuth
  
## Set-Location to the virtual drive, which invokes the supplied authentication credentials.  
sl SQLAuth:

使用 Invoke-Sqlcmd 的 SQL Server 身份验证SQL Server Authentication Using Invoke-Sqlcmd

将 Invoke-Sqlcmd 用于 SQL Server 身份验证To use Invoke-Sqlcmd with SQL Server Authentication

  1. 使用 -Username 参数可以指定一个登录 ID,以及用于指定关联密码的 -Password 参数 。Use the -Username parameter to specify a login ID, and the -Password parameter to specify the associated password.

示例 (Invoke-Sqlcmd)Example (Invoke-Sqlcmd)

此示例使用 read-host cmdlet 来提示用户输入密码,然后使用 SQL Server 身份验证进行连接。This example uses the read-host cmdlet to prompt the user for a password, and then connects using SQL Server Authentication.

## Prompt the user for their password.  
$pwd = read-host -AsSecureString -Prompt "Password"  
  
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance" -Username "MyLogin" -Password $pwd  

另请参阅See Also