connect to SQL database

[piercj2]

HI Guys,

i've searched for a PowerCLI SQL forum on here but can't find one. Please remove this it it's not the right forum but, i'm hoping someone on here may have tried this before.

I'm trying to connect to a SQL Database to verify some info before proceeding with an action.

the account i'm using has access to the DB but, connection attempts are failing.

here's what i'm trying

[string]$servername="MyDBServer.corp.com"

[string]$database="myDatabase"

[string]$sqluser="myUsername.corp.com"

[string]$sqlpassword="notMyPassword!"

$dbConnection = New-Object System.Data.SQLClient.SQLConnection

$dbConnection.ConnectionString = "server='$servername';database='$database';trusted_connection=true; user id = '$sqluser'; Password = '$sqlpassword'; integrated security='False'"

$dbConnection.Open()

Write-Verbose 'Connection established'

[LucD]

It is more of a PowerShell/SQL question.
I suspect this community would be the best fit on VMTN.

What is that your code returning?

An error? Nothing?

[piercj2]

an error

Exception calling "Open" with "0" argument(s): "Login failed for user 'jpierce'."

At line:18 char:5

+     $Connection.Open()

+     ~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

    + FullyQualifiedErrorId : SqlException

[LucD · Accepted solution]

I suspect you might be using a Windows AD account to connect.

But with Windows Authentication the connection should be different (the one in your code is for SQL Authentication).

[string]$servername = "sqlservFQDN"

[string]$database = "DBname"

[string]$ADuser = "domain\user"

[string]$ADpassword = "password"

$dbConnection = New-Object System.Data.SQLClient.SQLConnection

$dbConnection.ConnectionString = "Server=$servername;Database=$database;UID=$adUser;PWD=$adPassword;Integrated Security=true;"

$dbConnection.Open()

Write-Verbose 'Connection established'

[piercj2]

you were right Luc, was using a Windows AD Account.

after some googling i found this

“You cannot use Windows Authentication with another account unless the PowerShell window is RunAs another user.  You can use RunAs and make the PowerShell window or the Management Studio window run as that other Windows user and then connect with integrated security.

Windows users cannot be used with UserId and Password statements in the connection string or SQL Connection.  The only way to use Windows Authentication is to use Integrated Security=SSPI and it will take the currently logged on user. 

Otherwise if you use a Username and Password it uses SQL Authentication.  ”

If i run  Powershell as the Windows AD user, the connection to the SQL DB establishes.

Passing the Windows AD Credentials within the script, the connection fails.

Time for me to go get a local SQL account created

thanks again,

Jason