Hello R Studio Community,
Our small dept takes care of a couple of SQL databases, however now we are getting into companion program development.
We currently have two servers- One on-prem, and one running on a Datacenter OS in Azure. Both are running SQL Server.
The one On-Prem has been around since about ~2015ish and has been more than likely transferred between server upgrades.
A colleague prior to me had built a program that would scrape the accessible DBs to a SQL User (Right after a prompt asking them for their Login and Password and pass those variables along).
However now we're running into the issue of upgrading the program and also adding expandability to our Azure server.
My first issue- In R Studio under the Connections tab after this segment is run-
db_conn_onprem <- DBI::dbConnect(odbc::odbc(),
Driver = "SQL Server",
Server = Sys.getenv("server"),
Database = Sys.getenv("database"),
UID = Sys.getenv("db_user"),
PWD = Sys.getenv("PWD")
We see that the Connections tab shows On-Prem Connections Tab.
However when connecting to Azure, we don't encounter any issue at all, and are presented with Azure Connections Tab.
We have a query that will return the user's accessible DB tables minus the system tables in SQL:
EXECUTE AS LOGIN = 'db_user' SELECT name
FROM master.sys.sysdatabases
WHERE dbid > 4
AND HAS_DBACCESS(name) = 1
ORDER BY name ASC
However, the output of that returns this
Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][SQL Server][SQL Server]Cannot execute as the server principal because the principal "db_user" does not exist, this type of principal cannot be impersonated, or you do not have permission.
<SQL> 'EXECUTE AS LOGIN = 'db_user' SELECT name FROM master.sys.sysdatabases WHERE dbid > 4 AND HAS_DBACCESS(name) = 1 ORDER BY name ASC'
I believe the error to be linked to the initial connection to SQL for On-Prem since that connection is showing as "Guest". The connection is certainly established, it even lists the databases in the Connections Tab, but does not allow you to explore the tables (As should happen for guest).
Our variables and environment work perfectly fine for the Azure connection, however just this one segment is being dropped.
Here's what I've tried:
Ensure that all On-Prem databases are owned by a set account
Ensure that all users and permissions align with Azure
Ensure all roles have the same permissions as Azure
I'm trying my hardest here but I'm at about my wits end for this one and can find no other resource for the errors I'm running into. While it does yell at you for not having impersonation rights, I can't find anywhere in the Azure server where this is ticked largely for any account. Granting these rights to a particular account does not fix the issue.
Any help or guidance would be most appreciated.