SQL Prompt 8.0.8 not working with Active Directory Universal Auth in SSMS 17.2

Does SQL Prompt 8.0.8.2086 support Active Directory Universal Authentication in SQL Server Management Studio v17.2 (14.0.17177.0)?

My SQL Prompt suggestions are not working when I authenticate to my SQL Azure databases in this manner. I've pasted my SQL Prompt verbose logs below. You can plainly see in the logs the error message System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''..

21 Aug 2017 20:46:10,655 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnRegisterView IVsTextView: 629563692
21 Aug 2017 20:46:10,656 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnRegisterView new TextViewMonitor created
21 Aug 2017 20:46:10,656 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnRegisterView IVsTextLines:629570764
21 Aug 2017 20:46:10,657 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnRegisterView new TextLinesMonitor created
21 Aug 2017 20:46:10,657 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnNewLanguageService Guid: c4d96929-a9b0-42cc-b3e0-adac0435d7f2 IVsTextLines: 629570764 IsReadonly: False
21 Aug 2017 20:46:10,657 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnNewLanguageService It is a SQL editor
21 Aug 2017 20:46:10,658 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - Starting SQL Prompt
21 Aug 2017 20:46:10,831 [1] INFO  RedGate.SQLQueryKeeper.TabWatcher - Document opened
21 Aug 2017 20:46:10,849 [1] DEBUG RedGate.SQLQueryKeeper.TabWatcher - Activation changed from Object Explorer to SQLQuery4.sql
21 Aug 2017 20:46:10,850 [1] DEBUG RedGate.SQLQueryKeeper.TabWatcher - Documing losing focus
21 Aug 2017 20:46:10,850 [1] DEBUG RedGate.SQLQueryKeeper.TabWatcher - Document gaining focus
21 Aug 2017 20:46:10,919 [1] INFO  RedGate.SqlPrompt.Cache.Candidates.ServerCandidate - Created new ServerCandidate for server: testserver00001.database.windows.net
21 Aug 2017 20:46:10,920 [24] INFO  UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
21 Aug 2017 20:46:10,920 [24] ERROR UniversalAuthenticationAccessTokenCache - No tokens found in the token cache
21 Aug 2017 20:46:10,991 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 629563692
21 Aug 2017 20:46:11,145 [24] WARN  RedGate.SqlPrompt.Cache.Candidates.ServerCandidate - Failed to load server testserver00001.database.windows.net
System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.LoadServer()
ClientConnectionId:d4065436-7004-4c48-ba05-63da6c45b0f8
Error Number:18456,State:1,Class:14
21 Aug 2017 20:46:11,315 [1] DEBUG RedGate.SQLPrompt.CommonUI.Forms.TaskExecuter.ViewModels.LoadingProgressPopupViewModel - Closing messages for testserver00001.database.windows.net
21 Aug 2017 20:46:12,825 [26] INFO  UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
21 Aug 2017 20:46:12,825 [26] ERROR UniversalAuthenticationAccessTokenCache - No tokens found in the token cache
21 Aug 2017 20:46:13,068 [26] WARN  RedGate.SqlPrompt.Cache.Candidates.ServerCandidate - Failed to load server testserver00001.database.windows.net
System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.LoadServer()
ClientConnectionId:0b105b5e-4c83-411f-b121-5847fcebe6b6
Error Number:18456,State:1,Class:14
21 Aug 2017 20:46:13,138 [1] DEBUG RedGate.SQLPrompt.CommonUI.Forms.TaskExecuter.ViewModels.LoadingProgressPopupViewModel - Closing messages for testserver00001.database.windows.net
21 Aug 2017 20:46:13,426 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 629563692

MRuston August 21, 2017 20:51

Comments

10 comments

Hi @MRuston, sorry you're having this issue.

I can't reproduce this issue here, can you give me some more information about your connection?
- Are you using multi-factor authentication?
- Do you have any extra connection properties or Additional Connection Parameters set?
Thanks for your help!
James
James R August 23, 2017 08:25

0
Hi James,

Answers from my perspective (I'm having the same issue):

Are you using multi-factor authentication?
Yes - Active Directory - Universal with MFA support
Do you have any extra connection properties or Additional Connection Parameters set?
No

Paul

pjtaylor49 August 23, 2017 08:44

0
I've tried this with different Connection Properties of Encrypt Connection and Trust Server Certificate both on/off and have all reproduced the issue.

Also, the account I am using is secured with MFA.

MRuston August 24, 2017 13:55

0
Hey all,

I just tried this out - we'd missed that Microsoft have changes Universal authentication with MFA in the latest update. Thanks for letting us know!

Sorry for the inconvenience,
James

James R August 24, 2017 15:29

0
Hey guys,

Thanks for your patience. I think I've got a fix for this issue, could you please try this build of SQL Prompt out and see if it fixes things for you?

Thanks for your help!
James

James R August 25, 2017 14:51

0
Hi James,

Yes, that's worked for me. Thanks!

Paul

pjtaylor49 August 25, 2017 15:20

0
Fix confirmed. Thanks!

MRuston August 25, 2017 16:09

0
So was this fix not included in build 2247? I don't seem to have auto-complete working with the same scenario - SSMS 17.2, SQL Prompt v8 build 2247, Azure AD Universal w/ MFA.

paschott September 05, 2017 20:05

0
Hi @paschott , unfortunately the fix for this issue didn't quite make it out in the previous release of Prompt.

It should be included in this weeks release. Sorry for the confusion!

Thanks,
James

James R September 06, 2017 08:40

0
Just to let you guys know we have released SQL Prompt 8.0.11 today that contains this fix!

Sorry again for the inconvenience,
James

James R September 06, 2017 15:51

0

Add comment

Please sign in to leave a comment.

How can we help you today?

SQL Prompt 8.0.8 not working with Active Directory Universal Auth in SSMS 17.2

Comments

Add comment