azuredatastudio: Can't connect to Database using Windows Authentication on Mac
- 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.<TryGetConnection>b__0(Task1 _) 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:00000000-0000-0000-0000-000000000000
About this issue
- Original URL
- State: closed
- Created 6 years ago
- Comments: 39 (8 by maintainers)
Commits related to this issue
- Fixing IsMemoryOptimized not supported issue (#447) — committed to aleklj/azuredatastudio by benrr101 7 years ago
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.
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.gbogus.sqlserver.com,1433
. Windows Authentication was successful after that.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.
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.
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.<TryGetConnection>b__1(Task
1 _) at System.Threading.Tasks.ContinuationResultTaskFromResultTask
2.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.<<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 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-0f35105a29a9I 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?
I followed the instruction to setup the Kerberos on Mac, but I still got the following error:
And I’m using the version 0.26.7
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.
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 theServer
field, separated by a comma, worked in my case.I have the same error as mmalto. Is there any update on this?