Forum Discussion

Shack's avatar
Shack
Icon for Advisor rankAdvisor
3 years ago

Groovy & SQL authentication

I have a couple of Groovy Datasources running SQL queries and I would like to write in the lines for the script to use Windows auth or SQL auth but I'm not sure how.  I have it working using SQL auth but in some instances Windows Auth is going to be used.  I'm looking at some of the other core SQL datasources and how they work but I'm having a hard time picking out the lines in the scripts that they use for this.  It's my understanding that they use the outputs from some of the core SQL property sources.  I'd like to leverage that as well but don't know what I need.  

Here's the initial script I have but it's not working this way.  Do any of you have an example I could look at or see an issue in what I've shown below?

 

import org.xbill.DNS.*;
import groovy.sql.Sql;

def returnCode = 0;
def sql = null;
try {

    dbname = hostProps.get("databasexyz")    
    //user = hostProps.get("jdbc.mssql.user");
    //pass = hostProps.get("jdbc.mssql.pass");
    hostname = hostProps.get('system.hostname');

//Check for Integrated Security and then authenticate that way if it's available

    if(auto.integrated_security == "true"){
        
        SQLUrl = "jdbc:sqlserver://${hostname};integratedSecurity=true;applicationIntent=ReadOnly;logintimeout=5";
    }else
        SQLUrl = "jdbc:sqlserver://${hostname}:1433;user=${user};password=${pass}";
 
       sql = Sql.newInstance(SQLUrl)
    
    // Loop through every row returned by the query
    sql.eachRow( 'SELECT * from blah blah blah

  • Anonymous's avatar
    Anonymous

    The "if(auto.integrated_security" statement looks for a property on the device to indicate if integrated security should be used. If it should be used, the difference is the connection string, the SQLUrl variable. It'll contain "integratedSecurity=true" if you're using Windows. It'll have user and pass if it's false (or if it doesn't exist).

    However, the real problem is that there is no variable in your script called "auto.integrated_security". You intended for that to look in LM and find the property by that name, but you skipped it. You'd need to do something like this:

    import org.xbill.DNS.*;
    import groovy.sql.Sql;
    
    def returnCode = 0;
    def sql = null;
    try {
        dbname = hostProps.get("databasexyz")    
        hostname = hostProps.get('system.hostname');
        integrated = hostProps.get('auto.integrated_security');
    
    //Check for Integrated Security and then authenticate that way if it's available
        if(integrated){  //just checks if the variable is true. It'll be null (false) if the hostProps.get statement above failed to find the property in LM
            SQLUrl = "jdbc:sqlserver://${hostname};integratedSecurity=true;applicationIntent=ReadOnly;logintimeout=5";
        } else {
            user = hostProps.get("jdbc.mssql.user");
            pass = hostProps.get("jdbc.mssql.pass");
            SQLUrl = "jdbc:sqlserver://${hostname}:1433;user=${user};password=${pass}";
        }
    sql = Sql.newInstance(SQLUrl)
    // Loop through every row returned by the query
    sql.eachRow( 'SELECT * from blah blah blah')

     

  • Anonymous's avatar
    Anonymous

    I'm assuming your script was intentionally incomplete as you didn't close the try block.

    Also, I'd check the hostProps.get("databasexyz") statement. That one looks for a property on the device in LM called "databasexyz". That likely is the actual value, not the value of the property with that name. So you might change it to:

    dbname = hostProps.get("database_name")

    Then, in LM, you'd create a property on the device called "dbname" and give it the value of "databasexyz". You might consider making dbname part of the AppliesTo, since this whole script will fail if the device doesn't have that property.

  • Thanks Stewart for getting me headed in the right direction.  I should of known I had to define that variable.  So here is what's happening now - The script runs correctly when tested on a box that's setup for Integrated auth however when tested on a box using SQL authentication just throws the standard error code(1).  If I comment out the Integrated lines below then it works on the SQL auth instance.  I'm not sure what's going on but it doesn't like something...  fails the same way if I try moving the user and pass variables down under the "else" as well.

     

    import org.xbill.DNS.*;
    import groovy.sql.Sql;

    def returnCode = 0;
    def sql = null;
    try {    
        hostname = hostProps.get('system.hostname');
        integrated = hostProps.get('auto.integrated_security');
        user = hostProps.get("jdbc.mssql.user");
        pass = hostProps.get("jdbc.mssql.pass");

    //Check for Integrated Security and then authenticate that way if it's available
        if(integrated){
            SQLUrl = "jdbc:sqlserver://${hostname};integratedSecurity=true;applicationIntent=ReadOnly;logintimeout=5";

        } else {
            SQLUrl = "jdbc:sqlserver://${hostname}:1433;user=${user};password=${pass}";   
        }

        sql = Sql.newInstance(SQLUrl)

        
        // Loop through every row returned by the query
        sql.eachRow( 'SELECT dev_id,dev_displayname,dev_ipaddress,dev_serialnumber,dev_mac_address,dev_manufacturer,dev_model,PPM,total_memory,system_systemname FROM genericdatabase.dbo.view_device_properties' ) {
            // Print active discovery script output - WildValue, WildAlias and auto properties.
            println "$it.dev_id##$it.dev_displayname##$it.dev_mac_address####genericdatabase.id=$it.dev_id&genericdatabase.displayName=$it.dev_displayname&genericdatabase.ip=$it.dev_ipaddress&genericdatabase.serialNumber=$it.dev_serialnumber&genericdatabase.macAddress=$it.dev_mac_address&genericdatabase.manufacturer=$it.dev_manufacturer&genericdatabase.Model=$it.dev_model&genericdatabase.memory(mb)=$it.total_memory&genericdatabase.ppm=$it.ppm&genericdatabase.devicename=$it.system_systemname"
        }
    }
    catch (Exception e){
        returnCode = 1;
    }
    finally {
        if(sql != null) {
            sql.close()
        }
        return returnCode;    
    }

  • Anonymous's avatar
    Anonymous

    I'd suggest adding checks within your main try block. You might check that you're actually fetching values for user/pass. If you run this in a Collector debug, you can get more output, like the println statements.

    import org.xbill.DNS.*; //BTW, you don't need this unless you're interacting with a DNS server here in your script.
    import groovy.sql.Sql
    
    def returnCode = 0
    def sql = null
    
    hostname = hostProps.get('system.hostname')
    integrated = hostProps.get('auto.integrated_security')
    user = hostProps.get("jdbc.mssql.user")
    pass = hostProps.get("jdbc.mssql.pass")
    
    try {
        //Check for Integrated Security and then authenticate that way if it's available
        if(integrated){
            SQLUrl = "jdbc:sqlserver://${hostname};integratedSecurity=true;applicationIntent=ReadOnly;logintimeout=5"
        } else {
            if (!user || !pass) {
                println("Unable to fetch username/password.")
                return 2
            }
            SQLUrl = "jdbc:sqlserver://${hostname}:1433;user=${user};password=${pass}"
        }
        println(SQLUrl)
        sql = Sql.newInstance(SQLUrl)
    
    
        // Loop through every row returned by the query
        sql.eachRow( 'SELECT dev_id,dev_displayname,dev_ipaddress,dev_serialnumber,dev_mac_address,dev_manufacturer,dev_model,PPM,total_memory,system_systemname FROM genericdatabase.dbo.view_device_properties' ) {
            // Print active discovery script output - WildValue, WildAlias and auto properties.
            println "$it.dev_id##$it.dev_displayname##$it.dev_mac_address####genericdatabase.id=$it.dev_id&genericdatabase.displayName=$it.dev_displayname&genericdatabase.ip=$it.dev_ipaddress&genericdatabase.serialNumber=$it.dev_serialnumber&genericdatabase.macAddress=$it.dev_mac_address&genericdatabase.manufacturer=$it.dev_manufacturer&genericdatabase.Model=$it.dev_model&genericdatabase.memory(mb)=$it.total_memory&genericdatabase.ppm=$it.ppm&genericdatabase.devicename=$it.system_systemname"
        }
    }
    catch (Exception e){
        returnCode = 1
    }
    finally {
        if(sql != null) {
            sql.close()
        }
        return returnCode
    }

     

  • The fix seems to be this - if(integrated == 'true'){ instead of just if(integrated){  

    When I add:

    println(SQLUrl)

    and test it on an instance that I know should use SQL auth the SQL url being returned was the integrated URL so I'm not totally sure why it doesn't like if(integrated).  I appreciate the tips & pointers with the added return values and checks!

  • Anonymous's avatar
    Anonymous

    Has to do with the truthiness of the variable. The variable is a string. So if it's a non-zero length string, it evaluates to true. Even if the value of the variable is "false". Your test compares the string. My way would have required casting the variable to a boolean to work under all circumstances (or required the property to be created slightly differently in LM). 

    Great job figuring it out!