Post reply

power my PS script to automatically connect via SQL authentication and execute the command

  • October 6, 2017 at 6:44 am

    #352104

    Hi ,
    I have an working PS script that works via windows authentication so it appears. I have a specific server where i am denied windows authentication on the SQL server level but provided with an SQL server ID and password and as DBA sysadmin access.

    I need to amend my PS script to connect via the SQL login ID and password and establish a connection as i will be scheduling this script via an task scheduler or an SQL agent job.  Appreciate any of you can  assist me with establishing the connection to the database with the SQL ID and password , i am posting part of the script for your review 

    ----
    function Invoke-Sqlcmd2 ($server,$database,$query)
    {
        $conn=new-object System.Data.SqlClient.SQLConnection
        $conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $Server,$Database
        $conn.Open()
        $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
        $cmd.CommandTimeout=$QueryTimeout
        $ds=New-Object system.Data.DataSet
        $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
        [void]$da.fill($ds)
        $conn.Close()
        $ds.Tables[0]
    }

    # Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
    $v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
    if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
    }

    # Connect to the specified instance
        $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server

    Regards and Wishes
    Eben

  • October 6, 2017 at 6:49 am

    #1962606

    This should get you going, though hard-coding username/password in a script is not good practice, as I'm sure you are aware.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 6, 2017 at 8:11 am

    #1962628

    Phil Parkin - Friday, October 6, 2017 6:49 AM

    This should get you going, though hard-coding username/password in a script is not good practice, as I'm sure you are aware.

    Yes very much right it is not the right thing..thanks for sharing me the link 
    Let me try to get this tested but still i am not progressing and feel the SQL authentication part is still not working. When i execute my script i am not getting an error but the o/p generating the expected fragmentation report from the database is not coming.

    # Connect to the specified instance
     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    $srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"

    #This sets the connection to mixed-mode authentication
    $srv.ConnectionContext.LoginSecure=$false;

    #This sets the login name
    $srv.ConnectionContext.set_Login("abc_XXXX");

    #This sets the password
    $srv.ConnectionContext.set_Password("defghijkl")

    Regards
    Eben

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply