无法使用域凭据从PowerShell连接到SQL Server [英] Cannot connect to SQL Server from PowerShell with domain credentials

问题描述

我有一个问题，无法使用域凭据连接到SQL Server.使用SA凭据，它可以正常工作，并且查询将按预期运行.但是，当我使用域凭据时，会出现错误.

I have a problem where I cannot connect to a SQL Server using domain credentials. Using the SA credentials it works and the query runs as expected. But when I use domain credentials, I get an error.

这是脚本:

$SQLServer = 'server.domain.com'
$SQLDBName = 'DBname'
$username  = "DOMAIN\user"
$password  = "password"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

#--> With SA, it works, with DOMAIN creds, it does not

#$SqlConnection.ConnectionString = "Server=$SQLServer; Database=$SQLDBName; User ID=sa; Password=SApassword;"
$SqlConnection.ConnectionString = "Server=$SQLServer; Database=$SQLDBName; User ID=$username; Password=$password;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = 'select count (*) from my.table'
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

这是我运行脚本时遇到的错误:

And here is the error I get when I run the script:

使用"1"参数调用"Fill"的异常:与网络相关或特定于实例的错误发生在 建立与SQL Server的连接.服务器未找到或无法访问.验证实例名称正确，并且已将SQL Server配置为允许远程连接. (提供者:命名管道提供者，错误:40-无法打开与SQL Server的连接)

Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

在C:\ scripts \ PowerShell \ myscript.ps1:28 char:1
+ $ SqlAdapter.Fill($ DataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo:未指定:(:) []，MethodInvocationException
+ FullyQualifiedErrorId:SqlException

At C:\scripts\PowerShell\myscript.ps1:28 char:1
+ $SqlAdapter.Fill($DataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException

我应该指出一个事实，我正在从不在域中的计算机连接到SQL Server(因此，我无法使用任何类型的身份验证通过).

I should point out the fact that I am connecting to a SQL Server from a computer which is NOT in the domain (therefore I cannot use any sort of authentication passthrough).

关于为何无法使用域凭据进行连接的任何想法?我关注了其他文章，这些文章提供了有关检查连接，防火墙等的建议.所有这些都按工作顺序进行，并且脚本以sa(本地)用户的身份完美运行.只是不在域上.

Any ideas as to why I cannot connect with my domain credentials? I've followed other posts which offer advice on checking connections, firewalls etc. That's all in working order and the script runs perfectly as the sa (local) user. Just not on the domain..

推荐答案

如果您有SQL凭据，请使用以下代码:

If you have SQL Credentials use something like this:

$ConnectionString = server=*serverName*;database=*databaseName*;user id=*userName*;password=*passWord*;

如果您具有域凭据，请使用以下内容:

If you have Domain Credentials use something like this:

$ConnectionString = "server=*serverName*;database=*databaseName*;user id=*domain\username*;password=*passWord*;trusted_connection=true;"

这在我的环境中有效.当然，之后您可以这样做:

This works in my environment. Of course after that you do:

$Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)

这篇关于无法使用域凭据从PowerShell连接到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！