Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We鈥檒l occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unable to make oracledb_session work at all. #3594

Closed
g1ps opened this issue Nov 8, 2018 · 30 comments

Comments

@g1ps
Copy link

commented Nov 8, 2018

馃帥 Description

Again, I need to ask for assistance. After many hours of trying I can't get this to work at all.

My env is this:

Machine A -> Machine B -> Machine C

A is running the inspec tests. It connects to B which, in turn, can access C and has Oracle tools installed and working. A is RHEL, B & C are Windows machines. I am running this, based on the example in the docs, which also fails:

  sql = oracledb_session(user: 'system', password: 'sys', host: '<C's name>', service: '<service>')

 describe sql.query('SELECT NAME FROM v$database;').rows do
    its('count') { should eq 1 }
  end

inspec always returns:

  []
     x  count should eq 1

     expected: 1
          got: 0

     (compared using ==)

Which suggests strongly to me that it isn't connecting to the DB.
I have tried everything I can find (which is almost nothing) and think of. The query works if I run it directly. The user is fine. The details are correct. I tried connecting directly to C, the DB server. No change. I tried various additional parameters: sqlplus_bin, as_db_role. I tried the host IPs instead of names. I've tried other users. I've tried several queries.

Warning and error --log-level output show nothing. debug output shows nothing that looks like a problem to me. The query it shows will run if I run it manually. The login string it generates works on the command line on B. I'm out of ideas. What am I missing here?

馃實 InSpec and Platform Version

inspec 3.0.12

@tbugfinder

This comment has been minimized.

Copy link

commented Nov 8, 2018

What's the output of puts sql?

@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 9, 2018

Run where?

@tbugfinder

This comment has been minimized.

Copy link

commented Nov 9, 2018

  sql = oracledb_session(user: 'system', password: 'sys', host: '<C's name>', service: '<service>')

puts sql

puts sql.query('SELECT NAME FROM v$database;')

 describe sql.query('SELECT NAME FROM v$database;').rows do
    its('count') { should eq 1 }
  end
@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 12, 2018

OK, thanks.

Oracle Session
SQL ResultSet

Profile: tests from test_spec.rb (tests from test_spec.rb)
Version: (not specified)
Target:  winrm://********@http://**************:5985/wsman:3389

  []
     x  count should eq 1

     expected: 1
          got: 0

     (compared using ==)

Test Summary: 0 successful, 1 failure, 0 skipped
-sh-4.2$
@clintoncwolfe

This comment has been minimized.

Copy link
Contributor

commented Nov 14, 2018

@miah any thoughts?

Not being familiar with the oracledb_session resource, I'd first be interested in whether the OP can execute any test targeting machine C, perhaps host. This appears to be a bastion hop from a linux machine to a windows machine, which then targets a remote DB.

@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 15, 2018

I did some checking:
Targetting Machine B:

  describe os.name do
    it { should be_in ['windows_server_2012_r2_standard', 'windows_10'] }
  end

   describe host('Machine B', port: 80, protocol: 'tcp') do
    it { should be_reachable }
    it { should be_resolvable }
    its('connection') { should_not match /connection refused/ }
  end

  describe host('Machine C', port: 80, protocol: 'tcp') do
    it { should be_reachable }
    it { should be_resolvable }
    its('connection') { should_not match /connection refused/ }
  end

Results:

  windows_server_2012_r2_standard
     ?  should be in "windows_server_2012_r2_standard" and "windows_10"
  Host Machine B port 80 proto tcp
     ?  should be reachable
     ?  should be resolvable
     ?  connection should not match /connection refused/
  Host Machine C port 80 proto tcp
     x  should be reachable
     expected that host Machine C port 80 proto tcp is reachable
     ?  should be resolvable
     ?  connection should not match /connection refused/

Test Summary: 6 successful, 1 failure, 0 skipped

This casts doubt over whether my tests are actually working, now, since C has no web server and therefore result 5 is correct. So why are 6 & 7 showing as passed?? See also #3580. Now I'm concerned that the ? aren't failed ticks.

Targetting Machine C with the same tests:

  windows_server_2012_r2_standard
     ?  should be in "windows_server_2012_r2_standard" and "windows_10"
  Host Machine B port 80 proto tcp
     ?  should be reachable
     ?  should be resolvable
     ?  connection should not match /connection refused/
  Host Machine C port 80 proto tcp
     x  should be reachable
     expected that host Host Machine C port 80 proto tcp is reachable
     ?  should be resolvable
     ?  connection should not match /connection refused/

Test Summary: 6 successful, 1 failure, 0 skipped

which is equally concerning. Suggestions welcome.

@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 15, 2018

Results when run from Windows:


  Windows Server
     [MAJR]  2012 R2 Standard should be in "windows_server_2012_r2_standard" and "windows_10"
     expected `Windows Server 2012 R2 Standard` to be in the list: `["windows_server_2012_r2_standar
d", "windows_10"]`
  Host Server B
     [PASS]  port 80 proto tcp should be reachable
     [PASS]  port 80 proto tcp should be resolvable
     [PASS]  port 80 proto tcp connection should not match /connection refused/
  Host Server C
     [MAJR]  port 80 proto tcp should be reachable
     expected that host Host intg-db.intg.local port 80 proto tcp is reachable
     [PASS]  port 80 proto tcp should be resolvable
     [PASS]  port 80 proto tcp connection should not match /connection refused/

Test Summary: 5 successful, 2 failures, 0 skipped
```
Equally concerning plus it looks like the OS name is rendered differently across master OSs, which isn't helpful. 
@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 16, 2018

A clean installation of 3.0.52 on another RHEL VM behaves exactly the same way. I need this working. Can anyone offer advice as to whether rolling back to an earlier version is likely to help? Which version? Anything else I can try?

Thanks.

@tbugfinder

This comment has been minimized.

Copy link

commented Nov 16, 2018

how about?

sql = oracledb_session(user: 'system', password: 'sys', host: '<C's name>', service: '<service>')

puts sql

puts sql.query('SELECT NAME FROM v$database;').rows

 describe sql.query('SELECT NAME FROM v$database;').rows do
    its('count') { should eq 1 }
 end
@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 19, 2018

As you'd expect, that just doesn't output SQL ResultSet because it's not getting any rows.

@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 19, 2018

I reverted to Inspec 2.3.24.:

     [FAIL]  count should eq 1

     expected: 1
          got: 0

     (compared using ==)

Doesn't work in this version, either.

@clintoncwolfe

This comment has been minimized.

Copy link
Contributor

commented Nov 19, 2018

oracle_session hasn't changed much; I don't think your issue is particularly version sensitive. As you've never had it working, it may be a network, auth, or InSpec usage issue, or indeed a bug.

Responding to the bastion checking comment, I can explain the output. Here's my understanding of the setup:

  inspec      
  bastion    inspec     Oracle
  ingress     exec      Server
    A =======> B -------> C
        SSH        TCP

The bastion checking comment tested connectivity between B and C, using

  describe host('Machine C', port: 80, protocol: 'tcp') do
    it { should be_reachable }
    it { should be_resolvable }
    its('connection') { should_not match /connection refused/ }
  end

with outcome (numbers added for reference)

  Host Server C
    (1) [MAJR]  port 80 proto tcp should be reachable
     expected that host Host intg-db.intg.local port 80 proto tcp is reachable
    (2) [PASS]  port 80 proto tcp should be resolvable
    (3) [PASS]  port 80 proto tcp connection should not match /connection refused/

Here's what that outcome means:

  • First, realize that InSpec refers to a test name in out put using the control name (which includes all resource_parameters, followed by the conditions of the test). So, each test will be referred to as (typically) 'hostname port 80 proto tcp ' + ' should be ' + ' some test '. Here, it looks like you've redacted the actual hostname, which is understandable (or perhaps the host resource trims it, and only uses the params in test names. Point being, you'll see 'port 80 proto tcp ' in each test output under that control - regardless of whether the test had anything to do with the port or the protocol. Like it or leave, that's how InSpec works; with that in mind, hopefully the output is less confusing, at least as regards to its labelling.
  • You tried to probe port 80 on a machine not running a webserver, and it correctly failed - so that's (1) explained. That's all good. I think, though, you want to try port 1521 to try to reach Oracle; that gives you a useful diagnostic test: can your auditing machine (B) even reach the database server on the desired port?
  • For the resolvability check, (2), we have a positive result: machine B is able to successfully resolve C's hostname into an IP address. Perhaps confusingly, the test outcome is 'port 80 proto tcp should be resolvable' - though this was a DNS operation, and the port and protocol were not involved. One of the pitfalls of generating UX text from code. :/
  • For (3), sigh. connection changes structure and behavior depending on the OS it is being run on, and is undocumented. I don't think it should be in the examples; I think we should document reachable? instead. Basically, if you run on Linux, it will try to do an nc connection to the port and protocol specified; and then return the output - which may contain connection refused if it failed. On Darwin and Windows, other methods are used, and the string output is unstable. Here, for you, the string output apparently didn't contain 'connection refused'. The safer test is to use it { should be_reachable }.

Anyway, wall of text aside, I think you should try the host check again, specifically targeting port(s) that B will need to talk to on C.

@clintoncwolfe

This comment has been minimized.

Copy link
Contributor

commented Nov 19, 2018

Re: your concerns about os.name, that is indeed expected behavior. The os resource has all sorts of information used to identify and distinguish the OS; and it so happens that os.name is the most specific. So, if there are any distinctions between Windows releases (and there are many - that string varies widely), os.name will be different.

If you just care about windows-vs-linux, use the os.windows? predicate.

If you care about specific releases, try os.release, but the contents of that string are not standardized, even within an OS family.

@clintoncwolfe

This comment has been minimized.

Copy link
Contributor

commented Nov 19, 2018

Hrm, I stand corrected: in fact, we did have a change to oracledb_session, merged in #3170, inspec release 2.2.35 . We then had a portability bug reported on #3255, still open.

The change was not present in 2.2.34 , which was released as a gem. I'm not sure the resource worked on windows then, either, but it is worth a try.

@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 20, 2018

Many thanks for the detailed responses.

I don't know what the bastion discussion is about. I said at the start that the tests run on A, using B as the target. This test is asking B to check C though, yes.

ssh -> tcp

Not ssh. WinRM.

Here's what that outcome means:

  • That's fine, thanks. Redacted, I'm afraid.
  • Using port 1521 I get 3 successful results. Connections from sqlplus work, of course.
  • OK
  • OK

Re: your concerns about os.name

My point was that the same OS (same release, same machine!) is reported using two different strings depending on the OS running the test. That doesn't seem useful. I would expect the string to be formatted identically so I can match the same one on either master OS.

Windows Server 2012 R2 Standard vs 'windows_server_2012_r2_standard'

Thanks again.

@miah

This comment has been minimized.

Copy link
Contributor

commented Nov 21, 2018

I am trying to reproduce locally to debug further.

@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 21, 2018

Thanks. Much appreciated.

@miah

This comment has been minimized.

Copy link
Contributor

commented Nov 26, 2018

I was able to get this resource working! =)

I first set up a Oracle VM using https://github.com/oracle/vagrant-boxes

I then installed the Oracle Instant Client.

Here is my control:

title 'oratest'

sql = oracledb_session(user: 'system', password: 'B24GPAf8xv4=1', host: 'localhost', service: 'ORCLCDB', sqlplus_bin: '/opt/oracle/instantclient_12_2/sqlplus')

describe sql.query("SELECT UPPER(VALUE) FROM V$PARAMETER WHERE UPPER(NAME) = 'AUDIT_SYS_OPERATIONS';").rows do
  its('count') { should eq 1 }
end

describe sql.query("SELECT UPPER(VALUE) FROM V$PARAMETER WHERE UPPER(NAME) = 'AUDIT_SYS_OPERATIONS';").row(0).column('upper(value)') do
  its('value') { should eq 'FALSE' }
end

describe sql.query('SELECT NAME FROM v$database;').rows do
  its('count') { should eq 1 }
end

(Don't worry about password, its auto-generated when the VM starts)

Here is the output:

(bei is just my local shell alias for bundle exec inspec)

miah@anorak ~/projects/github/master:$ bei exec oratest/

Profile: InSpec Profile (oratest)
Version: 0.1.0
Target:  local://

  [#<Hashie::Mash upper(value)="FALSE">]
     鉁  count should eq 1
  SQL Column
     鉁  value should eq "FALSE"
  [#<Hashie::Mash name="ORCLCDB">]
     鉁  count should eq 1

Test Summary: 3 successful, 0 failures, 0 skipped
@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 27, 2018

I've tried this several times but I took your tests, changed the server details and tried it. Didn't work. I tried targeting the DB server (C) instead. No go. I have now tried about 4 versions of Inspec and run from numerous locations targeting all of the machines and it never works. It's good to know that it can work but what is this telling us? Something wrong with Oracle? Queries work fine from sqlplus anywhere I run it. I'm completely stumped.

@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 27, 2018

I tried some more things. I tried testing a different DB on a stand-alone VM, connecting directly to that machine. That doesn't work either. WinRm is working. I can test other commands successfully.

I then tried working around this by running sqlplus:

 describe command('@echo select count(*) from all_users; | sqlplus system/sys@pdborcl') do
    its('stdout') { should match /71/}
  end

results in

  Command: `echo select count(*) from all_users; | sqlplus system/sys@pdborcl`
     x  stdout should match /71/
     expected "" to match /71/
     Diff:
     @@ -1,2 +1,2 @@
     -/71/
     +""

This should fail since the number is wrong but it doesn't retrieve anything.
The command runs just fine from sqlplus on the machine itself.

@echo select count(*) from all_users; | sqlplus system/sys@pdborcl
....
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL>
  COUNT(*)
----------
       320
....

So Inspec refuses to talk to Oracle in any way in any environment here. I'm now stuck and can't even work around it.

Has this been tested with Oracle 12c? Multi-tennant DBs? Could there be something here that stops it working? This basically renders Inspec unusable for us as DB testing is a prime requirement...and I don't know where else to turn. Nothing else I've found seems particularly useful.

@miah

This comment has been minimized.

Copy link
Contributor

commented Nov 27, 2018

Can you try passing the full path to the sqlplus command in your command resource?

@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 28, 2018

I tried it. I also tried sqlplus_bin again with oracledb_session. It made not the slightest difference.

@g1ps

This comment has been minimized.

Copy link
Author

commented Nov 28, 2018

Just so that it's clear. Is the machine running Inspec expected to have sqlplus installed or does it connect to the target given in the test and run sqlplus there? What about the 3-machine chain I first mentioned. I assumed that Machine B should have sqlplus, and it does, but the master doesn't.

@g1ps

This comment has been minimized.

Copy link
Author

commented Dec 4, 2018

Also found #2907 today, so it's not just me. How about fixing this?

@maczamora

This comment has been minimized.

Copy link

commented Dec 13, 2018

Hello @g1ps , I've been on the Slack channel getting support from several people. It sounds like my environment variables may not be set correctly when I run the oraclesb_session resource. I feel like I've done exactly the same as you covering my ground. I sorta have the same situation going on.

Scenario:
I'm on a Windows machine where I use Inspec to run tests remotely. I have gotten certain controls to work which include Active Directory locally that checks password policy controls (Yay!). This was a project I started at work that I believe will continue, but the oracledb_session resource hasn't been working. We do have a lot of oracle db's to work with so if I can figure out this one issue I will have everything ready to test. I need to be able to come back with some answers for why this issue is occurring and I honestly can't say anymore.

Environment:
Windows 10 machine --> SSH to host or server (IBM AIX) --> Inspec control somehow kicks in and passes db credentials.

I'm thinking the db credentials aren't being passed or aren't establishing a connection to the db. I tried some steps to try and troubleshoot using the shell -l debug cl options and when I run the oracledb_session(user: name, password: password, host: aixhost, service: servicename, port: portnumber, sqlplus_bin" /opt/oraclce/etc/etc/etc/bin/sqlplus).query(some simple query).successful? and it outputs the below result:

SET MARKUP HTML ON SET PAGESIZE 32000 SET FEEDBACK OFF SELECT * from dba_profiles; EXIT EOC) Could not execute the sql query Error 6 initializing SQL*Plus SP2-0667: Message file sp1<lang>.msb not found SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory => false

If I leave out the sqlplus part it will end at EOC. To hack around this people have suggested trying to pass in in the ORACLE_HOME parameters on the cli with --shell and other --shell-options= and I'm almost certain that's not working. There were some other modifications to doing that, but I don't see a reason why I would have to do that. I tried my own little hack where in the server .profile file for my account it will automatically set the ORACLE_HOME directory anytime I ssh or go to connect to that server.

A couple other minor things I'd like to point out, in the resource code they define the port as 1521, my port on the db is different. Not sure if this is something implicit or explicit. Someone told me it should be port 22 when you use ssh, but if the db uses a different port wouldn't that also have to be specified to the desired port? I also see #3255 where they mentioned about platform independence. I'm wondering if this person got it to work on a unix/linux system. Only posting here because I know the pain and would like to know if it's an actual issue in the resource.

@g1ps

This comment has been minimized.

Copy link
Author

commented Jul 22, 2019

Here am I at Inspec 4.7.3 and this still doesn't work. I have the same environment but all servers have been refreshed, even my work machine, and the Inspec installations are new. I note additional discussion at #3255, which seems to verify that this resource is dysfunctional. When I run on Windows Server 2012 R2 I get this:


E:\temp>inspec exec test --controls test-1.0 --reporter cli html:index.html
Exception calling "NewScriptBlock" with "1" argument(s): "At line:1 char:96
+ ... :1521/pdborcl <<EOC
+                    ~
Missing file specification after redirection operator.
At line:1 char:95
+ ... l:1521/pdborcl <<EOC
+                    ~
The '<' operator is reserved for future use.
At line:1 char:96
+ ... :1521/pdborcl <<EOC
+                    ~
The '<' operator is reserved for future use.
"
At line:16 char:15
+               $scriptBlock = $ExecutionContext.InvokeCommand.NewScriptBlock($com ...
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : ParseException


Profile: Test Environment Validation (test-val)
Version: 0.0.1
Target:  local://

  [FAIL]  test-1.0: Testing....
     [FAIL]  Control Source Code Error test/controls/test.rb:13
     end of file reached


Profile Summary: 0 successful controls, 1 control failure, 0 controls skipped
Test Summary: 0 successful, 1 failure, 0 skipped
Traceback (most recent call last):
        11: from E:/opscode/inspec/bin/inspec:189:in `<main>'
        10: from E:/opscode/inspec/bin/inspec:189:in `load'
         9: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/inspec-bin-4.7.18/bin/inspec:11:in `<top (required)>'
         8: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/inspec-4.7.18/lib/inspec/base_cli.rb:33:in `start'
         7: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/thor-0.20.3/lib/thor/base.rb:466:in `start'
         6: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/thor-0.20.3/lib/thor.rb:387:in `dispatch'
         5: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/thor-0.20.3/lib/thor/invocation.rb:126:in `invoke_command'
         4: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/thor-0.20.3/lib/thor/command.rb:27:in `run'
         3: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/inspec-4.7.18/lib/inspec/cli.rb:297:in `exec'
         2: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/inspec-4.7.18/lib/inspec/base_cli.rb:215:in `exit'
         1: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/inspec-4.7.18/lib/inspec/ui.rb:192:in `exit'
E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/inspec-4.7.18/lib/inspec/ui.rb:192:in `exit': exit (SystemExit)
        3: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/train-2.1.13/lib/train/transports/local.rb:223:in `block in
        2: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/win32-process-0.8.3/lib/win32/process.rb:797:in `kill'
        1: from E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/win32-process-0.8.3/lib/win32/process.rb:797:in `each'
E:/opscode/inspec/embedded/lib/ruby/gems/2.6.0/gems/win32-process-0.8.3/lib/win32/process.rb:850:in `block in kill': Input/outp

This machine is running sqlplus which is configured to connect to a separate DB server. Queries run here manually work just fine.

Running on my Win 10 machine the resources 'works' i.e. it runs and returns gracefully but it returns no results from the DB:

  [FAIL]  test-1.0: Testing....
     [FAIL]  SQL Column value should cmp == "ORCL"

     expected: "ORCL"
          got: ""

     (compared using `cmp` matcher)

This is connecting to the same Win2102 server, so perhaps that's not surprising. I haven't been able to try from a Linux box, yet, and that probably won't suit our use case. We'll always be channelling through Windows to the DB.

@g1ps

This comment has been minimized.

Copy link
Author

commented Jul 22, 2019

I did a quick from RHEL.

  脳  test-1.0: Testing....
     脳  SQL Column value should cmp == "ORCL"

     expected: "ORCL"
          got: ""

     (compared using `cmp` matcher)

Not great. That's going through the Win 2012 server. This is going straight to the DB server, also WIndows.


  脳  test-1.0: Testing....
     脳  SQL Column value should cmp == "ORCL"

     expected: "ORCL"
          got: ""

     (compared using `cmp` matcher)

I wonder if the fact that the Oracle server itself is on Windows is a factor.

@g1ps

This comment has been minimized.

Copy link
Author

commented Jul 22, 2019

More experiments. I'm trying alternatives. For example, these work:

  describe command('sqlplus -v') do
    its('stdout') { should include 'SQL' }
  end

  describe command('sqlplus') do
    it { should exist }
  end

but

  describe command('echo SELECT NAME AS VALUE FROM v$database; | sqlplus -s System/sys@pdborcl') do
    its('stdout') { should include 'ORCL' }
  end

doesn't. This command works on the command line on the intermediate server and the DB server but fails in Inspec:

  [FAIL]  test-1.0: Testing.... (1 failed)
     [PASS]  Command: `sqlplus -v` stdout should include "SQL"
     [PASS]  Command: `sqlplus` should exist
     [FAIL]  Command: `echo SELECT NAME AS VALUE FROM v$database; | sqlplus -s System/sys@pdborcl` stdout should include "ORCL"
     expected "" to include "ORCL"

This also fails:

  script = <<-EOH
    SELECT NAME AS VALUE FROM v$database;
  EOH

  describe command('sqlplus -s System/sys@pdborcl @script') do
    its('stdout') { should include 'ORCL' }
  end

...

  [FAIL]  test-1.0: Testing.... (1 failed)
     [PASS]  Command: `sqlplus -v` stdout should include "SQL"
     [PASS]  Command: `sqlplus` should exist
     [FAIL]  Command: `sqlplus -s System/sys@pdborcl @script` stdout should include "ORCL"
     expected "" to include "ORCL"

These failures occur whether I point Inspec at the intermediate server or the DB server. It seems that Inspec simply refuses to run sqlplus on Windows in any way. I am unable to find any way to test an Oracle DB from Windows after days of trying on and off. Does anyone have a working solution? Workaround? Other suggestions? I'm open to anything that might help, here.

Thanks.

EDIT
...or perhaps it's just not capturing the output?
got: ""

@g1ps

This comment has been minimized.

Copy link
Author

commented Jul 24, 2019

I finally found one approach that works. Assuming that you have a sql script on the target server i.e. the one from which sqlplus is being run (Machine B in the flow shown above), containing the query

SELECT NAME AS VALUE FROM v$database;

this works:

  describe command('sqlplus -s System/sys@pdborcl @E:\temp\script.sql') do
    its('stdout') { should include 'ORCL' }
  end
     [PASS]  Command: `sqlplus -s System/sys@pdborcl @E:\temp\script.sql` stdout should include "ORCL"

It means that I have to ship the sql script there by some other means and I don't have the row and column etc. control but at least I can test something, now.

@kekaichinose

This comment has been minimized.

Copy link
Collaborator

commented Sep 3, 2019

@g1ps apologies for letting this issue run long, but this looks to be support-related. We use GitHub issues to track bugs and feature requests. If you need support help (i.e. with using Chef InSpec), we recommend that if you are a:

Support issues opened here will be assumed to be from community members, and therefore closed and redirected to our Mailing List. Your issue can be found here:
https://discourse.chef.io/t/unable-to-make-oracledb-session-work-at-all/15848

To read up more on how to file a support issue, check out:
https://github.com/inspec/.github/blob/master/SUPPORT.md

Thank you!
Chef InSpec Team

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
6 participants
You can鈥檛 perform that action at this time.