locked
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. RRS feed

  • Question

  • Hello,

    I have the in the title mentioned error in the following environment.

    I have a test windows domain (2016) with some test windows servers running as members of the domain. On some of them MS SQL 2016 installed.

    In addition, I installed for testing an UBUNTU Linux 16.04.3 and MS SQL Server 2017 on it. Server name is UBNTSQL / IPv4 is 10.10.11.199.

    I joined the UBNTSQL server to the Kerberos realm of my domain. In “AD Users & Computers” I can find my UBNTSQL server in an extra created OU=Linux. Also in “ADSI Editor” with the distinguished name

    CN=UBNTSQL,OU=Linux,DC=mytestdom,DC=mycompany,DC=com

    All looks fine.

    I can connect to my test SQL instance on UBUNTU as sa’ with the proper password. With the local sa’ user, all is working excellent.

    I can also execute the following statements:

    CREATE LOGIN [MYTESTDOM\MYSQLUSR] FROM WINDOWS; 

    GO

     

    The ’MYSQLUSR’ is an extra user, created in the MYTESTDOM. It is a member of the ’Domain Admins’ group. As the result, the login created.

     

    I can also assign the new created login the ’sysadmin’ server role. I can create in my test database ‘UBNT17’ the user ’MYSQLUSR’ and map the user to the login ’MYTESTDOM\MYSQLUSR’. All working.

     

    I do can login to the UBUNTU Linux as the user ’MYTESTDOM\MYSQLUSR’. It shows me, the server belongs really to the domain / realm.

     

    The only thing, I cannot run, is to connect from a Windows server – member of the mentioned domain to the UBNTSQL SQL server using the integrated security.

     

    I try actually the following.

    1.  I connect to Windows as the user ’MYTESTDOM\MYSQLUSR.

    2.  I start CMD and try to execute:

    sqlcmd –S 10.10.11.199 –d UBNT17 –E

    I receive the message:

    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed.

    The login is from an untrusted domain and cannot be used with Integrated authentication.

     

    From the same CMD session, the following command is working excellent.

          sqlcmd –S 10.10.11.199 –U sa –P mypassword

     

    The same error I receive, if I start SQL Operations Studio on Windows and try to connect:

     

    System.Data.SqlClient.SqlException (0x80131904):

    Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

       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.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 542

    ClientConnectionId:439d4b50-d1c8-40cd-9cff-39d102e125c0

    Error Number:18452,State:1,Class:14

     

    I will be glad to have any ideas to fix the issue.

    Thursday, August 9, 2018 1:27 PM

All replies

  • Hey,

    In your keytab (/var/opt/mssql/secrets/mssql.keytab), you should have SPN entries in the form "MSSQLSvc/**<fully qualified domain name of host>**:**<port>**". You can check this by running "klist -kte /var/opt/mssql/secrets/mssql.keytab".

    When you attempt to connect, Kerberos (the mechanism behind AD) must request an SPN to connect to. The client does this by taking the server name, prepending "MSSQLSvc/" and appending the port. So, if you connect with "sqlcmd -E -S <server>", then you need to have the SPN "MSSQLSvc/<server>:<port>" where <port> is 1433 by default.

    Since you are connecting with "sqlcmd –S 10.10.11.199 –d UBNT17 –E", it is looking for the SPN "MSSQSLvc/10.10.11.199:1433" which is most likely not in mssql.keytab. Try instead connecting with the hostname "sqlcmd -S <hostname> -d UBNT17 -E" so that the SPN generated by the client (MSSQLSvc/<hostname>:1433) will match the one in the keytab.

    • Edited by Dylan Gray Friday, August 10, 2018 9:52 PM formatting
    Thursday, August 9, 2018 2:30 PM
  • Hi Altair.83,

    According to your description, my understanding is that you are going to add a Linux server to Windows Domain and use the Windows Authentication for SQL Server.

    Based on my searching, this can be a problem related to Time Drift between SQL Linux Server and Windows AD, Kerberos authentication requires exact time between two machine, please try to use the following steps:

    ntpdate  **.**.**.** (Windows AD IP)
    adding record for NTP inside  /etc/ntp.conf
    Also creating crontab to synscing time

    Besides, please refer to this blog to check if you have missed something, for example, SNP: https://www.mssqltips.com/sqlservertip/5075/configure-sql-server-on-linux-to-use-windows-authentication/

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 10, 2018 8:51 AM
  • Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed.

    The login is from an untrusted domain and cannot be used with Integrated authentication.

    Most likely it's an SPN issue. Follow the steps provided in the following two sections in the link below and ensure that your SPN is set properly. Only after you properly set the SPN and keytab file will it work correctly.

    Create AD User for SQL Server and set SPN

    Configure SQL Server service keytab

    https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-2017#createuser

     Additional links that might help:

    https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-2017

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-2017

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, August 10, 2018 5:00 PM