Connecting with Active Directory Password Authentication
InsclearBackeman
Posts: 4 New member
Hi!
Setup
We are using Active Directory Password Authentication to connect to our databaseinstance. However, when SQL Prompt is loading I get the following log (Verbose logging on):
If I switch to SQL Server Authentication it works perfect.
I've tried googling after others with the same problem but without luck, the same for this forums. Does anyone know if this is a know bug/not implemented yet or is this something that should be reported? Are there any workarounds to make the AD-login work?
Thanks in advance.
\Jens
Setup
- Microsoft SQL server in Azure (V12)
- SQL Server 2016 Management Studio 13.0.15000.23
- Redgate SQL Prompt 7.2.4.291
We are using Active Directory Password Authentication to connect to our databaseinstance. However, when SQL Prompt is loading I get the following log (Verbose logging on):
27 Jun 2016 10:56:05,693 [1] INFO RedGate.SqlPrompt.Engine.LogService - Starting new SQL Prompt 7.2.4.291 editor session 27 Jun 2016 10:56:05,876 [9] INFO RedGate.SQLPrompt.CommonUI.Options.SqlPromptApplication - Initialized: RedGate.SQLPrompt.CommonUI.External.SharedControls.Controls.InteractiveHelp.ConvertHtml2Rtf.Html2Rtf 27 Jun 2016 10:56:05,974 [1] INFO RedGate.SqlPrompt.Metadata.Options.OptionsBase - Loading UIOptions from C:UsersusernameAppDataLocalRed GateSQL Prompt 7RedGate_SQLPrompt_CommonUI_Options_UIOptions.xml (attempt 0 of 10)... 27 Jun 2016 10:56:06,008 [1] INFO RedGate.SqlPrompt.Metadata.Options.OptionsBase - Successfully loaded UIOptions 27 Jun 2016 10:56:06,048 [1] INFO RedGate.SqlPrompt.Metadata.Options.OptionsBase - Loading EngineOptions from C:UsersusernameAppDataLocalRed GateSQL Prompt 7RedGate_SqlPrompt_Engine_EngineOptions.xml (attempt 0 of 10)... 27 Jun 2016 10:56:06,066 [1] INFO RedGate.SqlPrompt.Metadata.Options.OptionsBase - Successfully loaded EngineOptions 27 Jun 2016 10:56:06,092 [1] INFO RedGate.SqlPrompt.Metadata.Options.OptionsBase - Loading LayoutOptions from C:UsersusernameAppDataLocalRed GateSQL Prompt 7RedGate_SqlPrompt_Format_Layout_LayoutOptions.xml (attempt 0 of 10)... 27 Jun 2016 10:56:06,108 [1] INFO RedGate.SqlPrompt.Metadata.Options.OptionsBase - Successfully loaded LayoutOptions 27 Jun 2016 10:56:06,117 [1] INFO RedGate.SqlPrompt.Metadata.Options.OptionsBase - Loading TabMagicOptions from C:UsersusernameAppDataLocalRed GateSQL Prompt 7RedGate_SQLPrompt_CommonUI_Options_TabMagicOptions.xml (attempt 0 of 10)... 27 Jun 2016 10:56:06,125 [1] INFO RedGate.SqlPrompt.Metadata.Options.OptionsBase - Successfully loaded TabMagicOptions 27 Jun 2016 10:56:06,148 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - BaseConnect(RedGate.SQLPrompt.SsmsPackage.ConnectAdapter) constructed successfully 27 Jun 2016 10:56:06,245 [1] INFO RedGate.SQLQueryKeeper.TabMagicConnect2016 - ctor started 27 Jun 2016 10:56:06,250 [1] INFO RedGate.SQLQueryKeeper.TabMagicConnect2016 - ctor complete 27 Jun 2016 10:56:06,261 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Initialising DTE and AddIn connections. *** DTE.FullName = C:Program Files (x86)Microsoft SQL Server130ToolsBinnManagementStudioSsms.exe, DTE.Version = 2015.0130, DTE.CommandLine = , *** AddIn.Name = SQL Prompt, AddIn.ProgID = RedGate.SQLPrompt.SsmsPackage.ConnectAdapter, AddIn.Description = SQL Prompt 27 Jun 2016 10:56:06,265 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Current culture en-US 27 Jun 2016 10:56:06,269 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - AddIn.SatelliteDllPath = 27 Jun 2016 10:56:06,559 [1] DEBUG RedGate.SQLQueryKeeper.Commands.CommandKeyboardBinder - Configuration setting changed 27 Jun 2016 10:56:06,564 [1] DEBUG RedGate.SQLQueryKeeper.Commands.CommandKeyboardBinder - Configuring keyboard bindings 27 Jun 2016 10:56:07,390 [1] DEBUG RedGate.SQLQueryKeeper.Commands.CommandKeyboardBinder - Removing conflicting command bindings 27 Jun 2016 10:56:07,486 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Initialisation successful. 27 Jun 2016 10:56:07,545 [1] INFO RedGate.SQLQueryKeeper.TabMagicConnect2016 - Initialize started 27 Jun 2016 10:56:07,549 [1] INFO RedGate.SQLQueryKeeper.Initialization - Connecting 27 Jun 2016 10:56:07,699 [1] INFO RedGate.SQLQueryKeeper.MutexWrapper - Taking mutex for DocumentStore ctora8c521e0-5b69-4c31-8ddb-bc09f5c0073e 27 Jun 2016 10:56:07,724 [1] INFO RedGate.SQLQueryKeeper.MutexWrapper - Releasing mutex for DocumentStore ctora8c521e0-5b69-4c31-8ddb-bc09f5c0073e 27 Jun 2016 10:56:07,730 [1] INFO RedGate.SQLQueryKeeper.TabMagicConnect2016 - Initialize complete 27 Jun 2016 10:56:07,734 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - OnConnection(ext_cm_AfterStartup) 27 Jun 2016 10:56:07,738 [1] DEBUG RedGate.SQLPrompt.CommonVS.BaseConnect.1 - ConnectMode is cm_AfterStartup, so calling OnStartupComplete to complete startup process 27 Jun 2016 10:56:07,744 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Handling OnStartupComplete() 27 Jun 2016 10:56:07,748 [1] DEBUG RedGate.SQLPrompt.SsmsPackage.ConnectAdapter - Hooking up Object Explorer events 27 Jun 2016 10:56:07,756 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Entering Startup() 27 Jun 2016 10:56:07,760 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Loading plugins... 27 Jun 2016 10:56:07,766 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Starting EditorManager... 27 Jun 2016 10:56:07,836 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Creating menus for SQL Prompt edition Trial 27 Jun 2016 10:56:07,842 [1] DEBUG RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Building main menu into MenuBar 27 Jun 2016 10:56:07,917 [1] DEBUG RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Building context menu into SQL Files Editor Context 27 Jun 2016 10:56:07,921 [1] DEBUG RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Building tab context menu into Easy MDI Document Window 27 Jun 2016 10:56:07,957 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Calling SqlPromptApplication.Initialise() 27 Jun 2016 10:56:08,017 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Startup() completed successfully. 27 Jun 2016 10:56:08,022 [1] INFO RedGate.SQLPrompt.CommonVS.BaseConnect.1 - SqlPromptApplication initialised. (startup took 0:00:02.616) 27 Jun 2016 10:56:08,027 [1] DEBUG RedGate.SQLPrompt.CommonVS.BaseConnect.1 - Startup time: 00:00:02.6166776 from class load, 00:00:02.6166776 from instance init 27 Jun 2016 10:56:08,033 [1] INFO RedGate.SQLQueryKeeper.TabMagicConnect2016 - OnStartupComplete started 27 Jun 2016 10:56:08,037 [1] INFO RedGate.SQLQueryKeeper.Initialization - OnShellInitialized 27 Jun 2016 10:56:08,041 [1] INFO RedGate.SQLQueryKeeper.MutexWrapper - Taking mutex for Reload open tabsa9184b5c-e57d-46ff-b0f7-9aa80802701d 27 Jun 2016 10:56:08,046 [1] INFO RedGate.SQLQueryKeeper.MutexWrapper - Releasing mutex for Reload open tabsa9184b5c-e57d-46ff-b0f7-9aa80802701d 27 Jun 2016 10:56:08,049 [1] INFO RedGate.SQLQueryKeeper.TabMagicConnect2016 - OnStartupComplete complete 27 Jun 2016 10:56:48,617 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnRegisterView IVsTextView: 424259308 27 Jun 2016 10:56:48,621 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnRegisterView new TextViewMonitor created 27 Jun 2016 10:56:48,630 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnRegisterView IVsTextLines:424260940 27 Jun 2016 10:56:48,633 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnRegisterView new TextLinesMonitor created 27 Jun 2016 10:56:48,638 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnNewLanguageService Guid: c4d96929-a9b0-42cc-b3e0-adac0435d7f2 IVsTextLines: 424260940 IsReadonly: False 27 Jun 2016 10:56:48,641 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - OnNewLanguageService It is a SQL editor 27 Jun 2016 10:56:48,645 [1] DEBUG RedGate.SQLPrompt.CommonVS.Managers.EditorManager - Starting SQL Prompt 27 Jun 2016 10:56:49,431 [1] INFO RedGate.SQLQueryKeeper.TabWatcher - Document opened 27 Jun 2016 10:56:49,488 [1] DEBUG RedGate.SQLQueryKeeper.TabWatcher - Activation changed from Object Explorer to SQLQuery1.sql 27 Jun 2016 10:56:49,492 [1] DEBUG RedGate.SQLQueryKeeper.TabWatcher - Documing losing focus 27 Jun 2016 10:56:49,496 [1] DEBUG RedGate.SQLQueryKeeper.TabWatcher - Document gaining focus 27 Jun 2016 10:56:49,566 [1] INFO RedGate.SqlPrompt.Cache.Candidates.ServerCandidate - Created new ServerCandidate for server: ic-dev1-sql.database.windows.net 27 Jun 2016 10:56:49,596 [8] DEBUG RedGate.SqlPrompt.Cache.Candidates.ServerCandidate - Checking is contained or restricted 27 Jun 2016 10:56:49,836 [8] WARN RedGate.SqlPrompt.Cache.Candidates.ServerCandidate - Failed to load server dev-sql1.database.windows.net System.Data.SqlClient.SqlException (0x80131904): Cannot open server "ourdomain.com" requested by the login. The login failed. 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 ..(ConnectionProperties ) at ..() at ..() ClientConnectionId:47229db6-1177-4a56-a034-83133afce758 Error Number:40532,State:1,Class:20 27 Jun 2016 10:56:49,945 [1] DEBUG RedGate.SQLPrompt.CommonUI.Forms.TaskExecuter.LoadingProgressPopupViewModel - Closing messages for ic-dev1-sql.database.windows.net 27 Jun 2016 10:56:49,957 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 424259308 27 Jun 2016 10:56:57,905 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 424259308
If I switch to SQL Server Authentication it works perfect.
I've tried googling after others with the same problem but without luck, the same for this forums. Does anyone know if this is a know bug/not implemented yet or is this something that should be reported? Are there any workarounds to make the AD-login work?
Thanks in advance.
\Jens
Comments
Thanks for your post. Unfortunately connecting using Azure Active Directory authentication isn't supported in SQL Prompt at the moment. We're hoping to implement it in a future version of SQL Prompt, but it's being prioritised against other potential features that could be worked on.
The best way to register your interest (and allow others to register theirs, as well as get updates when we release a build with it in) is to post a request on our UserVoice site which we use to help prioritise what we'll work on next.
Thanks,
Aaron.
We're not aware of any workaround at the moment - if you find one however, please do let us know!
I think the issue is that Azure Active Directory Authentication support was only added in .NET 4.6. At the moment the code that connects and refreshes the suggestions is shared between all the SSMS versions supported, and so is targeting a lower .NET version so that older SSMS versions can use it. We've now started investigating what we'd need to change in SQL Prompt to get this working, if you're subscribed to the UserVoice request you'll get a notification as soon as we have a new build with support for it.
Thanks,
Aaron.
https://redgate.uservoice.com/forums/94 ... uthenticat