Sql Prompt locks AD accounts

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

  Andrei R October 19, 2017 09:18

  - 0 +
• 
  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!

  Jessica R November 16, 2017 03:03

  - 0 +
• 
  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 November 16, 2017 06:42

  - 0 +
• 
  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:
  
  
  

  Jessica R November 17, 2017 02:03

  - 0 +
• 
  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 November 17, 2017 06:18

  - 0 +
• 
  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.

  dmans1 November 17, 2017 08:54

  - 0 +
• 
  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?

  Jessica R November 18, 2017 01:29

  - 0 +
• 
  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
  

  dmans1 November 20, 2017 11:08

  - 0 +
• 
  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.

  AlexMBanks November 20, 2017 16:27

  - 0 +
• 
  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!

  Jessica R November 21, 2017 02:08

  - 0 +
• 
  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

  dmans1 November 21, 2017 07:50

  - 0 +
• 
  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?

  Jessica R November 22, 2017 02:34

  - 0 +
• 
  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 November 22, 2017 06:39

  - 0 +
• 
  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)
  
  

  dmans1 November 27, 2017 06:39

  - 0 +
• 
  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!

  Jessica R November 28, 2017 02:36

  - 0 +
• 
  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.
  

  dmans1 November 28, 2017 06:22

  - 0 +
• 
  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 November 29, 2017 21:26

  - 0 +
• 
  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.

  Jessica R December 19, 2017 17:52

  - 0 +
• 
  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.

  edburdo November 29, 2018 12:57

  - 0 +
• 
  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).
  
  

  Jessica R December 04, 2018 03:03

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

  edburdo December 04, 2018 11:47

  - 0 +

Add comment

Please sign in to leave a comment.