Sql Prompt locks AD accounts

dmans1

Recently we upgraded to Sql prompt 8 (8.1.0.2354) and we noticed that AD user accounts were being frequently locked for no apparent reason (users do not enter AD credentials anywhere).
The account gets locked instantly when sql management studio is opened (we have tried management studio versions 2012 , 17.2 and 17.3) and then gets locked intermittently. This behaviour seems to be also OS independent since it is being reproducted on win7x64 and win10x64 PC's.


In the event viewer a lot of lines with the following error can be found "ERROR UniversalAuthenticationAccessTokenCache [(null)] - No tokens found in the token cache"

Uninstalling sql prompt v8 and installing v7.5 resolves this issue

*edit: I have also attached the application's log
*edit2:verbose logs also attached

Jessica R

Hi @dmans1,

I'm glad that's helped with the original errors!

For these other ones, it looks like proxy authentication is failing for SSMS and preventing the connection to our update and usage reporting servers. Can you please try allowing SSMS authenticate the proxy?

To do this, you'll need to open each SSMS config file C:\Program Files (x86)\Microsoft SQL Server\xxx\Tools\Binn\ManagementStudio (where xxx is the version), locate the system.net node, and then add <defaultProxy useDefaultCredentials="true"> as below:

Andrei R

Hi dmans1! Thank you for reporting this issue, I have created bug ticket to look into this further, ref. SP-6659

Regards,
Andrei

Jessica R

Hi @dmans, as mentioned on the other post, this should be resolved as of version 8.1.0.2448 but please let us know if you experience further issues!

dmans1

Hi @Jessica R
The UniversalAuthenticationAccessTokenCache [(null)] is gone with the latest version (8.2.5.2924) but the account locking still exists.

I have attached verbose logs from management studio startup. resulting in AD account lockout (SSMS 17.3 and windows 10x64 Enteprise V.1709)

dmans1

Hi @Jessica R

I did modify the ssms config file, and there is an OMG moment for me. The AD locking account no longer occurs after starting SSMS. So I suppose SQL prompt uses the exe's config to do it's network stuff.
A nice thing to do in a future release (since I can only suppose this will happen in all users that use a proxy) would be to override this part an progammaticaly make use of the useDefaultCredentials value.

Anyway, thanks for the support and keep up the good Work!!

dmans1

Hi @Jessica R

Sadly, my "success" was due to making the following error in the config
<settings>
<defaultProxy useDefaultCredentials="true"/>
<ipv6 enabled="true"/>
</settings>
(default proxy element wrongly
placed inside the settings) which resulted in the below error


Once correcting my error, the problem reappeared.

That mistake made absolutely clear to me what caused the issue. It is the redgate client service that proxies the licensing requests (made from Sql Prompt, and any other product that uses the new licensing proplem)to you that is causing this.

So, I added the following section in the configuration section of the C:\Program Files (x86)\Common Files\Red Gate\Shared Client\RedGate.Client.Service.exe.config file

<system.net>
<defaultProxy useDefaultCredentials="true"/>
</system.net>
,restarted the service... and problem solved!!!

*Edit: The issue still seems to exist, I got locked some minutes after opening Management studio, still think that this is a redgate client issue

*Edit2: Still back to square one. I opened sql compare, data compare, but no account locking exists, account gets locked only with management studio/Sql prompt 8. The really weird thing is that the locking occurs when management studio 17.3,2016, VS2013 open but not on VS2010.

Jessica R

Hi @dmans1

So sorry to hear that!

Since SQL Prompt doesn't have it's own process but runs within SSMS, the option has to be changed in the config file for SSMS. I don't think we would make changes to the SSMS config by default, but perhaps an option in the installer to change it may be possible. I'll pass that suggestion on to our devs- thank you!

I didn't spot any Redgate Client errors in the Event Log so I didn't think about how we should also make the config change there- sorry about that!

To confirm though- are you still seeing frequent errors in the Event Log now? Or is the locking up happening even without the errors?

Can you please share the latest SQL Prompt logs?

Can I also just confirm as a sanity check- uninstalling SQL Prompt momentarily makes the problem go away?

dmans1

Hi @Jessica R

I confirm, I am getting account Lockouts without the errors mentioned in the OP.
I mentioned the redgate client part in my previous post, as when SSMS is misconfigured and sql prompt cannot communicate with the redgate client (see the nolock logs and the screenshot), I do not get any account lockouts at all.


When everything is configured "as it should" (lock logs) I get immediate account lockouts after starting SSMS. I have also noticed recently that i also get account lockouts if SSMS is running and I log on to my station via remote Desktop

AlexMBanks

We are having the same issue described here - lockouts on AD accounts. I'd just like to add that we are only seeing this issue when connecting with accounts from different domains. e.g. when using 'run as different user'. I have not tried the <defaultProxy> config change yet.

Jessica R

Thanks @dmans1!

It's odd because in the log with the locking, it doesn't appear like SQL Prompt is failing or even trying to connect and load your databases.

Can I just check:
- Does it remain if you disable Tab History?
- What settings do you have enabled from SQL Prompt>Options>Suggestions>Connections

@AlexMBanks I unfortunately don't think the <defaultProxy> workaround will help here that fix was mainly to help with the Proxy Authentication errors that @dmans1 was seeing in Event Viewer. Can you please let me know the answers to the above questions as well?

Thanks!

dmans1

Hi @Jessica R ,

My connection settings are:


I tried consecutively,

1. Unchecking "Enable tab history"
2. Checking "Enable Tab History" ,unchecking "Restore open tabs when SSMS starts",unchecking "automatically reconnect restored tabs"
3. Deleting appdata/local/redgate/sql prompt 8/savedTabs.db (also deleted sql prompt 6,7 directories on that folder level since history seemed to be rebuild on start from the old databases)

, with no sucess

Jessica R

Darn, thanks for checking nonetheless!

If it's not tab history, just to confirm that loading the databases is what's causing this - if you set Options>Suggestions>Connections> 'Specify the databases you want to load suggestions for' to "Only load suggestions for certain databases" and leave the list blank, then the problems do go away- is that right?

dmans1

Sadly, that's not the case. I unchecked everything and i still get lockouts.
Might be a hint that the lock occurs immediately after ssms is opened, even if no connection to any database is opened.

dmans1

Hi @Jessica R ,

Following up the other post , I have no other, non RedGate, ssms related plugins. To test the plugins indivirually, i uninstalled all the software, downloaded the latest developers pack (sql prompt pro 9, ssms integration pack 1.6) and started installing them individually, monitoring my bad password count with an ldap tool.
I have not come to a definite conclusion, but what is certain, is that with SSMS integration pack or sql prompt installed, when the ssms starts, the bad password count starts going up.

Sometimes that count stays at 2, sometimes it goes up to 3 or above(AD account gets locked on 3 wrong attempts, so I cannot know how more above 3 that number goes)

Jessica R

Thank you and my apologies, I posted my last reply to the other post you had commented on. I've deleted it from there and copied it below:

Thanks for those details, @dmans1!

Hm, I'm wondering if SQL Source Control may be causing it, since SQL Prompt doesn't actually connect through the object browser. Do you have SQL Source Control or any other SSMS plugins (Redgate or other)?

I did track down one old ticket from a few years ago, where a customer was getting lockouts after a password reset and suspected SQL Prompt, but the failed logins ended up coming from Microsoft Intellisense.

I don't exactly know why that would be, but can I just check--do you have SQL Prompt>Options>Labs>Experimental features>Refresh Microsoft Intellisense cache when refreshing suggestions enabled?

If you disable Intellisense from Tools>Options>Text Editor>Transact-SQL>Intellisense, does the problem go away?

Thanks!

Just to be entirely sure that this isn't related to native Intellisense like the previous customer experienced, can I just double check that disabling SQL Prompt>Options>Labs>Experimental features>'Refresh Microsoft Intellisense cache when refreshing suggestions enabled' and/or Tools>Options>Text Editor>Transact-SQL>Intellisense, doesn't have any effect?

If not, I'll go ahead and escalate this as I'm unfortunately running out of ideas- please let me know!

dmans1

Hi @Jessica R

Yes, both options are disabled as per previous suggestion. Let me point out that locking also occurs when i disable Sql prompt code suggestions from SqlPrompt-Options-Behaviour-Show code suggestions and
SqlPrompt-Options-Connections-Only load suggestions for certain databases (with an empty liist), and the 2 checkboxes above unchecked.

Jessica R

Thank you @dmans1! I'm going to escalate this and will get back to you through a ticket we have open for you on this- # 103063.

Jessica R

Just a quick update here that this bug where SQL Prompt is locking AD accounts is now logged internally as SP-6659. We will post here once we have an update on a fix.

edburdo

Has this been resolved, or is it still an open issue?

I have the same results.  Where I work, we have two accounts.  A "regular" account, and an "Admin" account.  The Admin (also called "a") account is used to connect to SQL Server.

So, I run SSMS as "Administrator".

With Redgates tools, this locks my "a" account at least 3 times a week (I start SSMS every morning, M-F).

I have SQL Prompt 9.2.8.6358 installed.

Jessica R

Hi @edburdo

I'm very sorry to say that this hasn't been resolved yet. We have actually decided to remove SQL Prompt's official support for Azure until we are able to resolve various issues that have been reported (this locking issue included).

edburdo

I'm not using Azure.  Would that still be locking my accounts?  Even though I don't use it?