Options

Connecting with Active Directory Password Authentication

InsclearBackemanInsclearBackeman Posts: 4 New member
edited October 27, 2016 4:19AM in SQL Prompt
Hi!

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
· Share on Twitter

Comments

  • Options
    Aaron LAaron L Posts: 596 New member
    Hi Jens,

    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.
    · Share on Twitter
  • Options
    InsclearBackemanInsclearBackeman Posts: 4 New member
    Thank you for the quick response. To bad it wasn't what I wished for. I've posted a suggestion https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/15008409-add-support-for-azure-active-directory-authenticat. Hopefully I'm not the only one needing this.
    · Share on Twitter
  • Options
    pkmccarthypkmccarthy Posts: 16
    Can you tell us if its an explicit permissions issue we could set in the db or is it due to redgate using a separate thread to connect to the db that doesnt have AD integrated mode available -- said another way, any work around?
    · Share on Twitter
  • Options
    Aaron LAaron L Posts: 596 New member
    Hi pkmccarthy,

    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.
    · Share on Twitter
  • Options
    cdebrycdebry Posts: 1 New member
    Any update on this? We're moving to active directory authentication and SQL Prompt will be completely unusable. Thanks.
    · Share on Twitter
  • Options
    InsclearBackemanInsclearBackeman Posts: 4 New member
    My suggestion at UserVoice has change status to Planned, I take that as a good sign.

    https://redgate.uservoice.com/forums/94 ... uthenticat
    · Share on Twitter
Sign In or Register to comment.