Vscode-mssql: Add ability to use Windows authentication when connecting to SQL Server from Mac/Linux

[sanagama]

Here's the original issue reported for the vscode-mssql prototype

[SmithPlatts]

This would be of huge benefit

[ChrisMiami]

This is huge for me and is the difference between being productive with VS Code on OSX or RDP'ing to a remote box to use an out-of-date SSMS that has only scripting capabilities. In fact, the inability to work efficiently has caused reprioritization of the project. So this would help me immeasurably!

[kburtram]

@ChrisMiami unfortunately this issue is blocked on upgrading to .Net Core 2.0 to provide this functionality. Currently .Net Core 2.0 is a little too early in the development cycle to use in a shipping project, so we can't implement for the upcoming release. We plan to upgrade to 2.0 in the subsequent release after that which will bring in integrated auth support.

[kevcunnane]

As we now have a .Net Core 2.0 Preview release, we can target support in our next update. This will depend on stability of the .Net Core 2.0 APIs

[datfinesoul]

This would be extremely helpful! Thanks for working on this feature.

[carlowahlstedt]

@kevcunnane Looks like they aren't currently working on Mac?
https://github.com/dotnet/corefx/issues/2089#issuecomment-300227321

[kevcunnane]

@carlowahlstedt it's all working nicely - verified myself, we have a "how to" doc up in the PR I just put out. It'll be merged in by tomorrow and in our 1.1 release (finally)!

[xs9627]

Have already configure the KDC and waiting for the update. Cheer!
P.S. In step 3 of the help doc, I think the command should be kinit [email protected] as to the steps above. Or, It'll shows 'kinit: krb5_get_init_creds: unable to reach any KDC in realm REDMOND.CORP.MICROSOFT.COM, tried 0 KDCs'

[carlowahlstedt]

I did steps 1-3, like @xs9627. I agree with was said about the username, but I assumed it was my domain anyway and it worked first time. I'm stoked to try this out!

[kevcunnane]

The build is uploading as we speak - you can check it out at https://github.com/Microsoft/vscode-mssql/releases/tag/v1.1.0-alpha.1. Download either the main VSIX or one of the ones that pre-packaged the tools service for your target. Then use the "Install from VSIX" action in VSCode to install and test.

Note that we'll be pushing some additional fixes and doing stability testing before releasing this. With the .Net Core 2.0 update and some significant changes in the backend we expect we'll need some fixes before publishing to the marketplace.

[carlowahlstedt]

@kevcunnane I've installed it. Tried to connect but getting
mssql: Failed to connect: Cannot access Kerberos ticket. Ensure Kerberos has been initialized with 'kinit'.
I did see a ticket from the klist command, which I would assume means that it's setup and generating properly.

Let me know how I can help.

[kevcunnane]

@carlowahlstedt that's unfortunate. Did you follow the steps in the guide we link to from the help button , or had you already set this up? A few more questions:

• does the ticket start with krbtgt? E.g. ones for my domain look like krbtgt/[email protected].
• has the it expired?

When testing (on Mac) my ticket looked as follows:

$ klist
Credentials cache: API:F1245867-9999-4444-BBBF-65F3BD7D7777
        Principal: [email protected]

  Issued                Expires               Principal
Jul 11 17:48:40 2017  Jul 12 03:48:34 2017  krbtgt/[email protected]

[xs9627]

got the same error with @carlowahlstedt ,

  Issued                Expires               Principal
Jul 13 09:20:10 2017  Jul 13 19:20:02 2017  krbtgt/[email protected]

And I can't use the 'mssql-1.1.0-osx.10.11-x64.vsix', because when reload the VS Code after the vsix installed, it shows error mssql: Failed to load Sql Tools Service.
Then I installed 'mssql-1.1.0.vsix', it will auto download 'SQL tools service' for the first time:

Initializing SQL tools service for the mssql extension.
Note: mssql commands will be available after installing the service.

Platform: darwin, x86_64 (OSX)

Installing SQL tools service to /Users/me/.vscode/extensions/ms-mssql.mssql-1.1.0/sqltoolsservice/1.0.0-alpha.45/OSX.
Downloading https://github.com/Microsoft/sqltoolsservice/releases/download/v1.0.0-alpha.45/microsoft.sqltools.servicelayer-osx-x64-netcoreapp2.0.tar.gz
(21836 KB) .................... Done!
Installing ...
Done! 343 files unpacked.

Dose the version of SQL tools service correct?

My OS version: 10.12.5 (16F73)

[kevcunnane]

Folks, are you seeing the login error or mac or on linux? If Linux, which version?
@xs9627:

• Regarding the bundled macOS version not working, I'll test the bundled macOS runtime out today to see if there is an issue there.
• Regarding failure to log in with a kerberos ticket @llali is trying to reproduce this on mac and linux. I know the ticket needs to be renewed but yours seems like it should be valid. This is a new area for us and is driven by how SqlClient works, and unfortunately it keeps "just working" for me making it hard to repro. So we will keep digging. The whole Kerberos setup is more complicated than we would like, but it's apparently how most major orgs do Windows Auth on Mac & Linux. At a minimum we'll try to update the steps in our guide so you can succeed.

I'll reopen this for now while we investigate.

[carlowahlstedt]

@kevcunnane I'm on macOS Sierra and I did follow the steps. I did step 1-3. Then I installed the vsix and did step 4. I just installed the full vsix, I didn't bother with the platform specific. I did notice it do the auto-download as well.

Note the listed ticket the domain before the @ is in lower case.

$ klist
Credentials cache: API:E225B71B-DBD0-4F26-B5F6-532B68FC9848
        Principal: [email protected]

  Issued                Expires               Principal
Jul 13 15:33:48 2017  Jul 14 01:33:44 2017  krbtgt/[email protected]

Here's the full stack trace from the output window:

[3:36:58 PM] Error connecting to server "[ServerName]". Details: Cannot access Kerberos ticket. Ensure Kerberos has been initialized with 'kinit'.
ErrorCode=InternalError, Exception=Interop+NetSecurityNative+GssApiException: GSSAPI operation failed with error -  An unsupported mechanism was requested (unknown mech-code 0 for mech unknown).
   at System.Net.Security.NegotiateStreamPal.GssInitSecurityContext(SafeGssContextHandle& context, SafeGssCredHandle credential, Boolean isNtlm, SafeGssNameHandle targetName, GssFlags inFlags, Byte[] buffer, Byte[]& outputBuffer, UInt32& outFlags, Int32& isNtlmUsed)
   at System.Net.Security.NegotiateStreamPal.EstablishSecurityContext(SafeFreeNegoCredentials credential, SafeDeleteContext& context, String targetName, ContextFlagsPal inFlags, SecurityBuffer inputBuffer, SecurityBuffer outputBuffer, ContextFlagsPal& outFlags)
   at System.Data.SqlClient.SNI.SNIProxy.GenSspiClientContext(SspiClientContextStatus sspiClientContextStatus, Byte[] receivedBuff, Byte[]& sendBuff, Byte[] serverName)
   at System.Data.SqlClient.SNI.TdsParserStateObjectManaged.GenerateSspiClientContext(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength, Byte[] _sniSpnBuffer)
   at System.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength)

I'll try to watch this thread closer, it took GitHub a bit to email me an update. Let me know how I can assist. Thanks.

[carlowahlstedt]

Additionally, not sure if it plays into anything, but my dotnet version installed is:

$ dotnet --version
2.0.0-preview1-005977

[kevcunnane]

we're using 2.0.0-preview2-006497 on our machines locally but it shouldn't interfere - the tools service is built as a standalone executable so at least in theory, should not be impacted by existing dotnet installs on the machine.

[kevcunnane]

OK, so I'm going to close this out for now and recommend everyone raise issues on the corefx repository if they run into Kerberos problems. We'll also follow up with them to get better error messages for different failure causes into future updates of CoreFx.

The current status is:

• Multiple users (internal and public) have successfully connected using integrated authentication
• There appear to be errors in the drivers on occasion when renewing tokens or performing other actions. This seems especially true on Linux machines. Needs follow up & investigation by the SqlClient team
• There need to be clearer error codes when things go wrong rather than just stating run kinit in the error message coming from SqlClient. Again, needs handling at the driver level
• There is a need for better documentation on an official Microsoft Docs site instead of on this repository. We will follow up with the docs team to see about this part.

Thank you all for your help, feedback and input here.

[carlowahlstedt]

@kevcunnane any idea when the next pre-release of 1.1 extension will drop or if you're otherwise planning on pushing stable? I'll wait to test with the changes that have come since this was last built to ensure something else didn't happen to fix my issue.

[kevcunnane]

We've published it to the marketplace now so I'd suggest testing against that version. However, we haven't changed the .Net Core 2.0 dependency so unfortunately I don't see it fixing the issue if you're running into it.

Things I can think of:

• You're on preview-1 version of dotnet core 2.0 on your machine. Try updating to preview-2. Given we build a fully-packaged dotnet app this shouldn't interfere, but it's worth trying just in case.
• Raise this with the corefx repo as suggested. It's the best place to start driving this - perhaps they can at least show how to turn on diagnostics so we can debug the issue

Can you remind me what platform you're on (e.g. macOS, RedHat Linux)?

[carlowahlstedt]

macOS 10.12.5

[carlowahlstedt]

FYI, I updated to the latest preview and the marketplace released extension but same issue. I'll raise it in corefx as requested. Thanks for the help!

[jpmckearin]

For anyone else coming here after the fact, try adding these two lines to your ~/.ssh/config or /etc/ssh_config:

GSSAPIAuthentication yes
GSSAPITrustDNS yes

https://github.com/dotnet/corefx/issues/22463#issuecomment-331521995
https://github.com/Microsoft/vscode-mssql/issues/985

[j-e-n-k-s-y]

This issue is unspecific. "Windows Authentication" can refer to either NTLM, or Kerberos. The resolution mentioned here solves for Kerberos integration but NTLM Windows Auth still fails.

As of 2020, there's still no way to connect, as far as I'm aware, to an NTLM-secured SQL Server from Mac OS X within Visual Studio or Azure Data Studio.