• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Powershell output to SQL

so I have some file servers I'm auditing ACLS in, the file output is too large for CSV, So someone recommended using SQL, so i have 2012 express installed on a server, I created a database and created a table with the headers represented in the script below:

$ErrorActionPreference = "Continue"
$strComputer = $env:ComputerName
$colDrives = Get-PSDrive -PSProvider Filesystem
ForEach ($DriveLetter in $colDrives) {
    $StartPath = "E:\Share"
    Get-ChildItem -LiteralPath $StartPath -Recurse |
    ForEach {
      $FullPath = Get-Item -LiteralPath (Get-Item -LiteralPath $_.PSPath)
      (Get-Item -LiteralPath $FullPath).GetAccessControl() |
      Select * -Expand Access |
      Select @{N='Server Name';E={$strComputer}},
             @{N='Full Path';E={$FullPath}},
             @{N='Type';E={If($FullPath.PSIsContainer -eq $True) {'D'} Else {'F'}}},
             @{N='Owner';E={$_.Owner}},
             @{N='Trustee';E={$_.IdentityReference}},
             @{N='Inherited';E={$_.IsInherited}},
             @{N='Inheritance Flags';E={$_.InheritanceFlags}},
             @{N='Ace Flags';E={$_.PropagationFlags}},
             @{N='Ace Type';E={$_.AccessControlType}},
             @{N='Access Masks';E={$_.FileSystemRights}} } |


Could someone help with the steps that would allow the results of this script to write to that DB?
0
leadtheway
Asked:
leadtheway
  • 10
  • 6
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
I don't have your specific answer but I found out this free ebook a few minutes ago: https://www.syncfusion.com/resources/techportal/details/ebooks/powershell
0
 
Dustin SaundersDirector of OperationsCommented:
I would write a function and then call that for each object after you collect the information.  Edit the connection string and table name here, but this code will do the trick:

$connectionString = "server='localhost';database='testDatabase';trusted_connection=true;"
$tableName = "FilePermissions"

function WriteSQL ($query)
{
    if ($debug -eq $true) {Write-Host $query}
    $Connection = New-Object System.Data.SqlClient.SqlConnection
    $Connection.ConnectionString = $connectionString
    $Connection.Open()
    $Command = New-Object System.Data.SqlClient.SqlCommand
    $command.Connection = $Connection
    $command.CommandText = $query
    $command.ExecuteReader()
    $connection.Close()
}

$ErrorActionPreference = "Continue" 
$strComputer = $env:ComputerName 
$colDrives = Get-PSDrive -PSProvider Filesystem 
ForEach ($DriveLetter in $colDrives) { 
    $StartPath = "C:\test" 
    Get-ChildItem -LiteralPath $StartPath -Recurse | 
    ForEach { 
      $FullPath = Get-Item -LiteralPath (Get-Item -LiteralPath $_.PSPath) 
      (Get-Item -LiteralPath $FullPath).GetAccessControl() | 
      Select * -Expand Access | 
      Select @{N='ServerName';E={$strComputer}}, 
             @{N='FullPath';E={$FullPath}}, 
             @{N='Type';E={If($FullPath.PSIsContainer -eq $True) {'D'} Else {'F'}}}, 
             @{N='Owner';E={$_.Owner}}, 
             @{N='Trustee';E={$_.IdentityReference}}, 
             @{N='Inherited';E={$_.IsInherited}}, 
             @{N='InheritanceFlags';E={$_.InheritanceFlags}}, 
             @{N='AceFlag';E={$_.PropagationFlags}}, 
             @{N='AceType';E={$_.AccessControlType}}, 
             @{N='AccessMasks';E={$_.FileSystemRights}} } |

             %{
                $query = "INSERT INTO $tableName (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,acetype,accessmasks) VALUES ('$($_.servername)','$($_.fullpath)','$($_.type)','$($_.owner)','$($_.trustee)','$($_.inherited)','$($_.inheritanceflags)','$($_.aceflag)','$($_.acetype)','$($_.accessmasks)')"
                WriteSQL $query
             }

    }
             

Open in new window


Here is the table I created to test with:
USE [testDatabase]
GO

/****** Object:  Table [dbo].[FilePermissions]    Script Date: 11/1/2016 1:52:17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FilePermissions](
	[servername] [nvarchar](MAX) NULL,
	[fullpath] [nvarchar](MAX) NULL,
	[type] [nvarchar](MAX) NULL,
	[owner] [nvarchar](MAX) NULL,
	[trustee] [nvarchar](MAX) NULL,
	[inherited] [nvarchar](MAX) NULL,
	[inheritanceflags] [nvarchar](MAX) NULL,
	[aceflag] [nvarchar](MAX) NULL,
	[acetype] [nvarchar](MAX) NULL,
	[accessmasks] [nvarchar](MAX) NULL
) ON [PRIMARY]

GO

Open in new window

0
 
leadthewayAuthor Commented:
that's very helpful, even fixed and issue i had with my table.  My question is where you have
$query = "INSERT INTO $tableName (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,acetype,accessmasks) VALUES ('$($_.servername)','$($_.fullpath)','$($_.type)','$($_.owner)','$($_.trustee)','$($_.inherited)','$($_.inheritanceflags)','$($_.aceflag)','$($_.acetype)','$($_.accessmasks)')"
                WriteSQL $query

Open in new window


How does it make that connection to the sql server?  Sorry if it seems like a dumb question..I am trying my best to learn as I go with this stuff.  Great info on here
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
leadthewayAuthor Commented:
oh wait i didn't see connectionString at first...sorry
0
 
Dustin SaundersDirector of OperationsCommented:
The first line in the code is:
$connectionString = "server='localhost';database='testDatabase';trusted_connection=true;"
$tableName = "FilePermissions"

Open in new window


That connection string tells it where to connect.  In the function:

function WriteSQL ($query)
{
    if ($debug -eq $true) {Write-Host $query}
    $Connection = New-Object System.Data.SqlClient.SqlConnection
    $Connection.ConnectionString = $connectionString
    $Connection.Open()
    $Command = New-Object System.Data.SqlClient.SqlCommand
    $command.Connection = $Connection
    $command.CommandText = $query
    $command.ExecuteReader()
    $connection.Close()
}

Open in new window


The line $Connection.ConnectionString tells the script where to write the data.
0
 
leadthewayAuthor Commented:
ok when running i get :

Exception calling "Open" with "0" argument(s): "Cannot open database "Audit" requested by the login. The login failed.
Login failed for user 'ENTERPRISE-1\admin.myuser'."
At C:\IT Scripts\AuditFilePermissions_single path.ps1:12 char:5
+     $Connection.Open()
+     ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
connection's current state is closed."
At C:\IT Scripts\AuditFilePermissions_single path.ps1:16 char:5
+     $command.ExecuteReader()
+     ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
0
 
Dustin SaundersDirector of OperationsCommented:
Sounds like you don't have permission to the database-- go to 'Security\Users' and and add your user in to the database permissions.
0
 
leadthewayAuthor Commented:
my user is listed under dbo
0
 
Dustin SaundersDirector of OperationsCommented:
Is your user (ENTERPRISE-1\admin.myuser) mapped to the database?

The error states:
Exception calling "Open" with "0" argument(s): "Cannot open database "Audit" requested by the login. The login failed.

Which is why the connection can't open.  Is your login in the database Security container or the Instance Security container?
0
 
leadthewayAuthor Commented:
0
 
leadthewayAuthor Commented:
0
 
Dustin SaundersDirector of OperationsCommented:
What happens if you run the script from an admin powershell window?
0
 
leadthewayAuthor Commented:
thats how i'm running it, also firewall on sql turned off
0
 
Dustin SaundersDirector of OperationsCommented:
Hmm...  Could you edit the connection string to use a SQL login like SA or a custom one to test?
Server=[SERVER];Database=[DATABASE];Uid=[USER];Pwd=[PASSWORD] 

Open in new window

0
 
leadthewayAuthor Commented:
ill try that this morning and report back
0
 
leadthewayAuthor Commented:
ok i adjusted the string, i also added the instance because just putting servername was still giving me log in error  so now i have :

$connectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$tableName = "FilePermissions"

Open in new window



now the error i get is:

Exception calling "ExecuteReader" with "0" argument(s): "Incorrect syntax near 's'.
Unclosed quotation mark after the character string ')'."
At C:\IT Scripts\AuditFilePermissions_single path.ps1:16 char:5
+     $command.ExecuteReader()
+     ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
0
 
leadthewayAuthor Commented:
after playing around i got it to connect. I had to add the instance to the servername...thanks for the assist!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now