Solved

Powershell output to SQL

Posted on 2016-11-01
17
118 Views
Last Modified: 2016-11-03
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
Comment
Question by:leadtheway
  • 10
  • 6
17 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41868956
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
 
LVL 12

Accepted Solution

by:
Dustin Saunders earned 500 total points
ID: 41868958
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41869065
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:leadtheway
ID: 41869073
oh wait i didn't see connectionString at first...sorry
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869077
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41869123
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
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869130
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41869138
my user is listed under dbo
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869168
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41869182
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869184
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869209
What happens if you run the script from an admin powershell window?
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869211
thats how i'm running it, also firewall on sql turned off
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869250
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41869643
ill try that this morning and report back
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869706
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41870020
after playing around i got it to connect. I had to add the instance to the servername...thanks for the assist!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A brief introduction to what I consider to be the best editor for PowerShell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question