Feature Request: Specify credentials for Windows Authentication

[ben-moore]

• SQL Operations Studio Version: 0.23.6 (MacOS)

I was excited to try SQL Ops Studio for Mac however it's fallen at the first hurdle by not allowing me to specify account details for connecting using Windows Authentication, something that other 3rd Party products (like TeamSQL) allows.

It would be great to be able to add specific credentials for Windows Auth as well as SQL Auth, to enable non-domain accounts on MacOS to connect.

[ghost]

Agreed. This is also a major issue with SSMS, as well. When using the elevated account access methodigy, in many cases the interactive user account does not have access to the SQL Server. This requires us (on Windows) to Run As on SSMS. It's inconvenient and it would be ideal if we could simply specify the appropriate credentials instead.

[nathairtras]

As a bandaid...

If you are on OSX (and I'm assuming quite similar steps on Linux) you can use KRB5CCNAME to force a different credential cache for the app. This is useful in cases of having a 'normal' account and an 'elevated' account, where you primarily want to connect to databases using the elevated account. Not as useful for 2+ Windows accounts that act as SQL logins.

This is all manual, can probably be wrapped in a bash script and made non-interactive.

• Create a ticket for the elevated account
  • kinit --keychain elevated_account
  • This will change your default credential cache!
  • --keychain will store your password in the OSX keychain.
• Switch back to your default account cache (interactive)
  • kswitch -i
• View list of your principals and caches
  • klist -l
  • Copy down the cache name of your elevated account
• Launch SQLOPS with env override for your credential cache
  • KRB5CCNAME='ccache_name' open /Applications/SQL\ Operations\ Studio.app

[mlakmal]

@nathairtras does ur solution depend on macOS to be connected to AD ?

We have macs that are not connected to AD account but all our sql logins through domain accounts. With this approach we can’t connect the Mac to AD but want to use sql studio to use windows credentials...

[rivens623]

Hey @kburtram, this is the suggestion I was speaking to you last night at the Oregon SQL Meeting. This feature would be great for all platforms.

[ben-moore]

Hi @saurabh500, any update on this issue - is it going to be included in a build at some point?

[kburtram]

@rivens623 thanks for the reminder. I'll investigate to see what's involved in supporting this scenario.

[ObsidianPhoenix]

Has there been any word on when this will make it into the schedule? Being able to avoid using runas would be a lifesaver.

Just as a comment, it would be great to be able to manage the credential in a single location, and apply it to multiple connections (to account for password changes, etc)

[tmikoss]

Consider this use case: I'm a consultant, that has to connect to different client-provided databases, with client-provided credentials, often in domain\user form. As I'm working on multiple projects, out of a mac that does not belong to any of those client companies, I'd rather not join my computer to clients AD.

Sadly, my current workaround is to use a different tool where client has provided domain\user type of credentials.

[vijayakarsh]

This would be a great relief when added, we have production on different domain and logging in from workstation is not possible without this option.

[kgleason]

Also casting my vote for this feature. I'm a Linux guy (currently Ubuntu 18.04, but I've been know to run Arch as well) in a Windows shop. I currently use Aqua Data Studio to connect to SQL Server, and it has an option to allow me to specify my domain, username, and password to connect using a domain account from a non-domain machine. Something similar would be amazing for this tool.

[derekforeman]

A certain necessity! Please add.

[atuttle]

I'm in the same boat as @tmikoss:

Consider this use case: I'm a consultant, that has to connect to different client-provided databases, with client-provided credentials, often in domain\user form. As I'm working on multiple projects, out of a mac that does not belong to any of those client companies, I'd rather not join my computer to clients AD.

I guess I'm going to go back to Aqua Data Studio for now too... But I would rather SQL Ops Studio had this feature.

[R-i-c-h-B]

Adding my plus one too. Really would be very helpful all around.

[ghost]

I'm a plus one also. This is a gotta have...

[ClausMunch]

This is the thing that holds me back from primarily using Azure Data Studio.

Def. a +1 from me! :)

[mariolopjr]

I would like to add that this would be a useful feature when connecting from a Mac. It would be nice if this feature is ported over to SSMS, not having to use runas would be amazing (since runas will have SSMS run in the profile of the user, sometimes I forgot and save things to the Desktop, etc...).

At least in PowerShell/Windows, it's so easy to execute things as a different user by providing a Credential object, I dunno why SSMS/Azure Data Studio do not support supplying a different Windows AD credential. And Azure Active Directory does not work for non-Azure SQL databases. -m

[brett-x]

+1 from this me on this feature. I use both Mac and PC (AD-Joined). For security purposes, the accounts we use for DB functions & development are not our regular AD accounts. The lack of the ability to specify the AD account limits what we can do.

[slosada]

Plus one for this feature. Not being able to manually input Windows Auth credentials is the only thing stopping me from using this on macOS consistently.

[DavidEaves345]

+1 for me as well. Runas is just an annoying workaround.

[daghb]

My vote for this, too. Cannot use this tool towards several of the databases having personal user accounts which are not in my local AD.

[ozhug]

my vote as well,

[SSchindi]

I will need this too, please implement it ;)

[martimors]

Great tool, very light weight, fast and intuitive, but this keeps me from using it at the moment unfortunately

[nrccua-bryancusatis]

Is this on anyone's radar? Over 2 years of requests and no real comment outside of workarounds.

[rivens623]

Hey, @kburtram can you provide any update?

[kburtram]

@rivens623 currently .Net SqlClient (the SQL Server driver we use) doesn't support using multiple AD identities in the same context. When we investigated this it looks like the best option would be a capability to manually provide a kerberos ticket to the client for each connection, since this also would enable various Integrated Auth scenarios on non-Windows clients as well. We opened this issue against SqlClient component to add this capability dotnet/SqlClient#305.

Currently SqlClient backlog is pretty filled up through at least end of June. So we are discussing how to fund this SqlClient update sooner if possible as part of a broader set of work items we're doing to improve our Authentication experience. For example, we have a major update to our AAD login experience shipping in the Feb release this week.

@aaomidi is driving our overall authentication experience enhancements. @aaomidi did I missing anything above? As far as ETA, I expect that April would be the earliest this may be available?

[rivens623]

Thanks for the update @kburtram!

[LeonarddeR]

Any updates on this subject, now it is June?

[jtlarson]

I''d also really like to see this happen and am curious about the current status...

[aaomidi]

Hey all, this is going to take a bit longer as it's not in our immediate plans. There is also some technical stuff we need to figure out to get this working with the best possible experience.

These technical discussions are happening concurrently with the work we're doing for the rest of the project.

Please use the emoji responses to show interest in this as that makes it easier to push this one forward.