Connect to Azure database from R-CodePudding

I have a database in Azure. Below is the connection string i have to connect to it from R

Server=tcp:biod.database.windows.net,1433;Initial Catalog=*******;Persist Security Info=False;User ID=****;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

I have used above string to connect but i see some error here

library(RODBC)
library(rlang)

## Connnection to sql server
conn <- odbcDriverConnect(connection = paste0("Driver={SQL Server Native client 11.0};
                            server=tcp:biod.database.windows.net,1433;Initial Catalog=******;Persist Security Info=False;User ID=****;Password=*****;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))

Error message below

 [RODBC] ERROR: state 08001, code 0, message [Microsoft][SQL Server Native Client 11.0]Invalid value specified for connection string attribute 'Encrypt'

Can anyone help me here?

CodePudding user response：

I created SQL database in Azure. connectionstring:

Server=tcp:<serverName>.database.windows.net,1433;Initial Catalog={databasename};Persist Security Info=False;User ID={userName};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

As per my knowledge above connection string is for connecting to .net webapps. we need change that to according to our requirement while connecting it to in R we should mention yes instead of True for Encrypt. The Encrypt=Yes is very important for SQL Azure as your connection may be outside your network and the connection may be refused without it. I think due to that you are getting error.

I connected to azure SQL database using below code in R

library(RODBC)
sqlServer <- "dbservere.database.windows.net"  #Enter Azure SQL Server
sqlDatabase <- "databaseName"                
sqlUser <- "userName"            
sqlPassword <- "Password"        
sqlDriver <- "SQL Server"        

connectionStringSQL <- paste0(
  "Driver=", sqlDriver, 
  ";Server=", sqlServer, 
  ";Database=", sqlDatabase, 
  ";Uid=", sqlUser, 
  ";Pwd=", sqlPassword,
  ";Encrypt=yes",
  ";Port=1433")
conn <- odbcDriverConnect(connectionStringSQL)

It run successfully without error. Image for reference:

enter image description here

Database is connected successfully. Image for reference:

enter image description here