Vscode-mssql: Preview support for Integrated Authentication on Mac is not working

[TVScoundrel]

• MSSQL Extension Version: 1.1
• VSCode Version: 1.16.1
• OS Version: OS X 10.12.6

Preview support for Integrated Authentication (aka Windows Authentication) on Mac is not working. I have a confirmed kerberos ticket but vscode can't access it.

[TVScoundrel]

Sorry I needed to edit because I copy - pasted the title from the release notes and the truth is that I can't claim it is not working on linux. I have only tested it on my mac.

[kburtram]

@TVScoundrel the Integration Authentication support is handled within the .Net Core SqlClient component. I've reached out to the owner for that component on what information we could collect that would help us troubleshoot this issue. I'll let you know once I hear back.

[TVScoundrel]

Thanks @kburtram, yes I seem to have found an open issue at dotnet/corefx here: https://github.com/dotnet/corefx/issues/22463

[kburtram]

@TVScoundrel I contacted the SqlClient owner and he will look at the corefx issue you've mentioned. I'll let you know if there is any update or more information needed to troubleshoot. Thanks!

[kburtram]

@TVScoundrel @geleems asked the following questions regarding your configuration. Please let me know if the questions need clarification.

Can you please verify if the user used setup SQL Server SPN with correct FQDN?
What was the SQL Server SPN the user used?

[geleems]

@TVScoundrel
Can you also provide stack trace of the error?
Is the trace the same as following?

mssql: Failed to connect: Cannot access Kerberos ticket. Ensure Kerberos has been initialized with 'kinit'.

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)

If so, the exception was thrown here:
https://github.com/dotnet/corefx/blob/dcc29528c9c4633e81b1937e7dd9fde7115a938c/src/Common/src/System/Net/Security/NegotiateStreamPal.Unix.cs#L122-L144
It seems we need help from System.Net.Security team.

[TVScoundrel]

I would not know where to get this stack trace from, I am just trying to connect from within vcode using the connection setup, I have no idea how to debug this.

[TVScoundrel]

As far as the Can you please verify if the user used setup SQL Server SPN with correct FQDN question, I just followed the tutorial here: https://aka.ms/vscode-mssql-integratedauth

[saurabh500]

@TVScoundrel We need 2 pieces of information to take this further.

1. We need to know the SPN registered, for the SQL server that you are trying to use. You can find this using setspn -L COMPUTERNAME where COMPUTERNAME is the NETBIOS name of the SQL server host. setspn is a Windows command. Alternatively, if you have access to the server host you can try from the server host setspn -L %COMPUTERNAME%
2. While using the tool vscode-mssql, what is the server name that you are using? Is it the FQDN of the server, or just the hostname of the server?

[geleems]

@TVScoundrel

Do you have the following two lines in your ~/.ssh/config file, or in your /etc/ssh_config? :

GSSAPIAuthentication yes
GSSAPITrustDNS yes

The first is required to enable GSSAPI (Kerberos) authentication, the second one is required to get ssh to canonicalize the hostname via DNS and use the canonical name to obtain a host service ticket. Without the second one, ssh will use the exact hostname or IP address entered on the command line to try and obtain a host service ticket, and in this case it fails.

[geleems]

@TVScoundrel
Did you get a chance to try my previous suggestion? Can you provide information about how it was?

[AmyLowe]

It seems I have the same problem as @TVScoundrel.

@geleems : Adding those entries to ~/.ssh/config file didn't do the trick.

@saurabh500 :
In answer to your first question, All of the SPNs take the format somegrouping/machinename or somegrouping/machinename.DC.DC.DC.

In answer to your second question, I've tried using just the CN, the FQDN and now the somegrouping/machinename.DC.DC.DC.

None of those attempts were successful.

The stacktrace is the same as the one posted above for all attempts, except for the grouping/machinename.DC.DC.DC, which gave an:
Error connecting to server "xxxxxx/machinename.dc.dc.dc". Details: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid

[VimalKumarS]

I also have the same issue, i tried all above mention solution, still not able to connect using Integrated authentication. any pointers

[carlowahlstedt]

@geleems Though my previous issue was resolved. It seems that it's returned when attempting to connected to a different SQL server. The one that worked is SQL 2016 (13.0.4446.0). The one I tried to connect to today is SQL 2012 (11.0.6607.3). The output window says:

[10:28:11] Connecting to server "[servername]" on document "filename.sql".
[10:28:12] 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 did verify that my ~/.ssh/config still exists and I can still connect to the 2016 server.

[VimalKumarS]

I am also trying to connect Sql 2016 getting below exception, Any pointers?

getting exception 
SqlException: 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)

[martinBaillie42]

I am getting exactly the same error as @TVScoundrel and @VimalKumarS

[saurabh500]

Folks getting the exception while connecting to sql server inspite of having retrieved the correct kerberos token using kinit can you check if your SQL server is setup to use Kerberos ?
While using Integrated auth from unix systems only Kerberos authentication to Sql Server is allowed.

You could start by checking if Sql Server host has a SPN registered with the SQL Server.

You could log in to the Sql Server host machine (if Sql is hosted on Windows) and use the command setspn -L %COMPUTERNAME%
If Kerberos is configured then you should see SPNs (Service Principal Names) registered on the Sql Server host which start with MSSQLSvc/host.domain.com

In case you cannot access the Sql Server host machine, you could also query this from another Windows Machine by using the command setspn -L SQLSERVERNETBIOS where the Sql Server Net BIOS is the Net BIOS name of the sql server host.
More information about registering SPN with Sql Server is at link

[saurabh500]

cc @VimalKumarS @TVScoundrel @martinBaillie42

[Code-DJ]

Thanks @saurabh500 I can confirm this works.

Ran the following command on my SQL Server:

setspn -A MSSQLSvc/SERVERNAME.Domain.com username

For username I used the same name that is running the SQL Server Service. I hope it is correct and does not have any security impact. Thanks!

[saurabh500]

For username I used the same name that is running the SQL Server Service. I hope it is correct and does not have any security impact. Thanks!

No, that shouldn't have an impact. In fact the username in the setspn command is used to see if the user has permissions to add an SPN record to the AD.

Thanks for the confirmation @Code-DJ

[VimalKumarS]

@saurabh500
After running the command, fails to assign SPN on account [Insufficient access right to perform the operation]

[saurabh500]

@vimalkumars you may need to work with your AD administrators to get this done.

[carlowahlstedt]

I was able to confirm that SQL servers with Kerberos configured could be connected to but without could not be connected. I was not able to adjust the Kerberos settings as the machines are locked down, but I suspect it would fix the issue.

[undecided2013]

Guys we seem to be having the same issue from within .net core 2.0 sql client from a linux docker container. Out sql server is definitely kerberos enabled but the spn is not set at the computer name. It is set to a service account instead. This definitely looks like an oversight in the sqlclient that's why some people have success (spn is computer name) and some don't (spn is svc account). We need to somehow get the right people involved to fix this in sqlclient or System.Net.Security.

[caztial]

simply i got the same error for .net core 2.0 sql client from a linux docker container.

Cannot authenticate using Kerberos. Ensure Kerberos has been initialized on the client with 'kinit' and a Service Principal Name has been registered for the SQL Server to allow Kerberos authentication.
ErrorCode=InternalError

just change your connection string to
"Server=127.0.0.1,1433;User Id=sa;password=yourStrong(!)Password;Database=test;MultipleActiveResultSets=true;"

this will use SQL authentication instead of Integrated Authentication

[carlowahlstedt]

Found a resolution to configuring SQL Servers with Kerberos. There is a tool
Microsoft® Kerberos Configuration Manager for SQL Server® which will identify if the server has the necessary spn and also fix them.

[sapnamc]

Hello Team ,

I am trying to connect to SQL server fro my .net core app from PCF. Its works fine when I use username and password. But Unable to configure Windows authentication.

Can someone please help
carlowahlstedt mentioned he has a solution.
I have configured SQL server with kerberos. What else is required? Could someone please hep.

exception I get
Cannot access Kerberos ticket. Ensure Kerberos has been initialized with 'kinit'.

[carlowahlstedt]

@sapnamc PCF...Pivotal Cloud Foundry?

1. Did you run kinit and authenticate with your windows creds?

2. Did you follow the Kerberos setup instructions?

3. Did you do what was mentioned in the above comment, which is also in some other linked issues?

[sapnamc]

1)
@carlowahlstedt . Yes I I used my windows Credentials . my connection string
[Data Source=[server];Integrated Security = SSPI;Persist Security Info=False;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;Initial Catalog=[MyDB];]

Did you follow the Kerberos setup instructions? Yes . This is Done by DBA

3: Did you do what was mentioned in the above comment
I am doing this settings and I will update here.

Thanks for replying

[riemers]

Since this is the only open ticket. I tried running it on my mac.

[5:22:16 PM] Error connecting to server "localhost". Details: Cannot authenticate using Kerberos. Ensure Kerberos has been initialized on the client with 'kinit' and a Service Principal Name has been registered for the SQL Server to allow Kerberos authentication.
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)

But no luck, i followed all the steps, checked kinit, have a correct kerberos ticket. I can log on to other services via kerberos and i have a windows laptop on which i can correct to it just fine with sql studio using the same credentials. Tried with the default port, tried with using ,1433 (comma etc)

[riemers]

Took a stab at it again. If anyone has some other ideas, i use a tunnel to connecto to it. But i opened the tunnel from outside of my mac. Connected to it from a windows machine and it works. So i know my connection is valid and allows credentials to go through. Just not locally from my mac. Also using 'nomad' which does kerberos for me. In the end i used the ticket viewer to make sure i had the correct AD account but no luck. I do notice i have different error message if i remove my krb5.conf (since i use nomad it still has a ticket) googled that message "unknown mech-code 0 for mech unknown" which at some point someone said he gets that message if he has no valid ticket. So mine is not picked up somehow.

[a087861]

I had the same exact issue on mac and was able to successfully use kerberos along with unixodbc to verify that I could use kerberos to authenticate against sql server. The solution was actually insanely simple for me and I really hope it is for some of you having this issue.

When you go to connect and it asks for your server name, host name, I was originally entering:
server.domain.com
Updating to the following solved it:
server.domain.com,1433

After hours of trying, the solution for me was to simply put a comma and the port after the server name.

Maybe this is a pretty "well, duh" fix, but somehow never tried that. Anyways, let me know if this helps anyone!