Solved

Powershell output to SQL

Posted on 2016-11-01
17
137 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 13

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 1

Author Comment

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

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 13

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 13

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 13

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 13

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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