August 8, 2021 at 12:13 am
Just like someone can be an "Accidental DBA", I am the "Accidental TFS Administrator" at work. And now we're moving, slowly, to Azure DevOps Services (ADS). One of the reasons for moving to ADS is because there's a group of epidemiologists at work who want to capture their code using Git. (At work, they've been using TFVC for longer than I've worked there. Bottom line, we don't use Git at all at work.) Being epidemiologists, they're used to doing lots of data analysis. They use both SAS and R. I've never seen SAS. Here on SSC, because of Steve's posts, I've seen some R, although I wouldn't call myself an R developer.
Anyway, we've got this group of sophisticated users, who want to store their SAS and R code into a Git repository. The reason for why they want to Git is because whatever tools they use, it only works with Git. For now, let me just talk about R. Like I said, I know so little of R that I don't call myself conversant in it at all.
Now, for other things which complicate this. Our chief security officer (CSO) is certain that these users have leaving connection string in plain text, in their R code. We talked about this in a Zoom meeting. Upon a lengthy discussion, it because apparently that the users access the databases they do, using integrated security. Nevertheless, the CSO is not convinced. He is firmly of the opinion that the epidemiologists who are doing the R (and SAS) coding are leaving connection strings scattered all about in their code.
So, let me ask people in this forum - how does R code save connection strings? If this were a .NET app, you could either save those connections to the database use integrated security, in which case there is no credentials saved anywhere. Or you would put them into a Web.Config or App.Config file, which hopefully is obfuscated somehow. Or since we're moving to ADS, they could be saved either in Azure Pipeline library or Azure Key Vault. So, how does R do it? Where are connections strings saved? How can we (those of us in IT) determine that either they user integrated security or the credentials are someplace. Is there a common place to look for database credentials in R?
Rod
August 9, 2021 at 1:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 9, 2021 at 4:22 pm
If I remember right with R (it's been a while since I touched it as it isn't really related to my job), the file you create is essentially just a text file and you need to connect to the database prior to running your R query. Similar to how SSMS works.
I have used SAS Visual Analytics in the past and the connection is configured on the SAS server and not stored in any of the queries that run against the database.
Now, the above should be easy to confirm as both R and SAS Visual Analytics store their files in plain text. So if you have access to Git, you can grab a few files as a sample and validate. Or you could pull down a full repository and run a grep tool (grep in linux/unix, astrogrep in windows for example) to search through for a connection string value and verify that it is not being stored OR if it is being stored, that it is being stored securely (ie no password being stored).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 9, 2021 at 8:40 pm
Thank you, Brian. Useful information. I'm especially happy to learn that neither SAS nor R saves files as some binary file. I'm not familiar with SAS Visual Analytics nor I'm guessing they use RStudio. But text files can be searched, so unless they're clever and trying to be deceptive, I should be able to look through them for connection strings.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply