I was faced today with the issue when trying to create an ODBC connection from an Application server to the SQL server.

When using Windows Authentication to access the SQL I got an error of “sql Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.”

Looking at the Application log I got the below

“SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure”

Now snooping around google I can see that this is caused by various reasons

1- Incorrect SPNs registered to the SQL Server

So performing SETPSPN – L Servername

image

Which looked fine to me, but if it doesn’t in your case delete the MSSQLSVC SPNs and restart SQL services and it should get re-registered automatically.

I returned to the Application Log on the SQL and now I could see the following

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

I verified that the FQDN of the Machines is infact the expected as others reported that this could be an issue

image

Now continuing to look for issues, I found this article http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452

As it suggests at the end

  • Check your local security policies to see if any essential rights have been denied.
  • Try to connect to a share on the server. If connecting to a share fails, the account may not have "access this computer from the network" rights or may be missing other domain or network level permissions.”
  •  

    I tried to make a network share access to verify the connectivity between machines and I was UNABLE to do that.

    Running an RSOP (Resultant Set of Policies) report showed that the “Access From network” Setting was locked out to a single user.

    So I removed it and refreshed the GP and now I can access the share.

    So going again to the ODBC, and it Worked perfectly fine.

    Hope this helps out.

    About these ads