Solved

User with DBO permission can't remotely connect

Posted on 2016-11-01
15
36 Views
Last Modified: 2016-11-03
have brand new 2012 express and am using a script to out put data to a table, but when the script runs i get an access error, but I'm listed as a dbo under the database.. not sure what could be preventing

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.user'."
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
Comment
Question by:leadtheway
  • 8
  • 6
15 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41869727
but I'm listed as a dbo under the database
(...)Login failed for user 'ENTERPRISE-1\admin.user'."
Being a database user doesn't really means that you can login to a SQL Server instance. For that you'll need to have the database user mapped to a SQL Server login (they are different objects).
Can you connect to the database with SSMS?
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869733
yes, i am using the same account i installed sqlexpress with...i think i found the issue though, i needed to specific the instance
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41869735
That's the other thing I wanted to ask after knowing that you can connect through SSMS: if you can post the connection string that you're using.
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869912
$connectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$tableName = "FilePermissions"
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869913
heres the full script
$connectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$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 = "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='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


I think its authenticating but i'm getting different error now:

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 45

Expert Comment

by:Vitor Montalvão
ID: 41869964
Yes, the error is different now.
Try to print $query before the $command.ExecuteReader() line.
Btw, if you have an INSERT shouldn't be executed by ExecuteNonQuery instead of ExecuteReader command?
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869980
hmm ok so now i added the print $query

 print $query
    $command.ExecuteReader()
    $connection.Close()

Open in new window



now i don't get a red error, i now get invalid switch for Insert...

Invalid switch - "INSERT INTO FilePermissions (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag
,acetype,accessmasks) VALUES ('FILE01','E:\Share\Accounting','D','ENTERPRISE-1\ACCOUNTING','CREATOR OWNER','False','ContainerInherit, ObjectInherit','InheritOnly','Allow','FullControl')"
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41869988
Try $command.ExecuteNonQuery instead of ExecuteReader.
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869990
looks like data did get into the table despite the invalid switch... I'll swap out commands
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869992
Invalid switch - "INSERT INTO FilePermissions (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,ace
type,accessmasks) VALUES ('domain-FILE01','E:\Share\Accounting\2016 SOC 1 Audit','D','ENTERPRISE-1\user','ENTERPRISE-1\user','True','None','None','Allow','FullControl')"
1
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41870000
Remove the print command since you don't need it anymore.
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41870006
getting a bunch of 1 with the occasional errors.  I assume that means its working now?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41870011
Not sure but at least main issue disappear.
I'm also a SQL Server guy so you might open a new question in development area to get better expertise answers.
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41870019
yeah i ran a query against the table and the rows are increasing and if i look at the top 1000, theres data that i expect there.  Now to export this to a webpage somehow...thanks for the assist
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41872206
Btw, if that's a real password you posted, please ask E-E to edit out the password for you(!).
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Update foreign key reference after insert 9 34
c# code 19 59
SQL Connection (Error 18456) 14 32
Sql query to Stored Procedure 6 16
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now