Forum Discussion

Vidyak's avatar
Vidyak
New Contributor II
2 years ago

SIC Password and API keys are stored in a plain text file

Hi 

We are evaluating the SIC tool for integration and noticed that API keys and DB passwords are stored unencrypted in plain text files. This is becoming a huge security concern as this way of storing credentials is going to case sensitive data leaks. Not sure if this is already identified by any other client and if it is in the roadmap of fixes? Did any of the clients report this issue?

Thanks

Vidya Kadiyala

CN Rail

Security
  • Ryan_Berry's avatar
    Ryan_Berry
    3 months ago

    Hello mithun_laha ,

       Can you provide a brief example of what calls you're making in your code?  Is it the remote BR that is returning this failure?  

  • Nikpowar97's avatar
    Nikpowar97
    3 months ago

    Hey mithun_laha ,

    Did u set up WinSCP to connect to the SFTP server on the client environment where the SFTP server can be accessed remotely on the OS Cloud App?

    you might have created a Direct Connection with your SFTP details like port number and host name to access the Smart Integration Connecter page. (System Pane)
    u are using the Smart integration function to get stored information like SFTP password, user name, etc.
    using the ApiLibrary.GetSmartIntegrationConfigValue("Key as String to get the password value").

    and u are using a  BRApi.Utilities.ExecRemoteGatewayBusinessRule to retrieve the results.
    As per my understanding,

    A database connection additionally with the Direct connection needs to setup to connect OS Cloud app Client Environment.If you notice and try to compile your smart integration function. it will ask for a database gateway connection to compile your code.
    In your 
    BRApi.Utilities.ExecRemoteGatewayBusinessRule function u need to mention the database connection name as the remotehostname to get details from the direct connection on the remote client environment.

    Dim remoteResults As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si,smartintegrationBRName,Nothing,database connection gateway name, function name in Smart integration Function BR name,String.Empty,False,600)

    If you have followed these steps and still facing an issue please check by hardcoding the credentials in the WinSCP session in your extensibility rules to check your connection. (I hope u have used the WinSCP.dll in the referenced assemblies). OR if you have issues connecting with the Smart integration function please check the SIC logs.

    I am not an expert at this but I have connected to Amazon S3 using a direct connection before. a similar approach should work here.







  • Nikpowar97's avatar
    Nikpowar97
    Contributor
    2 years ago

    NOTE: The solution below is as per my understanding and knowledge trough documentations provided. The information may not be 100% correct. So, please reach out to OS if needed. 

    I think the credentials can be stored inside the SIC local gateway settings as a key value pair. The setting name(key) can be passed in a Smart Integration functions (OS version 7.4.1) BR and to retrieve the setting value(value). 

    Let's same setting name = Password

    Setting value = "Password@123"

    Smart Integration Functions ( Applications> Tools>Business Rules> Smart Integration function)

    (Compile the BR by choosing the gateway) - SIC guide has similar rule in C#

    Imports System
    Imports System.Collections.Generic
    Imports System.Data
    Imports System.Data.Common
    Imports System.Globalization
    Imports System.IO
    Imports System.Linq
    Namespace OneStream.BusinessRule.SmartIntegrationFunction.GetPassword
    Public Class MainClass
    Public Shared Function RunOperation() As String
    Dim password As String = OneStreamgatewayservice.APIlibrary.getsmartintegrationconfigvalue("Password")
    Return password
    End Function
    End Class
    End Namespace

    Once this is done the same function can be called inside any BR (Example a connecter rule to password in the Uri of API endpoint ) using newly introduced Remote Brapis.

    Here in case highlighed in blue: 

    Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, brName, functionArguments, remoteHost, functionName)

     

    Pass the Smart integration function here and rest of the code can be reference through the SIC guide.

    However, I think the password can still be error-logged. (Just a conjecture).

    Alternatively we can store password in a Transformations lookup and restrict access to specific user group. 

    the same can be retrieved using,

    Dim sValue As String = BRApi.Utilities.TransformText(si, sourceText, transformationLookupGroup, usePassThroughIfLookupNotFound)

     

    or Use Brapi.Utilities.EncryptText (please check the exact Package) to encrypt. Log it using Brapi.Errorlog.logmessage to get the encrypted text. use t Brapi.Utilities.DecryptText to pass the encrypted text.

     

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    The Smart Integration Connector is very new, some things might be a bit undocumented.

    In this particular case, I suspect that the team meant for something like classic .Net .config encryption  to be used on your server, but I would strongly encourage you to contact Support and/or file an enhancement on IdeaStream.

  • Mark_vB's avatar
    Mark_vB
    New Contributor
    2 years ago

    The one way to overcome this is to give a domain account access to the DB and then run the "OneStream Smart Integration Connector Gateway" service using the same domain account. You can then leave the user credentials out of the connection string.

    • Ryan_Berry's avatar
      Ryan_Berry
      New Contributor III
      2 years ago

      This is in fact a supported scenario to help with this situation.  The option for the connection string you would use is called 'integrated security' and would allow the user-account the SIC service is running under to be used as the credential.

       

      This unfortunately isn't supported by all database providers such as AS400 connections.  This has always been something we have thought about but were not sure the best approach that would be acceptable by customers.  For instance, encrypting the contents of a database connection string would require some sort of key also be stored to 'unencrypt' the contents.  The challenge here is that while the database credentials would be encrypted, keys that are needed to decrypt would also need to be stored someplace such as the config file.   Alternatively, if we were to store these credentials inside a key vault, we would need to store credentials for that in some location which could also defeat the purpose.  

       

      Would it be acceptable to have one side of the encryption key stored inside the SIC configuration file that we would then use to encrypt/decrypt the credentials stored?  This path seems to be the best approach, but still involves some level of data leakage with respect to the actual key used to decrypt also being stored.  I'm curious about the community's feedback as we work to enhance and refine the SIC capabilities.  Thanks in advance!

  • Grant's avatar
    Grant
    New Contributor
    2 years ago

    Our client is wanting to use a Windows Service account for SQL database access in which case relying on SQL integrated security will be the only option. For the integrated security to work though does the Service account have to be logged into the SIC Local Gateway server or can you just run the OneStream SIC service as the Service user?

    • Ryan_Berry's avatar
      Ryan_Berry
      New Contributor III
      2 years ago

      You can simply run the windows service under an appropriate active directory user account and any connection to a relational database with a connection string using integrated or trusted security.  

      Here's an example sql server connection string you would use after setting up the service to run under a specific user

      Server=myServerAddress;Database=myDataBase;Trusted_Connection=True

       

      This is a supported approach to authenticating to data sources with sic. 

       

  • mithun_laha's avatar
    mithun_laha
    New Contributor III
    3 months ago

    I am trying to connect in SFTP. I did create a smart integration function and calling it from extensibility rules but getting the issue as HostName is not set. ObjectResultValue is null or empty. 

    • Ryan_Berry's avatar
      Ryan_Berry
      New Contributor III
      3 months ago

      Hello mithun_laha ,

         Can you provide a brief example of what calls you're making in your code?  Is it the remote BR that is returning this failure?  

    • Nikpowar97's avatar
      Nikpowar97
      Contributor
      3 months ago

      Hey mithun_laha ,

      Did u set up WinSCP to connect to the SFTP server on the client environment where the SFTP server can be accessed remotely on the OS Cloud App?

      you might have created a Direct Connection with your SFTP details like port number and host name to access the Smart Integration Connecter page. (System Pane)
      u are using the Smart integration function to get stored information like SFTP password, user name, etc.
      using the ApiLibrary.GetSmartIntegrationConfigValue("Key as String to get the password value").

      and u are using a  BRApi.Utilities.ExecRemoteGatewayBusinessRule to retrieve the results.
      As per my understanding,

      A database connection additionally with the Direct connection needs to setup to connect OS Cloud app Client Environment.If you notice and try to compile your smart integration function. it will ask for a database gateway connection to compile your code.
      In your 
      BRApi.Utilities.ExecRemoteGatewayBusinessRule function u need to mention the database connection name as the remotehostname to get details from the direct connection on the remote client environment.

      Dim remoteResults As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si,smartintegrationBRName,Nothing,database connection gateway name, function name in Smart integration Function BR name,String.Empty,False,600)

      If you have followed these steps and still facing an issue please check by hardcoding the credentials in the WinSCP session in your extensibility rules to check your connection. (I hope u have used the WinSCP.dll in the referenced assemblies). OR if you have issues connecting with the Smart integration function please check the SIC logs.

      I am not an expert at this but I have connected to Amazon S3 using a direct connection before. a similar approach should work here.







      • mithun_laha's avatar
        mithun_laha
        New Contributor III
        3 months ago

        Hello,

        Your assumption is correct. I am using SFTP with WinScp. I have a existing database connection in SIC. Can I use that in BRApi.Utilities.ExecRemoteGatewayBusinessRule

  • mithun_laha's avatar
    mithun_laha
    New Contributor III
    3 months ago

    I am not sure if the remote BR is giving issue.  I tried print the output of the remote BR but didn't get anything. 

    • Ryan_Berry's avatar
      Ryan_Berry
      New Contributor III
      3 months ago

      mithun_laha ,

          We have an example posted to connect without a remote business rule using a feature of SIC called direct connect.  This works well for SFTP connections and could be employed to simplify the approach -- Use Smart Integration Connector (onestream.com)

      What occurs when you use this direct connection approach is the gateway acts as a sort of proxy to the internal/protected endpoint making it only accessible by OneStream integrations.    

      Are you still experiencing issues getting things to work?

       

      • mithun_laha's avatar
        mithun_laha
        New Contributor III
        3 months ago

        Hi Ryan,

        Its working now. I have to use database connection while using BRApi.Utilities.ExecRemoteGatewayBusinessRule

        Thank you,
        Mithun