Endpoint Protection

 View Only
  • 1.  Powershell - how to connect to SEP 12 embedded Database

    Posted Jul 27, 2012 03:18 AM

     

    In SEP v11 to connect to the "embedded database" from powershell I used:
     
     
     
    but in SEP v12 it does not work. 
     
     
    The error is:
     
    "Exception calling" Open "with" 0 "argument (s):" The 'ASAProv.90' provider is not registered on the local machine. "
     
     
    When I change the provider name to the "SAOLEDB or SAOLEDB.11" I get the following message:
     
    "Exception calling" Open "with" 0 "argument (s):" Invalid user ID or password "."
     
    I am sure that the username and password are correct because it can pass these two tests:
     
    - "Test Connection" from ODBC (message: connect successfully)
    - From CLI using dbisqls.exe (query is executed and TXT file is generated)
       dbisqlc.exe-qc "uid = DBA; pwd = XXXX; SymantecEndpointSecurityDSN DSN =" "foobar.sql"
     
    I guess the problem is name of provider, but I do not know where I can find the correct name of provider.
     
     
    Does anyone know where I can find the name of provider?


  • 2.  RE: Powershell - how to connect to SEP 12 embedded Database

    Posted Jul 27, 2012 09:15 AM

    Edit: Sorry; I didn't see your comment about embedded. This is for the SQL server, not embedded.

     

    I got it working using the System.Data.SQLClient.SQLConnection as seen below. Just run the script with a user that has access to the SEPM database.

    ------------------------------

    $Connection = New-Object System.Data.SQLClient.SQLConnection

    #Change serverDB and Database accordingly

    $Connection.ConnectionString ="server=databaseserver;database=sem5;trusted_connection=true;"
    $Connection.Open()


    #Querying Computers
    $Query ='SELECT computer_name FROM SEM_COMPUTER'

    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $Query



  • 3.  RE: Powershell - how to connect to SEP 12 embedded Database

    Posted Jul 29, 2012 01:21 PM

    Your problem is not the provider name, which is indeed SAOLEDB (or SAOLEDB.11). See here.

    In my environment (Windows Server 2003 SP2 [32 bit], PS 2.0, SEPM 12.1.1101), the following script works fine:

    $conn1 = New-Object System.Data.OleDb.OleDbConnection("Provider = SAOLEDB; Data Source = SymantecEndpointSecurityDSN;User ID = DBA; Password = XXXX");
    
    $query = "SELECT * FROM SEM_AGENT;"
    $cmd = $conn1.createCommand();
    $cmd.commandText = $query;
    
    $conn1.Open();
    
    $reader = $cmd.ExecuteReader();
    
    while ($reader.Read()) {
        $hash = @{}
        for ($x = 0; $x -lt $reader.FieldCount; $x++) {
            $hash.$( $reader.GetName($x)) = $reader.GetValue($x)
        }
        New-Object PSObject -property $hash;
    }
    $reader.Close();
    $conn1.Close();
    

    As you see, the first line is the same as in your PS command. It's working with both SAOLEDB and SAOLEDB.11.

    So you have to look for other reasons sad



  • 4.  RE: Powershell - how to connect to SEP 12 embedded Database

    Posted Jul 30, 2012 03:36 AM

     

    Since the problem is not in the name of provider I will focus on the message:
     
    "Exception calling" Open "with" 0 "argument (s):" Invalid user ID or password"
     
    I'm confused by the fact that the same user name and password I use when performing ODBC test or do a query from command prompt.
     
    When I press button "Test Connection" from ODBC I receive message "connect successfully"
     
    From CLI using "dbisqls.exe":
     
       dbisqlc.exe-qc "uid = DBA; pwd = XXXX; SymantecEndpointSecurityDSN DSN =" "foobar.sql"
     
    where "foobar.sql" is:
     
    "SELECT FROM computer_name SEM_client order by computer_name;"
    "output to foobar.txt; "
     
    and as a result I get all computers from the "SEPM".
     
    My environment:
    Windows Server 2003 standard R2 [32 bit]
    PS: 2.0
    SEPM: 12.1.1000.157 RU1
     
    Any idea what/where to look?


  • 5.  RE: Powershell - how to connect to SEP 12 embedded Database

    Posted Jul 30, 2012 06:48 AM

    Connect via ODBC instead of OLE DB ... don't know why it should work better, but it's worth a try:

    $conn1 = New-Object  System.Data.Odbc.OdbcConnection("Driver={SQL Anywhere 11};
    uid=dba;pwd=XXXXXXXX;DSN=SymantecEndpointSecurityDSN")

    Works here.



  • 6.  RE: Powershell - how to connect to SEP 12 embedded Database

    Posted Jul 31, 2012 04:17 AM

     

    Thanks for the help, but still fail.
     
    To avoid errors in typing I copied/paste the connection string from:
     
      [dbisqlc.exe -q -c "uid=DBA;pwd=XXXX;DSN=SymantecEndpointSecurityDSN"]
     
    to:
     
    $conn1 = New-Object  System.Data.Odbc.OdbcConnection("Driver=SQL Anywhere 11;uid=DBA;pwd=XXXX;DSN=SymantecEndpointSecurityDSN")
    $conn1 = New-Object System.Data.OleDb.OleDbConnection("Provider = SAOLEDB;uid=DBA;pwd=XXXX;DSN=SymantecEndpointSecurityDSN");
     
    Whatever option I try always get the error message that the username or password is wrong:
     
    -------------------------------------------------------------------------------------------------------------------------------
    Exception             : System.Management.Automation.MethodInvocationException: Exception calling "Open" with "0" argum
                            ent(s): "ERROR [28000] [Sybase][ODBC Driver][SQL Anywhere]Invalid user ID or password
                            ERROR [28000] [Sybase][ODBC Driver][SQL Anywhere]Invalid user ID or password" ---> System.Data.
                            Odbc.OdbcException: ERROR [28000] [Sybase][ODBC Driver][SQL Anywhere]Invalid user ID or password
    -------------------------------------------------------------------------------------------------------------------------------
    Exception             : System.Management.Automation.MethodInvocationException: Exception calling "Open" with "0" argum
                            ent(s): "Invalid user ID or password" ---> System.Data.OleDb.OleDbException: Invalid user ID or password
    -------------------------------------------------------------------------------------------------------------------------------
     
     
    I even installed a new test virtual server from scratch but result is same.
     
     
    What confuses me is the fact that these two examples work even though I use the same username and password (in all examples).
     
     
    If in the ODBC panel I enter username and password I get a successful test message. If I enter wrong username or password I get error message (as expected).
     
    If I run this command:
     
    dbisqlc.exe -q -c "uid = DBA; pwd = XXXX, DSN = SymantecEndpointSecurityDSN" "foobar.sql"
     
    from CLI a file is generates with all relevant information. If I change username or password I get error message (as expected).
     
     
    Is there any additional settings witch I need to set on embedded database for access from PS?
     


  • 7.  RE: Powershell - how to connect to SEP 12 embedded Database
    Best Answer

    Posted Aug 01, 2012 07:16 AM

     

    Found solution.
     
    I started a "dbisqlc.exe" and connect to the "SEM5" database via ODBC (SymantecEndpointSecurityDSN).
     
    In the GUI I entered & execute the following command: 
     
    "SET OPTION PUBLIC.login_mode = 'Standard, Integrated, Kerberos';"
     
    After that I changed the connection string in my PS script as follows:
     
    $conn = New-Object System.Data.OleDb.OleDbConnection ("Provider=SAOLEDB;Data Source=SymantecEndpointSecurityDSN")
     
    Now I am able to export data from database using PS scripts.


  • 8.  RE: Powershell - how to connect to SEP 12 embedded Database

    Posted Aug 01, 2012 11:12 AM

    Thanks for sharing.



  • 9.  RE: Powershell - how to connect to SEP 12 embedded Database

    Posted Aug 02, 2012 05:24 AM

     

    Thank you for participate in resolving my case.
     
    Just to point out that the PS script must be started under an administrator account.
     
    Best regards.