Azuredatastudio: I have to re-authenticate with Azure SQL Servers multiple times a work day

[jefflafay]

Issue Type: Bug

There isn't a particular pattern that I noticed but this issue makes it a pain to use the app

I have to click disconnect/connect in a query tab to reconnect again so it times out.

Also, the connections in the server tab timeout often. They have the green circle on the server icon but when you drill down into tables, sprocs, etc. an error occurs and I have to manually disconnect and then click the server again to have it reconnect.

Azure Data Studio version: azuredatastudio 1.14.1 (f9b968c1ae514a0d1210788629e1c69125857279, 2019-12-24T02:42:26.303Z)
OS version: Windows_NT x64 10.0.18362

System Info

|Item|Value|
|---|---|
|CPUs|Intel(R) Core(TM) i7-7700HQ CPU @ 2.80GHz (8 x 2808)|
|GPU Status|2d_canvas: enabled
flash_3d: enabled
flash_stage3d: enabled
flash_stage3d_baseline: enabled
gpu_compositing: enabled
metal: disabled_off
multiple_raster_threads: enabled_on
oop_rasterization: disabled_off
protected_video_decode: enabled
rasterization: enabled
skia_renderer: disabled_off
surface_control: disabled_off
surface_synchronization: enabled_on
video_decode: enabled
viz_display_compositor: enabled_on
viz_hit_test_surface_layer: disabled_off
webgl: enabled
webgl2: enabled|
|Load (avg)|undefined|
|Memory (System)|15.89GB (5.58GB free)|
|Process Argv||
|Screen Reader|no|
|VM|0%|

Extensions (8)

Extension|Author (truncated)|Version
---|---|---
admin-pack|Mic|0.0.2
admin-tool-ext-win|Mic|0.0.2
agent|Mic|0.45.0
dacpac|Mic|1.1.0
import|Mic|0.12.0
managed-instance-dashboard|Mic|0.3.9
profiler|Mic|0.11.0
schema-compare|Mic|1.1.0

[Charles-Gagnon]

ADS shouldn't be doing anything to close connections. Have you looked at the server logs to see if that indicates why the connection is being closed? You can start with https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms176013(v=sql.90)

What server type is this? (On-Prem, Azure, other)

[jefflafay]

Azure Sql Server. I suppose I'll look at the server to see what it could be, I don't get this issue with SSMS so I didn't expect it to occur with ADS.

[jefflafay]

Do you have any recommendations to what I could look at on the azure sql server instance? The table you recommended shows current active sessions but is there somewhere I could look to see where my session ended from errors? I'm just not sure what to look at first.

[Charles-Gagnon]

I'd start with this https://docs.microsoft.com/en-us/azure/sql-database/troubleshoot-connectivity-issues-microsoft-azure-sql-database

If you open the developer console (Help -> Toggle Developer Tools -> Console tab) do you see any errors that seem like they might be relevant?

You can also enable debug logging:

1. Open settings (command palette -> Settings (UI)
2. Set the Mssql: Tracing Level to All
   
3. Restart ADS
4. Wait until the issue repros
5. Open command palette and execute the MSSQL: Show log file command, then choose sqltools.log
6. See if that has any errors that look related - it'd also help if you either uploaded log file here or sent it directly to me ([email protected]). There is going to be stuff such as server names and paths in that file so feel free to do any cleaning of the data before sending it over.

[magnusbratt]

Hi @Charles-Gagnon , I have the same / a similar issue in #8903. I followed your suggestion above, and there were no errors in the Console tab that seemed relevant. I will email you my sqltools.log. Very grateful for any recommendations.

[osmant]

This occurs to me frequently as well. When I am not active in Azure Data Studio more than approx 30 minutes, and try to run a query on active session, I also experience connection drop.

According to https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-to-an-azure-sql-database?view=sql-server-ver15#connections-dropped , Azure SQL database service drops those idle connection after 30 mins of inactivity.
In SSMS, when I leave my pc idle for a while and come back and run a query, query execution does not start immediately (takes 1-2 seconds to see the timer countng) and then executes the query. This indicates to me, SSMS is actually retrying to establish connection if the connection is dropped. Looks like same mechanism does not exists in ADS or not working properly.

[jefflafay]

@osmant I'm pretty sure this is my scenario too. It's annoying and intellisense stops working for me when this happens. It makes this tool a pain to use for Azure SQL work. I want Azure Data Studio to be a useful tool but it's a bit lackluster and little comical that the UX for working in Azure data is not so good.

@Charles-Gagnon is this functionality somewhere in the road map/backlog for this application by chance? I'd rather work with this tool than SSMS but I can't rely on it until reconnecting isn't a manual chore. Thanks!