search

microsoft / vscode-mssql

Visual Studio Code SQL Server extension.
Other
1.55k stars 458 forks source link

SQL Connection not established with Application Intent = Read-Only #18450

Open Mrugendra27 opened 1 week ago

Mrugendra27 commented 1 week ago

Steps to Reproduce:

  1. While creating SQL server Connection, after entering all the required fields
  2. Click "Advanced" in the left bottom
  3. Under "Database Initialization" choose "Application Intent" = "Read-Only" Image
  4. Return to the main connection creation by pressing 'X' on the Advanced panel.
  5. Click on "Connect"
  6. Get error "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)"Image
  7. Change the "Application Intent" to "Read-Write"
  8. Clicking on "Connect" is successful.
Benjin commented 1 week ago

Can you try increasing the connection timeout from 15 seconds to 60 seconds in case the database was an Azure DB that needed to be woken up?

Also, if you could confirm that the same credentials work without Application Intent set to Read-Write.

Mrugendra27 commented 1 week ago

Can you try increasing the connection timeout from 15 seconds to 60 seconds in case the database was an Azure DB that needed to be woken up?

Also, if you could confirm that the same credentials work without Application Intent set to Read-Write.

Thank you for the response.

  1. Increasing timeout from 15 seconds to 60 seconds does not change the behavior.By the way it is not an Azure DB.

  2. a. Application Intent set to "Read-Write" -- works, connects immediately b. Application Intent set to Empty -- works, connects immediately

N.B - I did further testing, and I am not sure, but this could be playing a role in this: The way the connection is established is like so: Client machine 1 ==> Jump Linux server ==> SQL Server I say that this could be playing a role, it is because I AM able to connect from a machine that directly has access to the SQL Server (with the same credentials with read-only intent; client machine 2 ==> SQL Server) But if it so, then I wonder - how is it able to connect with Read-Write or Empty intent in the above connection configuration (Client machine 1 ==> Jump Linux server ==> SQL Server)

Benjin commented 20 hours ago

Are you able to test with another client (SSMS or Azure Data Studio) to see if the Read-Only has the same issue when connecting through your Jump Linux server?