piercj2
Enthusiast
Enthusiast

connect to SQL database

Jump to solution

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'

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
LucD
Leadership
Leadership

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'


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

0 Kudos
4 Replies
LucD
Leadership
Leadership

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?


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
piercj2
Enthusiast
Enthusiast

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

0 Kudos
LucD
Leadership
Leadership

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'


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

0 Kudos
piercj2
Enthusiast
Enthusiast

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 Smiley Happy

thanks again,

Jason

0 Kudos