다음을 통해 공유

Error Message "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

  • 아티클

My name is Archana CM from Microsoft SQL Developer Support team, we support SQL Connectivity issue along with data access technologies and SSIS.

I had chance to work with SQL DBA who was having issues while connecting to his SQL server machine. We have seen many issue with connectivity to SQL but the solution we provider to his issue was sample and different.

In today's blog I am sharing my experience on how we could resolve the issue for him and what issues he was facing .

Main issue was When the BizTalk service is executed , it was throwing the below error message on the application server

Error Message

==================

Failed to contact the SSO database: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Data Source=SQLSERVERNAME;Integrated Security=SSPI;Initial Catalog=SSODB

Error code: 0x800710D9, Unable to read from or write to the database.

I followed all the steps that we do to troubleshoot an connectivity issue but none of those steps were able to resolve this issue. Some important steps are

Step 1:

Did UDL test, it was failing to connect to SQLServer "SQLSERVERNAME" from BIZTalk Server.

Error Message

==============

Microsoft Data Link Error

---------------------------

Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

---------------------------

OK

---------------------------

Step 2:

Created the SQL account and tested it , it was still failing.

Microsoft Data Link Error

---------------------------

Test connection failed because of an error in initializing provider. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

---------------------------

OK

---------------------------

Step 3:

We forced Np, TCp with port 1433 but it was still same issue.

SQL Server Native Client Data Link Error

---------------------------

[Microsoft SQL Server Native Client 10.0]: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

---------------------------

OK

---------------------------

Step 4:

Made a registry change to " DisableLoopbackCheck" under " HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa"

We added this registry change and rebooted, still it was failing with error below

SQL Server Native Client Data Link Error

---------------------------

[Microsoft SQL Server Native Client 10.0]: Login timeout expired [Microsoft SQL Server Native Client 10.0]: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [Microsoft SQL Server Native Client 10.0]: Named Pipes Provider: Could not open a connection to SQL Server [53].

---------------------------

OK

---------------------------

Step 5:

I collected Netmon and Profiler , I could see all the connections and communication happening from BIZTAlk server to SQL Server in SQL Profiler & Netmon but still we could see Login failed issue.

Steps 6:

Checked for Kerberos, Kerberos was not enabled on Active Directory.

We enabled Kerberos on active directory. I could also see correct SPN for SQL account for SQL server but again it was same result.

Even after changes and correct settings BizTalk was not able to successfully connect to SQL server.

Thought may be issue with security.

We added the SQL account to "Access this computer from network" Policy under Local Security Policy -> Local Policies -> User Rights Assignment -> Access this computer from network"

This resolved the issue for us.

Yes, only this setting under Local security Policy didn’t resolve the issue along with that Kerberos was very important.

Hope this blog and my experience will help you to troubleshoot similar issues.

Happy Troubleshooting!!!!

 

Author : Archana(MSFT) SQL Developer Engineer, Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead , Microsoft

Comments

  • Anonymous
    December 19, 2012
    Thanks Archana! I assume this registers 18456 errors in the log; if so, can you share what state gets associated with the error message? Thanks, Aaron

  • Anonymous
    June 30, 2013
    We have the same issue the Windows team has set policies to not accept connections between different domains. They did not want a trust between production and non production domains. So if I try to connect via SQL account or windows account we get the same error "The login is from an untrusted domain and cannot be used with Windows authentication".

  • Anonymous
    November 25, 2013
    Hi Archana, thanks a lot for sharinf this information. Your blog helped me to fix my ongoing production issue.

  • Anonymous
    April 01, 2014
    The comment has been removed

  • Anonymous
    May 05, 2014
    The comment has been removed

  • Anonymous
    November 12, 2014
    Error Message "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

  • Anonymous
    March 25, 2015
    Hi, Where I can find Local security Policy?

  • Anonymous
    March 31, 2015
    if running from LocalHost when debugging Try connection string as ;Integrated Security=false; It works for me

  • Anonymous
    April 14, 2015
    Hai, i have a problem to use my exe file in my intranet. i used sql 2008 r2 database. its work in the local system. when i try to open the same exe in my other computer(LAN CONNECTED) IT SAYS that Login Faied.the login is from untrusted domain and can not be used with windows authentiation. please help me out this trajendran1975@gmail.com

  • Anonymous
    May 12, 2015
    Good

  • Anonymous
    June 04, 2015
    También he tenido los mismos problemas y después de hacer varias revisiones también encontré el tema de la actualización pero antes de quitarla reinicie el servicio Netlogon y mi problema quedo resuelto, así que sugiero lo mismo antes de ejecutar cualquier cosa. :D

  • Anonymous
    June 04, 2015
    Successful También he tenido los mismos problemas y después de hacer varias revisiones también encontré el tema de la actualización pero antes de quitarla reinicie el servicio Netlogon y mi problema quedo resuelto, así que sugiero lo mismo antes de ejecutar cualquier cosa. :D

  • Anonymous
    August 22, 2015
    Any idea on how to remotely connect to an SQL DB from a Microsoft account (not local) on Windows 10 ?

  • Anonymous
    January 20, 2016
    Thanks. It helps a lot!

  • Anonymous
    April 21, 2016
    The comment has been removed

  • Anonymous
    July 26, 2016
    Hi, Could you please clarify this sentence?"Yes, only this setting under Local security Policy didn’t resolve the issue along with that Kerberos was very important."Thanks

  • Anonymous
    September 09, 2016
    Thanks, great inputs . Can you please add the tag "the login is from an untrusted domain"

  • Anonymous
    October 10, 2016
    Buenos dias estimados,necesito ayuda en la epresa donde labora tenemos un dominio en linux debian 7 con samba 3 y openldap, el mismo venia funcionando bien pero ahora los usuarios creados para que se conecten a traves de maquinas con sistema operativo windows estan teniendo problemas para conectarse a la bases de datos sql server2012 a traves de autenticación con usuarios windows, necesito solventar cada usuario tiene su permisologia correspondientes no se por que falla

  • Anonymous
    January 04, 2017
    when we have checked on our sql server log we also got the error as above. but could you plaese advise is ther any way to find the username tried to login

  • Anonymous
    January 23, 2017
    I can reflect to this error: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.It happens also when you try to connect from one SQL server to another (with an SSIS job, for example) and on the target server the SQL Service IDs password has been changed in Active Directory, but not on the service. This does not force the SQL Service to stop, but it cannot authenticate to the domain controller anymore and it leads to various errors and malfunction.

  • Anonymous
    January 31, 2017
    Super-Duper blog! I am loving it!! Will be back later to read some more.I am bookmarking your feeds also

  • Anonymous
    February 02, 2017
    I am not using SPN on my sql server. but I get SSPI handshake failed with error code 0xc000018c,Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

  • Anonymous
    February 12, 2017
    It's in reality a great and helpful piece of info.I'm satisfied that you shared this useful info with us.Please stay us informed like this. Thank you for sharing.

  • Anonymous
    April 01, 2017
    My brother suggested I might like this website. He was totally right.This post truly made my day. You can not imagine simply how much time I had spent for this information! Thanks!

  • Anonymous
    April 01, 2017
    I love what you guys tend to be up too. This kind of clever work and exposure!Keep up the great works guys I've incorporated you guys to my personal blogroll.

  • Anonymous
    April 04, 2017
    Aw, this was an exceptionally good post. Taking a few minutes and actual effprt to ake a very goood article?buut what can I say? I procrastinate a lot and never seem to get anything done.

  • Anonymous
    April 04, 2017
    I adore examining and I conceive this website got some really utilitarian stuff on it!

  • Anonymous
    April 04, 2017
    I think the admin of this web page is truly working hard for his site, for the reason that here every material is quality based stuff.

  • Anonymous
    April 04, 2017
    I really liked your post.Really thank you! Great

  • Anonymous
    April 06, 2017
    you are really a jusst right webmaster. The weebsite loading pace is incredible. It seems tat you're doing any distinctive trick.Also, The contents are masterwork. you've performed a great activity in this topic!

  • Anonymous
    April 06, 2017
    The comment has been removed

  • Anonymous
    April 08, 2017
    I blog frequently and I really appreciate your information. This great article has truly peaked my interest.I am going to take a note of your blog and keep checking for new information about once a week. I opted in for your RSS feed too.

  • Anonymous
    August 08, 2017
    Numéro d'enregistrement auprès de la CNIL : 623953.

  • Anonymous
    September 01, 2017
    Hi there Dear, are you in fact visiting this site on a regular basis, if so after that you will absolutely take good know-how.

    • Anonymous
      September 10, 2017
      Hi there,Thanks for your comments. The blogs are visited on regular basis. Please let me know if you have any specific questions that needs to be addressed.

  • Anonymous
    December 03, 2017
    SQL SERVER – Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication Loopback check can be removed by adding a registry entry as follows: Edit the registry using regedit. (Start –> Run > Regedit ) Navigate to: HKLM\System\CurrentControlSet\Control\LSA Add a DWORD value called “DisableLoopbackCheck” Set this value to 1

  • Anonymous
    July 20, 2018
    Hi,I have somewhat similar issue. In my case I have an application that's hosted on an business domain while my SQL server is in a different domain. Currently I'm able to connect using SQL authentication but I want to use windows authentication from the business domain which is not trusted on the SQL server. How can configure the untrusted business domain windows account on my SQL server ?ThanksKrishna

    • Anonymous
      August 30, 2018
      You would need a two-way mutual trust setup been these domains in order to use Windows Authentication. Please work with your DC/Network team to set this up.