Automation

 View Only
  • 1.  connect to SQL database

    Posted Jun 24, 2020 03:52 PM

    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'



  • 2.  RE: connect to SQL database

    Posted Jun 24, 2020 03:56 PM

    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?



  • 3.  RE: connect to SQL database

    Posted Jun 24, 2020 04:33 PM

    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



  • 4.  RE: connect to SQL database
    Best Answer

    Posted Jun 24, 2020 07:35 PM

    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'



  • 5.  RE: connect to SQL database

    Posted Jun 25, 2020 10:56 AM

    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 :smileyhappy:

    thanks again,

    Jason