Powershell - how to connect to SEP 11 SQL Database

[Sonihal]

I am writing powershell script and need to know the connection string to connect to SEP 11 MS SQL 2008 Database.

Can you point me in the right direction.

[ℬrίαη]

The default should be:

jdbc:sqlserver://*hostname*:1433;DatabaseName=sem5

Replace hostname with the name of your SQL server and replace sem5 with your DB name

[Migration User]

There are a lot of possibilities:

function Get-AccessDatabaseSep {
    param(
    $sql = "SELECT * FROM SEM_AGENT"
    )

    # -----------------------------------------------------
    # Access via OLE DB and Windows authentication
    # Don't forget the database instance
    $providertype = 'System.Data.OleDb'
    $connection = "Provider=sqloledb;" + 
                  "Data Source=192.168.0.40\DBINSTANCE;" + 
                  "Initial Catalog=sem5;Integrated Security=SSPI;"
    
    ## Alternatively, you can access via ODBC:
    ## Don't put the password in the script as I did here ;-)
    # $providertype = 'System.Data.Odbc'
    # $connection = "Driver={SQL Server};" + 
    #               "Server=192.168.0.40\DBINSTANCE;" + 
    #               "Database=sem5;Uid=sa;Pwd=password;"
    # -----------------------------------------------------
    
    $provider = [System.Data.Common.DBProviderFactories]::GetFactory($providertype)
    
    $db = $provider.CreateConnection()
    $db.ConnectionString = $connection
    $db.Open()
    
    $cmd = $provider.CreateCommand()
    $cmd.CommandText = $sql
    $cmd.Connection = $db
    
    $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()
    $db.Close()
}

The stuff in italics must be adjusted.

BTW, this function reads the SEM_AGENT table by default, but you can overwrite this with every possible SQL command, for example:

Get-AccessDatabaseSep -sql "SELECT * FROM SERVER_ADMIN_LOG_1"

[Sonihal]

Thank you, I shall try these out.

[Migration User]

 

Example of connection with powershell (embedded database) version v11.x:

 

 

$conn = New-Object System.Data.OleDb.OleDbConnection ("Provider = ASAProv.90; Data Source = SymantecEndpointSecurityDSN; User Id = DBA; Password = XXXX")

 

but does not work with version v12.

 

 

I guess the problem is name of provider, but I do not know where I can find the correct name of provider.

 

 

Does anyone have an idea what provider should I use in v12.

 

 

[Migration User]

I guess the problem is name of provider,

Not sure if this will work, but think it is worth a shot. (Sorry, don't have an embedded DB to test this against)

When creating a new ODBC connection using the Control Panel applet, next to the name of the driver you want to use is a filename of a DLL. Use the name of the DLL without the extension. Maybe that will work.

[Migration User]

 

In the "Control Panel" I opened the "ODBC" and there I found:

 

"SQL Anywhere 11, 11.00.01.2472, iAnywhere Solutions, Inc.., DBODBC11.DLL"

 

 

When I change provider in the script (from "ASAProv.90" to "DBODBC11"):

 

$ conn = New-Object System.Data.OleDb.OleDbConnection ("Provider = DBODBC11; Data Source = SymantecEndpointSecurityDSN; User Id = DBA; Password = XXX");

 

$ ds = New-Object "System.Data.DataSet" "SEMData"

$ query = "SELECT * FROM computer_name SEM_client;"

$ to = New-Object System.Data.OleDb.OleDbDataAdapter ($ query, $ conn);

$ conn.Open ();

 

I get the following error:

 

"Exception calling" Open "with" 0 "argument (s):" The 'DBODBC11' provider is not registered on the local machine. "

 

In any case, thank you.

 

Does anyone have an idea where I could find provider name for the embedded database used in SEP v12?

[Migration User]

Just a wild guess ... try SAOLEDB or SAOLEDB.11

Unfortunately I cannot test it for the time being.

Good luck!

 

[Migration User]

 

When I try "SAOLEDB or SAOLEDB.11" error appears:

 

"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-q-c "uid = DBA; pwd = XXXX; SymantecEndpointSecurityDSN DSN =" "foobar.sql"

  

Any idea?

[Migration User]

Will see for the issue ... perhaps you should open a new thread to get more visibility.

[Migration User]

New thread.