Pls help me to remote connect to Azure windows SQL DB--fails at Uid/Pwd

Question

i would like to run code scripts (R, SQL, YAML) to access my AZURE windows VM SQL DB.

Please help--requesting online session to have successful connect code line for R, SQL, and YAML.

For R script

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

                  Driver = "ODBC Driver 18 for SQL Server",

                  Server = "tcp:sqlserverxxx.database.windows.net,xxxx",

                  Database = "AzureSQLxxx",

                  Uid = "xxx",

                  Pwd = "xxx"

)

rlang::last_trace()
<error/odbc_database_error>
Error in `DBI::dbConnect()`:
! ODBC failed with error 08001 from [Microsoft][ODBC Driver 18 for SQL Server][258].
✖ TCP Provider: Timeout error .
• Login timeout expired
• Unable to complete login process due to delay in login response
ℹ From nanodbc/nanodbc.cpp:1150.
---
Backtrace:
    ▆
 1. ├─DBI::dbConnect(...)
 2. ├─DBI::dbConnect(...)
 3. │ └─odbc (local) .local(drv, ...)
 4. │   └─odbc:::OdbcConnection(...)
 5. │     ├─base::withCallingHandlers(...)
 6. │     └─odbc:::odbc_connect(...)
 7. └─odbc (local) `<fn>`("nanodbc/nanodbc.cpp:1150: 08001\n[Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Timeout error [258].  \n[Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired \n[Microsoft][ODBC Driver 18 for SQL Server]Unable to complete login process due to delay in login response ")
Run rlang::last_trace(drop = FALSE) to see 2 hidden frames.

Answer 1

@Sheila Varga

Thank you for being patient while we are working on this issue.

Issue:

1. You are using an Azure SQL DB free tier and not able to see tables you created earlier.
2. You are also not able to connect to your Azure SQL DB free Tier from R studio

Cause:

Free Azure SQL DB vcores were all utilized.

Did not install ODBC Driver for SQL

Resolution:

Check the overview page for the database on the Azure portal to see whether you have any remaining free credits for the month. If you choose the Auto-pause the database until next month option, once the free limits are met, the database in inaccessible until the start of the next calendar month. To continue usage with charges, go to the Compute + Storage tab and change the option to enable billing for usage of the database beyond the free amount.

https://learn.microsoft.com/en-us/azure/azure-sql/database/free-offer-faq?view=azuresql#my-vcore-seconds-are-being-used-faster-than-i-expected--how-can-i-see-what-is-using-the-vcore-seconds

Connecting from your Azure SQL Free Tier from R studio

We have successfully installed ODBC Driver from https://learn.microsoft.com/en-gb/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

You Tube example: https://www.youtube.com/watch?v=61BzALfuvLQ

Install the required packages: ODBC, DBI

Please don't forget to mark as accept answer if the reply were helpful.

Regards,

Oury

Answer 2

Hi Sheila Varga,

Welcome to Q&A forum, I understood that you're trying to connect to an Azure SQL Database from your R script, but you're running into a timeout error, the error message involves a failed login process. Here’s a list of actions to trobleshoot the issue that looks like may have a communication issue root cause:

• Check the firewall rules for your Azure SQL Database. Make sure your computer's IP address is allowed to connect. You can configure this in the Azure Portal under "Networking".
• Verify all the connection details are accurate. The server name should follow this format: tcp:<server_name>.database.windows.net,1433, and the username is usually like user@servername.
• Install the latest ODBC Driver for SQL Server. You can download it from the official Microsoft page to ensure compatibility.
• Extend the timeout duration in your connection string if it's timing out. Add Timeout = 30 (or adjust as needed) for better handling of network delays.
• Enable encryption for your connection. Azure SQL requires secure connections, so make sure to include these options: Encrypt = "Yes" and TrustServerCertificate = "No".
• If you need more information on the error, run rlang::last_trace(drop = FALSE) to get detailed insights.
• Test the connection using a tool like SQL Server Management Studio (SSMS) or Azure Data Studio. This helps confirm whether the issue is with the database or the script.

References:

• https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql
• https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16
• https://cran.r-project.org/web/packages/odbc/index.html
• https://cran.r-project.org/web/packages/DBI/index.html

If the information helped address your question, please Accept the answer.

Luis