dbConnect() doesn't work for MSSQL Server when database name contains "-" hyphen

General
, , ,
1

Firstly, I'm having an issue where I can't access databases on a particular MSSQL server if said databases contain a hyphen ("-") in the name, like DATA-WAREHOUSE. All the other databases on said MSSQL Server, the ones without hyphens, work just fine on the same connection.

Secondly, the Connections Pane in the RStudio doesn't show the contents of any of the databases that have hyphens in their names (for the MSSQL Server in question).

On the first issue, the error messages are varied as I've made many different attempts but simply writing Database = "DATA-WAREHOUSE" (where Database = "nohyphen" works) in the dbConnect function call gives up the following error (which, to be clear is not descriptive as accessing the hyphenated databases with the service principal in question is not an issue in SSMS or dBeaver)

Error in `dbConnect()`:
! ODBC failed with error 00000 from [Microsoft][ODBC Driver 18 for SQL Server][SQL Server].
✖ Cannot open database "DATA-WAREHOUSE" requested by the login. The login failed.
• Login failed for user 'myserveruser'.
• Invalid connection string attribute
ℹ From nanodbc/nanodbc.cpp:1184.
Run `rlang::last_trace()` to see where the error occurred.

Example connections to the same server, different databases

library(DBI)
library(odbc)
library(dbplyr)
library(tidyverse)

connection_success = dbConnect(odbc(), Driver = "drivername", 
                               Server = "myserver,1234", Database = "THISWORKS", 
                               TrustServerCertificate = "yes", 
                               UID = Sys.getenv("myserveruser"),
                               PWD = Sys.getenv("myserverpassword"), 
                               Port = 1234)

no_connection = dbConnect(odbc(), Driver = "drivername", 
                               Server = "myserver,1234", Database = "THIS-DOESNT", 
                               TrustServerCertificate = "yes", 
                               UID = Sys.getenv("myserveruser"),
                               PWD = Sys.getenv("myserverpassword"), 
                               Port = 1234)

still_no_dice = dbConnect(odbc(), Driver = "drivername", 
                               Server = "myserver,1234", Database = "[THIS-DOESNT-EITHER]", 
                               TrustServerCertificate = "yes", 
                               UID = Sys.getenv("myserveruser"),
                               PWD = Sys.getenv("myserverpassword"), 
                               Port = 1234)

just_nope = dbConnect(odbc(), Driver = "drivername", 
                               Server = "myserver,1234", Database = DBI::SQL("CRYING-EMOJI"), 
                               TrustServerCertificate = "yes", 
                               UID = Sys.getenv("myserveruser"),
                               PWD = Sys.getenv("myserverpassword"), 
                               Port = 1234)

Connecting using dbConnect() without specifying the Database name works, but then it just fails when I try to reference the database with in_catalog(), or I()

tbl(successful_con_object, I("[DATA-WAREHOUSE].myschema.my_table"))

Error in `db_query_fields.DBIConnection()`:
! Can't query fields.
ℹ Using SQL: SELECT * FROM [DATA-WAREHOUSE].myschema.my_table "q03" WHERE (0 = 1)
Caused by error in `tbl()`:
! ODBC failed with error 00000 from [Microsoft][ODBC Driver 18 for SQL Server][SQL Server].
✖ The server principal "myserveruser" is not able to access the database "DATA-WAREHOUSE" under the current security context.
• <SQL> 'SELECT *
• FROM [DATA-WAREHOUSE].myschema.my_table "q03"
• WHERE (0 = 1)'
ℹ From nanodbc/nanodbc.cpp:1802.
Run `rlang::last_trace()` to see where the error occurred.

Any and all help welcome!

2

Does it help if you use dbQuoteIdentifier("THIS-DOESNT") as the database argument?

3

Thanks for the suggestion but sadly that doesn't work either. I've tried using it both with and without a connection object.

connection_success = dbConnect(odbc(), Driver = "drivername", 
                               Server = "myserver,1234", Database = "THISWORKS", 
                               TrustServerCertificate = "yes", 
                               UID = Sys.getenv("myserveruser"),
                               PWD = Sys.getenv("myserverpassword"), 
                               Port = 1234)

no_connection = dbConnect(odbc(), Driver = "drivername", 
                               Server = "myserver,1234", Database = DBI::dbQuoteIdentifier("THIS-DOESNT"), 
                               TrustServerCertificate = "yes", 
                               UID = Sys.getenv("myserveruser"),
                               PWD = Sys.getenv("myserverpassword"), 
                               Port = 1234)

#Error: unable to find an inherited method for function ‘dbQuoteIdentifier’ for signature ‘conn = "character", x = "missing"’

no_connection = dbConnect(odbc(), Driver = "drivername", 
                               Server = "myserver,1234", Database = dbQuoteIdentifier(conn = connection_success, x = "THIS-DOESNT"), 
                               TrustServerCertificate = "yes", 
                               UID = Sys.getenv("myserveruser"),
                               PWD = Sys.getenv("myserverpassword"), 
                               Port = 1234)

#Error in `dbConnect()`:
#! ODBC failed with error 00000 from [Microsoft][ODBC Driver 18 for
#  SQL Server][SQL Server].
#✖ Cannot open database ""THIS-DOESNT"" requested by the login. The login
#  failed.
#• Login failed for user 'myserveruser'.
#• Invalid connection string attribute
#ℹ From nanodbc/nanodbc.cpp:1184.
#Run `rlang::last_trace()` to see where the error occurred.
4

What about using wrapping THIS-DOESNT in backticks (`), then wrapping that in quotes (")?

5

Really appreciate the suggestions, but sadly it didn't work. They all get the same type of #Error indbConnect()\ as shown at the bottom of my original post, only the second line of that error message changin. For brevity I've added only said second line of the error message.

What I've now tried

# Backticks in double quotes
Database = "`DATA-WAREHOUSE`"
#✖ Cannot open database "`DATA-WAREHOUSE`" requested by the login. The login failed.

# Backticks in single quotes
Database = '`DATA-WAREHOUSE`'
#✖ Cannot open database "`DATA-WAREHOUSE`" requested by the login. The login failed.

# Brackets in backticks in double quotes
Database = "`[DATA-WAREHOUSE]`"
#✖ Cannot open database "``" requested by the login. The login failed.

# Brackets in backticks in double quotes in single quotes
Database = '"`[DATA-WAREHOUSE]`"'
#✖ Cannot open database ""``"" requested by the login. The login failed.

# Double quotes in single quotes
Database = '"DATA-WAREHOUSE"'
#✖ Cannot open database """" requested by the login. The login failed.

# Double quotes in double quotes in single quotes
Database = '""DATA-WAREHOUSE""'
#✖ Cannot open database """DATA-WAREHOUSE""" requested by the login. The login failed.

# Backticks in double quotes in single quotes
Database = '"[DATA-WAREHOUSE]"'
#✖ Cannot open database """" requested by the login. The login failed.

# Brackets in double quotes in double quotes in single quotes
Database = '""[DATA-WAREHOUSE]""'
#✖ Cannot open database """""" requested by the login. The login failed.
6

Any chance you can assign a "data source name" to the DBs with hypenated names and use that in the dbConnect calls? I'm not a Windows user, but apparently there's an "ODBC Data Source Administrator" program (?) that can assign DSNs.