Microsoft SQL Server ODBC: Connecting As A Different User

[djslp]

My company is implementing RStudio Server Open in order to centralize our data science.

We trying to create a centralized account that would be set up as an ODBC connection for various users to access as they develop. This would allow users to easily develop without worrying about what database permissions they would need.

We set up this account as an ODBC connection:

[Data_Source_Name]
Driver=path/to/the/driver (downloaded from https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15)
Server=Server_Name
UID=Centralized_User_Name
PWD=Centralized_User_Password
Database=The_Database
Language=us_english
Integrated_Security=True
Trusted_Connection=Yes

We are able to successfully connect using this ODBC Data_Source_Name, but instead of using the UID and PWD supplied in the ODBC DSN, the user's local account is what ends up being used in the con object:

con <- DBI::dbConnect(odbc::odbc(), "Data_Source_Name")

dbGetQuery(con, "SELECT SYSTEM_USER")

> DOMAIN\\Local_User

dbGetQuery(con, "SELECT CURRENT_USER")

> DOMAIN\\Local_User

Why are the SELECT SYSTEM_USER and SELECT CURRENT_USER not returning DOMAIN\\Centralized_User_Name? We are using Kerberos authentication. Are there any extra driver arguments that need to be declared?

If this is not a best practice, I am also open to exploring other methodologies. If users could provide documentation and examples, that would be appreciated.

[grrrck]

I think the issue may be Trusted_Connection=Yes. When this is enabled in the DSN, then kerberos or Windows Authentication is used to authenticate the user/connection and UID and PWD are ignored.

If the centralized user name and password are accounts on SQL Server, you can try disabling Trusted_Connection to use SQL Server's authentication, in which case the user/password from the DSN should be used for auth.

Quoting from stack overflow:

A trusted connection is the same thing as using Windows Authentication in SQL Server 2005. Authentication is done by the domain, and authorization is handled by SQL Server.

SQL Server can also use its own logins, such as the sa user. These are both authenticated and authorized by SQL Server. They are only viable if SQL Server is run in Mixed Authentication Mode.

MSDN has a good article about choosing the right Authentication Mode. It's a good primer.

[djslp]

I tried using dbConnect with the Microsoft ODBC Driver for SQL Server arguments (https://db.rstudio.com/databases/microsoft-sql-server/) for both the central account and the local account. Removing the Trusted_Connection argument results in the following errors:

Error: nanodbc/nanodbc.cpp:983: 00000: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'Centralized_User_Name'. 

Error: nanodbc/nanodbc.cpp:983: 00000: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'Local_User'. 

[grrrck]

Is the central account a Windows account or a user in the database?

[grrrck]

Oops, I see I missed the fact that you're using Kerberos auth for the centralized user. In that case, I don't think the problem is solvable by changing the DSN or the dbConnect() parameters. Instead, your users would either need to re-auth with Kerberos using kinit Centralized_User_Name or you'd need to work out a pooled connection. The latter option isn't something I've done before but it's hinted at here: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication?view=sql-server-ver15

[djslp]

Thanks grrrck. I will let my IT look into this option.

I have also dug up some extra documentation for them. I'm posting it here so I can look back on it:

https://docs.rstudio.com/ide/server-pro/index.html

https://solutions.rstudio.com/auth/overview/

[zivan]

Perhaps you should try Integrated_Security=False

[djslp]

Solved the issue.

We had kinit issued for both the Local_User and the Centralized_User_Name. I think what was happening was that while both users were valid, the Local_User kinit ticket had priority when executing the query.

Once the kinit ticket was removed from Local_User, the Centralized_User_Name was returned when executing dbGetQuery(con, "SELECT SYSTEM_USER").

Below are the DSN settings we set up in our odbc.ini file:

[Data_Source_Name]
Driver=driver
Server=server
UID=Centralized_User_Name
PWD=password
Database=database
Language=us_english
Integrated_Security=True
Trusted_Connection=Yes