locked
Change to SQL authentication from Windows authentication for my PS script RRS feed

  • Question

  • Hello,

    I have tried my best with this PS script that works with no issues when run using Windows authentication, but converting to SQL authentication and testing it fails to get connected to the database but the HTML O/P generates but its of no use as the code fails to get connected to the DB and pull the results. Since i am tied with admin level access only on server level & on DB level only my SQL ID has sysadmin access, i tried modifying my code based on several blogs but i feel i am missing something.

    I have changed the Integrated security as False and core code has information to connect to specified instance , can someone help me where the mistake is as there is no script error o/p am facing and appears the connectivity is the issue due to authentication not called for correctly.

    I am new to PS scripting and i am trying my best to learn, appreciate all your assistance.

    Testing 1  (code)-

    $uid ="eee_abcde"
    $pwd = "XXXXXX"

    function Invoke-Sqlcmd2 ($server,$database,$query)
    {

    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "Server={0}; Database={1}; Integrated Security = false; User ID = $uid; Password = $pwd;"
    $Connection.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)
    $Connection.Close()
    $ds.Tables[0]
    }

    # Connect to the specified instance

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

    Testing 2 (code) 

    # 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("eee_abcde"); 

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

    Regards

    Eben

    Thursday, October 19, 2017 11:25 AM

Answers

  • This works exactly as expected.

    function GetDbStats{
    	Param(
    		$dbid,
    		$tbid,
    		$ixid,
    		$server,
    		$db,
    		$uid,
    		$pwd
    	)
    $query = @"
    select 
    	avg_fragmentation_in_percent, page_count
    from 
    	sys.dm_db_index_physical_stats($dbid, $tbid, $ixid, NULL, NULL)
    "@
    	
    	$tmplt = 'Server={0}; Database={1};User ID={2};Password={3};'
    	$connStr = $tmplt -f $server,$db,$uid,$pwd
    	$conn = New-Object System.Data.SQLClient.SQLConnection($connStr)
    	$conn.Open()
    
    	$cmd = $conn.CreateCommand()
    	$cmd.CommandText = $query
    	$rdr = $cmd.ExecuteReader()
    	$dt = New-Object system.Data.DataTable
    	$dt.Load($rdr)
    	$conn.Close()
    	$dt
    }
    $db =$srv.Databases[0]
    $tb = $db.Tables[0]
    $ix = $tb.Indexes[0]
    GetDbStats -dbid $db.ID -tbid $tb.ID -ixid $ix.ID -server 'omega\sqlexpress' -db master -uid jsmith -pwd ******


    \_(ツ)_/

    Thursday, October 19, 2017 2:40 PM

All replies

  • The connect string is as follows:

    $tmplt = 'User ID={0};Pwd={1};Initial Catalog={2};Data Source={3};'
    $connStr = $tmplt -f $userid, $password, $database, $serverinstance
    $conn = [System.Data.SQLClient.SQLConnection]::new($connStr)
    $conn.Open()
    


    \_(ツ)_/

    Thursday, October 19, 2017 12:49 PM
  • To connect to SMO in mixed mode using the universal ServerConnection object.

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo')
    $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
    $conn.ServerInstance = 'OMEGA\SQLEXPRESS'
    $conn.LoginSecure = $false
    $conn.Login = 'smith'
    $conn.Password = '(*&(*HIUH'
    
    $srv = [Microsoft.SqlServer.Management.SMO.Server]::new($conn)
    $srv.Databases
    
    $db = $srv.Databases['master']
    $logsize = ($db.logfiles | measure-object -property Size -sum).Sum/1024
    $size = $db.Size - $logsize
    write-host 'log size:' $logsize
    write-host 'data size:' $size
    $srv.ConnectionContext.Disconnect()


    \_(ツ)_/


    • Edited by jrv Thursday, October 19, 2017 1:16 PM
    Thursday, October 19, 2017 1:12 PM
  • We can also just use a standard connection object like this:

    $tmplt = 'User ID={0};Pwd={1};Initial Catalog={2};Data Source={3};'
    $connStr = $tmplt -f 'jsmith', '*********', 'Northwind', 'OMEGA\SQLExpress'
    $conn = [System.Data.SQLClient.SQLConnection]::new($connStr)
    $conn.Open()
    
    $srv = [Microsoft.SqlServer.Management.SMO.Server]::new($conn)
    $srv.Databases
    
    $db = $srv.Databases['master']
    $logsize = ($db.logfiles | measure-object -property Size -sum).Sum/1024
    $size = $db.Size - $logsize
    write-host 'log size:' $logsize
    write-host 'data size:' $size
    $srv.ConnectionContext.Disconnect()
    


    \_(ツ)_/

    Thursday, October 19, 2017 1:15 PM
  • Could you be kind enough to advice me which part of my code is to be altered, 

    is it function Invoke-Sqlcmd2 ($server,$database,$query

    or the part where i am # Connect to the specified instance as this part again calls as below

    $res = @(invoke-sqlcmd2 $server $dbname $q $uid $pwd)
              foreach ($row in $res)

    Regards

    Eben

    Thursday, October 19, 2017 1:45 PM
  • Use my code.  Yours is just not close.

    You also fail to say what you are trying to do in any clear way.

    My code accomplish a non-integrated connection with SMO which is what your code Is trying to do.

    The code does not return a dataset.  If you are trying to run a query then this is how:

    $connStr = "Server={0}; Database={1}; Integrated Security = false; User ID = $uid; Password = $pwd;"
    $conn = New-Object System.Data.SQLClient.SQLConnection($connStr)
    $conn.Open()
    
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $query
    $rdr = $cmd.ExecuteReader()
    $dt = New-Object system.Data.DataTable
    $dt.Load($rdr)
    $conn.Close()
    $dt


    \_(ツ)_/


    • Edited by jrv Thursday, October 19, 2017 1:58 PM
    Thursday, October 19, 2017 1:52 PM
  • Thank you, this is what is failing for me after i altered  and its part of an report of Fragmentation tables on a database i am trying to fetch out ( its an big code and felt not right to post the whole thing)

    ----- Changes i did on the first part of my code

    function Invoke-Sqlcmd2 ($server,$database,$query)
    {
    $tmplt = 'User ID={0};Pwd={1};Initial Catalog={2};Data Source={3};'
    $Connection.ConnectionString = $tmplt -f $userid, $password, $database, $serverinstance
    $Connection =[System.Data.SQLClient.SQLConnection]::new($connStr)
    $Connection.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)
    $Connection.Close()
    $ds.Tables[0]
    }

    --- Changes i did on the part where i am trying to connect to the database instance


    # Connect to the specified instance

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo')
    $connection = new-object Microsoft.SqlServer.Management.Common.ServerConnection
    $connection.ServerInstance = '192.168.0.44'
    $connection.LoginSecure = $false
    $connection.Login = 'eeben'
    $connection.Password = '8(($%1Oo%1'

     
    # Get only the databases for the instance, and iterate through them
        $dbs = $srv.Databases | Where-Object {($_.Name -like "XXXX*") -or ($_.Name -like "XXXX*")}
    foreach ($db in $dbs) {
     # Check to make sure the database is not a system database, and is accessible
     if ($db.IsSystemObject -ne $True -and $db.IsAccessible -eq $True) {
       # Store the database name for reporting
       $dbname = $db.Name
       $dbid = [string]$db.ID
        
           $tbs = $db.Tables
       foreach ($tb in $tbs) {
         # Store the table name for reporting
         $tbname = $tb.Name
         $tbid = [string]$tb.ID
          
             $ixs = $tb.Indexes
         foreach ($ix in $ixs) {
           # We don't want to process XML indexes or disabled indexes
          if (($ix.IsXmlIndex -eq $False) -and ($ix.IsDisabled -eq $False)) {
             # Store the index name for reporting
             $ixname = $ix.Name
             $ixid = [string]$ix.ID

                  # Get the Fragmentation and page count information 
              $q = @"
     select avg_fragmentation_in_percent, page_count
     from sys.dm_db_index_physical_stats($dbid, $tbid, $ixid, NULL, NULL)
    "@ 
              $res = @(invoke-sqlcmd2 $server $dbname $q $uid $pwd)
              foreach ($row in $res)
              {
                $frval = $row.avg_fragmentation_in_percent
                $pgcnt = $row.page_count  
                writeServiceinfo $EVDatabase $server $dbname $tbname $ixname $frval $pgcnt
              }
            }
            }
          }
     
        }
      }
    Out-File $EVDatabase -InputObject "</table>"  -Append
    }

    Thursday, October 19, 2017 2:03 PM
  • Sorry but I cannot figure out what you are trying to do.  You have to pick one small piece of code and post it with the erros.

    Please  use the code I posted as your code is not correct.  Why are you using SMO for aquery?

    Where did you get this code?  Can you at least try to format and post it correctly.


    \_(ツ)_/

    Thursday, October 19, 2017 2:08 PM
  • It was from an blog

    https://vox.veritas.com/t5/Downloads/SQL-Database-Fragmentation-Level-Email-Reporting/ta-p/811611

    It does work with windows authentication without an issue, on trying to get it working with SQL authentication facing the issue.

    I will come with the error which for now i am strangely not getting.. 


    Thursday, October 19, 2017 2:15 PM
  • Well if that was the blog code in the  blog was pretty bad.  Yes it can work but you must have the database in mixed mode.

    The code is also for a very old version of SMO.  The method I posted works much better.


    \_(ツ)_/

    Thursday, October 19, 2017 2:22 PM
  • Thank you
    Thursday, October 19, 2017 2:35 PM
  • This works exactly as expected.

    function GetDbStats{
    	Param(
    		$dbid,
    		$tbid,
    		$ixid,
    		$server,
    		$db,
    		$uid,
    		$pwd
    	)
    $query = @"
    select 
    	avg_fragmentation_in_percent, page_count
    from 
    	sys.dm_db_index_physical_stats($dbid, $tbid, $ixid, NULL, NULL)
    "@
    	
    	$tmplt = 'Server={0}; Database={1};User ID={2};Password={3};'
    	$connStr = $tmplt -f $server,$db,$uid,$pwd
    	$conn = New-Object System.Data.SQLClient.SQLConnection($connStr)
    	$conn.Open()
    
    	$cmd = $conn.CreateCommand()
    	$cmd.CommandText = $query
    	$rdr = $cmd.ExecuteReader()
    	$dt = New-Object system.Data.DataTable
    	$dt.Load($rdr)
    	$conn.Close()
    	$dt
    }
    $db =$srv.Databases[0]
    $tb = $db.Tables[0]
    $ix = $tb.Indexes[0]
    GetDbStats -dbid $db.ID -tbid $tb.ID -ixid $ix.ID -server 'omega\sqlexpress' -db master -uid jsmith -pwd ******


    \_(ツ)_/

    Thursday, October 19, 2017 2:40 PM
  • Simply Great..
    Thursday, October 19, 2017 4:26 PM