Intellisense against Azure SQL DBs very inconsistent

[ransagy]

• MSSQL Extension Version: 1.2.0 GA
• VSCode Version: 1.16.1
• OS Version: Windows 10 x64 CU

This is not really new or limited to the current version(s). Ever since i started using this, back in the 0.2.0 days, It behaved more or less the same.

(Note: Only relevant issue i found is the old #508 )

Steps to Reproduce:

1. Open a new SQL file.
2. Connect to an Azure SQL instance.
3. Wait for the Intellisense cache to finish building (according to the status bar).
4. Try and auto-complete something basic, like table names.

Most of the time, I can't get intellisense for Azure to work in any sort of reliable way. The same connection to the same DB can work one moment and not the other. Refreshing the cache sometimes helps, Sometimes it does not.

Is Intellisense in this extension dependent on something when working on an Azure SQL DB? Anything else i can try and diagnose/narrow down?

[brianjorden]

Ok I spent quite a bit of time trying to troubleshoot this issue before I ended up finding this issue and the other "closed-wontfix" issue referenced above. I actually only realized this extension was even supposed to have Intellisense when I was reading the latest changelog.

My primary environment is actually Linux and I connect to an Azure SQL Database instance, but I decided to test this a lot deeper. I used a Windows environment with a brand new installation of VS Code and the mssql extension with no settings/customizations to keep it as pure as I could. When that didn't help, it seemed to be specific to Azure SQL Database as eluded to above. So I decided to test that a bit further by installing a local Express version and finally saw what I REALLY hope to be able to use soon, real Intellisense similar to SSDT.

For the record, I'm not trying to complain here, just excited to see it hopefully coming soon and trying to give as much info as I can to help troubleshoot this. I actually dug through the source a fair bit, but didn't have much luck honestly. I'm inserting two screenshots that have a bit more detail on exact versions of everything and clearly demonstrate the exact same scripts/files/environment behaving very differently between the LocalDB connection and Azure SQL Database.

Connected to Azure SQL Database (it almost seems Intellisense is off?):

Connected to LocalDB (everything works as expected):

There is one seemingly false positive scenario that I hit that I think is worth mentioning and may be related to the inconsistent testing I had and mentioned above. I originally was testing in single file for my example, but the default VS Code settings will attempt to auto-complete word based suggestions from within the same file. You can see what would have looked like a "working" version in the first screenshot where it is suggesting the word "something" because that had been used in the comments. If you have that setting turned on and define the table in the same file, it will seem like it is somewhat working.

Hope this information helps and I'll maintain my little test setup for a while if anyone wants deeper details. Even without full Intellisense, this extension is quickly replacing my need to fire up SSMS on my Windows machine just to check something and excited to see it go further, so thanks for all the work here.

[ransagy]

That describes the situation i was talking about very well, Thank you for sharing and the effort you put into testing :)

[brianjorden]

I should have edited that huge thing down a bit.

In summary, it seems Intellisense is either not working or completely disabled for Azure SQL Database connections.

[kevcunnane]

One thing I'd ask is what is the service tier for the database? What I've seen is that lower tiers (basic or S0), especially with a larger schema, will result in Intellisense taking too long and the query eventually timing out. This leaves you in the state mentioned.

I'd also ask you to see if SSMS 17.2 has the same issues against an Azure database. We use a very similar stack to that so it that works, then we should be able to. If not, it's the same core issue (the DB is throttled enough that Intellisense building does not work).

[ransagy]

SSMS didn't work for me either, but I didn't try much there because we use a few 3rd party extensions for Intellisense there, since azure support natively came only recently.

A common schema i work on has fewer than a hundred objects and i get issues there. non-prod DBs are probably in S1-S3 tiers, prod is definitely P1 and up.

I'll try and verify against that tomorrow and report back.

i do suggest notifying the user in some way that fetching Intellisense failed, perhaps with a message about tiers as well.

it is a bit odd though, to be so dependent on performance in this case. there has to be a more reliable way to implement this, i hope.

[brianjorden]

Well, the plot thickens...and some unexpected and very different resolution. Short version, whatever credentials you are using must ALSO have access to the master database.

When I say credentials I actually mean the specific username/password combination must also be able to connect to the master database directly. So as a workaround, you can create another encapsulated database user in the master database with the exact same username/password, or create a user for that SQL Login in the master database. Kind of a hacky workaround in my opinion, but I'm going to create a separate issue with details more specific to what I've found through some digging.

It seems the confusion around this is because that isn't exactly typical setup for most users/connections with database encapsulated users for Azure SQL Database. What totally threw me off in testing was when I shortcut things a bit and just used the super-global-admin login thing.

I'm now erasing 5 paragraphs of analysis/testing with various new databases being created including the complete failure even after ramping the database instance I care about all the way up to P2 level. I have now gotten this all to work even when I tested scaling DOWN to a Basic tier (not what I normally keep it at, just ruling out possibilities).

@ransagy could you confirm if adding an equivalent user access to your master database resolves this issue for you? At least as a temporary workaround I mean or even as a test if you don't leave that in place.

[ransagy]

As far as i know, the user i connect with does have access, It's a somewhat admin-level account as you said. Did you need to add it specifically to the master DB anyway? I mean by this that i can connect and query around in the master DB with that user.

I tried today to get intellisense running against our P-tier prod DB, and while i initially had it working, one intellisense invoke later - gone. Cache refresh didn't help either.
I think there's more here than query timeouts - Unless the extension constantly tries to re-query for intellisense data and the moment one of those times out (and i still doubt it does against the P-tier DB) you lose Intellisense.

[brianjorden]

As for the performance thing, that wasn't the issue in my case at all. I tried ramping the development database I was using up to P2, still didn't work. Then created a user specifically in master for my SQL Login that I'm typically using and it all started working. To prove it out further, I scaled it all the way down to lower than normal, as the Basic tier and it still worked fine consistently. I then tried another developer account created with identical security/authority/permissions where I hadn't done the user in master yet, and it was not working. Closed/reopened VS Code a few times, refreshing cache, etc trying to get any different result, but seemed finally solid where the one user worked, and the other didn't. Added a user for that other account, closed, and reopened VS Code (for some reason this seemed necessary in testing). The additional user was then working perfectly as well.

The timeout thing doesn't quite line up for what I'm seeing. Updating IntelliSense seems to only last a few seconds (under 10) when it is going to work, but pretty consistently between 40-50 seconds when it isn't working. I did notice even when it is all working it seems to refetch the IntelliSense stuff for basically every new tab. Not a problem when everything is working, but makes testing a bit rough when it is taking way longer and/or not consistent.

@ransagy can you connect to master and try this select * from master.sys.database_principals; to confirm the user/login you are expecting shows up there? If you are actually connected directly to master it won't yell about the three-part name thing.

[ransagy]

The user im using does appear there:

name	principal_id	type	type_desc	default_schema_name	create_date	modify_date	owning_principal_id	sid	is_fixed_role	authentication_type	authentication_type_desc	default_language_name	default_language_lcid	allow_encrypted_value_modifications
USERHERE	5	S	SQL_USER	dbo	2014-10-31 09:20:33.427	2014-10-31 09:20:33.457	NULL	0x010600000000016480000000003B2F7C7CE8FD89A267414C99AAB0AE82CDF9EF	0	1	INSTANCE	NULL	NULL	0

What exact actions did you do to add that additional user? I'll try those here too and see.

[brianjorden]

Ok I also just tested and got the exact same behavior with SSMS 17.2 so I'm pretty confident this is just an underlying oddity on how the IntelliSense info is being fetched. The issue is that although on SQL Server the master database is just a 3-part name resolution away, but with Azure SQL Database it is a more explicit permission/access level.

To recreate this whole thing you can do the following:

-- connect to the master database with a sysadmin type account
use master;
create login test_login with password = 'some-really-good-password-here';

Then...

-- connect to your actual database instance
use [whatever-your-db-name-is];
create user test_db_user for login test_login;
alter role db_owner add member test_db_user;

At this point, you should be able to connect to your database with the new test_login credentials, but I would NOT expect IntelliSense to be working. Once you confirm it isn't working, you can then...

-- connect to the master database
use master;
create user test_master_user for login test_login;

FInally, if you disconnect/reconnect to your database with the test_login credentials, I would expect IntelliSense to start working. Although in SSMS 17.2 it did take 20-30 seconds before it seemed to really kick in after trying a query or two.

I put in the use statements just to help ensure the connection really is to the database you expect. Azure will just throw an error if you attempt a use statement for any database besides the one you are currently connected.

[brianjorden]

Ok, I've now been trying to find info on Azure SQL Database and SSMS 17.2 and seems a decent number of people are having issues with no one really providing answers/resolutions yet. @kevcunnane when you said the VS Code extension is using something similar for IntelliSense, did you mean re-use of the code base? More so, if it is an issue/oddity with how SSMS 17.2 and this extension works, where would the best place to log details actually be?

[kevcunnane]

@brianjorden yes, we use the same Intellisense Parser and the SMO MetadataProvider for connected intellisense that SSMS uses. Our actual implementation that hooks this up (first populating the provider, then querying) is different but it's the same core code.

I think this is the best place to log this. I'm sending a mail to the area experts including @shueybubbles and @Matteo-T to ensure this is on their radar. The information you've all added is very helpful, so I'll look to see if we can fix / work around this issue with the user account needing to be in master for this to work.

[brianjorden]

Appreciate the updates/responses here. I've been in the consulting world and entirely closed source ecosystem for many years and this was an item I was hoping to be able to really dig into if I could. I do have a bit more information to share that I've collected from MSDN/TechNet/StackOverflow/several other different Microsoft and additional sources. Is there any chat type place (Slack/Gitter/Discord/Twitter DMs/etc) I could share some additional info? Asking about chat just cause I have a handful of questions as well and likely easier with some back-and-forth.