Azuredatastudio: Can't connect to Database using Windows Authentication on Mac

[samirbehara]

• SQL Operations Studio Version: 0.25.0 (0.25.0)

Steps to Reproduce:
I am trying to connect to a Database Server from my Mac, and it throws an error. Details are below.
Is there something which I am missing from a connectivity perspective from Mac?
I have been using SQLOpsStudio for the last 1 month in my Windows machine and it has been working like a charm.

System.Data.SqlClient.SqlException (0x80131904): 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: 44 - Could not compose Service Principal Name (SPN) for Windows Integrated Authentication. Possible causes are server(s) incorrectly specified to connection API calls, Domain Name System (DNS) lookup failure or memory shortage) ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (0x00000005): Device not configured
at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)
at System.Net.Dns.GetHostEntry(String hostNameOrAddress)
at System.Data.SqlClient.SNI.SNIProxy.GetSqlServerSPN(String hostNameOrAddress, String portOrInstanceName)
at System.Data.SqlClient.SNI.SNIProxy.GetSqlServerSPN(DataSource dataSource)
at System.Data.SqlClient.SNI.SNIProxy.CreateConnectionHandle(Object callbackObject, String fullServerName, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Byte[]& instanceName, Byte[]& spnBuffer, Boolean flushCache, Boolean async, Boolean parallel, Boolean isIntegratedSecurity)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, 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.<>c__DisplayClass22_0.b__0(Task1 _) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass28_0.<b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ReliableConnection\ReliableSqlConnection.cs:line 298
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.d__46.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 542
ClientConnectionId:00000000-0000-0000-0000-000000000000

[kburtram]

@samirbehara have you setup the Kerberos ticket on your mac following the instructions at https://github.com/Microsoft/vscode-mssql/wiki/How-to-enable-Integrated-Authentication-on-macOS-and-Linux-using-Kerberos?

[samirbehara]

Thanks Karl for your response. No, I have not. Let me give it a shot.

[nixxholas]

Why would you need a kerberos ticket tho? What if i just want to debug locally?

Am also facing this issue

[kburtram]

@nixxholas Windows Integrated Authentication (Active Directory) uses Kerberos. For macOS and Linux, in most devtest scenarios on a local machine we expect that typically people will prefer SQL Login Authentication since these platforms don't natively integrate with AD. If you use SQL Login you won't need a Kerberos ticket.

[erickangMSFT]

@samirbehara looking at the error message, the error occurs before the login attempt meaning it's a network related issue. Can you ping the IP of host where SQL is running? Use the IP address and port number as the server name in SQL Ops Studio on mac unless you have registered the host in /etc/hosts.
Please let us know if it was the case.

[possebon]

I'm a macOS user too. Why sqlops is requiring to setup something additional when there are other tools based on Electron too and they does not require that? For example: https://github.com/sqlectron/sqlectron-gui

I'm a huge fan o vscode and I see that sqlops is on the same base, but the fact that all my SQL Servers are Windows Authentication and I need to setup something additional, specially because I work with different environments (customers), this is something that makes me really think about using the sqlops as my database client.

Not sure the reasons behind that (configure Kerberos to use sqlops with Windows Authentication), but I'm assuming that as both projects are open source (sqlops and sqlelectron) probably could have some synergy between them.

[nyang]

I followed the instruction to setup the Kerberos on Mac, but I still got the following error:

System.Data.SqlClient.SqlException (0x80131904): 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)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, 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.<>c__DisplayClass22_0.<TryGetConnection>b__0(Task`1 _)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass28_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ReliableConnection\ReliableSqlConnection.cs:line 298
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.<TryOpenConnection>d__46.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 542
ClientConnectionId:f3207182-209d-4221-837b-72e3a6363019

And I'm using the version 0.26.7

[vtomasr5]

Same for me. From OS X High Sierra and with a ticket created, but the OS X hasn't joined into the same AD Domain that resides the SQL Server.

SQL Ops: 0.26.7

System.Data.SqlClient.SqlException (0x80131904): 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) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, 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.<>c__DisplayClass22_0.<TryGetConnection>b__0(Task1 _)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass28_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ReliableConnection\ReliableSqlConnection.cs:line 298 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.<TryOpenConnection>d__46.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 542 ClientConnectionId:c1da064e-b3ba-48f7-afaf-28556d5217d6

[kburtram]

@samirbehara ping me if you'd like to investigate this issue while you're in town. Thanks!

[samirbehara]

@kburtram - Can we meet today anytime between 9AM - 11AM? Please let me know your availability.

[kburtram]

@samirbehara sorry, I was working from home this morning. I'm pretty much open any time tomorrow. Send me an email if you have time to sync-up (address is in my GitHub profile).

[saurabh500]

@samirbehara Are you using the IP address to connect to the server or the short server name?

[saurabh500]

Are you using the IP address of the Sql Server to connect to it?
What do you get for the output of nslookup ?

Can you try to modify your /etc/hosts file to have an entry
fullHostNameOfSqlServer
e.g.
192.168.1.2 mysql.my.domain.com

And then try to connect to Sql Server using the IP address in Ops Studio?

[saurabh500]

My response above is for the exception that looks like

System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (0x00000005): Device not configured
at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)

[jefbarn]

Same problem here, and like others I don't have ownership of the Sql Server to go in and play with the kerberos config.
My question is, can we avoid all this kerberos stuff by just allowing an option to specify the NTLM domain and user/pass in the connection dialog? That's how I'm connecting from my nodejs app and it works great (built on tediousjs/mssql driver) no kerberos required.

[mmalto]

i have the SPN registered for numerous sql servers. i am able to get a ticket for my user id as verified by klist. i still cannot get windows auth to work:

System.Data.SqlClient.SqlException (0x80131904): 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)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, 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.<>c__DisplayClass40_0.b__1(Task1 _) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass28_0.<b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ReliableConnection\ReliableSqlConnection.cs:line 298
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 543
ClientConnectionId:4c2b731a-93fa-4b36-ac56-0f35105a29a9

[kburtram]

I'm going to close this issue to clean-up the backlog a bit. There are various problems with macOS Integrated Auth that we've worked through on this issue and other related issues. If there is currently a blocking problem in this area please open a new issue with repro steps.

[jefbarn]

@kburtram I still don't see a way to specify a domain login in the latest version of azure data studio. Probably shouldn't close this.

[FireInWinter]

I also don't think this should be closed. Kerberos isn't always a solution. Falling back/using NTLM should be an option.

[kburtram]

@FireInWinter I've reopened and switched to a suggestion to support NTLM auth on macOS. Though it's probably better to open a new feature request than reuse this issue, since it already has a bunch of Kerberos troubleshooting notes above which makes the issue topic a bit unclear. Though the issue title seems to still work.

[mitchwinn]

I was having the same problem and searched throughout several issues trying to resolve it. The fix for me was to add an entry in my /etc/hosts file that resolved to the machinename.domain.com.

[thoma87]

The original issue is still a problem, and I suspect a bug. As others have stated, other applications are able to authenticate using Kerberos; the problem is with sqlops only.

Is there another ticket which we should use to capture this specific problem with Kerberos on macOS? Currently, the application is not usable for those using Macs and Windows Auth.

[mleatherb]

I am having the same problem. I've verified I have a ticket and ensured it's connecting to the proper DC by following the guide. I've been using SQLPro Studio since I can't get Data Studio to work and I'm able to use Windows Auth with that simply by giving it DOMAIN\User.Name and Password. Is it possible to have this same auth method in Data Studio?

[bandlor]

I have the same issue on Mac Os 10.14 with Azure Data Studio 1.7.0
I have configured the krb5.conf according to the documentation and I have cached credentials showing when running klist. I can also run the nltest on the server, but there is no MSQSQLSvc/HostName entry and I don't have rights to add anything there.
The server does not allow SQL authentication, so how do I connect now from Mac OS?
I am using a domain account on my MacBook and I can access domain SSO sites in my Firefox without issues, so I know this authentication works fine.
Thanks for help.

[tstanitz]

I have the same error as mmalto. Is there any update on this?

[mas-dse-juremigi]

I am also experiencing these issues. I have a Mac joined to a domain with the servers and have followed the Microsoft guide on setting up kerberos. I have a ticket as indicated using klist and still can't connect using windows authentication!

[sunithex]

I'm also getting an error connecting to a SQL Server on windows domain. AZ Data Studio prompt to run kinit, which I do then and get a valid klist output, but it's stuck there. Any ideas?

[jonhall123]

I am experiencing the same issue, able to get kerberos ticket but cannot authenticate via AD credentials to my mssql instance in ADS (Azure Data Studio)

On latest version of macOS too

[ed-velez]

Was experiencing similar issues where my kerberos config was correct and I was successfully getting a ticket. The less obvious issue was that I was not explicitly defining the sql server port in the connection settings even though its the default port. You can set the port in either the Advanced settings or in the Server field using a comma e.g bogus.sqlserver.com,1433. Windows Authentication was successful after that.

[bandlor]

Was experiencing similar issues where my kerberos config was correct and I was successfully getting a ticket. The less obvious issue was that I was not explicitly defining the sql server port in the connection settings even though its the default port. You can set the port in either the Advanced settings or in the Server field using a comma e.g bogus.sqlserver.com,1433. Windows Authentication was successful after that.

Thanks, this was a good tip, however it still does not work for me. I downloaded the latest Data Studio today (1.12.2), went through the articles in docs.microsoft.com to test the Kerberos and I still have the same problem as before. The only thing I am missing compared to the documentation is the MSQSQLSvc/HostName entry on the server, but I have no way of adding this myself.

[NathanKleekamp]

I, too, was running into this issue on macOS v10.14.6. I successfully setup Kerberos using the Azure Data Studio docs. When running klist I could see cached credentials. @ed-velez suggestion to specify the port in the Server field, separated by a comma, worked in my case.

[sunithex]

@NathanKleekamp could you please attach workflow with bitmaps since I'm still having the issue. Thanks!

[bandlor]

Side-note: Since I was not able to fix this on my machine, I am using Valentina Studio.
It works fine with domain credentials - something I can't do in the Azure Studio.

[slipmp]

1 year no response? :(