?
Solved

Powershell output to SQL

Posted on 2016-11-01
17
Medium Priority
?
153 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 2000 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
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.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

777 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