We have an Rstudio server running on an Ubuntu server. We also have a Microsoft SQL server where we have a lot of data and a user account. On the Rstudio side we have multiple users who should be able to connect to the SQL server to retrieve data but who should not be able to see the password used to connect to the server. To me this look like a pretty good example of where to use "Integrated security with DSN" as mentioned in the documentation here but I only find that short summary and elsewhere a short reference to service accounts here. To me it seems like my Google-fu is failing me and that there should be a more comprehensive guide somewhere online?
So to summarise I want to replace this code snippet
con <- dbConnect(odbc(),
Driver ="/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1",
Server = "our_server",
Database = "our_database",
UID = "shared_account_UID",
PWD = "password",
TrustServerCertificate="yes"
)
odbcListObjects(con)
With something like
con <- DBI::dbConnect(odbc::odbc(), "My DSN Name")
And in way that ensures that a subset of my Rstudio server users are able to use that to connect but without seeing the password.
Hi Tomas,
Using UID and PWD normally means that this is a service account in SQL Server. When using "integrated security", you are not using an SQL Server user but a user in Active Directory (either personal user or service account). What kind of authentication set-up would you prefer?
The short answer is pretty much "something that keep working but doesn't expose our password to the Rstudio user" as my experience in this particular domain is quite limited.
The slightly longer answer is that it is for a university network where we run Ubuntu 20.04 LTS on the Rstudio server we use. If we can use the local server users rather than trying to connect to Active Directory. On the SQL server side we would rather not touch anything unless there are clear instructions so that we can plan it with the other department managing it.
Please ask the team managing the SQL Server what authentication types (username/password or integrated security) they support. Changing that would happen on the SQL Server side first.
You cannot hide the password completely from the user if you are authenticating with username and password, i.e. user defined in SQL Server, simply because the password is part of the connection string. You can make the password itself less obvious. In the easiest case you just define a DSN in /etc/odbc.ini that contains not only the server etc but also username and password. Everybody who needs to use this DB connection needs to be able to read the file and can therefore read the password in plain text. But there is no need to put it into the source code and the enduser does no have to know it. Would that be enough for your usecase?
I got a bit confused about this as we switched from integrated security which I used on my own laptop (with Windows) to getting a user name and password as we wanted to access the database from the Rstudio community server. Based on your reply I get the impression that integrated security could work from Ubuntu as well or am I misreading you? If integrated security would enable us to hide the password from a Ubuntu server then it would be a workable solution I think.
Regarding the /etc/odbc.ini file I was hoping that it would be possible to have a separate (automated) account being passed the request to retrieve data, make the connection and just pass on the output to the human Rstudio user but I guess that would be an entire separate development project.
Otherwise I guess the next step is to get a keycloak service on our network to facilitate connections.
Yes, integrated security is possible from Ubuntu as well, if your ODBC driver supports it. I have not checked, but I assume that MS's ODBC driver does support that. Your users will need Kerberos tickets to use that, for which you could do something like this:
join the Linux server into the AD domain using realmd
now users can authenticate with their AD username & password
once logged in, users can call kinit (and provide their password again) to create a Kerberos ticket
The last step could be automated with the PAM session support in Posit workbench.
Indeed. Such an "ODBC proxy" would be pretty cool, though!
I am not sure how Keycloak (SSO via OpenID Connect or SAML) should help here. Can you elaborate?
Thanks a lot, the realisation that ODBC drivers can support this is something we hadn't really considered. We pretty much got two independent ecosystems and haven't spent enough time looking at ways to integrate.
Regarding keycloak I was under the impression that we could use it so that the Rstudio user would verify his credentials to Keycloak and then Keycloak doing the verification to the SQL server but I may be wrong there as I've just scraped the surface of the matter.
I think we got enough of an idea now to work properly on this with the ODBC driver on the Ubuntu server as alternativ one and the /etc/odbc.ini file option as a backup.