• Replies 12 replies
  • Subscribers 355 subscribers
  • Views 415 views
  • Users 0 members are here
Related

Direct Access to Database for RAMP Users

Carlo Palazzi
$organization

Hi All,

A number of users here at the RA have found that having direct access to the database would greatly facilitate their work. I know that there are security issues involved, but none of us here are Microsoft server experts, so I was wondering if someone could explain why server partitioning for different organisations as described here https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning, coupled with use of asymmetric keys (https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-server-certificates-and-asymmetric-keys?view=sql-server-ver15) would still cause issues. 

The main use cases our team has found are:

Directly querying data with python through a SQL connection would greatly streamline workflow for analysis, and might actually be necessary to create self-updating algorithms. 

The software development team here use DataGrip (https://www.jetbrains.com/datagrip/) - an IDE that allows access to MySQL, PostrgreSQL and MSSQL in one place. We can now work on all our other databases in one place and it would be a huge help to access Tessitura's MSSQL server in the same way. 

Is there any way we could have this access now? Is this a functionality that is being considered for future releases? 

Thanks,

Carlo

Parents
  • $organization

    ,

    You mention a potential solution.  I'm not clear what is actually keeping you from accessing the database over ODBC?  Are you a RAMP organization? Part of a consortium? 

    I know back in the day at BAM we solved the kind of problem you are describing by creating a specific subset of views and giving certain Active Directory accounts access to these views over and ODBC connection. This worked well with MS Excell and other R & Python approaches.  (This would only work for a local instance of SQL Server that was part of the Active Directory Domain the clients are using to log in to their workstations.). But this worked very nicely.  The DBA created secure data views.  And those who were granted rights to the data could access.

    I happen to be a RAMP user at this time.  Because we do not share an Active Directory and firewall configuration this is tough.  I'm working on ways to solve this issue.  If you are on RAMP let me know and we can discuss.

    --Tom 

  • $organization in reply to Tom Brown (Past Member)

    Hi ,

    We are indeed on RAMP. Your approach of creating a subset of views accessible via an ODBC connection sounds very promising. I've seen in the docs that custom (LT_?) tables can be made available through the REST API. I'm guessing this means that the REST API has ODBC capability. Is this functionality available to RAMP users? If so it would be a start for now to run scheduled procedures to update custom tables that can be accessed through the REST API. 

Reply
  • $organization in reply to Tom Brown (Past Member)

    Hi ,

    We are indeed on RAMP. Your approach of creating a subset of views accessible via an ODBC connection sounds very promising. I've seen in the docs that custom (LT_?) tables can be made available through the REST API. I'm guessing this means that the REST API has ODBC capability. Is this functionality available to RAMP users? If so it would be a start for now to run scheduled procedures to update custom tables that can be accessed through the REST API. 

Children