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

MRuston

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

James R

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

pjtaylor49

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

MRuston

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.

James R

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

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

pjtaylor49

Hi James,

Yes, that's worked for me. Thanks!

Paul

MRuston

Fix confirmed. Thanks!

paschott

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.

James R

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

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