DBI::dbconnect - connection to postgres server works to MSSQL server does not (though it works with DBeaver)

[tobias.gradinger]

I have a problem connecting to a MSSQL DB from Rstudio
The following code

SRC.connection <-
  DBI::dbConnect(odbc::odbc(),
                 Driver = "SQL Server",
                 Server = 'IP',
                 Database = "DBname",
                 UID = 'uid',
                 PWD = rstudioapi::askForPassword("Database password"),
                 Port = port)

throws the following error message:
Fehler: nanodbc/nanodbc.cpp:983: 01S00: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server existiert nicht oder Zugriff verweigert. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). [Microsoft][ODBC SQL Server Driver]Ungültiges Attribut für die Verbindungszeichenfolge

Using the same credentials in DBeaver works perfectlly fine.

Also

DWH.connection <-
  DBI::dbConnect(odbc::odbc(),
                 Driver = "PostgreSQL ANSI",
                 Server = 'IP',
                 Database = "dbname",
                 UID = 'uid',
                 PWD = rstudioapi::askForPassword("Database password"),
                 Port = 5432,
                 bigint = "numeric",
                 encoding = "latin1")

works perfectlly fine to connect to a postgres server in the same network.

Does anyone have any idea why this does not work as expected?

[Edpflager]

First try changing the port line to Port = 1433 ( or whatever port your SQL Server is setup on) Then make sure your SQL Server firewall is allowing connections on that port. Third see if you can connect to the SQL Server with the free Azure Data Studio application using your connection info.